Query to get the last inserted records first

Hello,
I have a table where new rows are inserted on a regulary basis (one by one)
during the day. Several thousands of new rows are inserted every day. This
is an 'History Like' table. It contains a DateRecord column.
The table is queried so that last inserted records must appears first in the
result set. A maximum of n records should be returned. Other constraints may
be applied on other columns.
Is there a way to avoid the SORT (DateRecord DESC) which is very time
consuming ?
TIA.if possbile u can use a identity column and while selecting the records mark
the query are ORDER BY <FIELD NAME> DESC
"Olivier Matrot" <olivier.matrot@.online.nospam> wrote in message
news:eJ7bzMQCFHA.208@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table where new rows are inserted on a regulary basis (one by
one)
> during the day. Several thousands of new rows are inserted every day. This
> is an 'History Like' table. It contains a DateRecord column.
> The table is queried so that last inserted records must appears first in
the
> result set. A maximum of n records should be returned. Other constraints
may
> be applied on other columns.
> Is there a way to avoid the SORT (DateRecord DESC) which is very time
> consuming ?
> TIA.
>|||You can't avoid sorting the records if you want it in a certain order.
According to what you wrote, you'll need to use TOP N in the select
clause and order by clause. If you'll have an index on the DateRecord
column, then the ordering could be fast. Also consider making it
clustered index.
Adi|||> Is there a way to avoid the SORT (DateRecord DESC) which is very time
> consuming ?
As mentioned by the others in this thread, you need to specify ORDER BY
DateRecord DESC to return data in the desired sequence. An index on the
DateRecord column may help performance, depending on the particulars of your
queries/
Hope this helps.
Dan Guzman
SQL Server MVP
"Olivier Matrot" <olivier.matrot@.online.nospam> wrote in message
news:eJ7bzMQCFHA.208@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table where new rows are inserted on a regulary basis (one by
> one) during the day. Several thousands of new rows are inserted every day.
> This is an 'History Like' table. It contains a DateRecord column.
> The table is queried so that last inserted records must appears first in
> the result set. A maximum of n records should be returned. Other
> constraints may be applied on other columns.
> Is there a way to avoid the SORT (DateRecord DESC) which is very time
> consuming ?
> TIA.
>|||One last thing, I would recommend not using an IDENTITY based solution
-- the idea of using a DATETIME data type is how you want to go.
-Alan
Olivier Matrot wrote:
> Hello,
> I have a table where new rows are inserted on a regulary basis (one
by one)
> during the day. Several thousands of new rows are inserted every day.
This
> is an 'History Like' table. It contains a DateRecord column.
> The table is queried so that last inserted records must appears first
in the
> result set. A maximum of n records should be returned. Other
constraints may
> be applied on other columns.
> Is there a way to avoid the SORT (DateRecord DESC) which is very time
> consuming ?
> TIA.|||If you're only interested in retrieving the most recent changes to this
table, I would highly recommend you create a clustered index on the
DateRecord column in descending order:
CREATE CLUSTERED INDEX IXC_DateRecord ON TableName (DateRecord DESC)
A clustered index defines the physical ordering of records in the
table. If this is the primary type of query you'll be executing on this
table, I would recommend clustering on the DateRecord column.
You may want to look at your existing query's execution plan in Query
Analyzer (Tools >> Show Execution Plan or Ctrl+k).
If you're unfamiliar with indexes, here's a good way to think of them
at first (please don't be offended if you're already familiar w/ these
concepts):
Think of the white pages in a printed phone book where everyone is
listed in alphabetical order, along with their address and phone
number. Lets say you have a phone number (but no name) and want to find
out whose it is, you would have to read through every single phone
number until you found it (you'll see these types of operations usually
listed as table scans or clustered index scans in your query's
execution plan).
Searching by name is much faster because you know the phone book is
sorted that way. If you're searching for "Samet Alan A" in the local
listings, you would thumb through it until you find S, look at the
ranges at the tops of the pages and then navigate to Samet Alan A. Once
you're past it, you can be sure there are no more listings for Samet
Alan A.
The white pages are the physical equivalent of a table that would be
clustered on LastName, FirstName. If you frequently had to look up
people by phone number, you would create a non-clustered index on the
phone number column. This would be the equivalent of having another
phone book that had all of the phone numbers sorted, with the LastName
and FirstName (the clustering key) also listed with them.
The non-clustered index would be significantly faster than having to
read through each listing's phone number. If all you needed is the name
associated with the phone number, the non-clustered index would be
adequate. If you needed an address based on the phone number, with the
above indexes, you would first look up the phone number in the
non-clustered index, get the associated name, and then look up the
address based on the person's name in the clustered index (this is
shown as a Bookmark Lookup in your execution plan).

>From what I've seen, when you have a clustered index and you want to
return the TOP [n] records sorted in the same way as the index, I've
always observed that they're returned sorted even without an ORDER BY
clause. However, I would not recommend leaving the sorting off of your
statement as I haven't seen any documentation that states you will
always experience this behavior -- not to mention, you may later choose
how you want to index your table. In which case, your sorting will
definitely change.
I hope I've helped.
-Alan
Olivier Matrot wrote:
> Hello,
> I have a table where new rows are inserted on a regulary basis (one
by one)
> during the day. Several thousands of new rows are inserted every day.
This
> is an 'History Like' table. It contains a DateRecord column.
> The table is queried so that last inserted records must appears first
in the
> result set. A maximum of n records should be returned. Other
constraints may
> be applied on other columns.
> Is there a way to avoid the SORT (DateRecord DESC) which is very time
> consuming ?
> TIA.