IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects(NOLOCK) 
    INNER JOIN SysColumns(NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'SoldRoomType'
    WHERE SysObjects.Name = 'ROOMBOOKINGSTATUS'
)
begin
  IF EXISTS (SELECT name FROM SysIndexes(NOLOCK) WHERE name = 
            'PK_ROOMBOOKINGSTATUS')
  begin
	ALTER TABLE ROOMBOOKINGSTATUS
	Drop CONSTRAINT [PK_ROOMBOOKINGSTATUS]
  end

  ALTER TABLE ROOMBOOKINGSTATUS ADD SoldRoomType SMALLINT NOT NULL DEFAULT 0

  ALTER TABLE ROOMBOOKINGSTATUS WITH NOCHECK 
  ADD CONSTRAINT [PK_ROOMBOOKINGSTATUS] PRIMARY KEY CLUSTERED 
  (
	[DateStamp] DESC,
	[RoomTypeNumber] ASC,
	[SegmentNumber] ASC,
	[PointOfSaleNumber] ASC,
	[HotelNumber] ASC,
	[SoldRoomType] ASC
  ) ON [PRIMARY]
end
GO
