June 25, 2012 at 12:37 pm
Hugo Kornelis (6/25/2012)
Neither table variables not temporary tables are created in memory only. And both are created in memory only. . . .
Hugo, hat off.
When we happen to be in the same zip code, I am buying.
June 25, 2012 at 12:52 pm
Dalton Moore (6/25/2012)
At least I agreed with the consensus "wrong" answer... 😎
You weren't the only one.:-)
June 25, 2012 at 1:54 pm
ScottPletcher (6/25/2012)
When read literally, today's question is simple and straightforward.
Quite true. The second part is clearly false, since "When [you are] dealing with table variables, ...", you cannot define indexes on them.
That is the literal reading.
No, it's not. You're inferring action when none is explicitly defined. To the literal question:
When dealing with temporary tables and table variables, which of the following statements are true:
1. A Temporary Table can have primary keys and indexes defined
2. A Table Variable can have primary keys and indexes defined
So, the introductory clause sets the scene. You are dealing with temporary tables and table variables. The next part says "which of the following statements are true:" Note that it does not say "which of the following actions are possible:" This means we are observing the state of affairs, not necessarily altering it.
Going to the statements next, is it possible that (1) a temporary table has primary keys* and indexes defined on it?
Is it possible that (2) a table variable has primary keys* and indexes defined on it?
* Here is the only valid gripe I've seen for this question so far. Any table (user, temporary, variable) can only have one primary key, so the use of the plural is grammatically a violation of quantity. Correct phrasing would either be "...temporary tables..." or "...a primary key..." Note that it does not say
June 25, 2012 at 2:05 pm
2. A Table Variable can have primary keys and indexes defined
Baloney.
"Defined" is inherently an action, not a state.
You cannot "define an index" on a table variable in SQL Server.
If the statement were:
"A Table Variable can have a primary key and indexes"
then that would be true -- and that is asking about the state.
When asked about "defined" indexes, you're asking about an explicit action.
If one isn't interested in the action, one should not say "defined".
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 25, 2012 at 2:39 pm
Revenant (6/25/2012)
Hugo Kornelis (6/25/2012)
Neither table variables not temporary tables are created in memory only. And both are created in memory only. . . .Hugo, hat off.
When we happen to be in the same zip code, I am buying.
The Netherlands, most of the year.
Copenhagen, October 1-3 (SQL Rally Nordic).
Seattle, November 6-9 (PASS Summit). And maybe I'll tack on one or two SQLSaturday events just before and after the PASS Summit.
Second round is on me. 🙂
June 25, 2012 at 2:48 pm
Here is the problem:
"A Table Variable can have primary keys and indexes defined"
This can be read two ways.
1) "can have defined" can be read to mean "can have the ability for them to be defined" which would imply an action on the user's part to define them (as opposed to a passive creation).
2) it can also be read to mean "definitions for indexes can exist on the server". Read this way, the question is true. If you make a primary key, an index exists and would be placed into sysindexes if that were appropriate (which I don't think it is for a table variable, but that's not important here).
The author clearly meant #2. Most of us were reading #1. The wording is definitely ambiguous.
June 25, 2012 at 2:58 pm
Another day, another incorrect answer, oh well. Alot of great posts though.
June 25, 2012 at 3:21 pm
The author clearly meant #2. Most of us were reading #1. The wording is definitely ambiguous.
I suppose it's somewhat ambiguous.
But #2 is so illogical and extreme given everything else in the q, that it's fatuous for the q askers to take #2 as the de facto default.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 25, 2012 at 4:52 pm
jeff.mason (6/25/2012)
Here is the problem:"A Table Variable can have primary keys and indexes defined"
This can be read two ways.
1) "can have defined" can be read to mean "can have the ability for them to be defined" which would imply an action on the user's part to define them (as opposed to a passive creation).
2) it can also be read to mean "definitions for indexes can exist on the server". Read this way, the question is true. If you make a primary key, an index exists and would be placed into sysindexes if that were appropriate (which I don't think it is for a table variable, but that's not important here).
The author clearly meant #2. Most of us were reading #1. The wording is definitely ambiguous.
+1 Jeff. Question is ambiguous but I can step back and see the author's point of view here.
Regardless of whether or not we agree or disagree with interpretation, thanks to the author for the question and for those with constructive commentary on the subject. Cheers
June 25, 2012 at 7:58 pm
OK. I got it "wrong" for knowing I could explicitly define an index on a temp table but not on a table variable. No big deal (I'm not a points-hawk) except that the explanation didn't explain the distinction at all. Made me doubt my knowledge till I read the discussion. And then I definitely learned something new (imply the creation of index via UNIQUE column constraint). So, thanks to all who contributed to the clarification.
June 26, 2012 at 2:08 am
ScottPletcher (6/25/2012)
2. A Table Variable can have primary keys and indexes defined
Baloney.
"Defined" is inherently an action, not a state.
You cannot "define an index" on a table variable in SQL Server.
Baloney 😉
"have defined" is an inactive verb, not an active one, and as such does not denote an action by nyou but by amyone (or anything) - in this case SQLServer itself.
But in any case, you <i>can</i> "define an index" on a table variable.
declare @t table
(a int not null primary key nonclustered,
b int not null unique clustered
)
defines a unique nonclustered index on a and a unique clustered index on b.
June 26, 2012 at 6:46 am
Toreador (6/26/2012)
ScottPletcher (6/25/2012)
2. A Table Variable can have primary keys and indexes defined
Baloney.
"Defined" is inherently an action, not a state.
You cannot "define an index" on a table variable in SQL Server.
Baloney 😉
"have defined" is an inactive verb, not an active one, and as such does not denote an action by nyou but by amyone (or anything) - in this case SQLServer itself.
But in any case, you <i>can</i> "define an index" on a table variable.
declare @t table(a int not null primary key nonclustered,
b int not null unique clustered
)
defines a unique nonclustered index on a and a unique clustered index on b.
Technically you are defining constraints which the engine enforces by creating indexes behind the scenes. Indexes defined by the user can be dropped, an index created by the engine to enforce a constraint cannot be dropped, the constraint must be dropped.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 26, 2012 at 6:51 am
Thank you for all your posts (positive and negative). Great response and content
Apologies for the grammatical error in the plurals (i never was an english A grader 😉 )
I will take the lesson and work on being clearer in future QotD's
July 1, 2012 at 8:23 pm
jeff.mason (6/23/2012)
You cannot define indexes at object creation. And while a typical table will create an index for a primary key, I suspect that this does not happen for table variables. And in at least SQL 2008, indexes are explicitly NOT supported for table variables (from the link on the post above):Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics. For more information about temporary tables, see CREATE TABLE (Transact-SQL).
Given this wording, I think that the question is not at all clear what it is intending. The only possible indexes might be a byproduct of primary key creation, and otherwise indexes are NOT supported.
Primary key and unique constraints are enforced in sql server by creating a unique index. This happens for permanent tables, temporary tables, and table variables.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 1, 2012 at 8:34 pm
bkmsmith (6/25/2012)
sestell1 (6/25/2012)
mtassin (6/25/2012)
sestell1 (6/25/2012)
I was always under the impression that there was no reason to create indexes on table variables because they exist only in memory.Does anyone know what SQL Server actually does when you define a primary key in a table variable? Does it create an 'index' in memory?
Yes, but table variables and temp tables both start out in memory, if you put enough records in them, they will indeed spill to disk.
SQL Server is amazing at memory management, but there's always a finite amout of RAM in the system.
If that is the case, are there any advantages to using a table variable over a temp table? I know the optimizer treats them very differently... assuming a single record for table variables, which can adversely affect performance if that is not actually the case. I had used table variables for small sets of static data or to temporarily hold return data from system procs, thinking there was less overhead. Is there actually any advantage over using a session scoped temp table?
Wayne Sheffield had recently published an article to SSC on the topic of comparing table variables to temp tables that I found very enlightening.
http://qa.sqlservercentral.com/articles/Temporary+Tables/66720/
In the article he had identified some common myths, general guidelines for when to use one over another, and a great summary table detailing a side by side comparison of the two.
Brian Smith
First of all, I want to thank Ian for posting this question. The replies to it show that despite my efforts to educate people, lots of myths are still out there concerning table variables.
Secondly, thank you Brian for plugging my article[/url]. While it does need to be updated for some SQL 2012 / SQL Azure changes, it is probably the best article out there for comparing temporary tables and table variables. (If you're going to the PASS Summit 2012 - I'll be doing a presentation based upon this article - please come see it... you will learn something!)
So, let's address various things brought up:
1. Table variable can only have one index. Well, I believe in demonstrations, so run this code to see three, count them, three indexes on a table variable! (In Sql 2008, you can have up to 1000! I wouldn't do it, but it is possible.)
IF OBJECT_ID('tempdb..#temptables','U') IS NOT NULL
DROP TABLE #temptables;
-- get all of the existing tables created in tempdb
SELECT object_id, name
INTO #temptables
FROM tempdb.sys.tables;
GO
-- make a table variable with a primary key and a unique constraint on it
DECLARE @test-2 TABLE (
Col1 INT PRIMARY KEY,
Col2 INT UNIQUE CLUSTERED,
UNIQUE(Col1, Col2));
-- get the object_id for this table variable
-- by seeing what has been added to a tempdb tables.
DECLARE @Object_id INT;
SELECT @Object_id = object_id
FROM tempdb.sys.tables
WHERE object_id NOT IN (SELECT object_id FROM #temptables);
-- see what indexes are on this table
SELECT *
FROM tempdb.sys.indexes
WHERE object_id = @Object_id;
2. Table Variables are in memory only - see Gail Shaw's excellent blog post (and I do this demo in my presentation with her permission!) demonstrating that the data being inserted into a table variable is written out to the tempdb mdf file - that's the data file folks, and it's on disk - not in memory. A Trio of Table Variables[/url]
3. When would you use a table variable?
3a. Well, first, there are times when you HAVE to use them: a Multi-statement table-valued function; when passing a table as a parameter to a stored procedure.
3b. When you want to use a feature of a table variable that isn't available to a temporary table: the "explicit transactions don't affect table variables" feature, and prevention of stored procedure recompilations that temporary tables cause. IMHO, these are both edge cases.
3c. You should NOT use a table variable if it has a WHERE clause, or is being JOINed to other tables.
3d. Aside from 3a and 3b, I don't use them for anything else. All because of statistics - there aren't any on table variables. So, even if you were to use the "OPTION RECOMPILE" query hint on a table variable, it still doesn't have histogram information and it will still make a bad query plan.
4. Use up to 100 rows. - This is not a rule, but a guideline. It was based on peoples observance that performance went bad fast after this number of rows. However, I wrote that article because of a little table variable with just 48 rows in it taking 3 minutes to run... and when I changed it to a temp table, it ran in < 1 second. Kevin Boles (@TheSqlGuru) has an edge case scenario where a table variable with just ONE record runs horrible compared to a temp table.
My suggestion is to thoroughly read and understand my article (several times if necessary). Use table vars when needed; use temp tables otherwise. Variations to this need to be thoroughly tested out.
Did I mention that I'm doing this presentation at the 2012 PASS Summit? Come see it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 46 through 60 (of 74 total)
You must be logged in to reply to this topic. Login to reply