Questions tagged [identity]
Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
192
questions
73
votes
10
answers
25k
views
Why do people recommend not using the name "Id" for an identity column?
I was taught not to use the name Id for the identity column of my tables, but lately I've just been using it anyways because it's simple, short, and very descriptive about what the data actually is.
...
- 8,437
49
votes
2
answers
284k
views
Best way to get last identity inserted in a table
Which one is the best option to get the identity value I just generated via an insert? What is the impact of these statements in terms of performance?
SCOPE_IDENTITY()
Aggregate function MAX()
...
- 4,053
48
votes
10
answers
74k
views
How do I copy a table with SELECT INTO but ignore the IDENTITY property?
I have a table with identity column say:
create table with_id (
id int identity(1,1),
val varchar(30)
);
It's well known, that this
select * into copy_from_with_id_1 from with_id;
results in ...
- 12.2k
30
votes
7
answers
3k
views
What are the minimum matching criteria recommended for reliable demographic based patient matching?
When matching patients based on demographic data are there any recommendations on what fields should match for the patient to be the "Same Patient"?
I know the algorithms will be different for ...
ScArcher2
30
votes
6
answers
6k
views
Can I rely on reading SQL Server Identity values in order?
TL;DR: The question below boils down to: 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 ...
- 575
27
votes
3
answers
11k
views
How to insert in a table with only an IDENTITY column?
Given a table with only an IDENTITY column, how do you insert a new row? I've tried the following:
INSERT INTO TABLE
(Syntax error)
INSERT INTO TABLE VALUES()
(Syntax error)
INSERT INTO TABLE (Id) ...
- 605
26
votes
1
answer
5k
views
Is it safe to rely on the order of an INSERT's OUTPUT clause?
Given this table:
CREATE TABLE dbo.Target (
TargetId int identity(1, 1) NOT NULL,
Color varchar(20) NOT NULL,
Action varchar(10) NOT NULL, -- of course this should be normalized
Code int ...
- 4,305
19
votes
6
answers
4k
views
What can be the downside of always having a single integer column as primary key?
Within one Web application I am working on, all database operations are abstracted using some generic repositories defined over Entity Framework ORM.
However, in order to have a simple design for the ...
- 1,261
19
votes
2
answers
18k
views
Unexpected gaps in IDENTITY column
I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script:
CREATE TABLE [dbo].[PONumbers]
(
[PONumberPK] [int] ...
- 352
18
votes
3
answers
86k
views
Reset IDENTITY value
I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
- 333
17
votes
4
answers
43k
views
Changing identity column from INT to BIGINT
I have a table with an identity column that is also a primary key. Currently, it has 50 million rows, with the highest value of the identity column sitting at 148,921,803. The table has a lot of ...
- 265
16
votes
5
answers
80k
views
Add autoincrement to existing PK
I created a table in a DB that already exists in another DB. It was initially populated with the old DB data. The table's PK had to receive the values that already exist on those records, so it couldn'...
- 1,533
15
votes
3
answers
14k
views
What are Identity Columns?
I was reviewing the commit-fest scheduled for 7/01 for PostgreSQL and I saw that Pg is likely going to get "identity columns" sometime soon.
I found some mention in information_schema.columns but ...
- 62.2k
14
votes
2
answers
45k
views
Is it recommended to use Identity as an alternative to primary key?
We can declare an Identity like id_num so that id_num will have an increment of unique numbers.
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname ...
- 2,489
14
votes
3
answers
14k
views
Why is SSMS inserting new rows at the top of a table not the bottom?
Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using ...
- 2,063
13
votes
1
answer
2k
views
Where does SQL Server physically store the IDENTITY VALUE for a table?
I'm hoping someone can point me in the right direction on this one. Here's my workings out so far.
SELECT * FROM sys.identity_columns is a system view which gives "last_value" but the definition for ...
- 145
12
votes
4
answers
2k
views
Identity column re-seed: when it is necessary?
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.
...
- 223
12
votes
1
answer
3k
views
Why is removing the Identity property on a column not supported
I have read that after SQL Server 2000, the ability to "un-identity" an identity column was removed. And that this was "By Design" (not just a missing feature).
Here is an example I found on a blog. ...
- 2,484
12
votes
1
answer
16k
views
Restarting identity columns in Postgresql
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, ...
- 355
12
votes
2
answers
7k
views
Composite Primary Key efficiency as a Foreign Key
I have a table with a Composite Primary key (consisting of 4 columns) which is used to ensure no duplicates are entered into the table. I am now in need of a new table which will need to reference the ...
- 437
12
votes
2
answers
4k
views
How does IDENTITY_INSERT affect concurrency?
I am trying to help a customer with a 3rd party SAP add-on that has a posting glitch and has gone out-of-support.
Under certain circumstances, it archives and incomplete post from the posting queue ...
- 890
11
votes
8
answers
3k
views
Identity Columns or UDF that explicitly generates a unique id?
I am in middle of a debate about whether it is better to make a PRIMARY KEY out of an Identity Columns, our out of a UDF that explicitly generates a unique id.
I am arguing for the Identity Column.
...
- 2,032
11
votes
1
answer
15k
views
How to add a PostgreSQL 10 identity column to an existing table?
I'm reading about the new Postgres 10 identity column, and saw how it is used to replace SERIAL columns when CREATE TABLE.
Is it also possible to add an identity column to an existing table?
For ...
- 3,425
10
votes
1
answer
92k
views
How to extract the last inserted row in SQL Server?
When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?
- 153
10
votes
3
answers
19k
views
How to get last identity row Inserted when using instead of trigger
When I insert into tables using instead of triggers, @@Identity, IDENT_CURRENT('Table') and SCOPE_IDENTITY() return null. How I can get the last identity of inserted row?
- 1,175
10
votes
3
answers
31k
views
How to copy migrate data to new tables with identity column, while preserving FK relationship?
I want to migrate data from one database to another. The table schemas are exactly the same:
CREATE TABLE Customers(
[Id] INT NOT NULL PRIMARY KEY IDENTITY,
(some other columns ......)
);
...
- 255
10
votes
1
answer
1k
views
Changing primary key from IDENTITY to being persisted Computed column using COALESCE
In an attempt to decouple an application from our monolithic database, we've tried to change INT IDENTITY columns of various tables to be a PERSISTED computed column that uses COALESCE. Basically, we ...
- 103
9
votes
6
answers
18k
views
When is the Identity Column as Clustered Index a Bad Idea?
Its just been a few months of programming in SQL Server for me so my knowledge is not good in many regards. In an already existing project at work I came across many tables with large composite ...
- 193
9
votes
2
answers
8k
views
Create a "INTO" table with primary key
Maybe for this community my problem is easy, but for me (a simple Java programmer) it's a BIG problem.
I have a Big DB with more and more data. So, the external db admin had create a job that show me ...
- 193
9
votes
1
answer
2k
views
What is the idiomatic solution in SQL Server for reserving a block of ids for use in a bulk insert?
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 ...
9
votes
1
answer
880
views
Is $IDENTITY documented and reliable in SQL Server?
I learned that SQL Server can return the value of the (single) identity column of a table using the pseudo-column $IDENTITY:
SELECT $IDENTITY FROM Table
Is this feature documented and reliable? The ...
- 1,289
8
votes
2
answers
2k
views
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?
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,...
- 355
8
votes
2
answers
102k
views
Change IDENTITY_INSERT to ON in SQL server [duplicate]
How can I insert an ID into an identity column in SQL Server? I'll make a special item into my table and I will quickly detect that item by the ID. So because all other ID's are bigger or equal to one,...
- 930
8
votes
1
answer
2k
views
How to add a PostgreSQL 10 identity column to an existing table with rows?
I have a table with some rows and I try adding an identity column using the command:
ALTER TABLE sourceTable
ADD COLUMN ogc_fid int
GENERATED BY DEFAULT AS IDENTITY;
The ...
7
votes
2
answers
24k
views
How can I change an existing type from "bigint" to "bigserial"?
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 ...
- 175
7
votes
3
answers
4k
views
What could cause the wrong ID to be inserted?
I have a SQL Server 2008 server (build 10.0.5500). Earlier this week I ran this on a table that already had data in it:
delete from dbo.table
go
dbcc checkident('dbo.table',reseed,0)
When the ...
- 3,028
7
votes
2
answers
7k
views
Why can a SQL Server table not have more than one IDENTITY column?
Why can a SQL Server table not have more than one identity column?
CREATE TABLE t(id INT IDENTITY, id2 INT IDENTITY)
Msg 2744, Level 16, State 2, Line 5
Multiple identity columns specified for ...
- 663
7
votes
1
answer
772
views
Is there a way to see how SQL Server 2012 defines new identities?
On Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64), is there a way to see what mechanism SQL Server uses to calculate the new identity values for tables created by SELECT INTO?
SAMPLE DATA
-- ...
- 1,803
7
votes
2
answers
2k
views
Reseed identity column in all empty tables
I need to write a query that resets the identity on all tables that have an identity column and are empty.
- 311
7
votes
1
answer
3k
views
Insert multiple rows into a table with only an IDENTITY column [duplicate]
I have a table called dbo.Groups defined like this:
CREATE TABLE dbo.Groups
(
GroupID int NOT NULL IDENTITY (1,1) PRIMARY KEY
);
The table really consists of just the one IDENTITY column.
...
- 22.8k
7
votes
2
answers
3k
views
PostgreSQL 10 Identity Column gets "null value" when inserting multiple rows with default keyword
I recently upgraded from PostgreSQL 9.5 to PostgreSQL 10. One of the nifty features in PostgreSQL 10 is the new identity column type, an alternative to PostgreSQL' serial pseudo-type. Official ...
- 73
7
votes
1
answer
2k
views
Getting Identity values to use as FK in an INSTEAD OF trigger
I have a series of updateable views we are exposing to end users as the interface for a back end process.
One of these views references two tables and requires an INSTEAD OF trigger for UPDATE and ...
- 17.9k
7
votes
1
answer
6k
views
Sudden PRIMARY KEY violation on IDENTITY column
I have a log table with an ID identity column. Everything works fine for years; then, yesterday, I see the following error in the logs:
The statement has been terminated.
Violation of PRIMARY KEY ...
- 3,155
6
votes
1
answer
2k
views
Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?
I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here.
I always thought that I could trust that identity values ...
- 12.2k
6
votes
1
answer
11k
views
Identity value jumps when restarting SQL Server
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 (...
6
votes
3
answers
42k
views
What could cause an auto increment primary key to skip numbers? [duplicate]
I have a table created with the following T-SQL:
CREATE TABLE [dbo].[TableName](
[id] [int] IDENTITY(1,1) NOT NULL
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, ...
- 1,001
6
votes
3
answers
1k
views
Regretting an identity: Is there a way to force inserts to specify the identity column?
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, ...
- 245
6
votes
2
answers
2k
views
Serializable range deadlocks
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 ...
- 555
6
votes
1
answer
5k
views
PostgreSQL How to DEFAULT Partitioned Identity Column?
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
...
- 163
6
votes
4
answers
3k
views
Primary Key choice on table with unique identifier
I'm reconsidering my choice of Primary Key on a table, and would like some input.
Some context: We are building a REST API, where clients access "Assets" identified by a GUID. So, the table for ...
- 275