Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2010-03-17 : 05:48:18
|
I use the same collation for all columns in the database. I'm now wondering if I should have different collations for some columns?Specifically we have some "code" columns. These are alpha-numeric, A-Z, 0-9 and underscore ("_") only. They are forced CAPs. The application takes care of this, so I am confident that there are "no exceptions" in the data.I've never bothered to enforce this in the database, should I? (Time penalty for Constraint to be checked on INSERTS etc?)But my real question was should I put a Binary Collation on these columns?WouldSELECT Col1, COl2, ...FROM MyTableWHERE MyCode = @AllCapsCodeValue be faster with binary collation?What about a range?SELECT Col1, COl2, ...FROM MyTableWHERE MyCode >= @AllCapsCodeValueStart MyCode <= @AllCapsCodeValueEnd I was reading Erland Sommarskog's article on a Splitter Function where he is using Binary Collation on CHARINDEX when looking for the String Delimiter (a comma) because it is not case sensitive and he got a 10% improvement in performance. However, he was using Nvarchar, and all my Code columns are Varchar so I would appreciate your thoughts on whether Binary Collation will improve performance in this narrow, specific, use-case.But I'm definitely going to add a Binary Collation to my String-to-Integer Split Function  |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-17 : 09:56:25
|
I would rather add a check constraint than have mixed collations.And I would definitely add that check constraint. I enforce data integrity at the database always. Not only don't I trust the application to handle it right 100% of the time, but other processes modify data too...like DBAs lol  This is one case where I'll trade the (minor) performance improvement for easy to maintain db |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 11:45:19
|
Yup, normally my approach to, and good to be reminded of it when I get casual about data-constraint-rules  But some things become blinking slow with all the constraint checking. Deleting parent data with lots of Children referential-integrity FKeys, for example.Lets assume I put the check constraint in place ...... what would you then think about a Binary Collation to improve performance?I know ... you lot are going to tell me to go away and test it and then let everyone else know if it made a measurable improvement.But I'm, asking because you must all have done this already, right?!! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-17 : 11:56:18
|
| never tried it :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-17 : 12:27:35
|
| Any suggestions for a test rig to get timings for something as simple as a single row lookup (i.e. WHERE MyPK = @SomeCode) so I can see if it is faster with Binary collation?Most of the time this is going to measure as 0ms I reckon ... but a 10% improvement (given that we do millions of such PK lookups every day) is going to be worth having. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-17 : 12:41:28
|
| set statistics time on and execute 1000 queries in a batch? |
|
|
|
|
|
|
|
|
Binary Collation for Performance?