There is nothing wrong with GUID as keys and clusters in an OLTP system (unless you have a LOT of indexes on the table that suffer from the increased size of the cluster). As a matter of fact, they are much more scalable than IDENTITY columns.
There is a widespread belief that GUID are a great problem in SQL Server - largely, this is quite simply wrong. As a matter of fact, GUID can be significantly more scalable on boxes with more than about 8 cores:
I am sorry, but your developers are right. Worry about other things before you worry about GUID.
Oh, and finally: why do you want a cluster index in the first place? If your concern is an OLTP system with a lot of small indexes, you are likely better off with a heap.
Let us now consider what fragmentation (Which the GUID will introduce) does to your reads. There are three major problems with fragmentation:
- Page splits cost disk I/O
- Half full pages are not as memory efficient as full pages
- It causes pages to be stored out of order, which makes sequential I/O less likely
Since your concern in the question is about scalability, which we can define as "Adding more hardware makes the system go faster" these are the least of your problems. To address each one in turn
Ad 1) If you want scale, then you can afford to buy I/O. Even a cheap Samsung/Intel 512GB SSD (at a few USD/GB) will get you well over 100K IOPS. You wont be consuming that any time soon on a 2 socket system. And if you should run into that, buy one more and you are set
Ad 2) If you do deletes in your table, you will have half full pages anyway. And even if you don't, memory is cheap and for all but the largest OLTP systems - the hot data should fit there. Looking to pack more data into pages is sub-optimising when you are looking for scale.
Ad 3) A table built out of frequently page split, highly fragmented data does random I/O at exactly the same speed that a sequentially filled tables does
With regards to joining, there are two major join types you are likely to see in an OLTP like workload: Hash and loop. Lets look at each in turn:
Hash join: A hash join assumes that the small table is scanned and the bigger one is typically seeked. Small tables are very likely to be in memory, so I/O is not your concern here. We already touched on the fact that seeks are the same cost in fragmented index as in a non fragmented index
Loop join: The outer table will be seeked. Same cost
You may also have lots of bad table scanning going on - but then GUID is again not your concern, proper indexing is.
Now, you may have some legitimate range scans going on (especially when joining on foreign keys) and in this case, the fragmented data is less "packed" as compared to the non fragmented data. But let us consider what joins you will be likely to see in well indexed a 3NF data are:
A join from a table that has a foreign key reference to the primary
key of the table it references
The other way around
Ad 1) In this case, you are going for a single seek to the primary key - joining n to 1. Fragmentation or not, same cost (one seek)
Ad 2) In this case, you are joining to the same key, but may retrieve more than one row (range seek). The join in this case is 1 to n. However, the foreign table you seek, you are seeking for the SAME key, which is just as likely to be on the same page in a fragmented index as on a non fragmented one.
Consider those foreign keys for a moment. Even if you had "perfectly" sequential laid our primary keys - anything pointing to that key will still be non sequential.
Of course, you may be running on a virtual machine in some SAN in some bank who is cheap on money and high on process. Then all this advise will be lost. But if that is your world, scalability is probably not what you are looking for - you are looking for performance and high speed/cost - which are both different things.