22,210
社区成员
发帖
与我相关
我的任务
分享
/****** Object: Table [dbo].[ISSUE_D2] Script Date: 12/14/2012 12:11:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ISSUE_D2](
[SequenceNumber] [int] NOT NULL,
[ISSUE_D2_ID] [uniqueidentifier] NOT NULL,
[ISSUE_DESCRIPTION] [ntext] NOT NULL,
[WITH_PHOTO] [ntext] NOT NULL,
[STATUS] [nvarchar](20) NOT NULL,
[ISSUE_TYPE_ID] [uniqueidentifier] NOT NULL,
[REPLY_DESCRIPTION] [ntext] NOT NULL,
[VERIFICATION] [int] NOT NULL,
[VERIFICATION_DESCRIPTION] [ntext] NOT NULL,
[RETURN_NUM] [int] NOT NULL,
[TRANSFER_DOC_NO] [nvarchar](20) NOT NULL,
[CLOSED_DATE] [datetime2](7) NOT NULL,
[CLOSED_DAYS] [int] NOT NULL,
[VERIFICATION_DATE] [datetime2](7) NOT NULL,
[CreateDate] [datetime2](7) NOT NULL,
[LastModifiedDate] [datetime2](7) NOT NULL,
[CreateBy] [uniqueidentifier] NOT NULL,
[LastModifiedBy] [uniqueidentifier] NOT NULL,
[Version] [timestamp] NOT NULL,
[ISSUE_ID] [uniqueidentifier] NOT NULL,
[mmm] [bit] NOT NULL,
CONSTRAINT [PK_ISSUE_D2] PRIMARY KEY NONCLUSTERED
(
[ISSUE_D2_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'SequenceNumber'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主鍵' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'ISSUE_D2_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'議題描述' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'ISSUE_DESCRIPTION'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附圖' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'WITH_PHOTO'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'議題狀態' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'STATUS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'議題分類' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'ISSUE_TYPE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'回復說明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'REPLY_DESCRIPTION'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'反映人驗證狀態' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'VERIFICATION'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'驗證說明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'VERIFICATION_DESCRIPTION'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'驗證退回次數' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'RETURN_NUM'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'轉單單號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'TRANSFER_DOC_NO'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'結案日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'CLOSED_DATE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'結案天數' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'CLOSED_DAYS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'驗證完成日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'VERIFICATION_DATE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'LastModifiedDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建者' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'CreateBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最后修改者' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'LastModifiedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'版本号,不要随意更改' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'Version'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2', @level2type=N'COLUMN',@level2name=N'ISSUE_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'议题明细/CHT/議題明細' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ISSUE_D2'
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ((0)) FOR [SequenceNumber]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [ISSUE_D2_ID]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT (' ') FOR [ISSUE_DESCRIPTION]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT (' ') FOR [WITH_PHOTO]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT (' ') FOR [STATUS]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [ISSUE_TYPE_ID]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT (' ') FOR [REPLY_DESCRIPTION]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ((0)) FOR [VERIFICATION]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT (' ') FOR [VERIFICATION_DESCRIPTION]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ((0)) FOR [RETURN_NUM]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT (' ') FOR [TRANSFER_DOC_NO]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ('1900-01-01 00:00:00') FOR [CLOSED_DATE]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ((0)) FOR [CLOSED_DAYS]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ('1900-01-01 00:00:00') FOR [VERIFICATION_DATE]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ('1900-01-01 00:00:00') FOR [CreateDate]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ('1900-01-01 00:00:00') FOR [LastModifiedDate]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [CreateBy]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [LastModifiedBy]
GO
ALTER TABLE [dbo].[ISSUE_D2] ADD CONSTRAINT [DF_ISSUE_D2_mmm] DEFAULT ((0)) FOR [mmm]
GO
--测试
--使用临时数据库
use tempdb;
go
--创建测试表tb_test
if object_id('tb_test') is not null
drop table tb_test;
go
create table tb_test
(
col1 uniqueidentifier,
col2 nchar(4000),
col3 nvarchar(4000),
col4 nvarchar(4000),
constraint PK_TEST primary key(col1)
);
go
/*
警告: 行的最大长度超过了所允许的上限(8060 个字节)。
对于某些大值组合,插入/更新操作将失败。
分析:因为nchar和nvarchar数据类型使用两个字节存储一个字符,
所以各列的最大长度分别为:16B、8000B、8000B、8000B。
共计24016B(不包括额外的磁盘开销),超过了行的最大
长度8060B,但由于定长数据nchar和uniqueidentifier的
总长度为8016,未超过8060的限制,而变长数据可以存储到
行溢出数据中,因此SQL Server并不阻止此创建语句的执行,
但给出警告,因为存在某些大值组合确实会导致行超过8060
的限制。(此种情况已在25楼的时候讨论过,这里略过)
*/
--查看表中所有行的分配情况
select c.name as column_name, c.column_id,
pc.max_inrow_length, pc.system_type_id, pc.leaf_offset
from sys.system_internals_partition_columns pc
inner join sys.partitions p
on p.partition_id = pc.partition_id
inner join sys.columns c
on c.column_id = pc.partition_column_id and
c.object_id = p.object_id
where p.object_id = object_id('tb_test');
/*
column_name column_id max_inrow_length system_type_id leaf_offset
------------- ----------- ---------------- -------------- -----------
col1 1 16 36 4
col2 2 8000 239 20
col3 3 8000 231 -1
col4 4 8000 231 -2
从以上数据我们可以看出,leaf_offset是列数据在行内相对于行头的偏移量
即:col1从4字节开始,到19字节结束,共16字节。
col2从20字节开始,因为是定长数据,长度为8000字节
col3、col4属于变长数据,实际的长度因不同的记录不同而不同,这里
不能指定准确的偏移量,-1、-2表示的是第一变长列和第二变长列。
*/
--插入记录
declare @string nchar(4000);
set @string = replicate('A', 4000);
insert into tb_test
select newid(), @string, @string, @string;
go
/*
消息 511,级别 16,状态 1,第 44 行
不能创建大小为 8077 的行,该大小大于所允许的最大值 8060。
分析:假设这条记录能成功插入,计算记录的长度
状态位占用2B,列数目的位置记录占用2B,列数目占用2B,
空值位图占用1B,变长列数目占用2B,两个变长列位置偏
移量占用4B,col1长16B,col2长8000B, col3只能是指针
24B,col4也只能是指针24B,共计8077字节,因超过8060B
长度限制,SQL Server不予插入此记录
*/
--为了知道数据内部存储的细节,先插入一条不超过限制的记录
declare @string nvarchar(5);
set @string = replicate('A', 5);
insert into tb_test
select newid(), @string, @string, @string;
go
/*
插入成功
(1 行受影响)
分析:计算记录的长度
状态位占用2B,列数目的位置记录占用2B,列数目占用2B,
空值位图占用1B,变长列数目占用2B,两个变长列位置偏
移量占用4B,col1长16B,col2长8000B, col3占用10B,
col4占用10B,共计8049B,因未超过8060限制,给予插入。
*/
--查看表占用的页面信息
dbcc ind(tempdb, tb_test, -1);
go
/*
PageFID PagePID ObjectID iam_chain_type PageType
------- ----------- ----------- -------------------- --------
1 93 245575913 In-row data 10
1 77 245575913 In-row data 1
1 174 245575913 Row-overflow data 10
1 120 245575913 Row-overflow data 3
1 41 245575913 Row-overflow data 3
分析: PageType=1代表行内数据页,PageType=3代表行溢出数据页
虽然之前插入超过限制的数据并没有成功,但是SQL Server
已经分配了行溢出数据的页,即col3和col4原本要放入行溢
出数据页内,但由于行内只存放指针的空间都不够,于是之前
插入的数据即使没有成功,但行溢出页已经分配。
*/
--查看行内数据行实际使用情况
dbcc traceon(3604);
go
dbcc page(tempdb, 1, 77, 1);
go
/*
结果略
分析略
细节显示与上面的分析一致
*/
--删除已插入数据,以便后面的测试
delete tb_test;
/*
执行成功
(1 行受影响)
*/
--修改表结构,修改col2
alter table tb_test
alter column col2 nvarchar(max);
go
/*
警告: 已经创建表 'tb_test',但是它的最大行大小超过了允许的
最大字节数 8060。如果得到的行超过此大小限制,则对此
表的 INSERT 或 UPDATE 操作将失败。
*/
--查看表中所有行的分配情况
select c.name as column_name, c.column_id,
pc.max_inrow_length, pc.system_type_id, pc.leaf_offset
from sys.system_internals_partition_columns pc
inner join sys.partitions p
on p.partition_id = pc.partition_id
inner join sys.columns c
on c.column_id = pc.partition_column_id and
c.object_id = p.object_id
where p.object_id = object_id('tb_test');
go
/*
column_name column_id max_inrow_length system_type_id leaf_offset
------------- ----------- ---------------- -------------- -----------
col1 1 16 36 4
col2 2 8000 231 -3
col3 3 8000 231 -1
col4 4 8000 231 -2
从以上数据我们可以看出,col2的数据类型已经改变,并且修改后的col2成为
第三个变长列,物理上紧跟col4的后面,至于上面的警告怎么出现,将在后面
的测试中说明
*/
--继续插入一条数据查看物理存储细节
declare @string nvarchar(1);
set @string = replicate('A', 1);
insert into tb_test
select newid(), @string, @string, @string;
go
/*
执行成功
(1 行受影响)
*/
--查看物理页存储细节
dbcc traceon(3604);
go
dbcc page(tempdb, 1, 77, 1);
go
/*
通过第3、4字节可以在偏移量8020字节后找到两字节的列数目,
通过查看可以看到有5列数据存在(元数据显示只有4列),另外
可以看到变长列只有3个,数据显示是col2、col3、col4就存在
这3个变长列中。
通过数据还可以看出修改之前的8000字节nchar数据长度还预留
在原先的位置,也就是说还在占用行的长度,因此除去定长的
数据以及额外的开销,留给3个变长列的长度为8060-8031= 29B
也就是说实际上以上的修改实际上等同于增加1个nvarchar(max)
的列(物理上有5个列,而元数据只有4个列),因此SQL Server对
增加一个列的这种行为进行了告警,并且这种超过长度限制的情况
是可能存在的。
*/
--删除测试数据
delete tb_test;
go
--继续修改col2
alter table tb_test
alter column col3 nvarchar(max);
go
/*
执行成功
(1 行受影响)
*/
--查看表中所有行的分配情况
select c.name as column_name, c.column_id,
pc.max_inrow_length, pc.system_type_id, pc.leaf_offset
from sys.system_internals_partition_columns pc
inner join sys.partitions p
on p.partition_id = pc.partition_id
inner join sys.columns c
on c.column_id = pc.partition_column_id and
c.object_id = p.object_id
where p.object_id = object_id('tb_test');
go
/*
column_name column_id max_inrow_length system_type_id leaf_offset
------------- ----------- ---------------- -------------- -----------
col1 1 16 36 4
col2 2 8000 231 -3
col3 3 8000 231 -4
col4 4 8000 231 -2
从以上数据我们可以看出,col3的数据类型已经改变,并且修改后的col2成为
第4个变长列,物理上紧跟col2的后面
*/
--继续插入一条数据查看物理存储细节
declare @string nvarchar(1);
set @string = replicate('A', 1);
insert into tb_test
select newid(), @string, @string, @string;
go
/*
执行成功
(1 行受影响)
*/
--查看物理页存储细节
dbcc traceon(3604);
go
dbcc page(tempdb, 1, 77, 1);
go
/*
通过数据可以看到,行的物理列数有6个,而变长列有4个
但第一个变长列没有内容
可以看出修改之前的偏移量字节还继续占用,增加了两字节的
第4变长列的偏移量,留给3个变长列的长度为8060-8033= 27B
*/
--删除测试数据
delete tb_test;
go
--继续修改col4
alter table tb_test
alter column col4 nvarchar(max);
go
/*
与上面的测试一样
通过数据可以看到,行的物理列数有7个,而变长列有5个
但第1、2个变长列没有内容
可以看出修改之前的偏移量字节还继续占用,增加了两字节的
第5变长列的偏移量,留给3个变长列的长度为8060-8035= 25B
*/
declare @string nvarchar(4000)
select @string = replicate('A',4000)
insert into tb_test
select newid(),@string, @string, @string
go
/*
消息 511,级别 16,状态 1,第 3 行
不能创建大小为 8107 的行,该大小大于所允许的最大值 8060。
语句已终止。
因为只剩下25B的空间,因此3个变长列都将被移到行溢出数据
中,但是即使只保存指针也需要24*3=72B的空间,于是SQL Server
报错,8035 + 72 = 8017B > 8060
*/
--重建索引
alter index PK_TEST on tb_test rebuild;
go
declare @string nvarchar(4000)
select @string = replicate('A',4000)
insert into tb_test
select newid(),@string, @string, @string
go
/*
执行成功
(1 行受影响)
*/
USE TEMPDB
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
COL1 UNIQUEIDENTIFIER PRIMARY KEY
,COL2 NCHAR(4000)
,COL3 NVARCHAR(4000)
,COL4 NVARCHAR(4000)
)
GO
DECLARE @STR NVARCHAR(4000)
SELECT @STR=REPLICATE('A',8000)
INSERT INTO TB
SELECT NEWID(),@STR,@STR,@STR
GO
--警告: 行的最大长度超过了所允许的上限(8060 个字节)。对于某些大值组合,插入/更新操作将失败。
--消息 511,级别 16,状态 1,第 3 行
--不能创建大小为 8077 的行,该大小大于所允许的最大值 8060。
ALTER TABLE TB ALTER COLUMN COL2 NVARCHAR(MAX)
ALTER TABLE TB ALTER COLUMN COL3 NVARCHAR(MAX)
ALTER TABLE TB ALTER COLUMN COL4 NVARCHAR(MAX)
GO
DECLARE @STR NVARCHAR(4000)
SELECT @STR=REPLICATE('A',4000)
INSERT INTO TB
SELECT NEWID(),@STR,@STR,@STR
GO
DROP TABLE TB
GO
--警告: 已经创建表 'TB',但是它的最大行大小超过了允许的最大字节数 8060。如果得到的行超过此大小限--制,则对此表的 INSERT 或 UPDATE 操作将失败。
--消息 511,级别 16,状态 1,第 3 行
--不能创建大小为 8107 的行,该大小大于所允许的最大值 8060。
exec sp_tableoption '楼主的数据表', 'large value types out of row', 1;
USE TEMPDB
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
COL1 UNIQUEIDENTIFIER PRIMARY KEY
,COL2 NCHAR(4000)
,COL3 NVARCHAR(4000)
,COL4 NVARCHAR(4000)
)
GO
DECLARE @STR NVARCHAR(4000)
SELECT @STR=REPLICATE('A',8000)
INSERT INTO TB
SELECT NEWID(),@STR,@STR,@STR
GO
ALTER TABLE TB ALTER COLUMN COL2 NVARCHAR(MAX)
ALTER TABLE TB ALTER COLUMN COL3 NVARCHAR(MAX)
ALTER TABLE TB ALTER COLUMN COL4 NVARCHAR(MAX)
GO
DECLARE @STR NVARCHAR(4000)
SELECT @STR=REPLICATE('A',4000)
INSERT INTO TB
SELECT NEWID(),@STR,@STR,@STR
GO
DROP TABLE TB
GO
CREATE TABLE TB(
COL1 UNIQUEIDENTIFIER PRIMARY KEY
,COL2 NVARCHAR(max)
,COL3 NVARCHAR(max)
,COL4 NVARCHAR(max)
)
go
DECLARE @STR NVARCHAR(4000)
SELECT @STR=REPLICATE('A',4000)
INSERT INTO TB
SELECT NEWID(),@STR,@STR,@STR