Changing SQL Server Database sorting

Question

I have a request to change the collation of a SQL Server Database:

ALTER DATABASE solarwind95 collate SQL_Latin1_General_CP1_CI_AS

but I get this strange error:

Meldung 5075, Ebene 16, Status 1, Zeile 1 Das 'Spalte'-Objekt 'CustomPollerAssignment.PollerID' ist von 'Datenbanksortierung' abhängig. Die Datenbanksortierung kann nicht geändert werden, wenn ein schemagebundenes Objekt von ihr abhängig ist. Entfernen Sie die Anhängigkeiten der Datenbanksortierung, und wiederholen Sie den Vorgang.

Sorry for the german errror message. I do not know how to switch the language to english, but here is a translation:

Translation: Message 5075, Layer 16, Status 1, Row 1 The 'column' object 'CustomPollerAssignment.PollerID' depends on 'Database sorting. The database sorting cannot be changed if a schema bound object depends on it. Remove the dependency of the database sortieren and retry.

I got a ton more of the errors like that.

Answer 1

These will be primary key coulmns that are clustered indexes. With clustered indexes the records are stored in ascending order of the column, if you change the collation of the database then all tables would need to be reordered.

Answer 2

You'll need to remove WITH SCHEMABINDING from your views and table-valued functions. To identify them you can query the INFORMATION_SCHEMA views:

SELECT TABLE_SCHEMA, TABLE_NAME AS VIEW_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE '%SCHEMABINDING%'

SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%SCHEMABINDING%'

1. First backup the database.
2. Generate an ALTER script of all schema-bound views and functions.
3. Delete the words "WITH SCHEMABINDING" from the script.
4. Run the script a few times, until all reference errors are resolved.
5. Change the collation on your database.
6. Script and drop all constraints (keys, checks and defaults).
7. Change the collation of each column using the script below.
8. Recreate constraints.
9. Finally, run the original script a few times to enable schema-binding.

You can change the collation of all columns with this script:

DECLARE @collation nvarchar(128)
DECLARE @commands table ([SQL] nvarchar(max))
DECLARE @cursor cursor
DECLARE @sql nvarchar(max)

SET @collation = 'SQL_Latin1_General_CP1_CI_AS'

INSERT @commands ([SQL])
SELECT 'ALTER TABLE ' + QUOTENAME(c.TABLE_SCHEMA) +'.'+ QUOTENAME(c.TABLE_NAME)
    + ' ALTER COLUMN ' + QUOTENAME(c.COLUMN_NAME)
    + ' ' + c.DATA_TYPE
    + ISNULL('(' + LTRIM(STR(c.CHARACTER_MAXIMUM_LENGTH)) + ')', '')
    + ISNULL(' COLLATE ' + @collation, '')
    + ' ' + CASE c.IS_NULLABLE WHEN 'NO' THEN 'NOT ' ELSE '' END + 'NULL'
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND c.COLLATION_NAME <> @collation

SET @cursor = CURSOR FOR SELECT [SQL] FROM @commands
OPEN @cursor
FETCH NEXT FROM @cursor INTO @sql

WHILE @@FETCH_STATUS = 0
BEGIN 
    PRINT @sql
    EXEC (@sql)

    FETCH NEXT FROM @cursor INTO @sql
END

Answer 3

In this case, even removing schemabinding won't help you

Changing collation requires the steps described in KB 325335

It applies to SQL Server 2000 mainly, but the same principle applies

• Script bare tables
• Script constraints, triggers, keys etc + all code, views etc for later
• Create bare tables in new DB
• DTS or SSIS bare data
• Apply script to create constraints, triggers, keys, code, views etc