Collection of SQL Server tips Set _ database Other

The last time I wrote an access technique set, it was good, so this is a special technique for SQL statements.

1. To reactivate a field (from 1 to N):
DECLARE @i int
Set @i = 0
Update Table1 Set @i = @i + 1,field1 = @i

2. Ranking by grade
Update Score Sheet
Set A. Rank = (
Select Count (*) + 1
From performance Form B
Where A. Total score < b. Total Score
)
From Performance form a

3. Query external database
Select a.*
From OpenRowSet (' microsoft.jet.oledb.4.0 ', ' C:\test.mdb '; Admin '; ', Table1) a

4. Query Excel File
Select *
From OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:\Test.xls '); User id=admin; password=; Extended properties=excel 8.0 ') ... sheet1$

5. Specify the collation in the query
Select * from Table1 ORDER by Field1 COLLATE Chinese_prc_bin
Why do you want to specify a collation? See also:
http://www.delphibbs.com/delphibbs/dispq.asp?lid=1633985
For example, check to see if the specified user exists in the Pub_users table in the database:
Select Count (*) from pub_users Where [username]= ' admin ' and [password]= ' AAA ' COLLATE chinese_prc_bin
The default comparison is case-insensitive and, if not collate chinese_prc_bin, the password AAA is equivalent to AAA, which of course does not match the reality. Note that the collation is specified for each condition, and the user name is not case-sensitive in the example above.


A little trick of 6.Order by
Order by can specify a column sequence without specifying a column name, which is useful in the following example (note that the third column does not specify an alias)
Select A.id,a.name, (select Count (*) from TableB b Where a.id=b.pid) from TableA a order by 3