One should define "large".
int columns can hold 2^32 unique values (-2^31 through 2^31) or about 4 billion values. Since most people don't bother with negative integers, that cuts the number of unique values in half to about 2 billion values. int uses 4 bytes of storage.
bigint makes a good compromise between using int or GUIDs, if your only consideration is the number of unique values available. bigint has the benefit of using 8 bytes instead of GUID's 16 bytes. bigint columns can hold -2^63 through 2^63 (-9,223,372,036,854,775,808) to (9,223,372,036,854,775,807).
If you have a single table with more than 9,223,372,036,854,775,807 rows, please contact me, I'd love to understand the use-case.
Chintak makes a good point: NEWID() can lead to fragmentation when used with a clustered index. Two suggestions to consider:
- use a non-clustered index
-use the NEWSEQUENTIALID() function instead of NEWID(). NEWSEQUENTIALID() can only be used in DEFAULT expressions.
commented on Dec 27 2010 10:45PM