To prevent an X-Y problem here's the actual problem we're trying to solve: The Problem: We have a bunch of lookup tables that were unfortunately created with an identity column on the Primary Key, which is an `int`. We wish we could simply remove the identity, however, we have some large tables with foreign keys pointing to the identity columns, and my understanding is removing the identity in this case is difficult. The reason we regret the identity is because these tables need to be synced across multiple environments, and developers insert data into these tables by writing scripts, and we run these scripts on multiple environments but not necessarily always in the same order, and so we ask developers to always: - Enable Identity Insert - Insert the row(s) with hard-coded integer IDs - Disable Identity Insert If everyone does that the data will either remain synced, or a script will fail and we can take immediate corrective action to resolve the conflict. But of course, sometimes the developers forget to follow the rules and just insert without the identity, and the auto increment of different scripts running in different orders in different environments causes them to get out of sync, and then problems arise. One Idea: Can we force the developers to always specify the identity column? I don't think there is a way to simply disable the Identity on these tables. What if we reseed the identity to a low number? When the seed value already exists, any insert that doesn't specify all columns will fail, and continue to fail until the number of insert attempts exceeds the number of existing (consecutive) rows. But after just one proper insert, that reseeds the table and the next improper insert will use the auto-increment again. So the extrapolation of this idea is to reseed the table to a low existing number after every insert (perhaps with a trigger, which feels odd, but might work?), or on a schedule, or perhaps every time we run the developers' scripts. Is t

For `serial` columns used in Postgresql `SELECT setval('table_id_seq', (SELECT MAX(id) FROM table)); ` From version 10, using identity columns, there is no need to use the sequence name. That's nice. `ALTER TABLE table ALTER COLUMN id RESTART WITH 1000; ` How do I set the identity column to be the `max(id)` without knowing the sequence name? As far as I can see from the ALTER TABLE syntax there is no way to have a subquery to compute the start of the sequence. I would like to write something like: `ALTER TABLE table ALTER COLUMN id RESTART WITH (SELECT MAX(id) FROM table); `

Objective I want to make a exact copy of 10 tables from a database on a SQL Server into my local SQL Server. The local copy is used for testing. Problem The copied tables all have the identity property on their `ID` column set to 'no'. As far as I understand, that setting allows the column to auto-increment (I get an error when I try to insert values without giving a value for `ID`). The tables on the server all have this setting set to 'yes' and now when I try to set it to 'yes' on the local copies, my software (SQL Server Management Studio) asks me to drop and re-create the whole table and therefore losing all the data. What I've tried The following solutions all copy the tables with their data, but don't copy the identity property: - Copy the tables each with a query (using linked servers): `SELECT * INTO NEW_DB.dbo.tblCopy FROM OPENQUERY([server], 'select * from OLD_DB.dbo.tblData')` - Import the data with `Task -> Import Data...` (Using this tutorial) - I tried this both with "Enable identity insert" checked and unchecked - Create the table on the destination database and simply copy/paste the data into it (this did not work at all - no data was inserted) Notes - I would rather not rely on third-party tools. I am using SQL Server Management Studio 2018. - I am aware of this question on Stack Overflow which talks about the exact same problem. But as already stated above, enabling "enable identity insert" did not copy the identity property. - I am also aware of this other question asked on Server Fault, both of its answers are about a third-party tool though, which I do not want to use. - I only have read permission on the source server/database. - The tables contain up to 6,000 rows. - The source database contains several more tables, I only want to copy 10 specific tables.

I just had a showerthought that the default Identity Seed is 1. I have some tables that I know will grow to the billions at a certain point. Wouldn't it make more sense to start on `int.Min` ( -2,147,483,648) for these tables? This could just make the difference of migrating your key to `bigint` in 4 years or in 8 years. Can be relevant enough. Is this common? It feels weird. Is there anything I am missing?

I've inherited a system where the application is running under `sysadmin` account. I limited this account to `db_datareader` + `db_datawriter` + `EXECUTE` on all database and set `extended events` session to catch the `permission errors` on the server. It was very surprising to me to see some `inserts` failed even if the user is a member of `db_datawriter` and no granular restrictions on tables are applied. Then I note that only inserts that set `IDENTITY_INSERT` failed. The database in question is full of `identity` and there is too much `SET IDENTITY_INSERT` in their code. The code means not only `modules` stored on server but also `C#` code. To be able to set `identity_insert` user must own the table or have `ALTER` permission on the table. But the fact is that you CANNOT grant `ALTER` only on tables, I'm forced to grant `ALTER` on a whole database to this `user` (I'll make this user member of `db_ddladmin` role that will add only 42 permissions(!!!) vs 51 permissions that could be added to user permissions by granting `ALTER` on database) I'm not interested in refactoring the database with `sequence` (server version is `2014` so I could do it theoretically) and I cannot just add `execute as dbo` to every sp that use `identity_insert` because there is also application code to rewrite, I just wonder WHY does Microsoft make such a strange permissions design that `db_datawriter` cannot set `identity_insert`? Is there other way to make the user be able to set `identity_insert` that will add less permissions than `db_ddladmin`? UPDATE I tried the solution offered by LowlyDBA with `synonyms`: ``` create table dbo.t(id int identity); go alter schema sch transfer dbo.t; go create synonym dbo.t for sch.t; go set IDENTITY_INSERT dbo.t on; ``` This causes the error Msg 1088, Level 16, State 11, Line 1 Cannot find the object "dbo.t" because it does not exist or you do not have permissions. The solution of `Antoine Hernandez` to grant the user ALTER only

I have a table with an identity column and I want to reserve a block of ids which I can use for bulk inserting, whilst allowing inserts to still happen into that table. Note this is part of a bulk insert of several tables, where those other tables relate to these ids via an FK. Therefore I need to block them out so I can prepare the relationships beforehand. I've found a solution which works by taking a lock on the table in a transaction and then does the reseeding (which is pretty fast). But it looks a bit hacky to me - is there a generally accepted pattern for doing this? ``` create table dbo.test ( id bigint not null primary key identity(1,1), SomeColumn nvarchar(100) not null ) ``` Here's the code to block out (make room for) some ids: ``` declare @numRowsToMakeRoomFor int = 100 BEGIN TRANSACTION; SELECT MAX(Id) FROM dbo.test WITH ( XLOCK, TABLOCK ) -- will exclusively lock the table whilst this tran is in progress, --another instance of this query will not be able to pass this line until this instance commits --get the next id in the block to reserve DECLARE @firstId BIGINT = (SELECT IDENT_CURRENT( 'dbo.test' ) +1); --calculate the block range DECLARE @lastId BIGINT = @firstId + (@numRowsToMakeRoomFor -1); --reseed the table DBCC CHECKIDENT ('dbo.test',RESEED, @lastId); COMMIT TRANSACTION; select @firstId; ``` My code is batch processing blocks of data in chunks of about 1000. I have about a billion rows to insert in total. Everything is working fine - the database isn't the bottle neck, the batch processing itself is computationally expensive and requires me to add a couple of servers to run in parallel, so I need to accommodate more than one process "batch inserting" at the same time.

Help required on an inherited deadlock problem! The code given below appears to have been written some time back, in an attempt to address contention issues around the assigning of new ID values on a much larger/fatter [IDs] parent table, by first creating the identity values in a smaller [ID_Stub] table. However, the presence of continued deadlocks indicates that this code appears to be causing more problems than it is solving. We frequently experience deadlocks around an INSERT statement on the following table (table and column names have been obfuscated). The table has no triggers or foreign key dependencies, but has a clustered and a non-clustered index as follows. ``` CREATE TABLE dbo.ID_Stub ( ID int IDENTITY(1,1) NOT NULL, IDReference nchar(25) NULL, AdditionalID int NULL, CreatedBy int NOT NULL, CreatedOn datetime NOT NULL, CONSTRAINT PK_ID_Stub PRIMARY KEY CLUSTERED (ID) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90 ) ); GO CREATE NONCLUSTERED INDEX idx_IDReference ON dbo.ID_Stub (IDReference) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70 ); GO ``` The table contains an average of around 70,000 rows at any one time (a process runs to trim the number of records nightly). The default instance transaction isolation level is READ COMMITTED, however this is overridden (in the stored procedure where the deadlocks occur) to SERIALIZABLE, along with an implicit transaction. Unfortunately, we cannot consider moving to an optimising locking strategy such as RCSI at this stage. The whole implicit transaction has been given below for completeness, but the deadlocks are occurring around the final INSERT statement on ID_Stub towards the

PostgreSQL 11 What is the best way to generate default values for identity columns on partition tables. E.g ``` CREATE TABLE data.log ( id BIGINT GENERATED ALWAYS AS IDENTITY ( INCREMENT BY 1 MINVALUE -9223372036854775808 MAXVALUE 9223372036854775807 START WITH -9223372036854775808 RESTART WITH -9223372036854775808 CYCLE ), epoch_millis BIGINT NOT NULL, message TEXT NOT NULL ) PARTITION BY RANGE (epoch_millis); CREATE TABLE data.foo_log PARTITION OF data.log ( PRIMARY KEY (id) ) FOR VALUES FROM (0) TO (9999999999); ``` If I do: ``` INSERT INTO data.foo_log (epoch_millis, message) VALUES (1000000, 'hello'); ``` I get: ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, 1000000, hello). SQL state: 23502 because the default generated value is not applied to the partition UNLESS I insert it into the root table like this: ``` INSERT INTO data.log (epoch_millis, message) VALUES (1000000, 'hello'); ``` There are times though that I want to insert directly into a specific partition for performance reasons (like doing bulk COPY). The only way I can get this to work is to create the partition while knowing about the sequence that was implicitly created for the identity column like this: ``` CREATE TABLE data.foo_log PARTITION OF data.log ( id DEFAULT nextval('data.log_id_seq'), PRIMARY KEY (id) ) FOR VALUES FROM (0) TO (9999999999); ``` Is there a better way to do this and if so how?

During one of the last lessons at university (I'm a student), lecturer asked us to develop a database (MySQL Server if it matters) and tiny client app that would consume the database as data source. One of requirements was that the identity column (which is the PK in every table) must be sequential, because it is a good practice (as per lecturer words). That is, when table row is deleted, it's PK must be reused in subsequent inserts. I have average knowledge in RDBMS, PKs and identity columns. From what I understand, that identity column is just a way to let DB to auto-generate PKs when inserting rows and nothing more. And identity column value shall not be related to row attributes in any way (as long as it is not natural key). This requirement (strictly sequential identity column) was suspicious to me. I tried to ask the lecturer what is wrong if identity is not sequential (with gaps caused by deletions), but got very abstract answer like "it is convenient for users and useful for DB administrators who maintain the database". No specific examples. The argument "convenient for users" sounds silly, because it doesn't have any meaning in business domain. Therefore I'm curious if these reasons are real? I can think only of one case when identity column reseed is required -- when identity space is exhausted. But this is more design issue when identity column type was chosen incorrectly, say simple `int` instead of `bigint` or `uniqueidentifier` when table contains billion rows. Suppose, an identity column is a clustered index: can gaps in identity column affect index performance? Maybe there are other real-world reasons for automatic identity column re-seed after each delete I'm not aware of? Thanks in advance!

We have a two replica 2017 Standard Availability Group in asynchronous mode. With a failover (manual/forced, since can't do automatic), we have the potential for overlapping/reused identity values if the secondary is lagging behind the primary. This is really bad for us...CustomerA seeing CustomerB's data. EDIT: I'm mostly concerned about a DR/failure failover where the primary goes offline unexpectedly. A planned failover (patching, etc.) would be easier to manage by waiting for or forcing the secondary to sync up to the primary before taking the primary offline, as suggested below. - Is there a way to automatically run a script as the secondary takes over the primary role, to bump all identity columns (DBCC CHECKIDENT RESEED) to allow for the eventual merging of the old primary's unsynced records? - Since this is Standard, the secondary is inaccessible until it takes over as the primary, so when/how would the script run? - Side question: Since the old primary becomes the secondary (once it's back online) and is at that point inaccessible (Standard), how does one run queries on it to determine the unsynced records? Back up/restore the old primary as a nonAG db? Thanks!