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.
| Author |
Topic |
|
datagod
Starting Member
37 Posts |
 Posted - 2006-07-14 : 09:45:50
|
| I have a log table that I store event information in. I use it for debugging purposes, and write a new record to it with information about what is currently happening.One of the columns is EventTime, which stores the datetime of the event. This column also has a non-unique clustered index which greatly helps with date-range searches.I made the index non-unique to allow for records to be written occur within the same 3 millisecond time-frame. In hindsight, I should have created an identity column. I know that SQL 2000 will "uniqueify" this data by adding a 4 byte row identifier (or something along those lines).I want to output this data by its true physical order, using that 4 byte row identifier information if necessary.Currently when I output the data and sort by the datetime column, the results vary. Records with the exact same datetime do not show up in the result sets in the same order that they were entered into the system.Does anyone have any suggestions? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-14 : 10:02:57
|
| Do you mean the rid?That's not easily accessible and wouldn't enable you to sort easily. It would mean going through the phycical page storage structure I think.Think you are stuck with what you have.You can add an identity so that things come out in a consistent order.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-07-14 : 10:55:53
|
| Add an INTEGER IDENTITY column to give you a unique row number. You may also want to make it a clustered Primay Key to store it in order by ID.CODO ERGO SUM |
|
|
|
datagod
Starting Member
37 Posts |
Posted - 2006-07-14 : 10:57:05
|
| Thanks, nr.I am going to add an identity column, but to pre-populate it I am going to traverse the physical page structure and pull out the info I need."DBCC prtipage" seems to produce the info I need. It may take a while, but I will only have to do it once. |
|
|
|
|
|
|
Determine Exact Row number for non-unique data