Click here to monitor SSC
A service from Red Gate

in
Home SQL .NET SysAdmin Opinion Blogs Forums Books
Sign in | Join | Help
Home Blogs Forums
Blogs Home > Tony Davis > Paradoxical T-SQL

Tony Davis

Simple-Talk Editor
News, views and good brews

Paradoxical T-SQL

Published Friday, December 11, 2009 10:05 AM

I've always taken comfort in the fact that T-SQL belongs to an enlightened age of computer languages in which the operations that were specified were close to real language. When one sees a command called DBCC_CHECKDB, then one could feel pretty confident that it checks your database for any problems. Or so I thought…until I discovered DBCC DBREPAIR.

If you're feeling confident, without looking it up, that DBREPAIR will perform some sort of repair work on a damaged database, then you're in for a shock. With an extraordinary irony, the command does the opposite of what you might think and destroys your database entirely. It is like using a Kalashnikov rifle to cure a headache.

The DBCC DBREPAIR command has been in SQL Server and Sybase from the start and one can still find the occasional forum reference to a time when it clearly did have some use for 'repairing a database that had failed another DBCC check'. However, over time, the command was 'simplified' to the point where it meant 'please drop my database from a great height'. Use it and you need never again worry about that torn page in your database!

Mercifully, the DBREPAIR command has been 'repaired' to the Grim Deprecator. However, its existence is a salutary lesson to all who are lulled into such a sense of confidence in the straightforward nature of T-SQL. When it lobs you a curve ball, it can have a devastating effect. And what next one wonders? What other tools and procedures could Microsoft provide to keep under-worked DBAs on their toes? Perhaps a DBCC CLEANTABLE command that relieves your database of its messy tables? Or a DBCC DBRECOVER command that pitches your healthy Database into 'recovery mode'?

I planned to finish this editorial with further examples of amusingly paradoxical Transact SQL. However, it appears at this point as if some celestial DBA has mistakenly done a DBCC DBREPAIR on my brain, so consider it the Simple-Talk Christmas challenge. The best example of paradoxical T-SQL, added as a comment the editorial blog, will win a very nice iPod Nano.

by Tony Davis

Comments

 

Itzik said:

Hi Tony,

Sounds like a fun one; here are a few examples:

1. In English not unknown is known, in T-SQL NOT UNKNOWN is still UNKNOWN, that’s why, for example, the following query returns an empty set:

WITH Customers AS
(
 SELECT * FROM (VALUES('A'),('B'),('C')) AS C(custid)
),
Orders AS
(
 SELECT * FROM (VALUES(1, 'A'),(2, 'B'),(3, NULL)) AS O(orderid, custid)
)
-- Return customers who did not place orders
SELECT custid
FROM Customers
WHERE custid NOT IN
 (SELECT custid FROM Orders);

2. The word OR in T-SQL (as well as in logic) usually means AND in English. :) As an example, consider the following English statement:

“Return information about employees Steve and Herbert”

To phrase it in T-SQL, you would write:

SELECT empid, firstname, lastname, hiredate
FROM HR.Employees
WHERE firstname = 'Steve' OR firstname = 'Herbert';

3. Sometimes the best way in T-SQL to say something positive is to use two negatives, e.g., to enforce a CHECK constraint that allows only digits in a character string column you can use the following predicate:

CHECK (col NOT LIKE '%[^0-9]%')

Cheers,
BG
December 12, 2009 8:22 AM
 

Twitter Trackbacks for Tony Davis : Paradoxical T-SQL [simple-talk.com] on Topsy.com said:

PingBack from http://topsy.com/tb/ping.fm/rtxsf
December 12, 2009 1:15 PM
 

sqldbg said:

Not exactly paradoxical but the ones that got me back when I was newbie were:

- STATISTICS_NORECOMPUTE - why the negative? pardon the pun but does not compute.
Whats wrong with STATISTICS_RECOMUPUTE(??)


-  CHARINDEX and PATINDEX - related to an index how exactly?
December 13, 2009 10:49 PM
 

DamianM said:

The one I love is used for disabling and re-enabling check constraints.

You create a check constraint with ALTER TABLE x CHECK CONSTRAINT cname (condition).
You disable it with ALTER TABLE x NOCHECK CONSTRAINT cname (i.e. if you want to leave the definition in place but make it inactive).

The fun statement is when you re-enable the constraint, specifying that you don't want existing rows checked: ALTER TABLE x WITH NOCHECK CHECK CONSTRAINT cname.
December 14, 2009 5:36 AM
 

HowardW said:

How about this:

SELECT 1/2

Will return 0

However
SELECT 1.0/2.0
will return 0.500000
December 14, 2009 5:44 AM
 

blugrasmaniac said:

Given the following tables and query,

--Cars
create table Cars
(carId int, carName varchar(50))

--xref of Cars and Wheels
create table CarWheels
(wheelId int, car_id int)

--find all cars that do not have wheels mapped
select * from Cars
where carId in (select carId from CarWheels)

The query will always return every row in Cars, no matter what the data conditions are.  

The reason, of course, is that the sub-query is not returning what it seems at a glance - i.e. carId is not a valid column in CarWheels.

How to prevent this?  Make it a habit to always use table aliases in your query.  This query will fail as expected:
select * from Cars c
where c.carId in (select cw.carId from CarWheels cw)
December 14, 2009 1:08 PM
 

SteveQ said:

I can't go past the UPDATE() and COLUMNS_UPDATED() functions.  

I have seen many a developer get caught out by assuming that these functions are going to tell you whether a value in a column has changed.  They actually only tell you whether a column was affected by an insert or update.
December 14, 2009 3:42 PM
 

timothyawiseman@gmail.com said:

blugrasmaniac, that is a very nice one.

When I was first learning SQL I had no end of trouble with nulls, especially when combined with a not.  I wouldn't call them paradoxical per se, but they can definitely be non-intuitive.

For instance I would expect:
select *
from customers
where firstName not like 'John%'

To return rows where firstName was null since that would appear at first glance to not be the same as John.  Of course, it does not.

The fact that Ansi nulls can be turned on and off and controls whether or not a null is considered equal to a null adds to the confusion.  Searching for "SQL Gotchas" turns up an impressive number of articles that are all about the nuances of dealing with nulls.  Also, while it makes complete sense when you really think about it, I found that concatenating a null with any other string would give you a null instead of leaving the original string unchanged very odd when I was first learning.

December 14, 2009 5:49 PM
 

tgrignon said:

I spent hours searching through Books on Line to learn how to use triggers and didn’t find what I needed there. It may be there, somewhere, in the cryptic language Microsoft likes to couch things in but it certainly wasn’t apparent to me.
I wanted to write a trigger that audited a specific action on a column in a table and I needed to know the previous and updated entry for this column. What BOL didn’t tell me was that the ‘deleted’ variable held the previous value although it did tell me about the ‘inserted’ value which is the new value. This, I discovered in an old SQL Team Introductory article on Triggers by Garth Wells at http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

Terry
December 16, 2009 9:01 AM
 

AaronS said:

I hate INSTEAD OF triggers. You might as well be allowed to change the names of sql commands on the fly. If you don't think to check if they exist on a table beforehand, you are under the impression your INSERT, UPDATE, or DELETE statement will do just what it always does. That's definitely not the case.

I was once trying to simply insert a record into a table. I tried a dozen times, banged my head on my desk almost as many times trying to figure out why this simple insert won't work, and wondered if after 13 years of being a DBA I'd completely lost my mind. Then it came to me: INSERT INSTEAD OF trigger. And there it was! Arrrggghhh!

Aaron
December 16, 2009 11:17 AM
 

reeteshjoshi said:

Take this easy: The SQL Server name itself is paradoxical! Why?

It got named the SQL Server, but still we have to serve the SQL to it and it serves either result or error. Ha…ha…ha…
December 16, 2009 1:07 PM
 

AaronS said:

There are lots of paradoxes and oddities in SQL, many of which have been mentioned here. As for a command doing something you might not expect it to do,  another good example is ALTER INDEX DISABLE. You might think this would simply disable a table's index, and that's true to an extent (no pun intended).

According to BOL, "Disabling a clustered index prevents user access to the underlying table data. To enable an index, use ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING." So if you inadvertently disable a clustered index, you could be in for a long haul if it's a large table and you have to wait for a rebuild to complete before you can access your data again. This is not as destructive as DBCC DBREPAIR, but still not something you might expect to occur.
December 16, 2009 9:25 PM
 

GordonToo said:

In true panto fashion:

IF ('spaces' = 'spaces                ')
PRINT 'Oh no it isnt!'
December 17, 2009 5:37 AM
 

Philip Kelley said:

I was going to go on at length on all the "fun" you can have with check/nocheck, but DamianM beat me to it. So I'll just mention how misleading the simple pivot statement is. All it does is pivot data -- like, flip the rows to columns and columns to rows, right? So why does it always take me 30 minutes to get one of these working when starting from scratch? (These days, I just dig up the last one I wrote and modify it, and it only takes 10 minutes.)
December 18, 2009 8:40 AM
 

timothyawiseman@gmail.com said:

AaronS you have a good point.  I have found cases where an "after" trigger came in very handy for things like logging, but I do not think I have yet seen a good use case for an "instead of" trigger.
December 18, 2009 12:32 PM
 

AaronS said:

And the winner is?
January 12, 2010 11:32 AM
 

Tony Davis said:

Many thanks for all the great contributions! It was a really difficult choice, but in the end the winner, as announced in the December 23rd Simple-Talk newsletter, was DamianM.

Cheers,

Tony.
January 14, 2010 6:03 AM
You need to sign in to comment on this blog
<December 2009>
SuMoTuWeThFrSa
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789
How to Kill a Company in One Step or Save it in Three
 The majority of companies that suffer a major data loss subsequently go out of business. David Wesley... Read more...

Migrating from OCS 2007 R2 to Lync: Part 4
 Having migrated the rest of our users and legacy resources across, and start getting ready to... Read more...

Automated Script-generation with Powershell and SMO
 In the first of a series of articles on automating the process of building, modifying and copying SQL... Read more...

Seth Godin: Big in the IT Business
 Seth Godin has transformed our understanding of marketing in IT. He invented the concept of 'permission... Read more...

Using SQL Test Database Unit Testing with TeamCity Continuous Integration
 With database applications, the process of test and integration can be frustratingly slow because so... Read more...