PASS Summit 2010 Keynote David DeWitt

This was the best presentation during all of PASS Summit 2010. Everyone enjoyed Dr. DeWitt's keynote session on SQL Query Optimization.

PASS Summit 2010 Keynote David DeWitt

View more presentations from GraySystemsLab.

SQL Server Row Counts in Properties Are Wrong!

One of my users came to me and asked why the row count in the table properties was different from the SELECT COUNT(1) from the table. This was a database that was sent to us from a different company and attached to our server. The first question for me was; where does row count in properties come from in SQL Server? As it turns out, it gets this count from the sysindexes table which we can see from the following;

USE DatabaseName
GO
SELECT rowcnt
FROM dbo.sysindexes
WHERE OBJECT_NAME(id) = 'TableName'
GO

-- Returns
330013

A count of the table results in the following;

USE DatabaseName
GO
SELECT COUNT(1) AS CNT
FROM dbo.TableName
GO


-- Returns
331882



To update the sysindexes table with the correct row counts, we run the following DBCC;

USE DatabaseName
GO
DBCC UPDATEUSAGE(0) WITH COUNT_ROWS;
GO

Now when we run our queries again, we get the following;

USE DatabaseName
GO
SELECT rowcnt
FROM dbo.sysindexes
WHERE OBJECT_NAME(id) = 'TableName '
GO

SELECT COUNT(1) AS CNT
FROM dbo.TableName
GO

-- Returns
331882
-- Returns
331882



Cast Scientific Notation as Money or Varchar

I run into Scientific Notation on occasion and always refer back to my TSQL scripts folder. Most of the time, I'll have a column where about 50% of the rows contain Scientific Notation. In these cases, I use a CASE statement.

First, CAST as VARCHAR:

LTRIM(RTRIM(CAST(CAST('2.50823E+12' AS FLOAT) AS NVARCHAR)))

Second, CAST as MONEY:

LTRIM(RTRIM(CAST(CAST(CAST('2.50823E+12' AS FLOAT) AS NVARCHAR)AS MONEY)))


Now, let's see this in a CASE statement:

,CASE
WHEN [AMOUNT] LIKE '%E-%' THEN LTRIM(RTRIM(CAST(CAST(CAST([AMOUNT]AS FLOAT) AS NVARCHAR)AS MONEY)))
WHEN [AMOUNT] = ' ' THEN NULL
ELSE [AMOUNT]
END AS [AMOUNT]



CHECKDB - A severe error occurred on the current command. The results, if any, should be discarded.

CHECKDB found 0 allocation errors and 0 consistency errors in database

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.


I ran into a case where I had corrupted pages in my database. I had previously had this database on a SQL Server 2005 instance. When I ran CHECKDB in SSMS 2005, it returned info messages detailing the consistency errors. However, after restoring the database on a 2008 instance and running CHECKDB in SSMS 2008, I received the message above. At first, you get this, “What now!” feeling. Don't panic. If you go to the SQL error log, you’ll see the CHECKDB results. Be sure that you are running CHECKDB with the WITH ALL_ERRORMSGS option.