CREATE TABLE Vs DECLARE TABLE

Question

CREATE TABLE Vs DECLARE TABLE

Carl Farmer

SSCrazy

Points: 2197
More actions
June 22, 2004 at 3:29 am

#108607

When using syntax such as 'CREATE TABLE #Temp...' to define the structure of a temporary table that is used several times in a Stored Procedure, it causes the SP to recompile (execution plan). Is the same true when using 'DECLARE @Table1 TABLE...' to define a structure?
Also - I notice that 'DECLARE @Table1 TABLE...' only works well for small record sets, does anyone know the optimum, or maximum size for these?
Thanks in advance,
Carl.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

It depends...

As you've noted, both temptables and table variables have their uses. Where the limit goes, I'd guess would depend on things as datavolume, hardware, etc. (ie many things)

However, if you collect all your DDL in the beginning of the procedure, it will only recompile once.

Scattered DDL sprinkled all around in Transact SQL is a common cause of multiple (and unnecessary) recompiles.

Best would of course be to not use temptables at all

(if possible, that is...)

/Kenneth

Carl Farmer SSCrazy Points: 2197 More actions · Answer 2

Avoiding temp tables is a little hard as the SP's in question are several hundred lines long. They are honking great things.

All the DDL is at the top - so it is quite efficient already, just trying to enhance it further by eliminating disk latency etc.

Thanks for your reply.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Well, the key to avoiding disk latency is to avoid all usage of tempdb and ensure that all datapages are already in cache

Utopia perhaps, but the main goal to strive for. Many times one can get much closer than one might first think.

=;o)

/Kenneth

schleep SSChampion Points: 12583 More actions · Answer 4

#tables in sprocs cause recompiles under certain conditions.

Using Profiler, monitor the sp_recompile event, and select the Sub-class data column. Then, go see Q308737 on MS website for an explanation of the codes.

Essentially, I expect you to see a lot of recompiles with sub-class = 2 (statistics changed). Apparently, it only takes about 6 rows in a #table to trigger a recompile.

As to your other question, how many rows before @tables max out their benefit, I have found it to be somewhere in the low 100's, maybe 4-500. It does depend on the width of the table, tho.

P