How to find the collation an index was built with

Question

How to find the collation an index was built with

ben.reese

Old Hand

Points: 367
More actions
April 13, 2011 at 9:14 am

#236684

Hi,
I am doing some sanity checking of our database build scripts, I need to make sure that the correct collation has been specified when creating columns and indexes. To do this I created a databse with a default collation of Georgian_Modern_Sort_CI_AS (just so it was obvious), ran the scripts and then queried the System Catalog Views to see what was wrong.
SELECT stb.name AS
, sc.name AS [column], st.name + '(' + CAST(sc.max_length AS NVARCHAR(5)) + ')' AS [type], sc.is_nullable , sc.collation_name
FROM sys.columns sc
INNER JOIN sys.types st On sc.system_type_id = st.system_type_id
INNER JOIN sys.tables stb ON sc.object_id = stb.object_id
WHERE st.name IN ('CHAR', 'NCHAR', 'VARCHAR', 'NVARCHAR')
AND sc.collation_name = 'Georgian_Modern_Sort_CI_AS'
I want to do the same for the indexes but can't find seeanywhere in the Catalog Views the collation is specified on the index.
Can anyone set me on the right path?
Thanks,

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The collation is on the column. The index can't have a different collation to the column that it's an index on.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ItsDaveTime SSC Eights! Points: 807 More actions · Answer 2

Indexes don't have collations assigned to them, they just attached to columns. The column has the collation, whether the same as the database or different.

You can see the collation of columns in a table by either using the GUI or running this query:

SELECT name, collation_name

FROM sys.columns

WHERE OBJECT_ID IN (SELECT OBJECT_ID FROM sys.objects WHERE name = 'TableName')

ben.reese Old Hand Points: 367 More actions · Answer 3

Ah yes, I see.

I had read an example where indexes of different collations were used on a column, but on revisisting this example I can see that the second index was built on a computed column defined as the first column but with an alternative collation.

So I only need to worry that the columns have the correct collation (and an index exists).

Great stuff,

Thanks.

ItsDaveTime SSC Eights! Points: 807 More actions · Answer 4

ben.reese (4/13/2011)
Ah yes, I see.
I had read an example where indexes of different collations were used on a column, but on revisisting this example I can see that the second index was built on a computed column defined as the first column but with an alternative collation.
So I only need to worry that the columns have the correct collation (and an index exists).
Great stuff,
Thanks.

If you are joining columns with different collations, you can always use COLLATE in the select statement.

For example: SELECT a.col1, b.col2 from a inner join b on a.id = b.id COLLATE SQL_Latin1_General_CP1_CI_AS

ben.reese Old Hand Points: 367 More actions · Answer 5

Yes, but when you do this the optimizer has to do a scan over the index rather than a seek.

This got me thinking overnight...

What happens to the index when the collation on the column is changed? Is it now potentially in the wrong order for the columns new collation and will this cause SQL to stop looking when it passes the point in the index where the desired record should have occurred? I can feel some experimentation coming on.

I guess the best practice would be to rebuild the indexes if you have to change the collation.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

ben.reese (4/14/2011)
What happens to the index when the collation on the column is changed? Is it now potentially in the wrong order for the columns new collation and will this cause SQL to stop looking when it passes the point in the index where the desired record should have occurred? I can feel some experimentation coming on.

CREATE TABLE testCollation (

SomeCol VARCHAR(20) COLLATE latin1_general_ci_as

)

CREATE INDEX idx_test ON testCollation (SomeCol)

GO

ALTER TABLE testCollation ALTER COLUMN SomeCol VARCHAR(20) COLLATE latin1_general_cs_as

go

Msg 5074, Level 16, State 1, Line 1

The index 'idx_test' is dependent on column 'SomeCol'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN SomeCol failed because one or more objects access this column.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ben.reese Old Hand Points: 367 More actions · Answer 7

In fact,

I have done my experiment and found that SQL won't allow you to alter the column that has an index referencing it, you must drop the index first. Makes perfect sense.

Thanks.

ben.reese Old Hand Points: 367 More actions · Answer 8

thanks Gail, you beet me to it. I found the same.