|
Comments
|
|
Fantastic. I never thought to try that.
|
|
Dustin Ryan on
12/31/2008
Nice video, very informative.
|
|
Steve Harris on
12/31/2008
I did not know that you could do this. Very good tip.
|
|
Jack Corbett on
12/31/2008
I knew about the PK, but never thought of using a unique constraint.
|
|
|
I knew about adding a primary key - but I didn't know about the constraint! Definitely something to add to my bag of tricks.
|
|
|
I didn't know that!
|
|
|
I didn't know that!
|
|
|
Good tip to know.
|
|
|
able to create index on TV is cool. happy 2009.
|
|
|
When do the better query plans begin to pay back the cost of the constraints?
|
|
|
Satyabodhi, this depends on many parameters (data size, what type of queries you run, query complexity, etc.). In general if you have query predicates that utilize the underlying indexes you will see benefits. Best as always is to test with your data to see if it makes sense to use them.
|
|
|
Adding indexes to table variables will definitely come in handy.
|
|
|
Although I know this and use it a lot, it is very good. Many developers are unaware of this.
|
|
|
Straight and to the point. Could have added other index types or other constraints.
|
|
|
Ed, since you cannot run CREATE INDEX against a table variable, you cannot create other indexes. The techniques demonstrated in the video are based on the fact that PRIMARY KEY and UNIQUE KEY constraints are enforced via index.
|
|
|
Very good & Informative
|
|
|
Excellent video, never thought of using unique constraints before...
|
|
rupesh sharma on
2/16/2009
excellent video....very useful to implement this mostly i use in reporting services stored procs
|
|
|
pretty neat
|
|
|
Simple, good, conceptual and to the point. Thank you.
|
|
|
Very helpful, especially since a view of the before and after execution plans are included.
|
|
|
good
|
|
Todd Burrell on
11/12/2010
The first question was too tricky. I took it as you can't directly add indexes to table variables.
|
|
Philip Hickey on
11/12/2010
Thanks very much for putting this great video together - keep up the good work.
|
|
|
Another clever use of SQL that I hadn't thought about. Thanks.
|
|
Craig Pessano on
11/12/2010
Thanks for the video... I knew about PRIMARY KEY om table vars, but not UNIQUE index, it's good to know.
|
|
Kasey Wheeler on
11/12/2010
Good video!
|
|
Jamshid Nouri on
11/12/2010
excellent
|
|
|
good video in a very less time.
|
|
|
thick accent, but still speaks clearly - good speaker!
|
|
|
Great Tip!
|
|
|
very nice video
|
|
Juri Kirillov on
11/13/2010
good to know
|
|
|
cool trick
|
|
Peter Voutov on
11/14/2010
It might be good to mention if a unique key can include multiple columns. My guess is that it can't in the table variable statement.
|
|
|
I want ot know more about index
|
|
Plamen Ratchev on
11/15/2010
Peter, yes, the unique key can include multiple columns. Here is example: DECLARE @Foo TABLE (col1 INT, col2 INT, UNIQUE(col1, col2));
|
|
Kenneth Wymore on
11/15/2010
Good information. I wasn't aware that you could do this with table variables but it makes perfect sense since the creation statement is similar to creating a standard table.
|
|
|
nice
|
|
|
scripts would be good to have
|
|
Brent Wells on
12/16/2010
I like it
|
|
|
Great stuff
|
|
|
what an accent
|
|
|
excellent tip
|
|
|
Good one
|
|
|
Simple and precise.
|
|
|
I would like to say why is there a key nlookip? This is worst than a table scan.
|
|
|
Very good
|
|
|
Short and sweet. Love adding new tricks to my arsenal.
|
|
Dennis Allen on
6/14/2011
I would like more discussion on when the cost of creating the index on a table-variable presents a higher cost than running the query that uses it without an index. How to measure this and rule-of-thumb for knowing when to index and when not to index.
|
|
Dennis Allen on
6/14/2011
I would like more discussion on when the cost of creating the index on a table-variable presents a higher cost than running the query that uses it without an index. How to measure this and rule-of-thumb for knowing when to index and when not to index.
|
|
Dennis Allen on
6/14/2011
I would like more discussion on when the cost of creating the index on a table-variable presents a higher cost than running the query that uses it without an index. How to measure this and rule-of-thumb for knowing when to index and when not to index.
|
|
|
Great tip!
|
|
Paul McCurdy on
6/14/2011
Short & sweet
|
|
|
Well focused and good presentation
|
|
|
Nice tip, thank you!
|
|
|
Learned something new, thanks!
|
|
Keith Badeau on
6/14/2011
Great information. I didn't know that a table variable could contain a primary key. Thanks for all the videos.
|
|
|
Great tip!
|
|
|
excellent tip
|
|
|
I did not know this.
|
|
|
The title was a bit misleading. Yes, there are indexes used here, but I was looking to see if one could create a non-unique index here.
|
|
Matthew White on
6/16/2011
Genius! Best tip I have seen for a long time.
|
|
|
good
|
|
|
Great job!!
|
|
|
this is very practical, thank you
|
|
|
Dennis Allen, as usual with performance questions the best is to look at the execution plan. There is no rule of thumb, you have to test in your particular scenario and see what works best.
|
|
|
Direct and to the point. Excellent.
|
|
|
good,
|
|
|
I never tried this one! excellent
|
|
|
I Love it... - Domingo M. Asuncion
|
|
|
Great video. Just to note, if you make a subtle change you will get rid of the Key Lookup that takes a hit in "WHERE emp_nbr = 2"
If you create the PRIMARY KEY as clustered on the first col, leaving the 3rd col as a UNIQUE NONCLUSTERED - you can kill both birds with the same stone :)
Ex:
DECLARE @Beer TABLE (
beer_no int PRIMARY KEY CLUSTERED,
beer_name varchar(25),
beer_id varchar(5) UNIQUE NONCLUSTERED
)
INSERT INTO @Beer VALUES (1, 'Molson', '00001')
INSERT INTO @Beer VALUES (2, 'Labatts', '00002')
INSERT INTO @Beer VALUES (3, 'Guiness', '00003')
SELECT Beer_no, Beer_name, beer_id
FROM @Beer
WHERE beer_no = 3
SELECT Beer_no, Beer_name, beer_id
FROM @Beer
WHERE beer_id LIKE '%0002'
|
|
|
I think another important thing to note is when you use this method and are inserting a lot of data into the temp table...there is a small performance hit when inserting each row (because the indexes need to be updated)
|
|
|
Brilliant - I never knew that you could do that
|
|
Maurice Ivory on
11/9/2011
Good video
|
|
|
I find it really hard to understand what Plamen is saying
|