SQL Efficiency - Which is quicker?

Question

Hi

I'm doing a bulk insert but before inserting into the actual table I need to do some checks. So currently I'm inserting into a temp table but I've just seen that it's possible to declare tables. So my question is - Which is quicker? To CREATE or DECLARE. The table will have multiple SELECTS done on it and will contains around 200,000 records.

DECLARE @tbl1 TABLE
(
col1 VARCHAR(10)
)

or

CREATE TABLE tbl1
(
col1 VARCHAR(10)
)

Lastly, Is it possible to ALTER a declared table?

Thank you in adv.

Answer 1

@tbl table vars are stored in memory, but if memory pressure is high they will spill into tempdb.

No DDL is allowed on @tbl vars, by design (no indexes, stats and so forth).

Regular tables are created in the database you are connected to.

Performance wise it all depends on how SQL Server is configured and how many resources are allocated to tempdb.

In general when dealing with large number of records I will go for a per connection temp table like #table which allows for statistics and rich indexing over table vars.

Sometimes it may make sense to have a permanent staging table, but you have to be extra careful with concurrency with such a solution.

Only way you can get a definitive answer here is measuring in your environment (note quite often production is configured differently to dev, be mindful of that)