1.sp_helpsort
SELECT serverproperty (' collation ')
See your sorting rules.
But you should have something to do with the character set.
2. Change Server Collation
Changing the default collation of an instance of SQL Server 2005 can be complex, including the following steps:
Make sure that you have all the information or scripts you need to re-create the user database and all the objects in those databases.
Export all data using tools such as bulk copy.
Deletes all user databases.
Regenerates the master database that specifies the new collation in the Sqlcollation property of the Setup command. For example:
Start/wait setup.exe/qb instancename=mssqlserver reinstall=sql_engine rebuilddatabase=1 SAPWD=test Latin1_general_cp1_ci_ai
For more information about rebuilding the master database, see how to rebuild the master database for SQL Server 2005.
Create all databases and all objects in those databases.
Import all data.
Attention：
You can specify a default collation for each new database that you create, without changing the default collation for an instance of SQL Server 2005.
3. Setting and changing database collation
When you create a new database, you can specify collations by using one of the following:
The COLLATE clause of the CREATE DATABASE statement.
SQL Server Management Studio.
The Database.collation property in the SQL management Object (SMO).
If no collation is specified, the server collation is used.
You can use the COLLATE clause of the ALTER database statement to change the collation of any new objects created in the user database. You cannot use this statement to change the collation of a column in any existing user-defined table. You can change the collation of these columns by using the COLLATE clause of ALTER TABLE.
When you change a database collation, you need to change the following:
The default collation of the database, which applies to all subsequent columns, user-defined data types, variables, and parameters that are created in the database. The new default collation is also used when parsing an object identifier specified in an SQL statement based on an object defined in the database.
Change any char, varchar, text, nchar, nvarchar, or ntext column in the system table to use the new collation.
Changes all existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to use the new collation.
Change the char, varchar, text, nchar, nvarchar, or ntext system data types and all user-defined data types based on these system data types to use the new default collation.
SQL Code:
1. Modify the character set of the database to:
Copy Code code as follows:
ALTER DATABASE dbname COLLATE chinese_prc_ci_as
2.
--1. To specify a collation for a database
Copy Code code as follows:
CREATE DATABASE db COLLATE chinese_prc_ci_as
Go
ALTER DATABASE db COLLATE Chinese_prc_bin
Go
/*====================================*/
--2. Specifying collations for columns in a table
Copy Code code as follows:
CREATE TABLE TB (
col1 varchar (10),
col2 varchar (a) COLLATE chinese_prc_ci_as)
Go
ALTER TABLE TB ADD col3 varchar (a) COLLATE Chinese_prc_bin
Go
Alter TABLE TB ALTER COLUMN col2 varchar COLLATE chinese_prc_bin
Go
/*====================================*/
--3. Apply collations to character variables and parameters
Copy Code code as follows:
DECLARE @a varchar (a), @b varchar (10)
SELECT @a= ' A ', @b= ' a '
--Use Collation chinese_prc_ci_as
SELECT case when @a COLLATE chinese_prc_ci_as = @b THEN ' @a=@b ' ELSE ' @a<>@b ' end
--Results: @a=@b
--Use Collation Chinese_prc_bin
SELECT case when @a COLLATE chinese_prc_bin = @b THEN ' @a=@b ' ELSE ' @a<>@b ' end
--Results: @a<>@b
3.
Table
Copy Code code as follows:
ALTER TABLE TB
ALTER COLUMN colname nvarchar (MB) COLLATE chinese_prc_ci_as
--No case sensitive
ALTER TABLE TB
ALTER COLUMN colname nvarchar (MB) COLLATE chinese_prc_cs_as
--Case sensitive
Database
Copy Code code as follows:
ALTER Database Database
COLLATE chinese_prc_cs_as
--Case sensitive
ALTER Database Database COLLATE chinese_prc_ci_as-case insensitive
Method OneSelect Case sensitive when installing SQL
or rebuild Mastar after installation, select the size
C:\Program Files\Microsoft SQL Server\80\tools\binn\rebuildm.exe
Method TwoThe version above SQL Server 8.0 is available, and 7.0 and below are not supported
ALTER DATABASE COLLATE Chinese_prc_cs_as
Modify collation, change to case sensitive collation
If you modify only one table, use the ALTER TABLE statement
If you modify the default collation for a library, use the ALTER DATEBASE statement
If you modify the default collation for the entire server, rebuild the master library with Rebuildm.exe
--Specify the collation.
--Example
Copy Code code as follows:
Select replace (' ABACB ' collate chinese_prc_cs_as_ws, ' B ', ' test ')
-If you are asking for table support, you can specify the collation when you build the table so that replace does not have to write the collation
--Example
Copy Code code as follows:
CREATE TABLE TB (a varchar (a) COLLATE chinese_prc_cs_as_ws)
Insert TB values (' Abac ')
Select Replace (A, ' a ', ' test ') from TB
DROP table TB
Specifying collations can
The Windows collation name specifies the Windows collation name in the COLLATE clause. Windows collation names consist of collation indicators and comparison styles.
Grammar
Copy Code code as follows:
< Windows_collation_name >:: =
Collationdesignator_ <ComparisonStyle>
< ComparisonStyle >:: =
Casesensitivity_accentsensitivity
[_kanatypesensitive [_widthsensitive]]
| _bin
Parameters
Collationdesignator
Specifies the basic collation used by Windows collations. The basic collation includes:
The alphabet or language whose collation is applied when sorting by dictionary is specified
A code page for storing non-Unicode character data.
For example, Latin1_General or French, both use code page 1252, or Turkish, which uses code page 1254.
Casesensitivity
CI Specifies case-insensitive, CS specifies case sensitive.
Accentsensitivity
AI specifies accent insensitive, as specifies accent sensitivity.
Kanatypesensitive
omitted specifies case-insensitive, KS specifies the kana type.
Widthsensitivity
omitted specifies case-insensitive, and WS specifies case sensitivity.
BIN
Specifies that the binary sort order is used.
If you are only the current query to distinguish, then still don't change, lest again, so query:
SELECT * from a
/*
A_nam A_add
---------- ----------
1 AA
1 BB
2 cc
2 VV
2 KK
3 DD
3 EE
4 DD
5 EE
6 yy
6 yy
(one row (s) affected)
*/
Now we inquire A_add = ' AA ', ' AA ' and so on!
Example 1：
Copy Code code as follows:
SELECT * from a
where A_add collate chinese_prc_cs_as_ws = ' AA '
/*
A_nam A_add
---------- ----------
1 AA
(1 row (s) affected)
*/
Example 2：
Copy Code code as follows:
SELECT * from a
where A_add collate chinese_prc_cs_as_ws = ' Aa '
/*
A_nam A_add
---------- ----------
(0 row (s) affected)
*/
Example 3. The above can't remember, then use the Stupidest method to convert to ASCII
Copy Code code as follows:
SELECT * from a
where
ASCII (substring (a_add,1,1)) = ASCII (substring (' Aa ', 1, 1))
and
ASCII (substring (a_add,2,1)) = ASCII (substring (' Aa ', 2, 1))
/*
A_nam A_add
---------- ----------
(0 row (s) affected)
*/
Example 4: Any version can be
Copy Code code as follows:
SELECT * from a
Where cast (a_add as varbinary) = cast (' AA ' as varbinary (10))
