August 12, 2010 at 6:27 am
jcrawf02 (8/12/2010)
I'm sure that I read someone's blog/article about how table variables can be indexed during the declaration of the variable, and I think it was outside even of a primary key, but I can't find it anywhere. Anybody remember that/know where I could find it?
Dunno about the blog, but you can create primary key and unique constraints on a table variable, if they're done as part of the declaration. Alter table and create index statements fail.
eg:
declare @tbl table (
id int identity primary key,
Col1 varchar(20) unique,
Col2 varchar(30),
Col3 datetime,
unique nonclustered (Col2, Col3, id)
)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2010 at 6:32 am
jcrawf02 (8/12/2010)Just general knowledge seeking, not specifically trying to do anything with it 🙂
Remember that the optimizer uses statistics to decide to use an index. Table variables have none, it is assumed that they will have 1 row. So if you want to use an index you generally have to use a hint.
August 12, 2010 at 6:43 am
Jack Corbett (8/12/2010)
My kids start school today. I can't believe how early school starts in FL. We are starting earlier than most other schools, but still, it is only August 12th. What happened to starting around Labor Day?
My middle daughter starts her Junior year of High School today, but my youngest doesn't start Middle school until next week. Middle daughter goes to High School out of district.
I agree. When I was in school, we didn't start until AFTER Labor Day.
August 12, 2010 at 6:45 am
Dave Ballantyne (8/12/2010)
Remember that the optimizer uses statistics to decide to use an index. Table variables have none, it is assumed that they will have 1 row. So if you want to use an index you generally have to use a hint.
Unless the index is covering, in which case it will be used without a hint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2010 at 7:05 am
Thanks Gail/Dave! I really gotta start testing stuff before I just ask you questions, although I'm glad I did....
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 12, 2010 at 7:07 am
Jack Corbett (8/12/2010)
My kids start school today. I can't believe how early school starts in FL. We are starting earlier than most other schools, but still, it is only August 12th. What happened to starting around Labor Day?
You gotta start early...
...cause of all the snow days...
...couldn't help myself :hehe:
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
August 12, 2010 at 7:08 am
Lynn Pettis (8/12/2010)
Jack Corbett (8/12/2010)
My kids start school today. I can't believe how early school starts in FL. We are starting earlier than most other schools, but still, it is only August 12th. What happened to starting around Labor Day?My middle daughter starts her Junior year of High School today, but my youngest doesn't start Middle school until next week. Middle daughter goes to High School out of district.
I agree. When I was in school, we didn't start until AFTER Labor Day.
Yeah. School started after Labor Day for me to, that was the last hoorah for Summer vacation. Texas (or at least around Houston) does the same thing, although they start a bit later in the month but still, before Labor Day.
From what I remember, when I was a kid, some of the private schools would start a week early, but then they would usually get out a week early as well. But starting in the middle of August? I wonder what started that trend or if it's the way things are done in the more southern states. I don't know if the school district I went to up north is starting early or is keeping to the after Labor Day schedule.
-- Kit
August 12, 2010 at 7:15 am
Dave Ballantyne (8/12/2010)
Remember that the optimizer uses statistics to decide to use an index.
Statistics are just one element in the decision-making process. Finding an efficient access path is another. The optimiser will generally prefer an index over a table scan, even on a table variable.
Table variables have none, it is assumed that they will have 1 row.
True, unless the OPTION (RECOMPILE) query hint is used. If that is the case, the recompilation at run time will have access to accurate cardinality information.
So if you want to use an index you generally have to use a hint.
What sort of hint did you have in mind? Index hints don't work with table variables, and you can't give the PRIMARY KEY or UNIQUE constraints names anyway!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 12, 2010 at 7:18 am
Back on topic, someone decided to post a request for T-SQL problem solution in the commentary of the new article I wrote. Did the whole "Here's my problem. Solve it and I'll give you credit for it." bit.
Is this a one time thing or a growing trend?
August 12, 2010 at 7:19 am
GilaMonster (8/12/2010)
Dave Ballantyne (8/12/2010)
Remember that the optimizer uses statistics to decide to use an index. Table variables have none, it is assumed that they will have 1 row. So if you want to use an index you generally have to use a hint.Unless the index is covering, in which case it will be used without a hint.
A table variable index does not have to be covering to be chosen by the optimiser.
Plans with a non-clustered index seek + lookup are perfectly possible with table variables, even without any hints.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 12, 2010 at 7:19 am
Lynn Pettis (8/12/2010)
Jack Corbett (8/12/2010)
My kids start school today. I can't believe how early school starts in FL. We are starting earlier than most other schools, but still, it is only August 12th. What happened to starting around Labor Day?My middle daughter starts her Junior year of High School today, but my youngest doesn't start Middle school until next week. Middle daughter goes to High School out of district.
My nephew started school on Tuesday or Monday. He's in Omaha, Nebraska.
EDIT: Of course, I prefer starting early to having to stay in school later. I remember the starting around Labor Day and, if there were too many snow days, having to attend school till almost mid-June.
August 12, 2010 at 7:33 am
Memphis, TN, started school this week. I think the county schools started late last week.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 12, 2010 at 7:40 am
Paul White NZ (8/12/2010)
Table variables have none, it is assumed that they will have 1 row.
True, unless the OPTION (RECOMPILE) query hint is used. If that is the case, the recompilation at run time will have access to accurate cardinality information.
So if you want to use an index you generally have to use a hint.
What sort of hint did you have in mind? Index hints don't work with table variables, and you can't give the PRIMARY KEY or UNIQUE constraints names anyway!
Thats 2 things learnt today 🙂
August 12, 2010 at 7:40 am
Paul White NZ (8/12/2010)
Dave Ballantyne (8/12/2010)
So if you want to use an index you generally have to use a hint.What sort of hint did you have in mind? Index hints don't work with table variables, and you can't give the PRIMARY KEY or UNIQUE constraints names anyway!
Are you saying you can't give the PK & Unique constraints names on a table variable or names at all? It's been a while (so I'd have to look up the code), but I'm pretty sure I've given both constraints names on temp tables and user tables before.
August 12, 2010 at 7:41 am
Dave Ballantyne (8/12/2010)
Thats 2 things learnt today 🙂
Kwit larnin' things! That ain't what this here site be for!
😀
Viewing 15 posts - 17,401 through 17,415 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply