February 25, 2015 at 8:08 am
GilaMonster (2/25/2015)
PHYData DBA (2/25/2015)
What reliable business data will reporting from a Heap provide?Not being sarcastic, just need to know to give you a good answer.
If a Heap is required then you can't add a clustered Index. That would mean it is no longer a heap.
The results of a query will not change between a heap and a table with a clustered index. That's part of the requirements for a relational database, that the structure not affect the results.
The performance may be different, the operators used by the QP will be different, but the results can't be. If they are, that's a bug in SQL and the DBA should call CSS and log a case.
That is all very true and why I asked the question.
I do not completely agree with your statement.
The order of the results returned by a query can be changed by adding any index.
IMHO - In 25 years of working with database development I have never encountered a valid use case to create a heap table on a RDBMS database to store data in. There is confusion I have encountered because a HEAP Table on a RDBMS is not identical to a HEAP in C++ and other programing languages. Both of those things are different from the HEAP memory and execution area of an OS.
Things with the same name that are not the same can be challenging to work with.
February 25, 2015 at 8:12 am
PHYData DBA (2/25/2015)
I do not completely agree with your statement.The order of the results returned by a query can be changed by adding any index.
Not if you have an ORDER BY clause.
If you don't have an order by, you're telling SQL that the order of the results is completely irrelevant and hence you can't complain when an index change (clustered OR nonclustered) or plan change results in a different order in the returned resultset.
No order by, no guarantee of order.
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
February 25, 2015 at 8:30 am
GilaMonster (2/25/2015)
PHYData DBA (2/25/2015)
I do not completely agree with your statement.The order of the results returned by a query can be changed by adding any index.
Not if you have an ORDER BY clause.
If you don't have an order by, you're telling SQL that the order of the results is completely irrelevant and hence you can't complain when an index change (clustered OR nonclustered) or plan change results in a different order in the returned resultset.
No order by, no guarantee of order.
You are exactly right.
HEAP tables as a "performance booster" and order by clauses don't really go together when you have millions of rows.
I am making an educated guess the order by does not exist.
butcherking13 - You got the attention of some very good experts now. How about a sample or two of CODE from these Stored Procedures and Heaps so they can give you some better informed advice?
February 25, 2015 at 8:34 am
ZZartin (2/19/2015)
Eric M Russell (2/19/2015)
In the original post, it was stated that table contains no primary key. Regardless of what the table contains (datatypes or internal value structure), all tables at least need a primary key. Whether that key is also clustered or whether other non-unique indexes are included is optional.
Ideally the table would have a narrow primary key that is also clustered and with the leading column sequentially incrmenting. For example on a table that contains a count of sales by Period and Region it would be: PeriodKey, SalesRegionKey.
Why?
Not all tables have a logically primary key, and throwing an identity column on a table just for the sake of making it have a primary key doesn't necessarily help anything.
Of course a table can technically exist with no primary key, just like a feral child can actually have no name. But I stand by my position that all tables should have a primary key, whether that be a natural key or an identity. If the database is a high school homework assignment, the teacher may not count it against you, but in the real world it matters.
Yes, adding an auto incremented identity column to a poorly designed heap (think Entity Framework) is useful. Even if the table is intended for no other purpose than populating a dropdown listbox, the application needs a key value to identify which row was selected.
Here is why you want to add an additional column for the purpose of constructing a natural key. Let's assume an agency that operates highway toll booths wants a count of traffic by state of origin. Maybe some of you think (or perhaps it's a slow day and you're pretending just for kicks) that StateCode by itself is sufficient for the purpose. However, it would be foolish for a number of different reasons not to add a date/time stamp to that row. Without that, you can't measure traffic flow over time. Also without a unique column combination you have no way to confirm if a malfunctioning traffic monitor is inserting duplicating records. That InsertDateTime + StateCode makes a reliable primary key. To complete the design, that primary key should be clustered as such (datetime then statecode) for reasons mentioned earlier.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 25, 2015 at 8:54 am
Eric M Russell (2/25/2015)
ZZartin (2/19/2015)
Eric M Russell (2/19/2015)
...
Why?
Not all tables have a logically ...
Of course a table can technically exist with no primary key, just like a feral child can actually have no name. But I stand by my position that all tables should have a primary key, whether that be a natural key or an identity.
Feral children don't read either. Please read the TechNet SQL BOL article called Heap Structures.
https://technet.microsoft.com/en-us/library/ms188270(v=sql.105).aspx
A heap is a table without a clustered index.
I don't like heaps either, but they are out there, get used to it.
February 25, 2015 at 8:56 am
Eric M Russell (2/25/2015)
To complete the design, that primary key should be clustered as such (datetime then statecode) for reasons mentioned earlier.
A clustered key, a clustered Index, and a Composite Key (what you have described here) are three different things.
Please stop!
February 25, 2015 at 8:57 am
Eric M Russell (2/25/2015)
ZZartin (2/19/2015)
Eric M Russell (2/19/2015)
In the original post, it was stated that table contains no primary key. Regardless of what the table contains (datatypes or internal value structure), all tables at least need a primary key. Whether that key is also clustered or whether other non-unique indexes are included is optional.
Ideally the table would have a narrow primary key that is also clustered and with the leading column sequentially incrmenting. For example on a table that contains a count of sales by Period and Region it would be: PeriodKey, SalesRegionKey.
Why?
Not all tables have a logically primary key, and throwing an identity column on a table just for the sake of making it have a primary key doesn't necessarily help anything.
Of course a table can technically exist with no primary key, just like a feral child can actually have no name. But I stand by my position that all tables should have a primary key, whether that be a natural key or an identity. If the database is a high school homework assignment, the teacher may not count it against you, but in the real world it matters.
Yes, adding an auto incremented identity column to a poorly designed heap (think Entity Framework) is useful. Even if the table is intended for no other purpose than populating a dropdown listbox, the application needs a key value to identify which row was selected.
Here is why you want to add an additional column for the purpose of constructing a natural key. Let's assume an agency that operates highway toll booths wants a count of traffic by state of origin. Maybe some of you think (or perhaps it's a slow day and you're pretending just for kicks) that StateCode by itself is sufficient for the purpose. However, it would be foolish for a number of different reasons not to add a date/time stamp to that row. Without that, you can't measure traffic flow over time. Also without a unique column combination you have no way to confirm if a malfunctioning traffic monitor is inserting duplicating records. That InsertDateTime + StateCode makes a reliable primary key. To complete the design, that primary key should be clustered as such (datetime then statecode) for reasons mentioned earlier.
/shrug Like i said earlier this is getting very off topic from the original discussion and I gave you a perfectly good example of a case where a primary key would be completely unnecessary unless you over design or change the requirements.
February 25, 2015 at 9:01 am
PHYData DBA (2/25/2015)
Feral children don't read either. Please read the TechNet SQL BOL article called Heap Structures.https://technet.microsoft.com/en-us/library/ms188270(v=sql.105).aspx
A heap is a table without a clustered index.
I don't like heaps either, but they are out there, get used to it.
Considering that the post you're replying to is discussing whether or not all tables should have a primary key (with no mention of heap anywhere in the quote or the two posts it was quoting), both the insult and the link are out of place.
You can make points without being insulting and this forum prides itself on polite, respectful discourse. Please could you moderate your comments appropriately.
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
February 25, 2015 at 9:04 am
I have already provided this to butcherking13 but I am going to put it here since I am sort of amazed nobody has yet.
-- Query to show Missing Indexes the Query Optimizer recommend you create
SELECT c.avg_user_impact, a.statement AS TableName
, a.equality_columns, a.inequality_columns, a.included_columns, c.unique_compiles, c.user_seeks, c.last_user_seek,
c.avg_total_user_cost
FROM sys.dm_db_missing_index_details AS a INNER JOIN
sys.dm_db_missing_index_groups AS b ON a.index_handle = b.index_handle INNER JOIN
sys.dm_db_missing_index_group_stats AS c ON b.index_group_handle = c.group_handle
Not sure how many people read today's editorial but it links to a page containing this quote:
Leaders think and talk about the solutions. Followers think and talk about the problems. – Brian Tracy
Not certain why I felt like posting that quote. Trying not to think about it... 😛
February 25, 2015 at 9:08 am
GilaMonster (2/25/2015)
PHYData DBA (2/25/2015)
Feral children don't read either. Please read the TechNet SQL BOL article called Heap Structures.https://technet.microsoft.com/en-us/library/ms188270(v=sql.105).aspx
A heap is a table without a clustered index.
I don't like heaps either, but they are out there, get used to it.
Considering that the post you're replying to is discussing whether or not all tables should have a primary key (with no mention of heap anywhere in the quote or the two posts it was quoting), both the insult and the link are out of place.
You can make points without being insulting and this forum prides itself on polite, respectful discourse. Please could you moderate your comments appropriately.
Gail I found nothing Polite or respectful in the post I responded to.
If you notice I used the same language that was used in the original post just a LOT less of it.
If you find that in polite and disrespectful then I apologize to you.
February 25, 2015 at 9:15 am
PHYData DBA (2/25/2015)
GilaMonster (2/25/2015)
PHYData DBA (2/25/2015)
Feral children don't read either. Please read the TechNet SQL BOL article called Heap Structures.https://technet.microsoft.com/en-us/library/ms188270(v=sql.105).aspx
A heap is a table without a clustered index.
I don't like heaps either, but they are out there, get used to it.
Considering that the post you're replying to is discussing whether or not all tables should have a primary key (with no mention of heap anywhere in the quote or the two posts it was quoting), both the insult and the link are out of place.
You can make points without being insulting and this forum prides itself on polite, respectful discourse. Please could you moderate your comments appropriately.
Gail I found nothing Polite or respectful in the post I responded to.
If you notice I used the same language that was used in the original post just a LOT less of it.
If you find that in polite and disrespectful then I apologize to you.
You should be apologising to the target of your insult, not to Gail. The big difference here is that your post is targeted at a person, not at a person's opinion.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 25, 2015 at 9:28 am
If I offended anyone here with my analogy, of a table having no primary key, to that of a feral child having no name, then I'm sorry. I think that all feral children and keyless tables deserve attention and help. My point was just that in a relational database, there is little practical use for a collection of records having no uniquely identifying key. They can't be properly managed or counted.
I'm moving on.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 25, 2015 at 9:29 am
Phil Parkin (2/25/2015)
PHYData DBA (2/25/2015)
GilaMonster (2/25/2015)
PHYData DBA (2/25/2015)
Feral children don't read either. Please read the TechNet SQL BOL article called Heap Structures.https://technet.microsoft.com/en-us/library/ms188270(v=sql.105).aspx
A heap is a table without a clustered index.
I don't like heaps either, but they are out there, get used to it.
Considering that the post you're replying to is discussing whether or not all tables should have a primary key (with no mention of heap anywhere in the quote or the two posts it was quoting), both the insult and the link are out of place.
You can make points without being insulting and this forum prides itself on polite, respectful discourse. Please could you moderate your comments appropriately.
Gail I found nothing Polite or respectful in the post I responded to.
If you notice I used the same language that was used in the original post just a LOT less of it.
If you find that in polite and disrespectful then I apologize to you.
You should be apologising to the target of your insult, not to Gail. The big difference here is that your post is targeted at a person, not at a person's opinion.
You are right. I insulted a person not the idea.
I read a post filled with negativity pointing out only the problems and offering no solutions that seemed WAY off topic to what the discussion was about.
Not only that, but the post also seems to be confusing ideas and concepts about the things it is talking about.
I apologize Eric for being so insulting in my request to become better informed before posting advice that is off topic.
I was trying to be brief, direct and use some of your own words to make the point.
I do agree with you that Heaps are not great permanent structures and have no real place in a well designed RDBMS.
I do not agree with the analogies the in you post or you soap boxing the need for primary keys in all tables and the woes of Entity framework.
I am going to believe that your confusion of Composite Keys and Clustered Keys is a typo that was cause by the passion of the subject you are talking about.
February 25, 2015 at 9:32 am
I appreciate all the comments and realize some heavy hitters responded and I appreciate it!!
I am testing on my DEV box as time persists as it is month end and have priorities, but this post opened doors and I'm grateful.
I will test with NCIX with SPROC when time permits me. I tried earlier, but realize I made a mistake and will go through again.
I posted earlier I can't post the code due to several reasons.
Thank you again!
February 25, 2015 at 9:33 am
Eric M Russell (2/25/2015)
If I offended anyone here with my analogy, of a table having no primary key, to that of a feral child having no name, then I'm sorry. I think that all feral children and keyless tables deserve attention and help. My point was just that in a relational database, there is little practical use for a collection of records having no uniquely identifying key. They can't be properly managed or counted.I'm moving on.
It is cool Eric. I am sorry that my post was so insult.
How could we know what powder kegs our words could be construed as.
I love how we can both disagree on so much, but still agree that both of these things deserve our help and love.
Lets concentrate on the positive things we can do for the future, not the negative stuff that exists in the past.
Viewing 15 posts - 46 through 60 (of 63 total)
You must be logged in to reply to this topic. Login to reply