sql语句:提升你的SQL语句(2)来源: 发布时间:星期日, 2008年10月19日 浏览:769次 评论:0
三、技巧
1、1=1,1=2的使用,在SQL语句组合时用的较多 “where 1=1” 是表示选择全部 “where 1=2”全部不选, 如: if @strWhere !="" begin set @strSQL = "select count(*) as Total from [" + @tblName + "] where " + @strWhere end else begin set @strSQL = "select count(*) as Total from [" + @tblName + "]" end 我们可以直接写成 set @strSQL = "select count(*) as Total from [" + @tblName + "] where 1=1 安定 "+ @strWhere 2、收缩数据库 --重建索引 DBCC REINDEX DBCC INDEXDEFRAG --收缩数据和日志 DBCC SHRINKDB DBCC SHRINKFILE 3、压缩数据库 dbcc shrinkdatabase(dbname) 4、转移数据库给新用户以已存在用户权限 exec sp_change_users_login "update_one","newname","oldname" go 5、检查备份集 RESTORE VERIFY from disk="E:\dvbbs.bak" 6、修复数据库 Alter DATABASE [dvbbs] SET SINGLE_USER GO DBCC CHECKDB("dvbbs",repair_allow_data_loss) WITH TABLOCK GO Alter DATABASE [dvbbs] SET MULTI_USER GO 7、日志清除 SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT USE tablename -- 要操作的数据库名 Select @LogicalFileName = "tablename_log", -- 日志文件名 @MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 1 -- 你想设定的日志文件的大小(M) -- Setup / initialize DECLARE @OriginalSize int Select @OriginalSize = size FROM sysfiles Where name = @LogicalFileName Select "Original Size of " + db_name() + " LOG is " + CONVERT(VARCHAR(30),@OriginalSize) + " 8K pages or " + CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + "MB" FROM sysfiles Where name = @LogicalFileName Create TABLE DummyTrans (DummyColumn char (8000) not null) DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255) Select @StartTime = GETDATE(), @TruncLog = "BACKUP LOG " + db_name() + " WITH TRUNCATE_ONLY" DBCC SHRINKFILE (@LogicalFileName, @NewSize) EXEC (@TruncLog) -- Wrap the log if necessary. WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired AND @OriginalSize = (Select size FROM sysfiles Where name = @LogicalFileName) AND (@OriginalSize * 8 /1024) > @NewSize BEGIN -- Outer loop. Select @Counter = 0 WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) BEGIN -- update Insert DummyTrans VALUES ("Fill Log") Delete DummyTrans Select @Counter = @Counter + 1 END EXEC (@TruncLog) END Select "Final Size of " + db_name() + " LOG is " + CONVERT(VARCHAR(30),size) + " 8K pages or " + CONVERT(VARCHAR(30),(size*8/1024)) + "MB" FROM sysfiles Where name = @LogicalFileName Drop TABLE DummyTrans SET NOCOUNT OFF 8、说明:更改某个表 exec sp_changeobjectowner "tablename","dbo" 9、存储更改全部表 Create PROCEDURE dbo.User_ChangeObjectOwnerBatch @OldOwner as NVARCHAR(128), @NewOwner as NVARCHAR(128) AS DECLARE @Name as NVARCHAR(128) DECLARE @Owner as NVARCHAR(128) DECLARE @OwnerName as NVARCHAR(128) DECLARE curObject CURSOR FOR select "Name" = name, "Owner" = user_name(uid) from sysobjects where user_name(uid)=@OldOwner order by name OPEN curObject FETCH NEXT FROM curObject INTO @Name, @Owner WHILE(@@FETCH_STATUS=0) BEGIN if @Owner=@OldOwner begin set @OwnerName = @OldOwner + "." + rtrim(@Name) exec sp_changeobjectowner @OwnerName, @NewOwner end -- select @name,@NewOwner,@OldOwner FETCH NEXT FROM curObject INTO @Name, @Owner END close curObject deallocate curObject GO 10、SQL SERVER中直接循环写入数据 declare @i int set @i=1 while @i<30 begin insert into test (userid) values(@i) set @i=@i+1 end 小记存储过程中经常用到的本周,本月,本年函数 Dateadd(wk,datediff(wk,0,getdate()),-1) Dateadd(wk,datediff(wk,0,getdate()),6) Dateadd(mm,datediff(mm,0,getdate()),0) Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) Dateadd(yy,datediff(yy,0,getdate()),0) Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) 上面的SQL代码只是一个时间段 Dateadd(wk,datediff(wk,0,getdate()),-1) Dateadd(wk,datediff(wk,0,getdate()),6) 就是表示本周时间段. 下面的SQL的条件部分,就是查询时间段在本周范围内的: Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) 而在存储过程中 select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) 0
相关文章
读者评论
发表评论 |