CREATE TABLE testDataType
(
"bit" bit,
"tinyint" tinyint,
"smallint" smallint,
"int" int,
"bigint" bigint,
"decimal(p,s)" decimal(10,4),
"numeric(p,s)" numeric(10,6),
"smallmoney" smallmoney,
"money" money,
"float" float,
"real" real,
"datetime" datetime,
"date" date,
"time" time,
"smalldatetime" smalldatetime,
"datetime2" datetime2,
"datetimeoffset" datetimeoffset,
"char(n)" char(4),
"varchar(n)" varchar(4),
"nchar(n)" nchar(4),
"nvarchar(n)" nvarchar(4),
"binary(n)" binary(6),
"varbinary(n)" varbinary(6),
"varchar(max)" varchar(max),
"nvarchar(max)" nvarchar(max),
"varbinary(max)" varbinary(max),
"Text" Text,
"Ntext" Ntext,
"Image" Image
)
--向各个字段插入相应的数据值
INSERT INTO testDataType(
"bit","tinyint","smallint","int","bigint",
"decimal(p,s)","numeric(p,s)",
"smallmoney","money",
"float","real",
"datetime","date","time",
"smalldatetime","datetime2","datetimeoffset",
"char(n)","varchar(n)","nchar(n)","nvarchar(n)",
"binary(n)","varbinary(n)",
"varchar(max)","nvarchar(max)","varbinary(max)",
"Text","Ntext","Image"
)
SELECT
1,123,12345,1234567890,1234567890123456789,
987654.3210,9876.543210,
123456.7890,123456789012345.6789,
1234567890.1234567890,1234567890.1234567890,
'2010-01-01 23:59:59.123','2010-01-01','23:59:59. 1234567',
'2010-01-01 23:59:59','2010-01-01 23:59:59.1234567','2010-01-01 23:59:59.1234567 +12:00',
'abcd','abcd','王保强','王保强',
CONVERT(BINARY,'123456'),CONVERT(BINARY,'123456'),
'1234567890','1234567890',CONVERT(BINARY,'1234567890'),
'1234567890','1234567890','1234567890'
--使用system_internals_allocation_units来获取该表的相关数据页
SELECT c.name,a.type_desc,
total_pages,used_pages,data_pages,
testdb.dbo.f_get_page(first_page) first_page_address,
testdb.dbo.f_get_page(root_page) root_address,
testdb.dbo.f_get_page(first_iam_page) IAM_address
FROM sys.system_internals_allocation_units a,sys.partitions b,sys.objects c
WHERE a.container_id=b.partition_id and b.object_id=c.object_id
AND c.name in ('testDataType')
--再使用dbcc page命令分析相关页面信息
|