July 3, 2006 at 4:21 am
Hi,
I am a little confused about converting SQL's binary data type into decimal.
I have the following stored in sysindexes.first = 0x5F0500000100.
I would like to convert this into a decimal number so I can access the contents of the page via dbcc page().
How do I convert this? I have answers to similar questions but am getting mixed results.
Any guidance is much appreciated.
Joe
July 3, 2006 at 6:07 am
To convert from binary(6) to bigint, you can just use:
CAST(0x5F0500000100 as bigint)
You can cast from binary type to an integer type provided that you don't stray into negative numbers, which are stored using the top half of the binary range. Binary(6) can't fall into the range of binary values that represent negative bigints, so this will work OK.
But sysindexes.first contains a pointer value, not a page ID, so you can't use this value for DBCC PAGE(). I'm not sure how you get page IDs - but DBCC IND(dbname|dbid, objname|objid, 1|2) will show you the index pages for a given table. The 3rd arg is 'print options' which affects how detailed the output is. I don't think this gives you page IDs usable with DBCC PAGE() either, though!
Sorry these comments are a bit sketchy but I haven't time to look up the details.
[edit: corrected (numerous!) typos]
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 3, 2006 at 7:00 am
I've worked it out. The value stored in sysindexes.first, sysindexes.root and sysindexes.firstiam can be turned into a file id and page number by splitting the value in two and then reversing it...
For example we have the following value 0xA16F03000100 in sysindexes.first.
Split this into two parts A16F03 (the page id) and 000100 (the file id).
To find the page id:
Reverse the first half - 036FA1 (the page id).
Then convert this to decimal (google: convert 0x036FA1 to decimal) - 225185 (the page id).
Now if you were to use DBCC PAGE command with the page 225185 you'll see your data.
Joe
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply