Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nathans
Aged Yak Warrior
938 Posts |
 Posted - 2005-01-13 : 21:34:07
|
| Hi all,I am trying to develop a creative way to store the combinations of days in one column. Currently, I am using a seven digit number:0101010 - Monday, Wednesday, Friday1000100 - Sunday, Thursday...This works, but c'mon, whats the fun in that? I smell bitwise operators somewhere in this solution?Any ideas?Thanks!- Nathan |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-13 : 21:42:29
|
Are you just looking for creative, or does it have to actually be practical too? Cause I can get VERY creative with encrypted base-64 encoded XML...it's not just for storing MP3's and JPG's in SQL Server... (note: I don't actually do the above, and judging from a lot of the posts on SQL Team I'm probably the only person who doesn't, sadly) |
 |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-01-13 : 22:00:08
|
| well... practical is good too :) It needs to work... and work well I guess. |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-01-13 : 22:01:53
|
| I guess Im looking for the most efficient way to store/retrieve the values |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-13 : 22:25:27
|
| Can you shed some light on what they'll be used for, and what the rest of the table/database looks like? I hesitate to say that storing them all in a single column is a good way to go. Bitmasks always look efficient but they end up more trouble than help usually. |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-01-13 : 22:33:11
|
| Well, this issue came up while building a staging table for automated imports (table stores data_file_path, farmat_file_path, destination_table, etc...) and also stores the days of the week the import should occur. This table is small, at most probably 30 rows. So, storing the M-W-F info in the 7 character string does not seem to be a major performance issue to me. And this table just acts as a simple lookup for a procedure that does the real work.But it just got me thinking, how would I accomplish the same thing on a million + row table? Perhaps one that is updated frequently, schedule days can change, etc. Thats why I said "creative," Im just having some fun with this one :)Thanks for the interest Rob |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-14 : 08:22:36
|
quote:
But it just got me thinking, how would I accomplish the same thing on a million + row table? Perhaps one that is updated frequently, schedule days can change, etc.
This may not make sense, but even under those circumstances I think you'd be better off storing each day on a separate row, vs. using a bitmask. The main reason is that a bitmask can't be indexed (workaround though) and so the query would end up scanning the table...not good for 1 million+. Proper indexing would let you get index seeks all the time, and they'd be instantaneous.As for a workaround, you COULD use a bitmask and create calculated column(s) to provide the day values, and in SQL 2000 you could even index these calculated columns. This is more "creative" and may save some space, but I don't know how well it would actually perform compared to a more standard approach (you may need seven indexed columns instead of one)<edit> Forgot to add earlier, you can always store the schedule info in a 2- or 3-column table, separate from the other data. This would be more efficient to index and query. </edit> |
|
|
|
|
|
|
|
|
Creative way to store date combinations - MWF, etc