none
SEQUENCE Object for Small Tables Only?

All replies

  • Why it was suggested to use NO CACHE for sequences is beyond me. I don't know what the use case for NO CACHE is, but it is certainly not the norm. You can of course use sequences for large tables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, July 12, 2014 8:59 AM
  • Well Erland, either you calm down your manager (with a martini?) or use NO CACHE.

    QUOTE: "This could cause a sequence to run out of numbers much more quickly than an IDENTITY value. It could also cause managers to become upset that values are missing, in which case they’ll need to simply get over it and accept that there will be numbers missing.

    If you need SQL Server to use every possible value, configure a cache setting of NO CACHE. This will cause the sequence to work much like the IDENTITY property. However, it will impact the sequence performance due to the additional metadata writes."

    LINK: Microsoft SQL Server: The Sequencing Solution


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012









    Saturday, July 12, 2014 5:15 PM
    Moderator
  • If you need SQL Server to use every possible value, configure a cache setting of NO CACHE.

    No, if you want to a number series without gaps, do not use sequence or IDENTITY at all. They are designed to be able to produce gaps.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, July 12, 2014 5:54 PM
  • No, if you want to a number series without gaps, do not use sequence or IDENTITY at all. They are designed to be able to produce gaps.


    That is so funny Erland! So we should go back MAX + 1 where we were 20+ years ago?

    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Sunday, July 13, 2014 12:53 AM
    Moderator
  • That is so funny Erland! So we should go back MAX + 1 where we were 20+ years ago?

    Yes, if you have a business requirement that calls for numbers to be contiguous. Or for that matter, you have no reason to assume that insertions will come with high concurrency.

    Not what I see what is particulary funny in this. IDENTITY and sequences are designed to support high concurrency. Then IDENTITY has been seriously over-used over the years, and people have used it when they should not have, and that have lead them into to all sorts of trouble.

    Note: if you have a business requirements that calls for contiguos numbers, and you must also support high-concurrency inserts, you have a challenge.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, July 13, 2014 10:38 AM