Explain report. 2

[JonMa]

Hi.
I have a database with many records and needs to know the field names of a table. I do not have EXPLAIN installed on my PC so I am a little worried about table space scan for this query.

Is it possible for someone to run an EXPLAIN report on sql server for the following sql to see if they avoid doing a table space scan:

SELECT * FROM TABLE WHERE 1=0
SELECT TOP 1 * FROM TABLE.

Hope someone can help.

 

[SQLBill]

Okay, this is a new one to me. What is EXPLAIN? I can't find any mention of it in the SQL Server Books OnLine.

-SQLBill

 

[JonMa]

Hello SQLBill.
Maybe I am using the wrong words.
Maybe you would call it Query Optimizer,Query Plan.
Regards Jon.

 

[SQLBill]

One thing about ME (or anyone else) running this for you is that our tables aren't the same as yours.

That said, I ran this against one of my tables and this is what the Query Analyzer showed as Estimated Execution Plan:

The first one was a 100% Constant Scan.
The second one was a 100% Clustered Index Scan.

If you have SQL Server installed, you should also have access to the client tools. If not, you should install the client tools which are on the installation disk.

-SQLBill

 

[JonMa]

No I do not have the sql server.
Thanks SQLBill.
Regards Jon.

 

[SQLBill]

Then why do you need this information? It's only going to help with running a query on SQL Server (which you don't have).

((I'm just curious))).

-SQLBill

 

[JonMa]

Hello SQLBill.
I am using MS Access but as you know it has no query analyzer. They should be very close.
Regards Jon.

 

[SQLBill]

Probably not as close as you think. They are two different products and use different SQL. Most likely the compiler is different also.

But the bottom line is that your table is probably not set up the same way as mine (indexes etc). And THAT is going to be the biggest issue with me or anyone else running an estimated plan for you.

But if you really think it helped you, then good luck.

-SQLBill

 

[Z3]

I side with SQLBill. SQL Server came from Sybase (do they still exist?), where Access born as Microsoft garage skunk project. I still wonder why don't Microsoft make Access ANSI SQL compliant, probably because Access is so bad even mighty MS can not do much to it.

 

[TysonLPrice]

Okay, this is a new one to me. What is EXPLAIN? I can't find any mention of it in the SQL Server Books OnLine.

Might not be relevant but on the IBM mainframe DB2 there is a utility called Explain. It analyzes the path of SQL statements to allow tuning.

 

[SQLBill]

Tyson,

Thanks for that information. No wonder why I never heard of it (never used DB2).

-SQLBill