Questions tagged [identity]
Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
182
questions
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 ...
- 15
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 ...
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....
- 1,341
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 "...
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 ...
- 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 ...
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 ...
- 922
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:
=> ...
- 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 ...
- 71
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, ...
- 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 ...
- 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 ...
- 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'...
- 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,...
- 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 ...
- 37
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 ...
- 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 ...
- 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 ...
- 31
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 ...
- 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)....
- 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 ...
- 153
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.
...
- 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 ...
- 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 ...
- 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 ...
- 17
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 : ...
- 101
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 ...
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 -...
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 ...
- 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?
- 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] [...
- 123
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.
...
- 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 ...
- 53
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, ...
- 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 ...
- 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 ...
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.
...
- 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 ...
- 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 ...
- 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
...
- 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?
- 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.
...
- 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 ...
- 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_ ...
- 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 [...
- 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 ...
- 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 ...
- 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 ...
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 ...
- 451
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 ...
- 133