What is collation and how it changes in SQL Server
Certifications:
- How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
- How do we find what permissions a user has on an Oracle database - 1 November 2024
- How we enable Unified Auditing in Oracle Database - 7 October 2024
With one sentence collation is the way characters are encoded in a database. That is, you define whether it will be case sensitive (uppercase, lowercase), what language the varchar and text fields are in.
Fields like nvarchar which are in Unicode format are not affected by the encoding of the characters, but it affects the sort since depending on the collation there may be a different order of letters or be affected by the case sensitive.
The collation on system databases and on user databases could be different from each other since SQL Server 2000 and later.
How can we see what collation all databases have with query
select name,databasepropertyex(name,'COLLATION'),databasepropertyex(name,'Recovery'),databasepropertyex(name,'STATUS') from master.dbo.sysdatabases
The collation at master database defines the collation in the rest of the system bases as well. This may affect system functions such as database login and sort on tempdb.
The collation on user databases affects as we mentioned first of all what language the non-unicode characters and there is a basic problem when we decide to change it. That whatever data was passed up to the moment of the change will remain with the collation they had.
Even if we have the wrong collation in one base, we can read the field with a different collation, e.g. on a base that is in SQL_Latin1_General_CP1253_CI_AI with the following query we can read the Greek characters:
select cast (Onoma as varchar(100)) collate Greek_CI_AS from Pelatis
To change the collation in the database
To change the collation to user databases the following steps should be taken so that we do not have the problem we mentioned above and that not all records in the database have the same collation (it is good to have done them backup before we start):
1 – We export the generate script of the base (its structure):
Right click on the database, Tasks, Generate scripts… , Script entire database and all database objects
2 – With the following script that I have made, generate the statements so that we can do bcp out and bcp in the whole base. We execute it and keep both columns.
declare @user varchar(50), @pass varchar(50), @pathout varchar(200), @pathin varchar(200) set @user = 'username' set @pass = 'password' set @pathout = 'c:\backups\' set @pathin = 'c:\backups\' SELECT 'bcp ' --bcp + QUOTENAME(DB_NAME())+ '.' + QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.' -- + QUOTENAME(name) + ' out ' +@pathout + REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' + REPLACE(name,' ','') + '.txt -S' + REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','') --server name +' -U'+@user+' -P'+@pass+' ' --username pass +'-n -t[cdel] -r[rdel]' , AS BCP_OUT, 'bcp ' --bcp + QUOTENAME(DB_NAME())+ '.' + QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.' + QUOTENAME(name) + ' in '+@pathin + REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' + REPLACE(name,' ','') + '.txt -S' + REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','') --server name +' -U'+@user+' -P'+@pass+' ' --username pass +'-w -n' +' -e'+@pathin +'logbcpin.txt' +' -t[cdel] -r[rdel]' , AS BCP_IN from sys.tables
3 – We execute it bcp out in command prompt from step 2.
4 – Turn off the base.
drop database [AdventureWorks2017 ]
5 – We run the script that creates the database from step 1.
6 – We change the collation in the base.
ALTER DATABASE AdventureWorks2017 GREEK_CI_AI ;GO
7 – We execute it bcp in in command prompt from step 2. (after first disabling the constraints)
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
8 – When the bcp in is completed, enable the constraints.
EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"
To change the collation on the system bases
To change the collation in the system bases it is necessary rebuild the master and this results in:
- May all be lost jobs from msdb
- May all be lost logins kai oi clock
- Let them be lost dblinks
- To lose all the rights that have been passed to the roles such as e.g. to the public
- To be done detach All the user databases
- Let them all perish the parameters of the instance (which we had in sp_configure) such as max_memory, max_dop etc.
The procedure for changing the collation in systemic ones is as follows:
1 – Backup of the master, msdb, model databases (with the corresponding modification of the paths in the following script that I have made)
DECLARE @date CHAR(8)
SET @date = (SELECT CONVERT(char(8), GETDATE(), 112))
DECLARE @backup_loc VARCHAR(125)
SET @backup_loc = 'X:\backups\' -- Σε ποιά τοποθεσία θα παρθούν τα backups
DECLARE @rest_dbf VARCHAR(125)
SET @rest_dbf = 'D:\Databases\' -- Σε ποιά τοποθεσία θα γίνουν restore τα datafiles
DECLARE @rest_log VARCHAR(125)
SET @rest_log = 'L:\Logfiles\'; -- Σε ποια τοποθεσία θα γίνουν restore τα logfiles
WITH CTE ( DatabaseName, Npath )
AS ( SELECT DISTINCT
DB_NAME(database_id) ,
STUFF((SELECT ' ' + CHAR(13)+', MOVE ''' + name + ''''
+ CASE Type
WHEN 0 THEN ' TO ' +@rest_dbf
ELSE ' TO ' +@rest_log
END
+ REVERSE(LEFT(REVERSE(physical_name),
CHARINDEX('\',
REVERSE(physical_name),
1) - 1)) + ''''
FROM sys.master_files sm1
WHERE sm1.database_id = sm2.database_ID
FOR XML PATH('') ,
TYPE).value('.', 'varchar(max)'), 1, 1, '') AS Npath
FROM sys.master_files sm2
)
--select * from CTE
SELECT
'BACKUP DATABASE ' + name + ' TO DISK = ''' + @backup_loc + '' + name + '_' + @date + '.bak'' WITH COMPRESSION, COPY_ONLY, STATS=5' as Backup_Commands_onSource,
'RESTORE DATABASE '+ name + ' FROM DISK = ''' + @backup_loc + '' + name + '_' + @date + '.bak'' WITH RECOVERY, REPLACE, STATS=5 ' + CTE.Npath as Restore_Commands_onTarget
FROM sys.databases d
INNER JOIN CTE ON d.name = cte.databasename
WHERE d.name in('master','model','msdb') --DBs
GO
2 – Backup of jobs (press F7 in SSMS to open the object explorer details, select all jobs and right click, script job as, create to, file.. as in the image below):
3 – Backup logins, users, roles with Greg Ryan's query:
--Script All Logins / Users / and Roles
/****************************************************************
This Script Generates A script to Create all Logins, Server Roles
, DB Users and DB roles on a SQL Server
Greg Ryan
10/31/2013
****************************************************************/
SET NOCOUNT ON
DECLARE
@sql nvarchar(max)
, @Line int = 1
, @max int = 0
, @@CurDB nvarchar(100) = ''
CREATE TABLE #SQL
(
Idx int IDENTITY
,xSQL nvarchar(max)
)
INSERT INTO #SQL
( xSQL
)
SELECT
'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
+ QUOTENAME(name) + ''')
' + ' CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD='
+ sys.fn_varbintohexstr(password_hash) + ' HASHED, SID='
+ sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE='
+ QUOTENAME(COALESCE(default_database_name , 'master'))
+ ', DEFAULT_LANGUAGE='
+ QUOTENAME(COALESCE(default_language_name , 'us_english'))
+ ', CHECK_EXPIRATION=' + CASE is_expiration_checked
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + ', CHECK_POLICY='
+ CASE is_policy_checked
WHEN 1 THEN 'ON'
ELSE 'OFF'
END + '
Go
'
FROM
sys.sql_logins
WHERE
name <> 'sa'
INSERT INTO #SQL
( xSQL
)
SELECT
'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''
+ QUOTENAME(name) + ''')
' + ' CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH '
+ 'DEFAULT_DATABASE='
+ QUOTENAME(COALESCE(default_database_name , 'master'))
+ ', DEFAULT_LANGUAGE='
+ QUOTENAME(COALESCE(default_language_name , 'us_english'))
+ ';
Go
'
FROM
sys.server_principals
WHERE
type IN ( 'U' , 'G' )
AND name NOT IN ( 'BUILTIN\Administrators' ,
'NT AUTHORITY\SYSTEM' );
PRINT '/*****************************************************************************************/'
PRINT '/*************************************** Create Logins ***********************************/'
PRINT '/*****************************************************************************************/'
SELECT
@Max = MAX(idx)
FROM
#SQL
WHILE @Line <= @max
BEGIN
SELECT
@sql = xSql
FROM
#SQL AS s
WHERE
idx = @Line
PRINT @sql
SET @line = @line + 1
END
DROP TABLE #SQL
CREATE TABLE #SQL2
(
Idx int IDENTITY
,xSQL nvarchar(max)
)
INSERT INTO #SQL2
( xSQL
)
SELECT
'EXEC sp_addsrvrolemember ' + QUOTENAME(L.name) + ', '
+ QUOTENAME(R.name) + ';
GO
'
FROM
sys.server_principals L
JOIN sys.server_role_members RM
ON L.principal_id = RM.member_principal_id
JOIN sys.server_principals R
ON RM.role_principal_id = R.principal_id
WHERE
L.type IN ( 'U' , 'G' , 'S' )
AND L.name NOT IN ( 'BUILTIN\Administrators' ,
'NT AUTHORITY\SYSTEM' , 'sa' );
PRINT '/*****************************************************************************************/'
PRINT '/******************************Add Server Role Members *******************************/'
PRINT '/*****************************************************************************************/'
SELECT
@Max = MAX(idx)
FROM
#SQL2
SET @line = 1
WHILE @Line <= @max
BEGIN
SELECT
@sql = xSql
FROM
#SQL2 AS s
WHERE
idx = @Line
PRINT @sql
SET @line = @line + 1
END
DROP TABLE #SQL2
PRINT '/*****************************************************************************************/'
PRINT '/*****************Add User and Roles membership to Indivdual Databases********************/'
PRINT '/*****************************************************************************************/'
--Drop Table #Db
CREATE TABLE #Db
(
idx int IDENTITY
,DBName nvarchar(100)
);
INSERT INTO #Db
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name NOT IN ( 'Master' , 'Model' , 'msdb' , 'tempdb' )
ORDER BY
name;
SELECT
@Max = MAX(idx)
FROM
#Db
SET @line = 1
--Select * from #Db
--Exec sp_executesql @SQL
WHILE @line <= @Max
BEGIN
SELECT
@@CurDB = DBName
FROM
#Db
WHERE
idx = @line
SET @SQL = 'Use ' + @@CurDB + '
Declare @@Script NVarChar(4000) = ''''
DECLARE cur CURSOR FOR
Select ''Use ' + @@CurDB + ';
Go
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'''''' +
mp.[name] + '''''')
CREATE USER ['' + mp.[name] + ''] FOR LOGIN ['' +mp.[name] + ''] WITH DEFAULT_SCHEMA=[dbo]; ''+ CHAR(13)+CHAR(10) +
''GO'' + CHAR(13)+CHAR(10) +
''EXEC sp_addrolemember N'''''' + rp.name + '''''', N''''['' + mp.[name] + '']'''';
Go''
FROM sys.database_role_members a
INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id
OPEN cur
FETCH NEXT FROM cur INTO @@Script;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @@Script
FETCH NEXT FROM cur INTO @@Script;
END
CLOSE cur;
DEALLOCATE cur;';
--Print @SQL
Exec sp_executesql @SQL;
--Set @@Script = ''
SET @Line = @Line + 1
END
DROP TABLE #Db
4 – Backup db links, certificates (If they exist). It can be easily done with the SSMS GUI.
5 – We do detach bases with the following script we can automate it:
USE [master];
GO
DECLARE @database NVARCHAR(200) ,
@cmd NVARCHAR(1000) ,
@detach_cmd NVARCHAR(4000) ,
@attach_cmd NVARCHAR(4000) ,
@file NVARCHAR(1000) ,
@i INT ,
@whichdb INT=0,
@dbname nvarchar(200)='%%',
@Drive_Now nvarchar(10)='',
@Drive_New nvarchar(10)='';
create table #tempattach(attach_script varchar(max));
create table #tempdetach(detach_script varchar(max));
--set 4 for only userdbs, 0 for all dbs
set @whichdb = 4
--if u want to attach db on new drive with same path uncomment with the correct drive letters
--set @Drive_Now = 'C:\' set @Drive_New = 'Z:\'
--uncomment if u pick only one DBname
--set @dbname = '%iDBA%'
DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT RTRIM(LTRIM([name]))
FROM sys.databases
WHERE database_id > @whichdb
and name like @dbname;
OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @i = 1;
SET @attach_cmd =
'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);
-- Change skip checks to false if you want to update statistics before you detach.
SET @detach_cmd =
'EXEC sp_detach_db @dbname = ''' + @database
+ ''' , @skipchecks = ''true'';' + CHAR(10);
-- Get a list of files for the database
DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
SELECT physical_name
FROM sys.master_files
WHERE database_id = DB_ID(@database)
ORDER BY [file_id];
OPEN dbfiles_cur
FETCH NEXT FROM dbfiles_cur INTO @file
WHILE @@FETCH_STATUS = 0
BEGIN
SET @attach_cmd = @attach_cmd + ' ,@filename'
+ CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + ''''
+ CHAR(10);
SET @i = @i + 1;
FETCH NEXT FROM dbfiles_cur INTO @file
END
CLOSE dbfiles_cur;
DEALLOCATE dbfiles_cur;
insert into #tempattach values(REPLACE(@attach_cmd,@Drive_Now,@Drive_New));
insert into #tempdetach values(@detach_cmd);
FETCH NEXT FROM dbname_cur INTO @database
END
CLOSE dbname_cur;
DEALLOCATE dbname_cur;
select * from #tempdetach
select * from #tempattach
drop table #tempattach
drop table #tempdetach
6 – Double click on the SQL Server ISO whose instance it is, e.g. 2016
7 – In the command prompt as an administrator, cd to the drive where the ISO was mounted and run the following command, after first putting the correct instance name, windows authentication, and the collation we want to go to:
F:\>cd F: Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=INSTANCENAME\SA /SAPWD=KWDIKOS /SQLCOLLATION=SQL_Latin1_General_CP1253_CI_AI
8 – The change is complete, so we have to go through the jobs, logins, parameters, etc. again.
9 – Attach the user databases. (from the results of step 5)
There is an error. No commas are needed in column names (,AS)
The queries have been tested. They work normally.
We must be more careful in what we recommend because we may be led into irreversible mistakes. The database structure as a whole is not done with what you suggest (SCRIPT DATABASE AS—>File). This makes the base's characteristics script and not its objects (Tables etc). The result is that when someone goes to pass the data, the corresponding tables do not exist!!! So if someone hadn't backed up the entire database then they will have to rebuild its structure from scratch by hand!!!
By mistake I had mentioned the Script Database option instead of Generate Scripts which exports the entire database schema along with the schema of all tables so the steps mentioned apply normally.