The tag has no usage guidance.

learn more… | top users | synonyms

3
votes
1answer
193 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 ...
0
votes
0answers
46 views

Can I simulate a sequence in pre-2012 SQL Server, with separate identity column, in function style?

A database on SQL Server 2005/2008/R2 is currently using an identity column in a separate table as primary key source. The reason is that the application, with NHibernate as ORM, needs to know the PK ...
0
votes
0answers
46 views

How to reset @@IDENTITY value inside a Sybase SP

Is there any way to reset the @@IDENTITY value inside a Sybase SP? I'm writing a SP which involves a transaction, so everytime I do an insert, to check data was correctly inserted, I assign ...
0
votes
1answer
119 views

Using Identity Insert on a linked server

We have a database hosted in Microsoft Azure and using this database connection details I have created a linked server object in SSMS on our on-premise SQL box and I can insert into it as following ...
0
votes
1answer
59 views

Set Identity as (11 - 2) in sql table

I have a issue, I want to set the ID column, where the ID should start from 11-2 then it should auto- increment by 1 like 11-3, 11-4. I am using sql-server 2008 R2. Please help
0
votes
2answers
145 views

Identy Column To Generate Flag Values

Sometimes there is a need to have the rows of a table have a flag type value which can be and-ed and or-ed by the end user. This of course can be accomplished by creating a new column or putting in ...
0
votes
0answers
50 views

redshift performance effect if use 32 character long unique string as Primary key instead of auto incremented int

In my analytics application I am creating 32 char long unique string for each hit as user_session. In current implementation, I am storing user_session in tbl_sesssion in session column and I am ...
1
vote
2answers
429 views

Assigning new auto increment value [duplicate]

I have a table that is created as below. CREATE TABLE [dbo].[Table1]( [ID] [INT] IDENTITY(1,1) NOT NULL, [Title] [VARCHAR](255) NULL, ... Lets say that the latest ID is 100 ( I have 100 ...
1
vote
1answer
866 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 ...
3
votes
1answer
1k 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 ...
0
votes
1answer
32 views

Composite Key for Gizmo Tracking

I'm trying to setup a many to many relationship between an gizmo table and a owner table. A owner can have many gizmos And a gizmo can have many owners When I have a linking table do I set the ...
3
votes
4answers
408 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 ...
3
votes
2answers
860 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 ...
2
votes
1answer
4k 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
135 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 ...
0
votes
3answers
34k views

SQL Server - Copy data from one database to another - identical tables, new identity values needed

Here's the thing, I have been running identical copies of my website for two different geographical regions. The site now supports multi-currency pricing so I want to merge both sites together. All ...
4
votes
2answers
212 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 ...
3
votes
1answer
697 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 ...
1
vote
1answer
171 views

How do Identity Columns get Replicated?

During Transactional Replication, we have data going from Server A to Server B. Say we were to reseed an Identity Column on a Table on Server A (for whatever reason), how would this affect the data ...
2
votes
2answers
88 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 ...
0
votes
2answers
403 views

What locking can I expect with DBCC CHECKIDENT(RESEED)

I'm working on a project that has hit a bit of a snag: I'm replacing some replication with a home built system so that if replication goes down we don't blow our SLAs with our customers and shell out ...
4
votes
1answer
187 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: ...
5
votes
2answers
4k 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 ...
1
vote
0answers
331 views

SET IDENTITY_INSERT equivalent in Access

In sql server i can explicitly insert value to a identity column of a table by using SET IDENTITY_INSERT ON . For an Access database i am using Autonumber column datatype. For this column i am not ...
1
vote
2answers
4k 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 ...
2
votes
1answer
271 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
2answers
1k 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 ...
3
votes
2answers
97 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 ...
5
votes
1answer
102 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 ...
0
votes
3answers
1k 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
256 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 ...
1
vote
1answer
178 views

When to use identity increment?

Many times I face these three choices to work with IDs: Using an auto-increment identity column? Suggesting a new codification to use (eg, for students: first 4 position is the year when the student ...
6
votes
1answer
955 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 ...
3
votes
3answers
6k 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 ...
0
votes
2answers
760 views

Is there a SQL Server equivalent to “OVERRIDING USER VALUE”

While googling I found about the OVERRIDING USER VALUE parameter for an INSERT for IBM's iSeries. Is there an equivalent command for Microsoft SQL Server versions 2005 or newer to allow you to ...
4
votes
5answers
4k 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 ...
1
vote
1answer
208 views

Reset all IDs in a Large Table

Consider a table that contains around 9,000,000 rows. Each row has a int-based "ID" IDENT column whose value automatically increments on each insert. The table undergoes a lot of "churn," that is to ...
0
votes
1answer
1k views

Find ID columns with null value in oracle database

I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database Any suggestion?
45
votes
10answers
6k 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. ...
2
votes
1answer
463 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 ...
1
vote
1answer
899 views

Copy SQL table to other server identity becomes int

I just built an Umbraco website. This uses a not-that-small SQL Server db. I copied this DB using the export data function built in in SQL Server 2008. This copies all the tables and their content ...
1
vote
1answer
143 views

Why is replication attempting to distribute NULL values into Identity columns

We are sporadically getting this error: Explicit value must be specified for identity column in our replication environment. I have checked the transaction command that is reporting the error and it ...
2
votes
2answers
2k 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
234 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 ...
0
votes
1answer
104 views

What is the rationale for requiring a column list when inserting into a table with identity column?

When inserting into a table with an identity column from some similar staging table, we have to set IDENTITY_INSERT to ON and specify a column list. Why do we have to specify a column list in this ...
28
votes
7answers
733 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 ...
5
votes
3answers
3k 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 ...
3
votes
3answers
460 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 ...
6
votes
1answer
769 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 ...
1
vote
2answers
862 views

Can I implement a 'gapless' identity column in Oracle?

In SQL Server it is so easy to write: create table #tmp ( id integer identity(1,1) primary key, message varchar(256) ); When I try to migrate this to Oracle, I end up with: CREATE ...