How to work around unsupported unsigned integer field types in MS SQL?

Question

Trying to make a MySQL-based application support MS SQL, I ran into the following issue:

I keep MySQL's auto_increment as unsigned integer fields (of various sizes) in order to make use of the full range, as I know there will never be negative values. MS SQL does not support the unsigned attribute on all integer types, so I have to choose between ditching half the value range or creating some workaround.

One very naive approach would be to put some code in the database abstraction code or in a stored procedure that converts between negative values on the db side and values from the larger portion of the unsigned range. This would mess up sorting of course, and also it would not work with the auto-id feature (or would it some way?).

I can't think of a good workaround right now, is there any? Or am I just being fanatic and should simply forget about half the range?

Edit:
@Mike Woodhouse: Yeah, I guess you're right. There's still a voice in my head saying that maybe I could reduce the field's size if I optimize its utilization. But if there's no easy way to do this, it's probably not worth worrying about it.

Answer 1

I would say this.. "How do we normally deal with differences between components?"

Encapsulate what varies..

You need to create an abstraction layer within you data access layer to get it to the point where it doesn't care whether or not the database is MySQL or MS SQL..

Answer 2

When is the problem likely to become a real issue?

Given current growth rates, how soon do you expect signed integer overflow to happen in the MS SQL version?

Be pessimistic.

How long do you expect the application to live?

Do you still think the factor of 2 difference is something you should worry about?

(I have no idea what the answers are, but I think we should be sure that we really have a problem before searching any harder for a solution)

Answer 3

I would recommend using the BIGINT data type as this goes up to 9,223,372,036,854,775,807.

SQL Server does not support signed and unsigned values.