2023年7月13日发(作者：)

SQLServer数据类型1. 数据类型的选择帮助优化查询，⽐如针对int类型列和针对⽂本类型列可能会⽣成完全不同的查询计划2. 三种数据类型：系统数据类型别名数据类型：⽤户可以为系统数据类型提供⼀个别名，并且可以对数据类型做进⼀步限制，这样可以保持数据库和应⽤程序的⼀致性⽤户⾃定义数据类型：引⼊的托管代码中的数据类型，包括CLR中的系统数据类型和CLR中⽤户⾃定义数据类型3. 整数数据类型：tinyint存储的是⼀个字节（8位），值范围是0-255smallint存储的是两字节（16位），值范围是-32768-32767int存储的是四字节（32位），值范围是-2147483648-2147483647bigint存储的是⼋字节，值范围是-2^63 - 2^63-1，存储的是⾮常⼤的整数值4. 分数数据类型：decimal：ANSI兼容，允许指定整数位和⼩数位numeric：功能上和decimal是⼀样的money、smallmoney：以前遗留下来的数据类型，⽤来存储货币值，四个⼩数位精度，通常使⽤decimal代替这个数据类型5. bit数据类型：存储1位值，存储上有优化，如果⼀张表中少于8个的bit列，他们将被存储在⼀个字节中，通常⽤来存储boolean值；在SQL Server中bit值没有字符串形式，True和False只是可以转化为bit值，就像整数值1、0可以转化为bit值⼀样，底层bit值存储的不是True或False，也不是整数值1、0，⽽只是⼀位值⽽已；⾼级语⾔中False都使⽤0来存储，⽽True存储根据语⾔不同使⽤不同的值，因此在检测boolean值时，使⽤@inputvalue=0来检测false，使⽤@inputvalue<>0来检测true，不要使⽤@inputvalue=1来检测true值，在有的语⾔中得不到预期的结果；bit类型值还可以存储null值，也就是说bit类型值共有三种状态0,1,null6. IDENTITY和int或者bitint列⼀起使⽤，通过SET IDENTITY_INSERT ON开关，允许在IDENTITY列显式插⼊值，具有IDENTITY属性的列不能够保证值都唯⼀，因为可以显式插⼊值；可以通过系统变量@@IDENTITY检索最近⼀次IDENTITY值，使⽤@@IDENTITY的问题是：当插⼊⼀条记录到Customer表中时，这个customer被赋予⼀个新的identity，但是如果这个表有⼀个触发器，这个触发器在插⼊时将这个条⽬写⼊到audit logging table中，@@IDENTITY返回的是logging table值，⽽不是Customer表的值；此时需要使⽤SCOPE_IDENTITY函数，它提供的是当前范围Scope内最新identity值；当⼀次插⼊多⾏记录时，通过OUTPUT语句检索IDENTITY值（通过检索inserted表来检索该值），插⼊⼀条记录时，使⽤这种⽅法也可以检索插⼊的值7. 近似数据类型通常⽤在科学计算应⽤程序中，⽽不是商业应⽤程序中，不要使⽤近似数据类型存储货币值这样的商业值real：存储的是4字节（32位），精确到7位⼩数float(n)：n代表⽤来存储尾数的位数，n默认等于53，当n在1-24范围时，n=24，即精度为7位⼩数，使⽤4字节存储，也就是real数据类型的值；当n在25-53范围时，n=53，即精度15位⼩数，使⽤8字节存储8. SQL Server⽀持丰富的⽇期和时间数据类型，需要⾮常⼩⼼每个类型的字符串形式，⼤量函数可⽤来处理这些类型date：符合ANSI标准，范围0001-01-01 to 9999-12-31，精确到天time：符合ANSI SQL标准，范围00:00:00.0000000 to 23:59:59.9999999，精确到100纳秒，允许指定⼩数位数，即定义纳秒的位数datetime2：是date和time类型的组合datetime：旧版本的⽇期时间类型，范围1753-01-01 to 9999-12-31，精确到3.33毫秒，datetime类型根据语⾔设置将字符串转化为⽇期，"YYYYMMDD"总是可以转化为正确的⽇期时间类型，"YYYY-MM-DD"可能会被当做"YYYY-DD-MM"格式进⾏转化，取决于设置，date类型不会有这样的⾏为datetimeoffset： datetime2和timezone offset的组合，⽇期时间部分代表本地时间，timezone offset范围-14:00 to +14:00，表⽰和UTC时间的偏移量，注意date类型是和时区⽆关的9. GUID就是⼀个数字分配器，保证⼀个程序⽣成的值和其他程序⽣成的值不冲突，是128位值；在SQL Server中对应的数据类型就是uniqueidentifier，该类型允许的操作只包括=、<>、<、>、<=、>=以及是否为null的检测IS NULL 、IS NOT NULL；该数据类型定义的列上不允许使⽤IDENTITY；通过NEWID()函数⽣成新的GUID值，该函数⽣成的GUID值具有⾮常⼤的随机性，有性能问题，NEWSEQUENTIALID()函数试图解决NEWID()函数的随机性，不过它是以牺牲唯⼀性为代价的10. NULL是数据库列的状态，不是数据类型的值，使⽤IS NULL检测是否为null，⽽不是使⽤=NULL检测是否等于null；为数据库列指定不当的nullability是常见的设计错误11. Unicode常量要求在前⾯都加上N前缀，使⽤LEN()函数返回字符的数量，使⽤DATALENGTH()返回字节数量12. 字符数据类型：char和nchar：存储固定长度的字符，如果这两个类型值的字符长度不够的话，在尾部使⽤空格填充，使⽤时通常都需要去掉尾部的空格，char使⽤单字节存储，nchar采⽤双字节存储varchar和nvarchar：⽤来存储变长字符串，允许你指定⼀个字符串的最⼤长度，nvarchar⽤来存储unicode字符（即双字节字符），varchar和nvarchar限制在8000和4000个字符上限，正好是数据库中⼀个数据页的⼤⼩varchar(max)和nvarchar(max)：存储更长的字符，它们允许存储⾼达2GB的数据text和ntext：旧数据类型，被启⽤，使⽤varchar(max)和nvarchar(max)替换这两个数据类型sysname：SQL Server中对象名称通常都是该类型，是nvarchar(128)的别名13. Collation⽤来控制在SQL Server中存储的⾮Unicode数据使⽤的代码页，以及SQL Server中排序和⽐较字符值时使⽤的规则code pages代码页：在计算机中每个字符使⽤⼀个字节，共有256个字符，0-31作为控制字符，⽐如回车（8）、Tab（9）、空格（32），⼀直到Delete（127）；127以上的值则根据标准不同表⽰不同的含义，这些标准在MSDOS操作系统中就被称为代码页，⼀个代码页就定义128-255之间的字符的⽤途，这些代码页0-127之间的字符定义都是相同的14. 共有两类Collations：SQL Server Collations和Windows Collations，SQL Server Collations⽤来保持向后兼容性，⿎励使⽤WindowsCollations，SQL Server Collations名称采⽤如下形式：SQL_SortRules[_Pref]_CPCodePage_ComparisonStyleSortRules：使⽤的字母表或者语⾔Pref：是否⼤写字母优先CodePage：代码页，1-4位整数，由于历史原因CP1使⽤的是1252代码页，其他正常Comparison Style：BIN表⽰按⼆进制排序或者case、accent是否敏感的组合，CI不区分⼤⼩写，CS区分⼤⼩写，AI不区分语调，AS区分语调15. 可以在服务器、数据库、列级别设置Collations，当⽐较使⽤不同的Collations存储的数据时，要求指定⽤于⽐较的Collations16. CAST是ANSI SQL中标准的数据类型转换操作符，Convert则提供了更加强⼤的功能，允许你指定如何执⾏类型转换，即允许指定转换的格式Style，注意Style通常是关于字符串输出的，但是也可以⽤来处理字符串输⼊，即解析字符串17. 尽量避免隐式转换，因为隐式转换可能会带来⾮期望的结果，隐式转换经常出现的问题：为⽬标类型指定了不恰当的值，⽐如不恰当字符串转换为int值为⽬标类型指定值超出了该类型的范围，⽐如为tinyint类型指定了300在转换时值被截断（有时是悄悄的），⽐如CONVERT(varchar(6),SYSDATETIME(),112)，112格式通常返回8位字符串，当转换为6位字符串时，就被悄悄截断在转换时值被舍⼊（有时是悄悄的），⽐如datetime类型值20051231 23:59:59.999被悄悄转换为20060101 00:00:00.000在转换时值被修改（有时是悄悄的），SELECT 5ee，被悄悄修改为5对数据类型内部存储做假设，依赖于数据类型内部存储是⾮常危险的，因为内部表⽰随着时间会发⽣变化⼀些⽇期类型转换依赖于语⾔设置，⽐如2010-05-04即可以转换为2010年5⽉4⽇，也可以转换为2010年4⽉5⽇有⼀些解析问题难以理解，⽐如SELECT 5ee18. rowversion数据类型是⽤来帮助处理并发的，之前SQL Server使⽤的是timestamp，现在timestamp已经被弃⽤；在每次修改数据⾏时，rowversion值都⾃动被修改，当你要提交数据修改时，⾸先检查是否存在rowversion值的⾏，如果不存在，则说明数据已经被修改，此时在使⽤相应的策略，注意这⾥存在竟态条件，即你检查时没有发现数据被修改，但是提交前数据却被修改；rowversion在数据库中保存⼀个计数器，所有修改都会让计数器增加，可以使⽤系统变量@@DBTS检索返回的rowversion值19. Alias data types就是系统内置数据类型的⼦类型，使⽤CREATE TYPE语句创建，包含了是否可以为null的说明，通常⽤来维护应⽤程序中使⽤的数据类型的⼀致性（⽐如定义了ProductNum类型，其为nvarchar(20)，则使⽤ProductNum定义的列都是nvarchar(20)，不会出现在⼀个表中product number是nvarchar(20)，在另⼀个表中是nvarchar(22)）；数据库公共⾓⾊都⾃动被授予对Alias datatypes的REFERENCES权限，即公共⾓⾊都⾃动可以引⽤创建的Alias data types20. 其他数据类型：binary、varbinary、varbinary(max)：⽤来存储任意的⼤的⼆进制对象（blobs）image：已经被遗弃，应该使⽤varbinary(max)hierarchyid：是在托管代码中实现的，代表树中的节点sql_variant：⽤来存储未知类型的数据，很少使⽤xmlcursor：在构造基于游标的代码时，保存对游标的引⽤table：保存整个结果集geometry、geography