October 3, 2007 at 5:55 am
Can anybody please tell me?
What is the basic difference between these 2 query?
Select * from Table;
--
Select ‘x’ from table;
How it works in the memory and performance?
Cheers!
Sandy
--
October 3, 2007 at 6:40 am
If you literally mean the difference between what you have typed, the first selects all columns & all rows from the table in question. Depending on the size of the table, if any of it is in cache, yada-yada, it can be an expensive operation. The second query you have type will return an 'x' for every row in the table.
I did a quick comparison using Adventureworks.HumanResources.Department. They both did a table scan with a couple of logical reads, but the first query also did a couple of physical reads. Obviously it's going to cost more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 3, 2007 at 10:41 pm
Hey Grant Fritchey,
Thanks for your quick reply,
but i think it may be in different in column wise,
i mean to say that if table contains more than one column in Table1
If I execute
"Select * from Table1" command,
it returns all the column where as
"Select 'x' from Table1"
returns only one column with x value,
why so?
Can you please explain to Me.
Cheers!
Sandy.
--
October 4, 2007 at 2:09 am
Sandy
It's doing what you ask it to. SELECT * means "select all columns". SELECT 'x' means "select the value 'x'" (regardless of what data is actually in each row). SELECT x would mean "select the column named x".
As an example, the following code:
[font="Courier New"]CREATE TABLE #MyTable (x char(1), y char(1))
SET NOCOUNT ON
INSERT INTO #MyTable
SELECT 'a', 'b' UNION
SELECT 'c', 'd' UNION
SELECT 'e', 'f'
SELECT * FROM #MyTable
SELECT 'x' FROM #MyTable
SELECT x FROM #MyTable[/font]
would give the following results:
[font="Courier New"]x y
---- ----
a b
c d
e f
----
x
x
x
x
----
a
c
e[/font]
Hope that helps
John
October 4, 2007 at 3:09 am
John,
Thanks a Lot.
Cheers!
Sandy
--
October 4, 2007 at 5:33 am
What John said.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 4, 2007 at 5:41 am
hey Grant Fritchey,
🙂
Thanks 2 both of you,
for helping me in my SQL topic.
Cheers!
Sandy
--
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply