-- 1.每天创建第二天的表,删除三个月前的表 /**/ /*----------------------------------------------------------------------------Author : HsuChong Date : 2007-08-25 15:20 Desc : uspCreateTomorrowTable------------------------------------------------------------------------------*/ CREATE PROCEDURE uspCreateTomorrowTable @sOriginalTableName sysname AS BEGIN SET NOCOUNT ON ; DECLARE @sExecSQL varchar ( 8000 ), @sNewTableName sysname, @sOldTableName sysname, @tempDate char ( 8 ); BEGIN TRY -- 创建明天的表 SET @tempDate = CONVERT ( CHAR ( 8 ), GETDATE (), 112 ); SET @sNewTableName = @sOriginalTableName + @tempDate ; SET @sExecSQL = ' IF NOT EXISTS(SELECT 1 FROM sysobjects ' + ' WHERE ID = OBJECT_ID( ''' + @sNewTableName + ''' )) ' + ' SELECT * INTO ' + @sNewTableName + ' FROM ' + @sOriginalTableName + ' WHERE 1<> 1 ' ; EXEC ( @sExecSQL ); -- 删除三个月(90天)前的表 SET @tempDate = CONVERT ( CHAR ( 8 ), GETDATE () - 90 , 112 ); SET @sOldTableName = @sOriginalTableName + @tempDate ; SET @sExecSQL = ' IF EXISTS(SELECT 1 FROM sysobjects ' + ' WHERE ID = OBJECT_ID( ''' + @sOldTableName + ''' )) ' + ' DROP TABLE ' + @sOldTableName ; EXEC ( @sExecSQL ); END TRY BEGIN CATCH EXECUTE dbo.uspLogError; END CATCH END ; -- 2.联合查询两个表中的数据 /**/ /*-----------------------------------------------------------------------------------Author : HsuChong Date : 2007-08-24 Desc : uspGetPlanetDataEX----------------------------------------------------------------------------------*/ CREATE PROCEDURE uspGetPlanetDataEX @VehicleSN varchar ( 20 ) = NULL , -- 车机SN @BeginTime varchar ( 50 ) = NULL , -- 开始时间 @EndTime varchar ( 50 ) = NULL , -- 结束时间 @FiterNavigation bit = 1 , -- 是否过滤信号弱数据:1=过滤,0=不过滤 @FiterSpeed bit = 1 , -- 是否过滤0速度数据 :1=过滤,0=不过滤 @FiterInvalidData bit = 1 -- 是否过滤非法数据(速度超过107海里/小时,方向大于不在0-360范围内等) -- 1=过滤,0=不过滤 AS BEGIN SET NOCOUNT ON ; DECLARE @sExecSQL varchar ( 4000 ), @sMainSQL varchar ( 1000 ), @sCondition varchar ( 4000 ), @sOriginalTableName sysname, @sTableName1 sysname, @sTableName2 sysname, @beginDate datetime , @EndDate datetime , @tempDate char ( 8 ), @DayCount int ; BEGIN TRY SET @sOriginalTableName = ' PlanetData ' ; SET @sMainSQL = ' SELECT SystemTime,PlanetTime, ' + ' Longitude,Latitude,Speed,Direction, ' + ' OverSpeed,LoadedOn,PowerOn,IsNavigation ' + ' FROM ' ; SET @beginDate = CAST ( @BeginTime AS datetime ); SET @EndDate = CAST ( @EndTime AS datetime ); SET @DayCount = DATEDIFF ( DAY , @BeginDate , @EndDate ); -- 只允许查询两天内的数据 IF @DayCount > 1 RETURN ; SET @tempDate = CONVERT ( CHAR ( 8 ), @beginDate , 112 ); SET @sTableName1 = @sOriginalTableName + @tempDate ; SET @tempDate = CONVERT ( CHAR ( 8 ), @EndDate , 112 ); SET @sTableName2 = @sOriginalTableName + @tempDate ; SET @sCondition = ' AND (VehicleSN = ' + @VehicleSN + ' ) ' ; IF @FiterNavigation = 1 SET @sCondition = @sCondition + ' AND (IsNavigation=1) ' ; IF @FiterSpeed = 1 SET @sCondition = @sCondition + ' AND (Speed>0) ' ; IF @FiterInvalidData = 1 SET @sCondition = @sCondition + ' AND (Direction BETWEEN 0 AND 360) ' + ' AND (Speed BETWEEN 0 AND 107) ' + ' AND (Longitude > 0) AND (Latitude >0) ' ; SET @sCondition = @sCondition ; IF @DayCount > 0 BEGIN SET @sExecSQL = @sMainSQL + @sTableName1 + ' WHERE (SystemTime >= ''' + @BeginTime + ''' ) ' + @sCondition + ' UNION ALL ' + @sMainSQL + @sTableName2 + ' WHERE (SystemTime <= ''' + @EndTime + ''' ) ' + @sCondition ; END ELSE BEGIN SET @sExecSQL = @sMainSQL + @sTableName1 + ' WHERE (SystemTime BETWEEN ''' + @BeginTime + ''' AND ''' + @EndTime + ''' ) ' + @sCondition ; END ; -- 2007-08-27 之前还没有分表 IF @BeginTime < ' 2007-08-27 ' BEGIN SET @sExecSQL = @sMainSQL + @sOriginalTableName + ' WHERE (SystemTime BETWEEN ''' + @BeginTime + ''' AND ''' + @EndTime + ''' ) ' + @sCondition ; END ; SET @sExecSQL = @sExecSQL + ' ORDER BY SystemTime ' ; PRINT @sExecSQL ; -- -just for test only EXEC ( @sExecSQL ); END TRY BEGIN CATCH EXECUTE dbo.uspLogError; END CATCH END ; -- 3.创建分区表(MS SQL2005) -- ================================================================= -- Author : HsuChong@hotmail.com -- Create date: 2007-07-10 -- Description: Make Partitioned Tables -- ================================================================= CREATE PROCEDURE uspMakePartitionedTables @bExecute bit = 0 AS BEGIN DECLARE @tempDate char ( 8 ), @sSQL varchar ( 8000 ), @sCRLF char ( 2 ), @sTAB char ( 1 ), @PartitionFunctionName varchar ( 50 ), @PartitionSchemeName varchar ( 50 ), @PartitionTableName varchar ( 50 ), @PartitionLine1 varchar ( 50 ), @PartitionLine2 varchar ( 50 ), @PartitionLine3 varchar ( 50 ), @PartitionLine4 varchar ( 50 ), @PartitionLine5 varchar ( 50 ); BEGIN TRY SET @sTAB = char ( 9 ); SET @sCRLF = char ( 13 ) + char ( 10 ); SET @tempDate = CONVERT ( char ( 8 ), getdate () + 1 , 112 ); SET @PartitionFunctionName = ' OneDayDateRangePFN ' + @tempDate ; SET @PartitionSchemeName = ' OneDayDateRangePScheme ' + @tempDate ; SET @PartitionTableName = ' GPSData ' + @tempDate ; SET @PartitionLine1 = '''' + @tempDate + ' 05:59:59.997 ' + '''' ; SET @PartitionLine2 = '''' + @tempDate + ' 10:59:59.997 ' + '''' ; SET @PartitionLine3 = '''' + @tempDate + ' 14:59:59.997 ' + '''' ; SET @PartitionLine4 = '''' + @tempDate + ' 18:59:59.997 ' + '''' ; SET @PartitionLine5 = '''' + @tempDate + ' 23:59:59.997 ' + '''' ; -- 1.Create the partition function SET @sSQL = ' CREATE PARTITION FUNCTION ' + @PartitionFunctionName + ' (datetime) ' + @sCRLF ; SET @sSQL = @sSQL + ' AS ' + @sCRLF ; SET @sSQL = @sSQL + ' RANGE LEFT FOR VALUES( ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + @PartitionLine1 + ' , ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + @PartitionLine2 + ' , ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + @PartitionLine3 + ' , ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + @PartitionLine4 + ' , ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + @PartitionLine5 + ' ) ' + @sCRLF + @sCRLF ; -- SET @sSQL = @sSQL + 'GO' + @sCRLF; -- I did not know why the use 'GO' will be syntax error. -- 2.Create the partition scheme SET @sSQL = @sSQL + ' CREATE PARTITION SCHEME ' + @PartitionSchemeName + @sCRLF ; SET @sSQL = @sSQL + ' AS ' + @sCRLF ; SET @sSQL = @sSQL + ' PARTITION ' + @PartitionFunctionName + ' TO ( ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + ' [FG1], [FG2], [FG3], [FG4], [FG5], [PRIMARY]) ' + @sCRLF + @sCRLF ; -- SET @sSQL = @sSQL + 'GO' + @sCRLF; -- 3.Create the table SET @sSQL = @sSQL + ' CREATE TABLE ' + @PartitionTableName + ' ( ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + ' TerminalID dbo.TerminalCode NOT NULL, ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + ' SystemTime datetime NOT NULL DEFAULT (getdate()), ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + ' GPSTime datetime NOT NULL DEFAULT(getdate()), ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + ' IsNavigation bit NOT NULL DEFAULT((0)), ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + ' Longitude float NOT NULL DEFAULT((0)), ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + ' Latitude float NOT NULL DEFAULT((0)), ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + ' Speed smallint NOT NULL DEFAULT((0)), ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + ' LoadedOn bit NULL DEFAULT((0)), ' + @sCRLF ; SET @sSQL = @sSQL + @sTAB + ' PowerOn bit NULL DEFAULT((0)) ' + @sCRLF ; SET @sSQL = @sSQL + ' ) ON ' + @PartitionSchemeName + ' (SystemTime) ' + @sCRLF + @sCRLF ; -- SET @sSQL = @sSQL + 'GO' + @sCRLF; -- 4.Create the clustered indexes as Primary keys SET @sSQL = @sSQL + ' ALTER TABLE ' + @PartitionTableName + @sCRLF ; SET @sSQL = @sSQL + ' ADD CONSTRAINT ' + @PartitionTableName + ' PK ' + @sCRLF ; SET @sSQL = @sSQL + ' PRIMARY KEY CLUSTERED (SystemTime,TerminalID) ' + @sCRLF ; SET @sSQL = @sSQL + ' ON ' + @PartitionSchemeName + ' (SystemTime) ' + @sCRLF ; -- SET @sSQL = @sSQL + 'GO' + @sCRLF; PRINT @sSQL ; IF @bExecute = 1 EXEC ( @sSQL ); END TRY BEGIN CATCH EXECUTE dbo.uspLogError; END CATCH; END ; -- Just for test -- EXEC uspMakePartitionedTables -- or -- EXEC uspMakePartitionedTables 1