数据库快照和分区表.docx

-创建分区表 begin-创建分区函数create partition function PF_FQBTest_CreateDateRange(datetime)asrange right for values (2010-1-1,2010-1-2,2010-1-3,2010-1-4,2010-1-5,2010-1-6,2010-1-7,2010-1-8,2010-1-9,2010-1-10,2010-1-11,2010-1-12,2010-1-13,2010-1-14,2010-1-15,2010-1-16,2010-1-17,2010-1-18,2010-1-19,2010-1-20,2010-1-21,2010-1-22,2010-1-23,2010-1-24,2010-1-25,2010-1-26,2010-1-27,2010-1-28,2010-1-29,2010-1-30,2010-1-31,2010-2-1,2010-2-2,2010-2-3,2010-2-4,2010-2-5,2010-2-6,2010-2-7,2010-2-8,2010-2-9,2010-2-10,2010-2-11,2010-2-12,2010-2-13,2010-2-14,2010-2-15,2010-2-16,2010-2-17,2010-2-18,2010-2-19,2010-2-20,2010-2-21,2010-2-22,2010-2-23,2010-2-24,2010-2-25,2010-2-26,2010-2-27,2010-2-28,2010-3-1,2010-3-2,2010-3-3,2010-3-4,2010-3-5,2010-3-6,2010-3-7,2010-3-8,2010-3-9,2010-3-10,2010-3-11,2010-3-12,2010-3-13,2010-3-14,2010-3-15,2010-3-16,2010-3-17,2010-3-18,2010-3-19,2010-3-20,2010-3-21,2010-3-22,2010-3-23,2010-3-24,2010-3-25,2010-3-26,2010-3-27,2010-3-28,2010-3-29,2010-3-30,2010-3-31,2010-4-1,2010-4-2,2010-4-3,2010-4-4,2010-4-5,2010-4-6,2010-4-7,2010-4-8,2010-4-9,2010-4-10,2010-4-11,2010-4-12,2010-4-13,2010-4-14,2010-4-15,2010-4-16,2010-4-17,2010-4-18,2010-4-19,2010-4-20,2010-4-21,2010-4-22,2010-4-23,2010-4-24,2010-4-25,2010-4-26,2010-4-27,2010-4-28,2010-4-29,2010-4-30,2010-5-1,2010-5-2,2010-5-3,2010-5-4,2010-5-5,2010-5-6,2010-5-7,2010-5-8,2010-5-9,2010-5-10,2010-5-11,2010-5-12,2010-5-13,2010-5-14,2010-5-15,2010-5-16,2010-5-17,2010-5-18,2010-5-19,2010-5-20,2010-5-21,2010-5-22,2010-5-23,2010-5-24,2010-5-25,2010-5-26,2010-5-27,2010-5-28,2010-5-29,2010-5-30,2010-5-31,2010-6-1,2010-6-2,2010-6-3,2010-6-4,2010-6-5,2010-6-6,2010-6-7,2010-6-8,2010-6-9,2010-6-10,2010-6-11,2010-6-12,2010-6-13,2010-6-14,2010-6-15,2010-6-16,2010-6-17,2010-6-18,2010-6-19,2010-6-20,2010-6-21,2010-6-22,2010-6-23,2010-6-24,2010-6-25,2010-6-26,2010-6-27,2010-6-28,2010-6-29,2010-6-30,2010-7-1,2010-7-2,2010-7-3,2010-7-4,2010-7-5,2010-7-6,2010-7-7,2010-7-8,2010-7-9,2010-7-10,2010-7-11,2010-7-12,2010-7-13,2010-7-14,2010-7-15,2010-7-16,2010-7-17,2010-7-18,2010-7-19,2010-7-20,2010-7-21,2010-7-22,2010-7-23,2010-7-24,2010-7-25,2010-7-26,2010-7-27,2010-7-28,2010-7-29,2010-7-30,2010-7-31,2010-8-1,2010-8-2,2010-8-3,2010-8-4,2010-8-5,2010-8-6,2010-8-7,2010-8-8,2010-8-9,2010-8-10,2010-8-11,2010-8-12,2010-8-13,2010-8-14,2010-8-15,2010-8-16,2010-8-17,2010-8-18,2010-8-19,2010-8-20,2010-8-21,2010-8-22,2010-8-23,2010-8-24,2010-8-25,2010-8-26,2010-8-27,2010-8-28,2010-8-29,2010-8-30,2010-8-31,2010-9-1,2010-9-2,2010-9-3,2010-9-4,2010-9-5,2010-9-6,2010-9-7,2010-9-8,2010-9-9,2010-9-10,2010-9-11,2010-9-12,2010-9-13,2010-9-14,2010-9-15,2010-9-16,2010-9-17,2010-9-18,2010-9-19,2010-9-20,2010-9-21,2010-9-22,2010-9-23,2010-9-24,2010-9-25,2010-9-26,2010-9-27,2010-9-28,2010-9-29,2010-9-30,2010-10-1,2010-10-2,2010-10-3,2010-10-4,2010-10-5,2010-10-6,2010-10-7,2010-10-8,2010-10-9,2010-10-10,2010-10-11,2010-10-12,2010-10-13,2010-10-14,2010-10-15,2010-10-16,2010-10-17,2010-10-18,2010-10-19,2010-10-20,2010-10-21,2010-10-22,2010-10-23,2010-10-24,2010-10-25,2010-10-26,2010-10-27,2010-10-28,2010-10-29,2010-10-30,2010-10-31,2010-11-1,2010-11-2,2010-11-3,2010-11-4,2010-11-5,2010-11-6,2010-11-7,2010-11-8,2010-11-9,2010-11-10,2010-11-11,2010-11-12,2010-11-13,2010-11-14,2010-11-15,2010-11-16,2010-11-17,2010-11-18,2010-11-19,2010-11-20,2010-11-21,2010-11-22,2010-11-23,2010-11-24,2010-11-25,2010-11-26,2010-11-27,2010-11-28,2010-11-29,2010-11-30,2010-12-1,2010-12-2,2010-12-3,2010-12-4,2010-12-5,2010-12-6,2010-12-7,2010-12-8,2010-12-9,2010-12-10,2010-12-11,2010-12-12,2010-12-13,2010-12-14,2010-12-15,2010-12-16,2010-12-17,2010-12-18,2010-12-19,2010-12-20,2010-12-21,2010-12-22,2010-12-23,2010-12-24,2010-12-25,2010-12-26,2010-12-27,2010-12-28,2010-12-29,2010-12-30,2010-12-31)go-创建分区方案create partition scheme PS_FQBTestaspartition PF_FQBTest_CreateDateRangeall to (primary)go-创建分区表create table dbo.FQBTest( id int not null ,Name varchar(10) not null ,CreateDate datetime not null)on PS_FQBTest(CreateDate)go-查询数据源(表)select * from FQTest-往分区表导入源数据insert into FQBTestselect Id, Name, CreateDate from FQTest- 查看分区表各分区数据情况select partition = $partition.PF_FQBTest_CreateDateRange(CreateDate) ,rows = count(*) ,minval = min(CreateDate) ,maxval = max(CreateDate) from FQBTest group by $partition.PF_FQBTest_CreateDateRange(CreateDate) order by partition- 查看某个时间对应在第几个物理分区表中select $PARTITION.PF_FQBTest_CreateDateRange(2010-06-04)- 查看某个物理分区表的记录 select * from FQBTestwhere $partition.PF_FQBTest_CreateDateRange(CreateDate)=1-根据时间查询其对应的分区表的信息select * from FQBTestwhere $partition.PF_FQBTest_CreateDateRange(CreateDate)=(select $PARTITION.PF_FQBTest_CreateDateRange (2010-06-01) as temp)-修改分区函数ALTER PARTITION FUNCTION PF_FQTest_CreateDateRange() MERGE RANGE (2010-06-07) - 删除分区表drop table FQBTest- 删除分区方案DROP PARTITION SCHEME PS_FQBTest- 删除分区函数DROP PARTITION FUNCTION PF_FQBTest_CreateDateRange-创建分区表 end-创建快照 begin-性能问题：SQL Server 2005 除了原来支持的四种事务隔离级别（脏读、提交读、可重复读、可串行读）外新增了-一个快照（SNAPSHOT）隔离级别，有可能使阻塞或死锁成为历史。SQL Server在TEMPDB中存放不同版本的数据行，-select 语句读取这些不同版本的行，读操作不阻塞写数据，写操作也不阻塞读操作，这样那些由于读/ 写争用-导致的大量死锁的系统将从中获得无穷益处。如果你的系统复杂难优化，那么升级到SQL Server 2005 试试-快照隔离级别，也许会有意想不到的效果。-创建数据库Use MASTER;GOCREATE DATABASE Snapshot_Test ON PRIMARY ( NAME = NSnapshot_Test, FILENAME =C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATASnapshot_Test.mdf , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = NSnapshot_Test_log, FILENAME =C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATASnapshot_Test_log.ldf , SIZE = 504KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) COLLATE Chinese_PRC_CI_ASGO-设置兼容级别为90（2005为90）（2000为80）EXEC dbo.sp_dbcmptlevel dbname=NSnapshot_Test, new_cmptlevel=90GOUSE Snapshot_TestGO-设置 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，-使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。- 即使 column_name 中包含非空值，使用 WHERE column_name NULL 的 SELECT 语句仍会返回零行。SET ANSI_NULLS ONGO-设置 set quoted_identifier 为 off 时，标识符不可加引号，且必须符合所有 transact-sql 标识符规则SET QUOTED_IDENTIFIER ONGO-创建数据库表CREATE TABLE dbo.test( id int NOT NULL, name nvarchar(50) COLLATE Chinese