COLLATION

Question

COLLATION

deepzzzz

SSCrazy

Points: 2890
More actions
November 2, 2010 at 9:41 pm

#231199

Comments posted to this topic are about the item COLLATION
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19254 More actions · Answer 1

An interesting question about commands that I was not aware of.

However, your explanation of fn_helpcollations is not entirely accurate for those of us using 2008. BOL describes it as 'Returns a list of all the collations supported by SQL Server 2008' with a note 'Deprecated collations that are no longer supported or installed in SQL Server 2008 cannot be queried with fn_helpcollations'. I have no idea how the 2005 BOL describes it, but if you don't specify the version you need to be aware of all of the possible variations in the documentation.

Steve Hall
Linkedin
Blog Site

cengland0 SSCertifiable Points: 6102 More actions · Answer 2

Curious why it was being converted to Char in the query.

vk-kirov SSCertifiable Points: 7686 More actions · Answer 3

Conversion to CHAR is wrong, because in this case we get a collation name which is truncated to 30 symbols: Chinese_Hong_Kong_Stroke_90_CS instead of Chinese_Hong_Kong_Stroke_90_CS_AS_KS_WS, SQL_Latin1_General_CP1250_CI_A insted of SQL_Latin1_General_CP1250_CI_AS etc.

The type of a collation name is nvarchar(128) (http://msdn.microsoft.com/en-us/library/ms190305.aspx), so the correct statement should be "SELECT CONVERT(nvarchar(128), ...)".

DigitalTeapot Valued Member Points: 54 More actions · Answer 4

Hi

The following TSQL statement would also work (SQL Server 2005 and later):

SELECT collation_name FROM sys.databases WHERE name = N'MyDatabase'

Regards,

DigitalTeapot

Peter Rijs Hall of Fame Points: 3400 More actions · Answer 5

I'm also curious for the reason to convert to char, as the following syntax will work just fine for me:

SELECT DATABASEPROPERTYEX('MyDatabase','collation')

It is very clear what answer should be picked on this question, since the other 2 answers do not return (a part of) the database collation (though server collation might be, but is not necessarily, the same value as the database collation).

Peter Rijs
BI Consultant, The Netherlands

SanDroid SSChampion Points: 10068 More actions · Answer 6

Nice question.

I learned something, there were no errors or typos with the answer, and the reference data supported the documentation.

The only negative comment I have is that this code:

SELECT CONVERT(char, SERVERPROPERTY('collation'),'MyDatabaseName')

Returned an error.

cengland0 SSCertifiable Points: 6102 More actions · Answer 7

SanDroid (11/3/2010)
Nice question.
I learned something, there were no errors or typos with the answer, and the reference data supported the documentation.
The only negative comment I have is that this code:
SELECT CONVERT(char, SERVERPROPERTY('collation'),'MyDatabaseName')
Returned an error.

It's okay that it returned an error because that wasn't the correct answer. The author of the question probably included that as a distractor in an attempt to get some people to select that answer.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 8

Good question. Thanks to VK to pointing out the Char issue (reinforcing what I know.....but frequently forget).

Michael Poppers SSCrazy Points: 2151 More actions · Answer 9

cengland0 (11/3/2010)
SanDroid (11/3/2010)
Nice question.
I learned something, there were no errors or typos with the answer, and the reference data supported the documentation.
The only negative comment I have is that this code:
SELECT CONVERT(char, SERVERPROPERTY('collation'),'MyDatabaseName')
Returned an error.
It's okay that it returned an error because that wasn't the correct answer. The author of the question probably included that as a distractor in an attempt to get some people to select that answer.

Yes, he might have been better off listing the possible choice as SELECT CONVERT(char, SERVERPROPERTY('collation','MyDatabaseName')) and perhaps that was his intention :-).

cengland0 SSCertifiable Points: 6102 More actions · Answer 10

Michael Poppers (11/3/2010)
Yes, he might have been better off listing the possible choice as SELECT CONVERT(char, SERVERPROPERTY('collation','MyDatabaseName')) and perhaps that was his intention :-).

Agreed. I work in a training organization and consider myself a quiz expert. Writing many quiz applications and writing the actual quizzes too. I can say with 100% certainty that it was a bad distractor because anyone that knows how to use the convert command would know that you don't put the Database name as the second parameter. You can then remove that possible answer as one that you should select.

It could have been offered as

SELECT SERVERPROPERTY('collation','MyDatabaseName') and just leave off the Convert portion.

SanDroid SSChampion Points: 10068 More actions · Answer 11

Michael Poppers (11/3/2010)

and perhaps that was his intention :-).

Good point. It could have been his attention from the start.

It would not be the first time the QOTD was differant from what the Author thought he had posted.

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

I will jump on the bandwagon of not getting the convert to char but will point out that I never like code that just uses the default size for datatypes. I don't like to have to stop and think (or more likely lookup) what the default size is for a given datatype.

This is so much easier to read exactly what is being returned:

SELECT CONVERT(char(30), DATABASEPROPERTYEX('AdevntureWorks', 'Collation'))

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Thanks for the question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 14

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP