Knowledge Draftsman (4/18/2013)
I think that would introduce concurrency issues unless you get really creative with the naming here. I don't understand why you care what the name of a default constraint is on a table variable. How is that useful?
First, we have a naming convention that should produce a unique name for each default constraint.
Second, it's not the default on a table variable. It's the default on a table type; a small but very important difference. If this were just for a table variable that was declared somewhere in the code and then disappeared once the code was done executing, we wouldn't care. But in a multistatement table-valued function, you have to define the table type that the function returns. That type definition is persisted by SQL Server in a couple different system tables. The constraint created as part of the type definition we do care about, because we'd like those constraint names to follow the naming standard.
I would say the difference really isn't that small. 🙂
It seems like a lot of effort with little reward to me because you will have to make sure you have sp_rename either in your code or part of the process everytime you update one of these functions. I would however be interested to see what you came up with.
_______________________________________________________________
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/