Heaps and data-pages | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Heaps and data-pages

Hi All! I got another doubt related to indexes – I understand that, in heaps, the data is not stored in any order and SQL Server has to access the pages sequentially. What is the cost involved in accessing the data pages for SQL Server 2005 in case of tables, which, do not have any clustered index on them? Thanks in advance.
~Avnip
SQL Server scans the table, finds the data (or not) and goes on scanning until the table is processed.
With no indexes defined on a table, you’re wasting precious I/O resources and force the server to do a lot more work than needed to be done. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
But, if you DONT have a where clause (I know, you ought to have one), a table scan is the fastest method. Roji. P. Thomas
http://toponewithties.blogspot.com

Thanks Frank/Roji, The where clause tip was an interesting one.
Do we have a formula (involving the number of records in the table) to compute the cost? ~Avnip
You can take help of Execution plan in this case on that query to see the cost. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
quote:Originally posted by [email protected] Thanks Frank/Roji, The where clause tip was an interesting one.
Do we have a formula (involving the number of records in the table) to compute the cost?
Yes, we have. Search for the articles of Joe Chang in this site. Edit: Here is the link:http://www.sql-server-performance.com/jc_sql_server_quantative_analysis1.asp Roji. P. Thomas
http://toponewithties.blogspot.com

Roji, Thanks a ton!
I might need some help understanding the article, though! ~Avnip
Roji, Is it true for ORDER BY clause too?
quote:But, if you DONT have a where clause (I know, you ought to have one), a table scan is the fastest method. Roji. P. Thomas

quote:Originally posted by [email protected] Roji, Is it true for ORDER BY clause too?
quote:But, if you DONT have a where clause (I know, you ought to have one), a table scan is the fastest method. Roji. P. Thomas

No. An Index on the ORDER By column will be helpful. Other wise the QP has to perform an explicit sort, whcih is a costly operation. Roji. P. Thomas
http://toponewithties.blogspot.com

]]>