Query Difference

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Query Difference

Query Difference

Rate Topic

Display Mode

Topic Options

Author

Message

MichaelJasson

Posted Thursday, June 18, 2009 4:05 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, June 03, 2011 4:58 PM
Points: 88, 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.

Post #737973

SQLRNNR

Posted Thursday, June 18, 2009 4:40 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #737980

Steve Jones - SSC Editor

Posted Thursday, June 18, 2009 4:59 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 4:46 PM
Points: 31,433, Visits: 13,745

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

Post #737985

MichaelJasson

Posted Thursday, June 18, 2009 5:25 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, June 03, 2011 4:58 PM
Points: 88, 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.

Post #737991

Glenn Dorling

Posted Thursday, June 18, 2009 6:08 PM

SSCommitted

Group: General Forum Members
Last Login: Monday, May 13, 2013 3:46 PM
Points: 1,545, Visits: 832

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.

Post #737998

SQLRNNR

Posted Thursday, June 18, 2009 6:11 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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).

Post #738000

hi_abhay78

Posted Thursday, June 18, 2009 11:13 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, July 26, 2012 10:06 AM
Points: 267, Visits: 385

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)

Post #738053

Grant Fritchey

Posted Friday, June 19, 2009 7:49 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,381, Visits: 25,172

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #738312

Grant Fritchey

Posted Friday, June 19, 2009 7:52 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 13,381, Visits: 25,172

hi_abhay78 (6/18/2009)

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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #738317

Adi Cohn-120898

Posted Friday, June 19, 2009 8:03 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:50 AM
Points: 2,019, Visits: 4,923

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/

Post #738329

« Prev Topic | Next Topic »

Permissions