Re: Is faster use MIN function or TOP 1 with ORDER BY

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Wayne Snyder (wsnyder_at_computeredservices.com)
Date: 04/21/04 

Date: Wed, 21 Apr 2004 09:41:46 -0400

You should check the query plan for both, but I would suspect that if there
is an index on myfield, that nothing would be any faster than Min(),,,
although the optimizer might be smart enough to treat the top 1 order by
like a min, but i really doubt it. 

--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Checco" <anonymous@discussions.microsoft.com> wrote in message
news:32DB8F40-37A0-4E00-8A21-38E643C530CB@microsoft.com...
> I should select the min value from a big table. Is better to use
> SELECT MIN(MYFIELD) FROM MYTABLE
> or
> SELECT TOP 1 MYFIELD FROM MYTABLE ORDER BY MYFIELD
>
> thanks

Relevant Pages

  • Re: SSIS and Cannot insert the value NULL into column myField
    ... Allan Mitchell ... I have to copy data from one sql server table into another sql server ... column does not allow nulls. ... 'myField' in the destination table which really does not allow NULLs ...
    (microsoft.public.sqlserver.dts)
  • Re: Copying Data from Access to SQL Server
    ... I can export the data from Access, copy it via FTP, then import it into SQL Server. ... That's a good, clean technique, but is extremely slow (about 10x slower than the file copy technique). ... dbo.mytable (myfield) ...
    (comp.databases.ms-access)
  • SSIS and Cannot insert the value NULL into column myField
    ... I have to copy data from one sql server table into another sql server ... I'm using SSIS for the first time... ... column does not allow nulls. ... This sounds really reasonable to me, because there is a field 'myField' ...
    (microsoft.public.sqlserver.dts)
  • Re: Is faster use MIN function or TOP 1 with ORDER BY
    ... In my testing with a table in my data warehouse, the TOP example was slower, ... because it did a sort on the data first, and fetched the first row. ... > SELECT TOP 1 MYFIELD FROM MYTABLE ORDER BY MYFIELD ...
    (microsoft.public.sqlserver.programming)
  • RE: Displaying numbers in normal sequence
    ... reason why it sorted that way is that the field is text type, ... Select MyField From MyTable Order By clng) ... > I know I can renumber the single digit numbers with a zero in front of the ...
    (microsoft.public.access.formscoding)