We all know what system tables are right? They are the tables within the master database that hold the metadata of all the objects stored in our SQL Server. In SQL2005 an extra set of views called the sys views has been provided on top of the system tables that makes even more information available to us.
How about INFORMATION_SCHEMA views? They are a set of views provided within SQL Server that sit on top of the system tables and present the information to the user in a comprehensive easy-to-understand manner. I believe (but don't quote me) that they also conform to a standard for presenting metadata as defined by the American National Standards Institute (ANSI).
As such the metadata of the INFORMATION_SCHEMA views and the information that they provide is not supposed to change between different versions of SQL Server. Well unfortunately they have. I've found one problem in INFORMATION_SCHEMA so far in SQL Server 2005 and I dare say there are others.
Do a favour for me. Issue the following statement against a SQL 2000 instance and then again against a SQL 2005 instance:
select data_type, numeric_precision
from information_schema.columns
where data_type = 'datetime'
or data_type = 'smalldatetime'
Now compare the rsults of the two queries. They're different right? In SQL 2000 numeric_precision returns '16' for smalldatetime and '23' for datetime. In SQL 2005 you get NULL for both.
I opened up the view definition to see why it is different. The first thing I noticed is that in SQL2000 the view pulls from the system tables whereas in SQL2005 it pulls from the sys views. Now I don't mind that so much - if they had been ported successfully that is.
What I found next quite angered me - this could have easily been avoided. Here's the T-SQL that INFORMATION_SCHEMA.COLUMNS uses to get the numeric_precision column:
SELECT convert(tinyint, CASE -- int/decimal/numeric/real/float/money
WHEN c.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN c.precision
END) AS NUMERIC_PRECISION
FROM
sys.objects o JOIN sys.columns c ON c.object_id = o.object_id
WHERE o.type IN ('U', 'V')
and here is what it SHOULD have been:
SELECT convert(tinyint, CASE -- int/decimal/numeric/real/float/money/datetime/smalldatetime
WHEN c.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127, 58, 61) THEN c.precision --added 58, 61
END) AS NUMERIC_PRECISION
FROM
sys.objects o JOIN sys.columns c ON c.object_id = o.object_id
WHERE o.type IN ('U', 'V')
Its not very different is it? Clearly the information is available so the fact that its not in INFORMATION_SCHEMA.COLUMNS means that this is a bug and I have told Microsoft so here.
This might seem incredibly insignificant, and to some it might be, but it succeeded in completely breaking one of my project's core applications that we are currently trying to migrate. Good job we tested it first eh!
Before I leave you, here's a little nugget on a similar theme that I found out today.
Have you ever used the following query to get the rowcount for a table?
select rowcnt
from sysindexes
where indid < 1
and [name] = 'mytablename'
Well if you have you will need to go back and revisit that code for it no longer works anymore. In SQL2005 sysindexes no longer returns the name of the table as the name of a heap - it just returns NULL instead. Instead, use the following:
select i.rowcnt
from sys.sysindexes i
inner join sys.sysobjects o
on i.id = o.id
where o.xtype = 'U'
and i.indid < 1
and o.[name] = 'mytable'
The message here, quite obviously, is if you are migrating your applications to SQL2005 please please please test them first. Don't just go and hit the upgrade button and think everything will be fine and dandy - chances are it won't be!
-Jamie