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