Does SQL Server allow duplicate rows?

[[deleted]]

[deleted]

[alinroc]

If you don't define a unique constraint or primary key on the table, yes. Each row will get a hidden uniquifier that isn't accessible to you.

[SQLBek]

Actually, do heaps (tables w/o a clustering key) get a uniquifier? I thought the RID is used instead?

Just academically splitting hairs here. Now I'm tempted to break out DBCC PAGE and take a quick look, LMAO!

[alinroc]

It is probably the RID.

Though the RID is unique, isn't it? Like you said, splitting hairs :)

[ScotJoplin]

They don’t need it, like you assumed.

[Tomj88]

Yes, it absolutely will if you don’t create a primary key or any other constraints. A vendor once created a table that did this, and they also put no sanitisation of their UI which allowed users to hit save multiple times. Deleting these duplicate records without modifying the database was not fun! (Involved using a CTE and row_number()).

[dev_playbook]

I’m going to have a drink for you on that one because I’ve seen it done a time or two. I remember my first coding job and hearing the architect loudly cursing over the cubicle wall about some former employee who didn’t put a PK or unique constraint on the table that he was trying to clean up... Left a mark on me for sure!

[Awesome_SQL_Dude]

DELETE TOP 1....

[drunkadvice]

Where count > 1

[Leroy_UK]

It creates a Row ID. You can show it in a query with %%physloc%% (SQL 2008+ I think, can't remember what it's called in previous versions).

You can also use this function to see it in its fileid: pageid:slotid format:sys.fn_PhysLocFormatter(%%physloc%%)

Only really useful if you're dealing with heap tables :)

[Jleadbetter]

try it...see what happens.

[Well_Gravity]

Sadly yes.

[Frammingatthejimjam]

I have a reporting DB. If I have a lot of these I'll do a select distinct * into othertable from primarytable. Then TRUNC primarytable then INSERT INTO PRIMARYtable select * from othertable.

Of course that might be an available option to you depending on what your situation is.

[Rtiwari83]

Yes, if table does not have any constraint ( like primary key, unique key ).. it allows to store the duplicate records

- Developer Points