This is a multi-part message in MIME format.
--=_NextPart_000_0013_01C66A18.613BDBB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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" = with the primary keys in numerical order.
What is the best way to accomplish this?
Thank you in advance,
TR
--=_NextPart_000_0013_01C66A18.613BDBB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
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" with the primary keys in numerical = order.
What is the best way to accomplish this?
Thank you in advance,
TR
--=_NextPart_000_0013_01C66A18.613BDBB0--Timothy 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" with 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:
>> 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"
>> with 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
> --
>|||In Enterprise Manager, I just went into "Table indexes and properties" --
verified that my primary key was under the "Column name" and the "Order" was
"Ascending". I then checked "Create as CLUSTERED"
My primary key column is now reordered numerically.
TR
"Timothy Ross" <ross_timothy@.hotmail.com> wrote in message
news:u6ZSyxjaGHA.508@.TK2MSFTNGP02.phx.gbl...
> 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:
>> 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"
>> with 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
>> --
>|||Timothy Ross wrote:
> In Enterprise Manager, I just went into "Table indexes and properties" --
> verified that my primary key was under the "Column name" and the "Order" was
> "Ascending". I then checked "Create as CLUSTERED"
> My primary key column is now reordered numerically.
>
You can create a clustered index but all that does is to fix the order
of data as it is stored. This doesn't guarantee that the data will
always be returned in the same order. Although in some cases it may
appear to give that effect you shouldn't rely on it.
--
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 - I agree that if I delete some of my primary keys and add some new
ones that I would have to do this again to re-order them - it will not be
automatic.
TR
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1146174590.856030.154560@.j73g2000cwa.googlegroups.com...
> Timothy Ross wrote:
>> In Enterprise Manager, I just went into "Table indexes and properties" --
>> verified that my primary key was under the "Column name" and the "Order"
>> was
>> "Ascending". I then checked "Create as CLUSTERED"
>> My primary key column is now reordered numerically.
> You can create a clustered index but all that does is to fix the order
> of data as it is stored. This doesn't guarantee that the data will
> always be returned in the same order. Although in some cases it may
> appear to give that effect you shouldn't rely on it.
> --
> 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
> --
>|||Timothy
This is NOT what David is saying.
There is absolutely no guarantee what order the rows from a table will be
returned, with a clustered index or without one. The only way to be sure
that you get the data in the order you want it is to request it to be sorted
with ORDER BY.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Timothy Ross" <ross_timothy@.hotmail.com> wrote in message
news:eeUdtakaGHA.1192@.TK2MSFTNGP04.phx.gbl...
> David - I agree that if I delete some of my primary keys and add some new
> ones that I would have to do this again to re-order them - it will not be
> automatic.
> TR
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1146174590.856030.154560@.j73g2000cwa.googlegroups.com...
>> Timothy Ross wrote:
>> In Enterprise Manager, I just went into "Table indexes and
>> properties" --
>> verified that my primary key was under the "Column name" and the "Order"
>> was
>> "Ascending". I then checked "Create as CLUSTERED"
>> My primary key column is now reordered numerically.
>>
>> You can create a clustered index but all that does is to fix the order
>> of data as it is stored. This doesn't guarantee that the data will
>> always be returned in the same order. Although in some cases it may
>> appear to give that effect you shouldn't rely on it.
>> --
>> 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
>> --
>|||Not sure what you are talking about - I did order the rows -- verified that
my primary key was under the "Column name" and the "Order" was
"Ascending"
This re-ordered my primary key column numerically.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e761cnkaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Timothy
> This is NOT what David is saying.
> There is absolutely no guarantee what order the rows from a table will be
> returned, with a clustered index or without one. The only way to be sure
> that you get the data in the order you want it is to request it to be
> sorted with ORDER BY.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Timothy Ross" <ross_timothy@.hotmail.com> wrote in message
> news:eeUdtakaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> David - I agree that if I delete some of my primary keys and add some new
>> ones that I would have to do this again to re-order them - it will not be
>> automatic.
>> TR
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1146174590.856030.154560@.j73g2000cwa.googlegroups.com...
>> Timothy Ross wrote:
>> In Enterprise Manager, I just went into "Table indexes and
>> properties" --
>> verified that my primary key was under the "Column name" and the
>> "Order" was
>> "Ascending". I then checked "Create as CLUSTERED"
>> My primary key column is now reordered numerically.
>>
>> You can create a clustered index but all that does is to fix the order
>> of data as it is stored. This doesn't guarantee that the data will
>> always be returned in the same order. Although in some cases it may
>> appear to give that effect you shouldn't rely on it.
>> --
>> 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
>> --
>>
>|||I guess I misunderstood what you were talking about. I don't see how you can
have the primary key column NOT in order if you use ORDER BY when retrieving
the data.
But if you're satisfied, that's fine (for now).
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Timothy Ross" <ross_timothy@.hotmail.com> wrote in message
news:eozjgWtaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> Not sure what you are talking about - I did order the rows -- verified
> that my primary key was under the "Column name" and the "Order" was
> "Ascending"
> This re-ordered my primary key column numerically.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e761cnkaGHA.3720@.TK2MSFTNGP03.phx.gbl...
>> Timothy
>> This is NOT what David is saying.
>> There is absolutely no guarantee what order the rows from a table will be
>> returned, with a clustered index or without one. The only way to be sure
>> that you get the data in the order you want it is to request it to be
>> sorted with ORDER BY.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Timothy Ross" <ross_timothy@.hotmail.com> wrote in message
>> news:eeUdtakaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> David - I agree that if I delete some of my primary keys and add some
>> new ones that I would have to do this again to re-order them - it will
>> not be automatic.
>> TR
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:1146174590.856030.154560@.j73g2000cwa.googlegroups.com...
>> Timothy Ross wrote:
>> In Enterprise Manager, I just went into "Table indexes and
>> properties" --
>> verified that my primary key was under the "Column name" and the
>> "Order" was
>> "Ascending". I then checked "Create as CLUSTERED"
>> My primary key column is now reordered numerically.
>>
>> You can create a clustered index but all that does is to fix the order
>> of data as it is stored. This doesn't guarantee that the data will
>> always be returned in the same order. Although in some cases it may
>> appear to give that effect you shouldn't rely on it.
>> --
>> 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 found
>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:
>>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.
> 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
>sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment