T-SQL Plugin - New rule for blocking issues in TempDb related to Select * Into #

tsql

(Christian Rohr) #1

The following query is blocking the complete TempDB (before Version 2014) on a SQL Server until the Statement finished:

Select * 
INTO #TempTable
From [Table,JoinedTables]

In a compliant way it would look like:

Create Table #TempTable
(
-- Table definition
)
Insert Into #TempTable
(
-- ColumnList
)
VALUES () or SELECT (ColumnList) FROM [Tables]

It might cause some performance issue or is at least a bad code smell and is at least related to code smells.


(Alexandre Gigleux) #3

Hello Chris,

From what I was able to find, only SQL Server 6.5 was affected by this problem of TempDB being locked.
SQL Server 7.0 (released in 1998) fixed it.

Because the version of SQL Server affected are old and no longer supported, I don’t think it’s required to have a rule to check that.

Then, if you have more recent information stating that SQL Server versions prior to 2014 were still affected by this problem, I may change my mind.

Regards


(Christian Rohr) #4

Hello Alexandre

I changed already before the description. So it is not a blocker bat a bad practice to do this so in Stored Procedures or functions.

So it would be good so have such a rule.

Thank you.

Christian

Christian Rohr

Site Reliability Engineer

t +49 (0) 6221 7961-105
m +49 (0) 1520 927 59 17

christian.rohr@verivox.com