SQL Server 数据类型

  SQL Server 2014数据类型(1)

在SQL Server中,数据类型是创建表的基础。在创建表时,必须为表中的每列指派一种数据类型。本节将介绍SQL Server中最常用的一些数据类型。即使创建自定义数据类型,也必须基于一种标准的SQL Server数据类型。例如,可以使用如下语法创建一种自定义数据类型(Address),但要注意,它基于SQL Server标准的varchar数据类型:

 
 
  1. CREATE TYPE Address  
  2. FROM varchar(35) NOT NULL; 

如果在SQL Server Management Studio的表设计界面中更改一个大型表中某列的数据类型,那么该操作可能需要很长时间。可以通过在Management Studio界面中脚本化这种改变来观察其原因。Management Studio创建一个辅助的临时表,采用像tmpTableName这样的名称,然后将数据复制到该表中。最后,界面删除旧表并用新的数据类型重命名新表。当然,此过程中还涉及其他一些用于处理表中索引和其他任何关系的步骤。

如果有一个包含数百万条记录的大型表,那么该过程可能需要花费10分钟,有时可能是数小时。为避免这种情况,可在查询窗口中使用简单的单行T-SQL语句来更改该列的数据类型。例如,要将Employees表中JobTitle列的数据类型改为varchar(70),可以使用如下语法:

 
 
  1. ALTER TABLE HumanResources.Employee ALTER COLUMN JobTitle Varchar(70); 

在转换为与当前数据不兼容的数据类型时,可能丢失重要数据。例如,如果要将包含一些数据(如15.415)的numeric数据类型转换为integer数据类型,那么15.415这个数据将四舍五入为整数。

你可能想为SQL Server表编写报表,显示表中每列的数据类型。完成这项任务的方法有很多种,但下例演示的这种常用的方法是连接sys.objects表和sys.columns表。在下面的代码中,有两个函数可能不太为你所熟悉。函数TYPE_NAME( )将数据类型ID转换为适当的名称。要进行反向操作,可使用TYPE_ID( )函数。需要注意的另一个函数是SCHEMA_ID(),它用于返回架构的标识值。在需要编写有关SQL Server元数据的报表时,这是特别有用的。

 
 
  1. USE AdventureWorks2014;  
  2. GO  
  3. SELECT o.name AS ObjectName,  
  4. c.name AS ColumnName,  
  5. TYPE_NAME(c.user_type_id) as DataType  
  6. FROM sys.objects o  
  7. JOIN sys.columns c  
  8. ON o.object_id = c.object_id  
  9. WHERE o.name ='Department' 
  10. and o.Schema_ID = SCHEMA_ID('HumanResources');  

该代码返回如下结果(注意,Name是一种用户定义的数据类型):

 
 
  1. ObjectName          ColumnName                  DataType  
  2. ---------------------------------------------------  
  3.  
  4. Department          DepartmentID            smallint  
  5. Department          Name                                        Name  
  6. Department          GroupName                       Name  
  7. Department          ModifiedDate            datetime  

1. 字符数据类型

字符数据类型包括varchar、char、text等。这些数据类型用于存储字符数据。varchar和char类型的主要区别是数据填充。如果有个列名为FirstName且数据类型为varchar(20)的表,同时将值Brian存储到FirstName列中,那么在物理上只存储5个字节。但如果在数据类型为char(20)的列中存储相同的值,将使用全部20个字节。SQL将插入拖尾空格来填满20个字符。

你可能想知道,如果要节省空间,那么为什么还使用char数据类型呢?这是因为使用varchar数据类型会稍增加一些系统开销。例如,如果要存储两字母形式的州名缩写,那么最好使用char(2)列。尽管有些DBA认为应最大可能地节省空间,但一般来说,好的做法是在组织中找到合适的阈值,并指定低于该阈值的采用char数据类型,反之则采用varchar数据类型。一条可以采用的原则是,任何小于或等于5个字节的列都应存储为char数据类型,而不是varchar数据类型。如果超过这个长度,使用varchar数据类型的好处将超过其额外开销。

nvarchar数据类型和nchar数据类型的工作方式与对等的varchar数据类型和char数据类型相同,但这两种数据类型可以处理国际性的Unicode字符。它们需要一些额外开销。以Unicode形式存储的数据为一个字符占两个字节。如果要将值Brian存储到nvarchar列,将使用10个字节;而如果将之存储为nchar(20),就需要使用40个字节。由于这些额外开销和增加的空间,应该避免使用Unicode列,除非确实有需要使用它们的业务或语言需求。提前考虑好将来是否需要在实例中使用Unicode列。如果将来没有这种需求,就应避免使用它们。

表1-1列出了这些类型,对其作了简单描述,并说明了要求的存储空间。

表1-1  SQL Server数据类型

数 据 类 型

描    述

存 储 空 间

Char(n)

n1~8000字符之间

n字节

Nchar(n)

n1~4000 Unicode字符之间

2×n字节

Nvarchar(max)

最多为2301(1 073 741 823)Unicode字符

2×字符数+2字节额外开销

Text

最多为2311(2 147 483 647)字符

每字符1字节+2字节额外开销

Varchar(n)

n1~8000字符之间

每字符1字节+2字节额外开销

Varchar(max)

最多为2311(2 147 483 647)字符

每字符1字节+2字节额外开销

2. 精确数值数据类型

数值数据类型包括bit、tinyint、smallint、int、bigint、numeric、decimal、smallmoney和money。这些数据类型都用于存储不同类型的数值。第一种数据类型bit只存储null、0或1,在大多数应用程序中被转换为true或false。bit数据类型非常适合用于开关标记,且只占据1字节空间。其他常见的数值数据类型如表1-2所示。

表1-2  精确数值数据类型

数 据 类 型

描    述

存 储 空 间

bit

01null

1字节(8)

tinyint

0255之间的整数

1字节

smallint

32 76832 767之间的整数

2字节

int

2 147 483 6482 147 483 647之间的整数

4字节

bigint

9 223 372 036 854 775 8089 223 372 036 854 775 807

之间的整数

8字节

numeric(p,s)decimal(p,s)

1 03811 0381之间的数值

最多17字节

money

922 337 203 685 477.5808922 337 203 685 477.5807

8字节

smallmoney

214 748.36482 14 748.3647

4字节

decimal和numeric这样的数值数据类型可存储小数点右边或左边的变长位数。scale(表中的s)是小数点右边的位数。precision(表中的p)定义了总位数,包括小数点右边的位数。例如,14.88531可为numeric(7,5)或decimal(7,5)。如果将14.25插入到numeric(5,1)列中,该值将被舍入为14.3。



 SQL Server 2014数据类型(2)

3. 近似数值数据类型

这个分类中包括数据类型float和real。它们用于表示浮点数据。但是,由于它们是近似的,因此不能精确地表示所有值。

float(n)中的n是用于存储该数尾数(mantissa)的位数。SQL Server对此只使用两个值。如果指定位于1~24之间,就使用24。如果指定25~53之间,就使用53。当指定float( )时(括号中为空),默认为53。

表1-3列出了近似数值数据类型,对其进行简单描述,并说明了要求的存储空间。

表1-3  近似数值数据类型

数 据 类 型

描    述

存 储 空 间

float[(n)]

1.79E+308~–2.23E308,0,2.23E3081.79E+308

n244字节

n>248字节

real( )

3.40E+38~–1.18E38,0,1.18E383.40E+38

4字节

4. 二进制数据类型

varbinary、binary、varbinary(max)等二进制数据类型用于存储二进制数据,如图形文件、Word文档或MP3文件,值为十六进制的0x0~0xf。image数据类型可在数据页外部存储最多2GB的文件。image数据类型的首选替代数据类型是varbinary(max),可保存超过8KB的二进制数据,其性能通常比image数据类型好。SQL Server 2012引入了一种功能,可以在操作系统文件中通过FileStream存储选项存储varbinary(max)对象。这个选项将数据存储为文件,同时不受varbinary(max)的2GB大小的限制。

表1-4列出了二进制数据类型,对其做了简单描述,并说明了要求的存储空间。

表1-4  二进制数据类型

数 据 类 型

描    述

存 储 空 间

binary(n)

n1~8000十六进制数字之间

n字节

varbinary(n)

n1~8000十六进制数字之间

每字符1字节+2字节额外开销

varbinary(max)

最多为2311(2 147 483 647)十六进制数字

每字符1字节+2字节额外开销

5. 日期和时间数据类型

datetime和smalldatetime数据类型用于存储日期和时间数据。smalldatetime为4字节,存储1900年1月1日~2079年6月6日之间的时间,并且只精确到最近的分钟。datetime数据类型为8字节,存储1753年1月1日~9999年12月31日之间的时间,并且精确到最近的3.33毫秒。

SQL Server 2012引入了4种与日期相关的新数据类型:datetime2、dateoffset、date和time。通过SQL Server联机丛书可找到使用这些数据类型的示例。

datetime2数据类型是datetime数据类型的扩展,有着更广的日期范围。时间总是用时、分钟、秒的形式来存储。可以定义末尾带有可变参数的datetime2数据类型--如datetime2(3)。这个表达式中的3表示存储时秒的小数精度为3位,或0.999。有效值为0~7之间,默认值为3。

datetimeoffset数据类型和datetime2数据类型一样,带有时区偏移量。时区偏移量最大为+/-14小时,包含了UTC偏移量,因此可以合理化不同时区捕捉的时间。

date数据类型只存储日期,这是我们一直需要的一个功能。time数据类型只存储时间,也支持time(n)声明,因此可以控制小数秒的粒度。与datetime2和datetimeoffset一样,n可为0~7之间。

表1-5列出了日期/时间数据类型,对其进行简单描述,并说明了要求的存储空间。

表1-5  日期和时间数据类型

数 据 类 型

描    述

存 储 空 间

date

111~99991231

3字节

datetime

175311~99991231日,精确到最近的3.33毫秒

8字节

datetime2(n)

111~99991231

0~7之间的n指定小数秒

6~8字节

datetimeoffset(n)

111~99991231

0~7之间的n指定小数秒+/–偏移量

8~10字节

smalldateTime

190011~207966日,精确到1分钟

4字节

time(n)

小时:分钟:.9999999

0~7之间的n指定小数秒

3~5字节

6. 其他系统数据类型

还有一些之前未见过的数据类型。表1-6列出了这些数据类型。

表1-6  其他系统数据类型

数 据 类 型

描    述

存 储 空间

cursor

包含对游标的引用,只能用作变量或存储过程参数

不适用

hierarchyid

包含对层次结构中位置的引用

1~892字节+2字节的额外开销

SQL_Variant

可能包含任何系统数据类型的值,除了textntextimagetimestampxmlvarchar(max)nvarchar(max)varbinary(max)以及用户定义的数据类型。最大尺寸为8000字节数据+16字节元数据

8016字节

table

用于存储用于进一步处理的数据集。定义类似于Create Table。主要用于返回表值函数的结果集,它们也可用于存储过程和批处理

取决于表定义和存储的行数

(续表)

数 据 类 型

描    述

存 储 空 间

timestamp

rowversion

对于每个表来说是唯一的、自动存储的值。通常用于版本戳,该值在插入和每次更新时自动改变

8字节

uniqueidentifier

可以包含全局唯一标识符(Globally Unique IdentifierGUID)GUID值可以从Newsequentialid()函数获得。这个函数返回的值对所有计算机来说是唯一的。尽管存储为16位的二进制值,但仍显示为char(36)

16字节

XML

定义为Unicode形式

最多2GB

cursor数据类型不能用于Create Table语句中。

XML数据类型存储XML文档或片段,在存储时使用的空间根据文档中使用UTF-16还是UTF-8决定,就像nvarchar(max)一样。XML数据类型使用特殊构造体进行搜索和索引。第15章将更详细地介绍这些内容。

7. CLR集成

在SQL Server 2014中,还可使用公共语言运行时(Common Language Runtime,CLR)中称为SQLCLR的部分创建自己的数据类型、函数和存储过程。这让用户可以使用Visual Basic或C#编写更复杂的数据类型以满足业务需求。这些类型被定义为基本的CLR语言中的类结构。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值