which is small and which is large data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

which is small and which is large data

Author

Topic

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2014-12-22 : 01:46:16

hi team,

in an interview i was asked a question that when to use table variable and temp table. i told the interviewer that when rows is less like hundreds or thousand then use table variable else use temp table.

After that he asked that what do u mean by less data or thousand rows
may be there are multiple columns involved with that less rows and make a huge data set.

i am still confused with this.please suggest me what should be the possible best ans for this.

prithvi nath pandey

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-22 : 09:48:20

It's not so much the size of the table (though that is a factor) as much as what you want to do with it. Table variables have some limitations wrt temp tables:

1. You can't add indexes to them after creation (though you can during the initial definition)
2. SQL doesn't maintain statistics on them
3. SQL Server storage engine never generate a parallel execution plan for a table variable

However, if you will only ever return the entire contents of the table variable and don't use them to join to other tables, these things don't matter. Basically, if your use is trivial and the generated plans are trivial, table variables are fine for almost any size. If your table is small (where "small" is dependent on a number of things, including the processor and disk setup for tempdb) they are generally fine in any case. Check the execution plan to be sure.

Note that table variables have one significant advantage over temp tables for some applications: They outlive transactions. that is, neither an explicit nor implicit ROLLBACK will undo changes to table variables. This can be quite handy.

desperadomar
Starting Member

3 Posts

Posted - 2015-01-07 : 05:42:34

This is a nice link which compares both

http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-01-07 : 07:56:04

And in terms of performance, SQL Server always assume there is only 1 row on the table variable no matter what.
It can affect the execution plan.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources