Mysql – Is UNIQUE(), INDEX() different from UNIQUE INDEX()

if you consider error, Msg 1505, Level 16, State 1, Line 2 The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MSmerge_contents' and the index name 'uc1SycContents'. The duplicate key value is (7696031, 08703987-557d-e111-9888-e61f13c44f03)...... I am running below Query
select * from msmerge_contents where rowguid='08703987-557d-e111-9888-e61f13c44f03'
and it is returning only 1 row

When you have index corruption problems (ie. keys present in NC index but not in base table or vice-versa) you must be very careful about the SQL you use to validate data. At this moment your data is inconsistent but the query optimizer does not know that and completely trusts your schema, including these incorrect indexes. As such it may optimize your query to use one of the NC indexes that is missing a key and the result will also miss a a key falsely returning no duplicates. To solve this catch-22 situation you need to force the optimizer hand by explicitly requesting an index or another and make sure the projected list of columns can be satisfied by the index you enforced (ie. no *). Assuming uc1SycContents is not the clustered index, try out the following:

select rowguid
from msmerge_contents with INDEX (1)
where rowguid='08703987-557d-e111-9888-e61f13c44f03';

select rowguid
from msmerge_contents with INDEX ([uc1SycContents])
where rowguid='08703987-557d-e111-9888-e61f13c44f03';

This will forcefully check if the rowguid has a duplicate for that guid in the base table clustered index (index id 1) vs. the index uc1SycContents. I expect that the first query returns 2 (or more) rows while the second returns 1.

1. Indexes do introduce some overhead for a database since the data must be inserted and then the index managed. Does it make the table much slower? That depends a lot on how many rows, how many indexes, how you organize the tablespaces, your filesystem, etc. In general, a couple of indexes on a table should not be noticeable on a properly operating database.

2. Yes. Many database engines provide for Range Partitioning based on ideas like this, some qualification that separates older, not-changing-much data to be managed separately. This can be done by moving data to another table, or allowing the engine to manage the data with Range Partitioning. see this for MySQL

3. You should use another client to measure this - something that can give you those statistics. MySQLWorkbench as been suggested. Other tools might include DBVisualizer, SQuirrel, etc.