Classic T-SQL code

1. N to M Records (with primary index ID)
Select Top M - N * from [Table] Where ID inch (SelectTop M ID from [Table]) Order by ID Desc

2.Query Logical Order
(8) SELECT (9) DISTINCT ( One) <top_specification><select_list>
(1) from<left_table>
(3) <Join_type>JOIN<right_table>
(2) on<join_condition>
(4) WHERE<where_condition>
(5) GROUP by<group_by_list>
(6) with{CUBE|ROLLUP}
(7) having<having_condition>
(Ten) ORDER by<order_by_list>

3.Date conversion parameters, worth collecting
SelectCONVERT(varchar, getdate(), - )
2004- the- A One: .: ,

SelectReplace(Replace(Replace(CONVERT(varchar, getdate(), - ),'-',"'),'',"'),':',"')
20040912110608

SelectCONVERT(varchar( A) , getdate(), 111 )
2004/ the/ A

SelectCONVERT(varchar( A) , getdate(), the )
20040912

SelectCONVERT(varchar( A) , getdate(), 102 )
2004.09. A

4. The first day of one months
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate ()), 0)
-- 2009-06-01 00:00:00.000

5. Paging SQL statements
Select* from(Select(Row_number () Over (ORDER bytab.idDesc)) asRownum,tab.* fromtable name as tab) asTwhererownumbetweenStart position and end position
6. Get all user tables in the current database
Select* fromsysobjectswherextype='U' andcategory=0
7. Get all the fields of a table
Selectname fromsyscolumnswhereID=object_id('Table name')

8. View the views, stored procedures, functions associated with a table
SelectA.* fromsysobjects A, syscomments bwherea.id=b.id andB.text like'% table name%'
9. View all stored procedures in the current database
Selectname asstored procedure name from sysobjectswherextype='P'
10. Querying the fields and data types of a table
Select Column_name,data_type from Information_schema.columns where table_name = ' Table name '
11. Delete duplicate values

Classic T-SQL code