| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-03-30 : 12:46:05
|
| I've got a TinyInt Column that has 3 values: 0, 1, 2I'd like to refer to those values in my stored procedures using mnemonics.Is there any way to setup a global or local variables / constants to represent these values?Sam |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-30 : 12:55:56
|
| Sam --I'm sure there are better ways, but you could use a user-defined function that always returns a constant tinyint to represent the values of 0,1 and 2.i.e, if the values are "OK","notOK","Unknown" you could have 3 functions:OK() -- always returns 0notOK() -- always returns 1Unknown() -- always returns 2Better yet, since you access these functions by the owner, you could have the owner of these functions be a user called "const":const.OK()const.notOK()const.Unknown()That will group them all together nicely.That's all I can really think of ... not sure if that's even a good idea, but it would be one way to do it.Others here will suggest that you just store whatever those tinyints represent in your actual data -- the natural vs. surrogate key debate. That may not be a bad idea as well.That is, instead of a tinyint store a varchar() with values of "ok", "notOK", "unknown" in all of your tables. This will require more storage for your data, of course.Hope this gives some ideas.- Jeff |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-30 : 13:05:35
|
| Thanks for the two new ideas Jeff.I'll probably stay away from the UDF as it's likely to be slow. The varchar is interesting, and I'll have to research the natural vs surrogate.This column actually contains Active, Inactive and Deleted status of users.Frequently referenced. Would you index this column? How?SamEdited by - SamC on 03/30/2003 13:07:17 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-30 : 13:35:37
|
| Why not use a char(1) with values of A, I, and D? This will perform identically to a tinyint as it is also 1 byte, and you don't need to translate anything regarding a numeric value. A, I and D are pretty self-explanatory. It will also index nicely, and if your queries depend on this status, you may want to cluster on it, possibly also adding a second column as the clustering key. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-30 : 14:10:47
|
| Not wantng to be pedantic (well actually ...)Chars will be slightly slower than ints due to needing the collation pages.But it's not anything you would want to worry about.If you want something that is self explanatory then I would go with I,U,D.You could also always join to a lookup table but that's probably not worthwhile.==========================================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. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-03-30 : 15:03:31
|
| Thanks everyone for the feedback. The 1 byte char field sounds good. But I have another problem now.I want to allow searching on any combination of Active, Inactive or Deleted. I'm almost ready to say the values should be 1, 2, 4 so a bitmask compare could be done at search time?WHERE Mycolumn & MyMask <> 0Does this make sense?? Any better ideas?SamFixed the AND operator..Edited by - SamC on 03/30/2003 15:10:08 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-30 : 16:10:17
|
| where flag in ('I','D')this will generate an or statement which can at least use an index.==========================================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. |
|
|
|
sanjnep
Posting Yak Master
191 Posts |
Posted - 2003-03-30 : 23:02:14
|
| Master Smack thank you I highly appreciate you.Thanks................"Heaven's light is our guide"Sanjeevshrestha |
|
|
|
|
Mnemonics for Scalar Values