June 7, 2013 at 1:51 pm
Table Value Constructors.... I've been trying to do some research and some tests and I haven't come across anything that tells me why I should use a TVC over anything else I normally use in T-SQL constructs.
Can anyone tell me what, if any, benefits or uses TVCs fulfill aside from being able to specify multiple value row constructors for Merge and Insert statements? I mean when would you specifically use a TVC over another SQL object?
Erin
June 7, 2013 at 2:08 pm
Erin Ramsay (6/7/2013)
Table Value Constructors.... I've been trying to do some research and some tests and I haven't come across anything that tells me why I should use a TVC over anything else I normally use in T-SQL constructs.Can anyone tell me what, if any, benefits or uses TVCs fulfill aside from being able to specify multiple value row constructors for Merge and Insert statements? I mean when would you specifically use a TVC over another SQL object?
Erin
You can also use them in a derived table.
select * from
(Values ('asdf', 'qwer', 'fidos'),
('fgjh', 'rtuy', 'ljoghi')
) as MyTable(col1, col2, col3)
For the most part they are just another way of creating a table on the fly with a LOT less keystrokes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2013 at 2:36 pm
Thanks, Sean..
I can use them to generate 20,000 9 digit numbers too...
select * from (values ('4'),('5'),('6')) as Ssn(dig)
cross apply
(select * from (values ('1'),('2'),('3')) as Ssn(dig)) dig2
cross apply
(select * from (values ('1'),('2'),('3')) as Ssn(dig)) dig3
cross apply
(select * from (values ('0'),('1'),('2')) as Ssn(dig)) dig4
cross apply
(select * from (values ('4'),('5'),('6')) as Ssn(dig)) dig5
cross apply
(select * from (values ('1'),('2'),('3')) as Ssn(dig)) dig6
cross apply
(select * from (values ('8'),('9'),('0')) as Ssn(dig)) dig7
cross apply
(select * from (values ('6'),('7'),('8')) as Ssn(dig)) dig8
cross apply
(select * from (values ('0'),('1'),('2')) as Ssn(dig)) dig9
and use that as a datasource for my transformations.. but WHY?
It's not more performant.
If I have to dynamically create it, it's more cumbersome.
It's not a reusable dataset in its current form.
Seems like a way of creating a really inferior object on the fly..
June 7, 2013 at 2:49 pm
I agree. The real world usage is pretty limited. But when you find yourself in the position of creating a dataset on the fly it can be a nice shortcut. One great example of using it successfully is in creating an "on the fly" tally table.
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
That certainly beats the select - union all method.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 8, 2013 at 1:15 pm
I wrote a little blog on this a few weeks ago:
Hope this helps
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy