Stack Overflow
Stack Overflow

Questions tagged [identity-column]

An Identity column is a column in a database table that is made up of values managed by the server and cannot be modified, to be used as a primary key for the table. It is usually an auto-incrementing number.

Several Database products provide means to guarantee unique sequences of numeric values. Manually coding values for a primary key can cause problems.

Common database support: - Oracle: SEQUENCE (stand-alone number generator) and IDENTITY column clause that uses a system-generated SEQUENCE - mySQL: AUTO_INCREMENT column clause - Microsoft SQL Server, IBM DB2: SEQUENCE (stand-alone number generator)

See also for questions not related specifically to a primary key.

368 questions
2
votes
2 answers

IDENTITY_INSERT ON not working - SQL Server 2008 R2

I am having problems with my query. Basically, what I am trying to do is empty out a table and copy the records from the same table in another database. I did use the SET IDENTITY_INSERT code to make sure that the identity column is turned off…
Smiley
  • 3,207
  • 13
  • 49
  • 66
2
votes
2 answers

How to write an INSTEAD OF INSERT trigger on a multi table view that works with identities?

I have two tables that have a 1:1 relationship and use IDENTITY columns for their primary keys. A view is joining both tables together to give the impression that all columns are really stored in one table. For viewing purposes this is fine, but the…
Jens Mühlenhoff
  • 14,565
  • 6
  • 56
  • 113
2
votes
2 answers

Get Identity Field out of KeyMembers

I would like to get the KeyMembers where I have set in the Edmx the StoreGeneratedPattern to Identity is there a way to do this? I can get the KeyMembers with this code: private static IEnumerable GetKeyMembers(string entityName) { …
Rand Random
  • 7,300
  • 10
  • 40
  • 88
2
votes
1 answer

Best way to obtain new uniqueidentifier from an SQL insert statement while still determining success or failure of insertion?

I'm starting to work with uniqueidentifiers, and I'm encountering an unexpected issue. First of all, where I'd typically use SCOPE_IDENTITY(), this is no longer possible with a uniqueidentifier, even though in concept it still involves an…
Triynko
  • 18,766
  • 21
  • 107
  • 173
2
votes
1 answer

SQL Guid to Identity Primary Key, Batch Query

I have a small process that works on a few SQL tables. The tables were originally Guid primary keyed, but for efficiency we're updating them to a BigInt identity. I have a batch insert that adds an item in the primary key table, then several items…
Jonesopolis
  • 25,034
  • 12
  • 68
  • 112
2
votes
3 answers

Identity Column as Primary Key

Could you please opine if having identity column as primary key is a good practise? For ORM tools, having identity column on tables helps. But there are other side effects such as accidental duplicate insertion. Thanks Nayn
Nayn
  • 3,594
  • 8
  • 38
  • 48
2
votes
2 answers

GUID or ID for tables in SPA

Been discussing the merits of GUIDS versus IDENTITY fields as primary keys with my colleagues this afternoon. Coming from a big data background, I instinctively went with IDENTITY but they, who are more web-oriented, preferred GUIDS. Most of the…
Bob Tway
  • 9,301
  • 17
  • 80
  • 162
2
votes
2 answers

SQL CE Compact 3.5 Identity columns for a table

Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column in SQL CE version 3.5?
rxm0203
  • 127
  • 1
  • 9
2
votes
3 answers

Is using a (sequential) GUID the only viable alternative to a database generated ID?

We are migrating our MS-Access database to SQL Server Compact 4.0 using the Entity Framework 5 Code First approach. We found that using Database generated Integer ID's is very slow and to make it worse, the delay increases exponentially with the…
Dabblernl
  • 15,831
  • 18
  • 96
  • 148
2
votes
3 answers

More than one identity column in a SQL Server table

I have the following problem to resolve and I think I need some help as I don't think what I'm thinking as a possible solution is correct: I have a SQL Server table with an identity column. This table is going to store current data as well as…
Cesar Vinas
  • 343
  • 1
  • 10
  • 20
2
votes
3 answers

return Identity in SQLServer Compact

How do you get the identity column value after an insert in SQL Server Compact 3.5?
Kumar
  • 10,997
  • 13
  • 84
  • 134
2
votes
2 answers

How do I set Identity seed on an ID column using Entity Framework 4 code first with SQL Compact 4?

I am having some problem setting the Identity Seed on the Id column in SQL Compact 4 using the code first approach. I have tried this context.Database.ExecuteSqlCommand("DBCC CHECKIDENT ('Members', RESEED, 100001"); but this is not working in Sql…
n3tx
  • 429
  • 2
  • 10
  • 23
2
votes
0 answers

EF 4.1 is trying to insert values in identity column despite StoreGeneratedPattern being set to Identity in SSDL

I have a table called ScanMasters in SQL Server 2008 which has a column called PresentationAuditId as the primary key. This column is also a foreign key. The table also has an identity column called ScanPartId. I am using Entity Framework 4.1 to…
Yasir
  • 1,595
  • 5
  • 23
  • 42
2
votes
2 answers

Strange @@IDENTITY scope behaviour?

Not a full bottle on this, but I had the impression @@IDENTITY gives the value of the last identity created in the database no matter where. SCOPE_IDENTITY() gives the value within the scope of the current statement. I have a table with an identity…
ingredient_15939
  • 3,022
  • 7
  • 35
  • 55
2
votes
3 answers

Teradata: How to back up a table that uses an identity column?

In Teradata, the way I've been doing backups for tables is like this: create table xxx_bak as xxx with data Works great, but I have just discovered that this doesn't work for tables with identity columns. I need a backup method that can duplicate a…
oscilatingcretin
  • 10,457
  • 39
  • 119
  • 206
1 2 3
…
24 25