SQL statements, three skills article

The use of 1, 1=1, 1=2, in combination with SQL statements more

"Where 1=1" is said to choose all "where 1=2" all don't choose,
Such as:
If @ strWhere!
=' 'The begin
The set @ strSQL='select count (*) as the Total from [' + @ tblName +] where' + @ strWhere
End
The else
The begin
The set @ strSQL='select count (*) as the Total from [' + @ tblName +'] '
End



We can directly write

Error! The entry is not found,
Set @ strSQL='select count (*) as the Total from [' + @ tblName +] where 1=1 stability' + @ strWhere



2, the contraction database


- rebuild index
DBCC REINDEX
DBCC INDEXDEFRAG
-- contract data and log
DBCC SHRINKDB
DBCC SHRINKFILE



3, compressed database

DBCC shrinkdatabase (dbname)



4, transfer the database to the new user to existing user permissions

The exec sp_change_users_login 'update_one', 'newname', 'oldname'
Go



5, check the backup set

RESTORE VERIFYONLY from disk='E: \ DVBBS bak'



6, repair the database

The ALTER DATABASE [DVBBS] SET SINGLE_USER
GO
DBCC CHECKDB (' DVBBS 'repair_allow_data_loss) WITH TABLOCK
GO
The ALTER DATABASE [DVBBS] SET MULTI_USER
GO



7, log removal



SET NOCOUNT ON
DECLARE @ LogicalFileName sysname,
@ MaxMinutes INT,
@ NewSize INT


USE tablename - to operate the database name
SELECT @ LogicalFileName='tablename_log, - log file name
@ MaxMinutes=10 - Limit on the time allowed to wrap the
@ NewSize=1 - do you want to set the size of the log file (M)

The Setup/initialize
DECLARE @ OriginalSize int
SELECT @ OriginalSize=size
The FROM sysfiles
WHERE name=@ LogicalFileName
SELECT 'the Original Size of' + db_name () + 'LOG is' +
CONVERT (VARCHAR (30), @ OriginalSize) + 'pages or 8 k +
CONVERT (VARCHAR (30), 8/1024) (@ OriginalSize *) + 'MB'
The FROM sysfiles
WHERE name=@ LogicalFileName
The 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)
The EXEC (@ TruncLog)
- Wrap the log if necessary.
WHILE @ MaxMinutes & gt; DATEDIFF (mi, @ StartTime, GETDATE ()), the time from the expired
AND @ OriginalSize=(SELECT the size FROM sysfiles WHERE name=@ LogicalFileName)
AND (@ OriginalSize * 8/1024) & gt; @ NewSize
The BEGIN of the Outer loop.
SELECT @ Counter=0
WHILE ((@ Counter & lt; @ OriginalSize/16) AND (@ Counter & lt; 50000))
The BEGIN - update
INSERT DummyTrans VALUES (' the Fill the Log ') DELETE DummyTrans
The SELECT=@ @ Counter Counter + 1
END
The EXEC (@ TruncLog)
END
SELECT the 'Final Size of' + db_name () + 'LOG is' +
CONVERT (VARCHAR (30), the size) + 'pages or 8 k +
CONVERT (VARCHAR (30), (the size * 8/1024)) + 'MB'
The FROM sysfiles
WHERE name=@ LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF



8, note: changing a table

The exec sp_changeobjectowner tablename, 'dbo



9, storage changes all table



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 the 'Name'=Name,
'the Owner'=user_name (uid)
The from sysobjects
Where user_name (uid)=@ OldOwner
The order by name

The OPEN curObject
The FETCH NEXT FROM curObject INTO @ Name, @ Owner
WHILE (@ @ FETCH_STATUS=0)
The BEGIN
If @ Owner=@ OldOwner
The begin
The set @ OwnerName=@ OldOwner + '+ rtrim (@ Name)
The exec sp_changeobjectowner @ OwnerName, @ NewOwner
End
- select @ name, @ NewOwner, @ OldOwner

The FETCH NEXT FROM curObject INTO @ Name, @ Owner
END

The close curObject
Deallocate curObject
GO


10, direct cycle in the SQL SERVER write data


Declare @ I int
The set @ I=1
While @ i<30
The begin
Insert into test (userid) values (@ I)
The set @ I=@ I + 1
End


Case:


Have the following table, which requires all didn't pass the exam score is framed, on the basis of each growth 0.1, so that they just pass the:



The Name score

Zhangshan 80

Lishi 59

Wangwu 50

Songquan 69

While ((select min (score) from tb_table) & lt; 60)

The begin

The update tb_table set score score * 1.01=

Where score<60

If (select min (score) from tb_table) & gt; 60

Break

The else

The continue

End



SQL statement data development article



Four, data development

1. According to the last stroke order

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as//From less to more



2. Database encryption


The select encrypt (' original password)
The select pwdencrypt (' original password)
Select pwdcompare (' original password ', 'the encrypted password')=1 - the same; nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull