new identity feature in 2012 with cache- overcoming the cons of it.

with the new 'jump identity value' feature in SQL 2012,
one SQL MVP says
"If you need contiguous numbers, you should not use neither IDENTITY nor sequences"
https://social.technet.microsoft.com/Forums/sqlserver/en-US/bd8fafde-15d9-47aa-8030-ee8f6e48a482/impact-of-trace-flag-272-on-sql-server-2012-apart-from-disabling-identity-jump

there is a Trace fix.. but in one of the user comments i read [This (trace fix) is working for standalone server but not cluster servers.]
http://www.dfarber.com/computer-consulting-blog/2014/2/13/how-to-solve-identity-problem-in-sql-2012.aspx

1)if you do desire contiguous numbers, what options do you have other than SQL Identity or sequences?
2)from what i understand, the trace 272 suppresses the cache feature in sql 2012 for identity column: if high availability feature is turned on or used in 2012, if there is a failover, will the trace be effective in that scenario,?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
I had similar issues a while ago in a financial module generating Invoice numbers and indeed - nothing worked but the old school T-SQL code using an explicit transaction. To note this was the only time in my career when I had to use explicit transactions in SQL to avoid dirty reads while getting(reading) the last entered and committed invoice number from the Invoice table and generate the new one to be inserted.

Now by doing this you're guaranteed to have contiguous numbers assuming the code to generate them has no flaws but also guaranteed to Exclusively lock the table for the duration of the read/generate/insert sequence so if you chose this path make sure is snappy where snappy means less than a second - ideally few hundred milliseconds.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
lcohan
thanks for explaining the situation how you are forced to use explicit transactions for financial sequence..
does that mean for the most part ,you are not using identity or sequence feature?
in a sence, sql 2012 takes us backward a little bit on this feature?
there is not *much* complaint over this in the SQL world, is there?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.