Questions tagged [identity]

Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.

Filter by
Sorted by
Tagged with
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 ...
akagixxer's user avatar
  • 163
2 votes
1 answer
68 views

What are the origins of the Identity Column? [closed]

Who were the first creators of the Identity column? What problem were those creators originally trying to solve? Is there a story behind it?
Bob's user avatar
  • 21
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. ...
Crypt32's user avatar
  • 223
1 vote
0 answers
186 views

Identity column changing on its own?

I have a situation where a client is using software that writes to a SQL table. The program has been running fine with no issues prior to today. Today, they began receiving errors inserting NULL ...
Chris's user avatar
  • 19
0 votes
1 answer
391 views

Populate Identity column with sequential numbers restarting count on change of key field

I am attempting to populate an Identity column with sequential numbers but I want the count to start over when the value of a key field changes. Example data and desired ID column results: Month_ ...
Marlene's user avatar
  • 11
0 votes
1 answer
1k views

How to increment identity without add a row and get the skipped value?

I'm trying to preserve value in identity column in manner to be added later. The value should be used in implicit foreign key when there is no constraint. For example: I have this table: Create table [...
SHR's user avatar
  • 874
2 votes
1 answer
36 views

Database Entity Design for Enumeration Table

I have an enumeration of ~10 values - they are Char such as A through K. These are all unique. They are used for two purposes - an application is forced to select acceptable values and also there is a ...
StixO's user avatar
  • 167
0 votes
1 answer
1k views

How can I insert without changing the identity seed? [duplicate]

I will add the records in table A to table B. The tables are the same. However, table B is an actively transactional table. When doing the operation, the record in the tableB will continue to be added ...
Yunus UYANIK's user avatar
  • 1,079
2 votes
1 answer
56 views

SQL Server Replication changing a tables ID numbers

I am using a merge replication setup for our ERP system and I encountered this problem: While creating a new entry on a table and with the last ID number given was 25, then new entry got the ID ...
lefteris mavridis's user avatar
3 votes
1 answer
441 views

Clustered Index Maintenance vs. Nonclustered Index Maintenance

There is a plethora of information available detailing why an IDENTITY field should be use as both the primary key and clustered index of a table for most situations; yet I'm having difficulty ...
philomathic_life's user avatar
3 votes
1 answer
2k views

HSQLDB get next value for IDENTITY column

I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into. I did some ...
BrainStone's user avatar
0 votes
2 answers
2k views

SQL Server - Copying a key from one table to another

In a trigger, I have created a table using SELECT * INTO. I have done that in a specific way, in order to create the table without also creating the identity column that exists in it (let's name this ...
FaultyOverflow's user avatar
3 votes
2 answers
3k views

Availability Group, asynchronous mode, manual/forced failover - handling identity overlap

We have a two replica 2017 Standard Availability Group in asynchronous mode. With a failover (manual/forced, since can't do automatic), we have the potential for overlapping/reused identity values if ...
Bobogator's user avatar
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 ...
Ionut Popescu's user avatar
0 votes
1 answer
941 views

Restore all IDENTITY seeds (out of sync since database restore)

We have recently done a full restore on a database from a backup (schema & data). When trying to insert new rows we are getting the "Cannot insert duplicate key in object" error message, it is ...
Jacob Regan's user avatar
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 ...
tinlyx's user avatar
  • 3,425
4 votes
2 answers
6k views

Can IDENTITY COLUMNS generate UUIDs?

I'm just wondering if either the spec of the implementation (PostgreSQL) provides for generating identity columns from UUIds or the like. Is there an alternative to, CREATE TABLE f ( id uuid ...
Evan Carroll's user avatar
  • 62.2k
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 ...
Dave Chambers's user avatar
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 ...
danwoz's user avatar
  • 73
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 ...
Lewis Menelaws's user avatar
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 ...
Tibin Thomas's user avatar
2 votes
1 answer
48 views

Existing data to new table, how to set up the PK & Identity

I am moving an Access app to SQL Server. I have confirmed the data in the existing PK is unique so I want to copy it into the new PK Identity field. The field is currently set as IDENTITY(1,1). I ...
BattlFrog's user avatar
  • 183
5 votes
1 answer
9k views

How to get information about the sequence behind the new Identity Column in Postgres 10? (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY)

Postgres 10 brings an implementation of the SQL standard GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY feature, loosely known as identity column. This feature supplants Postgres’ own SERIAL pseudo-...
Basil Bourque's user avatar
1 vote
1 answer
369 views

identity value way off in a restored database

I have a table with a identity primary key column. Its last value is 5228491220 using IDENT_CURRENT. I make a full backup of the database, restore it on a second server. (We backup and restore daily ...
fredito's user avatar
  • 71
1 vote
1 answer
570 views

SQL GUID or Identity in the Cloud

Follow up from my previous question SQL Server Identity Column in the Cloud: Are there any general guidelines? Should a person use Clustered Index Identity or GUID in the cloud for SQL Server 2016? ...
user avatar
-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 ...
user avatar
0 votes
3 answers
1k views

Configured identity seed starting value on database level

Is there an option in sql server to configure the default starting value(seed) of an identity column on a database level . I want that by default, every table that created with an identity column ,...
itzik Paz's user avatar
  • 621
3 votes
1 answer
566 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 ...
User1974's user avatar
  • 1,447
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 ......) ); ...
kevin's user avatar
  • 255
3 votes
1 answer
404 views

Adding an identity field to be the new cluster of a large table - how do I get the "right" order?

I have a very large table (88m rows, nearly 300GB in size) that has a poorly planned clustered index. I want to add a BIGINT identity field, and make it the new clustered index (having the desirable ...
BradC's user avatar
  • 9,924
2 votes
1 answer
1k views

How to replace a single table with Identity Primary Key column from a database backup?

I need to restore a table from a backup. My understanding is the only way to get a single table back into the database is to copy it into a temporary table. Then drop the old table and rename the new ...
Geoff Dawdy's user avatar
  • 1,103
5 votes
3 answers
2k views

How to reduce identity values to avoid integer overflow?

I have some leaf tables (no FK towards them) with a few hundreds of thousands records that are used to synchronize some external data using Entity Framework ORM. This involves some DELETEs followed by ...
Alexei's user avatar
  • 1,261
0 votes
1 answer
1k views

Search all table, find PrimaryKeys with Id = int/bigint and enable Identity, autoincrement

I have a large list of tables and due to change in middleware platform, the middleware is no longer creating the auto-increment on Add New Record at the app layer, as a result, I need to do this in ...
Transformer's user avatar
2 votes
2 answers
41 views

Does anyone ever use database sequences that count down from 2^64-1?

Does anyone ever use database sequences that count down from 2^64-1, rather than up from 1, for assigning ids to rows? The motivation for doing this would be to ensure that any application consuming ...
user2800708's user avatar
4 votes
1 answer
586 views

Is there a way to catch a dbcc checkident command

A colleague of mine noticed incorrect ids generated for inserted items. Most probable cause: some script issues a command like the following: dbcc checkident('dbo.table', reseed, 1) I have thought ...
Alexei's user avatar
  • 1,261
4 votes
1 answer
2k views

Restoring database backup makes identity column start at 1 again

I created a backup of our database in SSMS 2012, using Tasks -> Backup as a Full Backup, including all data. When I restore this backup on another server (2012 as well), all identity columns are ...
F.P's user avatar
  • 207
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 ...
Alexei's user avatar
  • 1,261
1 vote
2 answers
417 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 ...
JP Hellemons's user avatar
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 ...
Felix Pamittan's user avatar
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 ...
Evan Carroll's user avatar
  • 62.2k
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 ...
ErikE's user avatar
  • 4,305
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 ...
Mr Moose's user avatar
  • 103
1 vote
1 answer
168 views

INSTEAD OF INSERT trigger

Consider this a follow-up to BULK INSERT: Trials and Tribulations I have the following VIEW defined and would like to insert into the multiple underlying tables when sending a BULK INSERT from a CSV. ...
mpag's user avatar
  • 968
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 ...
boot4life's user avatar
  • 1,289
5 votes
3 answers
4k views

How to change primary key's data type from int to bigint in a huge transactional database under tremendous load?

This is a legacy system, with more than 1 billion records in a table that undergoes 10 million transactions per day. Now the application complains that: Arithmetic overflow error converting ...
Saeed Neamati's user avatar
3 votes
1 answer
3k views

SET IDENTITY_INSERT OFF - Caused Table Lock

I Executed SQL on database that looked like this: SET IDENTITY_INSERT table1 ON GO BEGIN TRANSACTION GO INSERT INTO table1 SELECT ... FROM table2 GO DELETE FROM table2 ... GO COMMIT TRANSACTION GO ...
Svisstack's user avatar
  • 145
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 ...
PaolaG's user avatar
  • 193
3 votes
1 answer
267 views

Select a table's current identity from another database

How can I get the current identity of a table using OPENQUERY in SQL Server? How to select the current identity on the table from another database?
user94162's user avatar
7 votes
1 answer
771 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 -- ...
Shaneis's user avatar
  • 1,803
5 votes
3 answers
6k views

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

If I create a simple table like this: CREATE TABLE SimpleTable ( [Id] INT IDENTITY(1,1) NOT NULL, [Value] VARCHAR(10) NOT NULL, CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED ( ...
ctorx's user avatar
  • 153