三、技巧
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 VERIFYONLY
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,
@NewSize = 1
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)
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE())
AND
@OriginalSize = (
SELECT
size
FROM
sysfiles
WHERE
name
= @LogicalFileName)
AND
(@OriginalSize * 8 /1024) > @NewSize
BEGIN
SELECT
@Counter = 0
WHILE ((@Counter < @OriginalSize / 16)
AND
(@Counter < 50000))
BEGIN
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
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
案例:
有如下表,要求就裱中所有沒有及格的成績,在每次增長0.1的基礎上,使他們剛好及格:
Name
score
Zhangshan 80
Lishi 59
Wangwu 50
Songquan 69
while((
select
min
(score)
from
tb_table)<60)
begin
update
tb_table
set
score =score*1.01
where
score<60
if (
select
min
(score)
from
tb_table)>60
break
else
continue
end