| Author |
Topic |
|
chorofonfilo
Starting Member
40 Posts |
 Posted - 2009-02-08 : 08:38:31
|
Hello all at SqlTeam Forums, thank you very much for the help you guys can give me in advance.I have an application that inserts and update records from a table, the idcolumn is an identity column, but something weird has happened because some of the records have switched their position according to the one they are supposed to have considering their idfield or date field as it is shown in the image:[URL]http://img21.imageshack.us/my.php?image=recordshy5.jpg[/URL] I am trying to figure if after an update the records changed their position after i updated the date without considering the time(on records with id 12 and 7), done for testing purposes. In this case the records set on time (00:00:00) should be together but they arent, the rest of them seem to be well sorted by date tho.Could anyone please help me to figure out whats going on?,thank you very much. Perseverance worths it...:) |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-08 : 09:01:43
|
| Did you Force identity insert with SET identity insert on? Also Which is your Clustered key? |
 |
|
|
chorofonfilo
Starting Member
40 Posts |
Posted - 2009-02-08 : 09:23:12
|
quote:
Originally posted by sodeep
Did you Force identity insert with SET identity insert on? Also Which is your Clustered key?
I havent forced the insertion of identities, and this table doesnt have a clustered key, other than the primary key, which is the column "idficha", and is just a column declared only as identity.The column was defined as it follows:idficha int identity(1,1) And the insertion is performed without SET identity insert on, is just a simple insert command.Thank you very much sodeep.Perseverance worths it...:) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-08 : 21:35:23
|
| what do you mean by chaged there positions? tyhere's no concept of positions in sql table. you cant guarantee that while retrieving,the records will be always in the order of id value unless you give an explicit order by id in the select. |
|
|
|
chorofonfilo
Starting Member
40 Posts |
Posted - 2009-02-09 : 08:39:00
|
quote:
Originally posted by visakh16
what do you mean by chaged there positions? tyhere's no concept of positions in sql table. you cant guarantee that while retrieving,the records will be always in the order of id value unless you give an explicit order by id in the select.
Hi visakh, thanks for your help.You can check the image attached to the first post, the records should be sorted by the id as default, but some of them have switched and i dont get why, since i am not performing any special clause to do it, just select * from table to view them. Please check the image it will explain it all.Thank you again.Perseverance worths it...:) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-09 : 08:44:08
|
THE ONLY GUARANTEED WAY TO HAVE RECORDS SORTED, is to use ORDER BY.You cannot rely on clustered index, nor DISTINCT or GROUP BY keywords. Period.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-09 : 09:34:54
|
quote:
Originally posted by chorofonfilo
quote:
Originally posted by visakh16
what do you mean by chaged there positions? tyhere's no concept of positions in sql table. you cant guarantee that while retrieving,the records will be always in the order of id value unless you give an explicit order by id in the select.
Hi visakh, thanks for your help.You can check the image attached to the first post, the records should be sorted by the id as default, but some of them have switched and i dont get why, since i am not performing any special clause to do it, just select * from table to view them. Please check the image it will explain it all.Thank you again.Perseverance worths it...:)
it will be sorted only if you use order by id in select. there's nothing like default sorting in sql. |
|
|
|
chorofonfilo
Starting Member
40 Posts |
Posted - 2009-02-09 : 09:58:05
|
quote:
Originally posted by visakh16
quote:
Originally posted by chorofonfilo
quote:
Originally posted by visakh16
what do you mean by chaged there positions? tyhere's no concept of positions in sql table. you cant guarantee that while retrieving,the records will be always in the order of id value unless you give an explicit order by id in the select.
Hi visakh, thanks for your help.You can check the image attached to the first post, the records should be sorted by the id as default, but some of them have switched and i dont get why, since i am not performing any special clause to do it, just select * from table to view them. Please check the image it will explain it all.Thank you again.Perseverance worths it...:)
it will be sorted only if you use order by id in select. there's nothing like default sorting in sql.
Thank you guys a lot for the enlightenment.I see this isn't a problem and thats good, but one more question which is the criteria used by the msql engine to display records or it is just totally random once a simple select * from table is performed?.Best Regards.Perseverance worths it...:) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-09 : 10:08:02
|
| its just a random ne...it depends on lots of factors. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-09 : 12:20:41
|
The Query Engine decides which plan to use mostly depending on disk I/O and CPU.The plan resulting in lesser I/O and CPU will be use since the plan is cost-based.The Query Engine favours sequential reads before random reads, and the minimum number of records read from disk at any given time is one page.That's how things are decided and how records are fetched.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
chorofonfilo
Starting Member
40 Posts |
Posted - 2009-02-11 : 14:57:21
|
| Thank you Peso and to all of you guys, is really cool to learn more here.Best Regards.Perseverance worths it...:) |
|
|
|
|
Weird Records Display(They are switched)