Is it a good idea to use a UDDT + Rules for this?

Question

Is it a good idea to use a UDDT + Rules for this?

kpwimberger

Ten Centuries

Points: 1110
More actions
January 15, 2015 at 10:34 am

#296609

Our company is implementing a parent -> child unique ID system. The IDs are formatted like so:
The 1st digit can be 3-9
The remaining digits can range from 0-9.
Would you all suggest creating a User Defined Data Type with Rules attached to enforce format? I like this idea because I can implement it at the DB level. If I create the type in the Master DB I can cascade it through all other DBs that may need it.
But I have never used a UDDT and would like to know about any possible pitfalls before starting.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27056 More actions · Answer 1

Why are you making up some sort of artificial value?

What does 3-9 in the first digit stand for? If this stands for something like a "parent type", this should likely be in a lookup table.

What do the second set of numbers stand for? Again, if they are there to define the child or parent, then this should be stored as an attribute, not a part of a number.

Do you realize what kind of a WHERE clause you will need if you need to filter by these "types"?

Does this make sense?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

kpwimberger Ten Centuries Points: 1110 More actions · Answer 2

Sadly the decision to use this ID structure was made 12,546 miles above my pay grade, so I can't really tell you why the numbers were structured that way. But that's what I'm stuck with.

But I am very interested in your comment about the WHERE clause. That's exactly the type of thing I need to be thinking of. As I have never used a UDDT before, can you tell me how their use might affect a WHERE clause, vs. how VarChar(x) would? I'll also research that as well.

Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 3

the decision on needing some format was made above your head, but the implementation is where you have some flexibility.

you can still use a foreign key to a parent table, and an identity or UDF function for a default value, and then create a calculated column which appends the parentId + function vale as a persisted calculated field.

/*

ChildId ParentID UniqueID Descrip

----------- ----------- ------------------------------------ ------------------------------

1 3 3000001 Computers

2 4 4000002 Books

*/

CREATE TABLE Parent (ParentId INT primary key,Descrip varchar(30))

insert into Parent(ParentId,Descrip)

SELECT 3,'Finance' UNION ALL

SELECT 4,'PayRoll' UNION ALL

SELECT 5,'Program Managers' UNION ALL

SELECT 6,'Tech Support'

CREATE TABLE Children(ChildId int identity(1,1) not null primary key,

ParentID int NOT NULL REFERENCES Parent(ParentId) ,

UniqueID AS CONVERT(varchar,ParentID) + RIGHT('000000' + CONVERT(varchar,ChildId),6),

Descrip varchar(30) )

INSERT INTO Children(ParentID,Descrip)

SELECT 3,'Computers' UNION ALL

SELECT 4,'Books'

select * from Children

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!

kpwimberger Ten Centuries Points: 1110 More actions · Answer 4

Thanks Lowell. Yup, implementation is up to me.

For starters, I always use autogenerated PKs anyway, and won't be losing those. I just need to record the two different IDs. The PK will be their actual behind-the-scenes link, so I'm not actually worried about that.

My question, I suppose, has more to do with UDTs in general. I can store the IDs as VarChar(n), or create a UDT and add a Rule to it. But what happens in WHERE and ORDER BY clauses with the UDT? Is it treated like the underlying data type? Is extra processing required? Is it worth creating a UDT or just leave it as VarChar(n)?

Michael L John One Orange Chip Points: 27056 More actions · Answer 5

My question, I suppose, has more to do with UDTs in general. I can store the IDs as VarChar(n), or create a UDT and add a Rule to it. Yes, this works.

But what happens in WHERE and ORDER BY clauses with the UDT? Is it treated like the underlying data type? Yes again.

Is extra processing required? Not sure...

Is it worth creating a UDT or just leave it as VarChar(n)?

I do not use UDT's for one reason. If the underlying type ever has to change, they need to be dropped and re-created.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

kpwimberger Ten Centuries Points: 1110 More actions · Answer 6

Good point Michael. So far, everything I am seeing in my searches seems to indicate I can very easily do without a UDT. I am thinking I may avoid them.

Kurt

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 7

I like that you're keeping a surrogate key. Good idea.

I like Lowell's thoughts on computed columns. While not specifically doing this, I have done similar things in the past and those on high often come back later and change things when they realize the folly of their current edict. A computed column might make things easier to change.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Steve Jones - SSC Editor (1/16/2015)
I like that you're keeping a surrogate key. Good idea.
I like Lowell's thoughts on computed columns. While not specifically doing this, I have done similar things in the past and those on high often come back later and change things when they realize the folly of their current edict. A computed column might make things easier to change.

I agree 10000%. My guess is that each portion of this derived value is some sort of indication that these segments actually mean something. That suggests that this derived value is a violation of 1NF. Something like the first value is the customer region, the second value means something else so in order to split these apart means you have to jump through hurdles again. I was immediately thinking about a computed column as a great solution to this.

_______________________________________________________________

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/

kpwimberger Ten Centuries Points: 1110 More actions · Answer 9

Steve and Sean:

Believe it or not they actually REMOVED any meaningful data from the ID structure before approving them. These IDs are similar to GUIDs in their uniqueness, but are based off of a different system. And they have adopted these enterprise wide so I'm not too worried that they will change them much for the next few years. (Job security? 🙂 )

I think I am going to steer clear of UDTs for now. It doesn't seem there's enough reason to go that route on this.

And Steve, I *NEVER* give up my keys! 🙂

Kurt