2

I use SQL Server with ASP.NET Core and EF Core. After each record is added, the identity column's value jumps about by 1000 and creates a gap between current row and the last previously added row.

Questions

  • Is there any way to prevent this?

  • How to delete those gaps that have been created before?

  • If I use GUID for key columns to prevent that issue, is there a problem (performance or each other problem)?

  • Is it way on the server side that with EF Core could handle it (each some way)?

Thank you in advance for your helps...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ali Taheri
  • 187
  • 2
  • 16
  • 1
    You don't remove the gaps. They are there by design; the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15#remarks) tells you this. If you don't want gaps, then `IDENTITY` is not what you want. Either way, you're asking 4 questions here, not 1, and this is therefore too broad. – Thom A Jul 13 '20 at 09:28
  • Yes, GUID's as clustering index keys [are **horrible** in terms of performance](https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/) ...... – marc_s Jul 13 '20 at 09:30
  • See this [other SO question and its answers](https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database) for answers to your other questions – marc_s Jul 13 '20 at 09:31
  • @marc_s I don't think that's completely relevant anymore. Especially with `NEWSEQUENTIALID()`. That article *is* 11 years old now. – Thom A Jul 13 '20 at 09:32
  • @Larnu: yes, using `newsequentialid()` can mitigate the negative effects *a bit* - but all the other points (like wasting space due to 4x bigger key size) are still **absolutely valid** - to this day – marc_s Jul 13 '20 at 09:33
  • True on the size, but I just see people just saying "guids are bad" far too often, and the main reason being that they are "random" and so poor clustered index choices. It's not completely slame dunk like is was 15 years ago. (But then people are still using 30 year old implicit Join syntax and data types like `image` and `text`in new development work, so why should I expect them to change *now*. ) – Thom A Jul 13 '20 at 09:39
  • In addition to this, the choice of guid as a key is not usually because it's "right or wrong" from a database point of view, but rather because this way the client can generate the auto-key before hitting the database. This is seen as useful for straightforward client-server, and arguably even more useful with distributed databases or distributed systems, and *definitely* useful when it comes to generating correlation id's for messages between systems. Of course, EF just uses `newsequentialid`, which is cheating. .net core has a native method for this. Still, I don't like the size of them. – allmhuran Jul 13 '20 at 09:42

2 Answers2

3

For the reason for 1000-value gaps, see Aaron Bertrand's answer

It doesn't really make sense to "want" to delete the gaps. The content of an identity column contains no semantic information. It correlates to nothing "in the world" outside the database. The gaps are as meaningless as the values themselves.

I don't see how a uniqueidentifier would "prevent" that issue. A uniqueidentifier may be "meaningfully" sortable (if you use newsequentialid()), but there's no sense in which any particular value is "one more" than a previous value.

You can certainly try to build your own key generating algorithm that does not produce gaps, but you will run into concurrency issues (also mentioned by Mr Bertrand).

allmhuran
  • 4,154
  • 1
  • 8
  • 27
0

workaround trick:

CREATE OR ALTER TRIGGER TGR_Transaction_Identity_Fix 
    ON [dbo].[TBL_Transaction] 
    FOR INSERT 
  AS 
  BEGIN
    DECLARE @RESEEDVAL INT
    SELECT @RESEEDVAL = MAX(TransactionId) FROM [dbo].[TBL_Transaction] 
    DBCC CHECKIDENT([TBL_Transaction], RESEED, @RESEEDVAL)
  END

this triger will reset identity on each insert

DOS.NET
  • 1
  • 1