Should be possible to specify collation when define a type/domain - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 451830 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 5/16/2009 5:35:57 AM
Access Restriction Public


Today you can define so-called user-defined types, a poor replacement for real domains.
One thing you cannot specify for a user-defined type is the collation. This can be quite essential. In a database you typically want columns for things like customer and product names to be case-insensitive to faciliate searching. But at the same time you may have columns for codes or various identities where you either only want to accept uppercase, or where case actually is significant. For instance, if you have columns for currency code, you always want USD, EUR, GBP etc appear as such, never as "usd", "eur". While this can be enforced with constraints or rules, it would be natural to use a CS collation for all these columns, and then rely on the FK. 
Sign in to post a comment.
Posted by Microsoft on 5/24/2011 at 3:42 PM

As we pointed out in a previous post, being able to associate a specific collation when creating a user-defined type has its advantages, especially considering the re-use of UDTs (e.g., saving users the trouble to re-specify collations, constriants, etc. when making use of it in a schema).

However, we are closing this one as "Won't fix" since its priority has been so low that we do not anticipate getting to this in the next release.

If you feel this item should receive more attention from us, feel free to respond to my notification and we can reconsider.


Joachim Hammer
SQL Server
Posted by Microsoft on 6/5/2009 at 12:25 PM
Hi Erland,

Thanks for this suggestion. Seems like a good idea. You may also be hinting that collation is so fundamental that it really is a key part of "DataType"? ANSI/SQL allows the two almost separate existence.

Anyhow, I'll keep this on the candidate list. (but can't promise we'll do this one soon).

Jim Hogg
Posted by AKuz on 5/16/2009 at 4:01 PM
This would allow for safer programming.
Posted by Steve Kass on 5/16/2009 at 2:10 PM
It looks to me like this is part of the latest ANSI/ISO standard also. Here's the relevant syntax productions for CREATE TYPE:

<user-defined type definition> ::=
CREATE TYPE <user-defined type body>

<user-defined type body> ::=
<schema-resolved user-defined type name> [ AS <representation> ]

<representation> ::=
<predefined type>

<predefined type> ::=
<character string type>
    [ CHARACTER SET <character set specification> ]
    [ <collate clause> ]
| <national character string type> [ <collate clause> ]

<character string type> ::=
    CHARACTER [ <left paren> <length> <right paren> ]
| CHAR [ <left paren> <length> <right paren> ]
| CHARACTER VARYING <left paren> <length> <right paren>
| CHAR VARYING <left paren> <length> <right paren>
| VARCHAR <left paren> <length> <right paren>
| <character large object type>

<collate clause> ::=
COLLATE <collation name>