Surrogate / Natural / Composite Primary Keys

I have heard several arguments both for and against the composite primary keys. First, let's understand the type of primary keys that a table may have...

• Surrogate Key - This is non essential data element that simply represent a record in the table but is not part of the business data. For example, an auto-generated identity key or a GUID.

• Natural Key - One or more data elements that are part of the data and is defined as a primary key. For example - ItemNumber or ItemDescription in an InventoryItems table.

• Composite Key - When two or more keys are joined to form the primary key. It generally is a combination of two or more natural keys.

This is not cast in stone, but most developers would prefer a surrogate key such as an identity column as a primary key which makes it easier to join multiple tables for querying purpose etc. It also makes it easier if you are using code generators such as nHibernate to generate your data access layer. But, if you ask a DBA, you may get a different answer. They much rather see natural key as a primary key, even if you have to use composite key to define it.

Personally, I like having an auto generated identity column as a primary key in base tables such as Employees with an EmployeeID as a primary key and Stores table with StoreID.

For linking tables, I much rather use a composite key instead of an identity column. For example, if I have EmployeeStores table which links Employees and Stores, instead of having an identity column EmployeeStoreID, I rather have EmployeeID and StoreID as a composite primary key. You may be wondering why not use an identity key instead?

Let's assume your application has a constraint i.e. an Employee can only be assigned to one store. If you have EmployeeStoreID as your primary key, the following records are perfectly legal...

EmployeeStoreID	EmployeeID	StoreID
1	100	20
2	100	30

This doesn't satisfy business constraint, does it? Sure, your application should enforce this but what if someone enters data directly? You can also enforce this by creating a unique constraint on EmployeeID and StoreID, but to me that is another layer of complexity. If I made EmployeeID and StoreID as a composite primary key, I wouldn't have this problem.

Linking tables like these are generally not involved in complex join operations so having a composite key shouldn't force you to write more complex queries. Code generators that I am aware of or have worked with also support composite primary keys.

There is still one issue though that I haven't addressed yet. What if you want to update a record, how do you determine which record to update?

For example, assume an employee can be assigned to multiple stores. consider the following table...

EmployeeID	StoreID
100	20
100	30
110	30
120	40

Employee 100 is assigned to stores 20 and 30. Now imagine this employee no longer works in store 20 but instead now works in store 40. The manager now updates this employees record via UI and changes his/her store from 20 to 40.

How would you know which record to update? Do you update the 1st record (correct one) or do you update second record (storeID=30) since your stored procedure likely received EmployeeID=100 and StoreID=40?

There are couple of ways to get around this problem.

• You can have your application send the stored procedure both old store ID and new store ID and you would know which record to update.
• You can have the EmployeeStoreID as an auto increment column and your application can pass this ID to your stored procedure to update the record.

Personally, I prefer option #1. If you are using typed data sets or even data access layer using code generators, you will have access to row state which will give you the original and updated values that you can pass to your stored procedure. Alternatively, you can code in a way to keep track of old value and pass it along with the new value.

I generally don't post as a programmer on this blog, I have another blog for that, but here I felt it necessary to discuss a bit about programming. I hope you've enjoyed reading this as much as I've enjoyed writing it. Your comments are appreciated.

Thank you

Using Temp Tables across Stored Procedures

I often come across a situation where it is hugely beneficial to create a temporary table, populate it with necessary information and then have another stored procedure use this temporary table.

In the past, developers were mostly relegated to one of the following scenarios...

Option 1

• Create a temporary table in your database.
• Call the stored procedure which can use this table just like any other table.

But, you must delete this table after you are done, otherwise next execution may result in an error (if you are creating table without checking whether it already exists).

Option 2

• Create a temporary table with # prefix 
• Call the stored procedure which can use this table just like any other table.

Table created with # or ## prefix is only available for that session. As long as you did everything within the same connection, you are good. Once you close the connection, the table will be automatically deleted.

This works, however creating temporary tables is generally not recommended because every time a temporary table is created, stored procedure must be recompiled and query execution plan must be generated. Moreover, if your stored procedure is used in a multi-user environment, a copy of temporary table will be created for every user - (you can avoid this by creating a global temp table (with ## prefix), but then you must account for the fact that several users may share the same data which may not be desirable). 

Table variables introduced in SQL 2005 solve this problem to a great extent although there may be some performance issues because you can't create non-clustered indexes on table variables. Further, you can't use table variables created in one stored procedure in another because they will be out of scope.

Creating Temp Table

CREATE TABLE #tmpTable(ID INT, Name varchar(100))

Creating Table Variable

DECLARE @tmpTable TABLE(ID INT, Name varchar(100))

As I mentioned earlier, you can't create a table variable in one stored procedure and use it in another. SQL Server 2008 introduced a Table variable type that you can use to pass a table variable from one stored procedure to another.

Create a Table Data Type

/****** Object:  UserDefinedTableType [dbo].[TableType]    Script Date: 05/17/2012 23:07:07 ******/
CREATE TYPE [dbo].[TableType] AS TABLE(
    [CustomerID] [int] NOT NULL,
    [Name] [varchar](255) NULL,
    [City] [varchar](50) NULL,
    [State] [char](2) NULL,
    [Zipcode] [varchar](20) NULL
)
GO

Create a Stored Procedure that declares a variable of this type - Here I am declaring @Customers variable and populating it with data.

ALTER PROCEDURE sp_GetCustomers

AS
BEGIN
    DECLARE @Customers AS TableType
    INSERT INTO @Customers(CustomerID,Name,City,State,ZipCode)
    SELECT C.CustomerID,C.FirstName + ' ' + C.LastName,A.City,A.State,A.ZipCode
    FROM Customers C INNER JOIN CustomerAddress A ON C.CustomerID=A.CustomerID
    EXEC sp_GetCustomersWithAddresses @Customers
    
END

Pass this variable to another Stored Procedure - You must pass a table type variable as READONLY

CREATE PROCEDURE sp_GetCustomersWithAddresses
@Customers TableType READONLY
AS
BEGIN
    SELECT * FROM @Customers
END

You can use table type variable in your applications as well and pass it around like a regular SQL data type.

Thank you.

Disconnect Users from Database

Recently I came across a situation where I wanted to take a database offline, but someone at work was connected to this database and had left the work. I could have rebooted his machine to disconnect the user but I didn't want to do that. So, I used the power of TSQL to disconnect this user and any others users connected to this database.

USE MASTER
GO

DECLARE @DBName varchar(50), @spid varchar(10)

SET @DBName= 'Orders'

WHILE(EXISTS(SELECT * FROM sysprocesses WHERE dbid= db_id('Orders')))
    BEGIN
        DECLARE spids CURSOR FAST_FORWARD FOR
               SELECT convert(varchar, spid) FROM sysprocesses
                       WHERE dbid = db_id(@DBName)
        OPEN spids
        FETCH NEXT FROM spids INTO @spid
        WHILE @@FETCH_STATUS = 0
        BEGIN
               EXEC('kill ' + @spid)
               FETCH NEXT FROM spids INTO @spid
        END
        CLOSE spids
        DEALLOCATE spids
   END
IF NOT exists(Select * FROM sysprocesses WHERE dbid = db_id(@DBName))
        EXEC sp_dboption @DBName, offline, true
ELSE
    BEGIN
        PRINT 'The following processes are still using the database:'
        SELECT spid, cmd, status, last_batch, open_tran, program_name, hostname
        FROM sysprocesses
        WHERE dbid = db_id(@DBName)
     END

GO

This is one of many useful queries in my arsenal that I use from time to time to get the job done. You may find it useful.

Thank you.

SQL Server - Grant Permission to Objects

Suppose you are using integrated security to login to SQL Management Studio and create stored procedures, views etc. But in production environment, the application connects to SQL Server via a SQL Server account that belongs to public role.

Unless you have assigned execute permissions to Public security group (server role), you will get a nasty permission denied error message. While manually assigning proper security rights to a single stored procedure isn't a problem, it becomes a hassle when you have several stored procedures to take care of.

This is a simple script that will recursively scan all your stored procedures and grant execute permissions to public.

--Grant permissions on Procedures
DECLARE curProcedures CURSOR FOR
    SELECT Name FROM sysobjects where type='P'
OPEN curProcedures
DECLARE @Procedure varchar(100)
DECLARE @strSQL nvarchar(200)
FETCH NEXT FROM curProcedures INTO @Procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL = 'GRANT EXECUTE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    FETCH NEXT FROM curProcedures INTO @Procedure
END
CLOSE curProcedures
DEALLOCATE curProcedures

You can grant SELECT/UPDATE/DELETE permissions on tables and views

Tables

--Grant permissions on Tables
DECLARE curProcedures CURSOR FOR
    SELECT Name FROM sysobjects where type='U'
OPEN curProcedures
DECLARE @Procedure varchar(100)
DECLARE @strSQL nvarchar(200)
FETCH NEXT FROM curProcedures INTO @Procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL = 'GRANT SELECT ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT UPDATE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT DELETE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    FETCH NEXT FROM curProcedures INTO @Procedure
END
CLOSE curProcedures
DEALLOCATE curProcedures

Views

--Grant permissions on Views
DECLARE curProcedures CURSOR FOR
    SELECT Name FROM sysobjects where type='V'
OPEN curProcedures
DECLARE @Procedure varchar(100)
DECLARE @strSQL nvarchar(200)
FETCH NEXT FROM curProcedures INTO @Procedure
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @strSQL = 'GRANT SELECT ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT UPDATE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    SET @strSQL = 'GRANT DELETE ON ' + @Procedure + ' TO PUBLIC'
    --PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL
    FETCH NEXT FROM curProcedures INTO @Procedure
END
CLOSE curProcedures
DEALLOCATE curProcedures

Thank You.

SQL Server - Truncate Table

The other day I ran into an issue where a SQL table was used as a staging area. Application would insert data into this table and then a user will review the contents of this table via the application, make any corrections and then click on "Verified". When Verified was clicked, the application will execute a stored procedure which will copy data from this table to a permanent table and then delete the records from the staging table. Depending on the number of records, the delete could take a while. Further, since the table had an identity key which was defined as SMALL INT, the table was frequently running out of ID values and had to be reseeded to start all over.

The fix was rather simple. Since this was a stand alone table and the data was deleted after it was successfully copied over to another table, the stored procedure could have just truncated the table instead of deleting the records. Truncation would have been faster and it would reset the table every time, resulting in identity key starting from initial value (1 in this case).

What if you had a table that you want to truncate every now and then but it also has foreign key constraints with other tables. Obviously, first you will have to remove the constraints, truncate the table and add the constraints back. This could get cumbersome rather quickly. Fortunately, you could create a generic stored procedure which will accept the table name, figure out any constraints on this table, drop them, truncate the table and recreate the constraints. It can then be used to truncate any other table (although you better be careful, because you may end up truncating live data tables).

Here is a stored procedure that I have used in the past.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC [dbo].[sp_TruncateTable]
    @TableName    varchar(255)

AS

SET NOCOUNT ON

DECLARE @strSQL    nvarchar(MAX)

DECLARE @CheckForeignKeys TABLE
(
    Name            varchar(100),
    ParentColumn    varchar(100),
    ChildTable        varchar(100),
    ChildColumn        varchar(100))

--    Get Foreign Key info
INSERT INTO @CheckForeignKeys
SELECT
    FK.Name,
    PC.Name,
    CT.Name,
    CC.Name

FROM SYS.TABLES T
    INNER JOIN SYS.FOREIGN_KEYS FK
        ON FK.REFERENCED_OBJECT_ID = T.OBJECT_ID
    INNER JOIN SYS.TABLES CT
        ON CT.OBJECT_ID = FK.PARENT_OBJECT_ID
    INNER JOIN SYS.FOREIGN_KEY_COLUMNS FKC
        ON FKC.CONSTRAINT_OBJECT_ID = FK.OBJECT_ID
    INNER JOIN SYS.COLUMNS PC
        ON PC.OBJECT_ID = FKC.REFERENCED_OBJECT_ID
        AND PC.COLUMN_ID = FKC.REFERENCED_COLUMN_ID
    INNER JOIN SYS.COLUMNS CC
        ON CC.OBJECT_ID = FKC.PARENT_OBJECT_ID
        AND CC.COLUMN_ID = FKC.PARENT_COLUMN_ID

WHERE T.Name = @TableName

SET NOCOUNT OFF

--    Drop foreign keys
PRINT 'Dropping Foreign Keys'

DECLARE DropConstraints CURSOR FAST_FORWARD FOR
    SELECT 'ALTER TABLE ' + FK.ChildTable + ' DROP CONSTRAINT ' + FK.Name
    FROM @CheckForeignKeys FK
    
OPEN DropConstraints
FETCH NEXT FROM DropConstraints INTO @strSQL
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executeSQL @strSQL
    PRINT @strSQL
    FETCH NEXT FROM DropConstraints INTO @strSQL
END

CLOSE DropConstraints
DEALLOCATE DropConstraints

--    Truncate table
BEGIN TRY

    SELECT @strSQL = 'TRUNCATE TABLE ' + @TableName
    PRINT @strSQL
    EXEC sp_executeSQL @strSQL

END TRY
BEGIN CATCH
    PRINT 'ERROR OCCURRED -- ' + @@Error
END CATCH


--Add constraints
PRINT 'Adding Constraints'
DECLARE AddConstraints CURSOR FAST_FORWARD FOR

SELECT 'ALTER TABLE ' + FK.ChildTable + ' WITH NOCHECK ADD CONSTRAINT ' + FK.Name + 
       ' FOREIGN KEY (' + FK.ChildColumn + ') REFERENCES ' + @TableName + '(' + FK.ParentColumn + ')
ALTER TABLE ' + FK.ChildTable + ' CHECK CONSTRAINT ' + FK.Name

    FROM @CheckForeignKeys FK

OPEN AddConstraints

FETCH NEXT FROM AddConstraints INTO @strSQL
WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC sp_executeSQL @strSQL
    PRINT @strSQL
    FETCH NEXT FROM AddConstraints INTO @strSQL

END

CLOSE AddConstraints
DEALLOCATE AddConstraints
PRINT 'DONE!'
GO

If I execute this stored procedure, passing the table name I want to truncate, here is the message I get...

EXEC sp_TruncateTable 'Customers_Audit'


And here is the output...

Dropping Foreign Keys
ALTER TABLE Customers_Audit2 DROP CONSTRAINT FK_Customers_Audit2_Customers_Audit
TRUNCATE TABLE Customers_Audit
Adding Constraints
ALTER TABLE Customers_Audit2 WITH NOCHECK ADD CONSTRAINT FK_Customers_Audit2_Customers_Audit FOREIGN KEY (CustomerID) REFERENCES Customers_Audit(CustomerID)
ALTER TABLE Customers_Audit2 CHECK CONSTRAINT FK_Customers_Audit2_Customers_Audit
DONE!

Keep in mind that sp_executeSQL expects unicode parameter that's why I had to declare @strSQL as nvarchar(MAX).

Thank You.