Recently, I wrote a post on using Regular expressions in CHECK constraints. Based on this post, I was asked a very interesting question:

Can a computed column have a primary key constraint defined on it?

Quite frankly, I had never encountered such a situation. I did not have an answer to the question off the top of my head and I therefore requested some time for research. This week-end, I ran a small test in order to get an answer to this question. This post is the answer to the question I was asked.

Attempting to define a primary key on a non-persisted computed column

The script provided below has a computed column (non-persisted), which I am attempting to define as a primary key:

USE tempdb;
GO
IF OBJECT_ID('tempdb..#ComputedColumnAsPk') IS NOT NULL
    DROP TABLE #ComputedColumnAsPk;
GO

CREATE TABLE #ComputedColumnAsPk (RecordId INT IDENTITY(1,1),
                                  RecordValue VARCHAR(20),
                                  ComputedColumn AS ('AWC' + CAST(RecordId AS VARCHAR(10))),
                                  CONSTRAINT pk_ComputedColumnsAsPK PRIMARY KEY CLUSTERED (ComputedColumn)
                                 );
GO

Execution of the script yields the following error message:

Msg 1711, Level 16, State 1, Line 7
Cannot define PRIMARY KEY constraint on column 'ComputedColumn' in table '#ComputedColumnAsPk'. The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 7
Could not create constraint or index. See previous errors.

Changes required to define the primary key

Reading the error message in detail tells us that the primary key constraint creation failed because:

• The column was not persisted
• The column is not nullable

Now, we know that the computed column consists of a static string and the RecordId column. The RecordId is an IDENTITY column, and based on the rules for IDENTITY columns it is automatically a NOT NULL column.

All that remains therefore is to convert the non-persisted computed column to a persisted computed column.

Executing the script provided below succeeds and we can validate that by checking the primary key definition (also provided in the script below):

IF OBJECT_ID('tempdb..#ComputedColumnAsPk') IS NOT NULL
    DROP TABLE #ComputedColumnAsPk;
GO

CREATE TABLE #ComputedColumnAsPk (RecordId INT IDENTITY(1,1),
                                  RecordValue VARCHAR(20),
                                  ComputedColumn AS ('AWC' + CAST(RecordId AS VARCHAR(10))) PERSISTED,
                                  CONSTRAINT pk_ComputedColumnsAsPK PRIMARY KEY CLUSTERED (ComputedColumn)
                                 );
GO

--Using sp_help to get the table design and verify that the primary key was indeed created
sp_help #ComputedColumnAsPk
GO

--Get the Computed Column definition
SELECT scc.is_persisted,* 
FROM sys.computed_columns AS scc
WHERE scc.name = 'ComputedColumn';
GO

Conclusion

As we can see through this little experiment, it is possible to have a primary key constraint defined on a computed column provided the column is not null-able and is persisted.

I am yet to come across a scenario in the projects I handle where I would need computed column as a primary key. If you, the kind reader has come across such a situation, please let us know by your comments in the comments section below.

Until we meet next time,

Be courteous. Drive responsibly.

At work, I am both a DBA and a technical lead for a couple of tracks. It is not uncommon to have situations where I have been working on a query for sometime and am pulled into a completely different task as part of a query resolution activity. When I return back to work on my query, it is sometimes hard to remember whether a particular result set is that of the most recent changes or that of a previous change iteration.

The SQL Server Management Studio provides a quick configuration option which allows me to include the query that produced a particular result as part of the informational messages generated during the query execution. This option is aptly worded “Include the query in the result set”.

To access this option, simply navigate out to Tools –> Options –> Query Results –> Results to Grid (Or Results to Text, if one is generating output as Text).

Here’s the option in action:

After setting this option, whenever I execute a query, the query is printed in the Messages tab. As can be seen in the image below, if the Query Editor has a query different than that available in the Messages Tab, it is a direct indication that the results in the Results tab is not generated by the query in the editor.

Summary

The ability to co-relate a result set with the query that produced the result set is a great productivity booster because it reduces the time it takes me to “get back into the zone”.

I trust this tip was helpful for you. Do share with me other productivity tips that you may have.

Until we meet next time,

Be courteous. Drive responsibly.

I was recently asked a good question by one of the team members. They wanted to know if it would be possible to control the insertion of values in a table to match a given set of business rules via check constraints. What attracted my attention and prompted me to spend some time with them was the fact that they knew CHECK constraints were the answer, but were unaware about how to use CHECK constraints in this scenario.

The solution that I provided was to use regular expressions in the CHECK constraint definition. Here’s a small example that will guide the reader on using regular expressions in CHECK constraints.

The example

Assume that the fictional AdventureWorks BiCycle Company has a business rule that all invoices being processed must begin with the letters AWC followed by a 5-digit number beginning at 10000, making the pattern (AWCxyyyy, where x = some integer greater than or equal to 1, y = some integer).

We will attempt to achieve this via a simple CHECK constraint. To begin with, we will create a sample temporary table:

USE [tempdb];
GO

--Safety Check
IF OBJECT_ID('tempdb..#InvoicesProcessed') IS NOT NULL
    DROP TABLE #InvoicesProcessed;
GO

--Create a dummy table
CREATE TABLE #InvoicesProcessed
    (InvoiceNumber VARCHAR(10) NOT NULL,
     ProcessDate   DATETIME
    )
GO

--Add a simple default constraint for the ProcessDate field
ALTER TABLE tempdb..#InvoicesProcessed
    ADD CONSTRAINT df_ProcessDate DEFAULT GETDATE() FOR ProcessDate;
GO

Using Regular Expressions in a CHECK constraint definition

Now comes time to create the CHECK constraint that will actually implement the business logic. We will be using regular expressions in the constraint. To summarize, the requirements for an Invoice Number are:

• Total length = 8 characters
• 1st 3 characters must be AWC
• Next 5 characters are numbers
• The sequence of numbers must begin from 10000

The pattern therefore is (AWCxyyyy, where x = some integer greater than or equal to 1, y = some integer), which translates to the following CHECK constraint:

USE tempdb;
GO
--Now. add the CHECK constraint with the regular expressions
ALTER TABLE tempdb..#InvoicesProcessed
    ADD CONSTRAINT chk_InvoiceNumber
    CHECK (InvoiceNumber LIKE 'AWC[1-9][0-9][0-9][0-9][0-9]');
GO

The Test

Let us know test the constraint by adding some valid and some invalid values:

USE [tempdb];
GO

--Add some valid test data
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES ('AWC10021');
GO
--Add some invalid test data
--Failure Reason: 1st numeric value after AWC must be greater than 1
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES ('AWC00021');
GO

--Failure Reason: Does not start with AWC
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES ('00010021');
GO

--Failure Reason: Trailing characters are not numeric
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES ('AWC100AB');
GO

As can be seen from the messages tab, we notice that only 1 record was added to the table – the others failed because they did not meet the Invoice Number requirements.

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint “chk_InvoiceNumber”. The conflict occurred in database “tempdb”, table “dbo.#InvoicesProcessed__________________________________________________________________________________________________000000000004”, column ‘InvoiceNumber’.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint “chk_InvoiceNumber”. The conflict occurred in database “tempdb”, table “dbo.#InvoicesProcessed__________________________________________________________________________________________________000000000004”, column ‘InvoiceNumber’.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint “chk_InvoiceNumber”. The conflict occurred in database “tempdb”, table “dbo.#InvoicesProcessed__________________________________________________________________________________________________000000000004”, column ‘InvoiceNumber’.
The statement has been terminated.

Let us validate the Inserts by SELECTing data from the table.

USE tempdb;
GO

--Select from the table
SELECT ip.InvoiceNumber,
       ip.ProcessDate
FROM #InvoicesProcessed AS ip;
GO

Conclusion

Regular Expressions are extremely powerful and CHECK constraints is just one of the many Microsoft SQL Server provides areas where they can be leveraged to enforced the rules required by the business.

Further Reading:

• Temporary Table Naming – Maximum Allowed Length and associated naming logic [Link]

Until we meet next time,

Be courteous. Drive responsibly.

There is a very interesting aspect of temporary tables – in many aspects they are very different from the “regular” user tables. One such area is the constraints that temporary tables support. We have already looked at named constraints in a previous post.

Today, we will look at foreign keys on temporary tables.

The rule

Temporary tables DO NOT support foreign key constraints.

The rule above says it all – temporary tables do not support foreign key constraints. If we explicitly attempt to define a foreign key constraint on a temporary table, we receive the following message:

Skipping FOREIGN KEY constraint ‘fk_temployeeList_HREmployee’ definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

To validate this, I wrote the following script. The script creates a local temporary table with a primary key defined on it. I later go on to define a foreign key constraint with the HumanResources.Employee table in the AdventureWorks database.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('tempdb..#employeeList','U') IS NOT NULL
DROP TABLE #employeeList;
GO

CREATE TABLE #employeeList 
    (ListId           INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
     BusinessEntityID INT     NULL,
    );

--FK creation
ALTER TABLE #employeeList
ADD CONSTRAINT fk_temployeeList_HREmployee 
    FOREIGN KEY (BusinessEntityID) 
    REFERENCES HumanResources.Employee(BusinessEntityID)
GO

/**********
RESULTS 01
**********/
/*
Skipping FOREIGN KEY constraint 'fk_temployeeList_HREmployee' definition for temporary table.
FOREIGN KEY constraints are not enforced on local or global temporary tables.
*/

The following script also helps us validate that the primary key was created, but the foreign key was not created.

-- Step 02: Confirm that the primary key was created, but the foreign key was not
IF OBJECT_ID ('tempdb..#employeeList','U') IS NOT NULL
BEGIN
    SELECT skc.name AS PrimaryKeyConstraintName
    FROM tempdb.sys.key_constraints AS skc
    WHERE skc.parent_object_id = OBJECT_ID ('tempdb..#employeeList','U')
      AND skc.type = 'PK'
      AND skc.is_ms_shipped = 0;

    SELECT psc.name                               AS ParentColumnName,
           OBJECT_NAME(sfkc.referenced_object_id) AS ReferencedTable,
           rsc.name                               AS ReferencedColumnName
    FROM tempdb.sys.foreign_key_columns AS sfkc
    INNER JOIN tempdb.sys.columns AS psc ON sfkc.parent_column_id = psc.column_id
                                        AND sfkc.parent_object_id = psc.object_id
    INNER JOIN tempdb.sys.columns AS rsc ON sfkc.referenced_column_id = rsc.column_id
                                        AND sfkc.referenced_object_id = rsc.object_id
    WHERE sfkc.parent_object_id = OBJECT_ID('#employeeList');
END
GO

/**********
RESULTS
**********/
/*
PrimaryKeyConstraintName
-------------------------------
PK__#employe__E38328055A6D792F

ParentColumnName  ReferencedTable  ReferencedColumnName
----------------- ---------------- ---------------------

*/

Hope that you found this post interesting. Do let me know your feedback in the comments area below before leaving.

Until we meet next time,

Be courteous. Drive responsibly.

I was recently developing a stored procedure and ended up with an strange error during unit testing – the stored procedure failed whenever two connections executed the stored procedure while the other connection was active.

I was able to attribute the error to an oversight I had made with respect to temporary table handling and that incident triggered some reading and the posts this week.

The Error

Here is a gist of what my temporary table creation script looked like:

USE tempdb;
GO
IF OBJECT_ID ('tempdb..#tableWithNamedPK','U') IS NOT NULL
    DROP TABLE #tableWithNamedPK;
GO

CREATE TABLE #tableWithNamedPK 
    (RecId    INT,
     RecValue VARCHAR(20),
     CONSTRAINT pk_tableWithNamedPK PRIMARY KEY CLUSTERED (RecId)
    );
GO

Whenever I run the same script from a different SSMS window (thereby simulating two or more users executing the script through the application), I ended up with the following error:

Msg 2714, Level 16, State 5, Line 2
There is already an object named ‘pk_tableWithNamedPK’ in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

The Explanation

The explanation is actually quite simple – when local temporary tables are created, there is a different instance of the temporary table created in the tempdb (Refer my post on the temporary table naming convention  for a detailed explanation). If we define named constraints, all these tables will have the same named constraint, which is not valid!

In my case the named constraints defined on my temporary tables ensured that only one user connection at a time can execute the statement that creates the temporary table.

Not restricted to primary keys

This thumb-rule is not restricted to primary keys alone. This is applicable to any named constraint. To demonstrate this in this post, I wrote this little script:

USE tempdb;
GO
IF OBJECT_ID ('tempdb..#tableWithNamedUK','U') IS NOT NULL
    DROP TABLE #tableWithNamedUK;
GO

CREATE TABLE #tableWithNamedUK 
    (UniqueExternalSystemId INT,
     RecValue               VARCHAR(20),
     CONSTRAINT uk_tableWithNamedUK_ExternalSystemId
     UNIQUE NONCLUSTERED (UniqueExternalSystemId)
    );
GO

To test it out, open two SSMS query editor windows. You can execute this in SSMS Query editor window #1 and #2, and the following error will be seen for query editor window #2.

Msg 2714, Level 16, State 5, Line 2
There is already an object named ‘uk_tableWithNamedUK_ExternalSystemId’ in the database.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

The Solution

The question therefore comes is: How can one define constraints on temporary tables?

The devil is in the details and if we re-read the thumb-rule highlighted above, we realize that the restriction is not on the definition of the constraint – it’s on the definition of named constraints.

The following script therefore will work in multiple concurrent connections. The script also contains a code snippet to identify the name of the system generated constraint names.

USE tempdb;
GO
IF OBJECT_ID ('tempdb..#tableWithNamedPK','U') IS NOT NULL
    DROP TABLE #tableWithNamedPK;
GO

CREATE TABLE #tableWithNamedPK 
    (RecId    INT,
     RecValue VARCHAR(20),
     PRIMARY KEY CLUSTERED (RecId)
    );
GO

USE tempdb;
GO
IF OBJECT_ID ('tempdb..#tableWithNamedUK','U') IS NOT NULL
    DROP TABLE #tableWithNamedUK;
GO

CREATE TABLE #tableWithNamedUK 
    (UniqueExternalSystemId INT,
     RecValue               VARCHAR(20),
     UNIQUE NONCLUSTERED (UniqueExternalSystemId)
    );
GO

--Check the primary key names
SELECT skc.name AS ConstraintName,
       skc.type_desc AS ConstraintType
FROM tempdb.sys.key_constraints AS skc
WHERE ( skc.parent_object_id = OBJECT_ID ('tempdb..#tableWithNamedPK','U')
        OR
        skc.parent_object_id = OBJECT_ID ('tempdb..#tableWithNamedUK','U')
      )
      AND 
      ( skc.type = 'PK' OR skc.type = 'UQ' )
  AND skc.is_ms_shipped = 0;
GO

/*********************
RESULTS 03
*********************/
/*
ConstraintName                  ConstraintType
------------------------------- -----------------------
PK__#tableWi__360414DF6D0EF7FB  PRIMARY_KEY_CONSTRAINT
UQ__#tableWi__D9712AD5EC6B160E  UNIQUE_CONSTRAINT
*/

Please do let me know your feedback, and have a great time working with temporary tables today!

Until we meet next time,

Be courteous. Drive responsibly.