Month / February 2013

by Arvind Shyamsundar

T-SQL formatter: a PowerShell script

EDIT 19 Aug 2022: This post has not been updated for a long time, and the link to the sample code is broken. So, I recommend you take a look at Mala’s more recent blog post and associated script at https://www.sqlservercentral.com/articles/formatting-t-sql-scripts-using-scriptdom-and-powershell If you really want to look at my original sample code, a member of the community updated my older script and shared it as a gist: https://gist.github.com/jheidt/5320803

This is a ‘quick-and-not-so-dirty’ version of a T-SQL formatter / ‘pretty printer’ in PowerShell. To use this, please install the SQL 2012 SP1 version of the SQLDOM from the Feature Pack page. (If you already have SQL 2012 client tools on your machine, you should be ready to go.) And you will need PowerShell of course Smile

Here is a sample usage of this script:

tsqlpp.ps1 -Source c:temptestcases.sql -Target c:tempout.sql

Some usage tips:

  • Right click and save the file to your machine, do NOT run it directly from this blog site
  • To execute the script your PowerShell execution policy must be set to RemoteSigned
  • The parser is by default set to 110 compatibility i.e. SQL 2012 syntax. If you are working with older compatibility levels feel free to edit the script to reference the 80, 90, 100 versions of the parser.
  • The generated / formatted script will NOT contain comments, you will lose any comments you had in the unformatted version

Please leave your comments, suggestions and asks for future versions of the script in the Comments section of this post. Thank you and I’m glad to be of help to you!

Disclaimer: This script is provided AS IS with no warranties, and confer no rights.

Advertisement
by Arvind Shyamsundar

Named constraints: two sides to the story!

Background

Constraints in SQL Server are of the following types:

  • CHECK constraints
  • DEFAULT constraints
  • Foreign key constraints
  • NULLable constraint
  • UNIQUE constraint
  • PRIMARY KEY constraint

BTW, if you are wondering what a NULLable constraint is, it is the formal representation of a NULL / NOT NULL definition for a field. We don’t normally think of NULL definitions in that way but in the parser that is how it is represented Smile

Anyways, constraints such as CHECK, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY can be named, for example:

CREATE TABLE #TableWithNamedConstraints
(
    i int not null constraint PK_TableWithNamedConstraints primary key,
    j int constraint DF_j default 100,
    k int constraint CK_k check (k > 0)
)

The issue

While this is generally considered to be a good practice for base tables, for a temp table such as the above, it can be a real problem if you have multiple connections executing the above CREATE TABLE code at the same time. The problem arises from the fact that while the temp table name is uniquified, the constraint names are not. In such cases of concurrent execution, you typically receive an error such as the below:

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

You can see the evidence of this in sys.objects:

select name from sys.objects
where parent_object_id = object_id(‘#TableWithNamedConstraints’)
or object_id = object_id(‘#TableWithNamedConstraints’)

The output shows that the table name is uniquified but the constraint names are not:

PK_TableWithNamedConstraints
DF_j
CK_k
#TableWithNamedConstraints_________…_____00000000001D

Workaround

This issue is described in this Connect bug as well. The workaround is therefore to not name constraints in the case of temporary tables. So for the example shown above, the more ‘robust’ version (for the temp table case) would look like this:

CREATE TABLE #TableWithNamedConstraints
(
    i int not null primary key,
    j int default 100,
    k int check (k > 0)
)

Conclusion

So the next time you have a CREATE TABLE with constraints, consider the two cases:

  • For base tables, you almost ALWAYS want to name them
  • For temporary tables, you almost NEVER want to name them

Hope this helps!