quote:

---

I know if there is no clustered index in the table i.e. heap table, then the system automatically builds a PK.

---

quote:

---

Is there a link you can refer me to for further reading on this very topic where the system creates a PK when the table is a heap?

---

quote:

---

Originally posted by robvolk

...Heaps maintain a unique row identifier (RID) but they are not a primary key...

---

quote:

---

Originally posted by robvolk

quote:

---

I know if there is no clustered index in the table i.e. heap table, then the system automatically builds a PK.

---

False. A primary key is a logical construct. Heaps are physical. You're probably confusing primary key with clustered index, they are not the same thing. Heaps maintain a unique row identifier (RID) but they are not a primary key. Primary keys are never automatically created.

quote:

---

Is there a link you can refer me to for further reading on this very topic where the system creates a PK when the table is a heap?

---

Read up on heaps here: http://goo.gl/Cz9Ky

---

quote:

---

Originally posted by Sachin.Nand

quote:

---

Originally posted by robvolk

...Heaps maintain a unique row identifier (RID) but they are not a primary key...

---

I think that is not true.A heap will never maintain a unique identifier until a primary or unique key is defined on it.

After Monday and Tuesday even the calendar says W T F ....

---

IF OBJECT_ID('tempdb..#foo') IS NOT NULL DROP TABLE #foo
CREATE TABLE #foo (
	[key] INT
	, [value] VARCHAR(255)
	)
	
CREATE INDEX keys ON #foo ([key])

INSERT #foo
SELECT CAST(CAST(NEWID() AS VARBINARY(255)) AS INT)
	,  CAST(CAST(NEWID() AS VARBINARY(255)) AS VARCHAR(255))
FROM
	sys.objects AS a
	CROSS JOIN sys.objects AS b

INSERT #foo VALUES (10, 'HI THERE')

SELECT * FROM #foo WHERE [key] = 10 

quote:

---

Hi, can you please refer me to a link which show the RID is created if the table is a heap?

---

quote:

---

I think that is not true.A heap will never maintain a unique identifier until a primary or unique key is defined on it.

---

create table #a(a int null)
insert #a default values
insert #a default values
insert #a default values
insert #a default values

select *,sys.fn_PhysLocFormatter(%%physloc%%) from #a

quote:

---

If the server has a clustered index then the rid is the clustered index columns

---

quote:

---

plus a sequence number if the index is not unique.

---

quote:

---

Originally posted by robvolk

quote:

---

Hi, can you please refer me to a link which show the RID is created if the table is a heap?

---

How about reading the link I provided and clicking on the other links inside it? And Google works for everyone, not just me.

quote:

---

I think that is not true.A heap will never maintain a unique identifier until a primary or unique key is defined on it.

---

The RID has to be unique, it consists of file ID, page ID and slot number. You can't store two rows in the exact same location. Moreover you can't create a unique index on the RID because it's not accessible (not in a documented way anyhow, and I just tried it anyway and failed). Here's an example:

create table #a(a int null)
insert #a default values
insert #a default values
insert #a default values
insert #a default values

select *,sys.fn_PhysLocFormatter(%%physloc%%) from #a

That's another reason why RID is not a primary key, a primary key cannot be applied to a nullable column. RID is not a column and, technically, not a value either.

More info on heaps at Paul Randal's blog: http://goo.gl/eJwBb

There's more than that, but that's the only one I could find quickly. Read Paul.

---

quote:

---

Again I would say RID key does not have to be unique in a heap table.

---

quote:

---

Also what you are pointing out is the physical location of the data returned by sys.fn_PhysLocFormatter(%%physloc%%). Optimizer does not use that value to identify rows from heaps.

---

quote:

---

Originally posted by robvolk

quote:

---

Again I would say RID key does not have to be unique in a heap table.

---

Alright, please post an example where 1 RID points to 2 or more rows. RID is not a key either.

quote:

---

Also what you are pointing out is the physical location of the data returned by sys.fn_PhysLocFormatter(%%physloc%%). Optimizer does not use that value to identify rows from heaps.

---

I know that too. A RID isn't recorded anywhere except in the leaf level of a non-clustered index on a heap table. The optimizer doesn't care whether the non-clustered index is based on a heap or a B-tree structure.

---

use tempdb
go
create table a(number1 int,number2 int)
create nonclustered index ix_id1 on a(number1)
go
create nonclustered index ix_id on a(number2)
go
insert a values(2,10),(3,100),(4,1000)

DBCC TRACEON(3604)

DBCC IND(tempdb,a,-1)

DBCC PAGE (tempdb, 1, 4840, 3)
GO
DBCC PAGE (tempdb, 1, 4845, 3)

declare @rowid BINARY(8)
set @rowid = 0xA312000001000000
select 
 
       CONVERT (VARCHAR(5),  
                    CONVERT(INT, SUBSTRING(@rowid, 6, 1) 
                               + SUBSTRING(@rowid, 5, 1)))
     + ':'
  
     + CONVERT(VARCHAR(10),  
                    CONVERT(INT, SUBSTRING(@rowid, 4, 1)  
                               + SUBSTRING(@rowid, 3, 1)  
                               + SUBSTRING(@rowid, 2, 1)  
                               + SUBSTRING(@rowid, 1, 1)))  
     + ':'  
   
     + CONVERT(VARCHAR(5),  
                    CONVERT(INT, SUBSTRING(@rowid, 8, 1)   
                               + SUBSTRING(@rowid, 7, 1)))  

quote:

---

Originally posted by Sachin.Nand
Also what you are pointing out is the physical location of the data returned by sys.fn_PhysLocFormatter(%%physloc%%). Optimizer does not use that value to identify rows from heaps.
After Monday and Tuesday even the calendar says W T F ....

---

quote:

---

Originally posted by Sachin.Nand
So if you see the value returned by the above function it is exactly the same what is returned by fn_PhysLocFormatter(%%physloc%%).
So in short an RID key can point to the same location which does not have to be necessarily unique.

---

quote:

---

Originally posted by Alexander Suprun

Are you deliberately trying to confuse everyone in this topic?
You took RIDs from 2 different non-clustered indexes! Which are just pointers in this case, certainly you can create multiple pointers that point to the same record, that's basically the whole purpose of the pointer and of course these RIDs must be the same, otherwise how they can point to the same record? But robvolk was talking about RID values in heap, and if you look at the heap only you will never find 2 RIDs with the same value, because they must be unique.

---

quote:

---

The whole point of the discussion in the fist place was that will SQL Server create unique RID keys for a heap irrespective whether they point to same record or not ,whether the heap has multiple indexes or not which I proved with an example that it is a FALSE assumption.

---

quote:

---

Did you even made an effort to understand what the discussion was all about or just jumped on your guns to bash me ??

---

quote:

---

Originally posted by Sachin.Nand

The whole point of the discussion in the fist place was that will SQL Server create unique RID keys for a heap

---

quote:

---

Originally posted by Sachin.Nandwhether the heap has multiple indexes or not which I proved with an example that it is a FALSE assumption.

---

quote:

---

Which is true. For each record in a heap table there is only one RID and for one RID there is only one record in a table.

---

quote:

---

Originally posted by Sachin.Nand
"Wise men learn more from fools than fools from the wise"...Marcus Porcius Cato

---