P.s  Nice trick with the TOP 0 thing... most folks use WHERE 1=0 to do the same thing.  TOP 0 seems more obvious and I like it.

First, SELECT/INTO does not lock system tables during a SELECT/INTO with no explicit transaction... (at least, none that I can detect)...

Test setup:

First, build yourself a nice medium sized test...

You should never set the database to grow by a percentage especially if the size of a database starts out small.  It takes 73 growth spurts to grow a 1...

Sure... I'll send you the shrunken head of a Developer that I didn't like

Just curious... did my post help at all?

Yep... write to a UNC instead of a file/path...

\\machinename\path

The server must have write privs, as well.

SELECT dbo.functionname(@parameter)

Job Requirements:

1. Must know T-SQL inside and out
2. Must at least know how to spell "performance code"
3. Must comply...

Here's how to do it... you will need to replace "dbname" with the actual name of the database.  You will need to do like wise with "servername".  Once you've tested...

Why on Earth would you put the creation of a temp table in an explicit transaction?  And, so far as exclusively locking the system tables when you use SELECT/INTO to...

You bet, Remi... we actually put the requirement in our "SQL Coding Standards" document so the Developers are made keenly aware of the problem.  Since we do code reviews on...

Sreejith,

Yep... we had a bunch of these... they all worked fine until, one day...  That's what's so insidious about it.  Works fine until you reach some unknown tipping point and...

I'm with you there... one of my favorite sayings is "Anyone can make a mistake but to really mess things up, you need a computer."

Nope... it doesn't matter if they're 2 or 20,000... especially when someone posts as well as you do   Dunno... maybe I need some stronger...