SQLServer 杂记

1、LINQ：Language-Integrated Query（语言集成查询）

例子:  var m = from n in arr where n<50 orderby  n select n;

2、Hierarchyid：SQLServer 2008 中提出的新的数据类型，用于表现树层次的数据结构。存储的值形式为：'/1/1/1/'。

3、SQLServer 2008 之后的版本可以直接操作 XML（查询、修改）。

4、可以一次INSERT 多行： insert into a values ('1','2') , ('a','b') 。

5、日期补全：'${start_date}' 代表开始时间，'${end_date}'代表结束时间

select cast(dateadd(day,a.number,'${start_date}') as date)
from  master..spt_values a
where a.type='P' 
and a.number>=0
and a.number<=datediff(day,'${start_date}','${end_date}')

6、SELECT语句执行顺序：FROM-->WHERE-->GROUP BY -->HAVING-->SELECT-->ORDER BY 。

7、语句中最好每个字段都加上表的别名，方便查看以及数据库执行语句快速找到字段。

8、执行顺序：NOT >AND>OR（可用括号改变运算顺序）

9、UNION 过滤重复行、排序，UNION ALL 不过滤重复行、不排序。

10、delete 一行一行的删除数据、会写入事务日志中，truncate 一次性删除和表有关的所有数据页、不会写入事务日志。

11、master数据库：主数据库，存放所有系统配置、用户名、数据库名称、数据库位置等相关信息。
model数据库：模板数据库，例如可以定义一个其他数据库都使用的表，对model数据库的更改会反应在tempdb数据库中。
msdb数据库：主要存放作业、备份、事务日志传送等信息。
tempdb数据库：临时存储数据，数据库关闭即丢失，启动时新建，所有临时表都被创建在此。

12、一页8KB即8192字节，实际可用空间是8060字节。8个连续的页为一个区，即区大小为64KB。

13、可通过以下语句查看数据库的基本信息：

select * from sys.databases
select * from sys.sysdatabases
select * from sys.database_files

14、修改数据库：
alter database old_name modify name=new_name;
exec sp_renamedb  'old_name','new_name';
alter database db_name set auto_shrink on/off;
15、创建自动计算列：create table aa(a1 int,a2 int,a3 as a1+a2);

16、waitfor：
waitfor delay '00:00:03';   --延迟3秒执行
waitfor time '16:07';   -- 到16：07开始执行
17、异常：
BEGIN TRY
SQL STATEMENT
END TRY

BEGIN CATCH
SQL STATEMENT  
END CATCH

18、分组：
  select year,quarter,month,sum(money) as totalmoney
  from [dbo].[yqm]
  group by 
  grouping sets((year,quarter,month),(year),
  (year,quarter)
  )

19、MERGE语句：
MERGE [INTO] TARGET_TABLE
USING SOURCE_TABLE 
ON MERGE_SEARCH_CONDITION
WHEN MATCHED [AND CONDITION] THEN
DO1
WHEN NOT MATCHED [BY TARGET] [AND CONDITION] THEN
DO2 
WHEN NOT MATCHED BY SOURCE [AND CONDITION] THEN
DO3
OUTPUT DO4
20、Numeric(P,S)：数字类型，其中 0<S<=P<=38，即共存储P位，其中S位小数。例如Numeric(18,6)可存储999999999999.999999，此时如果后面的小数发生进位则会报错。
21、查看数据库中表中字段类型：
SELECT * 
FROM
(select OBJECT_SCHEMA_NAME(b.id) 架构名,a.name 表名,b.id,b.name 字段名,c.name 字段类型,c.length 字段长度 
from sysobjects a,syscolumns b,systypes c 
where a.id=b.id
--and a.name='s_bd_person' 
and a.xtype='U'
and b.xtype=c.xtype
) AS AA
WHERE AA.字段类型='int'
22、批量删除表：

select 'drop table '+SCHEMA_NAME(schema_id)+'.'+name+';' from sys.objects where type ='u';

23、大表批量操作时（重建索引或者批量删除、插入、更新），先收缩下日志文件，防止因虚拟日志单元太多（VLF），导致事务失败回滚时间太长。

dbcc loginfo(dbname); --查看虚拟日志单元数(有多少行就有多少)

24、大表重建或者新建索引时，最好不要在SQL Server Management Studio工具界面上直接执行，生成脚本，在查询窗口里执行。

25、简单日志模式，在执行checkpiont或者backup之后，日志即被截断，此时再执行事务，日志文件大小不变，已有日志空间会被重新利用。