Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting hex to decimal issues Expand / Collapse
Author
Message
Posted Monday, July 3, 2006 4:21 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 28, 2013 5:14 AM
Points: 64, Visits: 129

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

Post #291762
Posted Monday, July 3, 2006 6:07 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:42 PM
Points: 696, Visits: 43

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
Post #291782
Posted Monday, July 3, 2006 7:00 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, September 28, 2013 5:14 AM
Points: 64, Visits: 129

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

 

Post #291790
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse