-1

I have a UNIQUE NONCLUSTERED INDEX on my Azure Database, code is below...

CREATE UNIQUE NONCLUSTERED INDEX [IX] ON [dbo].[TableName]
(
    [Username] ASC,
    [GUID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

For some reason there are duplicate values in the table on Username and GUID. How is that possible if this index exist?

Can you turn it off and then on to add a new record with the same Username and GUID?

Getting the below error...

Cannot insert duplicate key row in object 'dbo.Tablename' with unique index

Etienne
  • 7,141
  • 42
  • 108
  • 160
  • 1
    your question is unclear. Do you actually have dupes or is it just you are receiving that expected error msg? – Mitch Wheat Mar 07 '19 at 03:26
  • There are dupes, and I want to add another one. Just dont know how there can be dups with this Index. – Etienne Mar 07 '19 at 03:33
  • @Etienne- Once you will create the unqiue index on table, you will not able to insert the duplicate in that table. or let assume you already have a duplicate record in the table and later you are going to create unique index column, SQL server does not allow you to create unique index due to duplicate data, so there are no possibilities for duplicate data. – Mukesh Arora Mar 07 '19 at 04:00
  • I suspect you have another XY problem: is Username:GUID unique? Or did a hidden uniquiefier column get added? – Mitch Wheat Mar 07 '19 at 04:16

1 Answers1

2

It's possible to disable the checking of constraints when inserting into a table with a UNIQUE index on it using the ALTER TABLE ... NOCHECK command. Inserts can be performed that violate the constraint, and then the constraint can be later re-enabled, leaving the duplicate records intact.

e.g. 

USE AdventureWorks2012;  
GO  
ALTER TABLE Purchasing.PurchaseOrderHeader  
NOCHECK CONSTRAINT FK_PurchaseOrderHeader_Employee_EmployeeID;  
GO

(Reference link)

or

ALTER TABLE [MyTable] NOCHECK CONSTRAINT ALL

Related: How can foreign key constraints be temporarily disabled using T-SQL?

CJBS
  • 15,147
  • 6
  • 86
  • 135
  • There are no constrains on the table, your code does not work, not sure if it's because I am in Azure? – Etienne Mar 07 '19 at 03:34
  • @Etienne Possibly but unlikely (I'd assume MS has maintained compatibility in SQL syntax for forward migrations). I haven't tested in Azure. – CJBS Mar 08 '19 at 00:08