none
Formula Property RRS feed

  • Question

  • I have a table like this.

    UnitID            int
    UnitName       varchar(50)

    I am planning to enter UnitName only and the UnitID should be automatically generated.  I don't want IDENTITY here.  In the Formula property, I tried entering Rowcount(), Count() etc.  But, everything came up with an error.  What sort for Formula will be helpful here, so that it generates the numbers automatically.

    Thanks. 
    NSG12
    Tuesday, January 20, 2009 3:47 PM

Answers

  • NSG12 said:

    So, my UNITID column will not have numbers in sequence.  To avoid that, i tried to generate my own numbers, which would be in an order.


    If your design relies on the numbers being in sequence, then your design is flawed.
    This kind of thing can be generated at run time really easily in SQL 2005 onwards using windowed functions (specifically Row_Number() for this example), and can still be achieved in earilier versions but requires a bit more thought.

    An identity is an un-intelligent, arbitrary number - so trying to give it meaning would be un-intelligent and arbitrary ;)


    George
    Tuesday, January 20, 2009 5:13 PM
    Answerer

All replies

  • What's wrong with IDENTITY?  You may not want it, but it's the best choice.  What have you heard about IDENTITY columns that prompts you to avoid them?
    Aaron Alton | thehobt.blogspot.com
    Tuesday, January 20, 2009 3:50 PM
    Moderator
  • None, you have to roll this yourself  but then again why would you and deal with concurrency, num,bers already existsing etc etc

    Just use identity



    Tuesday, January 20, 2009 3:51 PM
    Moderator
  • What's wrong with identity?
    Bodo Michael Danitz - MCITP Database Administrator - free consultant - performance guru - www.sql-server.de
    Tuesday, January 20, 2009 3:51 PM
  • I have provided you only two fields.  Actually that table has several foreign key columns also.  If I enter a wrong foreign key value, and ultimately  enter a right value after several attemps, the IDENTITY column does not automatically populates the next value.  Instead it populates a number, the number which would come after several  numbers. 

    I mean, if 2 has be the next expected value, the IDENTITY column will not populate 2, instead it would be 4.  Because, i tried to enter a wrong value 2 times.

    So, my UNITID column will not have numbers in sequence.  To avoid that, i tried to generate my own numbers, which would be in an order.



    NSG12
    Tuesday, January 20, 2009 5:09 PM
  • NSG12 said:

    So, my UNITID column will not have numbers in sequence.  To avoid that, i tried to generate my own numbers, which would be in an order.


    If your design relies on the numbers being in sequence, then your design is flawed.
    This kind of thing can be generated at run time really easily in SQL 2005 onwards using windowed functions (specifically Row_Number() for this example), and can still be achieved in earilier versions but requires a bit more thought.

    An identity is an un-intelligent, arbitrary number - so trying to give it meaning would be un-intelligent and arbitrary ;)


    George
    Tuesday, January 20, 2009 5:13 PM
    Answerer