I am sure that many of my readers will be able to co-relate with the situation in today’s post.

We recently faced a situation at the office where one of our application servers was unable to communicate to the database server – the servers could “ping” each other just fine, but the SQL Server was inaccessible. While there were indications of connectivity being an issue, we did not have any definitive proof. The IT administrator continued to maintain that the server has been configured in the same way as all our other application servers were. We were therefore required to come up with a mechanism to test connectivity from the application server to the database server without the use of SSMS, SQLCMD (being the application server, it didn’t have any SQL client components installed) or any other applications. We had to use something that is shipped with the operating system.

Universal Data Link (UDL) file

Creation and Basic Configuration

The Microsoft Data Access Components (MDAC) provide the Data Link Properties dialog box as the common user interface for specifying connection information to a data provider on Windows 2000 and later operating systems. One can use the Data Link Properties dialog box to save connection information in a universal data link (.udl) file. We will be using this *.udl file to configure the most common connection string parameters and then help us test the connectivity to our database server.

1. On the desktop, or any other folder, right-click and create a new Text document

2. Rename the newly created file to “Test Database Connectivity.udl” (you can have the file name of your choice, the extension has to be .udl)

3. Double-click the file to open the “Data Link Properties” dialog box

4. In the “Provider” tab, choose the appropriate provider in use by the application

5. Provide the connection information in the “Connection” tab

6. You can define the connection timeout value in the “Advanced” tab

7. Other connection properties can be defined in the “All” tab

8. Click “OK” to save the data link information into the UDL file

Advanced Editing

Under the covers, the UDL file is nothing but a connection string stored in plain text. For seasoned administrators, the UDL file can therefore be opened in NOTEPAD where advanced connection string parameters can be defined by simply editing the connection string.

Shown below is the information from the UDL file we just defined above:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID=””;Initial Catalog=AdventureWorks2012;Data Source=W8SQLSERVERSQL2K12;Application Name=My Test Application;Initial File Name=””;Server SPN=””

Testing connectivity using the UDL file

Once the connection string definition in the UDL file has been finalized, one needs to double-click on the UDL file in the Windows explorer again to launch the “Data Link Properties” window.

To test the connection, we need to navigate out to the “Connection” tab and simply click “Test Connection” – If the connection succeeds, the underlying hardware, firewall and other configuration is as required.

Summary

The UDL file method of testing connection strings is not new. Yet, it is often forgotten as being one of the most easiest ways to test SQL Server connectivity. I would like to hear from you regarding the methods you use for testing connection strings when no SSMS, SQLCMD or other tools/applications are available at your disposal.

Further Reading:

• Testing Connection Strings using SSMS [Link]

Until we meet next time,

Be courteous. Drive responsibly.

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.