Skip to main content
Share Your Experience: Take the 2024 Developer Survey
33 votes
Accepted

Can I rely on reading SQL Server Identity values in order?

When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer ...
Paul White's user avatar
  • 85.5k
31 votes
Accepted

Is it safe to rely on the order of an INSERT's OUTPUT clause?

Can I rely on the returned identity values from the dbo.Target table insert to be returned in the order they existed in the 1) VALUES clause and 2) #Target table, so that I can correlate them by ...
Martin Smith's user avatar
  • 84.8k
31 votes

What are Identity Columns?

This is to implement the feature found in the standard. (copied from a draft, date: 2011-12-21): 4.15.11 Identity columns The columns of a base table BT can optionally include not more than ...
ypercubeᵀᴹ's user avatar
21 votes
Accepted

How can I change an existing type from "bigint" to "bigserial"?

bigserial is a pseudo-type, a notational convenience that is resolved to type bigint internally - plus a sequence, a column default, a dependency and an ownership. Basic commands to convert an ...
Erwin Brandstetter's user avatar
20 votes
Accepted

What can be the downside of always having a single integer column as primary key?

Other than additional disk space (and in turn memory usage and I/O), there's not really any harm in adding an IDENTITY column even to tables that don't need one (an example of a table that doesn't ...
Aaron Bertrand's user avatar
19 votes

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

You can use a join to create and populate the new table in one go: SELECT t.* INTO dbo.NewTable FROM dbo.TableWithIdentity AS t LEFT JOIN dbo.TableWithIdentity ON 1 = 0 ; Because of the 1 = ...
Andriy M's user avatar
  • 23k
19 votes
Accepted

Identity column re-seed: when it is necessary?

That is, when table row is deleted, it's PK must be reused in subsequent inserts. What universe is your lecturer from?? That is grossly inefficient. If you try to do that, you will cut your ...
Rick James's user avatar
  • 78.3k
19 votes
Accepted

As there is no unsigned int in SQL Server doesn't an Identity Seed of -2,147,483,648 make more sense for large tables?

There is nothing at all wrong with starting at -2,147,483,648 as far as SQL Server is concerned. Starting at 2,147,483,647 and counting backwards with IDENTITY(2147483647,-1) is perfectly valid too. ...
David Spillett's user avatar
18 votes
Accepted

Changing identity column from INT to BIGINT

As there is a primary key defined on identity column you wont be able to directly alter this column. Both the approaches that you have mentioned in your question can be used and downtime depends on ...
Shashank Tiwari's user avatar
16 votes
Accepted

Why can a SQL Server table not have more than one IDENTITY column?

I don't think there's any real "internals" reason. The metadata is stored at column level not table level. It would need a rethink though of scalar functions such as scope_identity() and pseudo column ...
Martin Smith's user avatar
  • 84.8k
15 votes
Accepted

How to add a PostgreSQL 10 identity column to an existing table?

You need to declare the type of the column, too (INT or BIGINT or SMALLINT): ALTER TABLE sourceTable ADD COLUMN ogc_fid int -- int or bigint or smallint GENERATED BY ...
ypercubeᵀᴹ's user avatar
14 votes

Is $IDENTITY documented and reliable in SQL Server?

$IDENTITY (and $ROWGUID) are documented in SELECT Clause (Transact-SQL). I would caution against using it because it will throw an error when a table doesn't have an identity column. Take these two ...
Brent Ozar's user avatar
14 votes
Accepted

Is there a way to see how SQL Server 2012 defines new identities?

SELECT INTO has two phases (not visible in execution plans). First, it creates a table that matches the metadata of the query used to create it. This happens in a system transaction, so the (empty) ...
Paul White's user avatar
  • 85.5k
14 votes

Changing identity column from INT to BIGINT

Aaron Bertrand has a 4-part series on this topic, starting with: Minimizing impact of widening an IDENTITY column – part 1 If you absolutely need to move to bigint, must minimize downtime, and have ...
14 votes
Accepted

What is the idiomatic solution in SQL Server for reserving a block of ids for use in a bulk insert?

You can use procedure (introduced in SQL Server 2012): sp_sequence_get_range To use it you need to create a SEQUENCE object and use it as a default value instead of IDENTITY column. There is an ...
Piotr Palka's user avatar
  • 1,591
13 votes

What can be the downside of always having a single integer column as primary key?

From my experience, the main and overwhelming reason to use a separate ID for every table is the following: In almost every case my customer swore a blood oath in the conception phase that some ...
AnoE's user avatar
  • 341
13 votes
Accepted

How to copy migrate data to new tables with identity column, while preserving FK relationship?

Here's a way that scales easily to three related tables. Use MERGE to insert the data into the copy tables so that you can OUTPUT the old and new IDENTITY values into a control table and use them for ...
Mister Magoo's user avatar
  • 3,543
11 votes
Accepted

Are auto incrementing identities always given in the order of inserts in SQL Server?

If this is the only transaction on that table, then yes. However, another transaction could also do an insert at the same time, and be somewhere in between 1-100, unless you use SERIALIZABLE ...
Chad Mattox's user avatar
  • 2,156
10 votes
Accepted

Insert multiple rows into a table with only an IDENTITY column

As of writing this, there is no way to insert multiple rows into just an IDENTITY column using an INSERT statement. The DEFAULT VALUES placeholder stands for just one row. And the INSERT ... SELECT ...
Andriy M's user avatar
  • 23k
9 votes

Create a "INTO" table with primary key

Some alternatives to adding the auto-increment column via the IDENTITY() function as suggested by @Shaneis are: Create the table explicitly using CREATE TABLE instead of using SELECT INTO. I much ...
Solomon Rutzky's user avatar
9 votes
Accepted

Does Clustered Index on GUID create more fragmentation than Non Clustered Index?

Since you are indicating insert performance is the primary concern, I'd take the recommendation of the DBA and make the clustering key the identity column since it is a unique, monotonically ascending ...
Hannah Vernon's user avatar
  • 70.1k
9 votes
Accepted

Where does SQL Server physically store the IDENTITY VALUE for a table?

If you can access the DAC (Dedicated Administrator Console), you can inspect the value of the identity column, for INT columns, by looking at the idtval column in sys.syscolpars. Thanks to Martin ...
Hannah Vernon's user avatar
  • 70.1k
9 votes
Accepted

What are Identity Columns?

How they're actually implemented in PG 10 You can see how they're actually implemented now using the test suite's expected output. Some keys to take away from this. You can specify where to start ...
Evan Carroll's user avatar
  • 63.5k
9 votes

Identity column re-seed: when it is necessary?

Reusing an identity value, should in general be discouraged. Either the value is used entirely internally, in which case it’s actual value is immaterial, or it is also used externally in which case ...
jmoreno's user avatar
  • 1,065
9 votes
Accepted

Serializable range deadlocks

We frequently experience deadlocks around an INSERT statement on the following table ... the stored procedure where the deadlocks occur [uses] SERIALIZABLE Yes. That is the expected behavior of ...
David Browne - Microsoft's user avatar
9 votes

As there is no unsigned int in SQL Server doesn't an Identity Seed of -2,147,483,648 make more sense for large tables?

No, you're not missing anything. Identities are meant to be meaningless internal-only surrogates which are for the computer to allocate and deal with, and the computer doesn't care whether you use ...
Michael Green's user avatar
8 votes

Can I rely on reading SQL Server Identity values in order?

As Paul White answered absolutely correct there is a possibility for temporarily "skipped" identity rows. Here is just a small piece of code to reproduce this case for your own. Create a database and ...
Stefan Kainz's user avatar
8 votes
Accepted

SET IDENTITY_INSERT OFF - Caused Table Lock

SET IDENTITY_INSERT is just a session option allowing you to insert identity values explicitly in a subsequent statement. It doesn't do anything to the transaction log. I presume your first ...
Martin Smith's user avatar
  • 84.8k
8 votes

How to fix all duplicate key values from sequences?

The code you display is not related to "reindexing". (Compare: REINDEX). It gets a sequence behind a serial column (or IDENTITY column in Postgres 10 or later) back in sync - by setting it ...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible