对现有表分区,选择合适的数据类型
分类:数据库

数据库使用Table来存款和储蓄海量的数目,细分Table布局,数据最后存储在Table Column中,由此,在筹算Table Schema时,必需严慎接收Table Column的Data Type,数据类型不只有决定了Column能够存款和储蓄的数据范围和能够进行的操作,何况适用的数据类型还是能增高查询和修正数据的个性。数据类型的接纳规范既要满足专门的工作和扩大性的须求,又要使行宽最小(行宽是单排中具备column占用的Byte)。最佳施行:使用与Column最大值最临近的数据类型。

在SQL Server中,普通表能够转正为分区表,而分区表无法转变为普通表,普通表转化元素区表的长河是不可逆的,将普通表转变为分区表的章程是:

UniqueIdentifier 数据类型用于存款和储蓄GUID的值,占用16Byte。 SQL Server将UniqueIdentifier存款和储蓄为16字节的二进制数值,Binary(16卡塔尔,依据一定的格式突显,突显的格式是:xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,此中,x是16进制数字,数值范围是从0-9,A-F。由于各种字节存储2个16进制数据,由此,依据存款和储蓄字节,UniqueIdentifier的格式简写为:4B-2B-2B-2B-6B。使用GUID的好处是:在不一致的Server上,完毕GUID数值的“唯后生可畏”,SQL Server保障GUID的值总是唯风华正茂的,只在极少数处境下或然会再一次。

在SQL Server中,堆表是指未有开创集中索引的表,其储存空间由PFS,IAM等种类页来追踪,PFS使用1Byte,表示三个page中空间的采取景况。BTree构造的囤积空间是稳步的,当向BTree构造中插入新的多寡行时,SQL Server依照键值该数据行插入到一定的职位上,以保险BTree布局是依葫芦画瓢的;当删除三个Page中的全部数据行之后,SQL Server会将该Empty Page释放,其余对象足以接纳该Page。堆表的长空管理,和BTree结构有十分的大的例外。

比方,bit 类型只好存款和储蓄1和0,能够对bit举办逻辑比较(=或<>),不可能对进展算术运算( ,-,*,/,%),不要对其进展>或<的可比,即便bit类型援救,可是,那不 make sense。

在分区结构(Partition Scheme)上创办聚集索引,便是说,将集中索引分区。

在SQL Server中,UniqueIdentifier 列的特点:

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

数据库中本来就有分区函数(partition function) 和分区构造(Partition scheme):

  • 系统不会自动为UniqueIdentifier列赋值,务必显式赋值;
  • 运用GUID爆发函数 NewID(卡塔尔(قطر‎赋值,产生随机的GUID;
  • NewSequentialID(卡塔尔(قطر‎函数只可以用于表列的Default约束中,产生顺序的GUID;
  • 接纳字符串常量赋值,字符串常量的格式是:'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',x是16进制数字,数值范围是从0-9,A-F;
  • UniqueIdentifier 值能够非常大小,能够采用is null 或 is not null 操作符判别是或不是为NULL;
  • 能够被RowGUIDCol属性标识,使用$ROWGUID 引用具备RowGUIDCol属性的UniqueIdentifier列;

当须求查询堆表的数量时,SQL Server只可以动用表扫描(Table Scan)访问堆表数据。表扫描意味着SQL Server会将探望堆表的具备数据行,以检查该数据行是还是不是满足查询条件。

在设计Table Schema时,要达成三大目标:占用空间少,查询速度快,更新速度快。那四个指标有个别复杂的涉及,设计能够的Table Schema,都会兑现,反之,设计差的Table Schema,都不能落到实处。

澳门新萄京 1澳门新萄京 2

意气风发,对UniqueIdentifier 变量赋值

2,向堆表中插入数据

内部存款和储蓄器是访问速度最快的存款和储蓄介质媒质,假若数据总体仓库储存在内部存款和储蓄器中,那会大幅度的拉长数据库系统的吞吐量,不过,各类数据库系统能够利用的内部存款和储蓄器有限,为了提升查询品质,SQL Server将新近利用过的数码驻留在内部存款和储蓄器中。SQL Server 查询的数额必需在内部存款和储蓄器中,要是指标数据页不在内部存款和储蓄器中,那么SQL Server会将数据从Disk读取到内部存款和储蓄器中。SQL Server 响合时间跟数据加载不小的关系,假如加载的数据集占用的半空中型Mini,数据页布满集中,那么SQL Server使用预读机制,能够神速将数据加载到内部存款和储蓄器,相应地,SQL Server的响适那时候候间会相当的小。

-- create parition function
CREATE PARTITION FUNCTION pf_int_Left (int)
AS RANGE LEFT 
FOR VALUES (10,20);

--determine partition number
select $Partition.pf_int_left(21)

CREATE PARTITION SCHEME PS_int_Left
AS PARTITION pf_int_Left
TO ([primary], [primary], [primary]);

1,使用NewID(卡塔尔(英语:State of Qatar) 为UniqueIdentifier赋值,函数NewID()用于发生随机的GUID数值。

SQL Server或许将新的多寡行插入到表构造的别样page中。在向堆表中插入数据行时,SQL Server扫描PFS系统页,查看堆表的存放空间,只要发觉此外三个page有丰盛的空余空间能够容纳新的数额行,SQL Server就将数据插入到该岗位。借使该Page的蕴藏数据行分布低比较零散,SQL Server会重新组织该数量页上现成数据行的存放,以腾挪出延续的闲暇空间,存款和储蓄新的数据行。

澳门新萄京 3

View Code

declare @ui uniqueidentifier
set @ui=newid()
select @ui

豆蔻梢头经已分配的数据页未有充分的空闲空间容纳新的数据行,那么SQL Server会分配新的extent,以存款和储蓄数据。

创设索引能够提高查询品质,其实是因为,索引字段比Base Table的字段少,索引布局占用的存放空间小,SQL Server 加载索引结构的耗费时间少。由于索引构造是不改变的,幸免了全表扫描,也能增高查询品质。使用窄的数据类型,使用数据压缩,成立BTree索引,创造ClumnStore 索引,都能收缩数据集占用的寄放空间,进步数据加载到内部存储器的进程。SQL Server在推行客户的询问央浼时,每风华正茂行数据都必得在内部存款和储蓄器中,由此,数据集占用的半空中国和越南社会主义共和国少,加载的长河越快,SQL Server的询问性能越高。

假诺在普通表上设有集中索引,而且集中索引列是分区列,那么重新建立集中索引,就会使表转产生分区表。集中索引的创建有两种办法:使用clustered 节制(primary key 或 unique约束)创制,使用 create clustered index 创建。

2,使用字符串赋值,字符串的格式是:‘xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx’

澳门新萄京,3,从堆表中剔除数据

意气风发,窄的数额行会节省存款和储蓄空间,降低IO次数

后生可畏,在分区构造(Partition Scheme)上,成立聚焦索引

declare @ui uniqueidentifier
set @ui='AA786048-44BB-E511-80E3-F8B156CF6E62'
select @ui

当从heap中除去数据行时,SQL Server 二零一三不会自动协会Page的仓库储存空间,直到插入新的多寡行时,SQL Server才会裁减Page中零散的上空,腾挪出连续的闲暇空间,以存款和储蓄新的数据行。要是将Page的有所数据行都删除,SQL Server不会将empty pages的储存空间释放,那有的空间依然被堆表占用,无法被别的对象使用。

采用窄的数据类型,使行的增长幅度抵达最小,在积存相近数据量时,能够节约存款和储蓄空间,缩小Disk IO的次数。

后生可畏旦聚集索引是使用 create clustered index 创制的,何况聚焦索引列就是分区列,使普通表调换到分区表的章程是:删除全数的 nonclustered index,在partition scheme上海重机厂建clustered index

二,创建UniqueIdentifier column

对现有表分区,选择合适的数据类型。对现有表分区,选择合适的数据类型。In additin to space on pages, not being reclaimed, empty pages in heaps frequently can not be reclaimed. Even if you delete all the rows from a heap, sql server does not mark the mepty pages as unallocated, so the space is not available for other objects to uses.

在蕴藏日期数据时,Date占用3Byte,DateTime占用8Byte,Date提姆e2(2卡塔尔占用6Byte,DateTime2(4卡塔尔(英语:State of Qatar)占用7Byte,DateTime2(7卡塔尔国占用8Byte。不管从代表的精度上,仍旧从占用的囤积空间上来看,DateTime2(N卡塔尔(قطر‎都小胜DateTime。

1,表dbo.dt_partition的聚集索引是利用 create clustered index 创制的,

是因为UniqueIdentifier 列不是由系统自动赋值,必需显式赋值,可认为UniqueIdentifier列创造Default限定,并动用GUID爆发函数赋值。

在实施删除操作使时,假若接纳with(tablock卡塔尔国申请表锁,那么SQL Server在剔除数据行时,将释放Empty Page的仓库储存空间。

比方,存款和储蓄‘yyyy-mm-dd MM:HH:SS’格式的日子数据,有以下4中挑选:

create table dbo.dt_partition
(
ID int,
Code int
)

create clustered index cix_dt_partition_ID 
on dbo.dt_partition(ID)

举个例子,在Default限定中选择NewID(卡塔尔,为每行数据变化随机的唯大器晚成值

假使堆表已经存在大气的悠闲空间未被保释,有三种方式来消除:

  • 采用字符串 varchar(19卡塔尔(英语:State of Qatar) 或 nvarchar(19卡塔尔国存款和储蓄,非常不明智,前面八个占用19Byte后再占领38Byte;
  • 接收数据类型 datetime2(7卡塔尔(英语:State of Qatar)存款和储蓄,占用8Byte,即便精度更加高,不过阿秒都以0,浪费存款和储蓄空间;
  • 动用数据类型 datetime存储,占用8Byte,假若急需仓库储存皮秒,datetime不知足;
  • 选取数据类型 datetime2(2卡塔尔存款和储蓄,占用6Byte,绝相比较是最理想的。

2,从系统表Partition中,查看该表的分区唯有一个

CREATE TABLE dbo.myTable_Rand
(
    ColumnA uniqueidentifier DEFAULT NewID(),
    ColumnB int,
    columnC varchar(10)
) 
  • 推行alter table rebuild命令,重新创制堆表空间,SQL Server将为堆表分配新的蕴藏空间,而自由原有的积存空间,新的积存空间是凝聚存款和储蓄的;
  • 在堆表上,创建聚焦index,将堆表转变为BTree,使用BTree布局管理表空间

是因为SQL Server存款和储蓄数据是比照row存款和储蓄数据的,每种Page能够存款和储蓄的数目行是有限的。在查询同生机勃勃数量的数额行时,借使row宽度窄,那么每个page会容纳更加的多的多少行,不止收缩IO次数,而且节省存款和储蓄空间。

select *
from sys.partitions p 
where p.object_id=object_id(N'dbo.dt_partition',N'U')

1,随机的GUID

4,更新堆表的多少

二,在窄的数目列上创立index,可以加强查询品质

澳门新萄京 4

NewID(卡塔尔(قطر‎函数产生的GUID是唯豆蔻年华的,可是,值的尺寸是大肆的,不是贰个生机勃勃味增添的值(ever-increasing value),SQL Sever 不保障产生的GUID比早前的GUID 大或小。要是将NewID(卡塔尔(英语:State of Qatar)函数产生GUID作为clustered index key,那么新的数额行插入的职责是即兴的,那样将导致Page split ,减少 IO 品质。

在创新堆表数据时,假使数额行长度增添,以致数据页不足以容纳更新之后的数额行,那么SQL Server将该多少行活动到任何Data Page上,在原始的Data Page上安装八个Forward Pointer,指向数据行活动之后的情理地点,那样做的好处是,堆表数据的换代不会潜移暗化nonclustered index。

在窄的数额列上创制Index,索引布局占用的仓库储存空间更加小,SQL Server消耗越来越少的Disk IO就可以将引得结构加载到内部存款和储蓄器中,能够增长查询品质。

3,使用partition scheme,重建表的聚集索引

极品的clustered index key 应该是雨后春笋的(increase),data type is narrow (narrow卡塔尔(قطر‎,值是unique(unique),不会再三更新(static),NewID(卡塔尔(قطر‎产生的GUID满意narrow,unique, static,不过不满意increase,由此不是不错中的clustered index key。

澳门新萄京 5

在创设Index时,必需严谨选用聚焦索引键,首要有三个原因

create clustered index cix_dt_partition_ID 
on dbo.dt_partition(ID)
with(drop_existing=on)
on PS_int_Left(ID)

2,有序的GUID

只要数据行再一次移动,那么Forward Pointer会实行新的轮廓地点,Forward Pointer会指向数据行的摩登的情理地方。假如数额行更新之后,原有的存放空间可以容纳,那么SQL Server移除Forward Pointer,将数据行活动到原有的物理地方上。

1,聚焦索引其实就是表自身,SQL Server必需保持大意存款和储蓄顺序和逻辑存款和储蓄顺序生机勃勃致

4,重新建构集中索引之后,表的分区有五个

在风姿潇洒台钦定的Server上,NewSequentialId(卡塔尔(قطر‎发生的GUID是有序的,后发生的GUID比先产生的GUID数值要大,即该函数发生的GUID值是始终扩充(ever-increasing value)的,SQL Sever 保险发生的GUID比早先的GUID 大。NewSequentialID(卡塔尔(英语:State of Qatar)函数只可以用来表列的Default约束中。

Forward Pointer利于堆表数据的立异,可是,不便于堆表数据的查询。SQL Server通过Forward Pointer获取数据行的概况地方,再路由到相应的情理地方去读取数据行的数额,额外扩张叁遍Disk的查询操作。

在SQL Server中,Clustered Index能够分明Table的大意存款和储蓄,使Table的大要存储顺序和聚焦索引键的逻辑顺序保持一致。在对Table数据进行update时,假使更新集中索引键,引致数据行所在聚焦索引键必得移动,那时,SQL Server无法“原地更新”数据行,必需将数据行活动到其应有的情理地方上,Table的物理存款和储蓄顺序和集中索引键的逻辑顺序工夫保持意气风发致。SQL Server将Update命令拆分成等价的delete命令和insert 命令。

select *
from sys.partitions p 
where p.object_id=object_id(N'dbo.dt_partition',N'U')

万风流罗曼蒂克将NewSequentialID(卡塔尔 爆发的GUID作为clustered index key,那么Insert会将新的row 将插入到table的尾声,制止 page split,推荐使用NewSequentialID(卡塔尔(英语:State of Qatar)作为clustered index key。

移除Forward Pointer的操作:

示范:聚集索引键4被改过为8,那么,SQL Server将数据行5删除,然后再相应之处上插入数据行8。

澳门新萄京 6

NewSequentialID()  Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started. After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

  • 缩小数据文件,推行dbcc shrinkfile命令,SQL Server将移除堆表的具备Forward Pointer
  • 在堆表上创建clustered index
  • 将Forward Pointer指向的数据行删除
  • 在实施更新命令时,数据行宽度变窄,SQL Server移除Forward Pointer,将数据行活动到原有的概况地方上
  • 重新营造堆表,实行alter table rebuild命令,重新分配堆表的存款和储蓄空间

澳门新萄京 7

二,假若表的集中索引是利用Primary key clustered 来创立,并且primary key 正是分区列

NEWSEQUENTIALID() can only be used with DEFAULT constraints on table columns of type uniqueidentifier. You can use NEWSEQUENTIALID to generate GUIDs to reduce page splits and random IO at the leaf level of indexes. Each GUID generated by using NEWSEQUENTIALID is unique on that computer.

5,查看堆表中Forward Pointer的数目

后生可畏旦插入的位置上并未有剩余的蕴藏空间,那么,插入操作会招致页拆分,产生索引碎片,影响查询品质。

在SQL Server中,不可能改改节制,将普通表调换来分区表,有二种方法来落到实处,第生龙活虎种办法是:在剔除clustered constraint 时,将数据移动到分区scheme上;第三种方法,删除clustered constraint,在分区scheme上海重型机器厂建clustered constraint。

CREATE TABLE dbo.myTable 
(
    ColumnA uniqueidentifier DEFAULT NEWSEQUENTIALID(),
    ColumnB int,
    columnC varchar(10)
) 

insert into dbo.myTable(ColumnB,columnC)
values(1,'a'),(2,'c')

在SQL Server中,Index ID是0,表示堆表布局;Index ID是1,表示Clustered Index。

澳门新萄京 8

1,在剔除clustered 限准期,将数据移动到分区scheme上

澳门新萄京 9

澳门新萄京 10澳门新萄京 11

2,NonClustered Index的纸牌节点中,都富含Clustered Index键。

采取alter table drop constraint 命令,在剔除集中索引时,将数据移动到钦命的Partition Scheme上,那个时候该表形成分区的堆表:

三,ROWGUIDCOL 属性

select ips.database_id,
    ips.object_id,
    ips.index_id,
    ips.partition_number,
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_level,
    ips.index_depth,
    ips.record_count,
    ips.forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),object_id('heap_table_name','U'),0,null,'DETAILED') ips

比如说,在表上有七个目录:Clustered Index(c1,c2卡塔尔(قطر‎,Nonclustered Index(c2,c3卡塔尔(قطر‎,实际上,Nonclustered index的目录定义(c2,c3卡塔尔国include(c1卡塔尔国,即,在Nonclustered Index的叶子节点中,包括Clustered Index全数的Index Key。包蕴列和Index Key的分别在于,Index Key用于路由索引布局,而含有列用于重返数据,不提供找出效果。

ALTER TABLE schema_name . table_name
DROP [ CONSTRAINT ]  constraint_name  
[ WITH ( MOVE TO { partition_scheme_name(partition_column_name ) | filegroup | [default] } )]

ROWGUIDCOL 属性用于标志二个表列是 UniqueIdentifier 列,假诺二个表列被标识为RowGUIDCol,那么能够选用 $ROWGUID 来引用该列 ,三个表只可以有一列具备RowGUIDCol属性,该属性仅部分职能是:钦定 $ROWGUID 援用的UniqueIdentifier列。

View Code

由于Clustered Index“无所不为”,Clustered Index的索引键最佳成立在窄的,不变的,唯生龙活虎的和只拉长的数量列上。在成立Clustered Index时,最佳是有一无二索引(Unique Index)。窄的数额行会使每个Index page存款和储蓄越来越多的index key,SQL Server Engine定位到某豆蔻梢头行所通过的节点数更加少,即导航的Path越来越短,加载和查询速度更加快。

move to 选项的效益是将Table移动到新的Location中,假如新的location 是partition scheme,那么在剔除clustered 节制时,SQL Server将表数据移动到分区布局中,这种操作和使用 create table on partition scheme成立分区表的功能同样。

CREATE TABLE dbo.myTable_RowGUIDCol
(
    ColumnA uniqueidentifier ROWGUIDCOL not null
            constraint DF__myTable_RowGUIDCol_ColumnA DEFAULT NewID(),
    ColumnB int,
    columnC varchar(10)
) 

forwarded_record_count:Number of records in a heap that have forward pointers to another data location. (This state occurs during an update, when there is not enough room to store the new row in the original location.)

鉴于每三个nonclustered index的Index pages或index key columns中都会包涵Clustered Index key columns,假若Clustered Index key columns的大幅度相当的大,那会招致全部nonclustered index的索引树占用极大的储存空间,Disk IO越多,更新和询问操作都会变慢。

create table dbo.dt_partition_pk
(
ID int not null constraint pk__dt_partition_ID primary key clustered ,
Code int not null
)

alter table dbo.dt_partition_pk
drop constraint pk__dt_partition_ID
with( move to PS_int_Left(ID))

援用$ROWGUID查看被标识为RowGUIDCol 的column

参照文书档案:

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.

2,删除clustered 约束,在partition scheme上重建clustered 约束

select $ROWGUID
from dbo.myTable_RowGUIDCol

SQL Server: Inside a DELETE operation in Heaps

三,使用科学的数据类型,收缩调换的次数

create table dbo.dt_partition_pk
(
ID int not null constraint pk__dt_partition_ID primary key clustered ,
Code int not null
)

alter table dbo.dt_partition_pk
drop constraint pk__dt_partition_ID

alter table  dbo.dt_partition_pk
add constraint pk__dt_partition_ID primary key clustered(ID)
on PS_int_Left(ID);

澳门新萄京 12

在SQL Server中,对数码实行强制类型转变或隐式类型转换都亟待付出代价,所以,使用准确的数据类型,幸免类型转变是十三分供给的。比方,尽管存款和储蓄的数量格式是‘yyyy-mm-dd MM:HH:SS’,固然字符串类型和Datetime类型可以隐式调换,不过选用字符串类型 varchar(19卡塔尔(قطر‎或 nvarchar(19卡塔尔(قطر‎存款和储蓄是不行不明智的,不仅仅浪费存款和储蓄空间,而且隐式调换对质量有负功能。

三,将堆表调换元素区表

 

四,不感到奇数据类型所占领的字节数

使堆表调换来分区,只供给在堆表上创办三个分区的clustered index

参照文书档案:

数据类型差不离分成多种:数值类型,日期和岁月项目,字符串类型,GUID,使用DataLength(卡塔尔可以查阅跋扈数据类型的变量所占用的字节数量

create table dbo.dt_partition_heap
(
ID int not null,
Code int not null
)

create clustered index cix_partition_heap_ID
on dbo.dt_partition_heap(ID)
on PS_int_Left(ID)

Uniqueidentifier vs. IDENTITY

1,数值类型

四,普通表转产生分区表的长河是不可逆的,普通表能够转形成分区表,而分区表不可能转形成普通表。

uniqueidentifier (Transact-SQL).aspx)

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

普通表存款和储蓄的Location是FileGroup,分区表存款和储蓄的Location是Partition Scheme,在SQL Server中,存款和储蓄表数据的Location叫做Data Space。通过在Partition Scheme上创设Clustered Index ,能够将曾经存在的普通表转产生partition table,可是,将Clustered index删除,表仍然为分区表,转变进度(将普通表转变来分区表)是不可逆的,四个Partition Table 是不可能转变成普通表的,固然通过联合分区,使Partiton Table 只存在二个Partition,这些表的照样是Partition Table,这一个Table的Data Space 是Partition Scheme,而不会转产生File Group。

NEWID (Transact-SQL).aspx)

对此小数类型,decimal(p,s卡塔尔(قطر‎表示准确的小数类型,float(n卡塔尔国表示相像的小数类型,常用来表示百分比,除法的结果,有两类别型float(24卡塔尔(قطر‎占用4B,float(53卡塔尔占用8B,参考《SQL Server的小数数值类型(float 和 decimal)用法》。

从 sys.data_spaces 中查看Data Space ,共有两种类型,分别是FG 和 PS。FG是File Group,意味着数据表的数目存款和储蓄在File Group分配的积累空间,一个Table 只好存在于三个FileGroup中。PS 是Partition Scheme,意味着将数据布满式存款和储蓄在不相同的File Groups中,存款和储蓄数据的File Group是依赖Partition column值的范围来分配的。对于分区表,SQL Server从钦定的File Group分配存款和储蓄空间,就算贰个Table只好钦命一个Partition Scheme,不过其数据却布满在多少个File Groups中,这几个File Groups由Partition Scheme钦命,能够等效,也足以分歧。

NEWSEQUENTIALID (Transact-SQL).aspx)

2,日期和时间项目

查阅Table的Data Space,通过索引的data_space_id 字段来查看各种索引(聚焦索引是表本人)数据的仓库储存空间:

  • date表示日期,占用3B;
  • Datetime2(n卡塔尔(英语:State of Qatar),依照时间的纳秒部分来显明占用的字节数量:当n是1,或2时,占用6B;当n是3,或4时,占用7B;当n是5,6,或7时,占用8B;
  • datetime占用8B,提出选择datetime2(n卡塔尔来替代datetime;
select o.name as TableName,o.type_desc,
    i.name as IndexName,
    i.index_id,i.type_desc,i.data_space_id,
    ds.name as DataSpaceName,ds.type_desc
from sys.indexes i
inner join sys.objects o
    on o.object_id=i.object_id
inner join sys.data_spaces ds 
    on i.data_space_id=ds.data_space_id
where i.object_id=object_id(N'[dbo].[dt_test_partition]') 
and i.index_id=0

3,字符类型

在分区在此以前,查看Data_space是Name是 Primary File Group

建议选择变长字符类型,varchar和nvarchar,前面一个占用的字节是前者生机勃勃倍;如若数额中都以拉丁字母,使用varchar越来越好。对于LOB数据类型,提出接收varchar(max卡塔尔国和 nvarchar(max卡塔尔国,单列能够存款和储蓄最大2GB的数目。

澳门新萄京 13

变长字符类型 varchar(n卡塔尔国 和 nvarchar(n卡塔尔国,N值的最大值是微微?举个例子:

在分区之后,查看Table的 Data Space 是ps_int Partition Scheme

declare @nv_max varchar(9000)
declare @nv_min varchar(0)

select @nv_max
select @nv_min

澳门新萄京 14

予以 类型 'varchar' 的深浅(9000卡塔尔国超过了随机数据类型的最大允许值(8000卡塔尔。 钦赐的长度或精度 0 无效。

日前不能将Table的Data Space 转变成FG

简单来讲,对于varchar(N卡塔尔国,N的取值范围的上限是8000,下限是1,可变长度单字节字符类型的取值范围是[1-8000];

 

  • 对于varchar(n卡塔尔(英语:State of Qatar),n的最大值是8000;
  • 对此nvarchar(n卡塔尔国,n的最大值是4000;
  • 任凭用于变量,依然用来table column,都不可能超过8000Bytes,这种范围是由SQL Server的一个Page=8KB决定的。

假定期存款款和储蓄的多寡占用的字节数当先8000,那么必需利用LOB类型:varchar(max卡塔尔国 和 nvarchar(max卡塔尔,max突破长度8000Byte的范围,达到LOB的最大值2GB。

The size of values specified with MAX can reach the maximum size supported by LOB data, which is currently 2GB. Because the max data types can store LOB data as well as regular row data, you are recommanded to use these data types in future development in place of the text, ntext, or image tyes, which MS has indicated will be removed in a future version.

4,UniqueIdentifier数据类型

UniqueIdentifier数据类型占用16B,GUID的格式是8-4-4-4-12,即'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx',不要使用varchar来积累GUID。

比方说,使用varchar来存储GUID,将会浪费20B

declare @ui uniqueidentifier
declare @vc varchar(max)

set @ui=newid()
set @vc=cast(@ui as varchar(max))

select @ui,@vc,datalength(@ui),datalength(@vc)

若果表的数码行总的数量是5千万,那么使用varchar来积攒GUID将会浪费:953MB。在数据类型的采纳上,必需睚眦必报,能省就省。

五,示例

效仿贰个气象:业务人士须求深入分析帖子,要求仓库储存的字段:PostID,AuthorName,PostTitle,PostUKugaL,PostContent,PostedTime。

在使用ETL同步社区爬虫数据时,日常会附加增加几个Column:DataCreatedTime和DataUpdatedTime,用于存款和储蓄新建数据行的时光和最终一次改正数据行的流年。

1,社区深入分析,平常涉及海量的数码,使用数据压缩(data_compression=page),升高查询品质。

2,增加代理键,使用代理键作为主键。

3,U中华VL使用varchar类型,对于AuthorName,Title和Content须求选取unicode类型来储存。

4,对于时间等级次序,精度不会超级高,使用最节省的数据类型 datetime2(2卡塔尔来囤积,分斤掰两。

5,将最占空间的PostContent和主表Posts分开,实际上是笔直分区,便于连忙对主表Posts进行询问和深入分析。

create table dbo.Posts
(
PostID bigint identity(1,1) not null,
OriginalPostID bigint not null,
AuthorID int not null,
Title nvarchar(256) not null,
url varchar(2048) not null,
PostedTime datetime2(2) not null,
IsDeleted bit not null,
DataCreatedTime datetime2(2) not null,
DataUpdatedTime datetime2(2) not null,
constraint PK__Posts_ID primary key clustered(PostID)
)
with(data_compression=page);

create table dbo.Authors
(
AuthorID int Identity(1,1) not null,
OriginalAuthorID int not null,
Name nvarchar(128) not null,
DataCreatedTime datetime2(2) not null,
DataUpdatedTime datetime2(2) not null,
constraint PK__Authors_AuthorID primary key clustered(AuthorID)
)
with(data_compression=page);

create table dbo.PostContent
(
PostID int not null,
Content nvarchar(max) not null,
DataCreatedTime datetime2(2) not null,
DataUpdatedTime datetime2(2) not null,
constraint PK__PostContent_PostID primary key clustered(PostID)
)
with(data_compression=page);

 

引入阅读《Performance Considerations of Data Types》:
A clustered index created as part of a primary key will, by definition, be unique. However, a clustered index created with the following syntax,

CREATE CLUSTERED INDEX <index_name>
ON <schema>.<table_name> (<key columns>);

will not be unique unless unique is explicitly declared, i.e.

CREATE UNIQUE CLUSTERED INDEX <index_name>
ON <schema>.<table_name> (<key columns>);

In order for SQL Server to ensure it navigates to the appropriate record, for example when navigating the B-tree structure of a non-clustered index, SQL Server requires every row to have an internally unique id. In the case of unique clustered index, this unique row id is simply the clustered index key value. However, as SQL Server will not require a clustered index to be unique - that is, it will not prevent a clustered index
from accepting duplicate values - it will ensure uniqueness internally by adding a 4-byte uniquifier to any row with a duplicate key value.

In many cases, creating a non-unique clustered index on a unique or mostly unique column will have little-to-no impact. This is because the 4-byte overhead is only added to duplicate instances of an existing clustered key value. An example of this would be creating a non-unique clustered index on an identity column. However, creating a non-unique clustered index on a column with many duplicate values, perhaps on a column of date data type where you might have thousands of records with the same clustered key value, could result in a significant amount of internal overhead.
Moreover, SQL Server will store this 4-byte uniquifier as a variable-length column. This is significant in that a table with all fixed columns and a large number of duplicate clustered values will actually incur 8 bytes of overhead per row, because SQL Server requires 4 bytes to manage this variable column (2 bytes for the count of variable-length columns in the row and 2 bytes for the offset of the the variable-length column of the uniquifier column). If there are already variable-length columns in the row, the overhead is only 6 bytes—two for the offset and four for the uniquifier value. Also, this value will be present in all nonclustered indexes too, as it is part of the clustered index key.

 

本文由澳门新萄京发布于数据库,转载请注明出处:对现有表分区,选择合适的数据类型

上一篇:Server之纵表与横表互转,经典行专列 下一篇:没有了
猜你喜欢
热门排行
精彩图文