Collection of commonly used t_ SQL statements (continuous sorting)

Mainly records some commonly used t_ SQL statements for future queries:

1. Restore filelistonly statement
Returns a result set consisting of a list of databases and log files contained in the backup set.
For example, restore filelistonly from disk = 'd:/sqltest. Bak '. 2. A single SQL statement is required to obtain the first N records of each news classification.
Method 1:
Select * from news a where ID in (select Top 2 ID from news group by kind, Id order by ID)
Method 2:
Select * from news a where ID in (select Top 2 ID from news where. kind = kind order by ID) 3. Repeat a field from 1 to n: declare @ I int
Set @ I = 0
Update Table1 set @ I = @ I + 1, field1 = @ I 4. Specify the sorting rule select * From Table1 order by field1 collate chinese_prc_bin in the query.

5. A tip about order by: Order by can specify the column order instead of the column name. In the following example, it is useful (note that the third column does not specify an alias)
Select. ID,. name, (select count (*) from tableb B where. id = B. PID) from tablea a order by 3 6. query the Excel file (the file must be on the machine where the database is located)
Select * fromopenrowset ('Microsoft. jet. oledb.4.0 ', 'excel 8. 0; HDR = yes; database = C:/book1.xls; ',' [sheet1 $] ') 7. Method for querying data in access
Select * From OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = C:/db2.mdb ', 'select * From serv_user') 8. The script creates a linked server and logs on to Exec sp_add1_server @ Server = 'dpvserver1 ', @ srvproduct = '', @ provider = 'sqloledb', @ datasrc = 'serverb' go exec sp_add1_srvlogin @ rmtsrvname = 'dpvserver1', @ useself = 'false ', @ rmtuser = 'sa ', @ rmtpassword = 'Password'
Go9 and round usage: Rounding: Select round (150.75, 0) 151.00
Truncation: Select round (150.75, 0, 1) 150.00 10. Use the OpenDataSource function to remotely query select * From OpenDataSource ('sqlodb', 'Data source = IP; user id = test; password = test '). esapp. DBO. AA