MSSQL2008 临时总结文档2014

2. 索引可以建立在基表上（基表索引），也可以视图上称为视图索引。

基表索引是建立在表数据中的，故视图索引是建立在查询后的结果集上的。

（假如建立了视图索引）当从视图中取数据的时候，优化查询分析器会根据实际情况判断如果使用视图索引性能较小，那么就使用视图索引。

默认，如果视图定义的列在基表中已经定义了索引，那么即使不显示定义视图索引，也会有视图索引可以使用。

聚索引似书的页码，非聚簇索引类似目录。

1）主键默认就是聚簇索引Cluster。索引直达数据页。索引项的数据和数据项的数据排序完全一致。一个表只能有一个。

2）主键的重要性，一般不放在递增列上

3）聚簇索引排序的是物理结构，所以可以快速找到。比如查询某个日期开始的到某日期范围内的。通过上一个数据就可以连续取出后面的几条记录。

非聚簇索引，必须要费点劲。

聚簇索引和非聚簇索引都可以排序，具体哪个速度快要视表

字典里拼音排序就是簇簇索引。

4）

唯一索引可以建立在聚簇和非聚簇上

一个表只能建一个聚簇索引,因此不能随便浪费;

聚簇索引是非常宝贵的,因此一般都不要在自增主键上建聚簇索引

可以建立非聚簇主键索引

创建index时候，有full text 和xml选项

主键不能为空，不能重复

唯一键可以为空，不能重复

4.完整性约束：级联操作

1）级联删除，级联更新。指的是被引用键和外键的关系。

级联操作：当用户视图更新或者删除现有外键指向的键时，sql server执行的操作

2）定义:create table 或者 alter table 中的 references 中建立

3）on delete [on action|cascade|set null|set default]

on update…

如果没有指定 ON DELETE 或 ON UPDATE，则默认为 NO ACTION

on action 试图删除或者更新某行数据，但是改行的某个键在其他表的现有行中被引用。则提示错误，并回滚。

on cascade 被引用键所在的行被删除或者更新，则外键所在的行数据也被删除或者更新。（注意多级级联操作。。）

set null ….，外键所在的行设置为空，前提此列可以设置为空

set default ….,外键所在的行设置默认值为此列类型的默认值。

4）如果 timestamp 列是外键或被引用键的一部分，则不能指定 CASCADE。

5）多个级联引用操作。

表A中id列被表b中现有行的id引用（并且定义时候使用 on delete cascade），此时b的主键又被表C中的XX列引用（并且表c中定义外键定义的是 on delete cascade），则删除A某行，那么B

中关联的B中数据也删除，同样和b关联的数据也都被删除。

5. 外键，被引用键的说法。 A中id列，被B中cd列引用，则cd叫外键，A中的id叫被引用键

6.大多数使用联接的查询可以用子查询（嵌套在其他查询中的查询）重写，并且大多数子查询可以重写为联接

7.MSSQL数据先写到事务日志，不是实时写到数据库。数据库会在检查点查看事务日志，这才更新到数据库中。日志中修改记录都是顺序的，所以可以回滚

8.元数据就是数据的描述信息

9.触发器可与视图关联，约束不行。触发器用在业务涉及到多个变。

10.自定义函数和存储过程区别。

1）两者相似

2）自定义函数只能完成其范围内的职责，无副作用。例：不能在自定义函数内更改表，更改数据库参数，发送电子邮件等！

3）函数参数是值传递

4）不能返回text,ntext等类型，可以返回表数据类型

11. null 设置在列上

定义null要注意。注意其表达的意思。注意：null和0，什么时候用null。

例：表中某列代表销售增长率。 2012年为第一年销售，所以增长率为0.那么2013年的增长率应为null，而不是0，因为如果为0，就说明没增长，那么意思去年也有收入了。所以此列应该允许为null。

12.查询优化分析器

1）查询优化分析器会综合查询条件和索引和数据库当前性能，寻找最佳查询方式。会在返回结果和影响别的用户情况下权衡。

2）显示估计查询计划，实际查询计划

注意：大多数情况估计和实际计划相同。

13. 联接

内连接 inner join 例：select * from A inner join b on x=y or select * from A ,B where A.x=B.x

外连接： left (outer) join , right (outer)join on xx.xx=yy.yy, full (outer) join

14. any,some,all,in ,exists

1)in 和 exists

select * from administrator where adminid in (select * from administrator where adMinid>0)
select * from administrator where exists (select adminid from administrator where adMinid>0) --注意 exists 后面的子查询只能是一列

2)any 和some

some要求查询条件必须大于或者小于或者不等于子查询的所有给定的列值

例： select * from tableA where NumberOfDays < SOME ( select NumberOfDays from xx where NumberOfDays beteween xx and xx)

any 只要满足一个条件就可以执行

例：例： select * from tableA where NumberOfDays =any ( select NumberOfDays from xx where NumberOfDays beteween xx and xx)

注意：scalar_expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } { SOME | ANY } ( subquery )

15.

嵌套查询. 内查询先查出，然后当作条件送到外面的查询例：select * from A where id in (select id from b where id>xx)
相关子查询信息双向进行的(比如同一个表关联)

select * from A as aa where id in (select id from a as bb where aa.id=bb.id )

外先查询id，然后再转到内查询（后面括号里的查询），然后再转到外查询

16. 交集，差集，合并

1）union 合并结果集，不包含重复的。合并的结果集要求对应的列名都一致

union all 包含重复的

2) except 差集（最后结果集中有重复的去重复的）。在左表中取出那些不在右表中的数据。

intersect 交集(最后结果集中有重复的去重复的)

3)exists 也可以实现交集

not exists 可以实现差集

注意:几乎 exists和not exists 性能大于等于 except ,intersect

>,<, <> ，>=,<=是ISO标准

!= ，!>,!< 非iso标准

is null ,is not null

like ‘%f

18. SQL SERVER 对XML的支持

1）列类型可以为xml类型

2）通过xquery 对xml类型列中数据特定的节点或者属性的值进行查询

3) xml模式集合 xml schema collection，检查数据是否符合自我定义的规范。

xml模式定义，以及使用

xquery 的扩展xml数据操纵语言 xmldml
新加了3条指令 insert,delete,replace value of，区分大小写

5).nodes可以获取xml块

注意sql模式(关系数据库模式)和xml模式区别
xquery 方法是只读操作，不能更改

19. sql server 数据存储单元

1)区段是表和给定文件中索引分配存储空间的基本存储单元

8个64kb连续的页组成

注：本区多余的一条记录将放到另外一个区中，此条记录占用的是整个区（不是此记录本身存储大小）。

基于区段而不是实际使用的空间来分配空间的概念。但这浪费空间的总量通常并不多，只占整个数据库空间的小百分比。

2)页是真正达到真正的数据行的最后一个级别。

区段中的数目是固定的，但页中存储的行数是不固定的，取决于行的大小，行不允许夸页。

通常行的大小可以达到8kb
行的大小限制为8060个字符，行中最大列数限制为1024列
当某行某列存放的数据若为text,image,varchar max,varbinary max,则此列的值存放在别处，而本行本列的值存的是实际存放的地址信息。

注：页拆分，聚簇索引不会拆分页

20.全文目录与存储单元无关，而是全文索引的逻辑分组

文件流

21. SQL中排序

1) 二进制排序

速度快，但基本不用，因为违背人类思维。原理每个字符都是一串数字。

2）字典排序

类似字典中排序，但是有改变。

可设 “区分大小写”,”区分重音”,以及其他字符集选项。

注：1. 每种语言都有自己默认的字典排序

2.排序规则，影响数据是否相等，也影响在索引中存储的方式。

22. 聚合函数_ group_by_expression

1）意思：按照 groupby_expression的表达式进行分组

select <selectlist> from t/view group by expressionn

注意：expression 表达式必须完全来自

2) group by xx with rollup/cute

group by xx roolup/cute(a,b,c)

注意：group by 有ISO和非iso的语法，同时只能用一种语法样式。（对新代码一律使用ISO语法，支持非iso语法为了以前代码能向后兼容）

(msdn帮助文档中列出了哪些版本支持哪些指令，如sql2005不支持with rollup等模式，2008某版本支持)

例：（下面的例子rollup()，但是本机器sql2008 模式太低，无法运行）

生成简单的 GROUP BY 聚合行以及小计行或超聚合行，还生成一个总计行。
SELECT a, b, c, SUM ( <expression> )
FROM T
GROUP BY ROLLUP (a,b,c)
--
消息 10707，级别 15，状态 1，第 3 行
当前兼容模式下不允许使用 CUBE() 和 ROLLUP() 分组构造。只有 100 或更高模式下才允许使用这些构造。

注意：group by 中的讲解的例子数据都来自administrator表，adminid为主键，所有数据如下图

例1：使用 with rollup

SELECT adminid,[username],[userpwd] FROM administrator
GROUP BY adminid,username,userpwd WITH rollup --用with rollup 结果集显示10条

例2：使用 with cube

SELECT adminid,[username],[userpwd] FROM administrator
GROUP BY adminid,username,userpwd WITH CUBE (19条数据)

3）表达式和selectlist的规定

*group by表达式可以包含 from中的表，派生表或者视图的列

*select 列表中任何非聚合表达式中的每个表的列或者视图的列都必须包含在 group by后面的表达式中。也就是若select出的列，没经过聚合函数处理，则必须都放在group by后面的表达式里。

（同样不能用*号）

*不能在 group_by_expression 中使用类型为 text、ntext 和 image 的列。

*group by 中的列名不能为 select中的别名列

例:不能使用* （错误的例子）

use dblifeshop
go
select * from administrator group by adminid

消息 8120，级别 16，状态 1，第 1 行
选择列表中的列 'administrator.userName' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

例子：（允许的做法，即基于选择出来的列，再去考虑group by后面的表达式。）

select ColumnA, ColumnB FROM TABLEA GROUP BY ColumnA, ColumnB

select ColumnA+ColumnB from tablea group by ColumnA,ColumnB

group by ColumnA+ColumnB

select ColumnA+ColumnB+ constant from t group by ColumnA,ColumnB //constant 为常量，恒定不变的，非列

例：不允许（多注意这个）

select ColumnA, ColumnB FROM TABLEA GROUP BY ColumnA+ColumnB

select ColumnA+ constant+ColumnB from t group by ColumnA+ColumnB

,例：

--此句错的
select adminid,username from administrator group by adminid
--对
select adminid,len(username) from administrator group by adminid,username
--adminid int类型，和username nvarchar进行相加，则必须将int转换成nvarchar。且group 表达式必须有adminid,username缺一不可的。
select adminid+username from administrator group by adminid,username

22.聚合函数

聚合函数对一组值执行计算，并返回单个值。除了 COUNT 以外，聚合函数都会忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用

T-sql提供12个聚合函数

大小 max,min; 平均求和 avg,sum;数目 count

,另外可以自定义聚合函数

每个数据库，每个表，表上的列，列上的索引都有一个对应的objectid.

Sysindexes 存储了索引id，可以找到根节点。

接着，根节点上面是排好序的列值，可以导向到非页级节点。

非也级节点存放：列的值,具体的物理地址（如：区段，页，行号偏移量）

http://www.jb51.net/article/29693.htm

1.给变量赋值一个查询到的结果,查询语句应该用括号,否则报错
对的SET @i=(SELECT COUNT(*) AS counts FROM dbo.Articles)
错的SET @i=SELECT COUNT(*) AS counts FROM dbo.Articles

另这种写法没解决，得通过 exec sp_executesql 才能执行，因为sql字符串中有变量
DECLARE @strs NVARCHAR(1000)
DECLARE @i int
SET @strs='SELECT @i= COUNT(*) FROM dbo.Articles'
exec (@strs)
print @i --打印为空，因为@i未被赋值

EXEC 执行拼接sql语句的时候不支持嵌入式参数
exec在执行时，又重新开辟了一段堆栈来执行EXEC内的内容!
@TempTable没有在EXEC的有效存储空间中！

1. FLOOR(numeric_expression)返回小于或等于数值表达式的最大整数。

print floor (1.4) ,结果是1

print floor (1.8),结果也是1

2. CEILING(numeric_expression) 返回大于或等于数值表达式的最小整数。

print CEILING (1.4) ,结果是2

print CEILING(1.8),结果也是2

3.标量函数function中不能使用可变的对象。比如使用 rand函数

可以定义个view产生随机数，然后在function函数中调用view。

create view v_random
AS
select rand() as random
go

函数中使用：

create function

select @randomInt= random FROM v_random

函数的定义，函数中不能使用可变对象，函数可以返回表类型但是必须批处理语句最后一个是return 语句

临时表：本地临时表和全局临时表。tempdb. 本能临时表（#开头）只针对当前用户或者会话，用不到了或者断开连接或者drop则清除了表。而全局临时表（##开头），对任何用户都可见的，在所有使用此表的用户

全部断开连接后才能被从系统中删除。临时表定义后可以修改，这是与表变量最大的区别吧。

表变量:本地和全局。内存。 @和@@开头。@@一般都是系统的全局变量，像我们常用到的，如 @@Error代表错误的号，@@RowCount代表影响的行数。表变量是在内存中，无日志。表变量不能有非聚簇索引。

不能有default和约束；表变量上的统计信息是不可靠的。表变量无锁机制。不用drop，断开连接或模块执行完就不存在了。

表变量对内存有要求，数据量小且不需要优化可以使用。当数据量大，要优化数据量的时候要使用临时表，临时表存在tempdb上也就是物理磁盘，你懂的哦。

若要改善性能，请在语句字符串中使用完全限定对象名。

5. 表变量不能作为存储过程的输入参数。

EXEC sp_executesql和exec 区别，前者可以执行一段sql拼接，然后带入参数执行。

//@sqlstr要执行的sql命令,参数2是定义变量，3是为变量初始化初始值

EXEC sp_executesql @sqlStr,N'@变量1 int,@变量2 int,@变量3 int',@变量1=xx

//不能为table类型.

exec 执行t-sql批中的命令字符串，字符串，或者（后面的模块）系统存储过程，自定义存储过程，扩展存储过程，标量值用户自定义函数（可以返回table）
1）验证 sql字符串，防注入
WITH RECOMPILE
执行模块后，强制编译、使用和放弃新计划。如果该模块存在现有查询计划，则该计划将保留在缓存中。

运行时检查
1）执行 sp_executesql或者exec之前，不缓存计划，且不检查和分析错误。执行时，才对参数进行检查

6. sql字符串拼接 int类型要转换成nvarchar类型

cast (@变量 as nvarchar)

convert(nvarchar, @变量)

5. datetime 类型（另外还有datetime2类型）

PRINT GETDATE() --03 29 2014 2:36PM
PRINT SYSDATETIME() --2014-03-29 14:36:29.2702587

6. 完全限定符数据库名.表名

SELECT * FROM master.dbo.Msreplication_options master数据库

*存储过程调试。可以用vs调试，右击存储过程名字，可以显示输入参数值

7.newid() 创建唯一类型

create table mytable
(
customerid uniqueidentifier NOT NULL
DEFAULT newid()

)

order by newid() 原理就是每扫描到一条记录，都产生这个唯一类型，每行一个，扫完全表，然后排列。

8.RAISERROR（信息,安全级别，状态）

安全级别：任何用户都可以指定0到18之间的级别，只有sysadmin才可以19-25级别。从小到大，越来越高。

状态：0 到 255 的整数。负值或大于 255 的值会生成错误。如果在多个位置引发相同的用户定义错误，则针对每个位置使用唯一的状态号有助于找到引发错误的代码段。

IF( ISNUMERIC(@dataCount)=0    )    --为1则为数字，为0则不是
     BEGIN
          RAISERROR('datacount必须为整数',1,1)
         RETURN
     END

datacount必须为整数
消息 50000，级别 1，状态 1

9.ISNULL ( check_expression , replacement_value ) 使用指定的替换值替换 NULL。

ISNUMERIC(@dataCount)=0 为1为合法数值，合法的数值类型如下