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