The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
1answer
24 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 ...
1
vote
2answers
49 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
209 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
1k 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 ...
1
vote
0answers
81 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
17k 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
188 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
113 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
103 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
82 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
261 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 ...
3
votes
1answer
99 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
3k 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
191 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
2k 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 ...
1
vote
1answer
130 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
750 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
2answers
96 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
99 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
851 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
192 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
170 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
713 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 ...
2
votes
3answers
4k 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
546 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
3k 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
198 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
938 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?
39
votes
10answers
4k 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
283 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
749 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
120 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
189 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
101 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 ...
27
votes
7answers
449 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
2k 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
412 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
710 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
720 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 ...