Which of the following SQL Statements is faster an

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

General SQL Server Forums

New to SQL Server Administration

Which of the following SQL Statements is faster an

Author

Topic

serverprogrammer
Starting Member

2 Posts

Posted - 2010-06-26 : 18:49:11

Hello guys,

Simply I have two questions:

1- Suppose we have a database DB1
2- We have the following tables

Table #1: HR.Employees_Data
The HR.Employees_Data table consists of 200 columns
The HR.Employees_Data Table has more than 100,000 rows of data
The HR.Employees_Data RowSize is about 1.5 Kilo Bytes
The primary index is EMP_NO ON EMP_NO field

Table#2: HR.Departments_Data
The HR.Departments_Data table has 50 rows of data
The HR.Departments_Data consists of three columns
The primary index is Dept_NO on Dept_NO field

Questions #1:

Which of the following SQL Statements is faster and why?

1- Select HR.Employees_Data.* from HR.Employees_Data;

2- Select AAA.* from HR.Employees_Data AAA;

Questions #2:

Which of the following SQL Statements is faster and why?

1- SELECT
HR.Employees_Data.*, HR.Departments_Data.*
FROM
HR.Employees_Data INNER JOIN HR.Departments_Data
ON HR. Employees_Data. Dept_NO = HR.Departments_Data. Dept_NO;

2- SELECT
HR. Departments_Data.*, HR. Employees_Data.*
FROM
HR.Departments_Data INNER JOIN HR.Employees_Data
ON HR. Departments_Data.Dept_NO = HR.Employees_Data.Dept_NO;

Thank you

http://www.leaderstechnology.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-26 : 19:21:31

These are stupid questions, tell your instructor I said so.

1. They are equivalent, because I said so.
2. They are equivalent, because I said so.

Aliases don't speed up or slow down queries. The order of the tables in the inner join doesn't matter either.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

serverprogrammer
Starting Member

2 Posts

Posted - 2010-06-27 : 01:41:11

Hello tkizer,

I agree with you,
but it does with ORACLE, that is why I asked both questions,

may I know why?
what is the logic?
remember the question consists of two parts "Which and why"
can you tell me why do you say that?

I just want to know

Thanks for your replay

http://www.leaderstechnology.com

Kristen
Test

22859 Posts

Posted - 2010-06-27 : 14:56:46

Don't use SELECT * - list the column names, and ONLY the columns that the application needs

Using an Alias, or using the Table Name is irrelevant in MS SQL, as is the order of tables in INNER JOINS.

Try it and compare the Query Plans, if they are different come back and tell us.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-27 : 15:40:43

Which school is this homework from?

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-06-27 : 16:12:34

You wanting to avoid sending your kids there?