SQL Server Learning Note 1

1. Query installation collation options drink the current collation server properties

SELECT * from fn_helpcollations ();

2. View the collation of the current server

Select SERVERPROPERTY (' Collation ') as servercollation;

3. Modify the collation of the database DB1 so that he is case-sensitive

Create DATABASE DB1

Go

ALTER DATABASE DB1

Collate sql_latin1_general_cp1_cs_as;

Go

Select Databasepropertyex (' DB1 ', ' Collation ') as Databasecollation

4.SQL server can set collations not only at the server, database, and column levels, but even at a separate query level.

SELECT * from Product ORDER by name Collate Danish_norwegian_ci_ai

5. An interesting phenomenon, look for the 6 lowest prices in the product table below:

Select Top 6 Productnumber,name,listprice  from Product  orderbydesc

Results:

ProductNumber	Name	ListPrice
Num1	Name1	4000
Num2	Name2	4000
Num3	Name3	4000
Num4	Name4	4000
Num5	Name5	4000
Num6	Name6	5000

It seems that the query is concise and the results are good, but it is wrong. If you look at the raw data sorted by price, you will find that the price is 4000 with 5 lines, and the price bit 5000 has 4 lines. If you want to show all 4 rows, you need to use the WITH TIES option, which allows the last position to contain multiple rows, provided that the values of these rows have the same value in the column used by the ORDER BY clause. The query is as follows:

 select  top  6  with   ties Productnumber,name, ListPrice  from  product order  Span style= "color: #0000ff;" >by  ListPrice desc  

ProductNumber	Name	ListPrice
Num1	Name1	4000
Num2	Name2	4000
Num3	Name3	4000
Num4	Name4	4000
Num5	Name5	4000
Num6	Name6	5000
Num7	Name7	5000
Num8	Name8	5000
Num9	Name9	5000

SQL Server Learning Note 1