SQL Server Case Sensitivity and Accent Sensitivity Demo….

01/18/2010 – 12:18PM

I am in the middle of a SQL Server 2008 Administration course, and a question came up about width-sensitivity in collations.  I am not sure if half-width vs. full-width makes sense in non-Asian character sets.  The only references I seem to find about it is on Korean collations.

However, in my poking around I was trying to test the differences collations make on comparisons, and I generated the following script in my testing.  I am posting it here so I can find it later, but also thought others might find it enlightening….

<span style="color: #0000ff">PRINT</span> <span style="color: #006080">'Testing Case Sensitivity......'</span><br /><span style="color: #0000ff">IF</span> (<span style="color: #0000ff">SELECT</span> N<span style="color: #006080">'A'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CS_AS) <br /> = (<span style="color: #0000ff">SELECT</span> <span style="color: #006080">'a'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CS_AS)<br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are equal'</span><br /><span style="color: #0000ff">ELSE</span><br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are NOT equal'</span><br /><br /><span style="color: #0000ff">PRINT</span> <span style="color: #006080">'Testing Case Insensitivity......'</span><br /><span style="color: #0000ff">IF</span> (<span style="color: #0000ff">SELECT</span> N<span style="color: #006080">'A'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AS) <br /> = (<span style="color: #0000ff">SELECT</span> <span style="color: #006080">'a'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AS)<br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are equal'</span><br /><span style="color: #0000ff">ELSE</span><br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are NOT equal'</span><br /><br /><span style="color: #0000ff">PRINT</span> <span style="color: #006080">'Testing Accent Sensitivity......'</span><br /><span style="color: #0000ff">IF</span> (<span style="color: #0000ff">SELECT</span> N<span style="color: #006080">'A'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AS) <br /> = (<span style="color: #0000ff">SELECT</span> <span style="color: #006080">'á'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AS)<br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are equal'</span><br /><span style="color: #0000ff">ELSE</span><br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are NOT equal'</span><br /><br /><span style="color: #0000ff">PRINT</span> <span style="color: #006080">'Testing Accent Insensitivity......'</span><br /><span style="color: #0000ff">IF</span> (<span style="color: #0000ff">SELECT</span> N<span style="color: #006080">'A'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AI) <br /> = (<span style="color: #0000ff">SELECT</span> <span style="color: #006080">'Á'</span> <span style="color: #0000ff">COLLATE</span> Latin1_General_CI_AI)<br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are equal'</span><br /><span style="color: #0000ff">ELSE</span><br />  <span style="color: #0000ff">PRINT</span> <span style="color: #006080">'They are NOT equal'</span>

If you have any insight to offer on width-sensitivity (especially as it relates to non-Asian collations) please let me know!

Enjoy!