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.

SqlCredit - Part 2

The second installment in the SqlCredit series was just published this morning. This article is entitled "Creating the Database, Tables, CRUD Procedures".

My hope is that it will generate much more discussion in the forum.

Rob