Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Naming Multi-Line Table-Valued Function Default Constraints Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 11:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:48 AM
Points: 318, Visits: 107
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
Post #1444058
Posted Thursday, April 18, 2013 1:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 12,901, Visits: 32,136
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1444117
Posted Thursday, April 18, 2013 1:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:48 AM
Points: 318, Visits: 107
...
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
Post #1444122
Posted Thursday, April 18, 2013 1:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 12,901, Visits: 32,136
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1444123
Posted Thursday, April 18, 2013 2:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 12,995, Visits: 12,414
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 Moden's 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)
Post #1444137
Posted Thursday, April 18, 2013 2:38 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:48 AM
Points: 318, Visits: 107
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
Post #1444144
Posted Thursday, April 18, 2013 3:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 12,995, Visits: 12,414
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 Moden's 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)
Post #1444154
Posted Thursday, April 18, 2013 3:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:59 PM
Points: 20,702, Visits: 32,342
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.



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)
Post #1444157
Posted Friday, April 19, 2013 8:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:48 AM
Points: 318, Visits: 107
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
Post #1444421
Posted Friday, April 19, 2013 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:13 PM
Points: 12,995, Visits: 12,414
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 Moden's 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)
Post #1444432
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse