GUID versus Int

Question

If you have a table with at most 10 records and it is referenced in lots of other tables( Via FK relationship) with at least 1,000,000 records in each table .

What will you recommend for type of PK( GUID or tinyint ), if replication is never going to be used? I have bump into this page with followng excerpt:

Guids on SQL Server can perform FASTER than numeric types, once you exceed about 100,000 rows on modern hardware. This is due to the indexing optimizations (cascading of the guid blocks) MS has implemented

is that true that GUID will perform faster in my case?

Thanks in advance ;)

Answer 1

If in doubt, profile. But with 10 rows in a table I doubt you will ever notice any difference.

Answer 2

If you can guarantee that your table is never going to have more than 10 records then I'd stick with ints. The comment seems to only be relevant for very large tables (> 100,000 rows).

Answer 3

Everything is fast for small N. Just use a PK type that is consistent with the rest of your database.

Answer 4

You don't say anything about the row count in the tables referencing your table_with_10_rows...if those tables have more than 100k rows, it might be.

Answer 5

INT will be faster - no question. And use less space, too (ok, with 10 rows, it's not a big difference really).

INT is IMO also a lot easier to use:

SELECT (list of fields)
FROM dbo.MyTable
WHERE ID = 5

is just a whole lot easier to write and memorize than

SELECT (list of fields)
FROM dbo.MyTable
WHERE OID = '8D1E4C8C-54B9-4EB2-B123-FC07E340EC7B'

So I would almost always (in 95%+ of cases) put my vote in for INT rather than GUID.

Also, because GUID's as clustered key in SQL Server (which the Primary Key defaults to) are horrible for performance (read this GUIDs as Primary Keys and/or the clustering key blog post by "The Queen of Indexing", Kimberly Tripp for background info on the topic), I would just try to make it a habit to use INT (possibly: INT IDENTITY) as my primary and clustering key whenever possible.

Marc

Answer 6

Another option you should consider with so few records is using tinyint instead of int. See the arguments for and against here: SOF question