tag:blogger.com,1999:blog-1484302378448571270.post4789450895141209293..comments2023-11-11T16:27:52.347-08:00Comments on New York City .NET: Another Way To Insert Many Rows Very Fast From Your .NET ApplicationOrdinaryUserhttp://www.blogger.com/profile/00208807533414926596noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-1484302378448571270.post-37312824456626804802014-11-28T05:00:37.194-08:002014-11-28T05:00:37.194-08:00I've had pretty good luck with hybrid TVPs tha...I've had pretty good luck with hybrid TVPs that define the table type like this:<br /><br />CREATE TYPE dbo.IntWithPayload AS TABLE <br />(<br /> ID INT NOT NULL,<br /> Payload XML NULL,<br /> PRIMARY KEY (ID)<br />);<br /><br />The nice thing is that this allows a flexible payload while still allowing SQL Server to do fast joins since the ID column must be unique and already sorted. Plus, this doesn't require SQL Server to shred an XML document representing your entire data structure in one go - it can shred each individual row separately as it's about to do the insert/update/delete operation. This seems to be a lot less stressful for SQL Server versus having it shred a potentially very large document as you go beyond a few hundred rows. This is probably worth another blog post.OrdinaryUserhttps://www.blogger.com/profile/00208807533414926596noreply@blogger.comtag:blogger.com,1999:blog-1484302378448571270.post-72849081919198291292014-11-19T20:28:46.545-08:002014-11-19T20:28:46.545-08:00I can show you how I made a single xml proc for al...I can show you how I made a single xml proc for all types. It's completely generic :-) <br />Is there a way to have that with TVPs? toddmohttps://www.blogger.com/profile/18102486213069100450noreply@blogger.comtag:blogger.com,1999:blog-1484302378448571270.post-28949677867546384172012-05-16T20:09:18.556-07:002012-05-16T20:09:18.556-07:00+1 for TVPs
Nice write up!+1 for TVPs<br /><br />Nice write up!opc.threehttps://www.blogger.com/profile/06230899142614692571noreply@blogger.com