When we install SQL Server Instance, we have option to choose the Collation. Here is the definition of Collation from Microsoft
" A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages)."
link : https://technet.microsoft.com/en-us/library/aa174903%28v=sql.80%29.aspx
The default collation for SQL Server instance is "SQL_Latin1_General_CP1_CI_AS" that is ascii characters with Case insensitive (CI) and Accent Sensitive ( AS).
If our collation for SQL Server instance is set to SQL_Latin1_General_CP1_CI_AS. This will be default Collation for the database we will create. If I have created a database with "SQL_Latin1_General_CP1_CI_AS" collation, AS part of Collation is case insensitive that SQL Server will treat "AAMIR" same as "Aamir".
Now if I want to create the two tables with same name in same schema, I have to change the collation to "SQL_Latin1_General_CP1_CS_AS". The red "CS" mean Case Sensitive. Now SQL Server will treat "AAMIR" and "Aamir" different as we have set the collation to Case Sensitive.
Script to Change Collation of SQL Server Database or you can go to database properties in SSMS and then Options and change from there.
--Change the collation of Database to SQL_Latin1_General_CP1_CS_AS
USE [master]
GO
ALTER DATABASE [Database] COLLATE SQL_Latin1_General_CP1_CS_AS
GO
Script to create tables with same name but different Case ( Upper or Lower) after changing the Database Collation to Case Sensitive.
USE [Database]
--Create two tables with same name by using Case Insensitive Collation
CREATE TABLE dbo.Test ( Id INT, Name VARCHAR(100))
GO
INSERT INTO dbo.Test VALUES(1,'Aamir')
GO
CREATE TABLE dbo.test (Id INT,Name VARCHAR(100))
GO
INSERT INTO dbo.test VALUES(2,'Shahzad')
--Run Select Statement on both of them to get out data
SELECT * FROM dbo.Test
SELECT * FROM dbo.test
" A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages)."
link : https://technet.microsoft.com/en-us/library/aa174903%28v=sql.80%29.aspx
The default collation for SQL Server instance is "SQL_Latin1_General_CP1_CI_AS" that is ascii characters with Case insensitive (CI) and Accent Sensitive ( AS).
If our collation for SQL Server instance is set to SQL_Latin1_General_CP1_CI_AS. This will be default Collation for the database we will create. If I have created a database with "SQL_Latin1_General_CP1_CI_AS" collation, AS part of Collation is case insensitive that SQL Server will treat "AAMIR" same as "Aamir".
Now if I want to create the two tables with same name in same schema, I have to change the collation to "SQL_Latin1_General_CP1_CS_AS". The red "CS" mean Case Sensitive. Now SQL Server will treat "AAMIR" and "Aamir" different as we have set the collation to Case Sensitive.
Script to Change Collation of SQL Server Database or you can go to database properties in SSMS and then Options and change from there.
--Change the collation of Database to SQL_Latin1_General_CP1_CS_AS
USE [master]
GO
ALTER DATABASE [Database] COLLATE SQL_Latin1_General_CP1_CS_AS
GO
Script to create tables with same name but different Case ( Upper or Lower) after changing the Database Collation to Case Sensitive.
USE [Database]
--Create two tables with same name by using Case Insensitive Collation
CREATE TABLE dbo.Test ( Id INT, Name VARCHAR(100))
GO
INSERT INTO dbo.Test VALUES(1,'Aamir')
GO
CREATE TABLE dbo.test (Id INT,Name VARCHAR(100))
GO
INSERT INTO dbo.test VALUES(2,'Shahzad')
--Run Select Statement on both of them to get out data
SELECT * FROM dbo.Test
SELECT * FROM dbo.test
Fig 1: SQL Server Database Collation
The collation can be set on Table level or Column level. Before you change collation on Database level make sure you understand the impact.
No comments:
Post a Comment