67 votes
Accepted

Best way to get last identity inserted in a table

Use SCOPE_IDENTITY() if you are inserting a single row and want to retrieve the ID that was generated. CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1)); INSERT #a(x) VALUES('a'); ...
user avatar
31 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 ...
user avatar
  • 72.4k
30 votes
Accepted

Unexpected gaps in IDENTITY column

This is a known and expected issue - the way IDENTITY columns are managed by SQL Server has changed in SQL Server 2012 (some background); by default it will cache 1000 values and if you restart SQL ...
user avatar
28 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 ...
user avatar
  • 78.5k
26 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 ...
user avatar
22 votes
Accepted

Why is removing the Identity property on a column not supported

Your question is, essentially: Why can I no longer do this risky thing that I should never have been allowed to do in the first place? The answer to that question is largely irrelevant (though you ...
user avatar
19 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 ...
user avatar
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 ...
user avatar
  • 69.7k
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. ...
user avatar
17 votes
Accepted

Add autoincrement to existing PK

The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column,...
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 ...
user avatar
  • 78.5k
16 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 ...
user avatar
15 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 = ...
user avatar
  • 21.3k
15 votes
Accepted

Change IDENTITY_INSERT to ON in SQL server

Explicit identity insert require IDENTITY_INSERT property set to ON. SET IDENTITY_INSERT MyTable ON -- Statement Allows explicit values to be inserted into -- the ...
user avatar
  • 3,953
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) ...
user avatar
  • 72.4k
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 ...
user avatar
  • 41.4k
14 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 ...
user avatar
14 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 ...
user avatar
13 votes
Accepted

Identity value jumps when restarting SQL Server

I found that it is a new feature (don't know what is use of it) Before SQL Server 2012, identity allocations were always individually logged (as each value was used). This per-row logging activity ...
user avatar
  • 72.4k
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 ...
user avatar
  • 341
13 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 ...
user avatar
  • 1,523
12 votes
Accepted

Assigning new auto increment value

You can use DBCC CHECKIDENT to reseed the IDENTITY column. Here is a sample you can run: SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.foo(ID INT IDENTITY(1,1)); GO INSERT dbo.foo DEFAULT VALUES;...
user avatar
12 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 ...
11 votes
Accepted

How to remove identity specification for multiple tables

There is no built-in command to remove the identity property from a column in SQL Server. You may have to create a new table, copy all the data across, and then rename the table. This can be time-...
user avatar
  • 72.4k
11 votes

Reseed identity column in all empty tables

Since you can reset the IDENTITY by issuing a simple TRUNCATE: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' + CHAR(13) +...
user avatar
11 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 ...
user avatar
  • 3,393
10 votes

Best way to get last identity inserted in a table

Apart from performance, they all have rather different meanings. SCOPE_IDENTITY() will give you the last identity value inserted into any table directly within the current scope (scope = batch, ...
user avatar
  • 9,488
10 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 ...
user avatar
  • 2,136
9 votes
Accepted

How does IDENTITY_INSERT affect concurrency?

Setting IDENTITY_INSERT ON on its own does not eliminate concurrency - this does not place any exclusive locks on the table, only a brief schema stability (Sch-S) lock. So what could theoretically ...
user avatar
9 votes

Scope_Identity() returns null after inserting first row in table

If you call the procedure multiple times, those are different scopes, so SCOPE_IDENTITY() is expected to be null. And you need to be careful about concepts - if you call the procedure multiple times, ...
user avatar

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