June 25, 2012 at 8:47 am
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.
June 25, 2012 at 9:12 am
"A Table Variable can have primary keys and indexes defined." I don't see the word "you" in there. Can indexes be defined on a table variable? Well, they can exist, and they're certainly not undefined, so the answer must be yes.
That's an extraordinarily broad intepretation of "defined" in the context of a q that starts "When dealing with temporary tables" ... I mean, certainly "you" is implied there ... after all, it's not SQL itself dealing with temp tables :-).
Similarly, if a q stated:
"When dealing with SQL Server, can indexes be defined on a system table?"
You think the answer should be "Yes"?? Because, while "you" can't define them, MS certainly can (altho perhaps not, because often "system tables" are materialized views ...).
I think trying to be overly literalistic in interpreting the qs leads to the type of "depends on what the meaning of the word 'is' is" qs that make every q an unending game of parsing.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 25, 2012 at 9:15 am
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?
June 25, 2012 at 9:28 am
If that is the case, are there any advantages to using a table variable over a temp table? ...
Is there actually any advantage over using a session scoped temp table?
AFAIK, the biggest advantage of table variables (tvs) over temp tables (tts) is that tvs don't cause recompilations to the extent that tts do.
I would strongly urge you to properly index either tvs or tts, as that will be beneficial regardless.
Since I actually prefer dealing with tts, I use them except for very small tables (100 rows or less), and explicitly specify "OPTION (KEEP PLAN)", if needed, to reduce recompiles when querying the tts.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 25, 2012 at 9:28 am
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://www.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
June 25, 2012 at 9:54 am
sestell1 (6/25/2012)
mtassin (6/25/2012)
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?
Probably the #1 reason would be that you can pass a Table variable as a parameter.
While you could create a temp table and then call a stored procedure, it's not something I would want to depend on.
June 25, 2012 at 9:55 am
ScottPletcher (6/25/2012)
"A Table Variable can have primary keys and indexes defined." I don't see the word "you" in there. Can indexes be defined on a table variable? Well, they can exist, and they're certainly not undefined, so the answer must be yes.
That's an extraordinarily broad intepretation of "defined" in the context of a q that starts "When dealing with temporary tables" ... I mean, certainly "you" is implied there ... after all, it's not SQL itself dealing with temp tables :-).
Similarly, if a q stated:
"When dealing with SQL Server, can indexes be defined on a system table?"
You think the answer should be "Yes"?? Because, while "you" can't define them, MS certainly can (altho perhaps not, because often "system tables" are materialized views ...).
I think trying to be overly literalistic in interpreting the qs leads to the type of "depends on what the meaning of the word 'is' is" qs that make every q an unending game of parsing.
Given the vagaries of the English language, being exactly literal is the only valid way to approach something like a QotD. You should only rely on a non-literal interpretation if the question is unsolvable without it. Indeed, that's the way you should approach any word problem or professional communication.
When read literally, today's question is simple and straightforward. It's only when you read though filters that it becomes difficult. Why make it so hard on yourself?
June 25, 2012 at 10:07 am
Hugo Kornelis (6/25/2012)
When I answered the question, I *knew* I was taking a gamble on the intended interpretation.What convinced me to pick the "wrong" answer was the wording: "can have primary keys and indexes defined" - I interpreted this as meaning that you could define any index you want, in addition to those implied by primary key and unique constraints.
For table variables, the truth is that the statement is neither true nor false.
"can have primary keys and indexes defined" - true, because you can define indexes by declaring a primary key constraints and unique constraints in the table declaration.
"can have primary keys and indexes defined" - false, because you can define the primary key any way you want (within the standard rules for primary keys), but you can not define indexes any way you want; you cannot defined non-unique indexes, cannot define unique indexes on columns taht you don't want to declare a unique constraint on, cannot include additional columns in a nonclustered index, etc.
Both explanations are valid, so I was looking for an answer option "true for temp tables; depends for table variables".
Thanks "Hugo Kornelis".
Cheers.
June 25, 2012 at 10:07 am
This is not true:
A Table Variable can have primary keys and indexes defined.
Unless the meaning of what you said is:
A Table Variable can have an index defined as primary key.
But first asumption is unclear and ambiguous :pinch:
June 25, 2012 at 10:25 am
I got the answer right in spite of the question(s) pertaining to table variables really leading me in another direction. Yeah, a primary key uses an index so by creating a PK you create an index leaving the correct answer to the way the question is worded as true. If this were a question for an interview it would certainly be a good idea to have a "explain your answer" area because it could have been taken to ask if create index was a valid command against a table variable and clearly it is not thus causing someone to answer fale.
Cheers
June 25, 2012 at 10:37 am
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.
Without that introductory clause, then, yes, the answer is true.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 25, 2012 at 10:57 am
Toreador (6/25/2012)
I took a risk and made the correct assumptions, so guessed the right answer 🙂But strictly speaking, the answer should be "false, false".
A Temporary Table can have primary keys ...
A Table Variable can have primary keys ...
A single table can have only one primary key 😉
I wish I had caught that. I answered "true, false" because a table variable can only ever have on index. A primary key or (as I learned today) a unique clustered index. It's not possible to create any more so it's not possible for a table variable to have multiple indexes. I assumed that was the crux of the question and that the plural was intentional. If I had noticed that primary keys was plural too I would have assumed the plural was incorrect and gotten it right.
June 25, 2012 at 11:21 am
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?
Neither table variables not temporary tables are created in memory only. And both are created in memory only.
When a table (permanent, temporary, or variable) is created, the data is stored in memory, in the buffer cache. Since these pages are "new", they are by definition "dirty" (different from the persisted pages on disk), so when the checkpoint process kicks in, they are written to disk (and still kept in memory for quick access). When the buffer pool fills and more room is needed, they may be removed from the buffer pool (depending on how recent and how often they were used in comparison with other pages), and then they have to be read from disk into cache again when the data is accessed again. When the data is modified, the page is marked as dirty again, and will be flushed to disk during the next checkpoint.
In practice, most temporary tables and table variables are so short-lived that they cease to exist before the checkpoint process ever kicks in.
sestell1 (6/25/2012)
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?
They are different in several ways. If you google or bing for them, you are bound to find a lot of information, and the challenge to distinguish the correct information from the nonsense and the parrotted myths.
Some of the differences are functional. If you need to alter a table or define an explicit index, temp tables are your only option. If you need to store data in a place where it survives a rollback, table variables are the only way to go. When none of the functional differences apply and you want to optimize for performance only, the best thing you can do is to run extended tests, with hardware and workloads that are as close to production as you can manage. The guideline to "use table variables for up to 100 rows" is no more than that - a guideline, a rule of thumb. There are workloads where a table variable is better than a temp table for thousands of rows, and there are those where a temp table performs better for even a single row.
June 25, 2012 at 11:22 am
cfradenburg (6/25/2012)
Toreador (6/25/2012)
I took a risk and made the correct assumptions, so guessed the right answer 🙂But strictly speaking, the answer should be "false, false".
A Temporary Table can have primary keys ...
A Table Variable can have primary keys ...
A single table can have only one primary key 😉
I wish I had caught that. I answered "true, false" because a table variable can only ever have on index. A primary key or (as I learned today) a unique clustered index. It's not possible to create any more so it's not possible for a table variable to have multiple indexes. I assumed that was the crux of the question and that the plural was intentional. If I had noticed that primary keys was plural too I would have assumed the plural was incorrect and gotten it right.
No, if table variables get indexes created for constraints that are part of the definition as Hugo says, then you can have multiple indexes on a table variable. Just add some other unique constraints in the CREATE TABLE statement. For each unique constraint (and really, a PRIMARY KEY is just a special unique constraint), an index is used to enforce it.
The problem we are having with the question is that BOL explicitly states that you cannot create indexes using a CREATE INDEX command on a table variable, and the question seems to imply that this is what it might be asking. From my perspective, when I make a constraint I am not doing it for the index but for the data integrity, so I don't think of that as "indexing the table." In fact, I routinely make a separate index for a table if I want my primary key to be clustered, so that I don't have to dismantle referential integrity if ever I need to change the clustered index to another column.
June 25, 2012 at 11:57 am
Don't agree with this answer for the same reason most people are talking about. You can define a primary key on a table variable which technically creates an index but you can not create a normal index on a table variable making the answer TRUE/FALSE.
Viewing 15 posts - 31 through 45 (of 74 total)
You must be logged in to reply to this topic. Login to reply