January 27, 2011 at 11:12 pm
Hi All,
Just a simple question, I've a table with id column
[id] [int] IDENTITY(1,1) NOT NULL
so when every time I insert a row, that column will add a id number.
when I just do a simple select query without ORDER BY [column name], I found that Id cloumn is not in order. why it's not in order
does the select query without oredr by will display the rows in the order of actual place in the table?
January 27, 2011 at 11:35 pm
This was removed by the editor as SPAM
January 27, 2011 at 11:45 pm
stewartc-708166 (1/27/2011)
The column will only be logically sorted if said column has a clustered index thereupon.
Nope.
The only way to guarantee order is by using the ORDER BY clause , period.
I blogged about this a little while back.
January 27, 2011 at 11:49 pm
my be you are deleting records
if so the run dbcc command
January 28, 2011 at 12:05 am
Hi,
This is not an issue of SELECT, AutoIncrement ID/IDENTITY nor SQL Server's. I am not going to tell what's behind the scene in SQL databases. In any RDBMS you should not believe the order of rows when ORDER BY clause is not used. This is the fact. That's why the language provides ORDER BY clause and ROWID (even when IDENTITY column is not designed in the table). In other words, you have to explicitly use ORDER BY when you want the rows to be in order as they have entered, like ORDER BY Id. I hope this is a logical issue in RDBMS systems about how they store the data inside the database.
Also I do not know whether you have experienced or not, when I was doing migration from SQL 2000 to 2005 ORDER BY did not work in function objects, unless TOP clause is written (still it's not a subquery). It looks like some questions are unanswered. I am not discouraging you, but wants you to understand the truth.
Happy Programming!
Suresh Kumar Madhavaraju
January 28, 2011 at 12:12 am
This was removed by the editor as SPAM
January 30, 2011 at 3:15 pm
Thank you all answerd the question.
ok, what I understand from the posts, if I want to display the order of the rows as what I enterd, I have to use ORDER BY [column name]
in my case is ORDER BY id, cuz it is a autoincreament,
what if the primary key cloumn is text only?
January 31, 2011 at 4:06 am
You should always include an identity key column in your tables designing. Then it doesn't matter with the type of primary key column.
Let's say you want 4 columns in a table like below:
Employee_Id
Name
Designation
Department
You should create one identity column in addition to the above columns like Auto_Id INT IDENTITY. By using this you can get the results in the order you have entered by including the option ORDER BY Auto_Id.
January 31, 2011 at 2:48 pm
Thank you for the answer. I get it.
January 31, 2011 at 3:16 pm
Suresh Kumar Madhavaraju (1/31/2011)
You should always include an identity key column in your tables designing. Then it doesn't matter with the type of primary key column.Let's say you want 4 columns in a table like below:
Employee_Id
Name
Designation
Department
You should create one identity column in addition to the above columns like Auto_Id INT IDENTITY. By using this you can get the results in the order you have entered by including the option ORDER BY Auto_Id.
Seems like it would be more useful if you used a datetime field instead. That way you can tell the order AND when they were entered. I typically have a SysAddDate and SysModDate field so you can at least tell when the record was created and the last time it was edited.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2011 at 3:44 pm
I can see the value in having a sysmoddate and a sysadddate however, I think in most cases having an auto-incrementing id is probably still a good thing if for no other reason than to be used as the clustering key. Kimberly Tripp goes through a pile of reasons why right here - http://www.sqlskills.com/BLOGS/KIMBERLY/category/Clustering-Key.aspx
and then Michelle Ufford more recently went through it again right here- http://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/
That is not to exclude those two dates, but rather being certain to have an id (or something similar to it) as the clustering key. Obviously that is not true in all cases (as is true with everything SQL Server) but I would wager that this is one of the safer ones you can do nearly anywhere.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply