The tag has no usage guidance.

learn more… | top users | synonyms (1)

54
votes
10answers
9k views

Why do people recommend not using the name “Id” for an identity column?

I was taught not to use the name Id for the identity column of my tables, but lately I've just been using it anyways because it's simple, short, and very descriptive about what the data actually is. ...
28
votes
7answers
1k views

What are the minimum matching criteria recommended for reliable demographic based patient matching?

When matching patients based on demographic data are there any recommendations on what fields should match for the patient to be the "Same Patient"? I know the algorithms will be different for ...
18
votes
6answers
429 views

Can I rely on reading SQL Server Identity values in order?

TL;DR: The question below boils down to: When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the ...
11
votes
1answer
190 views

Where does SQL Server physically store the IDENTITY VALUE for a table?

I'm hoping someone can point me in the right direction on this one. Here's my workings out so far. SELECT * FROM sys.identity_columns is a system view which gives "last_value" but the definition for ...
9
votes
1answer
629 views

Why is removing the Identity property on a column not supported

I have read that after SQL Server 2000, the ability to "un-identity" an identity column was removed. And that this was "By Design" (not just a missing feature). Here is an example I found on a blog. ...
8
votes
2answers
433 views

Create a “INTO” table with primary key

maybe for this community my problem is easy, but for me (a simple java programmer) it's a BIG problem. I have a Big DB with more and more data. So, the external db admin had create a job that show me ...
8
votes
2answers
5k views

Composite Primary Key efficiency as a Foreign Key

I have a table with a Composite Primary key (consisting of 4 columns) which is used to ensure no duplicates are entered into the table. I am now in need of a new table which will need to reference the ...
7
votes
1answer
241 views

Is there a way to see how SQL Server 2012 defines new identities?

On Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64), is there a way to see what mechanism SQL Server uses to calculate the new identity values for tables created by SELECT INTO? SAMPLE DATA -- ...
6
votes
3answers
1k views

What could cause the wrong ID to be inserted?

I have a SQL Server 2008 server (build 10.0.5500). Earlier this week I ran this on a table that already had data in it: delete from dbo.table go dbcc checkident('dbo.table',reseed,0) When the ...
6
votes
1answer
651 views

Why can a SQL Server table not have more than one IDENTITY column?

Why can a SQL Server table not have more than one identity column? CREATE TABLE t(id INT IDENTITY, id2 INT IDENTITY) Msg 2744, Level 16, State 2, Line 5 Multiple identity columns specified for ...
6
votes
1answer
848 views

Guarantee SQL Server Identity Columns that two consecutive values differ by constant increments?

I posed a question about Denali sequences here and another question about emulation of identity columns in Oracle Global Tables here. I always thought that I could trust that identity values ...
6
votes
1answer
1k views

Getting Identity values to use as FK in an INSTEAD OF trigger

I have a series of updateable views we are exposing to end users as the interface for a back end process. One of these views references two tables and requires an INSTEAD OF trigger for UPDATE and ...
5
votes
3answers
5k views

Why is SSMS inserting new rows at the top of a table not the bottom?

Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using ...
5
votes
3answers
775 views

Add autoincrement to existing PK

I created a table in a DB that already exists in another DB. It was initially populated with the old DB data. The table's PK had to receive the values that already exist on those records, so it couldn'...
5
votes
2answers
432 views

Reseed identity column in all empty tables

I need to write a query that resets the identity on all tables that have an identity column and are empty.
5
votes
1answer
602 views

Adding PRIMARY KEY and IDENTITY attributes to SQL Server 2008 tables

An application is using an SQL Server 2008 database that has ID columns, but these columns are not primary keys and have no IDENTITY attribute. What I need to do now is adding the PKs and IDENTITY ...
5
votes
1answer
104 views

How is SQL Server host identity ensured?

When I connect to some e-commerce website with my browser I use HTTPS that uses a certificate that (more or less) guarantees that example.com is indeed example.com. Now I connect to a SQL Server ...
5
votes
4answers
691 views

Primary Key choice on table with unique identifier

I'm reconsidering my choice of Primary Key on a table, and would like some input. Some context: We are building a REST API, where clients access "Assets" identified by a GUID. So, the table for ...
4
votes
6answers
5k views

using IDENTITY column with increment -1

I need to run a sql server logging db with 2 main tables in 2 seperate datacentres writing to both at the same time. I had the idea of restoring the db to the new datacentre and then reseeding the ...
4
votes
3answers
180 views

Are auto incrementing identities always given in the order of inserts in SQL Server?

If I create a simple table like this: CREATE TABLE SimpleTable ( [Id] INT IDENTITY(1,1) NOT NULL, [Value] VARCHAR(10) NOT NULL, CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED ( ...
4
votes
1answer
782 views

Scope_Identity() returns null after inserting first row in table

I have a stored procedure that is used for inserting values in two tables. The tables have a parent-child relationship i.e. the first table has an identity column, and the second table references the ...
4
votes
2answers
3k views

How to remove identity specification for multiple tables

I am having total 109 table in Database and now i am migrating to Amazon server with identity insert and now i want to remove auto increment column specification for all tables. How set auto increment ...
4
votes
3answers
4k views

Identity column as clustured index bad idea?

Its just been a few months of programming in SQL Server for me so my knowledge is not good in many regards. In an already existing project at work I came across many tables with large composite ...
4
votes
2answers
226 views

removal of GUIDS used for all PK's, FKs & Clustered indexes

Inherited control over a database that has this nasty config. It also has large sections of code generated by NHibernate, including the gneeration of GUIDs before they get to the db so no chance of ...
4
votes
1answer
110 views

SQL 2012 insert data into a table from another table and custom columns

I'm trying to insert data from one table into another table and I also have 4 columns that are not in the source table but need to be populated in the target table. Here is the Insert statement SET ...
4
votes
1answer
3k views

How to set identity_insert to on in SQL Server

I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created ...
4
votes
1answer
244 views

How to check with SQL whether a column is an identity column for an Access database?

Knowing how to check whether a column is an identity column for Microsoft SQL Server, I currently still found no solution for doing the same with SQL for a Microsoft Access database. My question: ...
3
votes
3answers
7k views

Inserting rows into other table whilst preserving IDENTITY

I'm performing ETL logic on a SQL Server table. I'm going to be synchronizing data from one table to another. For all of the records that need to be added to the target table based on the source ...
3
votes
2answers
101 views

Identity proceeds as normal, but with a leading 10…?

So I noticed a very strange thing when tinkering with my database. I had just inserted a row which was ID 47, then after inserting some more, I realised that somehow my Identity had skipped all the ...
3
votes
3answers
499 views

Database Schema with a shared identity Field

I'm a developer treading into the database world so bear with me please. I'm creating a Contract database with separate tables for various types of contracts. I have 5 tables but would like to ...
3
votes
2answers
1k views

How to update ID=null values in table to incremental counter values?

On SQL Server 2012, i've got an intermediate/staging table for merging existing with new data, where I want to insert numeric IDs for newly created rows: ID NaturalID Comment 1 ...
3
votes
1answer
3k views

Get identity value of all rows inserted

I can use Scope_Identity() to get the last identity value inserted into an identity column. But how can i get identity values of all rows which are inserted in a insert statement? Example insert ...
3
votes
1answer
225 views

Gigantic gaps in IDENTITY column

This question is about strange behavior in SQL Server 2008 R2 SP3 Standard Edition x64. I have read many questions about IDENTITY increments in SQL Server and strange gaps between them. But basically ...
3
votes
1answer
53 views

Identity management in Transaction replication with updatable subscription

I have migrated my publisher DB to new server yesterday. For migration what I did was removed subscriptions and publication completely backed up DB on which publication was configured restored DB ...
2
votes
2answers
93 views

Is it worth it to create an “Id” column?

I have a table which has a column Name, which is of type VARCHAR2(20), unique, cannot be null, and cannot be changed. At first I used it as a Primary Key for this table but I was wondering if it was ...
2
votes
1answer
85 views

SET IDENTITY_INSERT OFF - Caused Table Lock

I Executed SQL on database that looked like this: SET IDENTITY_INSERT table1 ON GO BEGIN TRANSACTION GO INSERT INTO table1 SELECT ... FROM table2 GO DELETE FROM table2 ... GO COMMIT TRANSACTION GO ...
2
votes
2answers
3k views

How To Restore A Replicated Database

I am replicating our live database using transaction replication. This is basically to have a backup copy of our database in the case of server failure. The replicated database is read only, so I ...
2
votes
1answer
398 views

Having 2 identities in a table

I need to have some how 2 identity columns in one table. One that is normal identity and another one that will reset yearly. I have done the reset part with the help of DBCC CHECKIDENT ('TestTable', ...
2
votes
1answer
3k views

Identity value jumps when restarting SQL Server

We just switched from SQL Server 2008 R2 to SQL Server 2012. I am facing a problem with identity columns: Whenever I restart SQL Server, the seed value for each identity column is increased by 1000 (...
2
votes
1answer
412 views

Polling for new records in a table

We have a table that is append+read only. We'd like to have multiple applications consume all new rows that show up in that database, in a cheap and easy way (without change tracking). The simple ...
2
votes
1answer
832 views

IDENTITY_INSERT seems stuck

I might be forgetting something, but essentially, I'm struggling with IDENTITY_INSERT. I'm trying to move data between tables, both of which have an identity column (similar schema). SET ...
2
votes
2answers
62 views

Finding and listing all columns in a SQL Server database with identity [duplicate]

I wanted to RESEED all the identity columns in that database, so I need to create a script so I can find out which column in which table has the identity property.
2
votes
1answer
57 views

Select a table's current identity from another database

How can I get the current identity of a table using OPENQUERY in SQL Server? How to select the current identity on the table from another database?
2
votes
2answers
2k views

How to rollback the identity seed after deadlock

Now that's an approximate sequence of operations Im performing: SET IDENTITY_INSERT <table-name> ON; INSERT SOMETHING to <table-name> with explicitly specifyed id DECLARE @oldID bigint ...
2
votes
4answers
2k views

Merge Replication identity field issues

One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of ...
2
votes
1answer
317 views

MDW performance_counter_instances table running out of identity values

My MDW database's performance_counter_instances table has run out of identity values. Has anyone ever encountered this? Is it safe to change the data type of the identity field ...
2
votes
1answer
278 views

Dropping then adding a table to a subscription with Identity option Not for Replication causes errors

We have a Transactional Replication environment that was initialised from a backup that replicates the data for several hundred tables. When a table is altered, say a new column added, I have created ...
2
votes
1answer
8k views

Using scope_identity() to get ID “during” insertion of row?

I have a column that has an ident_current constraint on it to populate another column, like so: alter table dbo.Employee add constraint dbo_Employee_D1 default ident_current('dbo.Employee') for ...
2
votes
0answers
196 views

Identity in a view treated as ordinary int - migrating from SQL Server 2000 to 2012

I'm migrating a very old platform from a SQL Server 2000 backend and MS Access 2003 as its frontend to SQL Server 2012 backend and MS Access 2010 as the frontend. This obviously means a bit of ...
1
vote
2answers
7k views

Is it recommended to use Identity as an alternative to primary key?

We can declare an Identity like id_num so that id_num will have an increment of unique numbers. CREATE TABLE new_employees ( id_num int IDENTITY(1,1), fname varchar (20), minit char(1), lname ...