none
How to verify field data collation is correct RRS feed

  • Question

  • Dear all,

    Initially I get a wrong collation which has been set at different table column field. So die to that "é" character was not properly encoded and show.

    Now if I change the correct collation of table column, what happen to records which was previously set using wrong collation, will they be able to render correctly after changing to correct collation of do we have to update those column records ?

    If I need to update the column records with the correct collation what is the correct way to update the wrong encoded field

    how to verify this ?

    Thanks for help

    regards

    Monday, September 9, 2019 9:58 AM

Answers

All replies

  • >>Now if I change the correct collation of table column, what happen to records which was previously set using w>>rong collation, will they be able to render correctly after changing to correct collation of do we have to update >>>those column records ?

    No, you would  need to update /insert them again


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by wakefun Monday, September 9, 2019 11:33 AM
    Monday, September 9, 2019 10:12 AM
    Answerer
  • Hi Wakefun,

    Tried analysis on UPPER and lower case sensitivity. Script is below:

    --SCENARIO 1 --> USING WRONG COLLATIONS
    create table Sample_Data
    (
    names varchar(50) collate SQL_Latin1_General_CP1_CS_AS --(CS=Case Sensitive)
    );
    
    insert into Sample_Data
    values ('WAKE');
    
    IF EXISTS(SELECT 1 FROM Sample_Data WHERE names='wake') -- opposite case (upper case = lower case)
    	BEGIN
    		select 'SCENARIO 1' AS SCENARIO_1,'collation is working correctly' AS RESULT;
    	END
    ELSE
    	BEGIN
    		select 'SCENARIO 1' AS SCENARIO_1, 'collation is not working'  AS RESULT;
    	END;
    
    --SCENARIO 2 --> USING CORRECT COLLATIONS WITHOUT EXISTING DATA UPDATES/CORRECTIONS
    
    ALTER table Sample_Data
    ALTER COLUMN names varchar(50) collate SQL_Latin1_General_CP1_CI_AS --(CI=Case Insensitive);
    
    IF EXISTS(SELECT 1 FROM Sample_Data WHERE names='wake') -- opposite case (upper case & lower case)
    	BEGIN
    		select 'SCENARIO 2' AS SCENARIO_2,'collation is working correctly without old data corrections'  AS RESULT;
    	END
    ELSE
    	BEGIN
    		select 'SCENARIO 2' AS SCENARIO_2, 'collation is not working without old data corrections'  AS RESULT;
    	END;
    
    --SCENARIO 3 --> USING CORRECT COLLATIONS WITH EXISTING DATA UPDATES/CORRECTIONS
    
    UPDATE Sample_Data SET names=names COLLATE SQL_Latin1_General_CP1_CI_AS;
    
    IF EXISTS(SELECT 1 FROM Sample_Data WHERE names='wake') -- opposite case (upper case & lower case)
    	BEGIN
    		select 'SCENARIO 3' AS SCENARIO_3,'collation is working correctly with old data corrections'  AS RESULT;
    	END
    ELSE
    	BEGIN
    		select 'SCENARIO 3' AS SCENARIO_3, 'collation is not working with old data corrections'  AS RESULT;
    	END;
    
    drop table Sample_Data;
    
    /*
    SCENARIO_1 RESULT
    ---------- ------------------------
    SCENARIO 1 collation is not working
    
    
    SCENARIO_2 RESULT
    ---------- -----------------------------------------------------------
    SCENARIO 2 collation is working correctly without old data corrections
    
    
    SCENARIO_3 RESULT
    ---------- --------------------------------------------------------
    SCENARIO 3 collation is working correctly with old data corrections
    */
    
    

    For CS/CI (Case Sensitivity and Case InSensitivity) , collation is working correctly for with/without old data corrections.

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Monday, September 9, 2019 7:53 PM
  • Anyway the proper/best option is correcting/updating the old entries respective to new collation types.

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Monday, September 9, 2019 7:56 PM