In sql2000 through sql 2008r2 is it ok to use bigint for primary key and foreign keys on all tables in a database?

I want to standardize my database to a consistent primary key because of a move from sql 2000 to sql 2008r2. Is using bigint a good way to go for use with primary keys and foreign keys and can it be auto incremented by 1 when a new row is added? Should I use int instead开发者_StackOverflow of bigint? Also are there any problem with ints or bigints when going form sql2000 to sql2008r2?

BIGINT is probably safest, but for a given entity you should use the largest number you need rather than standardize on the largest number possible across all entities.

For an IDENTITY column on an Employees table, for example, BIGINT just might be overkill. Are you going to have more than 2 billion employees, or write code that tries to create an employee 2 billion times but fails and rolls back every time? Probably SMALLINT is fine (32,000+) unless you're dealing with a very large company.

For other tables where you just don't know how many rows you'll get, again, BIGINT is probably safest. I would hope that you would have some idea based on the entity and the business to be able to lump a table into either a "might get > 2 billion rows" bucket, or a "definitely won't get > 2 billion rows" bucket. For the latter, you can break those down further, if you want. I've seen many systems with smallish lookup tables where the IDENTITY was defined as SMALLINT or even TINYINT - for those sizes I'd rather just standardize on INT. Personal preference, kind of like using CHAR for strings that may vary in size but will always be < 5 characters.

A BIGINT is 8 bytes, an INT is 4 bytes. It's only double the size but this can become a big performance factor on large tables, depending on index structure, number of rows per page, how often you delete, and a host of other factors. Unfortunately the largest tables, where this really does come into play, are also the ones that probably require the possibility that they will get > 2 billion values.

BIGINT is available in SQL Server 2000.

This depends entirely on what your needs are...do you see the identity columns going higher than INT? If so, use BIGINT. Otherwise it makes no difference.