USE [wartung]
GO
/****** Object:  Table [dbo].[Query]    Script Date: 30.09.2016 13:00:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Query](
	[qu_id] [int] IDENTITY(1,1) NOT NULL,
	[qu_sqlText] [varchar](max) NOT NULL,
	[qu_baselineReads] [int] NOT NULL,
	[qu_performTests] [bit] NOT NULL CONSTRAINT [DF_Query_qu_performTests]  DEFAULT ((1)),
	[qu_note] [varchar](200) NOT NULL,
 CONSTRAINT [qu_pk] PRIMARY KEY CLUSTERED 
(
	[qu_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[QueryTestResult]    Script Date: 30.09.2016 13:00:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QueryTestResult](
	[qtr_id] [int] IDENTITY(1,1) NOT NULL,
	[qtr_qu_id] [int] NOT NULL,
	[qtr_executionDateTime] [datetime] NOT NULL,
	[qtr_logicalReads] [int] NOT NULL,
	[qtr_elapsedTime] [time](7) NOT NULL,
	[qtr_workerTime] [time](7) NOT NULL,
	[qtr_numberOfRows] [int] NOT NULL,
	[qtr_queryPlan] [xml] NULL,
 CONSTRAINT [qtr_pk] PRIMARY KEY CLUSTERED 
(
	[qtr_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  View [dbo].[QueryTests]    Script Date: 30.09.2016 13:00:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW 
	[dbo].[QueryTests]
AS
SELECT  
	qu_note AS [Beschreibung],
	CONVERT(VARCHAR,qtr_executionDateTime,104) + ' ' + CONVERT(VARCHAR,qtr_executionDateTime,108) AS [Startzeitpunkt Test],
	qtr_executionDateTime,
	qtr_numberOfRows AS [Anzahl Datensätze],
	qtr_logicalReads AS [Reads],
	qtr_logicalReads - qu_baselineReads AS [Abweichung Reads zur Baseline],
	CAST(qtr_elapsedTime AS VARCHAR(20)) AS [Verstrichene Zeit],
	CAST(qtr_workerTime AS VARCHAR(20)) AS [CPU Zeit],
	qu_sqlText AS [SQL]
FROM    
			wartung.dbo.QueryTestResult
INNER JOIN	wartung.dbo.Query ON qtr_qu_id = qu_id
;

GO
ALTER TABLE [dbo].[QueryTestResult]  WITH CHECK ADD  CONSTRAINT [qtr_fk_qu] FOREIGN KEY([qtr_qu_id])
REFERENCES [dbo].[Query] ([qu_id])
GO
ALTER TABLE [dbo].[QueryTestResult] CHECK CONSTRAINT [qtr_fk_qu]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Query ID (PK)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Query', @level2type=N'COLUMN',@level2name=N'qu_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Abfragetext' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Query', @level2type=N'COLUMN',@level2name=N'qu_sqlText'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Number of logical reads baseline ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Query', @level2type=N'COLUMN',@level2name=N'qu_baselineReads'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Perform Tests for sqlText (0= No, 1= Yes)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Query', @level2type=N'COLUMN',@level2name=N'qu_performTests'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'QueryTestResult ID (PK)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QueryTestResult', @level2type=N'COLUMN',@level2name=N'qtr_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Query ID (FK auf Query)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QueryTestResult', @level2type=N'COLUMN',@level2name=N'qtr_qu_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ausführungszeitpunkt des Tests' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QueryTestResult', @level2type=N'COLUMN',@level2name=N'qtr_executionDateTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Logische Lesevorgänge der Abfrage' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QueryTestResult', @level2type=N'COLUMN',@level2name=N'qtr_logicalReads'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Verstrichene Zeit' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QueryTestResult', @level2type=N'COLUMN',@level2name=N'qtr_elapsedTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CPU-Zeit' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QueryTestResult', @level2type=N'COLUMN',@level2name=N'qtr_workerTime'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Anzahl zurückgegebener Datensätze' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QueryTestResult', @level2type=N'COLUMN',@level2name=N'qtr_numberOfRows'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ausführungsplan zur Abfrage' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'QueryTestResult', @level2type=N'COLUMN',@level2name=N'qtr_queryPlan'
GO