November 19, 2014 at 11:06 am
Sean Lange (11/19/2014)
Igor Micev (11/19/2014)
Sean Lange (11/19/2014)
Igor Micev (11/19/2014)
Sean Lange (11/19/2014)
Great question on a great topic that many people don't know about.It makes sense if you think about it. How would the 208 database know that the parameter is in fact the correct datatype because it is defined in the 2014 database. It makes sense really because this is a complex user defined datatype and there is no guarantee that just because the name is the same that the definition would be. Following that same logic you would have to be able to use different names across databases and pass in the user defined table. Quirky for sure but also not something that would be able to make it work correctly.
Good notice.
First. Actually I meet the problem on sql 2014 databases, so omit the difference of the sql server versions. It persists when you try on databases in a same instance.
Second. The definitions of the TVP types are identical and by name as well. I'm also giving an example with UDT of INT type and it works for it, but not when the UDT is TABLE.
Yes I know it won't work even on the same instance. The concept is the same. The datatype is not a simple datatype like int. Remember that all that really happens when you use a UDT type is it sets some properties on the column. To really make that interesting you should try it with different datatype names for int across those databases...it still works just fine.
When you are talking about a table type it is much more complex than that and would require a LOT of effort from the parser to ensure the datatypes are in fact the same.
Correct. The first think i'm thinking of is the collation for the char columns in the table type. I accept it must be very restrictive regarding all conditions for the parser. However under all conditions equal it should work.
Ok, thanks.
I remember reading somewhere that the sql team decided it was too resource intensive (meaning poor performance) for this type of conditional checking so they decided to not include this functionality. I can see how it would be a performance hog to check. The engine would have to make sure that a number of things are equal before it would be able to proceed and that checking is what could really bog things down. I will see if I can find that article again.
I'm curious to take a look at it, thanks in advance.
Igor Micev,My blog: www.igormicev.com
November 20, 2014 at 1:05 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 20, 2014 at 2:50 am
Igor Micev (11/19/2014)
serg-52 (11/19/2014)
Thanks, good question.I was not sure but rather had an impression that sending parameter of the UDT declared in the current DB to the proc in the other DB must fail. UDT are DB-scoped types, aren't they?
P.S.
Wonder if BOL has any other topic besides CLR UDTs that states " UDTs are scoped to a single database, and cannot be used in multiple databases ..."
http://msdn.microsoft.com/en-US/library/ms131079(v=sql.100).aspx
That was the reason for putting this question here. UDT as TABLE cannot be used in different DB-scope, and UDT as INT type (given in explanation), for example, can be used.
Thanx 4 the question & the info.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
November 20, 2014 at 5:33 am
Yeah, that we a good one and a very good explanation. Thanks.
November 20, 2014 at 5:50 am
Interesting question and great helpful suggestions!
November 20, 2014 at 6:39 am
Thanks for the question.
November 20, 2014 at 8:12 am
Good question, Igor, and very interesting. Thanks!
November 20, 2014 at 11:44 am
A nice fun question, required a bit of careful thought to get it right but not really difficult if one remembers that the scope of a UDT is the database.
Tom
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply