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
1 vote
1 answer
33 views

Can one dictate the order of generated values when adding a new BIGSERIAL column to a table?

I need to add a new BIGSERIAL column to a huge table (~3 billion records). This question is similar to what I need to do and the accepted answer has helped me somewhat. But I'm still wondering about ...
user avatar
1 vote
0 answers
46 views

Postgres: with identity column as unique key, need help in upsert

Follow the below script to create two tables source_t and dest_t. drop table if exists source_t; create table source_t as (select 1 id, 'dummy1' varcol union select 3,'dummy3'); drop table if exists ...
user avatar
0 votes
1 answer
85 views

XE_SERVICES_RWLOCK in top waits

We start facing some uncommon waits after the last release. XE_SERVICES_RWLOCK waits starts to appear in our top waits. Investigation shows that this waits happen during insert to our main OLTP tables....
user avatar
2 votes
2 answers
129 views

Primary keys due to inheritance

I am trying to design a database for postgrad office and we have a parent class "User" that has a username as a primary key for authentication and we have corresponding subclasses "...
user avatar
0 votes
0 answers
42 views

MSSQL 2016: How do I Populate an Existing 2+ Billion Row Table with a Unique Primary key?

Is there a way to leverage the identity feature to backfill a primary key for an existing very large table? What I have done in the past is script a new table from the old table adding a new ...
user avatar
  • 137
0 votes
1 answer
59 views

Determine Available Blocks of Contiguous Ids

We have a SQL Server table that has an int autoincrement primary key column on it. The table's primary key space is fragmented. For example, ids 1 through 10 are in use but then ids 11 through 100,000 ...
user avatar
0 votes
3 answers
99 views

Identify all tables with an identity column

Is there a query I can run that will show me all tables with an identity column? Trying to figure out if there are any identity based tables whose current identity is nearing upper bound of int32 or ...
user avatar
0 votes
1 answer
146 views

Why can't PostgreSQL identity columns be used in CREATE TYPE?

My impression is that each PostgreSQL table has a corresponding composite type associated with it. But it seems that the PostgreSQL identity column cannot be used in a CREATE TYPE statement: => ...
user avatar
  • 2,797
0 votes
1 answer
52 views

Must tuples of a relation and their referents be in a one-to-one relationship?

Figures 3.a and 3.b, section 1.4, page 381 of Edgar Codd’s 1970 landmark paper ‘A Relational Model of Data for Large Shared Data Banks’ presents a set of relation schemas (unnormalized in figure 3.a ...
user avatar
4 votes
1 answer
838 views

Restarting identity columns in Postgresql

For serial columns used in Postgresql < 10, we manage the sequence by its name. We were able to reset a sequence with: SELECT setval('table_id_seq', (SELECT MAX(id) FROM table)); From version 10, ...
user avatar
  • 265
3 votes
3 answers
685 views

Copy tables to another SQL Server keeping the identity property

Objective I want to make a exact copy of 10 tables from a database on a SQL Server into my local SQL Server. The local copy is used for testing. Problem The copied tables all have the identity ...
user avatar
  • 133
0 votes
1 answer
197 views

Is IDENTITY continuous in new versions of PostgreSQL? [duplicate]

Now I am use serial to generate table primary key in PostgreSQL 13, but today I found the ID jumps and has big gaps in the ID space. It will waste many IDs. I read the docs and found that PostgreSQL ...
user avatar
  • 477
1 vote
5 answers
209 views

Why can't you add an Identity to an existing colum in SQL Server?

What is so special about an Identity column? I am pretty sure there is a valid reason, but I'm trying to get a better understanding of SQL. So what is so special about an Identity column that this can'...
user avatar
  • 333
8 votes
2 answers
1k views

As there is no unsigned int in SQL Server doesn't an Identity Seed of -2,147,483,648 make more sense for large tables?

I just had a showerthought that the default Identity Seed is 1. I have some tables that I know will grow to the billions at a certain point. Wouldn't it make more sense to start on int.Min ( -2,147,...
user avatar
  • 333
1 vote
1 answer
63 views

What is MS Access ODBC SQL syntax for a table with AutoIncrement and Memo columns?

I am migrating a full framework .NET app to core. It used ADOX to create an MS Access database and tables. It used OLE DB to populate data. In ADOX, the data type was ADOX.DataTypeEnum.adInteger and ...
user avatar
0 votes
3 answers
31 views

Load data from a Table that has the same structure as Table1 with the only diferrence of an ID not null column?

So, I know that if you want to load data into a table that does not have the same structure as the data, well you have to do it in the way of column by column, the thing that in this situation, I have ...
user avatar
  • 1
2 votes
1 answer
468 views

MASSIVE Identity column value jump in SQL Server 2014

So in the middle of basic coding and testing we saw a huge non-patterned jump in Identity values for multiple tables. We are unaware of any server blips or attempted bulk operations, but DBAs are ...
user avatar
  • 123
2 votes
0 answers
76 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 ...
user avatar
1 vote
1 answer
1k 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 ...
user avatar
  • 285
0 votes
2 answers
241 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)....
user avatar
  • 24.2k
3 votes
2 answers
2k 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 ...
user avatar
6 votes
1 answer
2k 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. ...
user avatar
  • 21.1k
1 vote
2 answers
343 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 ...
user avatar
  • 11
4 votes
2 answers
3k 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 ...
user avatar
  • 10.3k
0 votes
1 answer
417 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 ...
user avatar
0 votes
1 answer
59 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 : ...
user avatar
9 votes
1 answer
1k 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 ...
user avatar
0 votes
2 answers
340 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 -...
user avatar
0 votes
0 answers
900 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 ...
user avatar
  • 1,285
1 vote
1 answer
3k 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?
user avatar
  • 13
0 votes
1 answer
63 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] [...
user avatar
2 votes
1 answer
2k 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. ...
user avatar
  • 1,791
2 votes
2 answers
920 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 ...
user avatar
2 votes
1 answer
2k 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, ...
user avatar
  • 24.2k
0 votes
0 answers
27 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 ...
user avatar
  • 121
2 votes
3 answers
1k 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 ...
user avatar
2 votes
0 answers
44 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. ...
user avatar
  • 683
6 votes
2 answers
2k 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 ...
user avatar
  • 525
2 votes
1 answer
973 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 ...
user avatar
  • 170
5 votes
1 answer
3k 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 ...
user avatar
  • 153
2 votes
1 answer
63 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?
user avatar
  • 21
12 votes
4 answers
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. ...
user avatar
  • 223
1 vote
0 answers
100 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 ...
user avatar
  • 19
0 votes
1 answer
318 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_ ...
user avatar
  • 11
0 votes
1 answer
891 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 [...
user avatar
  • 794
2 votes
1 answer
31 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 ...
user avatar
  • 155
0 votes
1 answer
899 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 ...
user avatar
  • 1,035
2 votes
1 answer
45 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 ...
user avatar
3 votes
1 answer
335 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 ...
user avatar
2 votes
1 answer
1k 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 ...
user avatar