{"id":1132,"date":"2021-08-23T08:00:00","date_gmt":"2021-08-23T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1132"},"modified":"2023-08-27T16:17:19","modified_gmt":"2023-08-27T13:17:19","slug":"ti-einai-to-collation-kai-pos-allazei-ston-sql-server","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/ti-einai-to-collation-kai-pos-allazei-ston-sql-server\/","title":{"rendered":"What is collation and how it changes in SQL Server"},"content":{"rendered":"

With one sentence collation <\/strong>is the way characters are encoded in a database. That is, you define whether it will be case sensitive<\/strong>  (uppercase, lowercase), what language the varchar and text fields are in. <\/p>\n\n\n\n

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.<\/p>\n\n\n\n

The collation on system databases<\/strong> and on  user databases<\/strong> could be different from each other since SQL Server 2000 and later.<\/p>\n\n\n\n

How can we see what collation all databases have with query<\/h4>\n\n\n\n
select name,databasepropertyex(name,'COLLATION'),databasepropertyex(name,'Recovery'),databasepropertyex(name,'STATUS') from master.dbo.sysdatabases<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The collation at master database <\/strong>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<\/strong>.<\/p>\n\n\n\n
The collation on  user databases<\/strong> affects as we mentioned first of all what language the non-unicode<\/strong> 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.<\/p>\n\n\n\n
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<\/strong>  with the following query we can read the Greek characters:<\/p>\n\n\n\n
select cast (Onoma as varchar(100)) collate Greek_CI_AS\nfrom Pelatis<\/pre>\n\n\n\n
To change the collation in the database<\/h2>\n\n\n\n
To change the collation to user databases<\/strong> 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 <\/strong>before we start):<\/p>\n\n\n\n
1 \u2013 We export the generate script of the base (its structure):<\/p>\n\n\n\n
Right click on the database, Tasks, Generate scripts\u2026 , Script entire database and all database objects<\/em><\/p>\n\n\n\n
2 \u2013 With the following script that I have made, generate the statements so that we can do bcp out<\/strong> and bcp in<\/strong> the whole base. We execute it and keep both columns.<\/p>\n\n\n\n
declare \n@user varchar(50),\n@pass varchar(50),\n@pathout varchar(200),\n@pathin varchar(200)\n\nset @user = 'username'\nset @pass = 'password'\nset @pathout = 'c:\\backups\\'\nset @pathin = 'c:\\backups\\'\n\nSELECT \n'bcp '           --bcp\n+  QUOTENAME(DB_NAME())+ '.'               \n+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.'  -- \n+  QUOTENAME(name)                         \n+ ' out ' +@pathout                        \n+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' \n+  REPLACE(name,' ','')                    \n+ '.txt -S'\n+ REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','')  --server name\n+' -U'+@user+' -P'+@pass+' '  --username  pass\n+'-n -t[cdel] -r[rdel]'  ,  \nAS  BCP_OUT,\n\n'bcp '           --bcp\n+  QUOTENAME(DB_NAME())+ '.'               \n+  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+ '.' \n+  QUOTENAME(name)                         \n+ ' in '+@pathin                          \n+  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' \n+  REPLACE(name,' ','')                   \n+ '.txt -S'\n+ REPLACE(REPLACE(QUOTENAME(@@servername),'[',''),']','')  --server name\n+' -U'+@user+' -P'+@pass+' '  --username  pass\n+'-w -n'\n+' -e'+@pathin +'logbcpin.txt'\n+' -t[cdel] -r[rdel]'   ,  \nAS  BCP_IN\nfrom sys.tables<\/pre>\n\n\n\n
3 \u2013 We execute it bcp out<\/strong> in command prompt from step 2.<\/p>\n\n\n\n
4 \u2013 Turn off the base.<\/p>\n\n\n\n
drop database [AdventureWorks2017 ]<\/pre>\n\n\n\n
5 \u2013 We run the script that creates the database from step 1.<\/p>\n\n\n\n
6 \u2013 We change the collation in the base.<\/p>\n\n\n\n
ALTER DATABASE AdventureWorks2017 GREEK_CI_AI ;GO<\/pre>\n\n\n\n
7 \u2013 We execute it bcp in<\/strong> in command prompt from step 2. (after first disabling the constraints)<\/p>\n\n\n\n
EXEC sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"<\/pre>\n\n\n\n
8 \u2013 When the bcp in is completed, enable the constraints.<\/p>\n\n\n\n
EXEC sp_msforeachtable \"ALTER TABLE ? CHECK CONSTRAINT all\"<\/pre>\n\n\n\n
To change the collation on the system bases<\/h2>\n\n\n\n
To change the collation in the system bases it is necessary rebuild<\/strong> the master <\/strong>and this results in:<\/p>\n\n\n\n