T-SQL Words You Need to Stop Misspelling - SQL Server Experts - Site Home - TechNet Blogs

T-SQL Words You Need to Stop Misspelling

T-SQL Words You Need to Stop Misspelling

  • Comments 1
  • Likes

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 ... 


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.


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.


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: http://bit.ly/520pQM

Nor is this peculiar to T-SQL: it's the same for those in Oracle-land too: http://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.


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!  

  • The RAND/ROUND confusion is one of the funniest database anecdotes I've ever seen.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment