February 27, 2008 at 10:35 am
So I have been unfortunate enough to inherit a whole estate of sql servers running a collation of Latin1_General_BIN.
I have a batch of maintenance and monitoring scripts that I want to implement on these servers. However I seem to have problems compiling the stored procs as the case of the column names varies throughout the script and table definitions.
Ok I know I could recode them, but is there way to overide the case sensitivity in insert and update statements so they dont have to be recoded?
February 27, 2008 at 10:54 am
mark blakey (2/27/2008)
Ok I know I could recode them, but is there way to overide the case sensitivity in insert and update statements so they dont have to be recoded?
Unfortunately no. Even I had same issues while coding in last project where seibel database is set to Latin1_General_BIN collation. Its the worst of all collation I think. I dealt with this nightmare for 6 months.
SQL DBA.
February 27, 2008 at 11:01 am
Yes. One way is to use "cast"
for example:
select *
from table1
where cast(InvoiceID as VARCHAR) COLLATE Latin1_General_CI_AI = (select InvID from table2)
That statement makes it possible to set InvoiceID equal to InvID when the columns in the different tables have different collations.
Depending on what you need to do there are various ways to use the cast statement to make comparisons possible between different collations and datatypes.
February 27, 2008 at 11:23 am
I actually like case sensitive collations. Forces accuracy in your scripts. Column names in system views in SQL Server 2005 are all lower case, so that's how I write queries that access them. If I have database tables or views that are like this: MyTable or MyView; that's how I wirte them in my queries. I don't type MYTABLE or mytable, just because a case insensitive collation allows me to get away with it.
😎
February 29, 2008 at 10:47 am
Think hard before you go with a case-senstive collation it affects not only the object names but the data also.
Take for exmple if a column called User_Name in the Users table has a value of 'Joe Smith', the query SELECT * FROM Users WHERE User_Name = 'joe smith' will not bring you back a record. If the query is used as a user search functionality you are in effect forcing the user to know the casing beforehand. While is true that you can always do SELECT * FROM Users WHERE UPPER(User_Name) = UPPER(@SearchString); the string function will force a table scan even if there is an index on the User_Name column.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply