As stated in the comments, dates are not stored as "dates". They are actually stored as numbers. So there is no need to worry about that side of things. You can change the default output though by changing your language setting.
EXEC sp_configure 'default language', '23';
GO
RECONFIGURE
GO
This is the setting for British English
. You can see all of them displayed in the syslanguages
system view. syslanguages
also contains the default date format. In the case of British English
it is dmy
. American English
is mdy
.
First, just to make sure that a collation is available or not, you can test in two ways:
Check the list of available collations (i.e. sys.fn_helpcollations()
). Running the following query in SQL Server 2012 and 2014 should return 52 rows:
SELECT * FROM sys.fn_helpcollations() WHERE [name] LIKE N'ukrain%';
Try using the collation:
SELECT N'a' COLLATE Ukrainian_100_CI_AS;
If a collation is not available, it will let you know ;-)
Next, we should see if the collations work as expected (best not to fully trust documentation, right ;-) ?).
To test these two collations, run the following:
DECLARE @CollationTest TABLE (Col1 NVARCHAR(5));
INSERT INTO @CollationTest (Col1) VALUES (N'Ы'), (N'Ю'), (N'Ь'), (N'Ъ'), (N'Я'), (N'Э');
SELECT Col1 AS [Ukrainian_100_CS_AS]
FROM @CollationTest
ORDER BY Col1 COLLATE Ukrainian_100_CS_AS;
SELECT Col1 AS [Cyrillic_General_100_CS_AS]
FROM @CollationTest
ORDER BY Col1 COLLATE Cyrillic_General_100_CS_AS;
Results:
Ukrainian_100_CS_AS
----------------------------
Ъ
Ы
Э
Ю
Я
Ь
Cyrillic_General_100_CS_AS
----------------------------
Ъ
Ы
Ь
Э
Ю
Я
Those two orderings at least match the collations linked in the question (which makes sense given that those collation charts were done in 2009 and the _100
series of collations came out in SQL Server 2008).
The only difference seems to be in the initial sort order of one character: Ь (Capital Letter Soft Sign). Is the placement of one character a determining factor? You do need to mirror, as much as possible, the expectations of your customers. But, keep in mind that sort rules and initial ordering changes over the years.
Here are some examples of the Unicode spec for the Ukrainian locale. As you can see, both placements have been correct, though the "current" placement matches the Cyrillic collation, not the Ukrainian collation. The main release history of the locale-specific data starts here: CLDR Releases/Downloads
http://www.unicode.org/repos/cldr/tags/release-1-4/posix/uk_UA.UTF-8.src (2006)
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN> <X5EF0>;<X0005>;<X008F>;IGNORE -- Ь
<CYRILLIC_CAPITAL_LETTER_YA> <X5EFC>;<X0005>;<X008F>;IGNORE -- Я
http://www.unicode.org/repos/cldr/tags/release-1-9/posix/uk_UA.UTF-8.src (2010)
<CYRILLIC_CAPITAL_LETTER_YA> <X5D45>;<X05>;<X8F>;IGNORE -- Я
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN> <X5D46>;<X05>;<X86>;IGNORE -- Ь
http://www.unicode.org/repos/cldr/tags/release-21/posix/uk_UA.UTF-8.src (2011)
<CYRILLIC_CAPITAL_LETTER_YA>
<X5D45>;<X05>;<X8F>;<CYRILLIC_CAPITAL_LETTER_YA> -- Я
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN>
<X5D46>;<X05>;<X86>;<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN> -- Ь
http://www.unicode.org/repos/cldr/tags/release-24/common/uca/allkeys_CLDR.txt (2013)
042C ; [.1B43.0020.0008.042C] # CYRILLIC CAPITAL LETTER SOFT SIGN -- Ь
042F ; [.1B5E.0020.0008.042F] # CYRILLIC CAPITAL LETTER YA -- Я
http://www.unicode.org/repos/cldr/tags/release-27/common/uca/allkeys_CLDR.txt (2015)
042C ; [.1E56.0020.0008] # CYRILLIC CAPITAL LETTER SOFT SIGN -- Ь
042F ; [.1E6D.0020.0008] # CYRILLIC CAPITAL LETTER YA -- Я
And to be fair, we should keep in mind that collations handle more than sorting. They handle casing, transformations, etc.
So what should one do? It is probably best, regardless of the rest of this, to choose the locale that is most common for your customers to be using as their main Windows OS locale. This will at least give consistent behavior to how everything else should be running for them. Run the following to see on your system:
SELECT os.os_language_version AS [WindowsLocale],
COLLATIONPROPERTY(N'Ukrainian_100_CS_AS', 'lcid') AS [SQLServerLocale-Ukrainian],
COLLATIONPROPERTY(N'Cyrillic_General_100_CS_AS', 'lcid') AS [SQLLocale-CyrillicGeneral]
FROM sys.dm_os_windows_info os;
Ask as many of your customers / potential customers as possible to check their Windows setup so that you can plan accordingly.
Best Answer
In general you should be ok. The only area that I am aware of that could be affected is SQLCLR, in which case the following conditions must be true:
(
The LCID of the OS does not match the LCID of the default Collation of the Database where the Assembly is loaded,
OR
The default Collation of the Database where the Assembly is loaded is either a binary Collation, or is any combination of the following: case-sensitive, accent-insensitive, width-sensitive, or Kana-type-sensitive
),
AND
custom SQLCLR code is being used
SqlString
is being used as an input parameter typeSqlString
parameter is being concatenated inline with astring
while not using either theValue
property or theToString()
method (i.e."some string" + SqlStringInputParam;
)Regex
.To be clear, this issue relates to the default Collation of the Database in which the Assembly is loaded, not the Instance- / Server- level Collation, nor even the Collation of any string column or any string literal or variable with a Collation set via the
COLLATE
keyword.If your Database has a default Collation of
SQL_Latin1_General_CP1_CI_AS
because that is what the Instance-level Collation is, then neither # 1 nor # 2 above are true. And in that case, IF there is any SQLCLR code in that Database that matches items 4 - 6 above, then you wouldn't see any problem. BUT, changing the Locale of the OS would make item # 1 above true, in which case if there is any SQLCLR code that matches item 4 - 6 above, then that code will start to throw "Collation mismatch" exceptions.Admittedly there is a fairly low probability of running into this. But, it has happened, so it's best to be aware of it.