May 10, 2018 at 5:26 am
How can I set a column with a set collation back to null? We currently have a mixture of column collations, and want all the columns to inherit the database collation. I can only find the command to set columns to the database default, but not back to NULL. Is this possible?
May 10, 2018 at 5:39 am
I've never heard of a NULL collation. If you want all columns to have the same collation as the database, why not specify that collation by name in the ALTER TABLE statement? Bear in mind that you have to drop all sorts of things such as constraints, indexes and statistics before you can change the collation of a column.
John
May 10, 2018 at 7:17 am
Hi John, If you create a column without a specific collation, it will be listed as NULL in in sys.columns, meaning the collation of the column will be the same as the database default. We have the same legacy database with several (around 60) different implementations, all with potentially different database level collations. I can change the columns to all be database default:
ALTER TABLE dbo.MyTable
ALTER COLUMN Mycolumn VARCHAR(50) COLLATE DATABASE_DEFAULT
However, if you change the database collation the column collation will stay the same as previous database default, whereas all "null" collations will simply inherit the database collation.
As you mentioned, changing the column collations is not as straight forward, as you need to drop and create all possible indexes/constraints etc, for all these columns. Ideally I would not like to not uses specific column level collations, and change it only on the database level.
I was hoping for something like this.. or similar but cant find it implemented:
ALTER TABLE dbo.MyTable
ALTER COLUMN Mycolumn VARCHAR(50) COLLATE NULL
This will make future deployments on all database levels so much easier. What will be the best way to do this? Thanks
May 10, 2018 at 7:23 am
Are you sure Sharky? I just ran the following on SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64), Microsoft SQL Server 2008 (SP4-GDR) (KB4057114) - 10.0.6556.0 (Intel X86) and Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64):
USE Sandbox;
GO
PRINT @@VERSION;
CREATE TABLE Test (column1 varchar(10),
column2 nvarchar(10),
column3 char(10),
column4 nchar(10));
GO
SELECT c.name, c.collation_name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'Test';
GO
DROP TABLE test;
All queries returned a collation in the column "Collation_name" (Latin1_General_CI_AS on the 2012 and 2008 instances and SQL_Latin1_General_CP1_CI_AS on 2017). If the above is true, I imagine it's on an older (and unsupported) version of SQL Server.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 10, 2018 at 7:42 am
Hi Thom,
Thanks for this. Yes this was old legacy 2000 databases! Will explain this then.
Thanks for the help.
May 10, 2018 at 7:54 am
sharky - Thursday, May 10, 2018 7:42 AMHi Thom,Thanks for this. Yes this was old legacy 2000 databases! Will explain this then.
Thanks for the help.
You've posted in the 2014 forums; so both myself and John would have assumed that that's the version of SQL Server you're using. I don't have access to a 2000 system, nor have I for many years, so I have no way of testing it against that; my suggestion would be to try and move away from such old software though. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 10, 2018 at 8:01 am
Sorry, My mistake. Most of the databases have been converted to 2014 and we are in the process of moving everything over.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply