Parentheses are Cheap (use them in Boolean expressions in SQL)

Here is a simple example of how a lack of parentheses will cause results that were possibly not intended:

Statement 1

  IF (1 = 2 OR 1 = 1 AND 1 = 2)
    PRINT 'True - 1'
  ELSE
    PRINT 'False - 1'

Statement 2

  IF (1 = 1 OR 1 = 2 AND 1 = 2)
    PRINT 'True - 2'
  ELSE
    PRINT 'False - 2'

The only difference between the Boolean expressions is that they evaluate to

Statement 1:

  (false OR true AND false)

Statement 2:

  (true OR false AND false)

Look at the statements. What will the results be? Is it obvious what the results will be?

The first statement is false, and the second is true.

The first statement finds that (1 = 2) is false and is followed by an OR, so it keeps going. (1 = 1) is true, but it's followed by an AND, so it keeps checking. (1 = 2) is false, so the result is false.

The second statement finds that (1 = 1) is true and is followed by an OR. It stops evaluating and returns true.

The lesson? Use parentheses to make things clear.

T-SQL: COUNT(*) without FROM

I just learned that “SELECT COUNT(*)” actually works without a FROM clause (no syntax error).

In a script, I wrote a SELECT and forgot to add the FROM. I was scratching my head as to why the “COUNT(*)” kept returning 1. Apparently it will always return 1 if there is no FROM.

Test Script:

SELECT COUNT(*) AS 'Ct* - Initial'
GO
SET NOCOUNT ON
GO
SELECT COUNT(*) AS 'Ct* - Initial'

SELECT @@SERVERNAME AS '@@SERVERNAME'
SELECT COUNT(*) AS 'Ct* - @@SERVERNAME'

SELECT TOP 10 [name] FROM sys.databases
SELECT COUNT(*) AS 'Ct* - sys.databases'

Results:

Ct* - Initial
-------------
1

(1 row(s) affected)

Ct* - Initial
-------------
1

@@SERVERNAME
---------
[snip]

Ct* - @@SERVERNAME
------------------
1

name
---------
master
tempdb
model
msdb
...

Ct* - sys.databases
-------------------
1

WebMD and Column

I have been a WebMD for awhile now, and I am really enjoying it there. My boss is great; the rest of the Data Architecture team is great. I really enjoy my role there: leadership without management. Perfect.

The 16th entry in my column was published today. Linda, the series editor, is great to work with.

New Job - WebMD

It's been a long time since my last blog entry, but that doesn't mean I've been idle.

I recently left Corillian after more than eight years and accepted a position with WebMD Health Services as a Data Architect. I am really enjoying it and looking forward to contributing to WebMD's continued success.

On the writing front, the eleventh installment of the SQL Credit series on Database Journal will be published November 23rd. I am also working on an article on leadership development.