geshi

-----建议数据库格式如下：将数据文件和index分开存放

use master
go
if db_id('LvXing168_V1') is not null
raiserror ('新建的数据库已经存在',10,1)
go

create database LvXing168_V1
on primary
(
name=LvXing168_V1 ,
filename='d:/LvXing168_V1.mdf',
size=200MB,
filegrowth=100MB,
maxsize=unlimited
)
log on
(
name=LvXing168_V1_log,
filename='d:/LvXing168_V1_log.ldf',
size=400MB,
filegrowth=200MB,
maxsize=unlimited
)
go

alter database LvXing168_V1
add filegroup LvXing168_V1_filegroup01
go

alter database LvXing168_V1
add file
(
name=LvXing168_V1_01,
filename='d:/LvXing168_V1_01.ndf',
size=200MB,
filegrowth=100MB,
maxsize=unlimited
),
(
name=LvXing168_V1_02,
filename='d:/LvXing168_V1_02.ndf',
size=200MB,
filegrowth=100MB,
maxsize=unlimited
) to filegroup LvXing168_V1_filegroup01
go

--alter database LvXing168_V1 modify filegroup LvXing168_V1_filegroup01 default

 

------------新建表格式：

/*
尽可能选择int类型并且唯一值的列做为主键和聚集索引；

*/
--案例1：

use LvXing168_V1
go

CREATE TABLE [dbo].[tblAirline](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [AirName] [nvarchar](100)  NULL,
 [AirValue] [nvarchar](100)  NULL,
 [IsEnabled] [smallint] NULL,
 CONSTRAINT [PK_tblAirline] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON  [LvXing168_V1_filegroup01]
) ON [PRIMARY]
GO

-----上面的意思是：将index保存在新建的文件组中;表数据保存在默认的文件组中

 

/*
如果非要选择非int类型的列设置为主键，则将该列设置为nonclustered

*/

--案例:

create table test
(
sid int not null,
sname varchar(100),
constraint cons01 primary key nonclustered (sname)
)
go

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自动编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAirline', @level2type=N'COLUMN',@level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'航空公司名称(格式：HU-海航)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAirline', @level2type=N'COLUMN',@level2name=N'AirName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'航空公司二字码(格式：HU)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAirline', @level2type=N'COLUMN',@level2name=N'AirValue'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否禁用(0:否/1:是)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblAirline', @level2type=N'COLUMN',@level2name=N'IsEnabled'