?
Solved

Can a few rows have the same value in an identity column?

Posted on 2011-04-20
6
Medium Priority
?
264 Views
Last Modified: 2012-05-11
Please enumerate curcumstances when the result below is possible. (Yes, I know... so never mind if you feel like the request is "stupid").
select columnproperty(object_id('boundaries'),'id','isidentity')
-----------
1

select COUNT(*)from Boundaries group by id having count(*)>1
-----------
8
3
2
3
2
2
2
2
2
2
2
2

(12 row(s) affected)

Open in new window

0
Comment
Question by:midfde
6 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35433626

Nope, its unique
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 1000 total points
ID: 35433849
It's possible if the id column is defined as an identity, but not as a primary key or as an unique key.

The script below generates duplicate identity ids:

CREATE TABLE Table_2 (
	id int IDENTITY(1,1) NOT NULL,
	test nvarchar(10) NULL
) ON [PRIMARY]

SET IDENTITY_INSERT Table_2 ON
INSERT Table_2 (id, test) VALUES (1, N'test1')
INSERT Table_2 (id, test) VALUES (2, N'test2')
INSERT Table_2 (id, test) VALUES (3, N'test3')
INSERT Table_2 (id, test) VALUES (4, N'test4')
INSERT Table_2 (id, test) VALUES (1, N'test1 dup')
INSERT Table_2 (id, test) VALUES (2, N'test2 dup')
INSERT Table_2 (id, test) VALUES (3, N'test3 dup')
INSERT Table_2 (id, test) VALUES (4, N'test4 dup')
SET IDENTITY_INSERT Table_2 OFF

SELECT * FROM Table_2

Open in new window


Output:
id          test
----------- ----------
1           test1
2           test2
3           test3
4           test4
1           test1 dup
2           test2 dup
3           test3 dup
4           test4 dup

(8 row(s) affected)

Open in new window

0
 
LVL 9

Assisted Solution

by:sureshbabukrish
sureshbabukrish earned 1000 total points
ID: 35434212
if you try to insert row by row without  doing this - SET IDENTITY_INSERT Table_XXX ON , then it won't duplicate
if you turn It ON, then you insert same value which is already available in the table
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 23

Expert Comment

by:wdosanjos
ID: 35434647
The question posted is: "Can a few rows have the same value in an identity column?". And the answer is Yes.

> Please enumerate curcumstances when the result below is possible.

. The identity column is not a primary key or an unique key
. Records are inserted with "SET IDENTITY_INSERT ON"

0
 
LVL 1

Author Closing Comment

by:midfde
ID: 35434679
Is it possible to =make= a column be identity in the middle of the table's many-year life time with possible described effect?
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35435072
No, it's not possible.  You need either to create a new column or a new table.  Something like this:

(Generated by SQL Manager Studio)
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Table_2
	(
	id int NOT NULL,
	test nvarchar(10) NULL,
	tt int NOT NULL IDENTITY (1, 1)
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Table_2 SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_Table_2 ON
GO
IF EXISTS(SELECT * FROM dbo.Table_2)
	 EXEC('INSERT INTO dbo.Tmp_Table_2 (id, test, tt)
		SELECT id, test, tt FROM dbo.Table_2 WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_Table_2 OFF
GO
DROP TABLE dbo.Table_2
GO
EXECUTE sp_rename N'dbo.Tmp_Table_2', N'Table_2', 'OBJECT' 
GO
COMMIT

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question