Case sensitivity in Sql Server depends on the collation applicable to the column, which of course is often the same as the database default collation. Unlike Oracle, Sql Server out of the box is case-insensitive (at least in English-speaking Western countries).
This means that you don’t need to consider whether data is upper- or lower-case when you’re searching for it, which tends to make an application more robust. You probably won’t want to change that. On the other hand, if your database stores codes or descriptions used as query filters (not including proper nouns like personal names where the capitalisation is part of the data) there may be an advantage to standardising on upper or lower case. You’ve reduced the need to take account of upper/lower case in client code, where string comparisons may be case-sensitive.
Is there an easy way? Yes. In a greenfield application I would use a column constraint, in fact I just have, which is the reason for this blog. Now any client code has to obey the rules or the INSERT just won’t happen.
If you’re working with an existing system, it’s less likely to be worth the bother unless the development team is small or the application is simple. In a case where updates are made with stored procedures (permissions are set up to forbid DML directly against tables), you’ll be able to alter the procedures and wrap the incoming value with an UPPER or LOWER function. This is relatively rare though; usually changes hit the tables directly, through an application, an ORMS, or in an emergency, possibly with an ad hoc script. You wouldn’t want to be causing problems in any of these cases.
As a general rule, in new or old applications, it’s career-threatening to poke around in the basement making “nice to have” changes unless everyone agrees it’s worth doing.
So – at your own risk (and do help your colleagues by naming your constraint in a way that gives them a clue as to why their sql is failing) here’s a simple way to constrain a column to upper or lower case:
PRINT 'EXAMPLE RAN ON Microsoft SQL Server 2014 - 12.0.4213.0 (X64)'
PRINT @@VERSION
--DROP TABLE #T1
CREATE TABLE #T1
(
C1 nvarchar(10) NOT NULL
--collation chosen for demo only; make your own selection--
CHECK(C1 = UPPER(C1) COLLATE SQL_Latin1_General_CP1_CS_AS)
,C2 nvarchar(10) NOT NULL
CHECK(C2 = LOWER(C2) COLLATE SQL_Latin1_General_CP1_CS_AS)
)
PRINT 'Insert two lower case values (ERROR)'
INSERT INTO
#T1
(
C1
,C2
)
VALUES
(
'lower'
,'lower'
)
PRINT 'Insert two upper case values (ERROR)'
INSERT INTO
#T1
(
C1
,C2
)
VALUES
(
'UPPER'
,'UPPER'
)
PRINT 'Insert values per case constraint (SUCCESSFUL)'
INSERT INTO
#T1
(
C1
,C2
)
VALUES
(
'UPPER'
,'lower'
)
SELECT * FROM #T1
blog.philipsymons.com 