April 13, 2011 at 9:14 am
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,
April 13, 2011 at 9:40 am
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
April 13, 2011 at 9:43 am
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')
April 13, 2011 at 10:05 am
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.
April 13, 2011 at 11:25 am
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
April 14, 2011 at 3:20 am
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.
April 14, 2011 at 3:23 am
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
April 14, 2011 at 3:32 am
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.
April 14, 2011 at 3:33 am
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