If I drop my clustered PK and add a new one, what order will my rows be in?

When you drop a clustered index the table becomes a heap. Since heaps have a very different physical structure from indexes, the data will have to be copied into the new structure. Heaps have no order whatsoever. When you add back a new clustered index, the data will be copied from the heap into the new index, and the order will be defined by the new clustered key.

If you want to preserve the existing order then all you have to do is assign the new integer ids properly:

ALTER TABLE Table ADD Integer_Id INT;
GO

WITH cte AS (
  SELECT ROW_NUMBER() OVER (ORDER BY Guid_Id) as RowOrderByGuid,
    Guid_Id
  FROM Table)
UPDATE t
  SET t.Integer_Id = c.RowOrderByGuid
FROM Table t
JOIN cte c ON t.Guid_Id = c.Guid_Id;

Now the order of Integer_Ids will match the order of Guids. You can drop the Guid column and add a clustered index on the new Integer column and the physical order of records was preserved.

By definition, a clustered index imposes a physical ordering on actual data pages; so, yes, if you drop a clustered index and create a new one, this will force a physical reordering of data.

In your case, I think it's safe to assume the following will happen:

• The existing clustered index will be dropped, but the actual data on disk is not going to move because of this.
• You will modify the column type (or drop the existing column and create a new one), setting costraints for it to be not null, unique, primary key, identity and autoincrementing (this is vital, or SQL Server won't even let you add it as it wouldn't know what to put into it).
• At this point, the column will be automatically populated by SQL Server. I don't know for sure what will happen here, but I think it will be populated in the order rows are phyically stored in the database. But I'm just guessing about this.
• Problem is, ordering can be quite messy when UIDs are involved; so you don't know how data is actually stored now, and you don't know how it will be stored later; if my guessing about column population is correct, there will not be a huge reordering... but it could happen; and, even if I'm correct, the index building is anyway going to take a while, if the table is large enough.

Bottom line: you will have a huge impact, and you could get rows from an unordered SELECT in the same order you get them now. You will have to try.

A clustered index, by definition, determines the physical order of the data, so when you create the new clustered index, the data will be re-ordered; if it's a large table, plan for that to take a while.

If you create a table with a clustered primary key and then drop the clustered PK the physical order of the data in the table will be undisturbed. However, the physical order of query results are not guaranteed to be the same as the order in the table, so this ordering is fairly meaningless.

If you then add an integer column and create a clustered primary key on that, the table will be rearranged into whatever order the key sorts in. This may or may not be the same physical order as the GUID depending on how the key is assigned. You could explicitly assign it based on the sort order of the GUID key (e.g. using row_number() over the old key ordering), or you could assign it some other way. Unless you take steps to ensure that the ordering is explicitly made the same the physical order or the rows in the table is not guaranteed to drive the ordering of your new key.