Problem statement:
One of the development team is using content management package which uses SQL Server for storing its metadata. After couple of months the team came to know that the database collation is of case sensitive, so in the customized tables they are having duplicate values, which they don't want.
Requirement:
1. The requirement is to change the collation to default (case-insensitive).
2. Remove all the duplicate values in the custom tables.
Here is a small test DB of how it has done:
-- Create database with case sensitive collation
USE [master]
GO
CREATE DATABASE [collation_test] ON PRIMARY
( NAME = N'collation_test', FILENAME = N'C:\Program Files\Microsoft SQL Server
\MSSQL10.MSSQLSERVER\MSSQL\DATA\collation_test.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB
)
LOG ON
( NAME = N'collation_test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server
\MSSQL10.MSSQLSERVER\MSSQL\Log\collation_test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CS_AS
GO
-- Create table
CREATE TABLE [dbo].[test1](
[number] [nchar](30) NOT NULL,
[name] [nchar](300) NOT NULL
) ON [PRIMARY]
-- Insert into table
INSERT INTO dbo.test1 VALUES
('1', 'Aa'),
('1', 'aA'),
('2', 'AA'),
('1', 'aa'),
('2', 'aA'),
('2', 'Ba');
-- Check the case sensitive
select * from test1 where name ='Aa'
-- Change the collation to case insensitive
USE [master]
GO
alter DATABASE [collation_test]
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
-- Apply collation changes for columns
Note: The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.
ALTER TABLE dbo.test1 ALTER COLUMN number
nchar(30)COLLATE Latin1_General_CI_AS NOT NULL;
ALTER TABLE dbo.test1 ALTER COLUMN name
nchar(300)COLLATE Latin1_General_CI_AS NOT NULL;
-- Delete the duplicate values
delete delta FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY number, name ORDER BY name) cnt
FROM test1) delta
WHERE delta.cnt > 1
Solution:
The above iteration has applied for all the tables to change its collation and remove duplicates. It worked perfectly.
One of the development team is using content management package which uses SQL Server for storing its metadata. After couple of months the team came to know that the database collation is of case sensitive, so in the customized tables they are having duplicate values, which they don't want.
Requirement:
1. The requirement is to change the collation to default (case-insensitive).
2. Remove all the duplicate values in the custom tables.
Here is a small test DB of how it has done:
-- Create database with case sensitive collation
USE [master]
GO
CREATE DATABASE [collation_test] ON PRIMARY
( NAME = N'collation_test', FILENAME = N'C:\Program Files\Microsoft SQL Server
\MSSQL10.MSSQLSERVER\MSSQL\DATA\collation_test.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB
)
LOG ON
( NAME = N'collation_test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server
\MSSQL10.MSSQLSERVER\MSSQL\Log\collation_test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CS_AS
GO
-- Create table
CREATE TABLE [dbo].[test1](
[number] [nchar](30) NOT NULL,
[name] [nchar](300) NOT NULL
) ON [PRIMARY]
-- Insert into table
INSERT INTO dbo.test1 VALUES
('1', 'Aa'),
('1', 'aA'),
('2', 'AA'),
('1', 'aa'),
('2', 'aA'),
('2', 'Ba');
-- Check the case sensitive
select * from test1 where name ='Aa'
-- Change the collation to case insensitive
USE [master]
GO
alter DATABASE [collation_test]
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
-- Apply collation changes for columns
Note: The COLLATE clause can be applied only for the char, varchar, text, nchar, nvarchar, and ntext data types.
ALTER TABLE dbo.test1 ALTER COLUMN number
nchar(30)COLLATE Latin1_General_CI_AS NOT NULL;
ALTER TABLE dbo.test1 ALTER COLUMN name
nchar(300)COLLATE Latin1_General_CI_AS NOT NULL;
-- Delete the duplicate values
delete delta FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY number, name ORDER BY name) cnt
FROM test1) delta
WHERE delta.cnt > 1
Solution:
The above iteration has applied for all the tables to change its collation and remove duplicates. It worked perfectly.
