identity-column

Can a sql server table have two identity columns?

I need to have one column as the primary key and another to auto increment an order number field. Is this possible? EDIT: I think I'll just use a composite number as the order number. Thanks anyways. ...

Entity Framework Identity column not working

I am fairly new to the Entity Framework and am in the process of building my first MVC application. I'm implementing a Create View for a simple Entity however, I am having problems with the (SQL Express 2005) Identity column primary key. As I understand it the Framework should handle the Identity column and let the SQL database generat...

Does the entity framework preserve ordering when it does inserts into the database?

We plan on using identity columns in our sql server database. Currently we are using guids to generate unique ids, but it turns out that the ordering is relevant so we consider switching to identity columsn. Since ordering is relevant we want to make sure that the order in which we add objects to the entity context is also the order in...

Sql Server 2005 Primary Key violation on an Identity column

Hi everyone, I’m running into an odd problem, and I need some help trying to figure it out. I have a database which has an ID column (defined as int not null, Identity, starts at 1, increments by 1) in addition to all the application data columns. The primary key for the table is the ID column, no other components. There is no set of d...

Adding an identity to an existing column [SQL Server]

I need to change the primary key of a table to an identity column, and there's already a number of rows in table. I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database. What's the SQL command to alter the column to have an identity property? ...

When Would You Choose NOT to Use an Identity/Autonumber Field as Your Primary Key?

I don't believe in a Silver Bullet, but I really like to use sequences or autonumber identity columns as my primary key columns for database tables. They're unique, they index well, and I don't have to worry about null values. On the other hand, in some cases, they seem redundant when there are other unique columns in the table that co...

Identity Column Out of Sync

I have a table with an Identity column as Primary Key. All is well until one several days ago, application that is using this table start complaining about PK violation. At first I thought this was impossible, until I remember about DBCC CHECKIDENT. The magic function told me that the 'current column value' is higher than 'current ident...

LINQ Inserts without IDENTITY column

Hello I'm using LINQ, but my database tables do not have an IDENTITY column (although they are using a surrogate Primary Key ID column) Can this work? To get the identity values for a table, there is a stored procedure called GetIDValueForOrangeTable(), which looks at a SystemValues table and increments the ID therein. Is there any ...

Usage of Table with single column (Identity)?

Hi, Whats the purpose of having single column table (identity column)..? is there a good usecase available?? is it really a good practise.?? Cheers Ramesh Vel ...

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? ...

Fabricate entities with multiple IDENTITY PKs?

Disclosure: I'm a 'natural key' advocate myself and averse to the IDENTITY PK approach. But I do have a 'live and let live' approach to lifestyle choices, so no religious arguments here please :) I have inherited a table where the only key is the IDENTITY PK column; let's call it ID. There are a many tables that reference ID. The intend...

Linq to Sql: Can I return the Identity_Scope after an insert?

After I do an insert using linq to sql, can I get the Identity_scope value back if my table has an identity column? ...

Is a guid as identity field better in domain-driven design?

Is it easier to implement domain-driven design when using guids as identity fields instead of auto incrementing integers? With guids you don't have to jump to the database to get the actual value. ...

Identity Column as Primary Key

Hi Guys, 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 ...

Identity SQL Server Problem

I have use Identity on ID primary key. And then I insert some data. For example. Data 1 -> Add Successful without error. ID 1 Data 2 -> Add Successful without error. ID 2 Data 3 -> Add Fail with error. Data 4 -> Add Fail with error. Data 5 -> Add Successful without error. ID 5 You can see that ID has jump from 2 t...

How to retrieve last autoincremented value in MS-Access like @@Identity in Sql Server

I want to retrieve identity column value in ms access from an autoincremented column. Basically i m running a transaction in which i have to write two insert queries. 2nd query will be containing autoincremented value that was generated from query 1. how can i do that ? ...

Table with identity field: Best SQL query to get Ids of deleted records?

I have a table with an identity field. What the best SQL query to get the Ids of all the deleted records from this table? ...

Changing Identity Seed in SQL Server (Permanently!)

Is there any way of changing the identity seed for an identity column permanently? Using DBCC CHECKIDENT just seems to set the last_value. If the table is truncated all values are reset. dbcc checkident ('__Test_SeedIdent', reseed, 1000) select name, seed_value, increment_value, last_value from sys.identity_columns where [object_id] = ...

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT [sql server 2005]

Which should i use to get last inserted record id in sql server 2005? I searched stackoverflow and i found this, http://stackoverflow.com/questions/45651/sql-how-to-get-the-id-of-values-i-just-inserted Comment of the best answer: there are known bugs with SCOPE_IDENTITY() in sql server 2005, not sure about 2008, the OUTPUT clause can...

Can I make an identity field span multiple tables in SQL Server?

Can I have an "identity" (unique, non-repeating) column span multiple tables? For example, let's say I have two tables: Books and Authors. Authors AuthorID AuthorName Books BookID BookTitle The BookID column and the AuthorID column are identity columns. I want the identity part to span both columns. So, if there is an AuthorI...