Changing SQL Server Database Sorting

Question

I have a request to change the sorting of a SQL Server database:

ALTER DATABASE solarwind95 map to 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 Object schemes Objekt von ihr abhängig ist. Entfernen Sie die Anhängigkeiten der Datenbanksortierung, und wiederholen Sie den Vorgang.

Sorry for the German error message. I do not know how to switch the language to English, but here is the translation:

Translation: Message 5075, Level 16, Status 1, Line 1 The column object 'CustomPollerAssignment.PollerID' depends on the database sorting. the sorting of the database cannot be changed if the related schema object depends on it. Remove the sortieren database dependency and try again.

I got more of these errors.

Answer 1

You will need to remove WITH SCHEMABINDING from your views and table functions. To define 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%' 

• First back up the database.
• Generate an ALTER script for all schema-related representations and functions.
• Remove the words < WITH SCHEMABINDING "from the script.
• Run the script several times until all support errors are resolved.
• Change the setting in your database.
• Script and remove all restrictions (keys, checks, and default values).
• Change the sort order of each column using the script below.
• Restore restrictions.
• Finally, run the source script several times to enable schema binding.

You can change the sorting of all columns using 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 2

These will be primary keys, which are clustered indexes. With clustered indexes, records are stored in ascending column order, if you change the sorting of the database, then all the tables will need to be reordered.

Answer 3

In this case, even removing schemabinding will not help you.

Changing the sorting requires the steps described in KB 325335

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

• Script bare tables
• Script restrictions, triggers, keys, etc. + all code, views, etc. for later
• Create bare tables in a new database
• Horizontal DTS or SSIS Data
• Apply a script to create constraints, triggers, keys, code, views, etc.