For serial columns used in Postgresql < 10, we manage the sequence by its name. We were able to reset a sequence with:

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](https://www.postgresql.org/docs/current/sql-altertable.html) 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);

I have a SQL Server log table that includes a column "Id" This is an identity column, but not a primary key, it is not even indexed. This would have just been set up from some tutorial for adding DB logging, and I'm not sure if the PK was just missed or intentionally left out. Given its logging all concern is to write performance, not read. I would have thought if its an identity column it would have to be checking the data anyway to do an insert at correct numbers, should this be set up as a pk for that reason? Is there any benefit to leaving it with no pk?

We have a SQL Server table that has an int autoincrement primary key column on it. The table's primary key space is fragmented. For example, ids 1 through 10 are in use but then ids 11 through 100,000 are not in use. Ids 1,000,000 through 1,100,000 are in use but ids 1,100,000 through 50,000,000 are not in use. I am trying to figure out any and all such available range of ids. Once the ranges are determined, then we may reseed the primary key column to start at the beginning of the widest range. Any tool or utility or SQL script out there for determining such available ranges?

I am migrating a full framework .NET app to core. It used ADOX to create an MS Access database and tables. It used OLE DB to populate data. In ADOX, the data type was ADOX.DataTypeEnum.adInteger and the syntax from c# was: fld.Properties["Autoincrement"].Value = true; fld.Properties["Seed"].Value = 1; fld.Properties["Increment"].Value = 1; For memo, ADOX data type was ADOX.DataTypeEnum.adLongVarWChar. Since we can no longer use ADOX under .NET Core, I have to use SQL DML through ODBC. When I attempt to create a table using syntax similar to create table t ( col1 int not null identity(1,1), col2 varchar(512) ) I get a syntax error. When I try to adjust this for Access according to this and other related articles in the same documentation tree, I also get syntax error. create table t ( col1 counter, col2 text ) What is the correct syntax for these columns that is compatible with .NET Core and MS Access ODBC driver?

I have migrated my publisher DB to new server yesterday. For migration what I did was 1. removed subscriptions and publication completely 2. backed up DB on which publication was configured 3. restored DB to new publisher server 4. Ran sp_removedbreplication on newly restored DB to give a clean start 5. recreated publication and subscribers on my respective servers I was able to setup replication properly and it came in sync normally. But when user tried to insert new records at subscriber I got primary key violation errors for lot of tables. Primary key is on identity columns for most of my tables and have not for replication true on both publisher and subscriber end. Upon checking the identity check constraint on subscriber I saw that it had old ranges than my current max values. It wasn't getting the new ranges even though it had automatic identity range management set. I had to manually RESEED the identity on subscriber to make it work and had to go through manual process of reseeding it for all tables. My environment is : Replication type: Transaction replication with updatable subscription with immediate updating 1. 1 publisher -- SQL server 2008 R2 + SP2 2. 1 distributor -- SQL server 2008 R2 + SP2 3. 2 subscribers -- SQL server 2008 R2 + SP2 I have below questions: 1. What could be the reason for automatic identity management not working? 2. How to fix this problem permanently? Is there a script available which can check my max values on publisher and subscribers and then RESEED it accordingly? 3. What is the fastest way to fix this?

I'm building a REST API, backed by MySQL. Normally for every table I will have at least one: id UNSIGNED INT NOT NULL PRIMARY KEY AUTO_INCREMENT field as a default, but the issue with this is that it makes identifiers predictable, and tells end-users how many entries of a given resource we have, and potentially the rate of change. I'd like my ID's to not have that issue. Instead, I would rather use something random. I've decided on using a 64bit random number, represented as a base64url string. The way I've implemented this is adding a external_id VARCHAR(11) NOT NULL UNIQUE to every table, keeping the id and using the id for all foreign keys. As I'm doing this, I'm starting to wonder if it isn't better to just fully ditch the AUTO_INCREMENT field and just convert my external_id to a 64 bit INT and make this the main key used throughout my data model. One disadvantage is that it's an extra step to convert this number to the string variant. I'm not interested in academic purity of my data model, but I'm curious how people generally solve this issue. Do you use 2 ids?

##### Context Here is the DDL that I am intending to use to define the table for a logistics/delivery company.

CREATE TABLE scraping_details (
    id INT IDENTITY(1,1) PRIMARY KEY, -- Identity insert and autoincrement 
    unique_id VARCHAR2(64) NOT NULL,
    ts DATETIME NOT NULL, -- Timezone naive 
    pickup_zip VARCHAR2(6) NOT NULL,
    pickup_long NUMBER NOT NULL,
    pickup_lat NUMBER NOT NULL,
    dest_zip VARCHAR2(6) NOT NULL,
    dest_long NUMBER NOT NULL,
    dest_lat NUMBER NOT NULL,
    UNIQUE (unique_id)
);
SET IDENTITY_INSERT scraping_details OFF;

##### Query Pattern The most frequent query pattern that I foresee, will always seek the ts, pickup_zip and dest_zip columns for a specific hour of a specific day. That means, we will want all the rows (and above columns) where ts is between 19th June 2024, 10 am to 10:59:59 am. ##### Questions * How to modify the table creation command, especially ts to make this query as efficient as possible? Any kind of clustering or indexing on this row will help? I can trade some insertion latency to make this query efficient. * About the implications of turning off the identity insert, can I insert the rows from a polars dataframe (using SQLAlchemy) where the original dataframe does _not_ have the id column? Does it mean the database will create the corresponding numbers? ##### Backend Technology If important, my company is using an Oracle ADB for this purpose. Mentioning this as I believe different backends have different functionalities.

I need to add a new BIGSERIAL column to a huge table (~3 billion records). This question is similar to what I need to do and the accepted answer has helped me somewhat. But I'm still wondering about something. In my case, the table already has a BIGSERIAL column which is the primary key, but many rows have been deleted so now there are *gaps*. (The table has subsequently been fully vacuumed.) I need to regenerate the values so that they are sequential again. Here are 5 example rows of what I want to achieve where the new_value > 1000:

+---------+---------+
|old_value|new_value|
+---------+---------+
|1026     |1001     |
|1027     |1002     |
|1030     |1003     |
|1032     |1004     |
|1039     |1005     |
+---------+---------+

I have successfully implemented the alternative approach as mentioned in the referenced answer above (CREATE TABLE ... and then INSERT INTO new_table SELECT * FROM ... ), but I would also like to attempt, and benchmark against, the initial suggestion. The problem, however, is that I don't know whether the new_value will be generated **in the same order** as the old_value as this is a requirement. How can I ensure the order of the new_value column follows/tracks the order of the old_value column when the new_value column is added using a statement like this:

ALTER TABLE existing_table ADD COLUMN new_value BIGSERIAL;

## A different approach I also attempted the following (that works quite well on a small table), but it's much slower than the alternative suggestion of the referenced answer on very large tables:

ALTER TABLE existing_table ADD COLUMN new_value BIGINT NOT NULL DEFAULT 0;

UPDATE existing_table AS existing
SET new_value = generated.new_id
FROM (
      SELECT original.old_value
           , row_number() OVER (ORDER BY original.old_value) AS new_id
      FROM existing_table AS original
     ) generated
WHERE existing.old_value = generated.old_value;

I'm just wondering if either the spec of the implementation (PostgreSQL) provides for generating identity columns from UUIDs or the like. Is there an alternative to, CREATE TABLE f ( id uuid DEFAULT gen_random_uuid() ); Especially one that can protect the column under GENERATED ALWAYS

We just switched from SQL Server 2008 R2 to SQL Server 2012. I am facing a problem with identity columns: Whenever I restart SQL Server, the seed value for each identity column is increased by 1000 (for int identity columns it is 1000 and for bigint it is 10,000). For example, if the next int identity value for a table was 3, after restarting SQL Server it will be 1003. If I again restart SQL Server, it will be 2003 and so on. After searching Google, I found that it is a new feature (don't know what is use of it) in SQL Server 2012, having only two solutions if you want the old identity behaviour: 1. Use a sequence object This is not possible for me because: a) I am using the same database in SQL Server 2008 and 2012. I can't use sequence in 2008. b) If I go with sequence then I need to change the save procedure for each table, which would be a bulky task for us. 2. Use Trace Flag 272 (-T272) I can go with this solution because there is no need to make any changes to my application. Someone suggested adding -T272 as a start-up parameter, after this SQL Server identity will work as in previous versions. I did the same but it is not working. I don't want to make any changes to my database structure. Please suggest solutions or explain why -T272 is not working.

After a machine malfunction and a hurried transfer of data onto another machine, we have somehow ended up with some brand new sequences replacing old sequences as the backing for identity columns (and for some old serials). We can RESET the new sequences with the maximum values in the tables, and also use ALTER SEQUENCE ... RENAME to rename the new sequences to the old names (to get around any hard-coding in the client code) but I was wondering if it was possible to swap the old sequence back in place instead?

Is there an easy and robust way to execute these two commands on all tables in a given schema myschema in a PostgreSQL (13) database:

ALTER TABLE myschema.table001 ADD PRIMARY KEY (oid);
ALTER TABLE myschema.table001 ALTER "oid" ADD GENERATED ALWAYS AS IDENTITY;

I'm 100% certain that the oid field is present in theses tables.

I have a PostgreSQL table with the following structure: I simply need to change the TYPE of prove_identity_id from bigint to bigserial. I read the docs but wasn't able to understand how to legally achieve the change without the following error: PostgreSQL said: type "bigserial" does not exist PostgreSQL 10.1 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit

When using PHP’s lastInsertId() method with PostgreSQL, it’s necessary to know the name of the sequence. If I defined the column using GENERATED BY DEFAULT AS IDENTITY, I know I can get the sequence name using something like pg_get_serial_sequence('whatever', 'id'). Is it possible to specify a sequence name in the CREATE TABLE statement?

I have a rather simple question when playing with a PG 15.1 database. I've tried to set up a simple inheritance case:

DROP TABLE IF EXISTS cities CASCADE;

CREATE TABLE IF NOT EXISTS cities (
  id INT UNIQUE PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "name" TEXT,
  population REAL,
  elevation INT
);

CREATE TABLE IF NOT EXISTS capitals (
  "state" CHAR(2) UNIQUE NOT NULL
) INHERITS (cities);

INSERT INTO cities (name, population, elevation) 
VALUES ('City One', 25000, 430), ('Town Two', 18000, 380), ('Urban 3', 30000, 400), ('Metropolitan 4', 50000, 450);

INSERT INTO capitals (name, population, elevation, state)
VALUES ('Capital 1', 1200000, 550, 'AK'), ('Capital 2', 1030000, 540, 'ZA');

But this raises the following error:

NOTICE:  drop cascades to table capitals
ERROR:  Failing row contains (null, Capital 1, 1.2e+06, 550, AK).null value in column "id" of relation "capitals" violates not-null constraint 

ERROR:  null value in column "id" of relation "capitals" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, Capital 1, 1.2e+06, 550, AK).

Which is OK I guess, because the doc says: > If a column in the parent table is an identity column, that property is not inherited. A column in the child table can be declared identity column if desired. Other people met the same issue and a frequent suggestion is to use the SERIAL type instead. Which I don't really want to do. So I also added the identity field on the child table:

DROP TABLE IF EXISTS cities CASCADE;

CREATE TABLE IF NOT EXISTS cities (
  id INT UNIQUE PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  "name" TEXT,
  population REAL,
  elevation INT
);

CREATE TABLE IF NOT EXISTS capitals (
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, -- added
  "state" CHAR(2) UNIQUE NOT NULL
) INHERITS (cities);

INSERT INTO cities (name, population, elevation) 
VALUES ('City One', 25000, 430), ('Town Two', 18000, 380), ('Urban 3', 30000, 400), ('Metropolitan 4', 50000, 450);

INSERT INTO capitals (name, population, elevation, state)
VALUES ('Capital 1', 1200000, 550, 'AK'), ('Capital 2', 1030000, 540, 'ZA');

This runs fine. But there is something strange now. Indeed, this first SELECT statement seems to be OK:

SELECT * FROM capitals;
 id |   name    | population | elevation | state 
----+-----------+------------+-----------+-------
  1 | Capital 1 |    1.2e+06 |       550 | AK
  2 | Capital 2 |   1.03e+06 |       540 | ZA
(2 rows)

But this one returns multiple times the same id in the first column:

SELECT * FROM cities;
 id |      name      | population | elevation 
----+----------------+------------+-----------
  1 | City One       |      25000 |       430
  2 | Town Two       |      18000 |       380
  3 | Urban 3        |      30000 |       400
  4 | Metropolitan 4 |      50000 |       450
  1 | Capital 1      |    1.2e+06 |       550
  2 | Capital 2      |   1.03e+06 |       540
(6 rows)

So it seems that the uniqueness of the identity field of the parent table is no more respected. I'm wondering *why* I met this behavior, and how could I properly deal with ids in case of inheritance in PostgreSQL? Especially, how can I keep a unique identifier on the parent table (if this makes sense)? An underlying target for me is to have the parent table having a FK on itself, e.g. a field called twined_with to be able to link two cities as twin cities.

We are having a strange intermittent issue where the identity column value falls behind the MAX(ID) for the table. Each customer gets their own table that stores statistics to the tune of [dbo].[Stat_customer1], [dbo].[Stat_customer2], etc. Randomly, one of these tables' identity values will fall behind, stopping any inserts from happening and we have no idea why. Here's one example: DBCC CHECKIDENT ('[dbo].[Stat_customer1]', NORESEED); GO Checking identity information: current identity value '43487', current column value '52012'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. SELECT MAX(ID) FROM [dbo].[Stat_customer1] 52012 To repair this we then do: DBCC CHECKIDENT ('[dbo].[Stat_customer1]', RESEED, 52012); GO Checking identity information: current identity value '52228', current column value '52228'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. We have about a thousand of these tables on average per SQL server. This will only occur on one random table, not all of them. It does not happen every day. Sometimes a week goes by with no issue. Data is imported to these tables every five minutes by an application that performs a bulk insert. Every night, a SQL job is executed that rolls up statistics by date. E.g. anything over a month old is summed up by month, anything over a year old is summed up by year. During this time, the application pauses importing data so no new data is imported during the roll-up. Data is read out of table into a temp table and is aggregated as it is being read out. We then delete the old data from the table and insert the new aggregated data from the temp table. We are not sure if it is the bulk insert or the statistics roll-up that is causing the issue. This issue started popping up after we upgraded from SQL 2008 R2 to SQL 2012 and virtualized the server. Does anyone have any clues as to why this is happening?

I have different tables, containing an id field. That field is typically defined as id (PK, int, not null). Normally, when adding an entry to a table, I add the value of the id field myself, but I'm wondering if there's no automatic way to do that: The following is not working: (there already is an entry with id being 1) INSERT [dbo].[Settings] ([GroupId], [Value], [Name], [Id], [Description], [Admin]) VALUES ( 1, N'1', N'BLABLA', 1, N'meer blabla', 0) The following is also not working (the id field not being filled in): INSERT [dbo].[Settings] ([GroupId], [Value], [Name], [Description], [Admin]) VALUES ( 1, N'1', N'BLABLA', N'meer blabla', 0) I know about the possibility to add a kind of IDENTITY table, which might handle this, but I'm very reluctant to add another table to the customer's database. Does anybody know a way to get this done? If possible, I would like to have two possibilities: - Finding the first possible id (using holes in the existing ids, if any): imagine that I have the following list of existing ids: (1, 2, 3, 4, 11, 12, 13, 14). Then I want 5 to be the next one. - Finding the highest id plus one: imagine that I have the following list of existing ids: (1, 2, 3, 4, 11, 12, 13, 14). Then I want 15 to be the next one. Does anybody know how to get this done in a one-liner SQL command, without altering the customer's database? For your information, I'm using Telerik OpenAccess for creating and accessing the database, and one of the items I can see is this: configuration.HasProperty(o => o.Id).IsIdentity(); However, for that table, I don't see that in SQL-Server: Thanks in advance

I have a table with the following structure:

CREATE TABLE categories (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name varchar NOT NULL
);

When I inserted some records with an explicit *id*:

INSERT INTO categories (id, name) VALUES (1, 'Children Bicycles'), (2, 'Comfort Bicycles'), (3, 'Cruisers Bicycles');

And then tried to insert a new record without specifying an *id*

INSERT INTO categories (name) VALUES ('Eletric Bicyles');

I got the error duplicate key value violates unique constraint "categories_pkey" I've already found this question [postgresql duplicate key violates unique constraint](https://stackoverflow.com/questions/4448340/postgresql-duplicate-key-violates-unique-constraint) where it says that happens due the sequence is outdated and the solution is to set the next value of the sequence to the next MAX value of the primary key plus one, but since I declare the primary key as an IDENTITY I'm not able to use the answer of that question as a solution for my case. So, my question is what should I do in order to set the next value of an IDENTITY in PostgreSQL?

I have the following table schema: CREATE TABLE [inputs].[source]( [Id] [int] IDENTITY(1,1) NOT NULL, [CreatedOn] [datetime] NULL, [ImportedOn] [datetime] NULL, [Identifier_Value] [nvarchar](25) NULL, [Identifier_PrefixLength] [int] NULL, [Identifier_SuffixLength] [int] NULL, [Identifier_CRC] [bigint] NULL, [Code 1_Value] [nvarchar](max) NULL, [Code 1_PrefixLength] [int] NULL, [Code 1_SuffixLength] [int] NULL, [Code 1_CRC] [bigint] NULL, [Code 2_Value] [nvarchar](max) NULL, [Code 2_PrefixLength] [int] NULL, [Code 2_SuffixLength] [int] NULL, [Code 2_CRC] [bigint] NULL, [Code 3_Value] [nvarchar](max) NULL, [Code 3_PrefixLength] [int] NULL, [Code 3_SuffixLength] [int] NULL, [Code 3_CRC] [bigint] NULL, [Code 4_Value] [nvarchar](max) NULL, [Code 4_PrefixLength] [int] NULL, [Code 4_SuffixLength] [int] NULL, [Code 4_CRC] [bigint] NULL, [Number_Value] [decimal](19, 5) NULL, [Approval_Value] [nvarchar](max) NULL, [Approval_NumberValue] [decimal](19, 5) NULL, [Volume_Value] [decimal](19, 5) NULL, [Program_Value] [nvarchar](max) NULL, [Program_NumberValue] [decimal](19, 5) NULL, [Info_Value] [nvarchar](max) NULL, [Info_PrefixLength] [int] NULL, [Info_SuffixLength] [int] NULL, [Info_CRC] [bigint] NULL, [UpdatedOn] [datetime] NULL, [FacilityTimeZoneId] [nvarchar](80) NULL, [ParsingTimeZoneId] [nvarchar](80) NOT NULL, [CreatedOnUtc] [datetime] NULL, [ImportedOnUtc] [datetime] NULL, PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] For tests, I've added 5 million rows into the table. Imagine you've been using this table for some time and [Id] column right now has 'gaps' between values for example (100, 120, 300, 3400 and so on). I know that it should not be the case, but because of some bad design desicions made before I need to update [Id] column to have values in sequence (1, 2, 3, 4 and so on). I've found the following solution: 1. Disable all nonclustered indexes on [inputs].[source] table 2. Create [inputs].[source_temp] table with the same schema but without identity on [Id] column 3. Switch to [inputs].[source_temp] table 4. Remove PK from [inputs].[source_temp] 5. Update [Id] column in [inputs].[source_temp] 6. Return PK to [inputs].[source_temp] 7. Switch back to [inputs].[source] 8. Rebuild disabled indexes Here is the script:

DECLARE @EnableOrRebuild as nvarchar(20) 
DECLARE @Sql AS nvarchar(MAX)='';
DECLARE @TableName as nvarchar(200) = 'source'
DECLARE @SchemaName as nvarchar(200) = 'inputs'
SET @EnableOrRebuild = 'DISABLE'

SELECT @Sql = @Sql + N'ALTER INDEX ' + quotename(i.name) + N' ON ' + quotename(s.name) + '.' + quotename(o.name) + ' ' + @EnableOrRebuild + N';' + CHAR(13) + CHAR(10)
  FROM sys.indexes i
 INNER JOIN sys.objects o ON i.object_id = o.object_id
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 WHERE i.type_desc = N'NONCLUSTERED'
   AND o.type_desc = N'USER_TABLE'
   AND o.name = @TableName 
   AND s.name = @SchemaName

EXEC (@Sql);


CREATE TABLE [inputs].[source_temp](
  [Id] [int] NOT NULL,
  [CreatedOn] [datetime] NULL,
  [ImportedOn] [datetime] NULL,
  [Identifier_Value] [nvarchar](25) NULL,
  [Identifier_PrefixLength] [int] NULL,
  [Identifier_SuffixLength] [int] NULL,
  [Identifier_CRC] [bigint] NULL,
  [Code 1_Value] [nvarchar](max) NULL,
  [Code 1_PrefixLength] [int] NULL,
  [Code 1_SuffixLength] [int] NULL,
  [Code 1_CRC] [bigint] NULL,
  [Code 2_Value] [nvarchar](max) NULL,
  [Code 2_PrefixLength] [int] NULL,
  [Code 2_SuffixLength] [int] NULL,
  [Code 2_CRC] [bigint] NULL,
  [Code 3_Value] [nvarchar](max) NULL,
  [Code 3_PrefixLength] [int] NULL,
  [Code 3_SuffixLength] [int] NULL,
  [Code 3_CRC] [bigint] NULL,
  [Code 4_Value] [nvarchar](max) NULL,
  [Code 4_PrefixLength] [int] NULL,
  [Code 4_SuffixLength] [int] NULL,
  [Code 4_CRC] [bigint] NULL,
  [Number_Value] [decimal](19, 5) NULL,
  [Approval_Value] [nvarchar](max) NULL,
  [Approval_NumberValue] [decimal](19, 5) NULL,
  [Volume_Value] [decimal](19, 5) NULL,
  [Program_Value] [nvarchar](max) NULL,
  [Program_NumberValue] [decimal](19, 5) NULL,
  [Info_Value] [nvarchar](max) NULL,
  [Info_PrefixLength] [int] NULL,
  [Info_SuffixLength] [int] NULL,
  [Info_CRC] [bigint] NULL,
  [UpdatedOn] [datetime] NULL,
  [FacilityTimeZoneId] [nvarchar](80) NULL,
  [ParsingTimeZoneId] [nvarchar](80) NOT NULL,
  [CreatedOnUtc] [datetime] NULL,
  [ImportedOnUtc] [datetime] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [inputs].[source] SWITCH TO [inputs].[source_temp];

ALTER TABLE [inputs].[source_temp] DROP CONSTRAINT [PK_Test];

;WITH CTE AS (
    SELECT
        [Id],
        ROW_NUMBER() OVER (ORDER BY [Id]) AS RN
    FROM [inputs].[source_temp]
)
UPDATE CTE
SET [Id] = RN
WHERE [Id] != RN;

ALTER TABLE [inputs].[source_temp] ADD CONSTRAINT [PK_Test] PRIMARY KEY (ID); 

ALTER TABLE [inputs].[source_temp] SWITCH TO [inputs].[source]


SET @EnableOrRebuild = 'REBUILD'

SELECT @Sql = @Sql + N'ALTER INDEX ' + quotename(i.name) + N' ON ' + quotename(s.name) + '.' + quotename(o.name) + ' ' + @EnableOrRebuild + N';' + CHAR(13) + CHAR(10)
  FROM sys.indexes i
 INNER JOIN sys.objects o ON i.object_id = o.object_id
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 WHERE i.type_desc = N'NONCLUSTERED'
   AND o.type_desc = N'USER_TABLE'
   AND o.name = @TableName 
   AND s.name = @SchemaName

EXEC (@Sql);

DROP TABLE [inputs].[source_temp];

The execution time is about 94 sec. Here is the [execution plan] (https://www.brentozar.com/pastetheplan/?id=rJJNpJFhn) . I found that if I don't remove PK on [inputs].[source_temp] the execution will take 236 sec. With this [execution plan](https://www.brentozar.com/pastetheplan/?id=HyeeMxF22) . Is it ok to remove PK in that case or there is a better solution. And maybe there is another approach to improve performance? I think the main goal is, "the fastest runtime regardless of how much hardware resources are consumed in doing so". This script will be executed when application is stopped for maintenance and "the downtime against this table specifically during this change" is not a problem. In short, due to some mistakes in the implementation of business logic the id values ​​in the table are close to their maximum (2,147,483,647), although the number of records in the table is not more than 10 - 20 million. The implementation is fixed, but the consequences in DB must be corrected. I want to reset IDs in the table and RESEED to the max ID. I know I can't prevent new gaps appearing in future. I just need to apply this script one time.

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: 1. Enable Identity Insert 2. Insert the row(s) with hard-coded integer IDs 3. 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 that a reasonable idea, and/or is there a better solution?** **Side Note:** we do have some other ideas, which I believe are out of scope for this question, for example: 1. A gated-checkin that would parse the scripts for inserting into certain tables without specifying the identity column, and fail if we detect this. 2. Store all of this data in source and update the entire table when deploying. (Rather than using run-once insert scripts.) 3. Don't run data scripts that alter these tables on all envs, but use replication or another syncing mechanism. Although these other ideas may be better in the long run, it seems like the lowest hanging fruit is just reseeding these tables so improper inserts will fail.