sql server 建库建表脚本

一．建库

 

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

;