澳门金沙vip 6

【澳门金沙vip】特殊的数据类型: bit、sql_variant、sysname

在SQL Server中,特殊的数据类型主要有三个,分别是:bit、sql_variant 和
sysname

数据库使用Table来存储海量的数据,细分Table结构,数据最终存储在Table
Column中,因此,在设计Table Schema时,必须慎重选择Table Column的Data
Type,数据类型不仅决定了Column能够存储的数据范围和能够进行的操作,而且合适的数据类型还能提高查询和修改数据的性能。数据类型的选择标准既要满足业务和扩展性的需求,又要使行宽最小(行宽是一行中所有column占用的Byte)。最佳实践:使用与Column最大值最接近的数据类型。

一,bit

例如,bit
类型只能存储1和0,能够对bit进行逻辑比较(=或<>),不能对进行算术运算(+,-,*,/,%),不要对其进行>或<的比较,虽然bit类型支持,但是,这不
make sense。

bit类型,只有三个有效值:0,1 和
null,字符串true或false能够隐式转换为bit类型,true转换为1,false转换为0;任何非0的整数值转换成bit类型时,值都是1。

declare @b1 bit
declare @b2 bit 

set @b1=1
set @b2=0

-- right,return 0
select iif(@b1=@b2,1,0)

--error,The data types bit and bit are incompatible in the add operator.
select @b1+@b2

1,将字符串 true 和 false 隐式转换成 bit 类型

在设计Table
Schema时,要实现三大目标:占用空间少,查询速度快,更新速度快。这三个目标有些千丝万缕的关联,设计良好的Table
Schema,都会实现,反之,设计差的Table Schema,都不能实现。

declare @bit_true bit
declare @bit_false bit
set @bit_true='true'
set @bit_false='false'
select @bit_true,@bit_false

内存是访问速度最快的存储介质,如果数据全部存储在内存中,那会极大的提高数据库系统的吞吐量,但是,每个数据库系统能够使用的内存有限,为了提高查询性能,SQL
Server将最近使用过的数据驻留在内存中。SQL Server
查询的数据必须在内存中,如果目标数据页不在内存中,那么SQL
Server会将数据从Disk读取到内存中。SQL Server
响应时间跟数据加载很大的关系,如果加载的数据集占用的空间小,数据页分布集中,那么SQL
Server使用预读机制,能够很快将数据加载到内存,相应地,SQL
Server的响应时间会很小。

澳门金沙vip 1

澳门金沙vip 2

2,存储空间

创建索引能够提高查询性能,其实是因为,索引字段比Base
Table的字段少,索引结构占用的存储空间小,SQL Server
加载索引结构的耗时少。由于索引结构是有序的,避免了全表扫描,也能提高查询性能。使用窄的数据类型,使用数据压缩,创建BTree索引,创建ClumnStore
索引,都能减少数据集占用的存储空间,提高数据加载到内存的速度。SQL
Server在执行用户的查询请求时,每一行数据都必须在内存中,因此,数据集占用的空间越少,加载的过程越快,SQL
Server的查询性能越高。

bit类型存储 0 和 1 ,只需要使用 1 bit
就能表示,但是,在存储到Disk时,SQL
Server按照Byte来分配存储空间。如果表中只有1个 bit
列,那么该列将会占用1Byte的空间,一个Byte最多存储8个bit列。

一,窄的数据行会节省存储空间,减少IO次数

The SQL Server Database Engine optimizes
storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte.
If there are from 9 up to 16 bit columns, the
columns are stored as 2 bytes, and so on.

使用窄的数据类型,使行的宽度达到最小,在存储相同数据量时,能够节省存储空间,减少Disk
IO的次数。

二,sql_variant

在存储日期数据时,Date占用3Byte,DateTime占用8Byte,DateTime2(2)占用6Byte,DateTime2(4)占用7Byte,DateTime2(7)占用8Byte。不管从表示的精度上,还是从占用的存储空间上来看,DateTime2(N)都完胜DateTime。

1,存储空间

例如,存储‘yyyy-mm-dd MM:HH:SS’格式的日期数据,有以下4中选择:

sql_variant
是变长的数据类型,包含两部分信息:基础类型和Value,最多存储8000Byte的数据。

  • 使用字符串 varchar(19) 或
    nvarchar(19)存储,十分不明智,前者占用19Byte后再占用38Byte;
  • 使用数据类型
    datetime2(7)存储,占用8Byte,虽然精度更高,但是毫秒都是0,浪费存储空间;
  • 使用数据类型
    datetime存储,占用8Byte,如果需要存储毫秒,datetime不满足;
  • 使用数据类型 datetime2(2)存储,占用6Byte,相比较是最理想的。

sql_variant includes both the base type
information and the base type value. The maximum length of the actual
base type value is 8,000 bytes.

由于SQL
Server存储数据是按照row存储数据的,每个Page能够存储的数据行是有限的。在查询同等数量的数据行时,如果row宽度窄,那么每个page会容纳更多的数据行,不仅减少IO次数,而且节省存储空间。

declare @sv sql_variant
set @sv=REPLICATE('abcd',2001)
--max bytes:8000
select len(cast(@sv as varchar(max)))

二,在窄的数据列上创建index,能够提高查询性能

2,赋值和运算

在窄的数据列上创建Index,索引结构占用的存储空间更小,SQL
Server消耗更少的Disk IO就能将索引结构加载到内存中,能够提高查询性能。

澳门金沙vip,在赋值时,SQL Server
自动将其他数据类型隐式转换为sql_variant类型,但是,SQL
Server不支持将sql_variant类型隐式转换成其他数据类型,必须显式转换。不能直接对sql_variant进行运算,例如,在对sql_variant
类型进行算术/字符操作时,必须显式将其转换成基础数据类型,然后才能对其进行运算。

在创建Index时,必须慎重选择聚集索引键,主要有两个原因

When handling the sql_variant data type, SQL Server supports implicit
conversions of objects with other data types to the sql_variant type. However, SQL Server does not support
implicit conversions from sql_variant data to an
object with another data type.

1,聚集索引其实就是表本身,SQL
Server必须保持物理存储顺序和逻辑存储顺序一致

declare @var_int sql_variant
declare @var_bit sql_variant

set @var_bit='true'
set @var_int=10

select @var_bit,@var_int,cast(@var_bit as bit),cast(@var_int as int)

在SQL Server中,Clustered
Index能够确定Table的物理存储,使Table的物理存储顺序和聚集索引键的逻辑顺序保持一致。在对Table数据进行update时,如果更新聚集索引键,导致数据行所在聚集索引键必须移动,此时,SQL
Server不能“原地更新”数据行,必须将数据行移动到其应有的物理位置上,Table的物理存储顺序和聚集索引键的逻辑顺序才能保持一致。SQL
Server将Update命令拆分成等价的delete命令和insert 命令。

澳门金沙vip 3

示例:聚集索引键4被修改为8,那么,SQL
Server将数据行5删除,然后再相应的位置上插入数据行8。

三,sysname

澳门金沙vip 4

sysname 是一个系统数据类型,用于定义表列、变量以及存储过程的参数,是nvarchar(128) 的同义词,当该类型用于定义table
column时,SQL Server 会自动添加 not null ,等价于nvarchar(128) not
null。

如果插入的位置上没有多余的存储空间,那么,插入操作会导致页拆分,产生索引碎片,影响查询性能。

查看sysname的定义

澳门金沙vip 5

exec sp_help  sysname 

2,NonClustered Index的叶子节点中,都包含Clustered Index键。

澳门金沙vip 6

例如,在表上有两个索引:Clustered Index(c1,c2),Nonclustered
Index(c2,c3),实际上,Nonclustered
index的索引定义(c2,c3)include(c1),即,在Nonclustered
Index的叶子节点中,包含Clustered Index所有的Index Key。包含列和Index
Key的区别在于,Index
Key用于路由索引结构,而包含列用于返回数据,不提供搜索功能。

  • 使用sysname定义变量或参数时,等价于 nvarchar(128)
  • 使用sysname定义column的类型时,等价于 nvarchar(128) not null

由于Clustered Index“无所不在”,Clustered
Index的索引键最好创建在窄的,不变的,唯一的和只增长的数据列上。在创建Clustered
Index时,最好是唯一索引(Unique Index)。窄的数据行会使每一个Index
page存储更多的index key,SQL Server
Engine定位到某一行所经过的节点数更少,即导航的Path更短,加载和查询速度更快。

当使用sysname定义column的类型时,SQL Server 自动在sysname 后面加上not
null,即 sysname not null,等价于 nvarchar(128) not null

由于每一个nonclustered index的Index pages或index key
columns中都会包含Clustered Index key columns,如果Clustered Index key
columns的宽度比较大,这会导致所有nonclustered
index的索引树占用较大的存储空间,Disk IO更多,更新和查询操作都会变慢。

create table dbo.dt
( 
  col sysname 
)
--系统生成的create table 脚本
CREATE TABLE [dbo].[dt]
(
    [col] [sysname] NOT NULL
)

In general, it is best practice to create
a clustered index on narrow, static, unique, and ever-increasing
columns. This is for numerous reasons. First, using an updateable column
as the clustering key can be expensive, as updates to the key value
could require the data to be moved to another page. This can result in
slower writes and updates, and you can expect higher levels of
fragmentation. Secondly, the clustered key value is used in
non-clustered indexes as a pointer back into the leaf level of the
clustered index. This means that the overhead of a wide clustered key is
incurred in every index created.

 

三,使用正确的数据类型,减少转换的次数

参考文档:

在SQL
Server中,对数据进行强制类型转换或隐式类型转换都需要付出代价,所以,使用正确的数据类型,避免类型转换是十分必要的。例如,如果存储的数据格式是‘yyyy-mm-dd
MM:HH:SS’,虽然字符串类型和Datetime类型能够隐式转换,但是使用字符串类型
varchar(19)或
nvarchar(19)存储是十分不明智的,不仅浪费存储空间,而且隐式转换对性能有负作用。

sql_variant
(Transact-SQL).aspx)

四,常见数据类型所占用的字节数

数据类型大致分为四种:数值类型,日期和时间类型,字符串类型,GUID,使用DataLength()能够查看任意数据类型的变量所占用的字节数量

1,数值类型

对于整数类型,TinyInt
占用1Byte,表示的整数范围是:0-255;SmallInt,int和bigint
分别占用2B,4B和8B。

对于小数类型,decimal(p,s)表示精确的小数类型,float(n)表示近似的小数类型,常用于表示百分比,除法的结果,有两种类型float(24)占用4B,float(53)占用8B,参考《SQL
Server的小数数值类型(float 和
decimal)用法》。