There is a DROP INDEX command;

DROP INDEX table_name.index_name

Should work.

It's also possible to disable the index. The pros and cons are discussed here, but the effective point from this article is:

The biggest difference between disabling and dropping an index is whether the metadata and statistics are persisted. If disabled, they are. If dropped, they are not. Make sure you carefully weigh your options before performing either action, and always have a way to recreate the index available.

So in this instance you may want to simply disable the index, collect your stats, then re-enable the index, via:

Disable:

ALTER INDEX index_name ON schema.table_name DISABLE

Enable:

ALTER INDEX index_name ON schema.table_name REBUILD

You can try to use IF EXISTS syntax to check sys.indexes table whether contains the index.

IF EXISTS(
    SELECT * 
    FROM sys.indexes 
    WHERE name='CLX_Enrolment_StudentID_BatchID' AND OBJECT_ID = OBJECT_ID('Enrollment')
)
BEGIN
    DROP INDEX CLX_Enrolment_StudentID_BatchID ON [dbo].[Enrollment]
END
GO

CREATE clustered INDEX CLX_Enrolment_StudentID_BatchID
ON Enrollment(Studentid, BatchId ASC); 

But I think there is an index PK__enrollme__DE799CE1E4649295 on your table from your error message.

Modify

If you want to DROP PK or Clustered index automatically, you can try to use dynamic sql to create the script and use sp_executesql to exec that.

DECLARE @sql NVARCHAR(500),
        @TableName VARCHAR(50) = 'Enrollment',
        @Para NVARCHAR(500)='@TableName VARCHAR(50)'

SELECT @sql = CONCAT('ALTER TABLE ',@TableName,' DROP CONSTRAINT ',name)
FROM sys.indexes 
WHERE OBJECT_ID = OBJECT_ID(@TableName) AND type_desc = 'CLUSTERED' AND is_primary_key = 1
EXEC sp_executesql @sql,@Para,@TableName

SELECT @sql = CONCAT('DROP INDEX ',name,' ON dbo.',@TableName)
FROM sys.indexes 
WHERE OBJECT_ID = OBJECT_ID(@TableName) AND type_desc = 'CLUSTERED'

EXEC sp_executesql @sql,@Para,@TableName

if your table already contains PK you need to use ALTER TABLE .... DROP CONSTRAINT instead of drop index.

Dropping the nonclustered indexes will make converting the heap to a clustered index faster since those nonclustered indexes will not need to be updated.

However, you probably will want to recreate them after the conversion process is complete, so make sure you script the CREATE INDEX scripts prior to dropping them.

Be aware, dropping the nonclustered indexes means any queries that run during the conversion may run very slowly since they may be expecting indexes that are no longer there.

leave it alone

In most OLTP workloads, the primary key is a surrogate key. This isn't a bad thing, especially because someone came along and decided that GUIDs are the best way to uniquely identify various entities in your application. Sure, I get it, it's hard to run out of GUIDs, but that's about where their helpfulness ends.

Even if you were using integer or big integers to uniquely identify entities, there's a strong chance that an identity column would be in use to assign them. As an example, every table in the Stack Overflow database has a clustered primary key on an identity column, and those values are used to identify:

• Users who posted questions
• Users who voted
• Posts that were voted on
• Posts that were commented on
• Comments that will be deleted because comments were a mistake

There's absolutely nothing wrong with surrogate keys, as long as you have sufficient (unique) indexes on the GUID columns that queries will be performing their lookups via.

Having a clustered primary key on an integer or big integer column buys you a few things that using GUIDs does not:

• Narrow data type (4-8 bytes)
• Uniqueness
• Monotonically increasing
• Non-updated data (insert/delete only)

One of the more important parts -- monotonically increasing -- is often not an attribute when GUIDs are used, because they're usually randomly generated. Very few application developers have the foresight to use NEWSEQUENTIALID() to assign GUIDs in the database, and instead generate them in the application, or just by using NEWID() in the database.

For OLTP workloads, having a clustered index (particularly one that meets the criteria I listed above) is far superior to not having one. Tables without clustered indexes (Heaps) in SQL Server can behave in unexpected ways when updated, or deleted from. If you're interested in this, look up forwarded records, and empty allocated pages in this context.

If you were to use non-sequential GUIDs for a clustered index, you would be making random inserts and doing a lot of data page reshuffling, and logging of that reshuffling. It may not matter when a database is small, but it could certainly start to get annoying for larger tables and indexes.

A common scenario for SQL Server is to have a surrogate unique clustered index on an integer or big integer column, and a non-clustered primary key on the GUID column(s) that make up a unique row. That's what you have currently, so don't worry about changing your tables to suit a different arrangement. Instead, just create unique indexes on your GUID columns where queries will use them. One reason for that arrangement is that clustered index key column(s) are automatically added to non-clustered indexes -- as a key when not unique, and as an include when unique -- so the narrow clustered index is more ideal.

In short, leave it alone. You are barking up the wrong tree for optimizing anything whatsoever about your workload.

From MSDN:

When a clustered index is dropped, the data rows that were stored in the leaf level of the clustered index are stored in an unordered table (heap). Dropping a clustered index can take time because in addition to dropping the clustered index, all nonclustered indexes on the table must be rebuilt to replace the clustered index keys with row pointers to the heap. When you drop all indexes on a table, drop the nonclustered indexes first and the clustered index last. That way, no indexes have to be rebuilt.

https://technet.microsoft.com/en-us/library/ms190691(v=sql.105).aspx

As for the nonclustered index drops, they aren't DELETING any data, it's a metadata operation and the index pages are simply deallocated. Think about TRUNCATE versus DELETE, TRUNCATE just deallocates the pages and you're done. Dropping a nonclustered index is very similar, hence why dropping one is lightning fast when you do it (unless you are blocked).

Because dropping of a NCI is already as much online as it gets. Is a metadata only operation. There is not even data deletion, a dropped index rowset is simply deallocated, ie. the same operation as truncate does.

Dropping a clustered index, on the other hand, implies a rebuild and is a size-of-data operation, so it does make sense to have an online alternative.