SQL Server Performance

Urgent Help: Default Values (Int=>Char)

Discussion in 'General Developer Questions' started by gracie, Apr 28, 2003.

  1. gracie New Member

    Hello,

    I have just started using SQL SERVER 2000 and I need urgent
    help on setting default values.

    Lets say I have 2 fields:
    1.AID Integer (Identity)
    2.ACHAR Character (6)

    I'd like the default value of ACHAR to be a string like
    'E00001', 'E00002', 'E00003'... where the digit part of the
    string comes from AID.

    I need this character based ID because it is an official ID
    (e.g. Employee ID).

    How I can I have this done most efficiently is SQL SERVER? I seem not be
    able to do an inline default value using any combination of
    the str, ltrim, convert, cast functions.

    I have used ORACLE extensively in the past.
    This was a breeze using nextval etc

    Can someone tell me how to do this in SQL SERVER?
    Thanks a million for your help..
    Gracie




    Many many thanks for your help.
  2. gaurav_bindlish New Member

    Set ACHAR to be NULL. Create a trigger on the table which will select the value of @@IDENTITY after a record id inserted into the table and format the same into ACHAR value.

    E.g.

    Create Trigger ... on tblXXX
    for insert
    as
    update tblXXX set ACHAR = 'E0000' + Convert(char, @@IDENTITY)
    where AINT = inserted.AINT

    I guess AINT can also be used here in place of @@IDENTITY. Also I haven't wrote the logic of handling the less no. of 0's in the 'E0000' string as identity keeps on increasing. I think u can manage that.

    One more request, post non-performance queries in appropiate forum.



    Gaurav
  3. bambola New Member

    That can be easily done with a calculated column.
    Create a calculated column and set it to

    'E' + RIGHT('00000' + convert(varchar, @AID),5)

    If the 'E' changes, consider putting it in a column.

    Bambola.
  4. gracie New Member

    I am terribly sorry guys but I tried both options and neither worked.

    Not sure what I may be doing incorrectly.



    Many many thanks for your help.
  5. Negative New Member

    Bambola's method should work fine, and it probably the easiest way to do this. I imagine your problem was putting @AID instead of just AID. You don't want to specify a variable, just a field name.
  6. bambola New Member

    ops... I was testing it with a variable and I did not remove the "@"
    Try now:

    E' + RIGHT('00000' + convert(varchar, AID),5)

    Bambola.
  7. gracie New Member

    I realise at some point today after re-reading the articles that I didn't really know what a calculated column was. I eventually figured it out by putting the expression in the formula field. Works beautifully.
    However, I cannot set aprimary key on that field because of it's new definition.

    This ID is used in various other tables and for various other references (since it is an HR app). So it has to be the parent key for a lot of other tables.

    Maybe now I need guidance cause it seems as though the solution using the trigger would be better. But I am not getting success with the trigger either. So please get me thru this. It will solve the problem for many other tables. I am really grateful for this timely help.



    Many many thanks for your help.
  8. gaurav_bindlish New Member

    I am posting the right syntax for trigger now....

    create table XXX (AID int identity(1,1), achar char(6))

    create trigger trig_xxx on XXX for insert as
    update XXX set ACHAR = 'E0000' + Convert(char, @@IDENTITY)
    from inserted i
    where XXX.AID = i.AID

    Write the logic for reducing the number of 0's as AID incerses.

    HTH.

    Gaurav
  9. Negative New Member

    I am sorry about not mentioning the fact that the code goes in the formula field, I had meant to mention that but accidentally left it out of my previous response.

    My only concern about the trigger method is wether it will allow you to insert the row without the primary key and let the trigger fill it?

    Since you have an identity increment on the AID field anyway, why not just make that the primary key? Then you should be able to create a unique constraint on the ACHAR (calculated) column. With the constraint on that column you could use it in relationships with other tables.

    If you decide to continue with the calculated column method, here is the syntax for creating a unique contraint:

    ALTER TABLE <tablename>
    ADD CONSTRAINT <constraintname> UNIQUE (ACHAR)


    I hope this helps, if not let us know.
  10. bambola New Member

    Gaurav - I cannot understand why do you insist on using a trigger when the task you are describing can be easily done with a calculated column, avoiding the overhead of triggers.

    - the logic for reducing number of zeros is very simple:

    say you need a string of 6 characters
    concat to the left of your number (or string) 6 zeros

    SELECT '000000' + '34' -- you can use replicate('0',6)
    now cut the right 6 characters from the string. In this particular case you need to
    add the 'E' to the beginning, but all the rest is the same.

    DECLARE @next_id int
    SELECT @next_id = 45
    SELECT 'E' + RIGHT('00000' + convert(varchar, @next_id),5)
    Gracie - here is another way to generate this code. Basically it is a way to generate an "identity" column, then as described above generate the PK.
    Create a table with 2 fields:

    column_name varchar(20)
    id_value int

    write a stored procedure that accept column_name and returns the next id_value .
    Before inserting to your table, call this stored and get the next id.



    CREATE PROCEDURE get_next_id
    (
    @column_name varchar(20)
    )
    AS
    DECLARE @next_id int
    SET @next_id = -2

    -- getting the current value from table into @next_id then incrementing it.
    UPDATE id_tables
    SET @next_id = id_value = id_value + 1
    WHERE column_name = @column_name

    IF @@ERROR <> 0 or @next_id < 0
    whatever....

    RETURN (@next_id )


    -- now with the @id_value you can generate the PK then insert into your table.
    SELECT @generated_pk = 'E' + RIGHT('00000' + convert(varchar, @next_id),5)

    If it still doesn't work from you, please let me know.

    Bambola.
  11. gaurav_bindlish New Member

    My understanding goes like this....

    Index can not be created on a computed column whose value is undeterministic. I gues this is the case here and the requirement is to have an index on this.

    If I am wrong, please correct me. This is why I insisted on using a trigger in place of computed column.

    Gaurav
  12. Negative New Member

    In this instance you could create an index on the computed column. I am not sure exactly what limits when you can put indexes on computed columns, but I tested it with unique, unique clustered, and non-unique indexes, and it will work as described above.
  13. gaurav_bindlish New Member

    Thanks for clearing the confusion

    Gaurav
  14. gracie New Member

    Hi Guys,

    Sorry for the delay. Very busy for two days. Now going to analyse and test all the options that you have presented and figure out which one would work best for me.

    One thing that I should say is that the database works perfectly with the configuration that Gaurav presented using stored procedures. However, this is in a native VFP database using a NextID table which temporarily locks the table to give a new ID.

    Now I want to upsize this database to MSS so that I could do more complicated queries with it and harness the power. This whole ID generation thing I didn't think would cause me such grief :-/. The calculated column is the simplest solution except I cannot make a primary key out of it.

    You guys are helping a great lot. Really appreciate it. Now I'll try to figure out which works best. I'll tell you what happened.

    Gracie

    Many many thanks for your help.
  15. gracie New Member

    Ok Guys,

    Here goes. I have something I am working on....my first stored procedure. Excited. It just this second worked so I tell right away.

    My test bed.

    I have an ID table with this structure. Rationale? I characterise my keys as visible or invisible to the average user. Invisible keys can be those hidden identity keys that are just there to maintian proper updates. Visible keys are those that print on reports and are used as a reference in the real world and need a table that defines their size and how they look etc. hence the field_names key_prefix, key_size.

    ***** this is the key table....
    CREATE TABLE [atabid] (
    [key_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [key_value] [bigint] NOT NULL ,
    [key_prefix] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [key_size] [int] NOT NULL
    ) ON [PRIMARY]
    GO

    ***** these are the two data tables to play with
    CREATE TABLE [atab] (
    [atab] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [atabtext] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_atab] PRIMARY KEY CLUSTERED
    (
    [atab]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    CREATE TABLE [atab2] (
    [atab2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [atab2text] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_atab2] PRIMARY KEY CLUSTERED
    (
    [atab2]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    ****** stored procedure ******
    CREATE PROCEDURE sp_GetNextKey
    @KeyTable varchar(100)
    AS
    BEGIN
    DECLARE @RetVal varchar(10)

    UPDATE atabid
    SET Key_value = Key_Value + 1,
    @RetVal = Key_Prefix + RIGHT(replicate('0',Key_Size) + convert(varchar(10), Key_Value),Key_Size)
    FROM atabid
    WHERE Key_Name = @KeyTable

    RETURN (@RetVal )

    END
    GO

    ********* things outstanding *******

    1. I have not yet placed the stored procedure as a default value. But I have tested it on its own.
    2. I am getting an error.
    Server: Msg 245, Level 16, State 1, Procedure sp_GetNextKey, Line 13
    Syntax error converting the varchar value 'F00004' to a column of data type int.

    I am now leaving my office. I will check on that error this evening. Is my idea good?

    Gracie



    Many many thanks for your help.
  16. Negative New Member

    The problem is because return can only return an integer value. It is usually used for error trapping and the like. If you just want to return the value, just do this:

    SELECT @RetVal
  17. gracie New Member

    Thanks Negative,
    I have corrected the stored procedure as follows...

    --******************************************
    CREATE PROCEDURE sp_GetNextKey
    @KeyTable varchar(100)
    AS
    BEGIN
    DECLARE @RetVal varchar(10)

    UPDATE atabid
    SET Key_value = Key_Value + 1,
    @RetVal = Key_Prefix + RIGHT(replicate('0',Key_Size-len(Key_Prefix)) + convert(varchar(10), Key_Value),Key_Size-len(Key_Prefix))
    FROM atabid
    WHERE Key_Name = @KeyTable

    SELECT (@RetVal )

    END
    GO
    --******************************************

    One question, do I have to do anything special to ensure that the necessary lock is placed on the atabid (keys table) or is it implicit in the update statement?



    Many many thanks for your help.
  18. Negative New Member

    It looks like you will not have to worry about it the way your code is written now.
  19. bambola New Member

    The update is done within a transaction so you don't need to worry about lock, it will be placed.

    Bambola.
  20. gracie New Member

    Thanks Bambola,

    I was thinking ..

    I now have my stored procedure that returns an ID sucessfully.

    I call it succesfully from a front end app as each record is created... but I have some data to be loaded directly into my table which means that the column that requires the default id should be created on the fly as records are appended.

    Can I place that same stored procedure as the default value for a column? Can I pass a parameter to it as well?







    Many many thanks for your help.
  21. bambola New Member

    You cannot use a stored, but you can use a function, and you can pass the column to it.

    Bambola.
  22. gracie New Member

    I changed my stored procedure into a function but SS complained about an update being an illegal call within a function. I am missing some kind of important point here about stored procedures vs. functions...

    Many many thanks for your help.
  23. vbkenya New Member

    There are numerous differences between Sps and UDFs. However in your case your function does not need to UPDATE any tables on its own. Just use the function to return the new calculated value (e.g 'F00004'); the new value will automatically be inserted into the column - At least that is the way default constraints work.

    Simply modify the sp code you were given earlier into a function and use it.

    Thanks.
  24. gracie New Member

    Hi vb,
    What you suggested is exactly what I did when I discovered that update doesn't work within a function.

    The sp code was re-worked into a function cause the default value said that it couldn't take an sp, only a function. The code has to update table of key values as it gets its new value.

    Many many thanks for your help.
  25. gracie New Member

    Hi vb,
    What you suggested is exactly what I did when I discovered that update doesn't work within a function.

    The sp code was re-worked into a function cause the default value said that it couldn't take an sp, only a function. The code has to update table of key values as it gets its new value.

    Many many thanks for your help.
  26. bambola New Member

    gracie - what you should do is the following:

    - create a table with 2 fields: next_id, column_name
    - write a stored that get the next id from this table

    - write (if you don't have one) a stored that is inserting a row into your table.
    - within this stored and before you are inserting, call the stored that is generating
    the next id to get the value.

    when you need to insert rows into this table, use the insert sproc (unless you have
    lots or rows and in this case we can come up with something suitable).

    Bambola.


  27. vbkenya New Member

    Cool solution.... make sure that you handle any and all errors that can occur within the inner stored procedure. You could easily introduce gaps in the sequential numbers.
  28. vbkenya New Member

Share This Page