How to find the collation an index was built with

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

  • 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
  • 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')

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

  • 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

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

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

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

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

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