locked
Sql Column Default Value RRS feed

  • Question

  • Can i set the default value for a column(say primary key) to isnull(max(colName),0)+1 ?

    How can I do this..?

    Wednesday, May 9, 2012 9:49 AM

Answers

  • I hate to use identity because these insertion and deletion process happens at a decent rate, so  i fear whether overflow occur.

    Hi Nik,

    I assume you afraid of overflow for 32 bit integer key and want to reuse deleted values. Well, while it's technically possible, that would be very complex process to implement. As I mentioned above you'd have side effects with concurrency (when multiple sessions are looking for (deleted) key values simultaneously) not even mention performance implications. I would suggest you to switch key type to bigint (64 bit integer) and use either identity or sequences.  

    As the side note, be very careful with OPENXML. From books online:

    A parsed document is stored in the internal cache of SQL Server. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

    I'd suggest to use build-in xml support in SQL Server to deal with XML. This approach is generally slower than OPENXML although it does not waste server memory. If performance is absolutely critical, I'd suggest to parse XML on the client side and provide row set to the stored procedure as table-valued parameter. This approach outperforms any other methods including OPENXML. If you're interested to play with that approach you can download the code from my SQL Saturday #62 presentation ("Refactoring for performance"). 


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Peja TaoEditor Monday, May 28, 2012 1:56 AM
    • Marked as answer by Nik Anand Friday, June 8, 2012 7:48 AM
    Friday, May 18, 2012 2:15 PM

All replies

  • 1. You can use IDENTITY column.
    2. Use SEQUENCE in SQL Server 2012

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Wednesday, May 9, 2012 10:10 AM
  • Can i set the default value for a column(say primary key) to isnull(max(colName),0)+1 ?

    How can I do this..?

    Technically you can if you use a function to compute this; however, I would never recommend this.  Another unsafe method is to use the IDENT_CURRENT function; again, I don't recommend this either.

    As previously suggested, use either an identity column or use a sequence object if using SQL 2012.  If you were using a current version of Oracle or DB2 you would also use a sequence object.

    Something to keep in mind: No matter which of these methods you use, there is always a possibility that you might develop "holes" in your numbering scheme; eliminating holes in a numbering scheme is more complicated than simply taking the top number and adding 1.



    Wednesday, May 9, 2012 12:42 PM
  • I can't use identity because sometimes  i want to add  large number  of records to it and remove them ,and i wish to avoid the burden of maintaining the primary key programmatically  and don't let the key overflow. i am working on sql 2005.

    Thursday, May 10, 2012 3:54 AM
  • Technically yes, BUT performance  will be hurt ,

    CREATE TABLE TableA
    (
     id  INT,
     col AS dbo.fn_check_TableA(id)

    )

    INSERT INTO TableA(id) VALUES (20)
    GO
    CREATE FUNCTION dbo.fn_check_TableA
      (@key int)
    RETURNS int
    AS
    BEGIN

    DECLARE @t INT

      SELECT @t=MAX(id)+1
                FROM TableA
                  

      RETURN @t

    END
    GO

    INSERT INTO TableA(id) VALUES (20)
    INSERT INTO TableA(id) VALUES (21)
    SELECT * FROM TableA

    DROP TABLE TableA
    DROP FUNCTION dbo.fn_check_TableA


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Thursday, May 10, 2012 4:06 AM
  • I marked Uri's response with a "helpful" because this is pretty much what I was talking about with doing this with a function.  I still don't like this solution without more discussion; performance of this method can get pretty high on the "sucks" scale.

    An aside:

    I don't mean anything against Uri or anyone else with this.

    Monday, May 14, 2012 12:16 PM
  • If there are rules that are to be followed on an INSERT, a stored PROCEDURE can implement them.

    That is, CREATE a stored PROCEDURE to INSERT the data. The default can be used when no data is provided for the COLUMN:

    INSERT INTO mytab(pk, colName, bob) SELECT ISNULL(@I_pk, isnull(max(colName),0)+1)), @I_colName, @I_bob FROM mytab ...

    In general, this is not an efficient method and other solutions are likely available. Perhaps we can help is you explain what the goal is.


    Monday, May 14, 2012 1:43 PM
    Answerer
  • Hi Nik,

    One of the issues you'd need to deal with in such model in addition to performance implications is concurrency. This pattern:

    select Max(ID) + 1 from Tbl

    is not safe in multi-user environment. 2 sessions can run that select at the same time and as result grab the same ID. Technically, you can add UPDLOCK hint there - it should help but would have other concurrency implications for other sessions. Another option would be serializing access to that select with sp_getapplock and/or implement try/catch and re-try logic but that requires extra coding and not always feasible.

    As others said, I'd suggest you to avoid that pattern. Sequences in SQL2012 gives you nice way to control ID generation. In case if you are using older versions of SQL Server, you can implement counters table or, perhaps, use identity but grab the list of IDs with OUTPUT clause. Either way would be better in the long run


    Thank you!

    My blog: http://aboutsqlserver.com

    Wednesday, May 16, 2012 4:21 PM
  • My scenario:

    I have a set of master detail tables and i insert into both using a single stored procedure.

    In the stored procedure parameter list, the detail table data is passed as xml and i use

    "exec sp_xml_preparedocument  @hdoc output,@Mydataxml"

    method to insert into detail table.

    The problem is to generate the key field for the detail table and it's a multiuser environment.

    I hate to use identity because these insertion and deletion process happens at a decent rate, so  i fear whether overflow occur.

    Thanks
    • Edited by Nik Anand Friday, May 18, 2012 4:05 AM
    Friday, May 18, 2012 4:05 AM
  • What overflow are you concerned about?

    Note, the OUTPUT clause may be of help here.

    Friday, May 18, 2012 10:50 AM
    Answerer
  • I hate to use identity because these insertion and deletion process happens at a decent rate, so  i fear whether overflow occur.

    Hi Nik,

    I assume you afraid of overflow for 32 bit integer key and want to reuse deleted values. Well, while it's technically possible, that would be very complex process to implement. As I mentioned above you'd have side effects with concurrency (when multiple sessions are looking for (deleted) key values simultaneously) not even mention performance implications. I would suggest you to switch key type to bigint (64 bit integer) and use either identity or sequences.  

    As the side note, be very careful with OPENXML. From books online:

    A parsed document is stored in the internal cache of SQL Server. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

    I'd suggest to use build-in xml support in SQL Server to deal with XML. This approach is generally slower than OPENXML although it does not waste server memory. If performance is absolutely critical, I'd suggest to parse XML on the client side and provide row set to the stored procedure as table-valued parameter. This approach outperforms any other methods including OPENXML. If you're interested to play with that approach you can download the code from my SQL Saturday #62 presentation ("Refactoring for performance"). 


    Thank you!

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Peja TaoEditor Monday, May 28, 2012 1:56 AM
    • Marked as answer by Nik Anand Friday, June 8, 2012 7:48 AM
    Friday, May 18, 2012 2:15 PM