Hot answers tagged

21

Yes. You can change the default collation of SQL Server 2008 R2 express instance and individual databases, but it is a complex task. Sadly, there is no visual option to do it via SSMS. SQL Server 2008 supports setting collations at the following levels: Server Database Column Expression The default installation settings are ...


19

First of all, apologies for such a long answer, as I feel that still there is a lot of confusion when people talk about terms like collation, sort order, code page, etc. From BOL : Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and ...


18

[0-9] is not some type of regular expression defined to just match digits. Any range in a LIKE pattern matches characters between the start and end character according to collation sort order. SELECT CodePoint, Symbol, RANK() OVER (ORDER BY Symbol COLLATE Latin1_General_CI_AS) AS Rnk FROM #CodePage WHERE Symbol LIKE '[0-9]' COLLATE ...


17

The trick here is to realize that these characters that you see in the question with the "accents" aren't really the characters (i.e. "These aren't the droidscharacters you are looking for" ;-) ). The "accents" are various types of notations indicating things like: vowels (lines and dots that are typically under the letters): base letter "ה" = "h"; "הֶ" ...


16

The official word from Microsoft: Some of the columns that contain pre-defined strings (like types, system descriptions, and constants) are always fixed to a specific collation – Latin1_General_CI_AS_KS_WS. This is irrespective of instance/database collation. The reason is that this is system metadata (not user metadata) and basically these strings are ...


13

To check for non-default collations on columns, you can use the following query: select table_schema, table_name, column_name, collation_name from information_schema.columns where collation_name is not null order by table_schema, table_name, ordinal_position; Edit: to find the collation of the database, you need to ...


12

Generally speaking, one of the Unicode variants is probably the best for broad language support - UTF-8 is going to use less memory per codepoint, and thus will have a slight advantage in any time/space tradeoffs you find yourself in need of making; however, I think there are some of the more esoteric languages/scripts that UTF-8 cannot represent (but I'm ...


10

No, there isn't. SQL Server doesn't support UTF-8. You need to define your columns as nvarchar/nchar if you want unicode data. Note, internally SQL Server stores this as UCS-2. Note that this has ben requested from MS on Connect and there is an older KB article. And some info on this blog too


10

Collation determines two things. The code page used (and hence characters that can be stored) for non unicode data. The comparison semantics for all textual data. (NB: SQL Server 2012 also introduces _SC, supplementary character, collations for UTF-16 encoding rather than UCS-2 but these are not relevant to your question here) SELECT * FROM ...


10

You need to use NCHAR(1 - 4000) or NVARCHAR, either as NVARCHAR(1 - 4000) or NVARCHAR(MAX) for storing anywhere from 4001 to just over 1,073,741,822 characters (or possibly less if storing any supplementary characters as described below). Technically, you can store Japanese characters in VARCHAR fields if you use a Japanese_* Collation that is associated ...


10

Collations in SQL Server determine the rules for matching and sorting character data. Normally, you would choose a collation first based on the comparison semantics and sorting order the consumers of the data require. Humans generally do not find that binary collations produce the sorting and comparison behaviours they expect. So, although these offer the ...


10

Without having an example of the data as well as the table DDL from the O.P., it is difficult to say for certain what the exact cause of this error is for the O.P. However, this behavior (and hence problem) can happen for others for the following reason: Some Code Pages (which are determined by the Collation of each CHAR / VARCHAR field) allow for ...


9

The C collation is the right choice. Everything is a bit faster without locale. And since no collation is right anyway, create the database without collation, meaning with C. It may be a pain to have to provide a collation for many operations. There shouldn't be a noticeable difference in speed between the default collation and an ad-hoc collation, though. ...


9

Given that this is an existing database that already has tables defined in it, there are some very serious implications to the action of changing the database collation, beyond the potential performance impact to DML opertions (which actually was already there). There is very real impact to performance and functionality, and this change not only did not ...


8

Yes, you can set the default character set and collation on various levels. First, in your question, on the database level: CREATE DATABASE IF NOT EXISTS foo DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci' or ALTER DATABASE foo DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci' All new tables in that database should ...


8

This looks like more of a SQL mode issue than the char set. Strict mode (and more specifically NO_ZERO_DATE which is part of strict mode) usually sets off that error if the table was created with an all zero default date before turning on strict. What we use (for similar modified date columns), is the '1970-01-01 00:00:01'. and on a slightly related note, ...


8

I would setup a computed column then sort based on that. Something like CAST( CASE WHEN IS_NUMERIC(left(OtherColumn, 2) = 1) then left(OtherColumn,2) else left(otherColumn, 1) AS INT) Then use this column to sort by as you can now index the column.


8

The one without the code is a version 90 (SQL Server 2005) collation, used by previous versions of Windows / SQL Server. The collation including 100 is newer (SQL Server 2008). There's a bit of an overview of the differences in the SQL Server 2008 documentation, here, however it doesn't go into great detail. If you're developing a new application, you may ...


8

The documentation often gives you an answer to such questions. Like in this case, too: The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by ...


7

Quite honestly, I think your easiest approach will be: backup your user databases uninstall SQL Server reinstall SQL Server with the right collation restore your databases fix the collation on the user databases Also you know that SQL Server 2000 is well out of mainstream maintenance, right? And that quite soon there will have been FOUR major releases ...


7

I believe that you should use a Unicode collation that is accent and case insensitive. Please read the MSDN articles Selecting Collation and Using sql collations and all linked articles.


7

I suggest you pick a collation that provides the default Unicode ordering. That way, you get sane results even if you don't override the collation in each query. Unfortunately, most (all?) operating systems don't provide a locale that is simply named "default Unicode" or something like that, so you will have to guess and/or research a good choice. For ...


7

First, as you have now seen, you cannot directly alter meta-data in the system views. However, you could change the setting for a particular database using ALTER DATABASE: ALTER DATABASE { database_name | CURRENT } COLLATE collation_name; Please note that the option to use the CURRENT keyword was introduced in SQL Server 2012. OR, if you only want to ...


7

This question is far more complicated than it appears to be on the surface (hence the longer-than-most-would-expect answer). If the strings being searched were codes (postal codes, ISO country codes, ISO state codes, SKUs, etc) or something where the characters used were a limited subset of all possible letters of all languages, then this would be fairly ...


7

The sysdac_instances view is defined as follows: CREATE VIEW [dbo].[sysdac_instances] AS SELECT -- this must be locked down because we use instance_id visability as a security gate case when (dbo.fn_sysdac_is_currentuser_sa() = 1) then dac_instances.instance_id when sd.owner_sid = SUSER_SID() then ...


6

No. Collation is about alphabetical sorting, depending on code page, accent, case, width, kana. Numbers characters (0-9) have none of there properties. So 9 is always after 10B in any sort. You have to split it up as you noted or sort like this: ORDER BY RIGHT(' ' + MyColumn, 30) The length in the right determines how ...


6

Latin1 is code page 1252, in which 178 is 'SUPERSCRIPT TWO'. This is an Unicode superscript: is the character "2" as superscript. According to the Unicode Technical Standard #10 it should compare equal to 2, see 8.1 Collation Folding: Map compatibility (tertiary) equivalents, such as full-width and superscript characters, to representative character(s) ...


6

Why does SQL convert Unicode 9619 to ASCII code 166? SQL Server is not employing any special custom logic here; it is using standard operating system services to perform the conversion. Specifically, the SQL Server type and expression service (sqlTsEs) calls into OS routine WideCharToMultiByte in kernel32.dll. SQL Server sets the input parameters to ...


6

Converting from Unicode data to a particular Code Page employs what is known as the "Best-fit" strategy (as noted in @Paul's answer and in the link that @Martin noted in a comment on the Question). According to that MSDN page for Character Encoding in the .NET Framework: Best-fit mapping is the default behavior for an Encoding object that encodes Unicode ...


6

What makes you think you are comparing dates? Actually, you are comparing string literals which - in the absence of a cast context and any explicit cast - default to text: SELECT '20150526' > '2015-05-26' AS text2text , '20150526'::date > '2015-05-26'::date AS date2date; text2text | date2date ----------+---------- t | f Try: SELECT ...



Only top voted, non community-wiki answers of a minimum length are eligible