Database Administrators

Questions tagged [identity]

Ask Question

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

167 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
0
votes
2answers
75 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)....
1
vote
2answers
35 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
75 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
39 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
288 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
81 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
21 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
39 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
654 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 ...
0
votes
0answers
13 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
2answers
85 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 -...
0
votes
0answers
100 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 ...
1
vote
1answer
695 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?
0
votes
1answer
52 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
132 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
214 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 ...
2
votes
1answer
583 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
20 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 ...
0
votes
0answers
64 views

After switchover OIM app to standby, getting ORA-01033: ORACLE initialization or shutdown in progress

I have the exact same issue as described here JDBC Connection on Current Primary Database After Switchover: ORA-01033 encountered We have OIM 11gr2PS3 app and 2 node rac DB cluster. When I switchover ...
2
votes
3answers
816 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 ...
2
votes
0answers
38 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
673 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 ...
2
votes
1answer
306 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 ...
4
votes
1answer
618 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 ...
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?
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. ...
1
vote
0answers
65 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
79 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_ ...
0
votes
1answer
526 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 ...
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 ...
0
votes
1answer
416 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 ...
2
votes
1answer
42 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 ...
3
votes
1answer
156 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 ...
1
vote
1answer
561 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 ...
0
votes
2answers
180 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 ...
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 ...
6
votes
1answer
691 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 ...
0
votes
1answer
284 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 ...
6
votes
1answer
4k 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 ...
2
votes
2answers
2k 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 ...
2
votes
2answers
6k 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 ...
7
votes
2answers
1k 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 ...
0
votes
1answer
7k views

How to fix all duplicate key value violates unique constraint

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 ...
2
votes
2answers
863 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 ...
2
votes
1answer
26 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
2k 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
219 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
429 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
406 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 ,...

1 2 3 4