primary keys have gotten out of sequence

In Enterprise Manager, my primary keys used to be all in sequence. After de
leting a few pk and adding new ones, they are no longer in sequence.
In Enterprise Manager, I would like to be able to view "Return all rows" wit
h the primary keys in numerical order.
What is the best way to accomplish this?
Thank you in advance,
TRTimothy Ross wrote:
> In Enterprise Manager, my primary keys used to be all in sequence. After
deleting a few pk and adding new ones, they are no longer in sequence.
> In Enterprise Manager, I would like to be able to view "Return all rows" w
ith the primary keys in numerical order.
> What is the best way to accomplish this?
> Thank you in advance,
> TR
Tables have no inherent order. The only way to fix the order you see is
to use an ORDER BY clause in your SELECT statements. Enterprise Manager
won't do that for you - you need to write your own query. Preferably
use Query Analyzer instead because it's a much more powerful tool.
You'll need to familiarize yourself with SQL syntax first but that's
not so hard for the basic stuff. For example:
SELECT col1, col2, col3, ...
FROM your_table
ORDER BY col1 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David - Thank you for your quick response.
I understand that I can use query analyzer for sorting. I have actually
used Microsoft Access because this will allow sorting very easily.
I also understand that the primary keys really do not need to be displayed
in any particular order.
I have been told there is a command that will re-order my primary keys
sequentially so that I can view them in Enterprise Manager in the correct
order - it has something to do with INDEX on the column, but I haven't found
any information on how to accomplish his.
CREATE INDEX indexname tablename (column) -- doesn't reorder the primary key
the way that I want it to - I was wondering if there was a way to accomplish
this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1146170512.392795.12240@.i40g2000cwc.googlegroups.com...
> Timothy Ross wrote:
> Tables have no inherent order. The only way to fix the order you see is
> to use an ORDER BY clause in your SELECT statements. Enterprise Manager
> won't do that for you - you need to write your own query. Preferably
> use Query Analyzer instead because it's a much more powerful tool.
> You'll need to familiarize yourself with SQL syntax first but that's
> not so hard for the basic stuff. For example:
> SELECT col1, col2, col3, ...
> FROM your_table
> ORDER BY col1 ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||On Thu, 27 Apr 2006 16:52:04 -0400, "Timothy Ross"
<ross_timothy@.hotmail.com> wrote:
>I have been told there is a command that will re-order my primary keys
>sequentially so that I can view them in Enterprise Manager in the correct
>order - it has something to do with INDEX on the column, but I haven't foun
d
>any information on how to accomplish his.
If you have a clustered index (or primary key) on the table, then the
physical and logical order will be the same.
But even then, a select statement without an order-by does not seem to
guarantee things will display in order. Just why not, I do not really
understand. May have to do with reuse of freed pages and order of
insertion, so the logical and physical *page* order are not the same,
irrespective of the logical and physical *row* orders.
Josh|||> But even then, a select statement without an order-by does not seem to
> guarantee things will display in order. Just why not, I do not really
> understand. May have to do with reuse of freed pages and order of
> insertion, so the logical and physical *page* order are not the same,
> irrespective of the logical and physical *row* orders.
SQL Server is free to return data in any sequence (e.g. most efficient
manner) unless the query contains ORDER BY. For example, there is a
'merry-go-round' scan feature that allows multiple concurrent queries to
'piggyback' on a scan already in progress. Once the later queries retrieve
the last row, the scan resumes at the beginning and continues until the
initially retrieved row is encountered. This could result in rows returned
in an arbitrary order.
It's good to have an understanding of the physical implementation for
performance analysis. However, you can rely only on the result described by
the SQL query.
Hope this helps.
Dan Guzman
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:gb88529oa6vkkdom9dgbtv9gfhhkh16u7e@.
4ax.com...
> On Thu, 27 Apr 2006 16:52:04 -0400, "Timothy Ross"
> <ross_timothy@.hotmail.com> wrote:
> If you have a clustered index (or primary key) on the table, then the
> physical and logical order will be the same.
> But even then, a select statement without an order-by does not seem to
> guarantee things will display in order. Just why not, I do not really
> understand. May have to do with reuse of freed pages and order of
> insertion, so the logical and physical *page* order are not the same,
> irrespective of the logical and physical *row* orders.
> Josh
>