SQLSERVER2005 建分区

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG200908;

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG200909;

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG200910;

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG200911;

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG200912;

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG201001;

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG201002;

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG201003;

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG201004;

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG201005;

ALTER DATABASE sjzs_stat ADD FILEGROUP MONTHFG201006;


ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF200908', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF200908.NDF') TO FILEGROUP MONTHFG200908;

ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF200909', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF200909.NDF') TO FILEGROUP MONTHFG200909;

ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF200910', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF200910.NDF') TO FILEGROUP MONTHFG200910;

ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF200911', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF200911.NDF') TO FILEGROUP MONTHFG200911;

ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF200912', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF200912.NDF') TO FILEGROUP MONTHFG200912;

ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF201001', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF201001.NDF') TO FILEGROUP MONTHFG201001;

ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF201002', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF201002.NDF') TO FILEGROUP MONTHFG201002;

ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF201003', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF201003.NDF') TO FILEGROUP MONTHFG201003;

ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF201004', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF201004.NDF') TO FILEGROUP MONTHFG201004;

ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF201005', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF201005.NDF') TO FILEGROUP MONTHFG201005;

ALTER DATABASE sjzs_stat ADD FILE (NAME = 'MONTHF201006', FILENAME = 'E:\SqlData\sjzs_stat_SoftUseWORKSF201006.NDF') TO FILEGROUP MONTHFG201006;

use sjzs_stat
GO

CREATE PARTITION FUNCTION MonthPartitionFunction(datetime)
AS
RANGE LEFT FOR VALUES ('01/08/2009'

                      ,'01/09/2009'

                      ,'01/10/2009'

                      ,'01/11/2009'

                      ,'01/12/2009'

                      ,'01/01/2010'

                      ,'01/02/2010'

                      ,'01/03/2010'

                      ,'01/04/2010'

                      ,'01/05/2010')

CREATE PARTITION SCHEME MonthPartitionstruct
AS PARTITION MonthPartitionFunction TO (MONTHFG200908, MONTHFG200909,MONTHFG200910,MONTHFG200911,MONTHFG200912,MONTHFG201001,MONTHFG201002,MONTHFG201003,MONTHFG201004,MONTHFG201005,MONTHFG201006)

GO

/****** 对象:  Table [dbo].[T_SoftUseHistory]    脚本日期: 07/15/2009 14:12:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_SoftUseHistory](
    [AutoID] [bigint] IDENTITY(1,1) NOT NULL,
    [UID] [bigint] NOT NULL,
    [ActionID] [int] NOT NULL,
    [SoftID] [int] NOT NULL,
    [Imei] [varchar](50) NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [UseTime] [int] NOT NULL,
    [Platform] [int] NOT NULL,
    [BuildTime] [datetime] NOT NULL,
) ON MonthPartitionstruct(StartTime)
GO
SET ANSI_PADDING OFF
GO
CREATE CLUSTERED INDEX [INDEX_StartTime_T_SoftUseHistory] ON [dbo].[T_SoftUseHistory](StartTime)
GO
CREATE NONCLUSTERED INDEX [INDEX_IMEI_T_SoftUseHistory] ON [dbo].[T_SoftUseHistory](Imei)
GO
ALTER TABLE [dbo].[T_SoftUseHistory] ADD CONSTRAINT PK_ID_T_SoftUseHistory PRIMARY KEY(AutoID,StartTime)
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增长ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_SoftUseHistory', @level2type=N'COLUMN',@level2name=N'AutoID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_SoftUseHistory', @level2type=N'COLUMN',@level2name=N'UID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'功能ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_SoftUseHistory', @level2type=N'COLUMN',@level2name=N'ActionID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_SoftUseHistory', @level2type=N'COLUMN',@level2name=N'SoftID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户IMEI号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_SoftUseHistory', @level2type=N'COLUMN',@level2name=N'Imei'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'功能开始使用时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_SoftUseHistory', @level2type=N'COLUMN',@level2name=N'StartTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'功能使用花费时间（秒为单位）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_SoftUseHistory', @level2type=N'COLUMN',@level2name=N'UseTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户使用平台' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_SoftUseHistory', @level2type=N'COLUMN',@level2name=N'Platform'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'入库时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_SoftUseHistory', @level2type=N'COLUMN',@level2name=N'BuildTime'
GO