Handy SQL Server Function Series: Part 1
- by Most Valuable Yak (Rob Volk)
I've been preparing to give a presentation on SQL Server for a while now, and a topic that was recommended was SQL Server functions. More specifically, the lesser-known functions (like @@OPTIONS), and maybe some interesting ways to use well-known functions (like using PARSENAME to split IP addresses) I think this is a veritable goldmine of useful information, and researching for the presentation has confirmed that beyond my initial expectations.I even found a few undocumented/underdocumented functions, so for the first official article in this series I thought I'd start with 2 of each, COLLATIONPROPERTY() and COLLATIONPROPERTYFROMID().COLLATIONPROPERTY() provides information about (wait for it) collations, SQL Server's method for handling foreign character sets, sort orders, and case- or accent-sensitivity when sorting character data. The Books Online entry for COLLATIONPROPERTY() lists 4 options for code page, locale ID, comparison style and version. Used in conjunction with fn_helpcollations():SELECT *, COLLATIONPROPERTY(name,'LCID') LCID, COLLATIONPROPERTY(name,'CodePage') CodePage,
COLLATIONPROPERTY(name,'ComparisonStyle') ComparisonStyle, COLLATIONPROPERTY(name,'Version') Version
FROM fn_helpcollations()You can get some excellent information. (c'mon, be honest, did you even know about fn_helpcollations?)Collations in SQL Server have a unique name and ID, and you'll see one or both in various system tables or views like syscolumns, sys.columns, and INFORMATION_SCHEMA.COLUMNS. Unfortunately they only link the ID and name for collations of existing columns, so if you wanted to know the collation ID of Albanian_CI_AI_WS, you'd have to declare a column with that collation and query the system table.While poking around the OBJECT_DEFINITION() of sys.columns I found a reference to COLLATIONPROPERTYFROMID(), and the unknown property "Name". Not surprisingly, this is how sys.columns finds the name of the collation, based on the ID stored in the system tables. (Check yourself if you don't believe me)Somewhat surprisingly, the "Name" property also works for COLLATIONPROPERTY(), although you'd already know the name at that point. Some wild guesses and tests revealed that "CollationID" is also a valid property for both functions, so now:SELECT *, COLLATIONPROPERTY(name,'LCID') LCID, COLLATIONPROPERTY(name,'CodePage') CodePage,
COLLATIONPROPERTY(name,'ComparisonStyle') ComparisonStyle, COLLATIONPROPERTY(name,'Version') Version,
COLLATIONPROPERTY(name,'CollationID') CollationID
FROM fn_helpcollations()
Will get you the collation ID-name link you…probably didn't know or care about, but if you ever get on Jeopardy! and this question comes up, feel free to send some of your winnings my way. :)And last but not least, COLLATIONPROPERTYFROMID() uses the same properties as COLLATIONPROPERTY(), so you can use either one depending on which value you have available.Keep an eye out for Part 2!
