附注:微软认证考试70-461范围
- Create Database Objects创建数据库对象 (24%)
- Work with Data数据处理 (27%)
- Modify Data数据修改 (24%)
- Troubleshoot & Optimize故障排解及SQL优化 (25%)
本文是第二节Work with Data 数据处理 。
第三部分:Implement data types. May include but not limited to: use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance;when to use what data type for columns. 数据类型实现。可能包含但不仅限于:合理使用数据;理解各种数据类型的使用和限制;GUID对数据库性能的影响(newid, newsequentialid);掌握特定情况下针对列使特定的数据类型;
数据类型实现
什么是数据类型?
在 SQL Server 中,每个列、局部变量、表达式和参数都具有一个相关的数据类型。数据类型是一种属性,用于指定对象可保存的数据的类型:整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。
换句话说,数据类型是一种约束,限制其列上输入的值。比如整型列不会存储字符串或小数。另外,每一个数据类型都有其自己的范围去存储值,并且最多占用的磁盘或内存空间不会超过其范围。
注意:废弃的数据类型(Deprecated data types)不建议使用,因为它们会在未来的新版本删除掉。
下面列出各种SQL Server数据类型:
SQL数字类型:
数据类型 | 范围 | 存储 |
---|---|---|
bigint | -9,000,000,000,000,000,000 to 9,000,000,000,000,000,000 | 8 bytes |
bit | 1 or 0 | 1 byte |
decimal |
| Varies |
int | -2,000,000,000 to 2,000,000,000 | 4 bytes |
money | -922,337,203,685,477.508 to 922,337,203685,477.5807 | 8 bytes |
numeric | ||
smallint | -32,000 to 32,000 | 2 bytes |
smallmoney | -214,748.3648 to 214,748.3647 | 4 bytes |
tinyint | 0 to 255 | 1 byte |
符点数据类型通常用于科学计算或用作近似值数字类型
数据类型 | 范围 | 存储 |
---|---|---|
float | ||
real |
日期和时间类型:
以不同的格式存储日期和时间
数据类型 | 范围 | 存储 |
---|---|---|
date | 10 | 3 bytes |
datetime2 | 19 to 27 | Varies |
datetime | 19 to 23 | 8 bytes |
datetimeoffset | 26 to 34 | 10 bytes |
smalldatetime | 19 | 4 bytes |
time | 8 to 16 | 5 bytes |
字符串类型:
通常用于存储英文字符串
数据类型 | 范围 | 存储 |
---|---|---|
char | 1 to 8,000 | Varies |
text | 2,000,000,000 | Varies |
varchar | 8,000 | Varies |
UNICODE字符串类型:
通常用于存储各种非英文语言字符串,如中文。
数据类型 | 范围 | 存储 |
---|---|---|
nchar | 1 to 4,000 | Varies |
ntext | 1,000,000,000 | Varies |
nvarchar | 4,000 | Varies |
二进制数据类型:
数据类型 | 范围 | 存储 |
---|---|---|
binary | Varies | |
image | Varies | |
varbinary | Varies |
其它数据类型:
数据类型 | 范围 | 存储 |
---|---|---|
cursor | ||
hierarchyid | ||
sql_variant | ||
table | ||
timestamp | ||
uniqueidentifier | ||
xml |
参考:http://www.katieandemil.com/sql-server-2012-data-types
GUID对数据库性能的影响(newid, newsequentialid);
两个点:1:newid比newsequentialid慢。2:newsequentialid不是绝对安全的,因为它是可猜测的。newid是随机的,是随机数的不错替代选择。
NEWID:创建 uniqueidentifier 类型的唯一值。使用:NEWID()
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)
结果:
Value of @myid is: 6F9619FF-8B86-D011-B42D-00C04FC964FF
注意:此值是随机的!
参考:http://msdn.microsoft.com/zh-cn/library/ms190348.aspx
NEWSEQUENTIALID()
在启动 Windows 后在指定计算机上创建大于先前通过该函数生成的任何 GUID 的 GUID。在重新启动 Windows 后,GUID 可以再次从一个较低的范围开始,但仍是全局唯一的。在 GUID 列用作行标识符时,使用 NEWSEQUENTIALID 可能比使用 NEWID 函数的速度更快。其原因在于,NEWID 函数导致随机行为并且使用更少的缓存数据页。 使用 NEWSEQUENTIALID 还有助于完全填充数据和索引页。
NEWSEQUENTIALID() 只能与 uniqueidentifier 类型表列上的 DEFAULT 约束一起使用。例如:
CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID())
当 NEWSEQUENTIALID() 用于 DEFAULT 表达式时,不能与其他标量运算符结合使用。例如,您不能执行以下操作:
CREATE TABLE myTable (ColumnA uniqueidentifier DEFAULT dbo.myfunction(NEWSEQUENTIALID()))
在上一个示例中,myfunction() 是接受并返回uniqueidentifier 值的标量用户定义标量函数。
NEWSEQUENTIALID() 不能在查询中引用。
您可以使用 NEWSEQUENTIALID() 生成 GUID 以减少叶级别索引上的页争用。
使用 NEWSEQUENTIALID() 生成的每个 GUID 在该计算机上都是唯一的。仅当源计算机具有网卡时,使用 NEWSEQUENTIALID() 生成的 GUID 在多台计算机上才是唯一的。
参考: http://msdn.microsoft.com/zh-cn/library/ms189786(v=sql.110).aspx