T-SQL Words You Need to Stop Misspelling

  • Article

For the
last few days my inbox, Twitter and Facebook feeds have been full of
advice about which words I should stop misspelling. To be fair, in
English, I have relatively little problem, and any misspellings I do make
may be sloppy, but rarely result in misunderstandings. On the other hand, there
are some T-SQL usages that really do cause problems, for myself and others ... 

SELECT

This is perhaps the
first word we learn as T-SQL newbies, but there is still some
confusion. Some people spell this with a star on the end - this is easy
and natural, but it is often wrong and will only help you if you are either too
lazy to write out a list of columns, or too intellectually incurious to care
about performance.

AUTO_SHRINK

Actually, this is
not so much a misspelling as a weirdly archaic word that is simply not
acceptable in polite DBA society. Using it will fragment your indexes and your
chances of social and professional success with equally devastating effects.

IN

I know what you're
thinking. How could someone possibly misspell IN? However, as with English,
there are some weird and wonderful things about T-SQL. In some circumstances
you would be better to spell IN as EXISTS (especially when preceded by NOT.)
The problem is that IN and EXISTS handle NULL values differently.

Jens Suessmeyer fro
MS in Germany, came across the problem and gives a good example here: https://bit.ly/520pQM

Nor is this
peculiar to T-SQL: it's the same for those in Oracle-land too: https://bit.ly/6fMRP5

In practice, I
nearly always come across this problem when someone has changed a column to
allow NULLS - they can then discover to their consternation that queries which
"worked" previously now return no rows at all.

REPAIR_ALLOW_DATA_LOSS

You could be
forgiven for using this strange spelling, as the word has indeed found its way
into the language in this form. Although this spelling is correct, it is
pronounced REPAIR_ENSURE_DATA_LOSS
as you will indeed lose data if you use it. Please note, that using this word
in the same sentence as "msdb" is a desperate faux pas, resulting
only in pain and embarrassment.

And finally ...

I really could not
let this article pass without recording my favorite misspelling, even though it
has nothing to do with T-SQL. I once visited a financial services customer who
had, just that morning, discovered a small typo in code that was
re-implementing a legacy application. After a whiteboard session, where the
notes had been left scrawled in an awkward hand, a developer had boldly sallied
forth and coded up using RAND in place of ROUND. The result was a series of
credit forecasts using a random number with the customers' closing balances as
the seed, rather than using their rounded balance. Strange to say, nobody had
noticed for ... well, let's just say for long enough. Even stranger, when the
error was fixed, several of the financial wonks complained that the numbers
were no longer so useful!

Comments

  • Anonymous
    December 31, 2009
    The RAND/ROUND confusion is one of the funniest database anecdotes I've ever seen.