写代码创建数据库，设置位置后比对用可视化界面创建数据库的区别

一、创建数据库
在网上搜了处理方法，稍微改进了一下
create database tttt_1  
on PRIMARY
(  
    NAME = test1,  
    FILENAME='F:\test\test1.mdf',   --此路径必须存在才能建成功
    SIZE = 10,  
    MAXSIZE = UNLIMITED,  --不限制增长
    FILEGROWTH = 5  
)  
LOG ON
(  
    NAME='test1_dat',  
    FILENAME='F:\test\test1.ldf',  --此路径必须存在才能建成功
    SIZE =5MB,  
    MAXSIZE = 25MB,  
    FILEGROWTH =5MB  
)  
GO
 
 
二、用这种方法创建的数据库 跟用可视化界面创建的发现有差异
右键 新建数据库 将两种的建表脚本生成以后对比有区别的是下面项
 
区别
 
 
 
然后逐一找这这些差异所代表的含义
 
 1）ENABLE_BROKER与SET  DISABLE_BROKER的区别，转自http://www.cnblogs.com/wanghk/archive/2012/05/12/2497170.html
未启用当前数据库的 SQL Server Service Broker，因此查询通知不受支持。如果希望使用通知，请为此数据库启用 Service Broker

昨晚遇到的这个问题，也知道Notifications service依赖底层的Service broker的。本以为只需要执行以下脚本对数据库启用Service broker即可。

alter database DBNAME set enable_broker

但是，执行后，脚本一直处于执行状态，不以为然，正好在忙其它事情就没有查看运行结果，结果到今早一看，居然运行还没有结束。虽然是在一个生产数据库上执行的，数据库也只有30G的样子，但也不至于执行一个晚上也未结束，只好终止执行，使用

SELECT is_broker_enabled FROM sys.databases WHERE name = 'DBNAME'

查看is_broker-enabled为0，依然未启用Service broker

后google下，在一个国外论坛里面找到了解决办法：

ALTER DATABASE DatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE Databasename SET ENABLE_BROKER;

执行以上2条语句，未做任何等待，就提示命令执行完成。

SELECT is_broker_enabled FROM sys.databases WHERE name = 'DBNAME'

查看is_broker-enabled为1

NEW_BROKER选项，SQL SERVER联机丛书上的解释：

每个数据库都包含一个 Service Broker 标识符。sys.databases 目录视图的 service_broker_guid 列显示该实例中每个数据库的 Service Broker 标识符。Service Broker 路由使用 Service Broker 标识符来保证一个会话的所有消息都传递到同一个数据库。因此，Service Broker 标识符在同一网络上的所有实例中应是唯一的。否则，消息可能被误传。

SQL Server 为每个新数据库生成新的 Service Broker 标识符。由于标识符是新的，因此 SQL Server 可以安全地激活新数据库中的 Service Broker 消息传递。网络上的其他数据库应该不会有与之相同的 Service Broker 标识符。

NEW_BROKER。此选项用于激活 Service Broker 消息传递，同时为数据库创建新的 Service Broker 标识符。由于该数据库中所有的现有会话都未使用新的实例标识符，因此，此选项将结束这些会话并返回一个错误。

ROLLBACK IMMEDIATE将立即回滚未完成的事务。

有四个选项用于管理标识符和消息传递：

  • ENABLE_BROKER。此选项用于激活 Service Broker 消息传递，并且保留数据库的现有 Service Broker 标识符。

    注意注意

    在任何数据库中启用 SQL Server Service Broker 都需要数据库锁。若要在 msdb 数据库中启用 Service Broker，请首先停止 SQL Server 代理。然后，Service Broker 才可获得必要的锁。

  • DISABLE_BROKER。此选项用于停用 Service Broker 消息传递，并且保留数据库的现有 Service Broker 标识符。

  • NEW_BROKER。 此选项用于激活 Service Broker 消息传递，并为数据库创建一个新的 Service Broker 标识符。选择此选项将结束数据库中的所有现有会话，并对每个会话返回一个错误。这是因为这些会话不使用新的标识符。必须使用新标识符重新创建任何引用旧 Service Broker 标识符的路由。

  • ERROR_BROKER_CONVERSATIONS。此选项用于激活 Service Broker 消息传递，并且保留数据库的现有 Service Broker 标识符。Service Broker 将结束数据库中的所有会话，并对每个会话返回一个错误。通常，如果某个数据库与其他数据库之间具有打开的会话，并且您要将该数据库还原到一个不同于与之对 话的数据库的时间点，则需要使用此选项。还原的数据库中的所有会话必须以错误结束，因为这些会话现在与其他数据库已不同步。Service Broker 标识符保留，以便引用该标识符的所有路由仍有效。

无论选择指定选项中的哪一个，SQL Server 都不允许具有相同 Service Broker 标识符的两个数据库在 SQL Server 的同一实例中都激活消息传递。如果附加一个数据库，该数据库的 Service Broker 标识符与现有数据库的标识符相同，SQL Server 将停用所附加数据库中的 Service Broker 消息传递。

2）手写脚本创建的数据库未指定PRIMARY  

PRIMARY

指定关联的 <filespec> 列表定义主文件。  在主文件组的 <filespec> 项中指定的第一个文件将成为主文件。  一个数据库只能有一个主文件。  有关详细信息，请参阅数据库文件和文件组。  

如果没有指定 PRIMARY，那么 CREATE DATABASE 语句中列出的第一个文件将成为主文件。

IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [tttt1] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

3）手动创建的生成脚本里面有TRUSTWORTHY OFF 但是自动的没有这个选项

默认情况下，此设置为“OFF”

4)手动创建的生成脚本里面有SET ALLOW_SNAPSHOT_ISOLATION OFF  但是自动的没有这个选项

默认情况下，此设置为“OFF”

SnapshotIsolationState 属性可为以下值之一：

  • On   为数据库启用快照隔离。

  • Off   为数据库禁用快照隔离。

  • in_transition_to_on   一旦当前事务完成，就将启用快照隔离。

  • in_transition_to_off   一旦当前事务完成，就将禁用快照隔离。

SET OPTION PUBLIC.allow_snapshot_isolation = 'On';

结论

  1. 使用 sys.databases 目录视图可以确定两个行版本控制数据库选项的状态。
  2. 对用户表和存储在 master 和 msdb 中的某些系统表的任何更新都会生成行版本。
  3. 在 master 和 msdb 数据库中，ALLOW_SNAPSHOT_ISOLATION 选项自动设置为 ON，并且不能禁用。
  4. 在 master 数据库、tempdb 数据库或 msdb 数据库中，用户不能将 READ_COMMITTED_SNAPSHOT 选项设置为 ON。
  5. 从上面的测试可以看到，原先会发生阻塞的两个会话在使用行版本控制的隔离级别后，都不会遇到阻塞了。但是两种行版本控制的结果又有不同

  SELECT name,snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on
FROM sys.databases

转自http://www.cnblogs.com/xwdreamer/archive/2012/07/30/2615357.html

 


 
5 )手动创建的生成脚本里面有SET READ_COMMITTED_SNAPSHOT OFF 但是自动的没有这个选项
这个选项默认为OFF，若要开启需要各种测试。有风险。
 
--查询数据库状态
select name,user_access,user_access_desc,
    snapshot_isolation_state,snapshot_isolation_state_desc,
    is_read_committed_snapshot_on
from sys.databases
 
--设置数据库为SINGLE_USER模式,减少锁定时间
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE dbname SET MULTI_USER
转自 http://www.cnblogs.com/yipu/p/3744031.html
 
从一些论坛上找了一些资料PO出来，（讲真我自己看的不是很懂，还在研究中） 转自https://social.msdn.microsoft.com/forums/zh-CN/bb76bbb4-7c22-40e1-bc2f-9cd59622642c/readcommittedsnapshot-
开启这个设置，如果数据更改比较大的话，可能会增加TEMPDB的负担。因为行版本信息是保存在TEMPDB的。所以开启这个选项要多做测试确保不会产生副作用。

简单地说, 在这种隔离级别下, 读取的数据如果在更新中, 那么读取到的是更新前的快照(条版本),  修改前的COMMIT数据, 所以这个不产生脏读

NOLOCK提示读取的是更新中的数据(没有 COMMIT), 是脏数据

而为了给数据读取提供可用的行版本, 对于数据更新而言, 它在更新数据前, 就需要为要更新的数据生成行版本(快照), 这是一个额外的开销, 在单个事务中更新的数据量越大, 这个开销越大

SQL有两种行版本控制：
--（1）行版本控制的已提交读隔离（read_committed_snapshot）
--（2）直接使用snapshot事务隔离级别

--（1）（read_committed_snapshot）：read_committed_snapshot数据库选项为ON时，read_committed事务通过使用行
--版本控制提供语句级读取一致性

--（2）（snapshot事务隔离级别）allow_snapshot_isolation数据库选项为ON时，snapshot事务通过使用行版本
--控制提供事务级读取一致性


--下列示例可以说明使用普通已提交读事务，行版本控制的快照隔离事务和行版本
--控制的已提交读事务的行为差异

--示例：

--A 普通已提交事务
--在此示例中，一个普通read committed事务将读取数据，然后由另一事务修改此数据。执行
--完的读操作不阻塞由其他事务执行的更新操作。但是，在其他事务已经做了更新操作后，读
--操作会被阻塞住，直到更新操作事务提交为止

--在会话1上：
USE [AdventureWorks]
GO
BEGIN TRAN
--查询1
--这个查询将返回员工有48小时休假时间
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

-----------------------------------------------------------------------------------------------

--在会话2上：
USE [AdventureWorks]
GO
BEGIN TRAN
--修改1
--休假时间减去8
--修改不会被阻塞，因为会话1不会持有S锁不放
UPDATE [HumanResources].[Employee]
SET [VacationHours]=[VacationHours]-8
WHERE [EmployeeID]=4

--查询1
--现在休假时间只有40小时
SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

--------------------------------------------------------------------------------------------------


--在会话1上：
--重新运行查询语句，会被会话2阻塞
--查询2
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

-----------------------------------------------------------------------------------------------

--在会话2上：
--提交事务
COMMIT TRAN
GO

-----------------------------------------------------------------------------------------------

--在会话1上：
--此时先前被阻塞的查询结束，返回会话2修改好的新数据：40
--查询3
--这里返回40，因为会话2已经提交了事务
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

--修改2
--这里会成功
UPDATE [HumanResources].[Employee]
SET [SickLeaveHours]=[SickLeaveHours]-8
WHERE [EmployeeID]=4

SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

--可以回滚会话1的修改
--会话2的修改不会受影响
ROLLBACK TRAN
GO

SELECT [SickLeaveHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
-------------------------------------------------------------------------------------------


--B 使用快照隔离
--此示例中，在快照隔离下运行的事务将读取数据，然后由另一个事务修改此数据。快照事务
--不阻塞由其他事务执行的更新操作，他忽略数据的修改继续从版本化的行读取数据。也就是
--说，读取到的是数据修改前的版本。但是，当快照事务尝试修改已由其他事务修改的数据时，
--他将生成错误并终止

--在会话1上：
USE [AdventureWorks]
GO

--启用快照隔离
ALTER DATABASE [AdventureWorks] SET ALLOW_SNAPSHOT_ISOLATION ON
GO

--设置使用快照隔离级别
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO

BEGIN TRAN
--查询1
--查询返回员工有48小时假期
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4
---------------------------------------------------------------------------------------------

--在会话2上：
USE [AdventureWorks]
GO

BEGIN TRAN
--修改1
--假期时间减8
--修改不会被会话1阻塞
UPDATE [HumanResources].[Employee]
SET [VacationHours]=[VacationHours]-8
WHERE [EmployeeID]=4

--查询1
--确认值已经被改成40
SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

-----------------------------------------------------------------------------------------------

--在会话1上：
--查询2
--再次运行查询语句
--还是返回48（修改前的值），因为会话1是从版本化的行读取数据
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

------------------------------------------------------------------------------------------------

--在会话2上：
--提交事务
COMMIT TRAN
GO

-------------------------------------------------------------------------------------------------

--在会话1上：
--查询3
--再次运行查询语句
--还是返回48（修改前的值），因为会话1还是从版本化的行读取数据
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

--修改2
--因为数据已经被会话2修改过，会话1想做任何修改时
--会遇到3960错误
--事务会自动回滚
UPDATE [HumanResources].[Employee]
SET [SickLeaveHours]=[SickLeaveHours]-8
WHERE  [EmployeeID]=4

--会话1的修改会回滚
--会话2的修改不会回滚
ROLLBACK TRAN
GO

----------------------------------------------------------------------------------------------



--C 使用行版本控制的已提交读
--在此示例中，使用行版本控制的已提交读事务与其他事务并发运行。已提交读事务的行为与快照事务的行为
--有所不同。与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。
--与快照事务不同的是，已提交读将执行下列操作：

--（1）在其他事务提交数据更改之后，读取修改的数据
--（2）能够更新由其他事务修改的数据，而快照事务不能

--在会话1上：
USE [AdventureWorks]
GO
--启用行版本控制的已提交读
--注意运行这句话的时候，不可以有其他连接同时使用[AdventureWorks]
ALTER DATABASE [AdventureWorks] SET READ_COMMITTED_SNAPSHOT ON
GO

--设置使用已提交读隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

BEGIN TRAN
--查询1
--这里将返回初始值48
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

----------------------------------------------------------------------------------------------------

--在会话2上：
USE [AdventureWorks]
GO

BEGIN TRAN
--修改1
--假期时间减8
--修改不会被会话1阻塞
UPDATE [HumanResources].[Employee]
SET [VacationHours]=[VacationHours]-8
WHERE [EmployeeID]=4

--查询1
--确认值已经被修改为40
SELECT [VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

------------------------------------------------------------------------------------------------

--在会话1上：
--查询2
--再次运行查询语句
--还是返回48（修改前的值），因为会话2还没有提交
--会话1是从版本化的行读取数据
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

-------------------------------------------------------------------------------------------------

--在会话2上：
--提交事务
COMMIT TRAN
GO
--------------------------------------------------------------------------------------------------

--在会话1上：
--查询3
--这里和范例B不同，会话1始终返回已提交的值
--这里返回40，因为会话2已经提交了事务
SELECT [EmployeeID],[VacationHours] FROM [HumanResources].[Employee] WHERE [EmployeeID]=4

--修改2
--这里会成功
UPDATE [HumanResources].[Employee]
SET [SickLeaveHours]=[SickLeaveHours]-8
WHERE [EmployeeID]=4

--可以回滚会话1的修改
--会话2的修改不会受影响
ROLLBACK TRAN
GO


------------------------------------------结论-----------------------------------------------------------
--从上面的测试结果可以看到，原先会发生阻塞的两个会话在使用行版本控制的隔离级别后，都不会遇到阻塞了。
--但是两种行版本控制的结果又有不同

--但是行版本控制并不是消除阻塞和死锁的万灵药。在决定使用之前，必须考虑下面两个问题

--（1）最终用户是否接受行版本控制下的运行结果？  行版本控制：数据库级别
--上面的3个测试返回的结果都各有不同。在不同的事务阶段，有的被阻塞住，有的读到的是旧版本值，
--有的读到新版本值。用户期望的行为是什么？他是希望哪怕被阻塞住也要读到最新版本数据，还是
--能容忍读到旧版本数据呢？某些应用程序依赖于读隔离的锁定和阻塞行为，例如生成一个串行的流水号
--之类的操作。改成行版本控制，原先的处理逻辑就不能正常工作了。所以在采用新的隔离级别之前，
--一定要做好测试，确保应用按预期的逻辑运行




--（2）SQL是否能支持行版本控制带来的额外负荷？
--开启了行版本控制之后，SQL会把行版本存放在tempdb里。修改的数据越多，需要存储的信息越多
--对SQL额外的负载就越大。所以如果一个应用要从其他隔离级别转向使用行版本控制，需要做特别
--的测试，以确保现有的软硬件配置能支持额外的负荷，应用程序能够达到相似的响应速度
6）手动创建的生成脚本里面有HONOR_BROKER_PRIORITY OFF 但是自动的没有这个选项
如果 HONOR_BROKER_PRIORITY 数据库选项设置为 ON，则会话优先级中指定的优先级别仅应用于传输队列中的消息。  如果 HONOR_BROKER_PRIORITY 设置为 OFF，则会为放入该数据库的传输队列中的所有消息都分配默认优先级别 5。  优先级别不适用于将消息直接放入同一数据库引擎实例的服务队列中的 SEND。 
 
7）手动创建的生成脚本里面有SET DB_CHAINING OFF 但是自动的没有这个选项
这个找了一篇文章阅读，说的相当详细，但是还没有去实践文章的举例，http://blog.163.com/kele_lipeng/blog/static/81345278201491271433795/

如何启用跨数据库的所有权链

使用 cross db ownership chaining 选项可以为 Microsoft SQL Server 实例配置跨数据库所有权链接。

此服务器选项使您能够在数据库级别控制跨数据库所有权链接，或者允许在所有数据库中启用跨数据库所有权链接：

  • 如果实例的 cross db ownership chaining 关闭（设置为 0），将禁用所有数据库的跨数据库所有权链接。
  • 如果实例的 cross db ownership chaining 打开（设置为 1），将启用所有数据库的跨数据库所有权链接。
  • 可以使用 ALTER DATABASE 语句的 SET 子句为各个数据库设置跨数据库所有权链接。如果正在创建新的数据库，则可以使用 CREATE DATABASE 语句设置新数据库的跨数据库所有权链接选项。
    建议不要将 cross db ownership chaining 设置为 1，除非 SQL Server 实例所驻留的所有数据库都必须参与跨数据库所有权链接，并且您了解此设置隐含的安全问题。有关详细信息，请参阅所有权链。

 

在打开或关闭跨数据库所有权链接之前，请注意下列事项：

  • 只有 sysadmin 固定服务器角色成员能够打开或关闭跨数据库所有权链接。
  • 关闭生产服务器的跨数据库所有权链接之前，应全面测试所有应用程序（包括第三方应用程序）以确保更改不会影响应用程序功能。
  • 如果使用 sp_configure 指定 RECONFIGURE，则在服务器运行时可以更改 cross db ownership chaining 选项。
  • 如果有数据库需要跨数据库所有权链接，建议使用 sp_configure 为实例关闭 cross db ownership chaining 选项；然后使用 ALTER DATABASE 语句打开需要此功能的各个数据库的跨数据库所有权链接。
总结：总的来说，用最开始的脚本语言选位置创建数据库跟可视化界面操作的没什么区别。
posted @ 2016-06-03 18:07  晴天MM  阅读(577)  评论(0)  编辑  收藏  举报