ansaurus

Question

SQL Server Best way to create a summary view from multiple views

Answer 1

A:

I am not sure what you mean by insert view.

Perhaps a query like this is what you are looking for?

select Plant, count(*) as Count
from MyTable
group by Plant
order by Plant

I am assuming all of your summary views are accessing the same table, MyTable. If this is not the case, you will likely need a stored procedure with some dynamic SQL to generate what you are looking for...

RedFilter 2010-01-20 22:50:50

Sorry if that wasn't clear. What I mean is that in order to create the final view summary_view_all_plants, the intermidiate views like summary_view_N insert into a temporary table. Then summary_view_all_plants just outputs the contect of that table.

noel_g 2010-01-20 22:54:36

@noel_g: What do you mean the views insert into a temp table.. Are you confusing terminology here?

Chris Lively 2010-01-20 22:56:06

Please post your underlying table schema.

RedFilter 2010-01-20 23:58:11

CREATE TABLE [ttPlantSum] ( [ReportName] [char] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Site] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Count] [numeric](18, 0) NULL ) ON [PRIMARY] GO

noel_g 2010-01-21 00:29:05

I mean for the source data - this looks liek some summary table...I am getting a bit lost in what your process is and why you are doing it...

RedFilter 2010-01-21 01:20:51

for better readability put answer here http://www.pastie.org/788289

noel_g 2010-01-21 15:46:39

Answer 2

+1 A:

Is there a way to create summary_view_all_plants without having to create each individual summary_view_N?

No - you have to define all the tables and/or views utilized by a view.

Layering the views is not an ideal practice. A founding view can change, breaking related children. The queries themselves risk not being optimized for performance.

Inserting values into a temp table means you have to remove existing records or employ logic to add or update accordingly. It also means that this has to be performed periodically to keep in sync. An indexed view, AKA materialized view, might be a potential solution.

OMG Ponies 2010-01-20 23:06:38

We do delete all rows from the temp table each time the summary_view_all_plants is run via a set of stored procedures (there is actually an ASP front end which I have left out of the discussion). Needless to say what you say about brittleness is correct and performance is less than idea. I inherited this and am trying to make the best out of it.I did not know about indexed views, but my googling tells me it may not work since the underlying data changes and some views have non-determanistic columns. Performance is not a big deal as these run once daily on a dedicated server.

noel_g 2010-01-21 00:46:36

@noel_g: Yeah, sadly materialized views are notoriously unaccommodating

OMG Ponies 2010-01-21 00:58:36

+1 for OMG Ponies. You have to define the tables/views, and once you've done it, you can't change any of the underlying tables or views without recompiling the summary view.

Brent Ozar 2010-01-23 14:49:41

Answer 3

A:

Found a good solution to this. I have a table which holds the names of the views which I want to summarize (detail_view_Names). I go thought each Report Name and build a query that performs the summary of each report.

DECLARE @REPORT_ID nvarchar(50),
    @sqlCommand varchar(1000)

DECLARE REPORT_cursor CURSOR
    FOR SELECT Report_Name
    FROM detail_view_Names

OPEN REPORT_cursor
FETCH NEXT FROM REPORT_cursor INTO @REPORT_ID

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @sqlCommand = 'SELECT ''' + @Report_ID + ''' AS ReportName, Plant, COUNT(*) AS [Count] FROM dbo.' + @Report_ID + ' GROUP BY Plant'
    EXEC (@sqlCommand)

    FETCH NEXT FROM REPORT_cursor INTO @REPORT_ID

END
CLOSE REPORT_cursor
DEALLOCATE REPORT_cursor

To add a new report to the summary, just add a new report to the detail_view_Names

noel_g 2010-02-02 01:02:54

ansaurus

tags:

views:

answers:

SQL Server Best way to create a summary view from multiple views

related questions