sql2005创建，删除分区表

--创建

----------------------------------------------------------------
--创建文件组
ALTER DATABASE PCCarer ADD FILEGROUP [Architectureamd64]
Go
ALTER DATABASE PCCarer ADD FILEGROUP [Architectureia64]
Go
ALTER DATABASE PCCarer ADD FILEGROUP [Architecturex64]
Go
ALTER DATABASE PCCarer ADD FILEGROUP [Architecturex86]
Go
ALTER DATABASE PCCarer ADD FILEGROUP [Architecturex861]
Go
------------------------------------------------------------------------
--将文件添加到该文件组中
alter database PCCarer
add file
(name = Architectureamd64_data,filename = 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data/Architectureamd64_data.ndf',size = 30MB)
to filegroup [Architectureamd64];
Go
alter database PCCarer
add file
(name = Architectureia64_data,filename = 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data/Architectureia64_data.ndf',size = 30MB)
to filegroup [Architectureia64];
Go
alter database PCCarer
add file
(name = Architecturex64_data,filename = 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data/Architecturex64_data.ndf',size = 30MB)
to filegroup [Architecturex64];
Go
alter database PCCarer
add file
(name = Architecturex86_data,filename = 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data/Architecturex86_data.ndf',size = 30MB)
to filegroup [Architecturex86];
Go
alter database PCCarer
add file
(name = Architecturex861_data,filename = 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data/Architecturex861_data.ndf',size = 30MB)
to filegroup [Architecturex861];
Go
-----------------------------------------------------------------
----创建分区函数
Create partition function
Part_func_Driver_Device(nvarchar(50)) as
range left
for values('amd64','ia64','x64','x86','x861');
-----------------------------------------------------------------
--创建分区架构
CREATE PARTITION SCHEME Part_Driver_DeviceScheme
AS
PARTITION Part_func_Driver_Device
TO ([Architectureamd64],[Architectureia64],[Architecturex64],[Architecturex86],[Architecturex861],[PRIMARY])
Go
-------------------------------------------------------------------
CREATE TABLE [dbo].[Driver_Device1](
 [DriverID] [int] NULL,
 [DeviceID] [nvarchar](200) NULL,
 [OSVersion] [nvarchar](50) NULL,
 [ProcessorArchitecture] [nvarchar](50) NULL,
 [DriverDate] [datetime] NULL,
 [DeviceName] [nvarchar](200) NULL,
 [Model] [nvarchar](256) NULL,
 [Manufacturer] [nvarchar](256) NULL,
 [Provider] [nvarchar](256) NULL,
 [ClassID] [int] NULL,
 [Company] [nvarchar](256) NULL,
 [DriverVersion] [bigint] NULL
)  ON Part_Driver_DeviceScheme (ProcessorArchitecture)

------------------------------------------------------------------------------

insert into Driver_Device1 (
[DriverID]  ,
[DeviceID],
[OSVersion],
[ProcessorArchitecture],
[DriverDate] ,
[DeviceName],
[Model],
[Manufacturer],
[Provider],
[ClassID],
[Company],
[DriverVersion])
select
[DriverID]  ,
[DeviceID],
[OSVersion],
[ProcessorArchitecture],
[DriverDate] ,
[DeviceName],
[Model],
[Manufacturer],
[Provider],
[ClassID],
[Company],
[DriverVersion] from LenovoPCCarerCN.dbo.Driver_Device

--------------------------------------------------------------------------------------------------------------------------------------------------

--删除

/*删除分区表*/
drop Table Driver_Device1
go
/*删除分区架构*/
drop PARTITION SCHEME Part_Driver_DeviceScheme
go
/*删除分区函数*/
drop partition function Part_func_Driver_Device
go
/*删除数据文件*/
ALTER DATABASE PCCarer REMOVE FILE Architectureamd64_data
ALTER DATABASE PCCarer REMOVE FILE Architectureia64_data
ALTER DATABASE PCCarer REMOVE FILE Architecturex64_data
ALTER DATABASE PCCarer REMOVE FILE Architecturex86_data
ALTER DATABASE PCCarer REMOVE FILE Architecturex861_data
go
/*删除文件组*/
alter database PCCarer remove filegroup [Architectureamd64]
alter database PCCarer remove filegroup [Architectureia64]
alter database PCCarer remove filegroup [Architecturex64]
alter database PCCarer remove filegroup [Architecturex86]
alter database PCCarer remove filegroup [Architecturex861]
go

/*清空日志*/
backup log PCCarer with no_log