Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 22nd, 2003, 06:03 PM
Registered User
 
Join Date: Jul 2003
Location: Winnebago, IL, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Columns_Updated

Using SQL Server 2000 sp3
I read the chapter in Robert Viera's book regarding Columns_Updated and in the example, it showed the integer value of the Columns_Updated() function as being a rather small integer such as 21, 96, etc.

In using this function in a table with 20 fields, the value became over 1000,000.

Is there a setting in SQL Server that determines the resulting conversion from the varbinary result from Columns_Updated(), is this behavior that you would expect?

TIA
Reply With Quote
  #2 (permalink)  
Old July 22nd, 2003, 06:19 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Columns_Updated() is a bitmask. The least significant bit of the first byte indicates column 1, the most significant bit represents column 8. Thus, a value of 21 indicates that columns 1, 3, and 5 have been updated. A value of 96 means columns 7 and 8 have been updated. If you have more than 8 columns in a table, you'll have more bytes in the function's result. The first byte corresponds to the first 8 columns, the next byte to the next 8, etc.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #3 (permalink)  
Old July 22nd, 2003, 06:24 PM
Registered User
 
Join Date: Jul 2003
Location: Winnebago, IL, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Can you post a short example of how to use the bit mask and retrieve the result as an integer?

I converted the result of Columns_Updated to a bigint to get the values I described. I could not understand the example in Viera's book.

BTW, I am an MCP in SQL Server, Access, VB, W9x, I just have not used bit masks before.

TIA

Reply With Quote
  #4 (permalink)  
Old July 22nd, 2003, 06:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sorry to jump in here Jeff, but I just love bitmasks. I so rarely get to do anything that low level.

To retrieve a single bit from a binary number you need to use a bitmask and to shift the result. You have a binary number, say: 1101010100. You want to see whether column 5 was updated. So you AND it with a bitmask, which is a binary number with all zeros except for the bit you want to get:
Code:
1101010100 &
0000010000
=
0000010000
This isn't much use because you don't get a 1 or 0 back. So you need to right shift the result. Right shifting one place is effectively the same as dividing by 2. So you want to divide this result by 2 4 times (ie divide by 2^5) to shift it right 4 times. The end result is to return that value 1.

Putting this into something more useful and general, try the following code to check to see whether a bit in a number was switched high or low:
Code:
DECLARE @Cols AS VarBinary(20), @Column AS Int, @Result AS bit, @Mask AS int, @MaskRes AS VarBinary(20)

-- set the column mask
SET @Cols = 0xDA        -- 11011010

-- set the column to check for
SET @Column = 5

-- build the mask
SET @Mask = POWER(2, @Column - 1)

-- apply the mask
SET @MaskRes = @Cols & @Mask

-- shift the result
SET @Result = CAST( @MaskRes / POWER(2, @Column - 1) AS bit)

PRINT @Result
I split the code out into multiple steps to make it clear what is happening in each step. You could also simplify the last step by simply casting the result of the bitmask to bit, but it feels a little untidy to me.

A few comments on working with binary numbers in SQL Server.
1. You can't assign a binary number to a variable, or at least I haven't managed to yet. So stick to hex. *Do Not* use decimal. The conversion between hex and binary is relatively simple (4 bits = 1 hex symbol), the conversion between decimal and binary involves a calculator if the numbers are more than about 8 bits or so.
2. The & operator can only take one binary/varbinary parameter.
3. Using windows' calculator in scientific mode. Can be very handy.
4. The least signifigant bit is at the far right, and it also called the zeroth bit. Remember to start counting from zero. You'd notice in the code above when I used POWER I used 2^4, rather than 2^5. This is why.
5. Terminonlogy, Setting a bit = set it to one, clearing a bit = set to zero. high = 1, low = 0 (from digital logic circuit design).


Some generally useful binary rules:
1. To retrieve a bit, use a bitmask of zeroes with a 1 for the position you want to get, AND the mask and the number then right shift.
2. To clear a bit, use a bitmask of ones with a zero for the position you want to clear then AND the number and the mask.
3. To set a bit, use a bitmask of zeroes with a one for the position you want to clear then AND the number and the mask.

regards
David Cameron
Reply With Quote
  #5 (permalink)  
Old July 22nd, 2003, 07:04 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Just realised there was an error in that last post. In the initial example I said:

Quote:
quote:
This isn't much use because you don't get a 1 or 0 back. So you need to right shift the result. Right shifting one place is effectively the same as dividing by 2. So you want to divide this result by 2 4 times (ie divide by 2^5) to shift it right 4 times. The end result is to return that value 1.
This should read:

This isn't much use because you don't get a 1 or 0 back. So you need to right shift the result. Right shifting one place is effectively the same as dividing by 2. So you want to divide this result by 2 4 times (ie divide by 2^4) to shift it right 4 times. The end result is to return that value 1.

regards
David Cameron
Reply With Quote
  #6 (permalink)  
Old July 22nd, 2003, 07:23 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by David Cameron
 Sorry to jump in here Jeff, but I just love bitmasks. I so rarely get to do anything that low level.
Be my guest :D

I've always enjoyed twiddling bits myself. Sometimes I miss those days long ago programming in assembler... nothing like getting down to the bare metal to understand what's really going on. Nobody knows that anymore :(

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #7 (permalink)  
Old July 22nd, 2003, 07:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

For me the memories are a little closer. I just finished my BE (mechatronics, ie robotics) last year and my favorite course of the year (apart from the thesis) was programming assembler and C on an intex 80196KC. You have to worry when the course expectations include "Late night group bonding". That was a lot fun.

Only yesterday I was looking at a book on assembler for x86 with longing eyes.

I ran into an a programmer a few weeks ago who was talking about the ?good? old days when he was given the task of writing a program in machine code. I think that is just a little too hardcore for me. Particularly as he was suggesting that this might be fun to use for a C++ program I'm writing in my spare time.

I love getting down to the nitty gritty. I also think a good understanding at all levels is a great help. If you know about bits, bytes and nibbles, then it changes the way you write your code.

At the moment I'm reading Internetworking with TCP/IP Volume 1, principles, protocols and architectures by Richard Comer. I doubt I'm ever going to write any more sockets code, or if I do it will probably be dropping a winsock control onto a form and using that. I also doubt that I'll ever be involved in setting up networks with more than a dozen machines in them (despite my dreams and plans for the home network), but I do find that it can be very helpful. It also opens your mind to alternative solutions.

Programmers these days are soft. Back when I was a lad, we wrote our code using punch cards and we liked it. Back in my day we had real bugs in computer (http://www.jamesshuggins.com/h/tek1/...mputer_bug.htm).

Saying all that might have a little more weight if I was older than 24.

regards
David Cameron
Reply With Quote
  #8 (permalink)  
Old July 22nd, 2003, 08:56 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by David Cameron
 Programmers these days are soft. Back when I was a lad, we wrote our code using punch cards and we liked it.

You young whippersnapper. Back in the real old days when I cut my computing teeth I really did use punch cards. I recall many a late night at a keypunch machine. I remember one time when I dropped the compiler. Really. All over the floor. My whole life flashed before my eyes as 3500 cards flew all over the room. Quite a chore getting them back together. I learned to always use a card tray after that...

Just to get sort of back on topic, vestiges of those punch cards are still with us today - that why we have files, records and fields, and why people still refer to data that way, even though the concept of records and fields have no place in SQL and in an RDBMS. Drives me nuts when I hear those terms in the SQL context now.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #9 (permalink)  
Old July 22nd, 2003, 10:31 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Jeff Mason
 
Quote:
quote:Originally posted by David Cameron
Quote:
 Programmers these days are soft. Back when I was a lad, we wrote our code using punch cards and we liked it.

You young whippersnapper. Back in the real old days when I cut my computing teeth I really did use punch cards. I recall many a late night at a keypunch machine. I remember one time when I dropped the compiler. Really. All over the floor. My whole life flashed before my eyes as 3500 cards flew all over the room. Quite a chore getting them back together. I learned to always use a card tray after that...
:) That's a great story. Great in retrospect that is. The phrase "dropped the compiler" isn't one you run across all that often.

regards
David Cameron
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MSSQL 2k trigger freak behavior -columns_updated() robsevcik SQL Server 2000 1 November 19th, 2003 07:23 PM



All times are GMT -4. The time now is 12:15 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.