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
2
votes
1answer
27 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 ...
4
votes
1answer
4k 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-...
1
vote
1answer
263 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 ...
1
vote
1answer
486 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? ...
-2
votes
2answers
444 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 ...
0
votes
3answers
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 ,...
3
votes
1answer
314 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 ...
9
votes
3answers
24k 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 ......) ); ...
3
votes
1answer
158 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 ...
2
votes
1answer
738 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 ...
5
votes
3answers
1k 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 ...
0
votes
1answer
348 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 ...
2
votes
2answers
37 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 ...
4
votes
1answer
414 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 ...
4
votes
1answer
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 ...
18
votes
6answers
3k 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
vote
2answers
108 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 ...
11
votes
2answers
24k 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 ...
11
votes
3answers
8k 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 ...
22
votes
1answer
3k 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 ...
10
votes
1answer
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 ...
1
vote
1answer
140 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. ...
8
votes
1answer
697 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 ...
5
votes
3answers
2k 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 ...
2
votes
1answer
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 ...
8
votes
2answers
6k 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 ...
3
votes
1answer
246 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?
7
votes
1answer
613 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 -- ...
5
votes
3answers
5k 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 ( ...
2
votes
2answers
3k views

Does Clustered Index on GUID create more fragmentation than Non Clustered Index?

I have a simple table: CREATE TABLE [dbo].[UserTestGroups]( [UserTestGroupId] [int] IDENTITY(1,1) NOT NULL, [Token] [nvarchar](100) NOT NULL, [TestId] [bigint] NOT NULL, [Group] [...
0
votes
1answer
220 views

Identity column jump in sql server restart

I had many research on identity column jump in sql server restart. I know the reason why this happen as it explained here. I know the solution how to prevent this using either manually perform ...
2
votes
1answer
984 views

Having 2 identities in a table

I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly. I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', ...
7
votes
1answer
6k 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 ...
12
votes
1answer
1k 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 ...
0
votes
0answers
240 views

NEXT VALUE FOR SEQUENCE inside a CASE Statement

I want to use a new SEQUENCE each year (See here: 'Id' with the format: YYYYNNNNNN with the NNNNNN part restarting each year ) However you can not use NEXT VALUE FOR inside a CASE statement. ...
27
votes
6answers
5k 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 ...
2
votes
1answer
236 views

Identity management in Transaction replication with updatable subscription

I have migrated my publisher DB to new server yesterday. For migration what I did was removed subscriptions and publication completely backed up DB on which publication was configured restored DB to ...
0
votes
1answer
190 views

DBCC CHECKIDENT behaving inconsistently

I have a script that sometimes needs to be run on a fresh database that's just had all the tables created and sometimes needs to be run on an existing database. I have a script to wipe out the tables ...
0
votes
1answer
388 views

Select Into removes IDENTITY property from target table

I am doing an exercise which removes the IDENTITY property from the target table when using a select into statement. source table name: Sales.Orders column name: orderid column property: int ...
16
votes
5answers
71k 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'...
2
votes
2answers
381 views

Finding and listing all columns in a SQL Server database with identity [duplicate]

I wanted to RESEED all the identity columns in that database, so I need to create a script so I can find out which column in which table has the identity property.
8
votes
2answers
90k 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,...
41
votes
2answers
207k 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() ...
3
votes
1answer
13k views

MERGE with IDENTITY_INSERT ON does not work if Identity is not the primary key

I frequently use MERGE statements and am quite familiar with it. Now I ran into a situation where some tables have IDENTITY columns that are not the primary key. In this situation the script failed ...
7
votes
1answer
5k 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 ...
1
vote
1answer
230 views

Reseeded Identity not showing in GUI

I am reseeding my Identity Seed with the following T-SQL: USE DATABASENAME; GO DBCC CHECKIDENT ('dbo.DOCUMENT', RESEED, 400000000); GO I am subsequently checking it went through: USE DATABASENAME; ...
0
votes
0answers
40 views

What can be the cause of sudden leaps in identity? How can it be undone? [duplicate]

In one of our clients' (testers') databases the Identity column of a SQL Server Local Db datatable looks thus: So, most of the time the identity increases by one, but sometimes the identity jumps up ...
4
votes
2answers
8k views

Scope_Identity() returns null after inserting first row in table

I have a stored procedure that is used for inserting values in two tables. The tables have a parent-child relationship i.e. the first table has an identity column, and the second table references the ...
11
votes
1answer
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. ...
3
votes
1answer
458 views

Gigantic gaps in IDENTITY column

This question is about strange behavior in SQL Server 2008 R2 SP3 Standard Edition x64. I have read many questions about IDENTITY increments in SQL Server and strange gaps between them. But basically ...