Questions tagged [identity]
Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
37
questions
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
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
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
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
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
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
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
5
votes
1
answer
9k
views
How to set identity_insert to on in SQL Server
I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created ...
- 351
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
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
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
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
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
0
votes
1
answer
143
views
How to store a version sequence per product
I need to create a table like the following one:
+----+---------+---------+
| Id | Product | Version |
+----+---------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 |...
- 137
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
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
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
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
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
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
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
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
5
votes
6
answers
11k
views
using IDENTITY column with increment -1
I need to run a sql server logging db with 2 main tables in 2 seperate datacentres writing to both at the same time.
I had the idea of restoring the db to the new datacentre and then reseeding the ...
- 2,591
3
votes
1
answer
568
views
Can an Oracle IDENTITY column be nullable?
I want to create an ID column that automatically generates unique ID numbers by default.
Users won't be inserting rows via INSERT statements, rather, they'll create rows using an out-of-box ...
- 1,447
3
votes
4
answers
3k
views
Merge Replication identity field issues
One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of ...
Paul Joicey
2
votes
2
answers
4k
views
Custom Sequences with SQL Server
What is the best method to generate a Custom Auto-Generated Sequence with SQL Server?
Here is my requirement:
My application is hosted on 10 independent servers which are not
interconnected.
Each ...
- 23
2
votes
1
answer
5k
views
How do Identity Columns get Replicated?
During Transactional Replication, we have data going from Server A to Server B.
Say we were to reseed an Identity Column on a Table on Server A (for whatever reason), how would this affect the data ...
- 1,099
2
votes
2
answers
1k
views
Primary keys due to inheritance
I am trying to design a database for postgrad office and we have a parent class "User" that has a username as a primary key for authentication and we have corresponding subclasses "...
1
vote
2
answers
420
views
Generate INSERT list but only for non-nullable columns
I am currently using SSMS 2016 as client and have an Azure SQL database (also 2008 r2).
But I am looking for a way to create a custom template to have an insert statement prepared with only the ...
- 255
1
vote
2
answers
15k
views
How to fix all duplicate key values from sequences?
I have a Postgres database that I created with a Django application. When I transferred it over, it never reindexed anything and it gives me errors trying to do this. It seems as if there are lots of ...
- 111
1
vote
2
answers
2k
views
Can I implement a 'gapless' identity column in Oracle?
In SQL Server it is so easy to write:
create table #tmp (
id integer identity(1,1) primary key,
message varchar(256)
);
When I try to migrate this to Oracle, I end up with:
CREATE ...
- 12.2k
0
votes
1
answer
965
views
Is IDENTITY continuous in new versions of PostgreSQL? [duplicate]
Now I am use serial to generate table primary key in PostgreSQL 13, but today I found the ID jumps and has big gaps in the ID space. It will waste many IDs. I read the docs and found that PostgreSQL ...
- 755
-2
votes
2
answers
521
views
Problems with having SQL Server Identity Column in the Cloud
Our company is utilizing Identity(1,1) clustered primary index in SQL Server 2016 Enterprise, and will eventually deploy into the Cloud (Amazon AWS, Google Cloud).
Is there any issues utilizing ...
user129291