On October 7th, I delivered my presentation about Advanced Dynamic Search Queries at the Israeli Data Platform Meetup, at Microsoft Reactor Tel-Aviv!
Sometimes, when you have a table with an IDENTITY column, there could be scenarios in which weird “gaps” are created between different IDs.
There can be several possible causes for this:
1. The most obvious cause is when rows are deleted from the table. If many rows are deleted from a table with an IDENTITY column, it’s obviously expected that nothing would “fill” up the “gaps” that these rows have left. IDENTITY values only go one way, they don’t automatically re-fill deleted values retroactively.
2. When a ROLLBACK is performed on a transaction after inserting into a table with an IDENTITY column, the increase in the IDENTITY value is NOT rolled back. So even if the row wasn’t actually inserted, the IDENTITY value is still increased. This can happen both with single-row INSERT commands, as well as BULK insertions. So if, for whatever reason, a lot of insertions are rolled-back in your database, you may see a lot of these “gaps”.
3. There’s a special mechanism, specifically in SQL Server 2012, which “pre-allocates” IDENTITY values for a table, and it does this in memory. So when the SQL service is restarted, next time you insert a value into the table, the IDENTITY value would “jump” by 1000 or 10000 (depending on the column data type). This happens in SQL 2012 only, and was reportedly fixed in later versions. More info about it in this blog post by Ahasan Habib.
A lot of DBAs would spend hours (and even days) sitting in front of their computer to write a stupendous amount of lines of code… Then take care of a lot of copy-paste and typing errors… Test the code and find errors… Fix the code… Run it again… And so on. All the while having to navigate within a huge forest of code.
I would presume that it doesn’t take a special kind of mind to stand up and say “just hold on a minute! Why am I working so hard with this mundane task?? Why can’t SQL Server do all of this for me??”
Because in fact… It can!Read More »Let SQL Server Write Code for You
Since SQL Server 2005, Microsoft had a nice addition to the common DML statements,
DELETE, by allowing the use of the TOP keyword. Well, SQL Server 2019 has just been released, and yet unfortunately for many of us, SQL Server still doesn’t support the use of the
TOP keyword in direct combination with the ORDER BY clause. So when we do need to update or delete the top x rows in a table based on a specific order, we’re bound to use all kinds of workarounds.
However, many of the commonly used workarounds are not as efficient as they can be.Read More »Efficiently UPDATE and DELETE using TOP and ORDER BY
In this post, I will discuss how to remove the IDENTITY property from an existing column using T-SQL (without changing the order of the columns or moving the data into a new table).Read More »Remove the IDENTITY property from an existing column