[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql server check index scan operation

Posted on 2011-10-22
7
Medium Priority
?
296 Views
Last Modified: 2012-05-12
Hi Expert,

I prepare 433 Sql server Exam,

How I can find index scan operation in this query? This from 433 self-paced trainning book.

USE AdventureWorks;

CREATE NONCLUSTERED INDEX OrderDateIndex
    ON Sales.SalesOrderHeader (OrderDate);

SELECT COUNT(*) FROM Sales.SalesOrderHeader
    WHERE YEAR(OrderDate) = 2004;


Thanks in advance.

0
Comment
Question by:dshi15
  • 3
  • 3
7 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37011262
Howdy. Since you are trying to learn, I will give you some hints and through some back and forth, hopefully you will learn the concepts better. Is there any think that will show you how the query you have will be executed? Additionally, does the YEAR() function affect anything in your query?
0
 
LVL 1

Author Comment

by:dshi15
ID: 37011321
thanks, I don't you explain anything yet, I want see the Figure, in the book, it said pressing Ctrl+M you can view execution plan for the query, and there is a figure on the book, how I can see it, after I run the query I can not see anything except query result by pressing Ctrl+M.
0
 
LVL 1

Author Comment

by:dshi15
ID: 37011343
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1800 total points
ID: 37011414
Execution plan is what you want. Good. In case the shortcut is just not working, right-click and select show actual execution plan. When it is done, you will see what is going on. You should see a table scan even though you have an index on the date column. My hint question of what YEAR() does then applies. Having the function on the column renders the index useless. ;)
0
 
LVL 13

Assisted Solution

by:dwkor
dwkor earned 200 total points
ID: 37011596
If your goal is to obtain the knowledge rather than get certification, I'd recommend to start with something different than training kit.

Probably the best book series is "Inside Microsoft SQL Server". For that exam I'd suggest you to either read this one:
http://www.amazon.com/Microsoft®-SQL-Server®-2008-Internals/dp/0735626243

Or those 2:
http://www.amazon.com/Inside-Microsoft%C2%AE-SQL-Server-2005/dp/0735621969
http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055

I personally like 2005 editions more than 2008 although it does not cover some of 2008 features
0
 
LVL 1

Author Comment

by:dshi15
ID: 37011601
Thank you very much, got it, if I have further question, I will open new question.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37011628
You are most welcome. The issue is usually discussed under search argument able topic. See SQL Server MVP Rob Farley's explanation here on EE: http://www.experts-exchange.com/A_2531.html

Best regards and happy coding,

Kevin
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question