在SQLSever2005 Manger
Studio中创建数据库和表时,时而会出现表中字段无法输入汉字的情况,后将系统默认输入法设置成中文输入法了没有再出现该问题。但没有明白深层原因,在此引用下面文章中的解决方法:

–1. 会话中某个表已将此属性设置为ON,当为另一个表发出了SET
IDENTITY_INSERT ON 句时将出错
–测试的表
CREATE TABLE ta(id int IDENTITY(1,1),col int)
CREATE TABLE tb(id int IDENTITY(1,1),col int)
GO

《Microsoft Sql server 2008 Interna》读书笔记订阅地址:
 

–设置 IDENTITY_INSERT 属性
SET IDENTITY_INSERT ta ON
SET IDENTITY_INSERT tb ON
GO

 

/*======================================================*/

 

问题重现:

–2. 如果插入记录的标识值大于表的当前标识值,则SQL
Server自动将新插入值作为当前标识值使用
–测试的表
CREATE TABLE tb(id int IDENTITY(1,1),col int)

《Microsoft Sql server 2008 Interna》索引目录:

1.  我们建立如下三个测试表:

–强制在表中插入标识值
SET IDENTITY_INSERT tb ON
INSERT tb(id,col) VALUES(10,1)
澳门新葡萄京所有网站,SET IDENTITY_INSERT tb OFF

 

CREATE TABLE ta(

INSERT tb(col) VALUES(2)
SELECT * FROM tb
/*–结果
id           col

《Microsoft Sql server 2008
Internal》读书笔记–目录索引

    id int IDENTITY,


  这一节我们继续了解稀疏列的物理存储。

    col varchar(50)

10          1
11          2
–*/
GO

  ■物理存储

)

/*======================================================*/

在某种角度上,你可以稀疏列作为存储,同时使用列集显示。如此,没有值的特殊列,它不会被列出,也不会占用空间;但有值的稀疏列比正常的null列得花费更多的空间。

 

–3.
如果插入记录的标识值小于表的当前标识值,则表的当前标识值不受新插入值的影响
–测试的表
CREATE TABLE tb(id int IDENTITY(1,1),col int)
INSERT tb VALUES(1)
INSERT tb VALUES(2)

用non-sparse列定义的表:

CREATE TABLE tb(

–强制在表中插入标识值
SET IDENTITY_INSERT tb ON
INSERT tb(id,col) VALUES(1,11)
SET IDENTITY_INSERT tb OFF

 

    col varchar(50),

INSERT tb(col) VALUES(3)
SELECT * FROM tb
/*–结果
id           col

ID sc1 sc2 sc3 sc4 sc5 sc6 sc7 sc8 sc9
 1  1                9
 2    2    4    6      
 3              7    
 4  1        5        
 5        4        8  
 6      3            9
 7         5    7    
 8    2            8  
 9      3      6      

    id int IDENTITY


 用sparse列定义的表:

)

1           1
2           2
1           11
3           3
–*/

ID sparse columns
1 (sc1,sc9)(1,9)
2 (sc2,sc4)(2,4)
3 (sc6,sc7)(6,7)
4 (sc1,sc5)(1,5)
5 (sc4,sc8)(4,8)
6 (sc3,sc9)(3,9)
7 (sc5,sc7)(5,7)
8 (sc3,sc8)(2,8)
9 (sc3,sc7)(3,7)

 

 SQL Server用一个叫做稀疏向量(sparse vector)的行来跟踪Sparse列的存储。
稀疏向量的里的字节:

CREATE TABLE tc(

Name Number of Bytes Meaning
Complex column Header 2 05
Sparse column Count 2 numbers of sparse columns
Column ID Set 2* the number of sparse columns two bytes for the column ID of each colun in the table with a value stored in the sparse vector
ColumnOffset Table 2* the number of sparse columns two bytes for the offset of the ending position of each sparse column
Sparse Data Depends on actural values Data

    col1 varchar(50),

我们看一个例子:

    col2 as col1

USE testdb;
GO
IF EXISTS (SELECT * FROM sys.tables  
   WHERE name = ‘sparse_bits’)
  DROP TABLE sparse_bits;
GO
CREATE TABLE sparse_bits

c1 int IDENTITY,
c2 varchar(4),
c3 char(4) SPARSE,
c4 varchar(4) SPARSE
);
GO
INSERT INTO sparse_bits SELECT ‘aaaa’, ‘bbbb’, ‘cccc’;
INSERT INTO sparse_bits SELECT ‘dddd’, null, ‘eeee’;
INSERT INTO sparse_bits SELECT ‘ffff’, null, ‘gg’;
GO

)

现在我们使用DBCC IND命令查找存储三行的数据页的页数量,用DBCC
Page查看页的字节。

 

DBCC IND(testdb, sparse_bits, -1);
GO

2.  在SQL Server 2005 Manger Studio
中打开上述三个表进行数据录入,你会发现是能录入中文的

澳门新葡萄京所有网站 1

 

澳门新葡萄京所有网站 2

3. 
那么错误故障是如何再现的呢?继续测试,在上面建立的测试表中,每个表都有一个只读的列,如果把你把光标移动到该列,再移回到可写列,你就会发现,无法开启中文输入法了。

稀疏向量的字节偏移:

 

澳门新葡萄京所有网站 3

结论:

字节交换后的值:

在 SQL Server 2005 Manger Studio
中录入数据的时候,不要把光标移动到只读的列中,否则你的中文就不要想录入了

澳门新葡萄京所有网站 4

另外,设计表的时候可能也得注意,如果你想在SQL Server 2005 Manger Studio
中录入中文,则不要把只读列设置为第1列,比如上面的测试表 ta,
如果你已经录入了数据,再用SQL Server 2005 Manger Studio
打开的时候,你会发现光标是定位在第1条记录的第1列(标识列,也是只读列)的,这样会导致你根本无法录入中文

关于sparse vector,请注意以下几点:

 

1、sparse vector不会出现关于null值列的信息

这应该算是SQL Server 2005 Manger Studio 的一个BU吧

2、对于稀疏向量,存储固定长度和可变长度的字符串没有区别。但是一个稀疏varchar列(不符合8060字节)能被存储在行溢出数据,而一个稀疏char列不可以。

 

3、因为只有两个字节存储稀疏列的数量,这就是数量的限制。

小提示:

4、两字节的复杂列的头部显示可能是另外一个复杂列的类型,可能是向后指针(back-Poiner)(对应前文的转向指针forward-pointer)

在 SQL Server 2005 Manger Studio
中,只读列显示的数据是灰色的(比正常列的颜色稍淡一些,仔细看一下就能区分出来

  ■元数据

 

 非常小的特殊元数据被用来支持稀疏列。目录视图sys.columns包含两个列,用来跟踪表的Sparse
column,每个列仅有两个可能的值0或1:is_Sparse和is_column_set,属性函数有两个值与稀疏列有关:isSarse和isColumnSet

本文来自CSDN博客,转载请标明出处:

使用Sparse的列:

 

SELECT OBJECT_NAME(object_id) as ‘Table’, name as ‘Column’, is_sparse, is_column_set
FROM sys.columns
WHERE OBJECT_NAME(object_id) like ‘%sparse%’;

 

使用列集的表:

 

SELECT OBJECT_NAME(object_id) as ‘Table’, name as ‘Column’
FROM sys.columns
WHERE COLUMNPROPERTY(object_id, name, ‘IsColumnSet’) = 1;

 

 ■稀疏列存储方面的节省

 

 Sparse column
主要是为了解决大部分为null值的表而设置的。但同时那些不是null的列却将要花费更多的空间存储开销。我们用一个例子(插入10万数据)说明:

 

澳门新葡萄京所有网站 5澳门新葡萄京所有网站 6代码

 USE testdb;
GO
SET NOCOUNT ON;
GO
IF EXISTS (SELECT * FROM sys.tables  
   WHERE name = ‘sparse_nonulls_size’)
  DROP TABLE sparse_nonulls_size;
GO
CREATE TABLE sparse_nonulls_size
(col1 int IDENTITY,
 col2 datetime SPARSE,
 col3 char(10) SPARSE
 );
GO
IF EXISTS (SELECT * FROM sys.tables  
   WHERE name = ‘nonsparse_nonulls_size’)
  DROP TABLE nonsparse_nonulls_size;
GO
CREATE TABLE nonsparse_nonulls_size
(col1 int IDENTITY,
 col2 datetime,
 col3 char(10)
 );
GO
IF EXISTS (SELECT * FROM sys.tables  
   WHERE name = ‘sparse_nulls_size’)
  DROP TABLE sparse_nulls_size;
GO
CREATE TABLE sparse_nulls_size
(col1 int IDENTITY,
 col2 datetime SPARSE,
 col3 char(10) SPARSE
 );
GO
IF EXISTS (SELECT * FROM sys.tables  
   WHERE name = ‘nonsparse_nulls_size’)
  DROP TABLE nonsparse_nulls_size;
GO
CREATE TABLE nonsparse_nulls_size
(col1 int IDENTITY,
 col2 datetime,
 col3 char(10)
 );
GO
DECLARE @num int
SET @num = 1
WHILE @num < 100000
BEGIN
  INSERT INTO sparse_nonulls_size
 SELECT GETDATE(), ‘my message’;
  INSERT INTO nonsparse_nonulls_size
 SELECT GETDATE(), ‘my message’;
  INSERT INTO sparse_nulls_size
 SELECT NULL, NULL;
  INSERT INTO nonsparse_nulls_size
 SELECT NULL, NULL;
  SET @num = @num + 1;
END;
GO

现在我们看一下每个表的页面数。下面的元数据查询(使用sys.allocation_units视图):

澳门新葡萄京所有网站 7

注意:页面数最小的是有(null值)稀疏列的表。与没有稀疏列的表使用相同页面数的是列有null值或没有但是数据被定义成固定长度的表。这个空间比那个有null的稀疏列大两倍还多。最可怕的是定义了稀疏列,但这个列有not
null值。

更多,请看MSDN:

Fixed-Length Data Types

 

 

Data type Nonsparse bytes Sparse bytes NULL percentage

bit

0.125

4.125

98%

tinyint

1

5

86%

smallint

2

6

76%

int

4

8

64%

bigint

8

12

52%

real

4

8

64%

float

8

12

52%

smallmoney

4

8

64%

money

8

12

52%

smalldatetime

4

8

64%

datetime

8

12

52%

uniqueidentifier

16

20

43%

date

3

7

69%

Precision-Dependent–Length Data Types

 

 

Data type Nonsparse bytes Sparse bytes NULL percentage

datetime2(0)

6

10

57%

datetime2(7)

8

12

52%

time(0)

3

7

69%

time(7)

5

9

60%

datetimetoffset(0)

8

12

52%

datetimetoffset (7)

10

14

49%

decimal/numeric(1,s)

5

9

60%

decimal/numeric(38,s)

17

21

42%

vardecimal(p,s)

Use the decimal type as a conservative estimate.

   

Data-Dependent–Length Data Types

 

 

Data type Nonsparse bytes Sparse bytes NULL percentage

sql_variant

Varies with the underlying data type

   

varchar or char

2*

4*

60%

nvarchar or nchar

2*

4*+

60%

varbinary or binary

2*

4*

60%

xml

2*

4*

60%

hierarchyid

2*

4*

60%

*The length is equal to the average of the data that is contained in
the type, plus 2 or 4 bytes.

通常推荐的做法是:当你断定使用稀疏列能节省20%-40%的空间时使用稀疏列。至此,稀疏列告一段落,下一篇请看数据压缩。