I would like to store ip addresses in the table and what is the best datatype to be defined for the ipaddress column in the table.
IPAdress varchar(15)
Or IPaddress tinyint
Can i use the ipaddress column as prmarykey for the table..
I would store them as 4 tinyints. You can create a computed column that concatenates them into the "standard" IP look of nn.nnn.nnn.nnn
IPAddress1 tinyint NOT NULL
IPAddress2 tinyint NOT NULL
IPAddress3 tinyint NOT NULL
IPAddress4 tinyint NOT NULL
IPAddress AS CAST(IPAddress1 AS varchar(3)) + '.' + ...
PRIMARY KEY ( IPAddress1, IPAddress2, IPAddress3, IPAddress4 )
Thanks for the response..
IPAddress1 tinyint NOT NULL
IPAddress2 tinyint NOT NULL
IPAddress3 tinyint NOT NULL
IPAddress4 tinyint NOT NULL
IPAddress AS CAST(IPAddress1 AS varchar(3)) + '.' + ...
PRIMARY KEY ( IPAddress1, IPAddress2, IPAddress3, IPAddress4 )
How would be the performance impact for the computed column and select query with order by ipaddress computed column....
there were will like 20 millions records in the table with ip address..
You can put an index on the computed column, so should be fine (well ... as fine as any other indexed column - if the index doesn't "cover" the query then it will still scan, same as any other "regular" column)
I haven't tried it, but I expect your could make the Primary Key NON-clustered, and have a Clustered Index on the IPAddress computed column, if you find that you need that to be the clustered index.
Unless you need to be able to reference the individual parts of the IP Address I'm not sure I would bother to split them into separate columns, I would just store them as a single VARCHAR ...
Wouldn't take much effort to generate 20 million random values for a test if the performance outcome is critical to your APP. I don't think of 20 million rows as being humongous, in terms of the difference between 4xTinyInt or 1xVarchar(15).
Presumably you won't be retrieving them all, sorted, as that's a large query for a user to do anything useful with the data. If you are retrieving them for, say, a publication then presumably that would not be as speed-critical (the transmission time for the data will significantly outweigh the sorting time)
If you want a true numeric sort, you would order by the individual columns, not the combined string. But sorting by the computed column will perform no differently than sorting by a physical column, SQL still has to do the same work to sort them.
God point, I never think of IP numbers in that way ... Doh! ... perhaps because I don't have he responsibility of looking after loads of them