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
2 answers
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] [...
gdoron's user avatar
  • 141
0 votes
1 answer
264 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 ...
Sagar Upadhyay's user avatar
2 votes
1 answer
1k 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', ...
Milad Firouzi's user avatar
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 ...
user1716729's user avatar
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 ...
Simon Jones's user avatar
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 ...
Fabian Schmied's user avatar
2 votes
1 answer
326 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 ...
SQLPRODDBA's user avatar
  • 1,908
0 votes
1 answer
334 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 ...
lathomas64's user avatar
0 votes
1 answer
722 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 ...
user2061466's user avatar
16 votes
5 answers
79k 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'...
Hikari's user avatar
  • 1,533
2 votes
2 answers
413 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.
DnL's user avatar
  • 211
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,...
H. Pauwelyn's user avatar
49 votes
2 answers
283k 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() ...
AA.SC's user avatar
  • 4,053
4 votes
1 answer
17k 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 ...
Magier's user avatar
  • 4,757
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 ...
Heinzi's user avatar
  • 3,145
1 vote
1 answer
290 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; ...
tmwoods's user avatar
  • 145
0 votes
0 answers
48 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 ...
Dabblernl's user avatar
  • 101
4 votes
2 answers
11k 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 ...
user avatar
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. ...
Vaccano's user avatar
  • 2,484
3 votes
1 answer
547 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 ...
kakaz's user avatar
  • 243
4 votes
1 answer
305 views

SQL 2012 insert data into a table from another table and custom columns

I'm trying to insert data from one table into another table and I also have 4 columns that are not in the source table but need to be populated in the target table. Here is the Insert statement SET ...
Pawel85's user avatar
  • 441
2 votes
1 answer
1k views

Identity column value falling behind randomly

We are having a strange intermittent issue where the identity column value falls behind the MAX(ID) for the table. Each customer gets their own table that stores statistics to the tune of [dbo].[...
Joose's user avatar
  • 21
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] ...
Ege Ersoz's user avatar
  • 352
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.
Jack's user avatar
  • 311
1 vote
0 answers
708 views

Can I simulate a sequence in pre-2012 SQL Server, with separate identity column, in function style?

A database on SQL Server 2005/2008/R2 is currently using an identity column in a separate table as primary key source. The reason is that the application, with NHibernate as ORM, needs to know the PK ...
Erik Hart's user avatar
  • 500
5 votes
1 answer
2k views

Adding PRIMARY KEY and IDENTITY attributes to SQL Server 2008 tables

An application is using an SQL Server 2008 database that has ID columns, but these columns are not primary keys and have no IDENTITY attribute. What I need to do now is adding the PKs and IDENTITY ...
Federico Razzoli's user avatar
0 votes
1 answer
142 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 |...
Jyrkka's user avatar
  • 137
0 votes
1 answer
2k views

Using Identity Insert on a linked server

We have a database hosted in Microsoft Azure and using this database connection details I have created a linked server object in SSMS on our on-premise SQL box and I can insert into it as following ...
Jay's user avatar
  • 163
1 vote
1 answer
2k views

Identity seed increased when using IDENTITY_INSERT

Running this snippet on SQL Server 2008 produces different result than on SQL Server 2014. I need them to produce the same result. My scenario is that I am writing unit tests (using tsqlt) and this ...
Peter Henell's user avatar
0 votes
1 answer
93 views

Set Identity as (11 - 2) in sql table

I have a issue, I want to set the ID column, where the ID should start from 11-2 then it should auto- increment by 1 like 11-3, 11-4. I am using sql-server 2008 R2. Please help
hud's user avatar
  • 203
0 votes
1 answer
798 views

Mysql Get Column Name after Case Max [closed]

i have this scenario with this query that trasform multiple rows key/values in a columns. SELECT t1.*, t2.*, u.name, u.email, u.username, MAX(CASE WHEN t2.datakey = 'latitude' THEN t2.data END) ...
Mark's user avatar
  • 1
3 votes
2 answers
23k views

Assigning new auto increment value [duplicate]

I have a table that is created as below. CREATE TABLE [dbo].[Table1]( [ID] [INT] IDENTITY(1,1) NOT NULL, [Title] [VARCHAR](255) NULL, ... Lets say that the latest ID is 100 ( I have 100 ...
Dust's user avatar
  • 137
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 (...
Alok Chandra Shahi's user avatar
5 votes
1 answer
6k views

How to remove identity specification for multiple tables

I have 109 tables in my database. Now I am migrating to Amazon server with identity insert and I want to remove auto increment column specification for all tables. How set auto increment column i.e. ...
Dinesh Reddy Alla's user avatar
0 votes
1 answer
61 views

Composite Key for Gizmo Tracking

I'm trying to setup a many to many relationship between an gizmo table and a owner table. A owner can have many gizmos And a gizmo can have many owners When I have a linking table do I set the ...
dbollig's user avatar
-1 votes
1 answer
2k views

SQL Server: performance of IDENTITY vs INT as primary key

The most important table of my database is about 300,000 records, and growing. More than 20 tables have a FK to it. Its primary key is a number, but for historical reasons is defined as nvarchar(6) ...
carlo.borreo's user avatar
  • 1,457
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 ...
dark_perfect's user avatar
0 votes
2 answers
2k views

How to create identity field in MySQL without using auto_increment column?

I am trying to create a dynamic system when a user can create different objects and relate them to another objects. I need is to be able to generate an identity column similar to auto_increment but ...
Mike's user avatar
  • 233
0 votes
1 answer
30 views

Separate table creation at runtime or add column

I am planning to keep patient data in a patient table. I will have multiple Clients registered with me, and each client will have multiple doctors. Should I make patient table client wise or a single ...
Chintan Mehta's user avatar
0 votes
1 answer
2k views

How can I reseed all identity columns?

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...
Shayma Ahmad's user avatar
3 votes
2 answers
7k views

How to update ID=null values in table to incremental counter values?

On SQL Server 2012, i've got an intermediate/staging table for merging existing with new data, where I want to insert numeric IDs for newly created rows: ID NaturalID Comment 1 ...
Erik Hart's user avatar
  • 500
4 votes
2 answers
27k views

Using scope_identity() to get ID "during" insertion of row?

I have a column that has an ident_current constraint on it to populate another column, like so: alter table dbo.Employee add constraint dbo_Employee_D1 default ident_current('dbo.Employee') for ...
aliensurfer's user avatar
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 ...
Kai's user avatar
  • 193
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 ...
Metaphor's user avatar
  • 890
2 votes
0 answers
325 views

Identity in a view treated as ordinary int - migrating from SQL Server 2000 to 2012

I'm migrating a very old platform from a SQL Server 2000 backend and MS Access 2003 as its frontend to SQL Server 2012 backend and MS Access 2010 as the frontend. This obviously means a bit of ...
Mackan's user avatar
  • 181
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, ...
Mansfield's user avatar
  • 1,001
4 votes
3 answers
69k views

SQL Server - Copy data from one database to another - identical tables, new identity values needed

Here's the thing, I have been running identical copies of my website for two different geographical regions. The site now supports multi-currency pricing so I want to merge both sites together. All ...
Jordan's user avatar
  • 41
4 votes
2 answers
280 views

removal of GUIDS used for all PK's, FKs & Clustered indexes

Inherited control over a database that has this nasty config. It also has large sections of code generated by NHibernate, including the gneeration of GUIDs before they get to the db so no chance of ...
DamagedGoods's user avatar
  • 2,591
3 votes
1 answer
16k views

Get identity value of all rows inserted

I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement? Example insert ...
IT researcher's user avatar
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 ...
Onion-Knight's user avatar
  • 1,099