Storing IPs in SQL Server
-
There doesn't seem to be an ANSI standard equivalent of BIT or TINYINT.
BIT fields I can live without because I like to have a specific "Don't know" value rather than using a NULL entry.
What should I be using instead of TINYINT?
-
I get the same execution plans as the article author. This leads me to believe that it is serializing then filtering.
I also built octet accessor properties into my UDT, these get even cleaner plans when queried through.
Note: I in no way extensively tested this. If someone wanted to I would gladly give them my source. It could definitely be optimized as I built it quickly, simply to get a feel for how to buil a UDT.
-
I was thinking about this some more and I would expect that the DB must deserialize each value from the varbinary storage to your UDT representation to check equality. Unless you are setting some .NET attribute (if one exists) that tells the DB that the way you serialize the values provide strict weak ordering, it can't know that e.g. 0xBBBB is a "greater" IP than 0xBBBA. If it doesn't know it has strict weak ordering I don't see how can it walk a b-tree.
I don't really have time to look into it I'm afraid, I just have enough time to wonder about it for a moment and then get back to work
-
In the SqlUserDefinedType attribute you can tell it to use byte ordering, which is what I do.
-
Hey slick, I think your approach is my favorite then. Cool beans!
-
yes that sounds great - has someone an example of this?
regards
audi
-
One question
What happens when they move to IP6????
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply