有关sql server数据库创建时，创建多个文件组与分配到多个物理磁盘的方法，基于性能优化的考虑，可以使用sql数据库文件组来保存数据库文件，并分散到多个磁盘上，性能会提升不少。
在sql server数据库部署时，服务器的瓶颈主要是15000转的硬盘无法满足日益增长的I/O需求，可以考虑将创建数据库时要尽量将数据库的I/O压力分解到多个磁盘中。
下面来看下sql server数据库创建多个文件组与物理磁盘的方法。
1、数据库文件组创建
复制代码 代码示例:
use master
go
if DB_ID(N'care365') is not null--如果数据库存在，则删除它
drop database care365
go
create database care365
on primary
(
name=care1,
filename='C:/database/care365.mdf',--主文件组放在C盘下
size=10MB,
filegrowth=15%
)
,filegroup caregroup1
(
name=care2,
filename='D:/database/care365_group1.ndf',--在D盘上创建一个文件组
size=10MB,
filegrowth=15%
)
,
filegroup caregroup2--该文件组分布在D和E两个盘符下
(
name=care3,
filename='D:/database/care365_group2.ndf',
size=10MB,
filegrowth=15%
)
,
(
name=care4,
filename='E:/database/care365_group2.ndf',
size=10MB,
filegrowth=15%
)
log on
(
name=care_log,
filename='c:/database/care365.ldf',--将
日志文件放到C盘下
size=10MB,
filegrowth=15%
)
go
2、数据库创建好之后，由于是实验用，设置自动关闭，节约资源
复制代码 代码示例:
alter database care365
set auto_close on
go
3、添加数据库中的表和实验数据
复制代码 代码示例:
use care365
go
if OBJECT_ID(N'Customer') is not null
drop table dbo.customer
go
create table dbo.Customer
(
ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY NOT NULL CONSTRAINT DF_Customer_id DEFAULT (NEWSEQUENTIALID()),
Name nvarchar(50) not null,
number nvarchar(50) not null constraint DF_Student_Number default (N'无')
)
on caregroup1;
go
insert dbo.Customer (Name,number) values (N'张三',N'001')
insert dbo.Customer (Name,number) values (N'李四',N'002')
insert dbo.Customer (Name,number) values (N'王五',N'003')
insert dbo.Customer (Name,number) values (N'赵六',N'004')
go
select * from dbo.Customer
if OBJECT_ID(N'Product') is not null
drop table dbo.Product
go
create table dbo.Product
(
ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY NOT NULL CONSTRAINT DF_Product_id DEFAULT (NEWSEQUENTIALID()),
Name nvarchar(512) not null,
Code nvarchar(50) not null constraint DF_Product_Code default (N'无'),
Price money not null constraint DF_product_price default ((0))
)
on caregroup1
go
insert dbo.Product (Name,Code,Price) values (N'奥迪 A4L 2010款 3.2FSI quattro 旗舰型',N'Audi_A4L_3.2FSI_quattro',57.81*10000)
insert dbo.Product (Name,Code,Price) values (N'奥迪 A6L 2010款 3.0TFSI quattro 豪华型',N'Audi_A6L_3.0TFSI_quattro',69.60*10000)
insert dbo.Product (Name,Code,Price) values (N'奥迪 A8L 2009款 6.0L quattro 专享尊崇型',N'Audi_A8L_6.0_quattro',291*10000)
insert dbo.Product (Name,Code,Price) values (N'奥迪 Q5 2010款 2.0TFSI 豪华型',N'Audi_Q5_2.0TFSI',60.39*10000)
insert dbo.Product (Name,Code,Price) values (N'奥迪 Q7 2010款 4.2FSI quattro 豪华型',N'Audi_Q7_4.2FSI_quattro',145*10000)
go
select * from dbo.Product
if OBJECT_ID(N'Orders') is not null
drop table dbo.Orders
go
create table dbo.Orders
(
ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY NOT NULL CONSTRAINT DF_Orders_id DEFAULT (NEWSEQUENTIALID()),
CreateTime datetime not null constraint DF_Orders_CreateTime default(getdate()),
CustomerID UNIQUEIDENTIFIER not null
)
on caregroup2
go
insert dbo.Orders (CustomerID) values ('00AC9FF3-348C-DF11-A98B-001E37DA94AB')
insert dbo.Orders (CustomerID) values ('01AC9FF3-348C-DF11-A98B-001E37DA94AB')
insert dbo.Orders (CustomerID) values ('02AC9FF3-348C-DF11-A98B-001E37DA94AB')
insert dbo.Orders (CustomerID) values ('03AC9FF3-348C-DF11-A98B-001E37DA94AB')
go
select * from dbo.Orders
if OBJECT_ID(N'OrderInfo') is not null
drop table dbo.OrderInfo
go
create table dbo.OrderInfo
(
ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY NOT NULL CONSTRAINT DF_OrderInfo_id DEFAULT (NEWSEQUENTIALID()),
OrdersID UNIQUEIDENTIFIER not null,
ProductID UNIQUEIDENTIFIER not null,
Price money not null constraint DF_OrderInfo_Price default ((0)),
Quantity int not null constraint DF_OrderInfo_Quantity default((0))
)
on caregroup2
go
insert dbo.OrderInfo (OrdersID,ProductID,Price,Quantity) values ('0A2E2E09-AA8C-DF11-9EB4-001E37DA94AB','0AF16B9C-A28C-DF11-9EB4-001E37DA94AB',578100.00,1)
insert dbo.OrderInfo (OrdersID,ProductID,Price,Quantity) values ('0B2E2E09-AA8C-DF11-9EB4-001E37DA94AB','0AF16B9C-A28C-DF11-9EB4-001E37DA94AB',578100.00,1)
insert dbo.OrderInfo (OrdersID,ProductID,Price,Quantity) values ('0B2E2E09-AA8C-DF11-9EB4-001E37DA94AB','0BF16B9C-A28C-DF11-9EB4-001E37DA94AB',696000.00,1)
insert dbo.OrderInfo (OrdersID,ProductID,Price,Quantity) values ('0C2E2E09-AA8C-DF11-9EB4-001E37DA94AB','0AF16B9C-A28C-DF11-9EB4-001E37DA94AB',578100.00,1)
insert dbo.OrderInfo (OrdersID,ProductID,Price,Quantity) values ('0C2E2E09-AA8C-DF11-9EB4-001E37DA94AB','0BF16B9C-A28C-DF11-9EB4-001E37DA94AB',696000.00,1)
insert dbo.OrderInfo (OrdersID,ProductID,Price,Quantity) values ('0C2E2E09-AA8C-DF11-9EB4-001E37DA94AB','0CF16B9C-A28C-DF11-9EB4-001E37DA94AB',2910000.00,1)
insert dbo.OrderInfo (OrdersID,ProductID,Price,Quantity) values ('0D2E2E09-AA8C-DF11-9EB4-001E37DA94AB','0AF16B9C-A28C-DF11-9EB4-001E37DA94AB',578100.00,1)
insert dbo.OrderInfo (OrdersID,ProductID,Price,Quantity) values ('0D2E2E09-AA8C-DF11-9EB4-001E37DA94AB','0CF16B9C-A28C-DF11-9EB4-001E37DA94AB',2910000.00,1)
insert dbo.OrderInfo (OrdersID,ProductID,Price,Quantity) values ('0D2E2E09-AA8C-DF11-9EB4-001E37DA94AB','0DF16B9C-A28C-DF11-9EB4-001E37DA94AB',603900.00,1)
insert dbo.OrderInfo (OrdersID,ProductID,Price,Quantity) values ('0D2E2E09-AA8C-DF11-9EB4-001E37DA94AB','0EF16B9C-A28C-DF11-9EB4-001E37DA94AB',1450000.00,1)
go
select * from dbo.OrderInfo
