March 2, 2014 at 9:50 pm
Comments posted to this topic are about the item The SQL TABLE Type: Beyond Table-Valued Parameters
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 3, 2014 at 1:14 am
Nice article Dwain. Keep them coming.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 3, 2014 at 1:18 am
Thanks for reading it Wayne. Comments from a master such as yourself are always most welcome.
BTW. I think we're tied again on article count. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 3, 2014 at 4:45 am
Thanks.Great article.
so now can do DML operation inside function using TVP.
so database state can be change inside function .
[font="Verdana"]Regards
Kumar Harsh[/font]
March 3, 2014 at 11:55 am
I think it's worth noting (for those new to the concept) that a table type with the same exact definition on the same server in two different databases are not equivalent.
For example if you can call a procedure like so:
[USE testEnvironment]
Declare @test-2 typParam;
Insert into @test-2
--Select something here--
exec dbo.usp_DoSomething @tableParam = @test-2
The same will not work:
[USE otherEnvironment] --Notice we're in a different environment
Declare @test-2 typParam; -- this is defined the same way, but in a different db
Insert into @test-2
--Select something here--
exec testEnvironment.dbo.usp_DoSomething @tableParam = @test-2 --this errors
March 3, 2014 at 4:41 pm
pandeharsh (3/3/2014)
Thanks.Great article.so now can do DML operation inside function using TVP.
so database state can be change inside function .
I'm not sure why you're drawing this conclusion from the article because it is not true. You cannot change the state of the database within a FUNCTION.
You've always been able to define table variables in a FUNCTION and populate them with data. That is not changing the state of the database.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 3, 2014 at 4:46 pm
eg61088 - Good input and thanks for reading.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 3, 2014 at 11:39 pm
oh sorry .i should have tested myself in machine before posting .
Actually ,
DECLARE @T dbo.MyTable;
INSERT INTO @T (Value) VALUES(6),(4),(10),(11);
confused me.Now its clear .I thought this will directly insert into table also .
[font="Verdana"]Regards
Kumar Harsh[/font]
March 4, 2014 at 4:13 pm
Hey Dwain,
Excellent usage explanation article, thanks for that.
The issue with table parameters is the big reason you'd want to use one, cross database or cross server data table pollination, fails. Specifically for the reason mentioned above. Those table definitions are labeled by some kind of internal ID and it fails when you try to pass exactly the same characteristics. This is possibly my biggest let down with TVP's. Anything you can do, T-SQL wise, with TVP's you can do with inherited Temp Tables in nested procedures.
It helps tremendously with ORM integration, however, for what that's worth, as the .NET layer can call up the table type to insert things into for passing to the database.
Dwain, did you do any optimization comparisons for the different methods? Knowing that TVPs are Table Variables with the peculiarities that come with them, did you find any particular rule of thumbs to know when to switch off the TVP and into a more conventional method, such as unpacking XML into a prepared document?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 4, 2014 at 5:19 pm
Evil Kraig F (3/4/2014)
Hey Dwain,Excellent usage explanation article, thanks for that.
The issue with table parameters is the big reason you'd want to use one, cross database or cross server data table pollination, fails. Specifically for the reason mentioned above. Those table definitions are labeled by some kind of internal ID and it fails when you try to pass exactly the same characteristics. This is possibly my biggest let down with TVP's. Anything you can do, T-SQL wise, with TVP's you can do with inherited Temp Tables in nested procedures.
It helps tremendously with ORM integration, however, for what that's worth, as the .NET layer can call up the table type to insert things into for passing to the database.
Dwain, did you do any optimization comparisons for the different methods? Knowing that TVPs are Table Variables with the peculiarities that come with them, did you find any particular rule of thumbs to know when to switch off the TVP and into a more conventional method, such as unpacking XML into a prepared document?
Hey Craig, thanks for stopping by!
I've not had the opportunity to test TVPs across databases so I wasn't aware of the restriction or I would have mentioned it in the article.
As to your last question, Erland Sommarskog covers it pretty well in the link I suggested you bookmark in the first paragraph of the article, so I didn't want to rehash it here. I did some myself in SQL 2005 back when I needed it. Of the three methods I mentioned (bulleted near the start of the article), if memory serves me correctly the last one seemed to perform the best. I believe at that time I also knew about and was using DelimitedSplit8K for the first. That first one probably lost though due to the overhead of creating the delimited list in the first place.
Possibly of interest, some of the SPs that I wrote were capable of handling all 3 of those approaches. My thinking there was that in some cases one might be better than the others. I would simply have 3 arguments (each being potentially the same table) and the one that was not NULL would be used. While this did add some complexity to the code in the SP, I found that the code block itself was highly reusable with just a few relatively minor tweaks.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 4, 2014 at 5:20 pm
pandeharsh (3/3/2014)
oh sorry .i should have tested myself in machine before posting .Actually ,
DECLARE @T dbo.MyTable;
INSERT INTO @T (Value) VALUES(6),(4),(10),(11);
confused me.Now its clear .I thought this will directly insert into table also .
Glad you worked that out. It would've pained me to think I led someone astray.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 5, 2014 at 5:00 am
Great article indeed! I only problem with TVP is that Microsoft didn't include an ALTER TYPE. If I need to alter a TVP, I need to drop it and recreate it. Such a hassle.
March 5, 2014 at 5:07 pm
louie1487 78804 (3/5/2014)
Great article indeed! I only problem with TVP is that Microsoft didn't include an ALTER TYPE. If I need to alter a TVP, I need to drop it and recreate it. Such a hassle.
Very true. Perhaps they're saving it for an enhancement in a future version of SQL Server.
Thanks for listening!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply