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.

Adding Leading Zeros in T-SQL

Today I ran into a situation where I had a character data type column that contained product ID numbers and these numbers were missing leading numbers. The column was a CHAR(11) as all product IDs should begin with leading zeros and be 11 characters long. To add leading zeros to a NVACHAR, VARCHAR, OR CHAR type column, use the RIGHT function.

SELECT RIGHT('00000000000' + LTRIM(RTRIM(PRODUCT_ID)),11) AS PRODUCT_ID

Or;

SELECT RIGHT(REPLICATE('0', 11) + LTRIM(RTRIM(PRODUCT_ID)),11) AS PRODUCT_ID

PASS SQL Summit 2009 Discount Code

With time running out for taking advantage of the pre-registration discounts to PASS 2009, here is a discount code for $100 off the registration fee. Get $100 off SQL Summit with code 24HR3D. See you at PASS 2009!

Microsoft Launches Non-Profit Open Source Foundation

I’m not sure what to think of this. I have always been a fan of the open source movement. I know, then why am I a Microsoft DBA? I can only say, at the time, my employers required me to support Microsoft products. And after working with SQL Server for many years, I became comfortable with it.

Anyway, Microsoft launched a new Non-Profit Open Source Foundation yesterday named, The CodePlex Foundation. And already, there are a lot of discussions about it. I think that Microsoft is trying to continue to build its community by having developers create open source software that will assist the community as a whole.

Also, I have no doubt that Microsoft has a business motivation in this as well, because after all, Microsoft is a profit driven company. It could be that Microsoft would like to see if there are ways for them sell their products to entities that run open source, or it could be something more. Either way, I don’t believe open source developers will embrace it as evident in this Slashdot post.

The CodePlex Foundation : http://codeplex.org/