Saturday, October 25, 2003

Source: WebLogs @ SqlJunkies.com The COLLATION of a column defines (among other things) the case sensitivity of search arguments when qualifying rows to be returned from SELECT statements as defined in WHERE clauses. But another important consideration is that a column's collation can also affect the behaviour of a unique / primary key if one exists on a column with collate defined. For example, the following script demonstrates that uniqueness of rows inserted into a key depends on the collation of the key column. Note carefully that the collation is either “_CS_” or “_CI_” - denoting case sensitive or case insensitive. Depending on how ths collation is set, the “duplicate” rows are either allowed or rejected by SQL Server. set nocount on go create table t1 (  col1 varchar (10) collate Latin1_General_CS_AS not null primary key) create table t2 (  col2 varchar (10) collate Latin1_General_CI_AS not null primary key) go insert into t1 (col1) values ('a') go insert into t1 (col1) values ('A') go insert into t2 (col2) values ('a') go insert into t2 (col2) values ('A') go select * from t1 select * from t2 go drop table t1 drop table t2 go Output from this script is: Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__t2__2042BE37'. Cannot insert duplicate key in object 't2'. The statement has been terminated. col1       ---------- a A col2       ---------- a The case in point here is simply that the case sensitivity in any given collation does not simply affect row qualification for select statements. It also affects the uniqueness of rows permitted by the column's key (if one exists). In short, take care to get the case sensitivity right (usually case insensitive would be expected) when setting collations on primary keys! Greg Linwood www.bainlinwood.com   [WebLogs @ SqlJunkies.com] 6:30:41 PM    trackback []     Articulate []