November 18, 2014 at 11:42 pm
Comments posted to this topic are about the item Table valued parameter
Igor Micev,My blog: www.igormicev.com
November 19, 2014 at 12:42 am
Thank you for the post, really interesting one.
Neven knew this and had to guess and guessed it wrong. I used tvp but did'nt tried on multiple database level. Learnt interesting stuff today. 🙂
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
November 19, 2014 at 2:47 am
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
November 19, 2014 at 3:26 am
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.
Igor Micev,My blog: www.igormicev.com
November 19, 2014 at 5:38 am
This was removed by the editor as SPAM
November 19, 2014 at 6:33 am
Good question! I knew about the case that a Table UDT is database specific, but I couldn't for the life of me decide which database would work and which would succeed, since I didn't see which database was creating the stored procedure. If only I had scrolled a little bit further down and read that it was being run from the AdventureWorks2014!
November 19, 2014 at 7:04 am
Thanks for the great question Igor. I learned something today. 😀
November 19, 2014 at 7:17 am
Interesting question, thanks for submitting!
November 19, 2014 at 8:29 am
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2014 at 9:24 am
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.
Igor Micev,My blog: www.igormicev.com
November 19, 2014 at 10:06 am
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2014 at 10:40 am
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.
Igor Micev,My blog: www.igormicev.com
November 19, 2014 at 10:57 am
Nice question Igor! I had no idea as I have never used these UDTs before.
November 19, 2014 at 11:01 am
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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply