Post reply

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

  • 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

  • January 15, 2015 at 10:55 am

    #1770523

    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/

  • January 15, 2015 at 11:12 am

    #1770525

    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!

  • January 15, 2015 at 12:14 pm

    #1770536

    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!

  • January 15, 2015 at 12:32 pm

    #1770544

    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)?

  • January 15, 2015 at 2:04 pm

    #1770568

    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/

  • January 15, 2015 at 2:29 pm

    #1770571

    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

  • January 16, 2015 at 10:39 am

    #1770833

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 16, 2015 at 10:59 am

    #1770837

    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/

  • January 16, 2015 at 12:37 pm

    #1770864

    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

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

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