May 15, 2009 at 11:08 am
The SARGable argument, for me, would make me lean towards straight integer or character columns.
May 15, 2009 at 11:24 am
Michael Valentine Jones (5/15/2009)
Why not just create a view in each database with the application ID hard coded in that view:
Create view vAppID as select AppID = 12345
This is actually the same thing as the "constant scan" that I suggested earlier.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 15, 2009 at 11:42 am
Lamprey13 said:
Using a bit mask is a nice and easy solution and since you are talking about so few rows performace should be fine. But, you do realize that:
1. You a limited to 63 Apps (assuming BIGINT).
2. It is not Sargable, so even though it is Numeric data Textual data will be faster since it can take advantage of an index.
big int is, indeed limited to 63 but numeric(18) would be limited to... well, a lot more (heh). and extending to varbinary.. well, our small company is just not going to face that wall for a long time...
I started using the bitmask solution in the 'long ago' times... wanted to avoid a separate column for each app's reference, and avoid a 'duplicate' record for each ref item. At the time I hadn't thought of a cross-reference table. sheesh: now i feel dumb...
Luckily we *are* talking about small sets: 5 to 30 records...
Steve Jones - Editor (5/15/2009)
The SARGable argument, for me, would make me lean towards straight integer or character columns.
Steve:
You are absolutely right. And I guess this kind of relates to your editorial today in a way... old habits/comfortable techniques... i had built in the bitwise approach 6 or so years ago, and hadn't really had a reason to revisit it, and don't really need to now, but, I just got to thinking.... what if?
Cheers from Denver,
Mark
Mark
Just a cog in the wheel.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply