June 10, 2008 at 7:07 am
Wonder if anyone can help me out.
Whats the difference in execution between the following? Or do they all have the same costs?
SELECT count(primary_ID) FROM dbo.tblOfMine
SELECT count(1) FROM dbo.tblOfMine
SELECT count(*) FROM dbo.tblOfMine
Also does anyone know the difference between the following?
SELECT [One] = 1
SELECT 1 'One'
In a SQL Document at work the former is the preferred, but doesn't mention why.
Thanks for your help in advance
Kevin
June 10, 2008 at 7:22 am
You can set the Execution Plan on in SSMS and check the costs yourself to see the differences.
June 10, 2008 at 7:54 am
Thanks Steve,
Just done that and all are the same.
Though on speed seems SELECT count(1) is 0.012 seconds faster than SELECT count(*) when querying 34000000 rows. SELECT(count(primary_ID) is 0.001 seconds slower.
Yeah so no real big deal.
Cheers
Kev
June 10, 2008 at 8:22 am
Kev,
then what is the difference between these two?
SELECT count(*) FROM dbo.tblOfMine
SELECT count('x') FROM dbo.tblOfMine
Cheers!
Sandy.
--
June 10, 2008 at 8:50 am
Hi Sandy,
I did
SELECT getDate()
SELECT count(*) FROM dbo.tblOfMine
SELECT getDate()
SELECT getDate()
SELECT count('x') FROM dbo.tblOfMine
SELECT getDate()
And got the same time differences. Guess what I haven't factored in was that I ran the others seperately, rather than the above which I did in one hit. So the explanation for the tiny apparent differences could be with whatever else the server is doing at that point.
Basically it doesn't matter one bit as each no matter how written execute in the same way, the same times and hence have the same execution plans.
Thank you for helping me.
Kev
June 10, 2008 at 8:57 am
Kevin Lewis (6/10/2008)
Wonder if anyone can help me out.Whats the difference in execution between the following? Or do they all have the same costs?
SELECT count(primary_ID) FROM dbo.tblOfMine
SELECT count(1) FROM dbo.tblOfMine
SELECT count(*) FROM dbo.tblOfMine
Also does anyone know the difference between the following?
SELECT [One] = 1
SELECT 1 'One'
In a SQL Document at work the former is the preferred, but doesn't mention why.
Thanks for your help in advance
Kevin
Keeping in mind that your first COUNT statement may not return the same result as the other two, it's going to have a different cost than the others since it has to check whether the column you're counting on is null or not. You'd have to try it on a big table, and factor in indexing vs no indexing.
As to your second question. The "alias by assignment" notation (your first option) is actually deprecated and will no longer be supported in a future version of SQL Server (I *think* 2008 supports it, but i am not 100% sure on that).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 10, 2008 at 8:59 am
Ok, Good Man..
Cheers!
Sandy.
--
June 10, 2008 at 9:00 am
I had wandered if NULLS had some issue.
Thank you for the clear explanation, and picking up on the alias by assignment question.
🙂
June 10, 2008 at 9:07 am
Hi Sandy,
I did
SELECT getDate()
SELECT count(*) FROM dbo.tblOfMine
SELECT getDate()
SELECT getDate()
SELECT count('x') FROM dbo.tblOfMine
SELECT getDate()
And got the same time differences. Guess what I haven't factored in was that I ran the others seperately, rather than the above which I did in one hit. So the explanation for the tiny apparent differences could be with whatever else the server is doing at that point.
Kev, Can u run the same query for the Table which is having more 100,000 record and Let me know .....
Cheers!
Sandy.
--
June 10, 2008 at 9:24 am
Matt,
As to your second question. The "alias by assignment" notation (your first option) is actually deprecated and will no longer be supported in a future version of SQL Server (I *think* 2008 supports it, but i am not 100% sure on that).
Looking at the Microsoft website, I think this: select [One] = 1 is not being deprecated.
What is being depreciated is this: select 'One' = 1
Here is the link that I looked at: http://technet.microsoft.com/en-us/library/ms143729(SQL.100).aspx
😎
June 10, 2008 at 9:31 am
I am running in one hit through Query Analyser against my table of 34280059 rows:
SELECT getDate()
SELECT count(*) FROM dbo.tblOfMine
SELECT getDate()
--STARTED AT 2008-06-10 16:14:53.233
--COUNTED 34280059
--ENDED AT 2008-06-10 16:14:56.123
--TIME DIFF: 2.890
SELECT getDate()
SELECT count('x') FROM dbo.tblOfMine
SELECT getDate()
--STARTED AT 2008-06-10 16:14:56.123
--COUNTED 34280059
--ENDED AT 2008-06-10 16:14:59.013
--TIME DIFF: 2.890
SELECT getDate()
SELECT count(primary_ID) FROM dbo.tblOfMine
SELECT getDate()
--STARTED AT 2008-06-10 16:14:59.013
--COUNTED 34280059
--ENDED AT 2008-06-10 16:15:01.903
--TIME DIFF: 2.890
SELECT getDate()
SELECT count(1) FROM dbo.tblOfMine
SELECT getDate()
--STARTED AT 2008-06-10 16:15:01.903
--COUNTED 34280059
--ENDED AT 2008-06-10 16:15:04.780
--TIME DIFF: 2.877
Strange.... and here's a re-run
SELECT getDate()
SELECT count(*) FROM dbo.tblOfMine
SELECT getDate()
--STARTED AT 2008-06-10 16:24:22.423
--COUNTED 34280059
--ENDED AT 2008-06-10 16:24:25.297
--TIME DIFF: 2.874
SELECT getDate()
SELECT count('x') FROM dbo.tblOfMine
SELECT getDate()
--STARTED AT 2008-06-10 16:24:25.297
--COUNTED 34280059
--ENDED AT 2008-06-10 16:24:28.187
--TIME DIFF: 2.890
SELECT getDate()
SELECT count(1) FROM dbo.tblOfMine
SELECT getDate()
--STARTED AT 2008-06-10 16:24:28.187
--COUNTED 34280059
--ENDED AT 2008-06-10 16:24:31.080
--TIME DIFF: 2.893
SELECT getDate()
SELECT count(primary_ID) FROM dbo.tblOfMine
SELECT getDate()
--STARTED AT 2008-06-10 16:24:31.080
--COUNTED 34280059
--ENDED AT 2008-06-10 16:24:33.953
--TIME DIFF: 2.873
Guess there's not conclusive info, must be other activity on the server. It's not a production server, and I am the only one on it.
June 10, 2008 at 9:37 am
You may be right - I hadn't seen that specific distinction previously (that looks to have been changed from the last time I read it).
One way or the other - this is one that's in the "some undisclosed future version", so either notation will continue to be supported through 2008 (at least as far as this current version of the document is concerned).
Good catch!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 10, 2008 at 9:40 am
Hi Lynn,
Cheers for the link, read it like you do:
In future you can use:
expression [AS] column_alias
expression [AS] [column_alias]
expression [AS] "column_alias"
expression [AS] 'column_alias'
column_alias = expression
But not:
'string_alias' = expression
I'd never come across the column_alias = expression, hence when I read it it threw me slightly. Guess it's no different than doing any of the above (bar the one that will at some point be depreciated).
Guess as it's not set in concrete it may or may not happen... possibly just best practice not to use 'string_alias' = expression
Once again thank you all
Kev
June 10, 2008 at 9:55 am
Kev, Its ok,
Can you check for this query in your server?
SELECT * FROM dbo.tblOfMine
SELECT 'x' FROM dbo.tblOfMine
Cheers!
Sandy.
--
June 10, 2008 at 10:33 am
Hi Sandy,
Here's the query runs:
SELECT getDate()
SELECT * FROM dbo.tblOfMine
SELECT getDate()
--START TIME 2008-06-10 17:10:16.757
--FINISH TIME 2008-06-10 17:14:59.980
SELECT getDate()
SELECT 'x' FROM dbo.tblOfMine
SELECT getDate()
--START TIME 2008-06-10 17:14:59.980
--FINISH TIME 2008-06-10 17:16:46.810
Regards
Kev
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply