SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @Major int, @Minor int, @Patch int, @Installed DateTime, @Prereqs int

Set @Major = 2;
Set @Minor = 1;
Set @Patch = 29;

Select @Prereqs = isnull(Count(InstallDate),0)  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch<@Patch

Select @Installed = InstallDate  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch=@Patch

If(@Installed is null AND @Prereqs = @Patch)
	BEGIN
--## Schema Patch ##

EXECUTE sp_executesql
	N'CREATE TABLE dbo.Tmp_cs_PageViews
	(
	URL nvarchar(440) NOT NULL,
	VisitCount int NOT NULL,
	DateTimeStamp datetime NOT NULL
	)  ON [PRIMARY]'

IF EXISTS(SELECT * FROM dbo.cs_PageViews)
	 EXECUTE sp_executesql
		N'INSERT INTO dbo.Tmp_cs_PageViews (URL, VisitCount, DateTimeStamp)
		SELECT CONVERT(nvarchar(440), URL), VisitCount, DateTimeStamp FROM dbo.cs_PageViews (HOLDLOCK TABLOCKX)'

EXECUTE sp_executesql
	N'DROP TABLE dbo.cs_PageViews'

EXECUTE sp_executesql
	N'EXECUTE sp_rename N''dbo.Tmp_cs_PageViews'', N''cs_PageViews'', ''OBJECT'''

EXECUTE sp_executesql
	N'CREATE CLUSTERED INDEX IX_cs_PageViews ON dbo.cs_PageViews
	(
	URL,
	DateTimeStamp
	) ON [PRIMARY]'

--## END Schema Patch ##
Insert into cs_SchemaVersion(Major, Minor, Patch, InstallDate) values (@Major, @Minor, @Patch, GetDate())

Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was applied successfully '

	END
ELSE IF(@Installed is not null)
	BEGIN
Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was already applied on ' + Convert(varchar(50), @Installed)  
	END 
ELSE
	BEGIN
Print 'The patch could not be applied because your current schema is missing previous updates (Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ')' 
	END 

