StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I faced a similar situation and I found that UDT's worked pretty much perfectly. We started with a very similar problem : "get data for this account" and then it became "get data for these accounts", then "with these criteria" etc. We used UDT's instead of passing XML strings - once you get into an SP, you can join directly off of the UDT, and UDT's are supported by ADO.NET, so it's nice and simple. We were passing in hundreds of thousands of rows into our SP's from the UDT's (massive upserts) and performance did not become an issue with one exception: don't ever try to trace a query when you're sending that many rows in - the thread scheduler inside SQL server will explode.</p> <p>One thing to be wary of when using user define table types: for some reason Microsoft thought it would be a good idea to prevent you from changing them, you can only drop / add them. Then someone else thought it would be even better to prevent you from dropping them if something depends on them, so you wind up with a very painful process to drop / reconsistute them if you change them if you do it by hand.</p> <p>We didn't encapsulate all params into a single UDT only because our needs were more specific from procedure to procedure. So when we had lists of things, we used a UDT for that parameter, but I could easily see One UDT To Rule Them All being useful, with a few convenience functions to pull out well-known values like dates. I despise writing the same code multiple times, and this would definitely shrink your codebase at a minor cost to increased complexity. A side benefit would be forcing all developers to stick to a standard way of doing things, which is desireable not always enforced when crunch-time hits. You would also open up some nice opportunities within your data layer for code reuse.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload