Storing IPs in SQL Server

Question

Post reply

Storing IPs in SQL Server

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 75656 More actions · Answer 1

David.Poole

SSC Guru

Points: 75656

April 9, 2007 at 8:44 am

#699303

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?

LinkedIn Profile

Robert Perona Valued Member Points: 55 More actions · Answer 2

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.

Adrian Hains Ten Centuries Points: 1322 More actions · Answer 3

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