I like the SQL Cop framework and have written about how I've incorporated it into the SQLServerCentral database. I've removed some checks, but I didn't want to remove the Primary Key (PK) check, as I think that's important. However, there is the possibility that I might not want a PK on a table, so I'd like to have a way to avoid this as a rule in development.

I also want all my tests to still pass before I commit code to my VCS. That's important as a developer. I should be sure as I create or refactor code, that my changes meet the standards that for my environment. Fortunately I found a way to cleanly incorporate exceptions in my tests, and this article explains how.

Clean Exceptions

There aren't many ways to add information to a table in SQL Server, but there is one area that works. I didn't come up with this idea myself, and credit goes to John McLusky, who actually mentioned this technique in a talk at SQL Bits IV. John noted that his team decided to use Extended Properties to store a value that documents a particular object as having an exception to a rule. I liked that technique and decided to adopt it. I chose an extended property on a table to indicate if a PK is not needed. Here is my Extended Property

• Name: PKException
• Value: 1 = no PK needed, 0 (or nonexistent) = PK required

This property can easily be added and updated to a table using the sp_addextendedproperty and sp_updateextendedproperty system stored procedures. Let's do that with a few pieces of code.

Below I'm going to create two new tables without Primary Keys. One will have the Extended Property added, and one will not.

CREATE TABLE PKCheckTable1
(
     id int
);
GO
CREATE TABLE PKCheckTable2
(
     id int
);
GO
EXEC sp_addextendedproperty
@name = N'PKException',
@value = '1',
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table',   @level1name = 'PKCheckTable2'
;
GO

I can easily look at the table properties in SSMS for PKCheckTable2 and see the value I've added in the Extended Properties page (shown below).

There is no extended property for PKCheckTable1, but that's OK. We'll use that to see if our SQLCop test works. 

Modifying SQLCop

This is a check for tables wthout a primary key in the SQL Cop framework. Here is the original code from the SQLCop site for that test:

ALTER PROCEDURE [SQLCop].[test Tables without a primary key]
AS
BEGIN
-- Written by George Mastros
-- February 25, 2012
-- http://sqlcop.lessthandot.com
-- http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/best-practice-every-table-should-have-a

SET NOCOUNT ON

DECLARE @Output VarChar(max)
SET @Output = ''
SELECT@Output = @Output + su.name + '.' + AllTables.Name + Char(13) + Char(10)
FROM(
SELECTName, id, uid
Fromsysobjects
WHERExtype = 'U'
) AS AllTables
INNER JOIN sysusers su
On AllTables.uid = su.uid
LEFT JOIN (
SELECT parent_obj
From sysobjects
WHERE  xtype = 'PK'
) AS PrimaryKeys
ON AllTables.id = PrimaryKeys.parent_obj
WHEREPrimaryKeys.parent_obj Is Null
AND su.name <> 'tSQLt'
ORDER BY su.name,AllTables.Name
If @Output > '' 
Begin
Set @Output = Char(13) + Char(10) 
  + 'For more information:  '
  + 'http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/best-practice-every-table-should-have-a' 
  + Char(13) + Char(10) 
  + Char(13) + Char(10) 
  + @Output
EXEC tSQLt.Fail @Output
End
END;

However, this code doesn't allow for exceptions. When I look at it, I see that the check accesses the sysobjects table for both the base tables and PKs. Here's the relevant part of the code:

Select AllTables.Name
From  (
      Select Name, id
      From  sysobjects
      Where  xtype = 'U'
      ) As AllTables
      Left Join (
        Select parent_obj
        From  sysobjects
        Where  xtype = 'PK'
        ) As PrimaryKeys
        On AllTables.id = PrimaryKeys.parent_obj
Where  PrimaryKeys.Parent_Obj Is NULL
Order By AllTables.Name

However sysobjects doesn't have all the information I need for querying extended events. If I look at sys.objects, however, I do find the schema in there, which is important. I don't want to check the tSQLt objects, and I want to ensure that if I'm checking an extended property, I'm doing so in the correct schema.

I can easily add sys.extended properties to the first derived table, but I need to do so as a LEFT OUTER join. Most of my tables won't have the extended property added to them and I don't want to force this on developers. I can also add the extended property to the column list and use the COALESCE function to return a 0 for those tables that don't have the extended property.

My new code looks like this for the first derived table.

SELECT    o .name ,
                    o .object_id AS id ,
                    COALESCE (e .value , 0 ) AS 'PKException'
          FROM      sys. objects o
                    INNER JOIN sys. schemas s ON s. schema_id = o .schema_id
                    LEFT OUTER JOIN sys .extended_properties e ON o. object_id = e .major_id
                                                              AND e. class = 1
                                                              AND e. class_desc = 'OBJECT_OR_COLUMN'
                                                              AND e. name = 'PKException'
          WHERE     o .type = 'U'
                    AND s .name <> 'tsqlt'

The second derived table can stay the same, but I want to then add the filter to the WHERE clause that returns a table if there is no PK, and there is not an Extended Property. This filter is

AND AllTables.PKException = 0

For my SQL Cop test, I set a variable to the output of this query. Ideally nothing is returned, which means that all tables have PKs or have an Extended Property to create an exception. Here's my final SQL Cop test code:

ALTER PROCEDURE [SQLCop]. [test Tables without a primary key]
AS
BEGIN
-- Assemble
DECLARE @output nvarchar(max)
, @tables nvarchar(4000);
-- act
SELECT @tables = COALESCE (@tables + ', ', '' ) + AllTables.name
  FROM    ( SELECT    o .name ,
                    o .object_id AS id ,
                    COALESCE( e. value, 0) AS 'PKException'
          FROM      sys. objects o
                    INNER JOIN sys. schemas s ON s. schema_id = o .schema_id
                    LEFT OUTER JOIN sys .extended_properties e ON o. object_id = e .major_id
                                                              AND e. class = 1
                                                              AND e. class_desc = 'OBJECT_OR_COLUMN'
                                                              AND e. name = 'PKException'
          WHERE     o .type = 'U'
                    AND s .name <> 'tsqlt'
        ) AS AllTables
        LEFT JOIN ( SELECT  parent_object_id
                    FROM    sys. objects
                    WHERE   type = 'PK'
                  ) AS PrimaryKeys ON AllTables .id = PrimaryKeys. parent_object_id
WHERE    PrimaryKeys. parent_object_id IS NULL
        AND AllTables .PKException = 0
ORDER BY AllTables. name;
-- assert
select @output = 'These tables need a PRIMARY key:' + @tables;
EXEC tsqlt. AssertEquals @Expected = '', @Actual = @tables, @Message = @output
END

When I execute this in SQL Test for the original tables I created at the top, the test fails. This is because I have two tables with no PK, but only one has an Extended Property with a 1 for PKException.

When I add an exception for the second table with this code:

EXEC sp_addextendedproperty
  @name = N'PKException',
  @value = '1',
  @level0type = N'Schema', @level0name = 'dbo',
  @level1type = N'Table',   @level1name = 'PKCheckTable1'
;

I can then run the test and everything passes. That's good. If I run this with T-SQL, I get use this code

EXEC tsqlt. Run @TestName = N'[SQLCop].[test Tables without a Primary Key]';

I then get these results:

  (1 row(s) affected)
  +----------------------+
  |Test Execution Summary|
  +----------------------+
  |No|Test Case Name                              |Dur(ms)|Result |
  +--+--------------------------------------------+-------+-------+
  |1 |[SQLCop].[test Tables without a primary key]|     20|Success|
  -----------------------------------------------------------------------------
  Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
  -----------------------------------------------------------------------------

Now, I need to be sure I can turn this on and off. If I set the value of the extended property to 0 for one of the tables, which should remove the exception, I expect that the test fails. However I get this:

+----------------------+
|Test Execution Summary|
+----------------------+
|No|Test Case Name                              |Result |
+--+--------------------------------------------+-------+
|1 |[SQLCop].[test Tables without a primary key]|Success|
-----------------------------------------------------------------------------
Test Case Summary: 1 test case(s) executed, 1 succeeded, 0 failed, 0 errored.
-----------------------------------------------------------------------------

That's not good. Apparently I have some logic error. I wasn't sure what was wrong here, but a little experimenting helped me solve the issue (I think).

I knew that the value column in sys.extended_properties was a SQL_Variant. I had a clue at one point in my experimentation with an implicit conversion error between a varchar(max) and sql_variant. I dislike sql_variant, but I don't get to redesign the system here. Instead, I added a CAST in my SQL Cop query to take the value and return it as an integer. It's this line:

CAST( COALESCE (e .value , 0 ) AS INT) AS 'PKException'

Now when I run the test, it fails, as expected. If I use sp_updateextendedproperty to switch the property back and forth, add new tables without an Extended Property, everything works.

Conclusion

This is a great solution (thanks to John McLusky) that allows me to set a rule, but allow exceptions. Developers can learn to include a PK on their tables with this check, but also consciously include an exception,  easily. They can even use a snippet in SQL Prompt to quickly pop up the code to add the exception for a PK.

I've rarely encountered a rule in databases that doesn't require exceptions to some type of standard. Having an easy way to provide these exceptions for static analysis tests (like SQL Cop) is essential. Hopefully this article shows you a way you can use SQL Cop, or any tSQLt code, to implement those exceptions for the rules and tests that you create.