Problem Solved - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Problem Solved

Author

Topic

WhiteDiablo
Starting Member

4 Posts

Posted - 2011-03-31 : 07:26:14

Hello everyone, I am new in SQL...
So I have a kind of problem about sorted primary key

As we know, a table must have at least one primary key.
Every primary will be sorted automatically
I want to know, Is there any way to make it origin / unsorted ??
If yes, how ??

thanks for your effort... WD

Please Close the thread

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 11:36:47

quote:

Every primary will be sorted automatically

That is not true. A clustered index will mean that the data is physically sorted on disk, however that doesn't mean your query results will be sorted. You must add an ORDER BY if you want a result set to be ordered.

Clustered indexes do not need to be the primary key, although commonly they are. A non-clustered index is also sorted but logically not physically.

If you don't want something sorted, then don't provide an ORDER BY.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WhiteDiablo
Starting Member

4 Posts

Posted - 2011-03-31 : 12:38:59

Thanks for the info about clustered index, but there is a problem... I am even don'/t use the ORDER BY but it still sorted automatically...
When I remove the primary key, The order become to what I want...
But I really need the primary key without ordered alphabetically ( usually ordered ascending ) and I really not using ORDER BY

thanks for your effor... WD

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-31 : 12:41:26

quote:
As we know, a table must have at least one primary key

A table can only have 1 primary key. It can have multiple unique keys, also known as candidate keys.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 12:50:59

quote:
Originally posted by WhiteDiablo

Thanks for the info about clustered index, but there is a problem... I am even don'/t use the ORDER BY but it still sorted automatically...
When I remove the primary key, The order become to what I want...
But I really need the primary key without ordered alphabetically ( usually ordered ascending ) and I really not using ORDER BY

thanks for your effor... WD

It may be sorted according to the clustered index right now, but there are no guarantees that it will remain in that order if you don't specify an ORDER BY.

If you want to get a random sort, then you can use ORDER BY NEWID().

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WhiteDiablo
Starting Member

4 Posts

Posted - 2011-03-31 : 14:29:00

It looks like I still don't get it

How if I show the problems ( I should do it from the first time, sorry )

create database Property

create table ChainSupply
(
ChainId char(4)primary key ,
street varchar (20) not null,
city varchar (10) not null,

)

insert into Branch ( branchNo, street, city)
values ( '31B','321 Freedom', 'Monte')

insert into Branch ( branchNo, street, city)
values ( '42A','412 Local', 'Okhisa')

insert into Branch ( branchNo, street, city)
values ( '12D','476 Fury', 'Hongkong')

SELECT * FROM ChainSupply

The result should be:
branchNo street city
12 D 476 Fury Hongkong
31B 321 Freedom Monte
42A 412 Local Okhisa

But, the result I want is default ( this is important ) like this :
branchNo street city
31B 321 Freedom Monte
42A 412 Local Okhisa
12 D 476 Fury Hongkong

Like the order when I input the VALUES... As addition, I still use SQL Managment Studio
Anyway thanks for your help

Thanks for your effor... WD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-31 : 14:32:28

Do you mean ORDER BY branchNo?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2011-04-01 : 05:03:01

quote:
Originally posted by WhiteDiablo
Like the order when I input the VALUES...

If you want the output to be in the same order as it's entered then you need another field such as a timestamp to indicate when you entered the data, you can then use that in your ORDER BY

for example

create table ChainSupply
(
ChainId char(4)primary key ,
street varchar (20) not null,
city varchar (10) not null,
TimeEntered DateTime default getdate()
)

Then your select would be something like

SELECT ... from ChainSupply ORDER BY TimeEntered

-----------

If debugging is the process of removing software bugs, then programming must be the process of putting them in.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-01 : 05:33:16

quote:
Originally posted by tkizer

A clustered index will mean that the data is physically sorted on disk,

It doesn't even mean that. The clustered index defines the logical order of the table, not the physical.

If the cluster enforced the physical sort order, then the cluster would never have fragmentation (fragmentation measures the difference between logical and physical order)

--
Gail Shaw
SQL Server MVP

WhiteDiablo
Starting Member

4 Posts

Posted - 2011-04-01 : 07:55:51

quote:
Originally posted by elwoos

quote:
Originally posted by WhiteDiablo
Like the order when I input the VALUES...

If you want the output to be in the same order as it's entered then you need another field such as a timestamp to indicate when you entered the data, you can then use that in your ORDER BY

for example

create table ChainSupply
(
ChainId char(4)primary key ,
street varchar (20) not null,
city varchar (10) not null,
TimeEntered DateTime default getdate()
)

Then your select would be something like

SELECT ... from ChainSupply ORDER BY TimeEntered

Yeah, It can be done by that way... But Is there any way without adding the column into table ?? Like using array in another programing code ( java, C, etc ) ?
Anyway, If I am using the addition column ( timestamp ), I would not using SELECT * FROM ChainSupply because addition column will be gather out too... that's exactly the problem

Anyway thank for your effor... WD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-02 : 12:11:01

quote:
Originally posted by WhiteDiablo

Please Close the thread

How lame to edit your post and remove the original problem. Now other people can't learn fro your topic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-02 : 16:10:38

I put the original text back, using a white color. You can highlight if you want to read it.

I'm also locking the thread, as requested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources