SQL SERVER 查询语句备忘录
分隔标识符
查看指定会话 quoted_identifier 是开启还是关闭状态：
1 select quoted_identifier 2 from sys.dm_exec_sessions 3 where session_id = @@SPID --查看当前会话
设置当前会话的 quoted_identifier 状态：开启时，双引号中的内容代表对象，如表、列等；关闭后，双引号中的内容代表字符串
set quoted_identifier on/off
查找一个物理页面文件号和页编号的三种方法：
1、使用未记录视图：sys.system_internals_allocation_units
select a.first_page from sys.partitions p join sys.system_internals_allocation_units a on p.partition_id = a.container_id where p.object_id = object_id('t')
以上查询得到表 ‘t’ 的fisrt_page：0x160200000100，然后再将其转换为文件和页面地址。首先将其转换（字节逆序），得到：0x 00 01 00 00 02 16 ，前两组（字节）表示文件编号，后4组表示页编号。
因此，文件编号为：0x0001（即1），页编号为：0x00000216（即534）。
2、使用非正式文件命令：DBCC IND。如：
dbcc ind(test,t,-1)
该语句查询数据库“test”，表“t”的相关信息，其中PageType = 1（表示该页是一个数据页）的行的前两列即为所需的文件、页编号。
3、使用为记录文件函数：sys.fn_PhysLocFormatter(%%physloc%%)
select sys.fn_PhysLocFormatter(%%physloc%%) as rid,* from t
查看页面内容：
通过上面的方法获取到文件号（1），页编号（534）后，通过 DBCC PAGE命令来查看该页的具体内容：
dbcc traceon(3604) --开启该跟踪标志后，dbcc page命令才会返回结果 dbcc page(test,1,534,1) --查看数据库test，文件号1，页号534的页面
DBCC PAGE 命令语法：DBCC PAGE （{dbid | dbname}，filenum，pagenum [，printopt ]）
NULL在可变长度列中的存储
首先创建测试表和数据：
create table dbo.null_varchar ( id int primary key identity(1,1), c1 varchar(10) null, c2 varchar(10) null, c3 varchar(10) null, c4 varchar(10) null, c5 varchar(10) null, c6 varchar(10) null, c7 varchar(10) null, c8 varchar(10) null, c9 varchar(10) null, c10 varchar(10) null ) go set nocount on insert into null_varchar(c10) select 'a'; insert into null_varchar(c1) select 'b'; insert into null_varchar select '','','','','','','','','','c'; insert into null_varchar select 'd','','','','','','','','',''; go select * from null_varchar
查询结果如下：
查看页面的实际内容：
dbcc ind(test,null_varchar,-1) --获取文件号、页号 dbcc traceon(3604) dbcc page(test,1,544,1)
结果如下：
由第一行和第三行对比，第二行和第四行对比可以发现：
1、NULL和空字符串的存储是一样的（除了NULL位图不同）。
2、如果NULL或空字符串在末尾，则“可变长度列的列数量”不包含这些列的数量，列偏移阵列也没有这些列的偏移指示。
备注：对于固定长度列，NULL存储为0，然后由NULL位图指示其为NULL
日期和时间
准备测试语句：
if object_id('times','u') is not null drop table times; go create table times ( a char(1), dt1 datetime, b char(1), sd smalldatetime, c char(1), dt2 datetime2, d char(1), dt date, e char(1), dto datetimeoffset, f char(1), t time, g char(1), t0 time(0), h char(1), t1 time(1), i char(1), t2 time(2), j char(1), t3 time(3), k char(1), t4 time(4), l char(1), t5 time(5), m char(1), t6 time(6), n char(1), t7 time(7) ); go insert into times select 'a','01:02:03.123', 'b','01:02:03.123', 'c','01:02:03.123', 'd','01:02:03.123', 'e','01:02:03.123', 'f','01:02:03.123', 'g','01:02:03.123', 'h','01:02:03.123', 'i','01:02:03.123', 'j','01:02:03.123', 'k','01:02:03.123', 'l','01:02:03.123', 'm','01:02:03.123', 'n','01:02:03.123' go
结果如下：
从以上图中可以得出以下结论：
1、对于datetime 和 smalldatetime 来说，日期部分存储的是给定日期到1900-01-01的天数；对于date、datetime2以及datetimeoffset来说，存储的是给定日期到 0001-01-01 的天数。
对于没有给定日期部分的日期时间数据来说，所有数据类型（datetime、smalldatetime、date、datetime2、datetimeoffset）均解释为1900-01-01。
2、
select dt1, CAST(dt1 as varbinary(8)) as bin8, SUBSTRING(CAST(dt1 as varbinary(8)),1,4) as 日期部分, cast(SUBSTRING(CAST(dt1 as varbinary(8)),1,4) as int) as 日期部分_十进制, SUBSTRING(CAST(dt1 as varbinary(8)),5,4) as 时间部分, cast(SUBSTRING(CAST(dt1 as varbinary(8)),5,4) as int) as 时间部分_十进制 from times --dt1是datetime类型
select dt2,CAST(dt2 as varbinary(10)) as bin8 from times --dt2为datetime2类型
- 对于datetime来说， substring显示的字节交换后的正常顺序（日期在前，时间在后）
- 对于datetime2来说，substring显示的是：时间精度（一个字节）+ 物理存储的实际二进制串（字节交换前）
- 为啥会这样呢？时间精度又是存储在哪儿的？为啥通过dbcc page中，看不到呢？
datetime2 值强制转换为 varbinary 值时，会向 varbinary 值添加一个额外的字节，用于存储精度 。来自https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver16。
添加了额外字节就不排序了？（额外字节+物理存储（字节倒着放的））.
所以，上述结果0x07307C27AB085B950A：第一个字节为时间精度（07），然后再把剩下的字节逆序得到：0A 95 5B（日期部分），08 ab 27 7c 30（时间部分）
3、时间部分的转换：
规则：对于datetime，时间部分存储的是半夜之后的时钟滴答数，每滴答一次表示3.33微妙，即1/300秒。
对于datatime2，时间计算公式为（H代表销售，M代表分钟，S代表秒，F代表小数部分，D代表小数位数）： (（H*60+M）*60+S) * 10^D+F
（注：01:02:03.123 的1位小数部分F表示为1 , 2位小数部分F表示为12 , 3位小数部分F表示为123，……，7位小数部分F表示为1230000）
| 数据类型 | select * from times查询显示 | 十进制时间 |
| datetime | 1900-01-01 01:02:03.123 | 1116937 |
| datetime2 | 1900-01-01 01:02:03.1230000 | 37231230000 |
1）：date，时间转存储
select ceiling( ((1 * 60 + 2)*60 + 3 + 0.123 )*300) -- 结果为：1116937
2）：date，存储转时间
select 1116937./300 --得到3723.123333（及3723.123秒）
select 3723/60/60 --得到小时：1
select (3723 % 3600) /60 --到的分钟：2
select 3723 % 60 --得到秒：3
3）：datetime2，时间转存储
select cast(((1 * 60 + 2) * 60 + 3) as bigint)*POWER(10,7) + 1230000 --结果：37231230000（datetime2默认精度为7，所以要把小数部分补充到7位）
或者：select ((1 * 60 + 2) * 60 + 3 + 0.123)*POWER(10,7) --也就是把整个时间转换成秒（3723.123秒）后，在乘以10的N次方（N为时间的精度）
4）：datetime2，存储转时间
select 37231230000/POWER(10,7) --得到3723.12300000000，然后的步骤和上的第二项相同
