[dba-SQLServer] Odds and evens

Hi Arthur

I don't think an index on Modulus(StreetNumber) will be of much use as you still will have to scan half the records of StreetName - and how many will these be? A couple of hundreds, max?

/gustav

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Arthur Fuller
Sendt: 25. november 2014 16:27
Til: Discussion concerning MS SQL Server
Emne: [dba-SQLServer] Odds and evens

An app I'm currently sketching out needs to list addresses by street, but with a kicker: I want to two groups per street -- odd and even street numbers. Ultimately, the front end will present a list of streets, and let the user select odd or even numbers, resulting in a list of those odd or even addresses.

One idea I haven't really thought through yet would involve using the modulus to distinguish the two groups, and then maybe build two views, based on whether the modulus is 1 or 0. Then I could just swap the views, depending on the user's choice of odd or even. In this scenario, the index would be StreetName, Modulus(StreetNumber), or something like that

Any alternative suggestions?
--
Arthur