Integer field question

Hi
I need to create a table with an integer column that stores 2-byte
numbers in the range 0..65535, and forms part of the table's primary
key. Which data type should I pick?
I'm torn between:
(1) smallint - right size, but this is a signed type. So if a user
does a query for col > 60000, it won't work ..
(2) int - can hold number range correctly, but this is 4 bytes. I
could end up with 2 rows which are unique according to the primary
key, but having the same value for the first 2 bytes. Could fix with
an additional table constraint, but I wonder if there is a neater
way..
Can anyone recommend the best method to do this ?
thanks,
Neil
I don't think there's any simple way to do it. Int with a check constraint
to limit to numbers <= 65535 will solve it databasewise, but perhaps the
check constraint is not necessary if the middle-tier or GUI limits input to
2 bytes anyway?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On 3 Mar, 09:39, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> I don't think there's any simple way to do it. Int with a check constraint
> to limit to numbers <= 65535 will solve it databasewise, but perhaps the
> check constraint is not necessary if the middle-tier or GUI limits input to
> 2 bytes anyway?
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com
OK, thanks for the answer. Yes, the GUI will validate user input.
I will go for the "int" option.