SET NOCOUNT ON
DECLARE @IdxName VARCHAR(100)
DECLARE @sql NVARCHAR(1000)

SELECT @IdxName = i.name
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id 
  AND i.object_id = ic.object_id
INNER JOIN sys.columns c ON ic.column_id = c.column_id 
  AND ic.object_id = c.object_id
WHERE i.object_id = OBJECT_ID(N'ReservationMain') 
AND c.name = 'ReservationType'

IF @IdxName <> ''
BEGIN
  SET @sql = 'DROP INDEX ' + @IdxName + ' ON ReservationMain'
  EXEC sp_executesql @sql

  UPDATE ReservationMain SET ReservationType = 0 WHERE ISNULL(ReservationType, 0) <= 0
  ALTER TABLE ReservationMain ALTER COLUMN ReservationType TINYINT NOT NULL
  
  SET @sql = 'CREATE NONCLUSTERED INDEX ['+ @IdxName+'] ON [dbo].[ReservationMain]
  (
  	[REFERENCENUMBER] DESC,
  	[HOTELNUMBER] ASC,
  	[ReservationType] ASC,
  	[AllotmentNumber] DESC
  )' 
  EXEC sp_executesql @sql
END