Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Accessing data from the data pages

Author  Topic 

sgondesi
Posting Yak Master

200 Posts
Posted - 2013-12-04 : 09:19:58
Hello everyone..

Will the table scan and the index scan takes equal time to retrieve data from data pages?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-12-04 : 09:41:40
nope..they wont.
As table scan will scan through entire table data pages whereas index scan will only scan through index pages to reach upto leaf level nad then use pointers to scan sccoociated data pages so it would be less. I hope you meant non clustered index.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts
Posted - 2013-12-04 : 11:46:12
quote:
Originally posted by visakh16

nope..they wont.
As table scan will scan through entire table data pages whereas index scan will only scan through index pages to reach upto leaf level nad then use pointers to scan sccoociated data pages so it would be less. I hope you meant non clustered index.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



hmmm

I think that provided the table is in the cache and the index is unique, yes they would be about the same. In that case if there are n rows in the table, SQL Server has to do n comparisons looking for a match wether it is an Index Scan or Table scan.

Also, if the Index is non-covering, SQL Server has the additional bookmark lookup with the Index scan.

If the table and the index are not in the cache, the Index Scan could be faster if the records are large. It could take much longer to import the Table to cache then it would the Index.

Here is someone who tried the experiment: http://architects.dzone.com/articles/full-table-scan-vs-full-index
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts
Posted - 2013-12-04 : 11:58:01
quote:
Originally posted by visakh16

nope..they wont.
As table scan will scan through entire table data pages whereas index scan will only scan through index pages to reach upto leaf level nad then use pointers to scan sccoociated data pages so it would be less. I hope you meant non clustered index.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



No sir,
I dont mean non-clustered indexes.

Give me conformation in the below aspect..
will the table scan differ from index scan which is performed by a query which is not using the index on that table actually.(The column on which the index is present is not used in the where clause)

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2013-12-04 : 12:25:35
If the clustered index fields are not in the WHERE clause then the Query Plan will show the "table scan" as an "index scan on the clustered index" won't it? When I see one of those my brain tells me SQL has used the wrong index, until I then realise that its the Clustered Index on the table

Sorry if I'm answering the wrong question
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts
Posted - 2013-12-04 : 12:56:42
quote:
Originally posted by Kristen

If the clustered index fields are not in the WHERE clause then the Query Plan will show the "table scan" as an "index scan on the clustered index" won't it? When I see one of those my brain tells me SQL has used the wrong index, until I then realise that its the Clustered Index on the table

Sorry if I'm answering the wrong question


I understood your point.
And also please also tell me when an index scan happens? I mean in which scenario?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2013-12-04 : 13:04:18
This would - I think! - someone else can confirm :)

WHERE ThisColumnIsIndexed BETWEEN @Start AND @End
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts
Posted - 2013-12-04 : 13:05:08
quote:


I understood your point.
And also please also tell me when an index scan happens? I mean in which scenario?



Here is a possible scenario:

- you have a table with one million rows
- and 30 columns
- you have only one index (LastName, FirstName)
- your query is select LastName where FirstName = 'xxx'

SQL Server would have to do a scan since there is no index on FirstName. But the Index would have all it needs for this query (i.e. covering). Since there are 30 columns, it would be a lot of effort to bring the table from disk to cache. And there might be only a few bookmark look ups, so the IndexScan would be quicker than a TableScan.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2013-12-04 : 13:08:32
So was mine an Index Seek, rather than Index Scan, then?
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts
Posted - 2013-12-04 : 13:20:59
quote:
Originally posted by Kristen

So was mine an Index Seek, rather than Index Scan, then?


I believe yes, it would be an Index Seek, unless SQL Server would predict a high number of matches (i.e. low selectivity), it may opt to use the Index Scan.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-12-05 : 02:13:52
quote:
Originally posted by denis_the_thief

quote:
Originally posted by visakh16

nope..they wont.
As table scan will scan through entire table data pages whereas index scan will only scan through index pages to reach upto leaf level nad then use pointers to scan sccoociated data pages so it would be less. I hope you meant non clustered index.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



hmmm

I think that provided the table is in the cache and the index is unique, yes they would be about the same. In that case if there are n rows in the table, SQL Server has to do n comparisons looking for a match wether it is an Index Scan or Table scan.

Also, if the Index is non-covering, SQL Server has the additional bookmark lookup with the Index scan.

If the table and the index are not in the cache, the Index Scan could be faster if the records are large. It could take much longer to import the Table to cache then it would the Index.

Here is someone who tried the experiment: http://architects.dzone.com/articles/full-table-scan-vs-full-index


That article speaks from MySQL front. Will there any difference in MSSQL implementation?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts
Posted - 2013-12-05 : 04:57:05
quote:
Originally posted by Kristen

This would - I think! - someone else can confirm :)

WHERE ThisColumnIsIndexed BETWEEN @Start AND @End


No sir.
In this case index seek will take place.
Because, if the column (on which the index is created) is present in the "where" clause, then index seek will take place.

Else, that means there is an index on some column but we are not using that column in the where clause, then index scan will happen.

And finally when there is no index on a table, the table scan will take place.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts
Posted - 2013-12-05 : 05:09:04
quote:
Originally posted by denis_the_thief

quote:
Originally posted by visakh16

nope..they wont.
As table scan will scan through entire table data pages whereas index scan will only scan through index pages to reach upto leaf level nad then use pointers to scan sccoociated data pages so it would be less. I hope you meant non clustered index.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



hmmm

I think that provided the table is in the cache and the index is unique, yes they would be about the same. In that case if there are n rows in the table, SQL Server has to do n comparisons looking for a match wether it is an Index Scan or Table scan.

Also, if the Index is non-covering, SQL Server has the additional bookmark lookup with the Index scan.

If the table and the index are not in the cache, the Index Scan could be faster if the records are large. It could take much longer to import the Table to cache then it would the Index.

Here is someone who tried the experiment: http://architects.dzone.com/articles/full-table-scan-vs-full-index



I feel that you told most valid points in the above post. Thank you sir.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts
Posted - 2013-12-05 : 05:12:25
quote:
Originally posted by Kristen

So was mine an Index Seek, rather than Index Scan, then?


Yes it is an index seek.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts
Posted - 2013-12-05 : 05:20:00
quote:
Originally posted by denis_the_thief

quote:


I understood your point.
And also please also tell me when an index scan happens? I mean in which scenario?



Here is a possible scenario:

- you have a table with one million rows
- and 30 columns
- you have only one index (LastName, FirstName)
- your query is select LastName where FirstName = 'xxx'

SQL Server would have to do a scan since there is no index on FirstName. But the Index would have all it needs for this query (i.e. covering). Since there are 30 columns, it would be a lot of effort to bring the table from disk to cache. And there might be only a few bookmark look ups, so the IndexScan would be quicker than a TableScan.



Thanks a lot for your support.
If there is an index(not covering index) on columns LastName and FirstName in this order, will the first column in the index (i.e LastName) is the only column which gets stored in stored in index pages?
No right?
If yes then why to mention second column in the index creation?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts
Posted - 2013-12-05 : 05:26:25
quote:
Originally posted by denis_the_thief

quote:
Originally posted by Kristen

So was mine an Index Seek, rather than Index Scan, then?


I believe yes, it would be an Index Seek, unless SQL Server would predict a high number of matches (i.e. low selectivity), it may opt to use the Index Scan.


Do mean this?
Will the SQL Server
1. Selects index scan if there is more selectivity for the query submitted? and
2. Selects an index seek if there is less selectivity for the query submitted?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts
Posted - 2013-12-06 : 14:21:37
quote:

That article speaks from MySQL front. Will there any difference in MSSQL implementation?



Good Point, I hadn't even noticed that.

I ran a test. It's not the be all and end all, so many factors could be at play.

I run the same query under 3 scenarios: Table Scan, Clustered Index Scan, Non-Clustered Index Scan. I ran each under both an empty cache and data-in-cache.

The table had a few million records. The table had 30 columns while the Non-Clustered Index had only 3 columns. Also, the index was covering so there was no bookmark lookup.

Here are my Results!:


Non-Clustered Index Scan Empty Cache		1,500ms
Clustered Index Scan Empty Cache	       16,500ms
Table Scan Empty Cache			       18,600ms

Non-Clustered Index Scan Data-In-Cache	          380ms
Clustered Index Scan Data-In-Cache		  570ms
Table Scan Data-In-Cache			  550ms


So the Index Scan was much faster than Table Scan when the cache was empty. It was also faster than the Table Scan when the data was in-cache, but not a huge amount.

I also found out that the Table Scan is only for the Heap.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-12-07 : 08:36:03
yep..thats what I thought too. Just wanted someone to confirm that.
Yes..that true. Table Scan would be for heap. Otherwise it will be replaced by clustered index scan
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts
Posted - 2013-12-11 : 09:22:01
quote:
Originally posted by denis_the_thief

quote:

That article speaks from MySQL front. Will there any difference in MSSQL implementation?



Good Point, I hadn't even noticed that.

I ran a test. It's not the be all and end all, so many factors could be at play.

I run the same query under 3 scenarios: Table Scan, Clustered Index Scan, Non-Clustered Index Scan. I ran each under both an empty cache and data-in-cache.

The table had a few million records. The table had 30 columns while the Non-Clustered Index had only 3 columns. Also, the index was covering so there was no bookmark lookup.

Here are my Results!:


Non-Clustered Index Scan Empty Cache		1,500ms
Clustered Index Scan Empty Cache	       16,500ms
Table Scan Empty Cache			       18,600ms

Non-Clustered Index Scan Data-In-Cache	          380ms
Clustered Index Scan Data-In-Cache		  570ms
Table Scan Data-In-Cache			  550ms


So the Index Scan was much faster than Table Scan when the cache was empty. It was also faster than the Table Scan when the data was in-cache, but not a huge amount.

I also found out that the Table Scan is only for the Heap.


Since it you are taking a covering non-clustered index, this non-clustered index is that efficient.

If you take a non-clustered(non-covering) index, then the decision of opting for a table or index scan depends on the number of rows that a query results right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-12-11 : 12:26:17
Not only that some of other factors lie selectivity of data etc.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts
Posted - 2013-12-11 : 12:34:11
quote:
Originally posted by visakh16

Not only that some of other factors lie selectivity of data etc.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Ok sir.
Can you share some of the other factors that lie selectivity.

And i dint get your statement sir. "Table scan would be for heap". Can you go detail about this.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site