Tag Info

Hot answers tagged

19

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 ...


17

[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 ...


11

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 ...


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

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

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 ...


9

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 ...


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, ...


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 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.


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

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 ...


7

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. ...


6

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 ...


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

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 ...


5

Be sure you really want to "drop" the user databases as noted in the answer above. You may just want to "detach" the databases. Or really, you can do nothing as rebuilding the master effectively removes any links to the user databases. There are times when the databases are created in the desired collation but the server isn't. You wouldn't want to have to ...


5

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 ...


5

Well, the trick is that a database can only specify which "locale" it is used for at creation time. When you create a database you either specify what you want by specifying the codeset, territory and collation (example CREATE DATABASE MYDB AUTOMATIC STORAGE YES ON '/data' DBPATH ON '/dbdir' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM), or you let ...


5

SQL server must version the collations. Since collations determine sort order of data persisted in the database the collation must be guaranteed to remain stable between the releases. Otherwise a collation change (eg. fixing a bug in a collation) with a new release of Windows it would result in two rows int he database to sort differently before and after ...


5

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 ...


5

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 ...


5

sys.server_triggers is a server view, so it has the server collation. sys.triggers is a database view, so it has the database collation. You have a DB that has a collation different from the server. QED. Repro: create database foo collate Latin1_General_CI_AI use foo select tr.name from sys.triggers tr union all select tr.name from sys.server_triggers tr; ...


4

That's a limit of the InnoDB engine. It cannot index more than 767 bytes (for a single column) See the manual: http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html By default, an index key for a single-column index can be up to 767 bytes ... When you attempt to specify an index prefix length longer than allowed, the length is silently ...


4

Along the lines of this with 2 where conditions. One will be become '%string%' LIKE '%string%' which is always true WHERE CASE WHEN @casesenstive = 1 THEN COL3 ELSE '%string%' END COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%string%' AND CASE WHEN @casesenstive = 0 THEN COL3 ELSE '%string%' END COLLATE ...


4

varchar doesn't support ≥: unicode only This includes the literal '≥' which is varchar too Collation here doesn't matter: this is sorting and comparison only never seen this before! Some more SQL to use your table SELECT ASCII('≥'), CHAR(61), '≥' GO insert into char_test values ('≥'); GO select item, ASCII(item) from char_test; GO select replace(item, ...


4

I did something like this and it worked but you have to keep in mind the indexes that are pointing to data type as text/varchar/nvarchar have to be dropped, run the script and then create the indexes. USE YourDataBase GO DECLARE @Table_Name NVARCHAR(100) SET @Table_Name = NULL--- THIS IS THE TableName that you want to change its collation columns --- if ...


4

If you want a painful way to prove what @gbn is saying (essentially that you can't tell a collation to order substrings differently), you can make a quick #temp table that has a coefficient for the order you expect, and see if ordering by any collation returns the same order: CREATE TABLE #foo(id INT, n NVARCHAR(10)); CREATE TABLE #bar(collation SYSNAME); ...


4

There isn't a single SQL command that will do that for you. You can write a loop in a different language, using the results from SELECT table_name, column_name FROM information_schema.columns WHERE table_schema IN ('your_schemas') AND data_type = 'text' AND collation_name IS NULL; or similar.


3

From a quick test this end none of the built in collations can represent all characters in that string in a single byte code page so you would need to use a unicode datatype. SET NOCOUNT ON; DECLARE @StringToTest nvarchar(2047) = N'بِسْمِ اللَّهِ الرَّحْمَٰنِ الرَّحِيمِ' --The below without the problematic character returns 7 results --DECLARE ...



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