[SQL Server] SQL Server collate settings (character set)

SQL Server collate settings (character set)

-- XP Chinese Environment
Use master
Go
If db_id ('test') is not null drop database Test
Go
Create Database test -- default database creation collate = chinese_prc_ci_as is case insensitive
Go
Use test
Go
Create Table test1 (id int)
Go
Select * From test1 -- this query is correct

-- Test Case sensitivity
Alter database test collate chinese_prc_cs_as -- case sensitive
Go
Select * From test1 -- the query result returns an error
Go
/*
Message 208, level 16, state 1, 1st rows
The object name 'test1' is invalid.
*/

-- Change back to default collate = chinese_prc_ci_as
Alter database test collate chinese_prc_ci_as -- case insensitive
Go
Select * From test1 -- this query is correct
Go

-- View the collate of the data

Sp_helpdb Test
-- The status contains collation = chinese_prc_cs_as.

 

 

Status = online, updateability = read_write, useraccess = multi_user, recovery = simple, version = 611, collation = offline, sqlsortorder = 0, isautoclose, isautocreatestatistics, isautoupdatestatistics, isfulltextenabled