While reading another blog post this morning I was reminded of the super helpful metadata function that is OBJECTPROPERTY.  I often have to stop and remind myself that while all the information raised by OBJECTPROPERTY is available directly through the catalog views it is in many cases simpler to deal directly with the function.  For example, solving the blogger’s original question of objects without indexes can be accomplished in a simple query:

We can extend our search to find all objects without an index, views and tables alike.

There is a wealth of information available to you via OBJECTPROPERTY.  What metadata queries could you simplify today?

If not, you need to start.  As Aaron Bertrand mentions in his blog today this will become mandatory in a future version (hopefully vNext) of SQL Server.

If you are looking at this post with a puzzled look I am referring to ending SQL statements with a semicolon.  In SQL Server 2008 these terminators are only required with CTEs, MERGE, and Service Broker. 

I have been a fan of them for a long time.  They add to the clarity of code as well as now making your code future proof.  Double win!

One of the more important structures in a database are foreign keys.  Referential Integrity (RI) rarely gets the publicity it deserves and is often pushed aside during the development period of a data driven application.  Before a database leaves development (I would argue conceptual design, but I’m a stickler) we should confirm that all possible RI is in place.  But how do we quickly do this without spending hours looking through Object Explorer?

Here are a couple queries that make use of the catalog views to point out potential misses in RI (Note, these queries assume SQL 2005 or higher).

First, we simply want to find tables that contain no foreign keys.

--Tables with no foreign keys
SELECT OBJECT_SCHEMA_NAME(t.[object_id], db_id()) AS SchemaName
    ,t.name AS ObjectName
FROM sys.tables AS t
    LEFT JOIN sys.foreign_key_columns AS fkc ON fkc.parent_object_id = t.[object_id]
WHERE fkc.constraint_column_id IS NULL
ORDER BY SchemaName, ObjectName;

This first query will simply point out objects for us.  This is okay if you were the original author of the database and you know where potential references exist.  If you were not the original author then we need to go a step further and see if there are primary key columns that could be referenced.

--Tables with no foreign keys that have potential matches
SELECT OBJECT_SCHEMA_NAME(t.[object_id], db_id()) AS SchemaName
    ,t.name AS ObjectName
    ,c.name AS ColumnName
    ,OBJECT_SCHEMA_NAME(pc.[object_id], db_id()) AS PotentialMatchSchemaName
    ,OBJECT_NAME(pc.[object_id]) AS PotentialMatchObjectName    
    ,pc.name PotentialMatchColumnName
FROM sys.tables AS t
    INNER JOIN sys.columns AS c ON c.[object_id] = t.[object_id]
    LEFT JOIN sys.foreign_key_columns AS fkc ON fkc.parent_object_id = t.[object_id]
    INNER JOIN (
                SELECT c.[object_id], c.column_id, c.name
                FROM sys.columns AS c
                    INNER JOIN sys.index_columns AS ic ON ic.column_id = c.column_id
                        AND ic.[object_id] = c.[object_id]
                    INNER JOIN sys.indexes AS i ON i.index_id = ic.index_id 
                        AND ic.[object_id] = i.[object_id]
                WHERE i.is_primary_key = 1    
                ) AS pc ON pc.name LIKE '%' + c.name +'%' 
                    AND pc.[object_id] <> c.[object_id] 
                    AND pc.column_id <> c.column_id
WHERE fkc.constraint_column_id IS NULL
ORDER BY SchemaName
    ,ObjectName
    ,c.column_id
    ,PotentialMatchSchemaName
    ,PotentialMatchObjectName
    ,PotentialMatchColumnName;

With this query, we can look through potential matches and see if the primary key of those objects are the same value in our child object.

We could go even one step further and dynamically generate the foreign key creation.

--Dynamically build the foreign key for matches
SELECT OBJECT_SCHEMA_NAME(t.[object_id], db_id()) AS SchemaName
    ,t.name AS ObjectName
    ,c.name AS ColumnName
    ,OBJECT_SCHEMA_NAME(pc.[object_id], db_id()) AS PotentialMatchSchemaName
    ,OBJECT_NAME(pc.[object_id]) AS PotentialMatchObjectName    
    ,pc.name PotentialMatchColumnName
    ,'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id], db_id())) 
    + '.' + QUOTENAME(t.name) 
    + ' ADD CONSTRAINT FK_' 
    + t.name + '_' + OBJECT_NAME(pc.[object_id]) 
    + '_' + pc.name AS CreateFKStatement
FROM sys.tables AS t
    INNER JOIN sys.columns AS c ON c.[object_id] = t.[object_id]
    LEFT JOIN sys.foreign_key_columns AS fkc ON fkc.parent_object_id = t.[object_id]
    INNER JOIN (
                SELECT c.[object_id], c.column_id, c.name
                FROM sys.columns AS c
                    INNER JOIN sys.index_columns AS ic ON ic.column_id = c.column_id
                        AND ic.[object_id] = c.[object_id]
                    INNER JOIN sys.indexes AS i ON i.index_id = ic.index_id 
                        AND ic.[object_id] = i.[object_id]
                WHERE i.is_primary_key = 1    
                ) AS pc ON pc.name LIKE '%' + c.name +'%' 
                    AND pc.[object_id] <> c.[object_id] 
                    AND pc.column_id <> c.column_id
WHERE fkc.constraint_column_id IS NULL
ORDER BY SchemaName
    ,ObjectName
    ,c.column_id
    ,PotentialMatchSchemaName
    ,PotentialMatchObjectName
    ,PotentialMatchColumnName;    

With the queries above in hand you can give yourself a pretty good indication of the work necessary to have solid RI in place for your database.

One of my favorite features of SQL Server 2008 is Change Data Capture.  I’ve blogged about it and talked to many user groups about it.  One nagging detail about the feature is that it is only included in the Enterprise Edition.  I’ve spoken to a lot of users that are on Standard Edition and either don’t have the financial ability to move to Enterprise or can’t justify the cost difference for the particular feature.

I’m aiming to resolve that issue with a project I started out on CodePlex called StandardCDC.  This project will implement the conceptual working parts of Change Data Capture on a Standard Edition instance running either SQL Server 2005 or 2008.  Here is what you will get:

• The ability to track DML changes on any table in a database with the results written to a relational format in a separate schema. 
• The ability to configure which columns are tracked for a given table.
• The ability to store tracking data on a separate filegroup.
• Automated purge of tracking data (user configurable, defaulting to 12 hours).
• A DDL trigger to alert you of changes to tracked objects.
• Easy to use stored procedures that allow you to simply reference your table and have all implementation handled for you.

If you are currently on Standard Edition and want to try StandardCDC I would love to hear from you.  Tell me what you love, what you hate, and why you think I’m a moron (if necessary).  My only goal here is to share a little joy with the full SQL community.

Ladies and gentlemen, start your downloads!

This came up at the SQLSaturday speaker dinner tonight and I had missed Bob Ward’s post about it.  Definitely good news for all those customers on Standard edition. 

The cumulative update for SQL Server 2005 should be arriving in June for SP3.  SQL Server 2008 will be getting CU2 for SP1 in May.

Always cool to see the SQL team get it right.

Just in time for Christmas…yes Virginia, there really is an SP3 for SQL Server.  :)

I really wasn’t sure this was going to be a reality for a while there.  You can start looking through the detail here.  Hopefully you don’t have your heart set on the “What’s New” document, it takes you to a Page Not Found at 10:20PM EST.  Doh.

The KB for all the bug fixes can be found here.  Happy installing!

Say you're a DBA responsible for several instances of SQL Server running in your company and you have a long deserved vacation coming up.  You want to know about data changes to certain mission critical tables while you're away but don't want your spouse noticing the company laptop tucked away in the carry on luggage.  Well, thanks to the SQL Server Samples blog you can put your fears to rest with the open source SQLRSS project.  The project writes changes to an RSS feed that can be subscribed to, alerting you of data changes as they take place.  With an RSS reader you can stay on top of changes and not be the dork with a laptop open down on the beach (for full disclosure I've been that dork).

This project is SQL Server 2008 specific as it makes use of Change Data Capture (one of my favorite new features).  I'm going to see how difficult it is to re-engineer this for SQL 2005 making use of similar auditing tables.  Another post to follow...

So check out this open source project on that VPC/VM you've got running with SQL Server 2008 RC0.  Your spouse/significant other will appreciate it.  ;)

I had an interesting email thread on Friday with Eric Kang, a Program Manager in the SQL Server team, about the IntelliSense feature in SQL 2008.  Eric has been gracious enough to let me post the contents of this thread here.

The conversation related to what I felt was misinformation in various places about whether or not IntelliSense would be backward compatible.  Currently, CTP6 will support IntelliSense against a SQL 2005 or SQL 2000 instance.  This was exciting to me as I tend to work in multiple revs of the product.  As it turns out, the RTM version of SQL 2008 WILL NOT support this behavior.  Here is Eric's statement to why that is the case:

CTP5 and CTP6 IntellliSense did not properly check the server version but allowed itself working on SQL Server 2005 (or any downlevel version).

Unfortunately, it was a known defect since core modules were designed explicitly for SQL Server 2008 and later versions. #1 goal of IntelliSense is to help users authoring Transact-SQL script more efficiently so that increase the productivity. However, IntelliSense could cause negative impact on 2005 or any unsupported versions by incorrectly suggesting syntax error or suggest things that won't actually execute. This case, users will spend more time to figure out the truth.

Although IntelliSense provides an application level feature, the underlying implementation is similar to rebuilding the server side lexer, parser binder in managed code base. The scale of project is more likely catching up the full scope of what SQL Server has implemented for many years. Adding support for downlevel version would multiply this scope of work.

Our team envisioned to invent a client side framework and platform that understand syntactic and semantic context of Transact-SQL script that enable many *intelligent* applications and tools. In SQL Server 2008, IntelliSense and Upgrade Advisor are good examples. By doing so, not only Microsoft product but also Microsoft partners and vendor could take advantage of it in the future and provide such intelligent applications to customers overall.

As a part of the roadmap, the business decision was first to enable core Transact-SQL language constructs such as query and DML statement for SQL Server 2008. We still have a long way to go to accomplish our vision but we are getting there and team has multiple project charters to enable more language coverage in versions to come.

Hope this helps understand the reason why we had to restrict the version support to SQL Server 2008 only.

While I appreciate Eric's description and understand his reasoning, I'm still disappointed.  I had been using the 2008 tools against two client's SQL 2005 instances with great results.  Giving this up will be painful as I have really enjoyed the experience of IntelliSense in SSMS.

If you agree with me on this point please go to Connect and vote for my suggestion that backward compatibility be included in the feature.  Hopefully this could find it's way into a service pack or other release.

I want to thank Eric again for letting me post part of our conversation.  Eric's job of implementing IntelliSense in SQL Server is no small task.  I am happy to see this feature make the product, it already has a better feel than many of the other add-ins on the market.

Next week marks the end of mainstream support for SQL Server 2000 SP4.  I have been really focused on SQL Server 2008 and it's new features so I kept having the "oh yeah" moment when it came to end of support for this version.

I am still surprised by the number of clients I run across that are still using SQL 2000 in production (some with very little thought to post-support life).  At the same time, it seems most of those customers are skipping SQL 2005 and going straight to SQL 2008.  It will be interesting to see how this affects many business as they will have given themselves quite a learning curve.  Incorporating the changes brought about by SQL 2005 was daunting for many shops.  Trying to roll two revs worth of into an environment will certainly be a challenge.  That said, I still believe there are enough compelling features in SQL 2008 to make that jump.

If you're reading this and the thought of a migration makes you nervous contact us at Magenic.  We've been preparing for this day for a while and can help guide your business to a successful implementation.

-W

One of my favorite features in SQL Server 2005 are the dynamic management views.  I've been playing with them for quite a while now and there is a wealth of information available to you.

I've been doing work with a client looking over the performance of their database application.  Two DMV queries in particular have been helpful and I thought I would share them.  The first has to do with index fragmentation and the other with stored procedure execution.

We start with the management function (not a view I know, but bear with me) sys.dm_db_index_physical_stats.  This function returns size and fragmentation information for the data and indexes of the specified object.  The function takes inputs of database_id, object_id, index_id, partition_number, and mode.  Here's an example that will return index fragmentation at an index level for your database:

SELECT
        OBJECT_NAME(s.[object_id]) AS [tableName]
        ,i.[name] AS [indexName]
        ,s.index_type_desc AS [indexType]
        ,s.partition_number
        ,s.page_count AS [numberOfPages]
        ,s.fragment_count AS [fragmentCount]
        ,ROUND(avg_fragmentation_in_percent,2) AS [fragmentationPercent]
        ,ROUND(s.avg_fragment_size_in_pages, 2) AS [averageFragmentationInPages]
FROM 
    sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
    INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id 
WHERE 
    s.database_id = DB_ID() 
    AND i.name IS NOT NULL    --Filter HEAPs
    AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY 
    fragmentationPercent DESC;

Next we move to stored procedure workload.  The query makes use of the sys.dm_exec_query_stats view which returns a row for each query statement within the cached plan.  We also use the management function sys.dm_exec_sql_text to return the T-SQL text of the query.  If you do not use stored procedures in your database application simply remove the join to sys.procedures as well as the [objectName] reference in the query output.

DECLARE @CurrentTime DATETIME ; 

SET @CurrentTime = GETDATE() ;

-- Get executed SP's ordered by calls/sec
SELECT
    SCHEMA_NAME([schema_id]) AS [schemaName]
   ,p.[name] AS [objectName]
   ,qs.execution_count AS [executionCount]
   ,qs.total_worker_time / qs.execution_count AS [avgWorkerTime]
   ,qs.total_worker_time AS [totalWorkerTime]
   ,qs.total_elapsed_time / qs.execution_count AS [avgElapsedTime]
   ,qs.max_logical_reads
   ,qs.max_logical_writes
   ,qs.total_physical_reads
   ,qs.total_physical_reads/(qs.execution_count * 1.0) AS [avgPhysicalReads]
   ,qs.creation_time
   ,DATEDIFF(MI,qs.creation_time,@CurrentTime) AS [ageInCache]
   ,qs.execution_count / DATEDIFF(SS,qs.creation_time,@CurrentTime) AS [callsPerSecond]
   ,qs.last_execution_time
   ,SUBSTRING(qt.text,qs.statement_start_offset / 2,( 
        CASE 
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),qt.text)) * 2
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset ) / 2
        ) AS [individualQueryText]
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    INNER JOIN sys.procedures AS p ON qt.objectid = p.[object_id]
WHERE
    qt.dbid = DB_ID()
ORDER BY
    [callsPerSecond] DESC ;