数据库开发规范（SQL SERVER篇） 2012年最新版
第一章 命名规范
1. 命名标志法
使用下面的三种大写标识符约定。
Pascal 大小写
将标识符的首字母和后面连接的每个单词的首字母都大写。可以对三字符或更多字符的标识符使用 Pascal 大小写。例如：
BackColor
Camel 大小写
标识符的首字母小写，而每个后面连接的单词的首字母都大写。例如：
backColor
大写
标识符中的所有字母都大写。仅对于由两个或者更少字母组成的标识符使用该约定。例如：
System.IO
System.Web.UI
可能还必须大写标识符以维持与现有非托管符号方案的兼容性，在该方案中所有大写字母经常用于枚举和常数值。一般情况下，在使用它们的程序集之外这些字符应当是不可见的。
2. 数据库命名
数据库名要求全部使用Pascal命名法
例如：
MFC
MFC53
DataController
3. 数据库月份库、数据表日分库命名规则
<DatabaseName><Month>
<TableName><Day>
DatabaseName按数据库命名要求命名
TableName按数据表命名规则命名
Month, Day要求中间无任何连接符
例如
MFCLOG200301
MFC_log_ClientCheckin20030109
4. 分段数据库分库命名规则
<DatabaseName><Segment>
DatabaseName按数据库命名要求命名
Segment是分段的编号，要求长度一致并且3位或者以上
例如 NIDCHyper021
5. 分段分日期数据库分库命名规则
<DatabaseName><Segment><Day>
DatabaseName按数据库命名要求命名
Segment是分段的编号，要求长度一致并且3位或者以上
Day要求中间无任何连接符
例如
GatheredLog00120110227
MassLog00320110227
6. 表的命名
<SystemName>_<TableType>_<Name>
SystemName为表所属的系统名，此处要求采用Pascal命名法
TableType为数据表的类别，此处要求全部使用小写，在我们的库中有如下几种数据表类型：
tb----------数据表,
stat--------统计表,
dict--------字典表,
sys--------系统信息表,
re----------关系表,
log---------日志表
Name为数据库表的名称，此处要求使用Pascal命名法
例如：
MFC_tb_Unit 场所信息表
MFC_stat_UnitDailyStatus 场所状态日统计表
MFC_re_UserArea 用户地区关系表
MFC_log_Customer 顾客日志表
MFC_dict_Sex 性别字典表
7. 字段命名
字段命名统一使用Pascal标志法，单词中间不用下划线。应尽量使用简短而又能说明字段实际意义的词组组合，为保证不与系统字段重复，应尽量至少使用两个单词。同样含义的字段应尽量使用已有字段的物理名。
例如：
CertificateCode 证件号
CertificateType 证件类别
AlertClassName 报警类别名
8. 存储过程命名
[<SystemName>]<FunctionModule>_<TableName>_<FunctionName>
SystemName是系统名，此处要求使用Pascal命名法，对于跨系统使用的存储过程要求此段，其他非跨系统的存储过程不要求。
FunctionModule为功能模块名，此处要求使用Pascal标志法
TableName为数据库已有表名，命名规则同上面的表命名要求
FunctionName为存储过程的功能说明，此处要求使用Pascal标志法。
常用的功能有：
GetList 取多条记录
GetModel 去单条记录
GetListByCondition 根据Condition条件取单条记录
Add 插入或修改单条记录
Delete 删除记录
Insert 插入单条记录
BatchInsert 批量插入多条记录
BatchUpdate 批量更新多条记录
Update 更新单条记录
例如：
Communication_MFC_re_UnitStatus_GetList
DataAnalysis_NIDC_tb_PersonGroup_Delete
DataAnalysis_MFC_tb_CrimeOnEsc_Add
9. 触发器命名,
TR_<TableName>[_<Operation>]
如果只是针对单个操作类型的触发器，则要求说明操作类型：
例如：
TR_MFC_tb_Argot
TR_MFC_tb_Argot_Insert
10. 索引命名
IX_<TableName>_<ColumnName>
例如：
IX_MFC_log_Customer_EndTime
11. 主键
PK_<TableName>。
TableName同表命名规则
例如
PK_MFC_Log_Customer
12. 外键
FK_<TableName1>_<TableName2>
例如：
FK_MFC_log_Customer_MFC_tb_Unit
13. 缺省值
DF_<TableName>_<ColumnName>
例如：
DF_MFC_log_Customer_UserName
14. 视图的命名用Pascal标志法，和表一致；
<SystemName>_view_<Name>
视图的命名除中间用’view’链接以外与表一致
例如：
MFC_view_Strategy
15. 函数的命名
采用存储过程同样的命名规则
16. 其他数据库对象命名规则
其他数据库对象，比如约定、队列、服务、路由等采用表名相同的命名法。
17. 其他数据库可编程性对象命名
其他数据库可编程性对象采用存储过程相同的命名法。
18. 数据库保留字
不要使用数据库保留字，给数据对象命名；
19. 禁止使用空格
在数据库对象命名时，禁止使用空格。
第二章 常用数据类型
下面是我们再数据库设计中常用的几种数据类型：
|
数据类型 |
类型 |
描 述 |
|
int |
整型 |
int 数据类型可以存储从- 231(-2147483648)到231(2147483 647)之间的整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库里占用4个字节 |
|
bigint |
整型 |
从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据。这种数据类型在数据库里占用8 字节空间 |
|
numeric |
精确数值型 |
numeric数据类型与decimal 型相同（要求在存储过程或其他语句中必须表名数据长度及精度） |
|
datetime |
日期时间型 |
datetime数据类型用来表示日期和时间。这种数据类型存储从1753年1月1日到9999年12月3 1日间所有的日期和时间数据， 精确到三百分之一秒或3.33毫秒 |
|
cursor |
特殊数据型 |
cursor 数据类型是一种特殊的数据类型，它包含一个对游标的引用。这种数据类型用在存储过程中，而且创建表时不能用 |
|
Uniqueidentifier |
特殊数据型 |
Uniqueidentifier数据类型用来存储一个全局唯一标识符，即GUID。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列 |
|
char |
字符型 |
char数据类型用来存储指定长度的定长非统一编码型的数据。当定义一列为此类型时，你必须指定列长。当你总能知道要存储的数据的长度时，此数据类型很有用。例如，当你按邮政编码加4个字符格式来存储数据时，你知道总要用到10个字符。此数据类型的列宽最大为8000 个字符 |
|
varchar |
字符型 |
varchar数据类型，同char类型一样，用来存储非统一编码型字符数据。与char 型不一样，此数据类型为变长。当定义一列为该数据类型时，你要指定该列的最大长度。 它与char数据类型最大的区别是，存储的长度不是列长，而是数据的长度 |
|
nvarchar |
统一编码字符型 |
nvarchar 数据类型用作变长的统一编码字符型数据。此数据类型能存储4000种字符，使用的字节空间增加了一倍 |
|
Nvarchar(max) |
统一编码字符型 |
最多为230–1(1 073 741 823)Unicode字符，占用2×字符数＋2字节的空间 |
|
Varchar(max) |
字符型 |
最多为231–1(2 147 483 647)字符，一般用来定义XML的入参，每字符1字节＋2字节额外开销 |
|
varbinary(max) |
二进制数据类型 |
可变长度二进制数据。 n 的取值范围为 1 至 8,000。 max 指示最大存储大小是 2^31-1 个字节。 存储大小为所输入数据的实际长度 + 2 个字节。 |
第三章 数据库设计规范
1. 三范式
数据库设计中应尽可能遵守三范式。所谓三范式即：
- 没有重复的组或多值的列，这是数据库设计的最低要求。
- 非关键字段必须依赖于主关键字，不能依赖于一个组合式主关键字的某些组成部分。消除部分依赖，大部分情况下，数据库设计都应该达到第二范式。
- 一个非关键字段不能依赖于另一个非关键字段。消除传递依赖，达到第三范式应该是系统中大部分表的要求，除非一些特殊作用的表。
2. 适当的冗余
但是完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。
比如一些日志表的历史统计信息，我们可以通过作业定期在数据库负载较小的凌晨8点对数据日志数据进行统计，并建立冗余的统计表记录下来。
3. 主键
主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。聚集索引对查询的影响是比较大的，这个在下面索引的叙述。在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。
4. 索引
索引分为聚集索引和非聚集索引。
每个数据表只能建立一个聚集索引，聚集索引决定了数据在表中的物理顺序，同时非聚集索引依赖聚集索引存在。每一个非聚集索引B树的页节点都存有对应的聚集索引键。因此聚集索引和非聚集索引的选择应该遵守如下规范：
1) 应尽量选择符合唯一约束的字段建立聚集索引
2) 尽量选择占用空间较小的字段建立聚集索引，一般要求聚集索引小于900字节
3) 根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。同时对数据量比较大的表(>1000行)应结合数据表的使用情况建立非聚集索引以提高数据库查询的反应效率。但是过多的非聚集索引也会影响数据表记录的插入及更新速度，一般要求非聚集索引的个数不超过两位数。因此应该针对各数据表的实际情况设计索引。
4) 若某列的值大部分是a，少数是别的值（如b,c,d…），且经常以该列的其它值(如b,c,d…)为查询条件，可以考虑对(如b,c,d…)建立筛选索引。
5) 把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面，同一索引中的组成列最好不要超过3列。
6) 根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
7) 若表主要用来查询，则可按需要建立索引，若对表操作主要是UPDATE，则尽可能少建索引。
5. 主键与聚集索引的关系
在数据库设计中，我们经常容易混淆主键和聚集索引的关系。因为如果我们建立主键的时候没有特别说明，SQL SERVER会默认在主键上建立聚集索引。同时由于聚集索引同时也是唯一索引，而且主键一般为较小的键。所以我们经常将主键作为聚集索引。但是这并不表示主键和聚集索引等同。
第四章 存储过程编写规范
统一和规范的代码书写风格对保证软件的开发质量、提高团队的开发效率以及将来的维护及其扩展都至关重要。
1. 注释
为了增强可读性及美观性，在存储过程头部和存储过程中间应尽量按照如下演示的存储过程做好注释。
USE [MFC_HOTEL] GO /*------------------------------------ -- 用途：根据用户ID查询辖区场所统计 -- 项目名称： -- 说明：这里对存储过程进行详细说明 -- 时间：2012-09-24 -- 编写者: *** -------------------------------------- -- 修改记录: -- 编号 修改时间 修改人 修改原因 修改标注 -- 001 2012-10-11 *** 这里说明修改原因 001 ------------------------------------ 测试语句 EXEC Web_UnitMange_MFC_tb_Unit_GetTreeList @LocationStatus=2 */ CREATE PROCEDURE [dbo].[Web_UnitMange_MFC_tb_Unit_GetTreeList] ( @GuildIDXML VARCHAR(MAX)=NULL, @LocationStatus INT=0 --0-全部;1-已标注;2-未标注 ) AS BEGIN --存储过程应尽量保持这种缩进风格，增强美观性和可读性 SET NOCOUNT ON --每个存储过程中关闭统计 --这里介绍每个代码块的功能，增强代码可读性 IF ISNULL(@GuildIDXML,'')<>'' BEGIN EXEC sp_xml_preparedocument @Handle OUTPUT, @GuildIDXML /*SELECT GuildID INTO #TempGuildID 修改前的代码段注释保留*/ INSERT INTO #TempGuildID --001 这里标注相应修改的位置 SELECT GuildID FROM OPENXML(@Handle, N'/ROOT/ROW') WITH (GuildID int) EXEC sp_xml_removedocument @Handle END END
|
2. 书写规范
数据库服务器端的触发器和存储过程是一类特殊的文本，为方便开发和维护，提高代码的易读性和可维护性。规范建议按照分级缩进格式编写该文本。
1) 编写存储过程时应遵守以下缩进规则，如下示例
IF 1<>1 BEGIN --每个IF条件后的程序块缩进 SELECT U.[GuildID] --各字段尽量对其 ,U.[UnitCode] --每个查询字段要写明表别名或表名 ,U.[UnitID] ,U.[AreaCode] FROM [MFC_HOTEL].[dbo].[MFC_tb_Unit] U WITH(NOLOCK) INNER JOIN MFC_HOTEL.dbo.MFC_tb_Area A WITH(NOLOCK) ON U.AreaCode = A.AreaCode --JOIN条件缩进增强层次感 WHERE A.IsActive=0 --FROM,JOIN,WHERE对齐 END ELSE RETURN
2) 不要使用SELECT * 需要哪些字段，查询哪些字段, 尽可能少的返回结果集行的数量。
3) 在多表关联时，列名前需要加上别名（或表名），表名前加Owner（dbo）。如果涉及到跨数据库，就需要加上数据库名称。
例如：AdventureWorks.dbo.Contact；存储过程也一样；
4) SQL保留字要大写
对SQL的保留字，都需要大写。
例如：SELECT，UPDATE，INSERT，WHERE，INNER JOIN，AND，OR等。
5) 过多使用GOTO语句会使得代码可读性降低
6) 查询列表和条件中的字段全部需要指定所属的表，可以使用表名别名简化。表名别名要简短，但意义要尽量明确，避免使用A、B、C等过于简单的别名。通常，使用大写的表名作为别名，使用 AS 关键字指定表或字段的别名。
3. 性能相关
1) Where子句尽量避免使用函数；
2) 避免在ORDER BY子句中使用表达式；
3) 限制在GROUP BY子句中使用表达式；
4) 慎用游标；
5) 避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’；
6) 查询语句一定要有范围的限定，避免全表扫描操作；
7) 慎用DISTINCT关键字；
8) 慎用OR关键字，可以用UNION ALL替代；
9) 除非必要，尽量用UNION ALL而非UNION
10) 使用EXISTS(SELECT 1)替count(*)来判断是否存在记录；
11) SET NOCOUNT ON 语句
把 SET NOCOUNT ON 语句放到存储过程和触发器中，作为第一句执行语句。例如：
CREATE PROCEDURE [dbo].[UP_GetOrgChildren] AS BEGIN SET NOCOUNT ON ...... 关闭数据库提示输出。
4. 尽量使用索引
1) IN/OR子句使用
IN、OR、NOT IN 应尽量避免使用，这可能会导致SQL SERVER不使用索引而选择全表扫描，可以索引查找的，可以正常使用。
2) !=或<>操作符子句使用
!=或<>操作符应尽量避免使用，可以用索引查找的，可以正常使用。
3) 不要对索引字段进行运算
例如： SELECT ID FROM T WHERE NUM/2=100 应改为: SELECT ID FROM T WHERE NUM=100*2 SELECT ID FROM T WHERE NUM/2=NUM1 如果NUM有索引应改为: SELECT ID FROM T WHERE NUM=NUM1*2
如果NUM1有索引则不应该改。
4) 不要对索引字段进行格式转换
日期字段的例子： WHERE CONVERT(VARCHAR(10),日期字段,120)='2008-08-15' 应该改为 WHERE 日期字段>='2008-08-15' AND 日期字段<'2008-08-16'
5) 不要对索引字段使用函数
日期查询的例子： WHERE LEFT(NAME, 3)='ABC' 或者 WHERE SUBSTRING(NAME,1, 3)='ABC' 应改为: WHERE NAME LIKE 'ABC%' 日期查询的例子： WHERE DATEDIFF(DAY, 日期,'2005-11-30')=0 应改为:WHERE 日期>='2005-11-30' AND 日期<'2005-12-1' WHERE DATEDIFF(DAY, 日期,'2005-11-30')>0 应改为:WHERE 日期<'2005-11-30' WHERE DATEDIFF(DAY, 日期,'2005-11-30')>=0 应改为:WHERE 日期<'2005-12-01' WHERE DATEDIFF(DAY, 日期,'2005-11-30')<0 应改为:WHERE 日期>='2005-12-01' WHERE DATEDIFF(DAY, 日期,'2005-11-30')<=0 应改为:WHERE 日期>='2005-11-30'
6) 不要对索引字段进行多字段连接
例如： WHERE FAME+'.'+LNAME='H.Y' 应改为: WHERE FNAME='H' AND LNAME='Y'
7) Like的使用
对索引列避免使用like ‘%xx’， 应该使用like ‘xx%’。设计数据结构时就应该考虑这个问题，不要出现必须要采用like ‘%xx’才能满足业务需要的情形。
5. 事务和锁
事务是数据库应用中和重要的工具，它有原子性、一致性、隔离性、持久性这四个属性，很多操作我们都需要利用事务来保证数据的正确性。在使用事务中我们需要做到尽量避免死锁、尽量减少阻塞。具体以下方面需要特别注意：
1) 使用NOLOCK提示查询优化器
在繁忙的系统中，对改善并发问题，是个不错的选择；
2) 在存储过程，触发器，以及SQL 簇中，尽可能按照相同的循序来访问相关的表。这样可以减少死锁的机会；
3) 事务尽可能短
4) 在事务中涉及到数据修改量，尽可能小，提高事务中每个语句的效率，利用索引和其他方法提高每个语句的效率可以有效地减少整个事务的执行时间。
5) 事务操作过程不应该有交互，因为交互等待的时候，事务并未结束，可能锁定了很多资源。
6) 尽可能低的设置锁，以及隔离的级别。
7) 尽量不要指定锁类型和索引，SQL SERVER允许我们自己指定语句使用的锁类型和索引，但是一般情况下，SQL SERVER优化器选择的锁类型和索引是在当前数据量和查询条件下是最优的，我们指定的可能只是在目前情况下更有，但是数据量和数据分布在将来是会变化的。
6. 其他注意事项
1) 在相关表存在的数据库下创建存储过程和函数
2) 有设置默认值限制的字段不允许设置为可以为空
3) 合理对大表进行分区
4) 视图嵌套使用不能超过3层
5) 对数据量比较大的日志表，应按日期，ID段分库分表
7. 注意临时表和表变量的用法
在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：
1) 如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。
2) 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。
3) 如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。
4) 其他情况下，应该控制临时表和表变量的使用。
5) 关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。
6) 临时表使用CREATE TABLE + INSERT INTO的方式
8. 注意子查询的用法
子查询是一个SELECT查询，它嵌套在SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会形成一个性能瓶颈。如果子查询的条件中使用了其外层的表的字段，这种子查询就叫做相关子查询。相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入。
关于相关子查询，应该注意：
1) NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法。
例如：
SELECT BEA.[AddressID] ,BEA.[AddressTypeID] FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK) WHERE BusinessEntityID NOT IN (SELECT BusinessEntityID FROM [AdventureWorks2012].[Person].[BusinessEntity] WITH(NOLOCK))
可以改写成
SELECT BEA.[AddressID] ,BEA.[AddressTypeID] FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK) LEFT JOIN [AdventureWorks2012].[Person].[BusinessEntity] BE WITH(NOLOCK) ON BEA.BusinessEntityID = BE.BusinessEntityID WHERE BE.BusinessEntityID IS NULL
2) 如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替。
SELECT BEA.[AddressID] ,BEA.[AddressTypeID] FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK) WHERE BusinessEntityID IN (SELECT BusinessEntityID FROM [AdventureWorks2012].[Person].[BusinessEntity] WITH(NOLOCK))
可以改写成：
SELECT BEA.[AddressID] ,BEA.[AddressTypeID] FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK) INNER JOIN [AdventureWorks2012].[Person].[BusinessEntity] BE WITH(NOLOCK) ON BEA.BusinessEntityID = BE.BusinessEntityID
3) 不要用COUNT(*)的子查询判断是否存在记录，最好用LEFT JOIN或者EXISTS
SELECT BEA.[AddressID] ,BEA.[AddressTypeID] FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK) WHERE (SELECT COUNT(*) FROM [AdventureWorks2012].[Person].[BusinessEntity] WITH(NOLOCK))=0
可以改写成：
SELECT BEA.[AddressID] ,BEA.[AddressTypeID] FROM [AdventureWorks2012].[Person].[BusinessEntityAddress] BEA WITH(NOLOCK) LEFT JOIN [AdventureWorks2012].[Person].[BusinessEntity] BE WITH(NOLOCK) ON BEA.BusinessEntityID = BE.BusinessEntityID WHERE BE.BusinessEntityID IS NULL
9. 常用写法
9.1. XML解析
CREATE TABLE #Temp ( FieldName nvarchar(50), FieldValue nvarchar(256), Memo nvarchar(100) ) DECLARE @docHandle int EXEC sp_xml_preparedocument @docHandle OUTPUT, @Xml INSERT INTO #Temp(FieldName,FieldValue,Memo) SELECT FieldName, FieldValue, Memo FROM OPENXML(@docHandle, N'/ROOT/ROW') WITH ( FieldName nvarchar(50), FieldValue nvarchar(256), Memo nvarchar(100) ) EXEC sp_xml_removedocument @docHandle
9.2. 检查表是否有数据
IF EXISTS(SELECT 1 FROM #Temp)
9.3. 检查变量是否为空或为’’
IF ISNULL(@Input,'')<>''
9.4. 动态SQL
--不带输出参数值的写法 DECLARE @SQL NVARCHAR(MAX),@Input INT SET @Input=1 SET @SQL=N' SELECT UnitCode FROM dbo.MFC_tb_Unit WITH(NOLOCK) WHERE UnitID='+CONVERT(NVARCHAR(8),@Input) EXEC(@SQL) --带输出参数值的写法 DECLARE @SQL NVARCHAR(MAX),@Input INT,@Output NVARCHAR(20) SET @Input=1 SET @SQL=N' SELECT @Output=UnitCode FROM dbo.MFC_tb_Unit WITH(NOLOCK) WHERE UnitID=@Input ' EXEC sp_executesql @SQL,N'@Input INT,@Output NVARCHAR(20) OUTPUT',@Input,@Output OUTPUT PRINT @Output
9.5. 建表
CREATE TABLE [dbo].[NB_re_RoleDepartment]( [RoleID] [int] NOT NULL, [DepartmentID] [int] NOT NULL, CONSTRAINT [PK_NB_RE_ROLEDEPARTMENT] PRIMARY KEY CLUSTERED ( [RoleID] ASC, [DepartmentID] ASC )WITH (IGNORE_DUP_KEY = OFF,DATA_COMPRESSION = PAGE) ON [PRIMARY] ) ON [PRIMARY] 这其中DATA_COMPRESSION = PAGE页压缩选项在SQL SERVER2008或之后的版本才能使用
9.6. 建索引
CREATE NONCLUSTERED INDEX [IX_MFC_tb_Process_UserID] ON [dbo].[MFC_tb_Process] ([UserID] DESC)WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY] 这其中DATA_COMPRESSION = PAGE页压缩选项在SQL SERVER2008或之后的版本才能使用
9.7. 建用户
--创建数据库用户sdfsa IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sdfsa') CREATE LOGIN [sdfsa] WITH PASSWORD=N'123214', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[简体中文], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC sys.sp_addsrvrolemember @loginame = N'sdfsa', @rolename = N'sysadmin' GO
9.8. 建全文索引
BEGIN TRY -- 屏蔽全文错误 --建全文索引目录 IF NOT EXISTS(SELECT name FROM sys.fulltext_catalogs WHERE name = 'ChatQQ20121018') -- 全文目录不存在 AND EXISTS (SELECT 1 FROM sys.tables WHERE name = 'NIR_log_ChatQQ20121018') -- 表存在 BEGIN CREATE FULLTEXT CATALOG ChatQQ20121018 WITH ACCENT_SENSITIVITY = ON AUTHORIZATION [dbo] END; --建全文索引 IF NOT EXISTS(SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id('NIR_log_ChatQQ20121018')) -- 全文索引不存在 AND EXISTS (SELECT 1 FROM sys.tables WHERE name = 'NIR_log_ChatQQ20121018') -- 表存在 AND EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = 'ChatQQ20121018') -- 全文目录存在 BEGIN CREATE FULLTEXT INDEX ON NIR_log_ChatQQ20121018([Content]) KEY INDEX PK_NIR_log_ChatQQ20121018 ON ChatQQ20121018 END END TRY BEGIN CATCH END CATCH SQL SERVER 2008及以后全文索引目录是一个虚拟的概念，不需要制定PATH
9.9. 建链接服务器
-- 增加链接服务器 exec sp_addlinkedserver 'MFC208', ' ', 'SQLOLEDB ', '192.168.1.27' --MFC208是链接服务器的数据库逻辑名(别名) -- 增加链接服务器关联登录用户 exec sp_addlinkedsrvlogin 'MFC208 ', 'false ',null, 'asdf', '654561' --MFC208是链接服务器关联到远程的用户asdf,密码是654561
9.10. SERVICE BROKER
USE MFC GO --建立消息类型 CREATE MESSAGE TYPE [XMLMessageType] VALIDATION = WELL_FORMED_XML GO --建立约定 CREATE CONTRACT [XMLMessageContract] ([XMLMessageType] SENT BY INITIATOR) GO --建立队列 CREATE QUEUE [dbo].[Queue_Argot] WITH STATUS = ON , RETENTION = OFF ON [PRIMARY] GO --建立服务 CREATE SERVICE [Service_Argot] ON QUEUE [dbo].[Queue_Argot] ([XMLMessageContract]) GO --建立表删除新增触发器,并发送消息到队列 CREATE TRIGGER [dbo].[TR_MFC_tb_Argot] ON [dbo].[MFC_tb_Argot] FOR INSERT,DELETE AS BEGIN IF @@ROWCOUNT=0 RETURN SET NOCOUNT ON -- 将要发送的数据生成xml 数据 DECLARE @message xml IF EXISTS ( SELECT 1 FROM INSERTED ) BEGIN SET @message = ( SELECT Operation = 'INSERTED' ,ArgotName AS KeyWordID FROM INSERTED FOR XML RAW('ROW') , ROOT('ROOT') ) END IF EXISTS ( SELECT 1 FROM DELETED ) BEGIN SET @message = ( SELECT Operation = 'DELETED' ,ArgotName AS KeyWordID FROM DELETED FOR XML RAW('ROW') , ROOT('ROOT') ) END DECLARE @handle uniqueidentifier BEGIN DIALOG CONVERSATION @handle FROM SERVICE [Service_Argot] TO SERVICE N'Service_Argot' ON CONTRACT XMLMessageContract WITH ENCRYPTION = OFF; SEND ON CONVERSATION @handle MESSAGE TYPE XMLMessageType(@message); -- 消息发出即可, 不需要回复, 因此发出后即可结束会话 --END CONVERSATION @handle END
9.11. 分区
--创建分区函数 IF NOT EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = N'MFCPartitionFunction') CREATE PARTITION FUNCTION [MFCPartitionFunction](bigint) AS RANGE FOR VALUES (-7378697629483820647, -5534023222112865486, -3689348814741910325, -1844674407370955164, -3, 1844674407370955158, 3689348814741910319, 5534023222112865480, 7378697629483820641, 8378697629483820641) GO --创建分区方案 IF NOT EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = N'MFCPartitionScheme') CREATE PARTITION SCHEME [MFCPartitionScheme] AS PARTITION [MFCPartitionFunction] TO ([2005P1], [2005P2], [2005P3], [2005P4], [2005P5], [2005P6], [2005P7], [2005P8], [2005P9], [2005P10], [PRIMARY]) GO IF NOT EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = N'MFCPartitionSchemeInd') CREATE PARTITION SCHEME [MFCPartitionSchemeInd] AS PARTITION [MFCPartitionFunction] TO ([PRIMARY], [2005P10], [2005P9], [2005P8], [2005P7], [2005P6], [2005P5], [2005P4], [2005P3], [2005P2], [2005P1]) GO
