We have added an autonumber "RowID" to all of our tables to act as the clustered index (we use GUIDs as the primary key). There is some debate about whether or not this is the proper way to do things. But I just noticed that the "Unique" checkbox for these clustered indexes is NOT checked. Does that impose overhead on SQL Server? Should I be checking that box for all tables?
Thanks.
In general, that's a horrible way to do things. The clustered index is the single most critical factor for performance, and you've just arbitrarily defaulted it with no thought given to overall performance. In fact, the idea that "the clustering key should always be an identity" (it even rhymes!) is the single most performance-damaging myth in table design (well, actually pseudo-design, since no real thought went into it for each table).
Yes, you are imposing additional overhead by not making it unique. Thus, it's slightly less horrible if you specify unique.
What is the best way to script this change?
DROP CREATE?
ALTER?
REBUILD?
And what would that syntax look like?
Sadly, it will be a huge effort (100 tables times 50 databases) so I'd like to script a loop for, at least, each database.
I would use SSMS Table Designer, make the changes there but do NOT press the [SAVE] button. Instead press the "Generate Script" button, save or Copy&Paste the Script somewhere safe and then ABANDON the changes (in the SSMS Designer).
SSMS generally makes good scripts for DDL changes, and if nothing else it will show you a decent, methodical, script for the change you wanted to make, which has some feedback/learning benefits too. No "human error" in the scripts that SSMS generates either 
Ideally your CLUSTERED INDEX should be unique. Obviously if the keys in it are not unique then ... No! ... but if they are (or SHOULD BE!!) UNIQUE then definitely include that attribute. Otherwise SQL will add an extra (internal) KEY column to the clustered index to MAKE each entry unique AND those extra bytes will also be included in every row of every non-clustered index on that table too - so potentially "quite a lot of bloat" !!
I don't think table designer is the place. The actual column is already an "identity" column. It is the index that doesn't have the "unique" checkbox checked.
Since the data is "guaranteed" to be unique, you should specify that that index is UNIQUE. The engine can make use of this information when creating an execution plan. There really no downside to specifying this attribute. I'll echo Scott's comments that the design here is wrong. Why would you ever need two unique surrogate columns (GUID and Identity)?
You can't use GUID as a clustered index since it is random so you create a sequential unique identifier like RowID. I think Scott is suggesting that there are other columns that might make better clustered indexes but not GUID.
Yes. I don't have a real problem with the PK being a guid, as long as it's not the clustered index.
I do have a terrible problem with identity being a "default" clustering key. There should be no such thing as "default" clustering, since it is so critical to performance.
@craigdavidsmith:
I recently has some time off (woohoo!) but I now have your index file and will try to look at it as soon as I can.