Bit Data Types

Question

Post reply

Bit Data Types

SQLbeginner

SSC Eights!

Points: 918
More actions
February 24, 2008 at 1:07 pm

#187013

I have a column in a table, that is a bit data type with the values of 1 and 0 (1 for True, 0 for False).
How would I modify the column so that I could have 3 predetermined codes instead of 2?
I would like to have the codes (10,15, 100) that represent percentages of totals.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

percentages, seems you're going to calculate with them, so _must_ be a numerical datatype.

I would store it as decimal (3,2). This way you can avoid the division by 100 .

If you don't like this option, store it as tinyint (smallest integer).

Restrict the values with a constraint.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

SQLbeginner SSC Eights! Points: 918 More actions · Answer 2

Thanks for the quick reply!

I realized however, that I made a mistake in the modification.

I am NOT using percentages, but rather 3 predetermined codes (100 for approved, 50 for pending, and 0 for not approved)

I hope this clarifies it and sorry for the mishap, been working on a project all weekend!!!

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 3

Hi,

You can have DECIMAL or INT datatypes but this is not the right way to do it. The best thing would be declare this column as CHAR(1) to store the status.

Ex: P -- Pending, A -- Approved, K -- Acknowledge etc....

This will give you more flexibility interms of understanding the data and can accodate future requiments.

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 4

In that case, no doubt about it. use the guidance Vijaya Krishna has given.

And as said, restrict it's values with a constraint !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

No... I wouldn't use a CHAR(1) for this... if there are predetermined numeric codes, it's by design... perhaps a requirement for a different app. Having to translate meanings between the server and the app would be confusing, a bit more difficult to verify, and would have the extra overhead of translating everything you put in the table.

I would use the appropriate integer type... for positive whole numbers less than 256, that would be a TINYINT. Same number of bytes as CHAR(1) with lot's more possibilites.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions