0

I recently started at a company and working with an existing Microsoft SQL Server database. I am also new to SQL and database programming so please be kind :-) I have developed code in other languages however.

When viewing some of the tables using SQL Server Management Studio, I wondered in if the tables are being populated "properly". When I right-click on any table there are options for "Select Top 1000 Rows" and "Edit Top 200 Rows".

When I click either, the results show me the OLDEST entries not the NEWEST entries, as indicated by the Date column in the table. Granted I can view recent rows with a simple query from this thread SQL Server SELECT LAST N Rows. However, shouldn't the most recent entries (rows) be at the top of the tables? (and thus be shown with SELECT TOP command) Is this an indication that the code which populates the table was not written "properly" or according to good practice? It seems that, with the way the tables are currently being populated, that over time, it will take longer and longer to add new entries and also take longer and longer to get the latest data.

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JoeChiphead
  • 1
  • Imagine you're writing a sentence to a whiteboard. Then you want to add another one. You'd write it below, naturally? Same concept. The magic happens in your SQL statements where you can fetch whatever data you want ( ideally in the most efficient way possible ). – FullStackOfPancakes Nov 14 '19 at 18:21

4 Answers4

2

Welcome to a whole new area of programming to get your head around!

As others have said - no, this is sorta-kinda expected behaviour. In the absence of an "order by" clause, the order in which results are returned is not defined; usually, it's in the order of the clustered index, which in turn is often the order of the primary key, which in turn is often an auto-incrementing integer.

Your concerns about performance are worth studying - an RDBMS uses indexes to speed up retrieval; the presence of absence of an index determines whether the database engine has to inspect all rows to check whether they match the where clause. It's worth spending some time getting familiar with indexing if you're doing a lot of database work.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
1

Not at all. SQL tables represent unordered sets. That means that when you select 1000 arbitrary rows, they can come from anywhere in the table.

In practice, tables are often read from the first page onwards, so it looks like SQL always returns the rows on the first page first. These would tend to be the oldest, but that might not be the case either.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Maybe this answer can help you. According to Martin Smith:

There is no guarantee which two rows you get. It will just be the first two retrieved from the table scan.

The TOP iterator in the execution plan will stop requesting rows once two have been returned.

Likely for a scan of a heap this will be the first two rows in allocation order but this is not guaranteed. For example SQL Server might use the advanced scanning feature which means that your scan will read pages recently read from another concurrent scan.

Community
  • 1
  • 1
joacoleza
  • 775
  • 1
  • 9
  • 26
  • "...once two have been returned." ? Don't you mean once the first 1000 have been returned? – JoeChiphead Nov 14 '19 at 19:06
  • I copied the answer from another question and I didn't want to change anything. The links are included to give credits to the user that wrote the original answer. – joacoleza Nov 14 '19 at 20:52
0

The default ordering (*) in SQL often shows the oldest entries before the newest entries -- however the SQL specification clearly states the ordering is random if not specified by an ORDER BY statement.

As far as speed none of this should make any difference. Databases don't work like sequential files -- they don't need to go over the whole file to get to the "end" they can go to any location in the table instantly

You can read more about random access or direct access files here https://en.wikipedia.org/wiki/Random_access

These kind of files have existed for a long while... basically a fundamental part of Winchester drives (or as we call them now Hard Drives). As op. to prior methods like reel to reel tape or punch cards.

This was in 1953 (https://en.wikipedia.org/wiki/History_of_hard_disk_drives)

(*) By "default ordering" I mean the ordering when there is no "order by" specified.

Hogan
  • 69,564
  • 10
  • 76
  • 117
  • No such thing as "default ordering". You cannot "see" a table without using a query to retrieve those rows. A resultset has no defined order if the query that generated it had no order by clause. The engine is free to retrieve rows in any order it sees fit. – SMor Nov 14 '19 at 18:29
  • @SMor - Not really sure why you are making this distinction, but I put in a footnote to make you happy. – Hogan Nov 14 '19 at 18:46