Techniques for completing classical SQL statements

1. 1 = 2. It is used in combination of SQL statements.

"Where 1 = 1" indicates that all "where 1 = 2" are not selected,
For example:
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

We can directly write

Error! Directory item not found.
Set @ strSQL = 'select count (*) as Total from ['+ @ tblName +'] where 1 = 1 Anding '+ @ strWhere 2. shrink the database
-- Re-Indexing
DBCC REINDEX
DBCC INDEXDEFRAG
-- Shrink data and logs
DBCC SHRINKDB
DBCC SHRINKFILE

3. compress the database
Dbcc shrinkdatabase (dbname)

4. Transfer the database to a new user with the existing user permission
Exec sp_change_users_login 'Update _ one', 'newname', 'oldname'
Go

5. Check the backup set
Restore verifyonly from disk = 'e:/dvbbs. Bak'

6. Restore the database
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. Log clearing
SET NOCOUNT ON
DECLARE @ LogicalFileName sysname,
@ MaxMinutes INT,
@ NewSize INT

USE tablename -- Name of the database to be operated
SELECT @ LogicalFileName = 'tablename _ log', -- log File Name
@ MaxMinutes = 10, -- Limit on time allowed to wrap log.
@ NewSize = 1 -- the size of the log file you want to set (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) + '8 K 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) + '8 K pages or '+
CONVERT (VARCHAR (30), (size * 8/1024) + 'mb'
FROM sysfiles
WHERE name = @ LogicalFileName
Drop table DummyTrans
SET NOCOUNT OFF

8. Description: modify a table.
Exec sp_changeobjectowner 'tablename', 'dbo'

9. Store and change all tables

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
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. Data is written cyclically in SQL SERVER.
Declare @ I int
Set @ I = 1
While @ I <30
Begin
Insert into test (userid) values (@ I)
Set @ I = @ I + 1
End
Case:
As shown in the following table, all the failures in the system are required to pass the course on the base of 0.1 growth each time:

Name score

Zhangshan80

Lishi 59

Wangwu 50

Songquan69

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