Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
sequence object

Answered sequence object

  • Thursday, March 21, 2013 12:28 AM
     
     
    Sign In to Vote
    0

    Hi,

    I have a table which has multiple organizations where each of the organization must have its own incremental value starting from one. E.g.

    OrgID 1, Data 1

    OrgID 1, Data 2

    OrgID 2, Data 1

    OrgID 3, Data 1

    Currently I am using "SELECT MAX(Data)+1 FROM Org WHERE OrgID=1" to get the next value for Data.

    I was planning to use the sequence object on this but in this case I may need to create 100,000 sequence objects for all organizations we have. May I know would this idea slow down the SQL performance? Any problem to database clustering, replication, DR? These are the information I couldn't find it from web.

    Thanks,

    Kenny

     

All Replies

  • Thursday, March 21, 2013 1:28 AM
     
     Answered
    Sign In to Vote
    0

    You should use sequence for one table; it means you can have new sequence ID for newly inserted record. Or you can use Identity column as well. Read:

    http://msdn.microsoft.com/en-us/library/ff878091.aspx

    http://msdn.microsoft.com/en-us/library/aa933196%28v=SQL.80%29.aspx

    Many Thanks & Best Regards, Hua Min



     
  • Thursday, March 21, 2013 2:16 AM
     
     Answered
    Sign In to Vote
    0

    You should create table with one row per organization and keep there last used id.

    When you need to add more rows to your primary table execute code in single transaction:

    1. increase counter for id

    2. insert record in main table

     
  • Thursday, March 21, 2013 3:23 AM
     
     Answered
    Sign In to Vote
    0

    The CREATE SEQUENCE for each organization should be the best way to do this. Since it is part of Standard SQL, so it will port and be optimized over future releases. It should not cause any problems that I know about. 

    The beauty of this construct is that it is independent of any particular table so you can use it in many different tables. Look at the options and play with it. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

     
  • Thursday, March 21, 2013 7:40 AM
     
     
    Sign In to Vote
    0

    Thanks for the feedback.

    Currently I am using identity but performance is not efficient enough due to I need a MAX function to get the MAX value for the organization befor inserting the data. I have 10,000,000 rows organizations data.

    I couldn't find any articles who are creating 10k to 100k ++ sequence objects in production environment to prove that my idea is workable. Maybe you guys can suggest a way that I can prove this idea is working and without performance issue.

    I also have no idea would this create problem for database clustering or failover, which I don't think so but just need to be sure.

     
  • Thursday, March 21, 2013 7:50 AM
     
     
    Sign In to Vote
    0
    Identity column will increment the ID automatically.

    Many Thanks & Best Regards, Hua Min

     
  • Thursday, March 21, 2013 9:03 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Kenny,

    I think although you can create thousands of sequence object on your database, it is not easy to maintain these sequences within your SQL codes. I mean you have to determine which sequence will be used according to the related organization, so there must be a dynamic SQL code behind it.

    I think you can create a table for all organization codes and their next sequence number.

    Then using a function with organization data as input parameter, you can return the next sequence number or a range of numbers.

    The function should also update the field for that organization to reflect the next sequence value.

    What you should take care is the locking. You should not use the same value for two rows for example. This lock mechanism, maybe a transaction with commit and rollback options should take place in the sql function

    On the otherside, you can find samples at SQL Sequence object in SQL Server 2012 for new enhancements on new SQL Server version.

    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

     
  • Thursday, March 21, 2013 5:00 PM
     
     
    Sign In to Vote
    0

    With sequences, in case of dirty failover, you will lost all numbers stroed in cache (they will be never used), so you will have holes in data.