SQL Server Fun
Lately, I've been having "fun" with SQL Server:
- Today I made the phun discovery that indexes on a varchar column are case-insensitive by default. Apparently, you can change the default collation in SQL Server on a per database basis, though I haven't figured out how to do this yet. But anyway, what kind of a crazy idea is this? Who decides these things and why isn't it written in big red flashing letters? Why would anyone assume that you would want your varchar columns to be case-insensitive?
- Earlier this week, we discovered a problem with our database migration scripts. It seems as though if you send a batch of statements to SQL Server containing intermingled DDL and DML, the preprocessor will reorganise the statements so that the DDL statements are executed before the DML statements. This can create some strange behaviour if you are updating values in a column that you drop later on in the batch. Once we realised this, the trick was to appropriately delimit the batch so that the statements got executed in the right order. Why we are doing this is the topic of a future blog entry.
- On a more positive note, on Friday night at the Bangalore .NET user's group meeting I attended a great presentation by Vinod Kumar on SQL Server execution plans. The talk was very informative and it reveal how much I have to learn about the inner workings of RDBMSs. Interestingly, he was also talking about the implications of intermingling DDL and DML, though in his case, it was with regards to caching.