any order

Hi,
Is there any sort order if we don't use order by? for example,
select * from tablename
what is sort order? each time, I get the same records with the same order,
any mystery?As far as the I know, the order was the same as you add the data.
You can change the order bu add "order by" statement , I think you can get
whole information from BOL|||Wei
I don't think that order depends on insertion. (unless you have clustered
index on this column)
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> As far as the I know, the order was the same as you add the data.
> You can change the order bu add "order by" statement , I think you can get
> whole information from BOL
>|||Hi ,
If you have a clusted index in that table database will be ordered based on
the Index key. So incase if you have a clustered index the
"select * from tablename" will return the result set based on the clustered
index key order.
If you donot have a clusted index the result set will be reurned based on
the way you inserted.
Thanks
Hari
MCDBA
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||records are sorted in ascending order by default.
"John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||Sorry
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
I meant if you don't have a clustered index on the column it depends on
insertion.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> Wei
> I don't think that order depends on insertion. (unless you have clustered
> index on this column)
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > As far as the I know, the order was the same as you add the data.
> > You can change the order bu add "order by" statement , I think you can
get
> > whole information from BOL
> >
> >
>|||The first reply you made me shocked :)|||Incorrect, I'm afraid. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"harsh" <harshalmistry@.hotmail.com> wrote in message
news:%23RaX%23pW6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> records are sorted in ascending order by default.
>
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same order,
> > any mystery?
> >
> >
>|||> If you have a clusted index in that table database will be ordered based on
> the Index key.
Not necessarily. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPL7gkW6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> Hi ,
> If you have a clusted index in that table database will be ordered based on
> the Index key. So incase if you have a clustered index the
> "select * from tablename" will return the result set based on the clustered
> index key order.
> If you donot have a clusted index the result set will be reurned based on
> the way you inserted.
>
> Thanks
> Hari
> MCDBA
> "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same order,
> > any mystery?
> >
> >
>|||> I meant if you don't have a clustered index on the column it depends on
> insertion.
Not correct. I'm afraid. See my other post.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23qCR2tW6DHA.1936@.TK2MSFTNGP12.phx.gbl...
> Sorry
> > I don't think that order depends on insertion. (unless you have clustered
> > index on this column)
> I meant if you don't have a clustered index on the column it depends on
> insertion.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#JxgijW6DHA.3548@.TK2MSFTNGP11.phx.gbl...
> > Wei
> > I don't think that order depends on insertion. (unless you have clustered
> > index on this column)
> >
> >
> >
> > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > news:O9uMAcW6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> > > As far as the I know, the order was the same as you add the data.
> > > You can change the order bu add "order by" statement , I think you can
> get
> > > whole information from BOL
> > >
> > >
> >
> >
>|||A table, is in the relation model, an unordered set of rows. Imagine your rows being notes on
paper-slips and you throw them into a bag. The back is shaken from time to time. Now. try to pull
the paper-slips out of this bag "in order". The term "order" doesn't make sense in the relational
model.
To be more specific, if you don't have order by, the optimizer is free to return the rows in any
order. It will pick the order it finds most cost-effective.
It doesn't have to be in the same sequence as the rows are inserted (read about IAM page etc in the
physical database chapter in Books Online). That is regardless whether you have a clustered index or
not.
It doesn't have to be in the same sequence as the clustered index. The index can be heavy
fragmented, so the optimizer can decide to instead of jumping back and forth on the disk, it will
scan the file in physical order, according to the IAM page.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <spam@.spam.com> wrote in message news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is there any sort order if we don't use order by? for example,
> select * from tablename
> what is sort order? each time, I get the same records with the same order,
> any mystery?
>|||Tibor
I have just finished to read the article, you are absolutely right. Sorry
for giniven incorrect information.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> A table, is in the relation model, an unordered set of rows. Imagine your
rows being notes on
> paper-slips and you throw them into a bag. The back is shaken from time to
time. Now. try to pull
> the paper-slips out of this bag "in order". The term "order" doesn't make
sense in the relational
> model.
> To be more specific, if you don't have order by, the optimizer is free to
return the rows in any
> order. It will pick the order it finds most cost-effective.
> It doesn't have to be in the same sequence as the rows are inserted (read
about IAM page etc in the
> physical database chapter in Books Online). That is regardless whether you
have a clustered index or
> not.
> It doesn't have to be in the same sequence as the clustered index. The
index can be heavy
> fragmented, so the optimizer can decide to instead of jumping back and
forth on the disk, it will
> scan the file in physical order, according to the IAM page.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same
order,
> > any mystery?
> >
> >
>|||Tibor.
Greate Poster, let me learn many thing.
I do not think you are right after you poster, but after read the
information on BOL. Yes you are right.|||>The term "order" doesn't make sense in the relational
> model.
I understand that the order and PHYSICAL placement of the data is not a
concern in the relational model but surely the RM addresses returning
ordered data? Isn't this called sort sets in the RM?
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> A table, is in the relation model, an unordered set of rows. Imagine your
rows being notes on
> paper-slips and you throw them into a bag. The back is shaken from time to
time. Now. try to pull
> the paper-slips out of this bag "in order". The term "order" doesn't make
sense in the relational
> model.
> To be more specific, if you don't have order by, the optimizer is free to
return the rows in any
> order. It will pick the order it finds most cost-effective.
> It doesn't have to be in the same sequence as the rows are inserted (read
about IAM page etc in the
> physical database chapter in Books Online). That is regardless whether you
have a clustered index or
> not.
> It doesn't have to be in the same sequence as the clustered index. The
index can be heavy
> fragmented, so the optimizer can decide to instead of jumping back and
forth on the disk, it will
> scan the file in physical order, according to the IAM page.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "John" <spam@.spam.com> wrote in message
news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > Is there any sort order if we don't use order by? for example,
> >
> > select * from tablename
> >
> > what is sort order? each time, I get the same records with the same
order,
> > any mystery?
> >
> >
>|||As far as I know, RM does not have this concept, as this would mean that RM defines other concepts
than relations. I might be wrong, of course and I welcome (as always :-) ) pointers etc...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eNG0m9X6DHA.2380@.TK2MSFTNGP10.phx.gbl...
> >The term "order" doesn't make sense in the relational
> > model.
> I understand that the order and PHYSICAL placement of the data is not a
> concern in the relational model but surely the RM addresses returning
> ordered data? Isn't this called sort sets in the RM?
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OS1xRJX6DHA.2712@.tk2msftngp13.phx.gbl...
> > A table, is in the relation model, an unordered set of rows. Imagine your
> rows being notes on
> > paper-slips and you throw them into a bag. The back is shaken from time to
> time. Now. try to pull
> > the paper-slips out of this bag "in order". The term "order" doesn't make
> sense in the relational
> > model.
> >
> > To be more specific, if you don't have order by, the optimizer is free to
> return the rows in any
> > order. It will pick the order it finds most cost-effective.
> >
> > It doesn't have to be in the same sequence as the rows are inserted (read
> about IAM page etc in the
> > physical database chapter in Books Online). That is regardless whether you
> have a clustered index or
> > not.
> >
> > It doesn't have to be in the same sequence as the clustered index. The
> index can be heavy
> > fragmented, so the optimizer can decide to instead of jumping back and
> forth on the disk, it will
> > scan the file in physical order, according to the IAM page.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "John" <spam@.spam.com> wrote in message
> news:OkuS1PW6DHA.1636@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > Is there any sort order if we don't use order by? for example,
> > >
> > > select * from tablename
> > >
> > > what is sort order? each time, I get the same records with the same
> order,
> > > any mystery?
> > >
> > >
> >
> >
>