Useful SQL statements (continuously updated)

1 . Waitfor

-- Waitfor
-- For example, the SELECT statement is executed after 1 hour, 2 minutes, and 3 seconds.
Waitfor Delay ' 01:02:03 '
Select   *   From Employee
-- For example, the SELECT statement will not be executed until AM.
Waitfor Time ' 23:08:00 '
Select   *   From Employee

2.View sqlserver version

Select Serverproperty ( ' Productversion ' ), Serverproperty ( ' Productlevel ' ), Serverproperty ( ' Edition ' )

3.In sqlserver, a simple combination of sp_spaceused and sp_msforeachtable stored procedures can easily calculate the size of the user data table, including the total number of records and space usage, which is very practical, both sqlserver2k and sqlserver2005 passed the test.

/**/ /**/ /**/ /*
1. Exec sp_spaceused 'table name' (SQL statistics, which may be inaccurate after a large number of transaction operations)
2. Exec sp_spaceused 'table name', true (update the table space size, accurate empty table size, but it may take some time)
3. Exec sp_spaceused (database size query)
4. Exec sp_msforeachtable "Exec sp_spaceused '? '"(All user tablespace tables are small, SQL statistics, and may be inaccurate after a large number of transaction operations)
5. Exec sp_msforeachtable "Exec sp_spaceused '? ', True "(all user tablespace tables are small, and large databases are used with caution)
*/

Create   Table # T (name Varchar ( 255 ), Rows Bigint , Reserved Varchar ( 20 ), Data Varchar ( 20 ), Index_size Varchar ( 20 ), Unused Varchar ( 20 ))
Exec Sp_msforeachtable" Insert   Into # T Exec Sp_spaceused ' ? ' "
Select   *   From # T
Drop   Table # T

4. Extract n data from the table.

Select   Top   10   *
From Largetable
Order   By   Newid ()

5.Attach a database to an SQL statement

Use Master
Exec Sp_attach_db @ Dbname   = N ' Pubs ' ,
@ Filename1   = N ' C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ pubs. MDF ' ,
@ Filename2   = N ' C: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ pubs_log.ldf '  

6.Data replication between database tables

-- 1. tables with the same table structure are in the same database (e.g., Table1, table2)

SQL: Insert   Into Table1 Select * From Table2 (full replication)

Insert   Into Table1 Select Distinct * From Table2 (do not copy duplicate records)

Insert   Into Table1 Select Top   5   * From Table2 (First Five Records)

-- 2. Not in the same database (for example, db1 Table1, DB2 table2)

SQL: Insert   Into Db1.. Table1 Select * From Db2.. Table2 (full copy)

Insert   Into Db1.. Table1 Select Distinct * From Db2table2 (do not copy duplicate records)

Insert   Into Tdb1.. able1 Select Top   5   * From Db2table2 (First Five Records)

7. Sort by last name strokes

Select   *   From Tablename Order   By Customername collate chinese_prc_stroke_ci_as

8. Multi-condition Query
Multi-condition query is used in many practical applications. On some pages, you are required to query data by time, category, or other keywords, and these conditions are not necessarily input. I used to use C # Code The where condition string is spelled based on the conditions selected, and if is used in the stored procedure to determine the passed parameters. However, it is known that the use of or in the stored procedure is the simplest, as follows: Create   Table Users
(
ID Int ,
Name Nvarchar ( 20 ),
Age Int
)

Create   Proc Sp_sgetuserinfo
(
@ Name   Nvarchar ( 20 ),
@ Age   Int
)
As
Select  
*
From
Users
Where
( @ Name = ''   Or Name = @ Name )
And
( @ Age = ''   Or Age = @ Age )

9Two methods for obtaining random numbers in. sqlserver
A. Create a table rand with the field randomnum, which stores data ranging from 0 to 9.
Use the following SQL statement to generate a random number:Select Top 1RandomnumFrom Rand Order By Newid()

B. Use the rand () function provided by sqlserver
 

Select   Cast ( Floor ( Rand () * N) As   Int )
-- Generate a random number between 0 and N-1
Select   Cast ( Ceiling ( Rand () * N) As   Int )
-- Generates a random number between 1 and N.

10. Several common date format conversions in the database
Date Format Conversion is also frequently used. Generally, 2008-04-12 and 20080412 are used in many formats. The following lists some commonly used formats: Convert ( Varchar ( 10 ), Getdate (), 120 )
-- Returns 2008-04-12.
Convert ( Varchar ( 10 ), Getdate (), 20 )
-- Returns 2008-04-12.
Convert ( Varchar ( 10 ), Getdate (), 112 )
-- Returns 20080412
Convert ( Varchar ( 10 ), Getdate (), 111 )
-- Back to 2008/04/12