SQL Server does support regular expressions in check constraints, you don't always need triggers

Someone posted the following question

I need to add table called group with a column called code How do I add a check constraint to the column so it will only allow the following alphabetic characters (D, M, O, P or T) followed by 2 numeric characters.

Someone posted the following answer

You cannot do this out of the box - MS SQL Server does support CHECK CONSTRAINTS - but for things like a maximum or minimum INT value, or a string length or such. What you're looking for would be a CHECK based on a regular expression - and out of the box, SQL Server does not offer that capability. You could theoretically write a .NET assembly, deploy it inside SQL Server, and then use it to enforce the CHECK - not a trivial undertaking.

While SQL server does not support a full implementation of regular expression, you can do what the person asked for without a problem in T-SQL. Here is what the regular expression looks like

[DMOPT][0-9][0-9]

A constraint like that will allow allow the following alphabetic characters (D, M, O, P or T) followed by 2 numeric characters. Enough talking let's look at some code, first create this table

CREATE TABLE blatest(code char(3))

Now add the check constraint

ALTER TABLE blatest ADD  CONSTRAINT ck_bla 
CHECK (code like '[DMOPT][0-9][0-9]' )
GO

Now we can run some tests by inserting some data

INSERT blatest VALUES('a12') --fails
INSERT blatest VALUES('M12')  --good
INSERT blatest VALUES('D12') --good
INSERT blatest VALUES('DA1') --fails

As you can see we got the following message twice

Server: Msg 547, Level 16, State 1, Line 1 The INSERT statement conflicted with the CHECK constraint "ck_bla". The conflict occurred in database "Test", table "dbo.blatest", column 'code'. The statement has been terminated. 

If you want to insert D12 but not d12, in other words you need the constraint to be case sensitive.

You will need to create the constraint like this check (code like '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS ) What we did is used the SQL_Latin1_General_CP1_CS_AS collation, to find out what this collation does, run the following

SELECT * FROM ::fn_helpcollations()
WHERE name = 'SQL_Latin1_General_CP1_CS_AS'

Here is what is returned as the description Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data

Let's create the constraint, first we need to drop the old constraint

ALTER TABLE blatest DROP CONSTRAINt ck_bla
GO

Now we will create the new case sensitive constraint

ALTER TABLE blatest ADD CONSTRAINT ck_bla 
CHECK (code LIKE '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS )
GO

INSERT blatest VALUES('D12') --good
INSERT blatest VALUES('d12') --fails

The insert with D12 will succeed but the one with d12 will not. As you can see you can use regular expressions in check constraints, there is no need to use a trigger in a case like this.

Standardized Naming And Other Conventions

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

Today we are going to look at standardized naming conventions and other conventions that you should standardize as well. Every company needs to have standards that developers need to follow in order to make maintenance easier down the road. There are several things that you can standardize on, here are just a few:

The naming of objects
The layout of code including comments
The way that error handling is done

The naming of objects

I am not a fan of underscores,  we tend to name our objects CamelCased

Stored procedures are usually prefixed with usp_ or pr but never sp_

One tool that ships with SQL Server that you can use is policy management, you can set it so that it checks if procs start with sp_

And here is what happens after the policy is evaluated

Since this is Adam Machanic's proc.. we will let this fly  :-)

Something like this can also be accomplished with DDL triggers, there are many ways to skin the cat, there is no excuse for having all kind of crazy named objects.

I also wrote about naming conventions in the using the ISO-11179 Naming Conventions post

Never use Hungarian notation on column names or variables, I have worked with tables that looked like this

CREATE TABLE tblEmployee(
strFirstName varchar(255),
strLastName varchar(255),
intAge int,
dtmBirthDate datetime
.......
.......
)

If you have intellisense in SSMS, having every table start with tbl is making it pretty useless. Also sometimes the data type of a column will change but of course nobody goes back to rename the column to reflect this because it will break code all over the place

Instead of having something like the following

-- the salary for the employee
declare @decValue decimal(20,2)

It would be better to have something like this

declare @EmployeeSalary decimal(20,2)

Now I don't have to scroll all the way to the top to figure out what is actually stored in this variable, EmployeeSalary pretty much describes what it is and I can also pretty much assume that this will be some amount and not a date

The layout of code including comments

I have worked with code that was all in lowercase and all in uppercase. I have no problem with either but if you at least standardize on one or the other it will be a little easier to jump from your code to someone else's code


You can setup standard templates in SSMS for your organization, you can get to it from the menu bar, View--> Template Explorer or hit CTRL + ALT + T

Now expand the Stored Procedures folder

The basic stored procedure template looks like this

-- =============================================
-- Create basic stored procedure template
-- =============================================

-- Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'<Schema_Name, sysname, Schema_Name>'
     AND SPECIFIC_NAME = N'<Procedure_Name, sysname, Procedure_Name>' 
)
   DROP PROCEDURE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name>
GO

CREATE PROCEDURE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, Procedure_Name>
 <@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>, 
 <@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
 SELECT @p1, @p2
GO

-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE <Schema_Name, sysname, Schema_Name>.<Procedure_Name, sysname, 
Procedure_Name> <value_for_param1, , 1>, <value_for_param2, , 2>
GO

You can modify this template, give it to every developer and now you all have the same template. What can be done with templates can also be done with snippets, if you do Tools-->Code Snippets Manager, you can see all the snippets that are available, you can add your own snippets so that all developers will have the same snippets for comment tasks.

Standardize on comments as well.  Besides what ships with SSMS, there are also commercial tools that will do an even better job than SSMS

The way that error handling is done

I like to have all the errors in one place, this way I know where to look if there are errors. Capture the proc or trigger that threw the error, it if is a multi-step proc then also note the code section in the proc, this will greatly reduce the time it will take you to pinpoint where the problem is. Michelle Ufford has a nice example here: Error Handling in T-SQL that you can use and implement in your own shop.

There are many more things that you need to standardize on, the thing that bothers me the most is when I see dates in all kind of formats when passed in as strings, use YYYYMMDD, this will make it non ambiguous.

Do not trust the SSMS designers, learn the T-SQL way

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

Read the following two lines

Question: How do you add a primary key to a table?
Answer: I click on the yellow key icon in SSMS!

Have you ever given that answer or has anyone every answered that when you asked this question?

Technically, yes, that will create a primary key on the table but what will happen when you do that? Let's take a look at some examples.

First create this very simple table

CREATE TABLE TestInt(Col1 tinyint not null)

Now the developers changed their mind and want to insert values that go beyond what a tinyint can hold. If you try to insert 300, you will get an error

INSERT TestInt VALUES(300)

Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 300.

The statement has been terminated.

No, problem, I will just change the data type by running this T-SQL statement

ALTER TABLE TestInt ALTER COLUMN Col1 int NOT NULL

But what if you use the SSMS designer by right clicking on the table, choosing design and then changing the data type from tinyint to int?

The answer is it depends on an option and if it is checked or not

If that option is checked, then you will get the following message when clicking on the script icon

If that option is not checked then here is what SSMS will do behind the scenes for you

/* To prevent any potential data loss 
issues, you should review this script in 
detail before running it outside the context
 of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestInt
 (
 Col1 int NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestInt SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.TestInt)
  EXEC('INSERT INTO dbo.Tmp_TestInt (Col1)
  SELECT CONVERT(int, Col1) FROM dbo.TestInt WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestInt
GO
EXECUTE sp_rename N'dbo.Tmp_TestInt', N'TestInt', 'OBJECT' 
GO
COMMIT

That is right, it will create a new table, dump all the rows into this table, drop the original table and then rename the table that was just created to match the orgiinal table. This is overkill.

What about adding some defaults to the table, if you use the SSMS table designer, it will just create those and you have no way to specify a name for the default.

Here is how to create a default with T-SQL, now you can specify a name and make sure it matches your shop's naming convention

ALTER TABLE dbo.TestInt ADD CONSTRAINT
 DF_TestInt_Col1 DEFAULT 1 FOR Col1

About that yellow key icon, let's add a primary key to our table, I can do the following with T-SQL, I can also make it non clustered if I want to

ALTER TABLE dbo.TestInt ADD CONSTRAINT
 PK_TestInt PRIMARY KEY CLUSTERED 
 (Col1)  ON [PRIMARY]

Click that yellow key icon and here is what happens behind the scenes, I have not found a way to make it non clustered from the designer

/* To prevent any potential data loss issues, 
you should review this script in detail before running it 
outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TestInt
 DROP CONSTRAINT DF_TestInt_Col1
GO
CREATE TABLE dbo.Tmp_TestInt
 (
 Col1 int NOT NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestInt SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_TestInt ADD CONSTRAINT
 DF_TestInt_Col1 DEFAULT ((1)) FOR Col1
GO
IF EXISTS(SELECT * FROM dbo.TestInt)
  EXEC('INSERT INTO dbo.Tmp_TestInt (Col1)
  SELECT Col1 FROM dbo.TestInt WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestInt
GO
EXECUTE sp_rename N'dbo.Tmp_TestInt', N'TestInt', 'OBJECT' 
GO
ALTER TABLE dbo.TestInt ADD CONSTRAINT
 PK_TestInt PRIMARY KEY CLUSTERED 
 (
 Col1
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

You might ask yourself why you should care, all the tables are small, this is not a big issue. This might be true now, what if you start a new job and now you have to supply alter, delete and create scripts? Now you are in trouble.

I used to do the same when I started, I used the designers for everything, I didn't even know Query Analyzer existed when I started, I created and modified the stored procedures straight inside Enterprise Manager. Trying to modify a view that had a CASE statement in Enterprise Manager from the designer....yeah good luck with that one....you would get some error that it wasn't supported, I believe it also injected TOP 100 PERCENT ORDER BY in the view as well

I don't miss those days at all. Get to learn T-SQL and get to love it, you might suffer when you start but you will become a better database developer.

Aaron Bertrand also has a post that you should read about the designers: Bad habits to kick : using the visual designers