>>>parentid = curnewtrans->newtrans
>
>Thanks for the -> , a reminder of good old days I'd not seen since well before the mdot wars. ;-)
>
>As Kevin notes, I'm referring to the Sequence feature added in SQL Server 12. It's a very efficient way to get a unique value before you commit, and an easy way to convert a dbc's Autoinc SP or function if one of those is still in use. It's quicker and more efficient than GUID and includes the ability to "cache" sets of (say) 10 values that get doled out without updating the underlying resource until another batch is needed.
>
>It also turns out that the default field type for Sequence is bigint and you can overcome this issue by defining the Sequence as INT. So much for a bug!
>
>As an aside: if your commandstring is parameterized, I think SQL Server automatically caches the plan whether you SQLPrepare() or not. Or so I'm told.

I had never tried to redefine a sequence object as a four-byte integer. To be honest, I think it's nearly dangerous if they do allow it to be redefined. If you have enough tables in a database, you increase the possibility of having 2 billion+ rows.


As for being more efficient than a GUID,, well, that's debatable. Yes, the footprint is much smaller.

If you're populating a GUID using newid(), you're nearly guaranteed a universally unique value (note the word 'nearly'), though you'll have higher risk of index fragmentation.
A sequence object will only be unique to a database


If you're populating using newsequentialid(), it's only going to be unique within a smaller context, though you have less fragmentation.

Basically what I tell people is that a sequence object is a better version of a GUID + newsequentialid()

I'm glad MS added it - though by the time SQL 2012 was released, countless SQL installations already had their own home-grown little factory proc to generate a table-independent unique integer. :)