Part of tuning I want to update the indexes of a production DB to one with better indexes that has been worked on in dev.
Drop all Non Clustered indexes on the database we want to update
select 'DROP index ' + i.name + ' on ' + so.name
from sys.indexes i
inner join sys.objects so ON i.object_id = so.object_id
where i.type_desc = 'nonclustered'
AND so.type = 'u'
However I can’t do this as:
An explicit DROP INDEX is not allowed on index 'xxx'. It is being used for UNIQUE KEY constraint enforcement.
looks like I’ll need to drop the uniqueness eg
alter table tbl_Payroll_SubType drop constraint IX_tbl_Payroll_SubType
Can see UC’s here: (against the actual db – not master)
WHERE type = 'UQ' ;
What Are Clustered Indexes?
..Telling the database to store close values actually close to one another on the disk.
If you wish to quickly retrieve all orders of one particular customer, you may wish to create a clustered index on the "CustomerID" column of the Order table. This way the records with the same CustomerID will be physically stored close to each other on disk (clustered) which speeds up their retrieval.
P.S. The index on CustomerID will obviously be not unique, so you either need to add a second field to "uniquify" the index or let the database handle that for you but that's another story.
Regarding multiple indexes. You can have only one clustered index per table because this defines how the data is physically arranged. If you wish an analogy, imagine a big room with many tables in it. You can either put these tables to form several rows or pull them all together to form a big conference table, but not both ways at the same time. A table can have other indexes, they will then point to the entries in the clustered index which in its turn will finally say where to find the actual data.
Non Clustered Index
With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.
It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table.
Unique Non Clustered Index and Key
When want to enforce uniqueness eg:
CostCentreCode ** on this column must be unique
we put on a nonclustered index which is unique called: UC_CostCentres_CostCentreCode
this appears in Keys too.