Constraints On Temp Tables

Kenneth Fisher argues that you should use default naming for temp table constraints:

You should be able to create a #temp in every session. That’s the idea, right? It’s one of the things that differentiates a global temp table from a local temp table. But there can be some difficulties with that.

If you are working with reusable code that uses temp tables (a stored procedure for example), sometimes you need to create a constraint. The thing about constraints is that their names are just as unique as tables, stored procedures etc. i.e. the name of a constraint can only be used once. You can’t have two tables with the same constraint name. In fact, you can’t even have a constraint name that matches a table, stored procedure etc name.

There’s some solid advice in this post.

Related Posts

Operator Precedence In Complex Check Constraints

Louis Davidson walks us through the case of the disappearing parentheses: You will see the code is not the same as when I coded it: ([Value1] IS NOT NULL AND [Value2] IS NULL OR [Value1] IS NULL AND      [Value2] IS NOT NULL OR NOT [Value1]=[Value2] AND [Value2]>(2)*[Value1]) Looking as this, one thing stands out […]

Read More

Checking For Temp Table Existence

Wayne Sheffield offers a clinic on temp tables: I was recently reviewing a newly created T-SQL stored procedure. This procedure was verifying temporary table existence with the following code: 2 IF exists (select * from tempdb..sysobjects where name like ‘#fg%’) DROP TABLE #fg Seeing this takes me back to one of my favorite presentations, where […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *


May 2018
« Apr