一.建库
use master
go
--1.建库
create database testdb
on primary
(name='testdb', filename='d:/db/testdb.mdf', size=8mb, filegrowth=8mb)
log on
(name='testdb_log', filename='d:/db/testdb_log.ldf', size=32mb, filegrowth=32mb)
go
--2.建表的默认文件组
alter database testdb add filegroup testdb_data
alter database testdb
add file (name=testdb_data, filename='d:/db/testdb_data.ndf', size=32mb, filegrowth=32mb)
to filegroup testdb_data
--3.设置默认文件组
alter database testdb
modify filegroup testdb_data default
--4.建索引文件组
alter database testdb add filegroup testdb_index
alter database testdb
add file ( name = 'testdb_index', filename = 'e:/db/testdb_index.ndf' , size = 1024mb , maxsize = unlimited, filegrowth = 1024mb )
--5.为分区表建单独文件组(sql server 05及以上版本)
alter database testdb add filegroup testdb_partition_table_name
alter database testdb
add file (name=testdb_partition_table_name, filename='d:/db/testdb_partition_table_name.ndf', size=256mb, filegrowth=256mb)
to filegroup testdb_partition_table_name
二.创建分区函数分区方案(sql server 05及以上版本)
/**************************************************
功 能:为testdb库表分区,从2011-01-01 到2012-12-31 按天做的分区
创建日期:
创建人员:tongye
**************************************************/
use testdb;
go
declare
@date datetime,
@date_str varchar(max),
@sql varchar(max)
--开始时间@date_str 比 @date 小一天
select
@date = '2011-01-02',
@date_str = '''2011-01-01'''
--结束时间
while @date <= '2012-12-31'
begin
set @date_str = @date_str+','''+convert(char(10),@date,20)+''''
set @date = dateadd(day,1,@date)
end
-- 为partition_table_name表分区(这里按时间分区)
select
@sql = 'create partition function pf_ partition_table_name_date(smalldatetime)
as range right for values ('+ @date_str +')'
exec(@sql)
create partition scheme ps_partition_table_name_date
as partition pf_partition_table_name_date all to (testdb_partition_table_name)
三.建表
use testdb
go
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('partition_table_name') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE partition_table_name
;
CREATE TABLE partition_table_name (
date smalldatetime NOT NULL,
id int NOT NULL,
mac varchar(20) NOT NULL,
is_disk tinyint NOT NULL,
) on ps_partition_table_name_date(date) –应用分区方案
;
--索引放到索引文件组
CREATE CLUSTERED INDEX ixc_partition_table_name_date
ON partition_table_name (date ASC, bar_id ASC)
ON testdb_index
;