Non-Unique Clustered Indexes Administration
This one is hard to answer without knowing your query patterns, but you will most definitely cause side effects when dropping all clustered indexes and replacing them with something else.

I also don't think you are necessarily correct in assuming that having a non-unique clustered index is sloppy table design or by definition sub-optimal.

When you add a non-unique clustered index, SQL Server will add a 4-byte uniqueifier  to the index to make sure the key is unique if there are duplicate values in the key. Calculating this uniqueifier adds a bit of overhead but nothing too dramatic. This uniqueifier also has to be in the non-clustered index since the index bookmark has to point to a single row.

On the other hand, the example you give could be a good clustered index depending on the situation:


  For example, a table named Transaction will have a Clustered Index
  with the following keys: date, is_deleted. Also, the table will have a
  Non-Clustered Primary key on id column.


This could be a very good design decision if there are a lot of transactions on the same date, and almost every query is a  SELECT * FROM transactiontable WHERE date = &lt;somedate&gt; AND is_deleted=0.

A query like that could hurt badly if it has to use a non-clustered index and resort to key lookups.

It's impossible to say what side effects you will trigger, but you'll surely trigger some, and your reasons for this endeavour might be the wrong ones.

If you insist on doing this, make sure you test it first in a testing environment, and if you do you should be able to tell if this method works and if it will cause fragmentation or not (but that will probably be the least of your worries).