Why Allow Heaps at All?

Question

Why Allow Heaps at All?

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

GilaMonster (12/29/2012)
You may be interested to know that SQL Azure (or whatever it's called this week), does not allow heaps.

It's called SQL Database at the moment which is part of the higher level offering now named Windows Azure. I had to go look all of this up again. You are right, they seemed to change it weekly there for a while. Formerly it was SQL Azure Database in its most recent iteration before this one (I think). At one point it was SQL Azure and probably 3 things in between too.

Windows Azure Feature List, see SQL Database[/url] < Note: this link is liable to be dead as early as tomorrow when everything having to do with Azure is rebranded once again 😛

I am under the impression that the clustered index requirement is a function of the way the Azure database team decided to replicate data behind the scenes to accomplish redundancy within the cloud and less about consciously moving away from heaps as a means to store data, but I could be wrong about that. Microsoft is notorious for leveraging their existing technology to bring things to market sooner even if it is not the most technologically savvy move. I am venturing a guess that this is another one of those cases. Mirroring does not impose any requirements on database schema as Replication does yet they chose to use the Replication subsystem as a means to keep redundant copies of data in the cloud, maybe for expediency or maybe for scalability since Replication scales much easier than does Mirroring. Maybe the Azure team will release something comprehensive on the makings of that decision at some point. I could not find anything on it in some basic searches.

@Hakim.Ali, even though there is a requirement in Azure to have a clustered index on all tables you may be interested to know that the requirement is still on us to define that key. From

MSDN Blogs > SQL Azure Team Blog > Why Do I Need a Clustered Index? (bold added by me):

In SQL Azure the ordering of the data pages are the key to our data replication, and that is why you need to have a clustered index on your tables. We believe that you are the best person to pick the clustered index and will pick that best clustered index for your database design to achieve maximum performance.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

opc.three (12/29/2012)
I am under the impression that the clustered index requirement is a function of the way the Azure database team decided to replicate data behind the scenes to accomplish redundancy within the cloud and less about consciously moving away from heaps as a means to store data, but I could be wrong about that.

No clue offhand, however replication (transactional replication in the non-cloudy versions of SQL) requires a primary key, not a clustered index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

GilaMonster (12/29/2012)
opc.three (12/29/2012)
I am under the impression that the clustered index requirement is a function of the way the Azure database team decided to replicate data behind the scenes to accomplish redundancy within the cloud and less about consciously moving away from heaps as a means to store data, but I could be wrong about that.
No clue offhand, however replication (transactional replication in the non-cloudy versions of SQL) requires a primary key, not a clustered index.

It's odd too because the clustered index on Windows Azure SQL Database (that's too long too type every time) tables does not even need to be unique. I just verified it. The requirement and the explanation of why it is required from the blog I posted (which still seems valid 2.5 years later) seems to imply that the underpinnings are stuck somewhere between SQL Server Transactional Replication and block-level Replication (a'la what many SANs can do).

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 4

GilaMonster (12/29/2012)
You may be interested to know that SQL Azure (or whatever it's called this week), does not allow heaps.

Understood. Found that out in the very early documentation on SQL Azure. Any idea why they have that particular restriction?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Jeff Moden (12/29/2012)
GilaMonster (12/29/2012)
You may be interested to know that SQL Azure (or whatever it's called this week), does not allow heaps.
Understood. Found that out in the very early documentation on SQL Azure. Any idea why they have that particular restriction?

Not a clue. You might want to ask someone that can actually spell SQL Azre.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass