I need help on SQL!

Question

I need help on SQL!

sql_learner

SSC Rookie

Points: 37
More actions
June 3, 2005 at 6:23 am

#164729

I need help on the following queries, can anyone tell me what hypothetical execution plans these queries do??

(1)SELECT *
FROM TABLE;
(2)SELECT COUNT(*)
FROM TABLE;
(3)SELECT S_SUPPKEY
FROM TABLE;
(4)SELECT *
FROM TABLE
WHERE S_NAME = 'User';

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Without knowing HOW you set up the table (indexes, row count, etc..) it is impossible for us to even GUESS.

However, IF you hit CTRL+L inside QA it will provide you an "estimated execution plan" OR CTRL+K and RUN the query it will provide the ACTUAL plan used

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Looking at my crystal I see:

1) Table scan or Clustered Index Scan

2) Scan of the narrowest index SQL Server can find. If no index present, I think table scan

3) Index scan or clustered index scan.

4) Index Seek or Clustered Index Seek.

But as AJ pointed out, you can easily check this out yourself.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Actually, I can answer for #2.

If there's an index on the table on a column that is not nullable, then the query will scan that index to count all rows.

If not suitable index is found, then a table scan will be performed.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Ninja's_RGR'us

SSC Guru

Points: 294069

June 3, 2005 at 6:36 am

#562854

Damn, got beaten again .

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Sorry,

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sql_learner SSC Rookie Points: 37 More actions · Answer 6

wow... the replies are fast!

thanks alot guys, appreciated the helps.. will look tru the Ctrl + K

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

You're welcome!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

3 replies in 10 minutes... I've seen faster in this board .

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

What do you expect on a Friday afternoon?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

5 replies in 2 minutes... we are less busy that time of the week .

sql_learner SSC Rookie Points: 37 More actions · Answer 11

heh, its actually 8.51pm at my place.. doing my assignment..

actually... where do i press the ctrl + k at?? what QA??

thanks all..

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

QA = Query Analyzer

Paste the 4 queries in the window, then hit ctrl-k (or go in the query menu, and select show execution plan).

Then run the queries (F5 or the green play button).

Then under the results you'll see the execution plan tab.

sql_learner SSC Rookie Points: 37 More actions · Answer 13

sql_learner

SSC Rookie

Points: 37

June 3, 2005 at 7:09 am

#562876

where can I find the QA?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Start / All programs / Sql Server / Query Analyser

You'll be prompted to login to a server.. select the server and enter login info as required. Then follow the previous steps.