SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Difference


Query Difference

Author
Message
MichaelJasson
MichaelJasson
SSC Eights!
SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)

Group: General Forum Members
Points: 860 Visits: 191
Does following query returns the first record from the table?
SELECT TOP 1* FROM TicketActivity

I think NO. The resultant TicketId of following query is different from the ticketId we fetched from first query.
SELECT MIN(TicketId) FROM TicketActivity

The clustered key for this table is @ following columns:
TicketId, ActDtim, PostActName

How both are different? Any heads up!!

-MJ

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)

Group: General Forum Members
Points: 83082 Visits: 18577
Is TicketID an identity field?

What kind of data is in ActDtim?


Since the clustered key is the three columns combined, and if you have datetime as the data type in ActDtim, then it is perceivable that the lowest identity is not listed as the first record in the clustered index. A misaligned insert statement could have inserted into your table with an invalid date that predated the lowest identity value you have in the table and produce the irregular results.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Steve Jones
Steve Jones
SSC Guru
SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)

Group: Administrators
Points: 183314 Visits: 19498
Keep in mind that the order of rows returned is never guarenteed without an ORDER by clause. You should include that if you are looking for the first anything.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
MichaelJasson
MichaelJasson
SSC Eights!
SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)

Group: General Forum Members
Points: 860 Visits: 191
The clustered Key is the primary key only. One of the column is Ticketid and other two columns as per my previous post.

I have executed SELECT TOP 1* FROM TicketActivity at least 10 times but I got the same response. I don't see any role of order by clause here. This fetches data from somewhere everytime we execute this command. What is that criteria when this doesn't fetch the first data from table ie Min(TicketId)?

-MJ

-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
Glenn Dorling
Glenn Dorling
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3796 Visits: 929
Without an ORDER BY clause there is no guarantee that SQL Server will return the same row every time.

As a very simplistic example, say someone has just done a query to select all the rows from the table ordered by a different field. All the data is therefore in memory (if it's a small enough table) but the first row that SQL Server might read in that case is the first row based on the sort order of the earlier query.

If you want the smallest TicketId then you MUST say that, either using a min(TicketId) or an ORDER BY TicketId: you can't assume that you'll always get the same row.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)

Group: General Forum Members
Points: 83082 Visits: 18577
Which of the fields is the identity field? What are the datatypes for the remaining fields in your Key?


When comparing these queries, it is difficult to answer your question without knowing the datatypes we are dealing with. Having a key combined of three different fields is going to play into your query results. Since you said the three fields comprise a clustered key - that indicates to me that your clustered index is based on those fields - this will make a difference in your queries. Since the key is used in the Select top 1 * query and the entire key is not being used when performing a select min(id).



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

hi_abhay78
hi_abhay78
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1925 Visits: 386
SELECT TOP 1* FROM TicketActivity
SELECT MIN(TicketId) FROM TicketActivity
The above 2 queries are different.

But you can also run the min query as below :
select top 1 ticketid from TicketActivity order by ticketid

Check the sub-tree cost of bot the queries and see which one is more beneficial.Most probably the cost would be same but i am confident that in any case the query with orderby clause would not be using Stream Aggregates and should be a better choice .

HTH

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)

Group: General Forum Members
Points: 120820 Visits: 33082
What you've got are an ordering query, TOP, and an aggregate query, MIN. The ordering query must have an ORDER BY statement to work. You can't know what order things will come out of the db in. So, to compare these two, you'd need to write the first one like this:

SELECT TOP 1 * FROM TicketActivity
ORDER BY TicketID ASC



The ORDER BY statement will establish the order of the records returned and the ASC will make them ascend from the lowest to the highest. So this query will return the same value as the other.

BTW, depending on your indexes, this query is likely to perform better than the other. The reason for this is that ordering the values from an index is a pretty inexpensive operation, but aggregating values is usually more expensive. But your mileage may vary, so test within your environment.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)SSC Guru (120K reputation)

Group: General Forum Members
Points: 120820 Visits: 33082
hi_abhay78 (6/18/2009)
SELECT TOP 1* FROM TicketActivity
SELECT MIN(TicketId) FROM TicketActivity
The above 2 queries are different.

But you can also run the min query as below :
select top 1 ticketid from TicketActivity order by ticketid

Check the sub-tree cost of bot the queries and see which one is more beneficial.Most probably the cost would be same but i am confident that in any case the query with orderby clause would not be using Stream Aggregates and should be a better choice .

HTH


Just a note, sub-tree costs are estimated values. They don't accurately represent performance. Better to compare the scans, reads and actual execution times of the two queries to determine which is faster.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Adi Cohn
Adi Cohn
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10695 Visits: 6596
The fact that you always got the same results at the past doesn’t guarantee that you’ll always get the same results at the future. If something would change in the future you’ll might be surprised with different results that you’ll get. This could be due to a service pack or new version of SQL Server or could even be because of modifications in the table’s indexes. Hugo Kornelis had a great post that showed that shows that data won’t always be returned according to the clustered index’s order. You can read that post at http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search