Click here to monitor SSC

Tony Davis is an Editor with Red Gate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the Simple-talk.com and SQLServerCentral.com websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his Simple-Talk.com blog or his SQLServerCentral.com author page. As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management. In his spare time, he enjoys running, football, contemporary fiction and real ale.

SQL Server Myths

Published 27 June 2007 10:24 am

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?

 

It is therefore refreshing to read Object Oriented Programming Oversold!, by Mr. B. Jacobs 

 

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.

 

Cheers,

 

Tony.

One Response to “SQL Server Myths”

  1. drsql says:

    >>useful reminder of the need to test anything you’re told about SQL Server< <

    It should also hopefully serve as a reminder to all of the people who tell people stuff that they haven’t actually tested :)

    I have been using SQL Server, well, like forever, and one thing I never do anymore is take for granted that I know something until I have tested it, usually in a test script that I include with most any advice I dole.

Leave a Reply