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
2answers
2k 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 ...
0
votes
2answers
136 views

pg_restore fails with drop default on identity column

Trying to restore a Postgres 10.6 database via pg_restore, and it seems to want to do a DROP DEFAULT when it should be doing a DROP IDENTITY during the clean phase. See below: pg_restore --verbose -...
2
votes
1answer
217 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
356 views

Create trigger to ignore user input for Identity Columns

Using the this table definition CREATE TABLE testtable ( id NUMBER GENERATED AS IDENTITY, desc VARCHAR2(30) ); I would like to create a trigger before inserts and updates to ...
2
votes
0answers
44 views

Is a three-way associative relationship correct in this case

So I have started modelling some entity relationships. It goes as follows: A Mandate represent a client. A row for a client (Mandate) will only appear once in the Mandate table. Each Mandate can ...
0
votes
1answer
131 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 |...
4
votes
2answers
7k 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 ...
8
votes
1answer
65k 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?
0
votes
1answer
1k 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 ...
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 ...
8
votes
3answers
21k 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 ......) ); ...
1
vote
1answer
57 views

Grant rights to create a table with an identity column on another schema

I have a user in charge of deploying DDL on other schemas than its own. This user can create a table on those schemas, it can also create sequences on those schemas. But when I try to create a table ...
0
votes
2answers
79 views

What's the easiest way to move tables with identity key columns while keeping the referential integrity between them?

I have four tables in database A that have identity key columns and have foreign key references to each other on those columns (without actual foreign key objects created, just the columns themselves)....
2
votes
2answers
233 views

GUID vs Identity on a table performance [duplicate]

My question is using a guid(data type unique identifier) versus using identity specification(data type int). Are there any articles where it talks about why you would use one versus another? I'm at a ...
4
votes
1answer
388 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. ...
1
vote
2answers
45 views

What can cause what appears to be missing data in a single table [duplicate]

I have a situation where it appears as if I've lost data in a single table. I have a .NET Core API, with a logging middleware. This middleware takes the entire request and response and stores in the ...
4
votes
1answer
660 views

Can SET IDENTITY_INSERT be allowed with less privileges than db_ddladmin?

I've inherited a system where the application is running under sysadmin account. I limited this account to db_datareader + db_datawriter + EXECUTE on all database and set extended events session to ...
0
votes
1answer
153 views

Pentaho Data Integration - Kettle- Update Identity Column in Microsoft SQL Server

For migration purposes I need to migrate existing ID's from my old database to my new table in MS SQL Server. I can truncate my table in MS SQL Server and then adjust my database connection in ...
0
votes
0answers
31 views

Modelling a database structure for multiple user types with multiple entry points

I am working on a project and my task is to design a database. I tried to read as much as possible, but I still have my doubts how to solve it using a optimal solution. I'm designing a database that ...
0
votes
1answer
46 views

Can I fix this insertion anomaly without using a composite key?

Suppose I have a schema with the following basic elements: Client : id (PK) Pet : id (PK) owner_id (FK to Client) Contract : id (PK) client_id (FK to Client) PetInContract : ...
8
votes
1answer
777 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 ...
2
votes
3answers
959 views

Is “SET IDENTITY_INSERT” required to insert arbitrary GUID into [uniqueidentifier] column?

SQL Server 2016 and 2017, Standard Edition I need to insert raw data (from another table) into a table that has two uniqueidentifier columns. Does this require that I use SET IDENTITY_INSERT Carrier ...
1
vote
1answer
780 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 ...
18
votes
2answers
14k 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] ...
2
votes
1answer
925 views

Why am I receiving the identity insert error even after explicitly setting IDENTITY_INSERT ON and using a column list? [closed]

My code: SET IDENTITY_INSERT SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn ON INSERT INTO SomeCoolDatabase.BoringOldSchema.TableWithIdentityColumn SELECT ColumnWithIdentity, ...
0
votes
0answers
17 views

sp_help does not reflect reseed on empty table, is that a bug?

Say I create a create a table with an identity, and before inserting any records, perform a reseed. create table dbo.mytest(x bigint identity(1,1), y char(1)) DBCC CHECKIDENT ( 'mytest', RESEED, ...
0
votes
0answers
301 views

Transactional Replication : Issues with Identity Column in Subscriber

I have an Transactional replication set up form Server A to Server B. I have a table which has an Identity column as a primary key. In my publisher ,for the table tb1,the the identity column,Id1 was ...
7
votes
5answers
13k views

Identity column as clustered index 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 ...
1
vote
1answer
1k views

Insert into table with column same values as auto increment column ( computed column )

Say I have a table with "column1" (Primary Key, Auto Increment) and "column2". How can insert into the table with column2 will have the same values as column1 which is auto increment column?
17
votes
3answers
6k 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) ...
2
votes
1answer
421 views

Function SCOPE_IDENTITY not found

When I call SCOPE_IDENTITY in Sybase, I get [Error] Script lines: 1-6 -------------------------- Function 'SCOPE_IDENTITY' not found. If this is a SQLJ function or SQL function, use sp_help to ...
0
votes
1answer
148 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
53 views

SSMS: Restart the values on a IDENTITY field [duplicate]

I am starting to use SSMS to manage a database and I am having a problem I would appreciate some help with. I have a table that was created with CREATE TABLE [dbo].[Machine]( [Machine_id] [...
2
votes
1answer
313 views

How do you retrieve the identity value of a row inserted from the OUTPUT of an UPDATE statement?

How do you retrieve the identity value for an inserted row when that row is inserted from the OUTPUT of an UPDATE statement? Neither @@IDENTITY nor SCOPE_IDENTITY() appears to be set properly. ...
2
votes
2answers
350 views

Stored Procedure - Reseed Identity

I have a bunch of LU tables that have values added and deleted all the time. Therefore, I figured writing a quick stored procedure would serve me well and then can call the stored procedure on another ...
14
votes
4answers
65k 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'...
0
votes
0answers
22 views

Converting SalesForce IDs for Performance Reasons

I'm writing code for a data warehouse that extracts information from SalesForce. The format of the SalesForce IDs is like so: 001n000000UELLJAA5 Where each digit is a base-62 value denoted by: a-z ...
11
votes
8answers
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
votes
0answers
39 views

Windowed IDENTITY column definition? [duplicate]

Is it possible to create a windowed identity column that automatically initializes and increments in SQL Server 2017 or later? I would like the conceptual functionality of SelectedSubsetId below. ...
6
votes
2answers
937 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 ...
4
votes
1answer
873 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 ...
11
votes
4answers
1k 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. ...
2
votes
1answer
50 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?
1
vote
0answers
67 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 ...
0
votes
1answer
114 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_ ...
16
votes
3answers
80k 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 ...
0
votes
1answer
606 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 [...
1
vote
1answer
239 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 ...
2
votes
1answer
28 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 ...
4
votes
2answers
5k 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. ...