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. ThomasDo we have a formula (involving the number of records in the table) to compute the cost?
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?
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. Thomasquote: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
]]>