December 11, 2003 at 6:58 pm
I appear to have everything working properly except for one problem. I have a tblproperty and tblrental: tblerental is so far in falling in Ascending order using the ID column. Yet tbleproperty has the ID in no particular order at all. The Asc & Des buttons are not highlighted so I cannot fix it. I insert it into a query and it does not stick when I reopen the table. In the Manage Indexes/Keys window it states that ID is Ascending...yet it's not. What gives?
I am below novice level regarding this subject and have somehow fallen into this and am feeling wildly stressed by it all. Everything works fine except for this problem. Any suggestions explained in a very simple manner would be more than greatly appreciated. Thanks,
Barb
Barbara Jensen
December 11, 2003 at 7:39 pm
Barbara,
This is going to sound stupid but...
despite having an index on a table, SQL does not guarantee to return rows in any particular order unless you explicitly say "ORDER BY <columnName> ASC" in the query.
This is even true for clustered indexes (where the rows are physically ordered according to the index). In the majority of cases, when you SELECT * FROM MyTable, SQL will return them in clustered index order, but that's only because it's more convenient for SQL to do so. As the table gets bigger, and especially in multiprocessor servers, you may find that result sets are out of order when you don't explicitly say ORDER BY.
So, what good is the index then? Well, you'll find out as your tables get bigger... They enable efficient processing when you have a WHERE clause (SQL automatically will choose to use the index if it determines that the index will speed things up), and they enable efficient JOINs of tables, and ORDERing/GROUPing of results.
In short, always use ORDER BY to see things in a required order.
If you're looking at tables through Enterprise Manager (Open table...) there's no ORDER BY by default. Click the SQL button, add ORDER BY ID to the end of the displayed query, and then hit the RUN button.
Cheers,
- Mark
Cheers,
- Mark
December 12, 2003 at 2:27 am
Yes, Mark is correct on this.
Because ordering is of less or no importance in a RDBMS, the only reliable way to force SQL Server to order your resultset is to use the ORDER BY clause in a query.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 12, 2003 at 8:15 am
Thanks for both of your replies. I appreciate your explanation Mark of why this type of thing was happening.
This forum will be a great resource for me while I learn. Thanks again for your help.
Barb
Barbara Jensen
Barbara Jensen
December 12, 2003 at 8:17 am
Thanks for both of your replies. I appreciate your explanation Mark of why this type of thing was happening.
This forum will be a great resource for me while I learn. Thanks again for your help.
Barb
Barbara Jensen
Barbara Jensen
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply