Script to create the IisLogs table with indexes.

IisLogs.sql

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[IisLog]'
GO
CREATE TABLE [dbo].[IisLog]
(
[EntryTime] [datetime] NOT NULL,
[LogFilename] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RowNumber] [int] NOT NULL,
[SiteName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sIp] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[csMethod] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[csUriStem] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[csUriQuery] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sPort] [int] NULL,
[csUsername] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cIp] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[csUserAgent] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[csReferer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[scStatus] [int] NULL,
[scSubstatus] [int] NULL,
[scWin32Status] [int] NULL,
[scBytes] [int] NULL,
[csBytes] [int] NULL,
[timeTaken] [int] NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_IisLog] on [dbo].[IisLog]'
GO
ALTER TABLE [dbo].[IisLog] ADD CONSTRAINT [PK_IisLog] PRIMARY KEY CLUSTERED  ([EntryTime], [LogFilename], [RowNumber])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_csUriStem] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_csUriStem] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [csUriStem])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_csUriStem_timeTaken] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_csUriStem_timeTaken] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [csUriStem], [timeTaken])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_cIp_csUriStem] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_cIp_csUriStem] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [cIp], [csUriStem])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [csUriStem])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [csUriStem], [scStatus])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus_scSubstatus_timeTaken] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus_scSubstatus_timeTaken] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [csUriStem], [scStatus], [scSubstatus], [timeTaken])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus_timeTaken] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_csUriStem_scStatus_timeTaken] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [csUriStem], [scStatus], [timeTaken])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [IX_EntryTime_LogFilename_RowNumber_siteName_ServerName_csUriStem] on [dbo].[IisLog]'
GO
CREATE NONCLUSTERED INDEX [IX_EntryTime_LogFilename_RowNumber_siteName_ServerName_csUriStem] ON [dbo].[IisLog] ([EntryTime], [LogFilename], [RowNumber], [SiteName], [ServerName], [csUriStem])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO