74
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');
...
- 180k
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 ...
- 83k
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 ...
- 83.3k
30
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 ...
- 97.4k
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 ...
- 180k
20
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 ...
- 172k
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 ...
- 77k
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.
...
- 32k
18
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 = ...
- 22.8k
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,...
- 9,095
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 ...
- 83.3k
16
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 ...
- 196
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 ...
- 4,053
15
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, ...
- 9,638
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 ...
- 97.4k
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) ...
- 83k
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 ...
- 42.7k
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 ...
- 1,571
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 ...
- 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 ...
- 3,523
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
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 ...
- 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 ...
- 22.8k
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, ...
- 180k
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 ...
- 69.7k
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 ...
- 69.7k
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 ...
- 68.3k
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 ...
- 62.2k
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 ...
- 1,053
Only top scored, non community-wiki answers of a minimum length are eligible