SQL Server Myths

The whole area of “SQL Server myths” is an interesting one. One of the fascinations of working with SQL Server is that, the more you learn, the more you realize you don’t know. To the beginner SQL Server is presented as a series of black-and-white facts and rules designed to make learning easier. It is a necessary dogma. However, the more one discovers, the more one is obliged to admit that there are shades of grey. There are very few technical answers to which you couldn’t add the qualifying clause ‘But, on the other hand…’.

There have been some very interesting blog entries that set out to be Myth Busters, but are really pointing out that it isn’t wise to cling to hard-and-fast rules any longer than you have to. A recent one caught our eye, taking a new look at the adage that the use of foreign key constraints slowed performance. I suppose that the answer is ‘Yeeesss, but, on the other hand…’ (Selects can be faster sometimes, evidently).

Denis Gobo’s blog entry, SQL Myth: Truncate Cannot Be Rolled Back Because It Is Not Logged was a useful reminder of the need to test anything you’re told about SQL Server. Also, it reminded me of the debate about using table variables, which was triggered off by a tip last year on SQLServerCentral. The tip contained this advice:

Table variables are created and manipulated in memory instead of the tempdb database’

This ended in a fascinating post by Darren Gosbell, with a comment by Greg Linwood.

Greg Linwood’s series on clustered indexes has been fascinating reading, and the dogma about every table requiring a clustered index received a mauling in his blog entry Debunking myths about clustered indexes – part 1 (CIXs worsen bookmark lookups)

On a broader subject, Euan Garden’s blog is always interesting and his posting on SQL MythBusters – SQL Server is really a Sybase product not a Microsoft one was refreshing and timely. More of these legends are mentioned in the amusing articles The myths and legends of the holy land of the database, by Mark Whitehorn, which contain the excruciating ‘So, happily, Larry Ellison doesn’t have to say to his marketing people, “That’s another fine myth you’ve gotten me into”.’

One of the most irritating myths for the relational database developer is the myth of the superiority of the object-oriented approach and OODBMSs. Here is a typical contribution to the debate from Dare Obasanjo, Why Aren’t You Using An OODMS?

These just happen to have been the contributions that have stimulated us to argue and try things out, but we’d love to hear from anyone else who has seen some iconoclastic work on SQL Server. Simple-Talk gift bags to the three best contributions.