August 12, 2024 at 1:00 pm
Any recommendations on changing the collation if DB ?
There is dependent objects so simple alter won't work. Also DB to big to generate script with schema and dats
August 12, 2024 at 6:49 pm
Well I wouldn't want to make any recommendations in this regard. Changing collation on an entire db could cause major issues. If possible maybe set up a new instance with the new collation and then migrate the objects from the old to the new. Testing every step of the way
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 13, 2024 at 9:00 am
Unfortunately we don't have the option to setup a new instance
August 13, 2024 at 3:36 pm
Channelling my inner Brent...............
What is the problem you are trying to solve by changing the whole collation?
August 13, 2024 at 3:39 pm
If you alter the collation at the database level it can have a negative impact on many, many things including query results, query performance, backups, indexes, data integrity, and can cause data corruption. Once the baseline is altered getting back to "normalcy" might require extensive effort if there are complex dependencies. Not to say that's what will happen because there's no way to know. What is the underlying impetus for making this change? SQL Server has many ways to isolate the collation to a particular column or comparison and even within functions. Maybe another way to address the underlying issue could be found.
"All the king's horses and all the king's men couldn't put Humpty Dumpty back together again"
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 19, 2024 at 7:49 pm
Limitations and restrictions
Windows Unicode-only collations can only be used with the COLLATE clause to apply collations to the nchar, nvarchar, and ntext data types on column level and expression-level data. They can't be used with the COLLATE clause to change the collation of a database or server instance.
If the specified collation or the collation used by the referenced object uses a code page that isn't supported by Windows, the Database Engine displays an error.
Server-level collation in Azure SQL Managed Instance can be specified when the instance is created and cannot be changed later. Learn more in Set or change the server collation.
Important
The ALTER DATABASE COLLATE statement is not supported on Azure SQL Database. Specify database collation and catalog collation at the time of CREATE DATABASE.
Recommendations
You can find the supported collation names in Windows Collation Name (Transact-SQL) and SQL Server Collation Name (Transact-SQL); or you can use the sys.fn_helpcollations (Transact-SQL) system function.
When you change the database collation, you change:
Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables are changed to the new collation.
All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions are changed to the new collation.
The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, are changed to the new default collation.
You can change the collation of any new objects that are created in a user database by using the COLLATE clause of the ALTER DATABASE statement. This statement does not change the collation of the columns in any existing user-defined tables. These can be changed by using the COLLATE clause of ALTER TABLE.
Important
Changing the collation of a database or individual columns does not modify the underlying data already stored in existing tables. Unless your application explicitly handles data conversion and comparison between different collations, it is recommended that you transition existing data in the database to the new collation. This removes the risk that applications may incorrectly modify data, resulting in possible wrong results or silent data loss.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply