StandardCDC Updated

This morning I released a new version 1.4 to CodePlex. The release contains bug fixes for stored procedures.

Go grab the new release and take it for a spin!


Posted by: whitneyw
Posted on: 8/24/2011 at 11:14 AM
Tags: , ,
Categories: Transact SQL
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

Don’t Forget OBJECTPROPERTY

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:

image

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

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


Posted by: whitneyw
Posted on: 8/10/2010 at 11:15 AM
Tags: , ,
Categories: SQL Server | Transact SQL
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

Partition Level Locking SQL CAT Post

A little late with this one but the SQL CAT team has a great blog post about partition lock escalation in SQL Server 2008. 

The post describes the introduction of table level lock escalation control made available via TSQL syntax.  In prior versions a trace flag was used to alter lock escalation.

A great feature added to an already great feature.  Enjoy!


Posted by: whitneyw
Posted on: 3/11/2010 at 1:22 PM
Tags:
Categories: SQL Server | Transact SQL
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

Do you use batch terminators in your TSQL?

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!


Posted by: whitneyw
Posted on: 9/3/2009 at 2:57 PM
Tags: ,
Categories: SQL Server | Transact SQL
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

SQL Server Best Practices ? a reminder

I tend to get pulled into a lot of discussions around best practices in regards to SQL Server.  I am always a little shocked (and saddened) that so few people seem to be aware of the SQL Server Best Practices site.  This site is loaded with white papers and other content from various Microsoft groups and MVPs.

Do yourself a favor and head on over there once you have read whatever you were looking for on this blog.  No rush though, take your time.  ;)


Posted by: whitneyw
Posted on: 8/11/2009 at 11:08 AM
Tags:
Categories: SQL Server
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

AtlantaMDF Slides and Code Samples

As promised, here is the Powerpoint and code samples from tonight's AtlantaMDF meeting.  Thanks to everyone who came out and for all the great questions!

 

AtlantaMDF_Aug10_CDC.zip (1.31 mb)


Posted by: whitneyw
Posted on: 8/10/2009 at 11:02 PM
Tags:
Categories: SQL Server | User Groups
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

AtlantaMDF - August 10

I will be speaking to the AtlantaMDF group about one of my favorite topics, Change Data Capture,  on Monday August 10th.  If you will/can be near the Microsoft office in Alpharetta swing on by.


Posted by: whitneyw
Posted on: 8/2/2009 at 10:46 PM
Tags: ,
Categories: SQL Server | User Groups
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

Transact SQL To Find Missing Foreign Keys

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.


Posted by: whitneyw
Posted on: 7/15/2009 at 12:44 PM
Tags: ,
Categories: SQL Server | Transact SQL | SQLServerPedia Syndication
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

Change Data Capture on Standard Edition

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!


Posted by: whitneyw
Posted on: 7/10/2009 at 12:31 AM
Tags: , ,
Categories: BlogEngine.NET
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (1) | Post RSSRSS comment feed

Do you like reporting? And do you like Silverlight?

If the answer to those two questions is yes, then I have just the webinar for you.  My colleague, and all-around great guy, Sergey Barskiy will be hosting a webinar on reporting in Silverlight.  See his blog here for the details.  You’re sure to learn something with Sergey at the wheel.


Posted by: whitneyw
Posted on: 7/9/2009 at 9:46 PM
Tags: , ,
Categories: BlogEngine.NET
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed