SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Naming Multi-Line Table-Valued Function Default Constraints


Naming Multi-Line Table-Valued Function Default Constraints

Author
Message
MKEDataGuy
MKEDataGuy
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 108
I want to be able to name the default constraints that I place on the table returned from a multi-line table-valued function, however SQL Server doesn't appear to support naming the constraint in this scenario. I want to be able to do this so that schema comparisions don't get false positives when comparing the names given to these default constraints. Even though you can't specify them, SQL Server does give names to these default constraints.

I'd like to be able to do something like what's in bold below:

CREATE FUNCTION [dbo].[GetElementList] 
(
@color smallint
)
RETURNS
@elementList TABLE
(
PartName VARCHAR(50),
Color VARCHAR(50),
Active INT CONSTRAINT DF_GetElementList_Active DEFAULT 0
)
AS
BEGIN
...
Body removed for brevity.
...
END



Does anybody know a way to do this, or a way to acheive the same end result?

Thanks,
MKE Data Guy
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69598 Visits: 40917
for table variables, you can create unique constraints, but you cannot name them.

i don't think they allow default or check constraints either.

only temp tables or regular tables will allow a named constraint in the definitions.

this was the first link i found stating the same:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=131034

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
MKEDataGuy
MKEDataGuy
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 108
...
i don't think they allow default or check constraints either.
...

Lowell, thanks for the reply.

You can create default constraints, but it doesn't appear that you're allowed to name them. It's the naming part that I'm looking to accomplish.

Actually, right as I was notified of your reply, I was testing out using sp_rename to change the system-generated name to something more standardized. Every indication at this point is that this will work. I just need to work out how to do this on a whole database without hard-coding names.

Thanks,
MKE Data Guy
Lowell
Lowell
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69598 Visits: 40917
just double checked, and i can create default constraints, but no check constraints on a table variable:

declare @mytable table
(id int identity(1,1) not null primary key,
name varchar(30) unique,
employee char(1) default ('Y') )



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60827 Visits: 17954
Knowledge Draftsman (4/18/2013)
...
i don't think they allow default or check constraints either.
...

Lowell, thanks for the reply.

You can create default constraints, but it doesn't appear that you're allowed to name them. It's the naming part that I'm looking to accomplish.

Actually, right as I was notified of your reply, I was testing out using sp_rename to change the system-generated name to something more standardized. Every indication at this point is that this will work. I just need to work out how to do this on a whole database without hard-coding names.


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?

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
MKEDataGuy
MKEDataGuy
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 108
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.

Thanks,
MKE Data Guy
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60827 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92339 Visits: 38954
Looking at the CREATE TYPE statement in MSDN I don't see where it allows you to name a column constraint like you can with the CREATE TABLE statement.

Your statndards my require default contraints to be named on tables, but it is quite possibile that this won't be possible on table types, nor do I think you would want to. It may cause issues if you use the same table type more than once in a given procedure or function. I also do not see a benefit to trying to naming a default constraint on a multi-statement table valued function.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
MKEDataGuy
MKEDataGuy
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 108
So, here's what I've come up with...

SET NOCOUNT ON;

DECLARE @MisnamedConstraints TABLE
(
OldConstraintName SYSNAME,
NewConstraintName SYSNAME,
ParentObject SYSNAME,
ColumnName SYSNAME,
ConstraintDefinition NVARCHAR(Max)
)

-- Find all constraints on multistatement table-valued functions where the
-- constraint name doesn't match the standard.
INSERT INTO @MisnamedConstraints
SELECT
DC.[name] 'Constraint Object',
'DF_' + O.[name] + '_' + C.[name] 'Standards Compliant Constraint Name',
O.[name] 'Parent Object',
C.[name] 'Column Name',
DC.[definition] 'Constraint Defintion'
FROM [sys].[default_constraints] DC
INNER JOIN [sys].[objects] O
ON DC.[parent_object_id] = O.[object_id]
INNER JOIN [sys].[columns] C
ON C.[object_id] = DC.[parent_object_id]
AND C.[column_id] = DC.[parent_column_id]
WHERE O.[type] = 'TF'
AND DC.[name] <> 'DF_' + O.[name] + '_' + C.name;

-- If there are naming collisions, remove them and output them for further research.
-- It is possible that a naming collision will be cleared by this script, and that
-- running this script a second time will not result in a collision.
DELETE MC
OUTPUT
DELETED.OldConstraintName,
DELETED.NewConstraintName,
DELETED.ParentObject,
DELETED.ColumnName,
DELETED.ConstraintDefinition
FROM [sys].[default_constraints] DC
INNER JOIN @MisnamedConstraints MC
ON DC.[name] = MC.[NewConstraintName];

-- Loop through all the non-colliding constraints and rename them to
-- the standards compliant name.

DECLARE @OldConstraintName SYSNAME;
DECLARE @NewConstraintName SYSNAME;
DECLARE @OBJECT_TYPE VARCHAR(13); SET @OBJECT_TYPE = 'OBJECT';

SELECT
@OldConstraintName = OldConstraintName,
@NewConstraintName = NewConstraintName
FROM @MisnamedConstraints;

WHILE @@RowCount > 0
BEGIN

EXECUTE sp_rename @OldConstraintName, @NewConstraintName, @OBJECT_TYPE;

DELETE
FROM @MisnamedConstraints
WHERE OldConstraintName = @OldConstraintName;

SELECT
@OldConstraintName = OldConstraintName,
@NewConstraintName = NewConstraintName
FROM @MisnamedConstraints;

END



I've done some preliminary testing and things appear to function fine after the rename.

Thanks,
MKE Data Guy
Sean Lange
Sean Lange
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60827 Visits: 17954
That is certainly interesting. I still think this is way over the top worried about naming conventions. This will be difficult to maintain when you have to update your procs and such.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search