Featured Script
dba3_0To20ComputedTimeOnlyValueAndFullDateTimeOffset_demo
— For a GivenFullDateTimeArgument display: — GivenFullDateTimeArgument, Random 0 – 20 MinuteOffSetValue, — ComputedTime… (read more)
Case Sensitive Database
There are times when you wish to have your database data perform in a Case Sensitive fashion. When a database performs Case Sensitive comparisions, "A" and "a" are not equal. This configuration is usually done by selecting a different code page for the Database, table or specific column.
When you select a case sensitive code page for the database, the entire database becomes case sensitive. This includes object names as well as data in tables. You could have two separate stored procedures that are identical with the exception of the case: usp_demo != USP_DEMO. The case comparison is on each character, so there are a lot of variations on my simple example.
Having configured your data to be case sensitive you must convert your data case in order to make a comparison that is not case specific. For example, if you want to compare the variable @FruitParm=’Apples’ to every value in a fruit table with a case sensitive column named fruit you would have convert the case on both the variable and the table to allow a Case Agnostic comparision SELECT fruit FROM dbo.fruit WHERE UCASE(fruit) = UCASE(@FruitParm).
Do you see the performance problem here? For every comparison you must convert the value stored in the database to assure a case agnostic comparision. As a result, the query will not take advantage of any index because of the data conversion.
Another option would be to have two columns in the database. One column could be a virtual column converting the value to all upper case. Then the second column could be indexed allowing a quazi case agnostic comparison. This would actually perform faster than having another column with a case agnostic code page because SQL Server will only have to compare A for A rather than comparing to both A and a. So you get both worlds by losing a little space.
Another way you can implement case sensitive storage without changing the code page of a specific column and having the table and/or database be case agnostic is to use a BINARY data type instead of a CHAR. Data stored in VARBINARY is always case sensitive. It is more intensive to generate case agnostic data from BINARY data, but if the case sensitive nature of the data is preferred, this is a good choice.
Do you have systems where you are required to be case specific, but don’t wish to deal with specific case 100% of the time? How about sharing techniques you use to resolve the problem. Send your ideas and comments to btaylor@sswug.org.
Cheers,
Ben