SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 T-SQL Enhancements Part - II


SQL Server 2008 T-SQL Enhancements Part - II

Author
Message
Arshad Ali-556241
Arshad Ali-556241
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 194
Comments posted to this topic are about the item SQL Server 2008 T-SQL Enhancements Part - II

With Thanks and Regards
Arshad Ali
Microsoft India

My Blog - http://arshadali.blogspot.com/
riix
riix
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 66
Nice summary. Probably the biggest disappointment with TVPs is how promising they are in concept but how limiting they are in practice. Its unfortunate that one needs to define a .NET data table by hand, to exactly match a TVP, instead of being able to just define a datatable as being of type of the TVP itself.

Its also a tremendous disappointment that Entity Framework does not support TVPs (even in .NET 4).

SQL Server developments often seem so disjointed. What I was really was hoping to see released was something more "object oriented" - to use that abused phrase - something like (a) to be able to define a table "type" (or "class" or "template" or whatever you want to call it), and (b) be able to create a table as of that type, and (c) be able to create a TVP as of that type, and (d) to be able to create an ADO data table as of that type, etc.

(Heck - make "MERGE" know about templates so that when its input is a table and a correspondlingly matched TVP, then it would automatically know how to do its field by field upsert ..)

Anyhow, one abstraction for a "data template definition" and many concrete implementation of that template. And EF supported ! Wouldn't that be nice?
Cbo Spikes
Cbo Spikes
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 36
Thanks for theses articles. I can't wait to start using TVP. I will see an immediate change in my web app structure. I already have found myself using strongly typed datatables more often for complex transactions despite the extra work. Now being able to insert in bulk instead of looping rows is a significant improvement. Enough so, that some of the refactoring I have been planning on doing has more value. I would love to read your enhancement summary on time zones. This also has some serious potential for considering app structure changes. Thanks again!
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43349 Visits: 14925
Nice article, I especially appreciated how you included the .NET example of passing a table type parameter. Many articles leave this peace out.

I would have appreciated a few more examples with the datetime datatypes.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
dpatel0501-696442
dpatel0501-696442
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 133
hi arshad,

I would appreciate if you can put down a piece of code to how it was implemented in SQL Server 2005 ...It can be either using Temp tables or many parameter. A simple example is needed.

Thanks,
D
Vlatko Ivanovski-465795
Vlatko Ivanovski-465795
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 41
In SQL 2005 you can use XML data Type to pass structured data to stored procedure, instead of TVP.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65053 Visits: 18570
Nice article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

fonHof
fonHof
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 177
I agree, XML is nice. I use XML in similar cases.
Finnlake
Finnlake
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 42
We are using TVP's in a call to a stored procedure. The proc takes three arguments of which all are TVP's. This functionality does not seem to scale at all, no
matter how many threads are calling the stored procedure, it always performs the same.
We removed all functionality in the proc but this made almost no difference.
It seems that the major time is spent in the passing of the TVP's from the .net dataaccess code to the stored procedure.
Could there be that there are some locks involved?
Is the TVP's not recommended if you call the stored proc at a high frequency?
Do we have to send batches instead to achieve any performance?
sanjeev.k
sanjeev.k
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 27
really gr8 ariticle :-)
but one thing I'd like to say order of columns must match in Table Type(DB) and Passing Table(.NET).
is there any solution for this. plz shere it.

Thanks,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search