Post reply

Unique Indexing (case sensitive) ???

  • January 15, 2017 at 9:01 pm

    #317603

    I have a table with a column Password with NVarchar data type and want to store a unique values (case sensitive). A unique value for me is eg:  'Monica' and ‘monica'. Normal unique clustered index does not allow me to store those two values. How do I solve this problem? Do I need to convert string to a Unicode characters and store this value in a table or is there an easier way?

  • January 15, 2017 at 10:36 pm

    #1922872

    Crown Clit - Sunday, January 15, 2017 9:01 PM

    I have a table with a column Password with NVarchar data type and want to store a unique values (case sensitive). A unique value for me is eg:  'Monica' and ‘monica'. Normal unique clustered index does not allow me to store those two values. How do I solve this problem? Do I need to convert string to a Unicode characters and store this value in a table or is there an easier way?

    From what you describe, it sounds like you're using a case-insensitive collation.  I've not tried this exact situation, but I think you'll be able to do what you need if you change the collation of the column to a case-sensitive one.  I prefer the binary collation when doing case-sensitive string comparison on a case-insensitive instance.

    Information on how to change column collation is on MSDN at https://msdn.microsoft.com/en-us/library/ms190920.aspx.  I hope this helps.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • January 16, 2017 at 2:00 am

    #1922897

    Ed Wagner - Sunday, January 15, 2017 10:36 PM

    I've not tried this exact situation, but I think you'll be able to do what you need if you change the collation of the column to a case-sensitive one.  I prefer the binary collation when doing case-sensitive string comparison on a case-insensitive instance.

    Yup, that'll work.

    OP: You either want a collation that is CS (Case Sensitive), eg Latin1_General_CS_AS, or you want a binary collation.
    The way the values in the column sort may be different between the two, so test before you pick one or the other.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 16, 2017 at 3:02 pm

    #1923062

    Thank you, changing collation worked

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply