SQL Server 2014 :: Query Plan For Partitioned Views Not Running As They Should

Mar 29, 2015

I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:

- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.

To explain wat I'm doing is this:

1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:

SELECT * FROM [server1].[database].[dbo].[table]
UNION ALL SELECT * FROM [server2].[database].[dbo].[table]
UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:

SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'

Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.

View 9 Replies


ADVERTISEMENT

Incorrect Query Plan With Partitioned View On SQL 2000

Jun 19, 2001

I have a partitioned view containing 4 tables (example follows at end)

The query plan generated on a select correctly accesses just one of the tables

The query plan generated on an update always accesses all four of the tables. I thought that it should only access the partition required to satisfy the update. Can anyone please advise whether:
a) Is this is expected behaviour?
b) Is the partitioned view incorrectly configured in some way?
c) Is there is a known bug in this area

Note that the behaviour is the same with SP1 on SQL2000

I would be very grateful for any advice

Thanks

Stefan Bennett

Example follows

--Create the tables and insert the values
CREATE TABLE Sales_West (
Ordernum INT,
total money,
region char(5) check (region = 'West'),
primary key (Ordernum, region)
)
CREATE TABLE Sales_North (
Ordernum INT,
total money,
region char(5) check (region = 'North'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_East (
Ordernum INT,
total money,
region char(5) check (region = 'East'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_South (
Ordernum INT,
total money,
region char(5) check (region = 'South'),
primary key (Ordernum,region)
)
GO

INSERT Sales_West VALUES (16544, 2465, 'West')
INSERT Sales_West VALUES (32123, 4309, 'West')
INSERT Sales_North VALUES (16544, 3229, 'North')
INSERT Sales_North VALUES (26544, 4000, 'North')
INSERT Sales_East VALUES ( 22222, 43332, 'East')
INSERT Sales_East VALUES ( 77777, 10301, 'East')
INSERT Sales_South VALUES (23456, 4320, 'South')
INSERT Sales_South VALUES (16544, 9999, 'South')
GO

--create the view that combines all sales tables
CREATE VIEW Sales_National
AS
SELECT *
FROM Sales_West
UNION ALL
SELECT *
FROM Sales_North
UNION ALL
SELECT *
FROM Sales_East
UNION ALL
SELECT *
FROM Sales_South
GO

--Look at execution plan for this query
-- This correctly only accesses the South partition
SELECT *
FROM sales_national
WHERE region = 'south'

-- Look at execution plan for update
-- This accesses all partitions - Why?
update sales_national
set total = 100
where ordernum = 23456;

View 1 Replies View Related

SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements

Oct 30, 2015

When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.

1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?

<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>

2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?

View 0 Replies View Related

Query Server To Find All Partitioned Tables, Partition Name, Column Used, Partitioned By

Dec 17, 2007

I want to find a way to get partition info for all the tables in all the databases for a server. Showing database name, table name, schema name, partition by (maybe; year, month, day, number, alpha), column used in partition, current active partition, last partition (for date partitions I want to know if the partition goes untill 2007, so I can add 2008)

all I've come up with so far is:






Code Block

SELECT distinct o.name From sys.partitions p
inner join sys.objects o on (o.object_id = p.object_id)
where o.type_desc = 'USER_TABLE'
and p.partition_number > 1

View 3 Replies View Related

SQL SERVER 2000 Partitioned Views Bug

May 12, 2008

Thanks in advance in reading this post ! I'm facing a situation in sql server 2000 sp4 with partitioned views.

I have a partition views that joins about 10 tables, in each table there is a check constraint.

For example, if a exec a select count(*) from VIEW where col1 = '20080101' , it goes for the table that has data for '20080101' .

If I exec a select col1,col2,col3,col4 from VIEW where col1='20080101', it goes to all tables and make an index seek.

I want the beaviour of query 1, beause it is just looking on 1 table and not one the 10.



Thanks in advance !

View 3 Replies View Related

SQL Server 2014 :: Indexes And Views - Manage Date Filtering Within A Query?

May 21, 2014

I have a dynamic SQL query that uses various indexes and views.

When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:

@date is an integer.

begindate is datetime format.

begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))

The above code slows my query performance by at least 400%!

Would it be faster to add a computed column to my table, using the suggested method in the link below?

[URL] ....

Then i could add an indexed view to improve performance, or can this be done another way?

View 9 Replies View Related

SQL Server 2014 :: Query For Listing Views In A Database Involving Tables From Other Databases?

Oct 19, 2015

script to get the list of views in a database, involving tables from other databases?I AM using SQL server 2014

View 2 Replies View Related

Query Plan Re-use On Views?

Apr 25, 2006

Here's the setup:

Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.

A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.

Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.

If I open up the view and re-save it, all is well again for a few days.

What gives?

Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.

Any ideas on what causes this and what to do about it?

View 2 Replies View Related

SQL Server 2008 :: Partitioned Views Table Elimination Not Working

Jul 7, 2015

I have some Partitioned Views and on all queries using a table for the in clause, table elimination isn't happening.

Check Constraint is on the oid column

This works as expected, only goes to 2 tables;
SELECT *
FROM view_oap_all
WHERE oid IN ( '05231416529481', '06201479586431' )

This works as expected, only goes to 2 tables;
SELECT *
FROM view_oap_all
WHERE oid IN ( SELECT oid
FROM owners
WHERE oid IN ( '05231416529481', '06201479586431' ) )

This is checking all tables (headingnames are unique), ive tried this for the last 3 hours on many different tables containing the oid column.

Unless I write the oid as in the above queries it just doesn't work.
SELECT *
FROM view_oap_all
WHERE oid IN ( SELECT oid
FROM owners
WHERE headingname = 'TestSystem' )

View 6 Replies View Related

SQL Server 2014 :: How To Print State Of Running Query To Output

Sep 21, 2015

I want print state of running query to output, because my query need long time to run.But print statement dos not work correctly!

Note: I cannot use "go" in my query!

/* My query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'

[code]...

View 2 Replies View Related

SQL Server Admin 2014 :: Print State Of Running Query To Output

Sep 21, 2015

I want print state of running query to output, because my query need long time to run. But print statement dos not work correctly!!

Note: I cannot use "go" in my query!

/* My Query */
print 'Fetch Data From Table1 is Running...'
insert into @T1
select x,y,z
from Table1
print 'Fetch Data From Table1 Done.'

[Code] .....

View 1 Replies View Related

SQL Server 2014 :: Left Join With A Large Partitioned Table?

Aug 3, 2015

I have a query that has a left join with a large partitioned table. The partitioned table has 10s of millions of records, and each partition has about 100,000 records.

The left join is part of an insert that gets a column from the partitioned table, if the column exists. The query contains the partition ID and all other joined columns are part of a non-clustered index.

Through the profiler, I found that there were millions of reads and the execution plan was giving me a table scan on the partitioned table.

I changed the query to do the insert followed by an update with inner join. That did the trick, but it worries me that SQL Server 2014 behaves differently from 2012 or 2008R2, which can make upgrading very time consuming.

View 3 Replies View Related

SQL Server 2014 :: Indexed View Not Being Used For Partitioned Clustered Column-store Index?

Oct 9, 2015

I am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.

I have created a view with the following code:

ALTER view dbo.FactView
with schemabinding
as
select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost]
, sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value]
, sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2]
, sum(isnull(easy_target_co2,0)) as [s_easy_target_co2]
, sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2]
, sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq]
from dbo.FactConsumptionPart
group by local_date_key, read_type_key, meter_key, unit_key

I then created an index on the view as follows:

create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)

I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. what I might be missing, for the query not to be using the indexed view?

View 1 Replies View Related

Partitioned Views

Aug 23, 2006

Hi,

I've starting to explore the Distributed Partitoned Views, in order to use it in the next project, and I've found the article:
"MS SQL Server Distributed Partitioned Views"
By Don Schlichting

I came across the following problem:
While running sample:
USE test
GO

CREATE VIEW AllAuthors
AS

SELECT *
FROM AuthorsAM,
TEST1.test.dbo.AuthorsNZ

GO

I got the error message:
Server: Msg 4506, Level 16, State 1, Procedure AllAuthors, Line 5
Column names in each view or function must be unique. Column name 'au_lname' in view or function 'AllAuthors' is specified more than once.

Could anyone please explain? Can't i use the same column names in both tables?

Regards,
Yifat

View 3 Replies View Related

Partitioned Views

Aug 17, 2001

I would like to break up a very large table into about ten smaller ones. With partitioning to be efficient the columns in the check constraint need to be used when accessing the view. The problem is the table has a composite primary key made up of LocationID/ProductID. With another composite index on ProductID/LocationID. This is accessed both ways from our applications.

I would like to partition the table by LocationID. But then when called by ProductID a scan of all tables in the view would have to be done.

In Oracle there is something called a global index that would solve this. Is there anything similar in SQL Server or does anybody have a work around?

Thanks,

Rob

View 2 Replies View Related

Obtain The Query Plan Of A Running Process

Sep 21, 2006

Hi,Is there a way to findout the query plan of the executing process usingthe SPID/KPID information.Thanks in advance,Thyagu.D

View 1 Replies View Related

Maintaining Partitioned Views

Jul 20, 2005

Hello,I have a large set of data that I have set up as a partitioned view.The view is partitioned by a datetime column and the individual tableseach represent one month's worth of data. I need to keep at least twoyear's worth of data at all times, but after two years I can archivethe data. A sample of the code used is below. It is simplified forspace reasons.My question is, how do other people maintain the database in this typeof scenario? I could create all of the tables necessary for the nextyear and then go through that at the end of each year (archive tablesover two years, add new tables, and change the view), but I was alsothinking that I might be able to write a stored procedure that runsonce a month and does all three of those tasks automatically. It seemslike a lot of dynamic SQL code though for something like that.Alternatively, I could write VB code to handle it in a DTS package.So, my question again is, how are others doing it? Any suggestions?Thanks!-Tom.CREATE TABLE [dbo].[Station_Events_200401] ([event_time] [datetime] NOT NULL ,[another_column] [char] (8) NOT NULL )GOCREATE TABLE [dbo].[Station_Events_200402] ([event_time] [datetime] NOT NULL ,[another_column] [char] (8) NOT NULL )GOCREATE VIEW Station_EventsASSELECT event_time,another_columnFROM Station_Events_200401UNION ALLSELECT event_time,another_columnFROM Station_Events_200402GO

View 3 Replies View Related

Distributed Partitioned Views

Jul 14, 2006

Hi everyone,
I have some doubts about distributed partitioned views.
When we create a distributed partitioned view whcih include three server, do we have tocreate this same distributed partitioned view in that three server in order to make each server to see adn especially modify it ?

Thanks

View 1 Replies View Related

Delete Statements In Partitioned Views

Dec 10, 2002

Hi, I am using sql2000 ent edition. I have a partitioned view based on 8 tables. My selects and inserts are fine. But, when I run a delete on the view based on a query on the paritioned column, I get a "Transaction (Process ID 149) was deadlocked and has been chosen as a victim".
I looked at the query plan and it was showing a parallel query on all the underlying tables. So, I put the Option(maxdop 1), using only one processor and the delete worked fine.

Does anybody know why? is parallel query create deadlocks? is there any known problems with deletes on partitioned views?
same question for updates. I think I have the same problem for updates.

Any help will be useful.
thanks!!

View 1 Replies View Related

Books On Lies - Partitioned Views

May 8, 2007

From BOL...

CHECK constraints are not needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints, the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition.

Then why am I getting index scans on my partitioning column on tables that fail the search value based on their check constraint?

Not looking for an answer because I know the query optimizer is a fickle b*tch and I did not post any code, but I needed to rant.

View 10 Replies View Related

Very Urgent : Bcp Or Dts Data To Partitioned Views

Jan 1, 2004

someone please tell me if i can bcp or dts data in to partition views
it is failing with the error the bulk operation does not support this

View 14 Replies View Related

T-SQL (SS2K8) :: Local Partitioned Views

May 13, 2014

SQL 2008R2, Standard on Windows 2008R2 Enterprise.I have implemented a set of local partitioned views to facilitate multi-threading in one of our applications.Testing with SQL and the insert, update, Delete all work fine. However the inserts from SSIS are Bulk and then fail.According to all I have read I should be able to add "instead of Trigger" for the insert and it should work fine. URL....

I have created instead of triggers for insert and delete and again they work fine when I test them with T-SQL but fail in the SSIS package. The error message is that the view is not updateable.

View 0 Replies View Related

256 Table Limit For Partitioned Views

Aug 8, 2005

I have a partitioned view sitting over several tables and I'm slowlyapproaching the 256 number. Can anybody confirm if there is such alimit for the maximum number of tables that a partitioned view canhold?If this is true, does anybody have any suggestions or ideas to workaround this max limit?TIA!

View 4 Replies View Related

Problems With Partitioned Views And Pruning

Jul 20, 2005

/*problem: Trying to get partitioned views to "prune" unneededpartitions fromselect statements against the partitioned view. There are 5partitionedtables. Each with a check constraint based on a range of formula_idcolumn.Test: Run this script to create the 5 partitioned tables and thepartitioned view. Thenrun the explain plans on the select statements at the end of thescript and see that wecan only prune if we give a seemingly superfluous is not nullcriteria in addition tothe formula_id.Ideal: We want to only have to use the formula_id in the selectstatement to prune.*//*note: you may get errors on the drops first time run*/drop table dbo.cs_working_e2goCREATE TABLE dbo.cs_working_e2 (formula_id int NOT NULLCONSTRAINT formula_id_e14CHECK (formula_id between 1and 1000),submission_id int NOT NULL,node_id int NOT NULL,reference_year smallint NOT NULL,observation_period datetime NOT NULL,authority_flag tinyint NOT NULLCONSTRAINT ONE_DEFAULT3436DEFAULT 1CONSTRAINT Binary_flag_rule667CHECK (authority_flag IN(0, 1)),interpolated_flag tinyint NOT NULLCONSTRAINT ZERO_DEFAULT6926DEFAULT 0CONSTRAINT Binary_flag_rule668CHECK (interpolated_flag IN(0, 1)),observation_value decimal_datatype NOT NULL,time_created smalldatetime NOT NULLCONSTRAINTCURRENT_DATE_DEFAULT1807DEFAULT getdate(),CONSTRAINT XPKcs_working_e2PRIMARY KEY NONCLUSTERED (formula_id, submission_id,node_id, reference_year, observation_period)--ON "INDEXES")--ON "WORKING"goCREATE UNIQUE CLUSTERED INDEX XAK1cs_working_e2 ON dbo.cs_working_e2(submission_id ASC,formula_id ASC,node_id ASC,authority_flag ASC,observation_period ASC,reference_year ASC,observation_value ASC)goCREATE INDEX XIE1cs_working_e2 ON dbo.cs_working_e2(node_id ASC,authority_flag ASC,formula_id ASC,observation_period ASC,reference_year ASC,observation_value ASC)--ON "INDEXES"godrop table dbo.cs_working_indexes2goCREATE TABLE dbo.cs_working_indexes2 (formula_id int NOT NULLCONSTRAINT formula_id_indexes14CHECK (formula_id between7001 and 9000),submission_id int NOT NULL,node_id int NOT NULL,reference_year smallint NOT NULL,observation_period datetime NOT NULL,authority_flag tinyint NOT NULLCONSTRAINT ONE_DEFAULT3437DEFAULT 1CONSTRAINT Binary_flag_rule669CHECK (authority_flag IN(0, 1)),observation_value decimal_datatype NOT NULL,interpolated_flag tinyint NOT NULLCONSTRAINT ZERO_DEFAULT6927DEFAULT 0CONSTRAINT Binary_flag_rule670CHECK (interpolated_flag IN(0, 1)),time_created smalldatetime NOT NULLCONSTRAINTCURRENT_DATE_DEFAULT1808DEFAULT getdate(),CONSTRAINT XPKcs_working_indexes2PRIMARY KEY NONCLUSTERED (formula_id, submission_id,node_id, reference_year, observation_period)--ON "INDEXES")--ON "WORKING"goCREATE UNIQUE CLUSTERED INDEX XAK1cs_working_indexes2 ONdbo.cs_working_indexes2(submission_id ASC,formula_id ASC,node_id ASC,authority_flag ASC,observation_period ASC,reference_year ASC,observation_value ASC)goCREATE INDEX XIE1cs_working_indexes2 ON dbo.cs_working_indexes2(node_id ASC,authority_flag ASC,formula_id ASC,observation_period ASC,reference_year ASC,observation_value ASC)--ON "INDEXES"godrop table dbo.cs_working_other2goCREATE TABLE dbo.cs_working_other2 (formula_id int NOT NULLCONSTRAINT formula_id_other14CHECK (formula_id >= 9001),submission_id int NOT NULL,node_id int NOT NULL,reference_year smallint NOT NULL,observation_period datetime NOT NULL,authority_flag tinyint NOT NULLCONSTRAINT ONE_DEFAULT3438DEFAULT 1CONSTRAINT Binary_flag_rule671CHECK (authority_flag IN(0, 1)),observation_value decimal_datatype NOT NULL,interpolated_flag tinyint NOT NULLCONSTRAINT ZERO_DEFAULT6928DEFAULT 0CONSTRAINT Binary_flag_rule672CHECK (interpolated_flag IN(0, 1)),time_created smalldatetime NOT NULLCONSTRAINTCURRENT_DATE_DEFAULT1809DEFAULT getdate(),CONSTRAINT XPKcs_working_other2PRIMARY KEY NONCLUSTERED (formula_id, submission_id,node_id, reference_year, observation_period)--ON "INDEXES")--ON "WORKING"goCREATE UNIQUE CLUSTERED INDEX XAK1cs_working_other2 ONdbo.cs_working_other2(submission_id ASC,formula_id ASC,node_id ASC,authority_flag ASC,observation_period ASC,reference_year ASC,observation_value ASC)goCREATE INDEX XIE1cs_working_other2 ON dbo.cs_working_other2(node_id ASC,authority_flag ASC,formula_id ASC,observation_period ASC,reference_year ASC,observation_value ASC)--ON "INDEXES"godrop table dbo.cs_working_p1q12goCREATE TABLE dbo.cs_working_p1q12 (formula_id int NOT NULLCONSTRAINT formula_id_p1q114CHECK (formula_id between3001 and 7000),submission_id int NOT NULL,node_id int NOT NULL,reference_year smallint NOT NULL,observation_period datetime NOT NULL,authority_flag tinyint NOT NULLCONSTRAINT ONE_DEFAULT3439DEFAULT 1CONSTRAINT Binary_flag_rule673CHECK (authority_flag IN(0, 1)),interpolated_flag tinyint NOT NULLCONSTRAINT ZERO_DEFAULT6929DEFAULT 0CONSTRAINT Binary_flag_rule674CHECK (interpolated_flag IN(0, 1)),observation_value decimal_datatype NOT NULL,time_created smalldatetime NOT NULLCONSTRAINTCURRENT_DATE_DEFAULT1810DEFAULT getdate(),CONSTRAINT XPKcs_working_p1q12PRIMARY KEY NONCLUSTERED (formula_id, submission_id,node_id, reference_year, observation_period)--ON "INDEXES")--ON "WORKING"goCREATE UNIQUE CLUSTERED INDEX XAK1cs_working_p1q12 ONdbo.cs_working_p1q12(submission_id ASC,formula_id ASC,node_id ASC,authority_flag ASC,observation_period ASC,reference_year ASC,observation_value ASC)goCREATE INDEX XIE1cs_working_p1q12 ON dbo.cs_working_p1q12(node_id ASC,authority_flag ASC,formula_id ASC,observation_period ASC,reference_year ASC,observation_value ASC)--ON "INDEXES"godrop table dbo.cs_working_pq2goCREATE TABLE dbo.cs_working_pq2 (formula_id int NOT NULLCONSTRAINT formula_id_pq14CHECK (formula_id between1001 and 3000),submission_id int NOT NULL,node_id int NOT NULL,reference_year smallint NOT NULL,observation_period datetime NOT NULL,authority_flag tinyint NOT NULLCONSTRAINT ONE_DEFAULT3440DEFAULT 1CONSTRAINT Binary_flag_rule675CHECK (authority_flag IN(0, 1)),interpolated_flag tinyint NOT NULLCONSTRAINT ZERO_DEFAULT6930DEFAULT 0CONSTRAINT Binary_flag_rule676CHECK (interpolated_flag IN(0, 1)),observation_value decimal_datatype NOT NULL,time_created smalldatetime NOT NULLCONSTRAINTCURRENT_DATE_DEFAULT1811DEFAULT getdate(),CONSTRAINT XPKcs_working_pq2PRIMARY KEY NONCLUSTERED (formula_id, submission_id,node_id, reference_year, observation_period)--ON "INDEXES")--ON "WORKING"goCREATE UNIQUE CLUSTERED INDEX XAK1cs_working_pq2 ONdbo.cs_working_pq2(submission_id ASC,formula_id ASC,node_id ASC,authority_flag ASC,observation_period ASC,reference_year ASC,observation_value ASC)goCREATE INDEX XIE1cs_working_pq2 ON dbo.cs_working_pq2(node_id ASC,authority_flag ASC,formula_id ASC,observation_period ASC,reference_year ASC,observation_value ASC)--ON "INDEXES"go----- create view ---------drop view cs_working2goCREATE VIEW cs_working2 (submission_id, node_id, reference_year,observation_period, formula_id, observation_value, interpolated_flag,time_created, authority_flag) ASSELECT we.submission_id, we.node_id, we.reference_year,we.observation_period, we.formula_id, we.observation_value,we.interpolated_flag, we.time_created, we.authority_flagFROM cs_working_e2 weunion allSELECT wo.submission_id, wo.node_id, wo.reference_year,wo.observation_period, wo.formula_id, wo.observation_value,wo.interpolated_flag, wo.time_created, wo.authority_flagFROM cs_working_other2 wounion allSELECT wpq.submission_id, wpq.node_id, wpq.reference_year,wpq.observation_period, wpq.formula_id, wpq.observation_value,wpq.interpolated_flag, wpq.time_created, wpq.authority_flagFROM cs_working_pq2 wpqunion allSELECT wp1q1.submission_id, wp1q1.node_id, wp1q1.reference_year,wp1q1.observation_period, wp1q1.formula_id, wp1q1.observation_value,wp1q1.interpolated_flag, wp1q1.time_created, wp1q1.authority_flagFROM cs_working_p1q12 wp1q1union allSELECT wi.submission_id, wi.node_id, wi.reference_year,wi.observation_period, wi.formula_id, wi.observation_value,wi.interpolated_flag, wi.time_created, wi.authority_flagFROM cs_working_indexes2 wigo--- sample selects against partitioned view -----/*--run explain plan here and see all 5 partitions being pulledselect * from cs_working--run explain plan here and see just the 1 partitionselect * from cs_working_e2--run explain plan and see this is not pruning to the needed partitionselect * from cs_workingwhere formula_id = 1--run explain plan and see it is now pruning to the needed partitionselect * from cs_workingwhere formula_id = 1and submission_id is not null--run explain plan and see it is now pruning to the needed partition,tooselect * from cs_workingwhere formula_id = 1and observation_value is not null*/

View 1 Replies View Related

Distributed Partitioned Views/Federated Servers Anyone?

Apr 21, 2003

I'm trying to do some researh on the use of SQL's DPV. I'm looking for feedback from people who've actually done this production to know more about the design challenges and level of added administration required. Any information will be much appreciated. Thanks.


aK

View 4 Replies View Related

DB Engine :: Partitioned Views In Standard Edition?

Apr 24, 2015

We have are SQL 2014 Standard edition, I have a situation where-in I plan to partition a table now since table partition is not supported in standard version I thought about Partitioned views however now I am stuck where I cant make the view writable because of the identity column in the base table.

Do I have any other option in this case?

View 5 Replies View Related

Help With Partitioned Views Or Updating Data From Multiple Tables

Mar 16, 2008

Hi All,

My database's design is set out here. In summary, I'm trying to model a Stock Exchange for a Technical Analysis application written using Visual C++. In order to create the hierachy I'm using a Nested Set Model. I'm now trying to write code to add and delete equities (or, more generically, nodes) to the database using a form presented to the user in my application. I have example SQL code to create the necessary add and delete procedures that calculate the changes to the values in the lft and rgt columns, but these examples focus around a single table, where as my design aggregates rows from multiple tables using UNION ALL:




Code Snippet
CREATE VIEW vw_NSM_DBHierarchy -- Nested Set Model Database Hierarchy
AS
SELECT clmStockExchange, clmLeft, clmRight FROM tblStockExchange_
UNION ALL
SELECT clmMarkets, clmLeft, clmRight FROM tblMarkets_
UNION ALL
SELECT clmSectors, clmLeft, clmRight FROM tblSectors_
UNION ALL
SELECT clmEPIC, clmLeft, clmRight FROM tblEquities_




Essentially, I'm trying to create an updateable view but I receive the error "UNION ALL View is not updatable because a partitioning column was not found". I suspect that my design in wrong or lacks and this problem is highlighting the design flaws so any suggestions would be greatly appreciated.

View 9 Replies View Related

SQL 2012 :: Using Partitioned Views In Order To Manage Table Sizes

Oct 13, 2015

I have a few databases that are using Partitioned Views in order to manage the table sizes and they all work well for our purposes. Recently I noticed a table that had grown to 400+ million rows and want to partition it as well, so I went about creating new base tables based on the initial table's structure, just adding a column to both table and primary key to be able to build a Partitioned View on them.The first time around, on a test system, everything worked flawlessly but when I put the same structure in place on the production system I get the dreaded "UNION ALL view 'DBName.dbo.RptReportData' is not updatable because the primary key of table '[DBName].[dbo].[RptReportData_201405]' is not included in the union result. [SQLSTATE 42000] (Error 4444)" error.

I have searched high and low and everything I see points to a few directives in order for a UNION ALL view to be updatable:

- Need a partitioning column that is part of the primary key
- Need a CHECK constraint that make the base tables exclusive, i.e. data cannot belong to more than one table
- Cannot have IDENTITY or calculated columns in the base tables
- The INSERT statement needs to specify all columns with actual values, i.e. not DEFAULT

Well, according to me, my structure fulfills these conditions but the INSERT fails anyway. CREATE scripts below scripted from SQL Server. I only modified them to be on a single row - it is easier to verify that they are identical in a text editor that way.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

[code]....

View 3 Replies View Related

SQL Server 2008 :: Is Only One Plan Is Kept For One Query In Plan Cache

Mar 14, 2015

Is only one plan is kept for one query in plan cache?

i heard generally hash is created for a query and plan is search with this hash.

View 2 Replies View Related

SQL Server 2014 :: Maintenance Plan Not Visible Under Database Engine After Import

Aug 13, 2015

I have a query. Here are the steps:

1. I created a maintenance plan using Visual Studio 2013 (nothing fancy pretty basic)
2. Using ssms 2014 I imported it (the dtsx file) under the Integration Services and it appeared there successfully
3. I connected to the Database Engine again using ssms 2014 - my expectation was to see it under the Management > Maintenance Plans folder but it was not present.

View 0 Replies View Related

SQL Server Admin 2014 :: Procedure Plan Cache Is Cleared Automatically

Sep 17, 2015

The plan cache is cleared automatically (every 30-60 seconds) on our high load OLPT server.

Can't find a reason for it. I have been logging procedure cache thru dm_exec_cached_plans, every second for a couple of minutes.

It shows that it doesn't grow beyond 200Mb. SQLserver has 64Gb available.

DBCC FREEPROCCACHE is not executed.
dm_os_memory_clerks shows the same 200Mb max (approximately).

I disabled the SQL Agent, still happens, so it's not a job causing the problem.

I disabled the Resource Governor, still the same problem.

View 9 Replies View Related

SQL Server Admin 2014 :: Configuration Of Maintenance Plan For Full Database And Log Back Up

Jul 9, 2015

I was trying to configure maintenance plan to take nightly full database backup and Log backup. I was trying to configure it like in attached file. Any links that i can follow and configure as in attached file.

View 7 Replies View Related

SQL Server Admin 2014 :: Script To Find Nested Views In A Database

Oct 7, 2015

Any easy way to find if there are any nested views existing in the database?...using SQL server 2014...

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved