ISNUMERIC functions differently SQL 2000 to SQL 2005

Did you know that the ISNUMERIC function works differently between SQL Server 2000 and SQL Server 2005?  I didn't realize it until the other day when a post on the forums made me dig into it a little bit.

The difference is documented in the books online for sp_dbcmptlevel.

SQL Server 2000 and SQL Server 2005 Compatibility Level 80
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are significant.

For example, the following SELECT ISNUMERIC('121212,12') query returns 0. This indicates that the string 121212,12 is not numeric.

SQL Server 2005 Compatibility Level 90
In SELECT ISNUMERIC('<string>'), embedded commas within <string> are ignored.

For example, the following SELECT ISNUMERIC('121212,12') query returns 1. This indicate that the string 121212,12 is numeric.

Pretty interesting, only the way to maintain the functionality if you actually expect it to return a zero (0) for commas means that you lose the ability to use things like CROSS APPLY, PIVOT, UNPIVOT, Common Table Expressions (CTE's), SQL CLR, and numerous other features in SQL Server 2005.  The only real way to go around this would be to change code to function differently and test for the commas or do a bulk update of the column to replace commas with a different symbol to force a failure or do an inline REPLACE in the SELECT.

Drop All Indexes and Stats in one Script

I am not sure why someone would want to do this, but it was asked on the forums, and I figured I would post the code I created to perform such a nightmarish operation.  As with any post that I make providing code that could be potentially damaging and dangerous, if you use it, you do so at your own risk.  Don't send me emails complaining that you got fired for deleting all the indexes with the scripts on this post.  I am not going to be able to help you fix it, and my recommendation is going to be restore a backup, and start scripting them all off if you still have a job.

SQL Server 2000 Code

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR

SELECT sysindexes.name, sysobjects.name, sysusers.name
FROM sysindexes
JOIN sysobjects ON sysindexes.id = sysobjects.id
JOIN sysusers ON sysobjects.uid = sysusers.uid
WHERE indid > 0
  AND indid < 255
  AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 0
  AND sysobjects.TYPE = N'U'
  AND NOT EXISTS (SELECT 1 FROM sysobjects WHERE sysobjects.name = sysindexes.name)
ORDER BY sysindexes.id, indid DESC

OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql
  EXEC sp_executesql @sql  
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE dropindexes
DEALLOCATE dropindexes

GO
DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropstats CURSOR FOR

SELECT sysindexes.name, sysobjects.name, sysusers.name
FROM sysindexes
JOIN sysobjects ON sysindexes.id = sysobjects.id
JOIN sysusers ON sysobjects.uid = sysusers.uid
WHERE indid > 0
  AND indid < 255
  AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 1
  AND sysobjects.TYPE = N'U';

OPEN dropstats
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
  EXEC sp_executesql @sql  
  --PRINT @sql
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
END
CLOSE dropstats
DEALLOCATE dropstats


The above script will work for SQL 2005 and 2008 also, but only because compatibility views have been carried forward in code by Microsoft. The correct code for doing this in SQL 2005 and 2008 is as follows:

SQL Server 2005/2008

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR

SELECT indexes.name, objects.name, schemas.name
FROM sys.indexes
JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE indexes.index_id > 0
  AND indexes.index_id < 255
  AND objects.is_ms_shipped = 0
  AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name)
ORDER BY objects.OBJECT_ID, indexes.index_id DESC


SELECT * FROM sys.stats
OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql
  EXEC sp_executesql @sql  
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE dropindexes
DEALLOCATE dropindexes

GO
DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropstats CURSOR FOR

SELECT stats.name, objects.name, schemas.name
FROM sys.stats
JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE stats.stats_id > 0
  AND stats.stats_id < 255
  AND objects.is_ms_shipped = 0
ORDER BY objects.OBJECT_ID, stats.stats_id DESC

OPEN dropstats
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
  EXEC sp_executesql @sql  
  --PRINT @sql
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
END
CLOSE dropstats
DEALLOCATE dropstats


Hope it helps someone out.

Creating an Indexed View with a Self-Join (Kinda)

The following is based on a post on the MSDN Forums regarding building an indexed view in SQL Server.

Indexed Views are another tool in the toolset for squeezing performance out of SQL Server.  When applied correctly they can be very powerful, but they have so many limitations, that they really are very difficult to use.  The poster on the above thread hit one of these limitations that really seems kind of nonsensical when you are looking at the code, that being that a Indexed View can't have a self-join in its definition.  If you would like to know all about indexed views and how they work, as well as some examples you can play with in AdventureWorks take a look at the following whitepaper on MSDN:

Improving Performance with SQL Server 2005 Indexed Views ...

First to reproduce the problem we'll need to create some tables and data.  Don't worry, these will be reusable for this whole exercise:

SET NOCOUNT ON;
GO
USE tempdb
GO
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
GO
CREATE TABLE dbo.dimColor (ColorID INT IDENTITY PRIMARY KEY, ColorName VARCHAR(10))
GO
INSERT INTO dbo.dimColor VALUES ('red')
INSERT INTO dbo.dimColor VALUES ('blue')
INSERT INTO dbo.dimColor VALUES ('black')
INSERT INTO dbo.dimColor VALUES ('silver')
GO
CREATE TABLE dbo.dimManufacturer (ManufacturerID INT IDENTITY PRIMARY KEY, ManufacturerName VARCHAR(10))
GO
INSERT INTO dbo.dimManufacturer VALUES ('Ford')
INSERT INTO dbo.dimManufacturer VALUES ('Chevrolet')
GO
CREATE TABLE dbo.Cars (CarID INT IDENTITY PRIMARY KEY, ManufacturerID INT, FirstColorID INT, SecondColorID INT)
GO
INSERT INTO dbo.Cars VALUES (1, 1, 1)
INSERT INTO dbo.Cars VALUES (1, 1, 2)
INSERT INTO dbo.Cars VALUES (1, 1, 3)
INSERT INTO dbo.Cars VALUES (1, 1, 4)
INSERT INTO dbo.Cars VALUES (1, 2, 1)
INSERT INTO dbo.Cars VALUES (1, 2, 2)
INSERT INTO dbo.Cars VALUES (1, 2, 3)
INSERT INTO dbo.Cars VALUES (1, 2, 4)
INSERT INTO dbo.Cars VALUES (1, 3, 1)
INSERT INTO dbo.Cars VALUES (1, 3, 2)
INSERT INTO dbo.Cars VALUES (1, 3, 3)
INSERT INTO dbo.Cars VALUES (1, 3, 4)
INSERT INTO dbo.Cars VALUES (1, 4, 1)
INSERT INTO dbo.Cars VALUES (1, 4, 2)
INSERT INTO dbo.Cars VALUES (1, 4, 3)
INSERT INTO dbo.Cars VALUES (1, 4, 4)
INSERT INTO dbo.Cars VALUES (2, 1, 1)
INSERT INTO dbo.Cars VALUES (2, 1, 2)
INSERT INTO dbo.Cars VALUES (2, 1, 3)
INSERT INTO dbo.Cars VALUES (2, 1, 4)
INSERT INTO dbo.Cars VALUES (2, 2, 1)
INSERT INTO dbo.Cars VALUES (2, 2, 2)
INSERT INTO dbo.Cars VALUES (2, 2, 3)
INSERT INTO dbo.Cars VALUES (2, 2, 4)
INSERT INTO dbo.Cars VALUES (2, 3, 1)
INSERT INTO dbo.Cars VALUES (2, 3, 2)
INSERT INTO dbo.Cars VALUES (2, 3, 3)
INSERT INTO dbo.Cars VALUES (2, 3, 4)
INSERT INTO dbo.Cars VALUES (2, 4, 1)
INSERT INTO dbo.Cars VALUES (2, 4, 2)
INSERT INTO dbo.Cars VALUES (2, 4, 3)
INSERT INTO dbo.Cars VALUES (2, 4, 4)
GO

To reproduce the original error reported in the forums post, we'll first create a view that has the "self-join" in its definition:

CREATE VIEW dbo.CarDetails
WITH SCHEMABINDING
AS
SELECT c.CarID,
  m.ManufacturerName,
  pc.ColorName AS [FirstColor],
  sc.ColorName AS [SecondColor]
FROM dbo.Cars c
JOIN dbo.dimManufacturer m ON c.ManufacturerID = m.ManufacturerID
INNER JOIN dbo.dimColor pc ON c.FirstColorID = pc.ColorID
INNER JOIN dbo.dimColor sc ON c.SecondColorID = sc.ColorID
GO
CREATE UNIQUE CLUSTERED INDEX CarDetails_CarID ON dbo.CarDetails (CarID)
GO

If you try to create this view, you will get an exception like the following

Msg 1947, Level 16, State 1, Line 1 Cannot create index on view "tempdb.dbo.CarDetails". The view contains a self join on "tempdb.dbo.dimColor".

So what exactly is it complaining about, after all there isn't a self join in the normal way that we would think of it? Well the self join statement is somewhat ambiguous, as is the self join limitation as listed in the whitepaper on MSDN. You actually can't join the same table two times in a indexed view, even if it is through another table. So what do you do if you have an actual structure like the one posted above where you have two foreign key columns to the same table and you need the double join to actually make sense of the data?

I personally was confused by the error, so I built the above example and posted it to the MVP private groups to see if someone else could help shed some light onto the problem.  Assistance and a solution were provided by Steve Kass and Aaron Bertrand, and the following example will show you how to code around this limitation.

CREATE TABLE dbo.Two (i INT)
INSERT INTO dbo.Two VALUES (1)
INSERT INTO dbo.Two VALUES (2)
GO
CREATE VIEW dbo.CarDetails_Imed
WITH SCHEMABINDING
AS
SELECT
   c.CarID,
   m.ManufacturerName,
   CASE WHEN i = 1 THEN 'FirstColor' ELSE 'SecondColor' END AS whichColor,
   CASE WHEN i = 1 AND c.FirstColorID = pc.ColorID THEN pc.ColorName
        WHEN i = 2 AND c.SecondColorID = pc.ColorID THEN pc.ColorName END AS [Color]
FROM dbo.Cars c
JOIN dbo.dimManufacturer m ON c.ManufacturerID = m.ManufacturerID
CROSS JOIN dbo.Two
INNER JOIN dbo.dimColor pc ON (c.FirstColorID = pc.ColorID OR c.SecondColorID = pc.ColorID)
WHERE
   CASE WHEN i = 1 AND c.FirstColorID = pc.ColorID THEN pc.ColorName
        WHEN i = 2 AND c.SecondColorID = pc.ColorID THEN pc.ColorName END IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX CarDetailsImed_CarID ON dbo.CarDetails_Imed (CarID, ManufacturerName, whichColor, Color)
GO
CREATE VIEW dbo.CarDetails_Final
WITH SCHEMABINDING
AS
SELECT CarID, ManufacturerName, [FirstColor], [SecondColor]
FROM
(
SELECT
   CarID,
   ManufacturerName,
   whichColor,
   Color
FROM dbo.CarDetails_Imed WITH (NOEXPAND)
) src
PIVOT
(
MAX(Color)
FOR whichColor IN ([FirstColor], [SecondColor])
) pvt
GO

The first thing Steve did was add a new table called dbo.Two into the mix.  This table has, you guessed it, two rows holding values 1 and 2 respectively.  Then he rewrote the query to use a cross join, so the output is going to be double the size in rows.  A slight error in code was corrected by Aaron initially, and then Steve provided the fix as well with the idea that you could pivot the data to get the output to match that of the original view since the cross join is going to be a normalized return instead of a denormalized output as in the original view.

This view can now be indexed and used as an intermediate view to the actual results view which will pivot the data to provide the formatted output that would come from the original view that could not be indexed. One nice thing is that you can put the table hint WITH(NOEXPAND) directly into the output view DDL definition so that queries don't have to use this option explicitly. It has already been defined in the view itself. Since the intermediate view is now indexable, you can also create any needed covering indexes to satisfy the queries against the output view and assist with the pivot.

I know this is a big hack to make something work, but it is one way to solve the problem. One final note is that since this uses the PIVOT operator it is not possible as coded above in SQL Server 2000. However, you can write a 2000 compliant pivot query that would still make use of the indexed intermediate view.

Picking Random Rows from A Table

Being that I work for a number of restaurant chains, I get some interesting requests/requirements for extracting data that has been collected through various marketing campaigns or contests. A common request in the past has been to randomly pick a winner or randomly pick a list of winners from a particular set of data. This seems like it should be pretty easy to do, but I was never able to figure out how to pick a truly random set of records without looping. Until today, and the solution is really simple:

USE [tempdb]
GO
CREATE TABLE randomresults
(id INT IDENTITY PRIMARY KEY)
GO
INSERT INTO randomresults
DEFAULT VALUES
GO 100000 -- execute batch 100000 times
-- Return rows in a random order
SELECT TOP 10 id
FROM randomresults
ORDER BY checksum(NEWID())
GO

It doesn't matter how many times you run this, it will always return a completely random resultset.

SQL Server Views and Performance

Someone asked me recently how do views affect performance in SQL Server, since they don't get a compiled execution plan like a stored procedure?  In investigating this, I was at first surprised to find that the person asking was absolutely correct, there is no compiled plan for a view in the dmv's:

select usecounts,cacheobjtype,objtype,query.text,executionplan.query_plan 
from sys.dm_exec_cached_plans 
cross apply sys.dm_exec_sql_text(plan_handle) as query
cross apply sys.dm_exec_query_plan(plan_handle) as executionplan 
where text not like '%sys%'and cacheobjtype ='compiled plan' 

If you run the above, you will find that Views get a Parse Tree instead.  So what does this mean exactly in the context of performance and using a view?  The answer is not really all that simple.  The Parse Tree defines the expansion of the view definition for execution time.  While the actual view doesn't have a compiled plan, the queries that use the view do.  You can test this by clearing the procedure cache:

DBCC FREEPROCCACHE

and then running a query, twice with different parameter values, with a view in your database:

SELECT * 
FROM [dbo].[SampleView]
WHERE OrderID = 10248
 
SELECT * 
FROM [dbo].[SampleView]
WHERE OrderID = 10250

If you look at the cache now, you should see that this query was parameterized by the SQL Server, and stored in the plan cache for reuse.  This means that your index tuning for a system that heavily uses Views that join multiple tables, must be based, not on the view definition itself but instead on the code that utilizes the view.

The Anatomy of a Deadlock

This post has been moved to my new blog site. You can find it at the following link:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx

Identifying Blocked Processes with the Blocked Process Report

If you have query delays from blocking, SQL Server has the Blocked Process Report that can generate as an XML document in Profiler, or as a WMI Alert which will provide the Blocked Spid and process information, as well as the blocking spid and process information.  However, before you can use this, you have to set a blocked process threshold to trigger the report.  By default it is set to 0 or off, but you can enable it with the following:

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 5 ;
GO
RECONFIGURE ;
GO

By using this, you can set a trace for a single event and it will generate a report much like the output of deadlock trace flag 1205/1222.  In fact if you have ever read a deadlock graph from the ErrorLog for either of these trace flags, then the Blocked Process Report will seem really friendly.

Difference between SQL 2000 and SQL 2005 system Views

Part of my job is answering questions for internal and external audits.  Today one came in that made me have to break out some code and do some investigation.  One of the queries I provided had conflicting data with the results from another query.  Basically the first query said that there were objects owned by a database user, while the second said that the database user can't create objects.  This might seem trivial since it is possible that the user once had rights to create objects, only the objects were created since the last audit, and the user never had create rights on the database.

The problem wasn't that the user created objects, it was that the queries being used were returning invalid information in SQL 2005 where they worked correctly in SQL 2000.  As a part of the upgrade to 2005, a new schema was created for DBA use in one of our databases where I copy tables (using SELECT * INTO DBA.TableName_DR_#### FROM TableName) before making changes to them in a Deployment Request, so that there is a rapid rollback point for changes being made in the event of a problem.  In SQL 2000 we would run code like the follow:

SELECT sysobjects.name AS [object Name], sysusers.name AS Owner, CASE 
WHEN sysobjects.xtype = 'S' THEN 'System Table' 
   WHEN sysobjects.xtype = 'P' THEN 'Stored Procedure' 
   WHEN sysobjects.xtype = 'U' THEN 'User Table' 
   END AS Type
FROM sysobjects 
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
Where sysobjects.xtype in ('S','P','U')
Order by sysobjects.xtype desc

With schemas this pulls back incorrect users as the owner of objects.  Instead in SQL 2005, the query should look like this:

select o.name, s.name [schema], p.name [schema owner], o.type_desc [Type]
from sys.objects o 
join sys.schemas s on o.schema_id = s.schema_id
join sys.database_principals p on s.principal_id = p.principal_id

In some cases like the above the output from the compatibility is not equivalent.

How to Disable All Constraints or Triggers in a Single Command

From time to time we all make mistakes, no DBA is immune from creating a total disaster with the click of a button.  Luckily today this was on a testing database and not on a production one, but a table hint out of place, resulted in the loss of a 90GB database table in less than a few seconds.  What started as a simple data purge ended up being nothing short of a disaster, albeit on a test database. 

The idea was to purge roughly 80GB of the data from this table, so rather than issue a long running set based delete, I opened the table in the SSMS designer and made a change to one of the columns, then scripted the operation to a new window.  I did this so that I could replace the dynamic SQL Statement with a statement to grab the rows to keep.  The problem was I put the WITH (HOLDLOCK, TABLOCKX) after my WHERE clause.  Since this is dynamic SQL, the syntax will pass a syntax check, but when the code actually executes you get an exception, and no data is copied to the tmp_Tablename table from the original base table, and then the original base table is dropped, leaving you with a wonderfully empty new table.

So on to fixing the problem and the purpose of this post.  To solve the problem, I used an integration package to pull the data from the production database table into a flat file which I then could import back into the testing server.  The problem came when I went to load the data, that check constraints failed, and the load failed.  What to do??  Well, it turns out that disabling all constraints and triggers in database is really simple, you just need to use the undocumented sp_msforeachtable stored procedure as follows:

--Disable Constraints 
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

--Disable Triggers 
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' 

-- Load data Now

--Enable Constraints 
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

--Enable Triggers 
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 

this allowed for the data to be loaded, and then I was able to resolve the orphaned records as needed.

Comparing 2 Results Sets to see if they are identical

A post on the MSDN forums asked how to check if two results sets were identical, and Jim McLeod offered a pretty simple method to check this, that was worth sharing:

SELECT CASE WHEN COUNT(*) = 0 THEN 'Same' ELSE 'Different' END
FROM (
        (
        SELECT * FROM Table1 
        EXCEPT
        SELECT * FROM Table2
        ) 
        UNION 
        (
        SELECT * FROM Table2
        EXCEPT
        SELECT * FROM Table1 
        )
) dv

This query basically gets all the rows that are in Table 1 but not Table 2, then UNIONS all rows that are in Table 2 but not Table 1.  If there's zero rows for both, the result sets must be the same.

Fast Simple, and easy to implement.

Making the GO command live up to its fullest potential

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/06/28/making-the-go-command-live-up-to-its-fullest-potential.aspx

Things to know about the SQL 2008 Date/Time Datatype

I was researching a question made on the SQL Forums regarding the return results of the SQL 2008 Date/Time datatype. If you run the following code, the output is a full datetime output in the Query Results.

SELECT CAST(GETDATE() AS DATE)

The issue in question was already reported in a connect Feedback and identified as an error by Microsoft, however they posted it as fixed and under test 2/7/2008 so the issue still exists in the Feb CTP which is what brought up the question.

However, while searching for this I happened upon another Feedback that provides some good return information from Microsoft about the implicit conversions that occur during usage of the various date/time datatypes in SQL 2008. Like all other datatypes in SQL, the newer date/time types introduced in 2008 have a conversion hierarchy which is (highest to lowest):

• DATETIMEOFFSET
• DATETIME2
• DATETIME
• DATE or TIME

What this means is that when a DATE variable is compared to a DATETIME datatype, the DATE variable will be implicitly converted to a DATETIME. This is important to keep in mind when using these datatypes.

The other important thing that the feedback on this page provides is that the output of the DATEADD() function in TSQL has a DATETIME return datatype. This means that if you have a DATE variable @DateVar that you are doing a comparison with using

DATEADD(dd, 1, @DateVar)

against a DATE column in a table, you force an implicit conversion of the column in the table to the DATETIME datatype for the matchup.