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


ADVERTISEMENT

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

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 2012 :: Re-order Views Fields Name By Alphabet

May 20, 2014

There are about 300 fields in a views.

How to reorder all fields by alphabet?

For example, from script of views like below:

select name, date, amount from order

re-order fields' name like below:

select amount, date, name from order

For about 300 fields view, how to code to re-order fields' name?

View 6 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

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

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

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

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 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 View Related

Partitioned Index Column Order

Aug 22, 2007

We are using partitioned unique indexes on partitioned tables. When the Unique Index is built, should the column the index is partitioned by be the top (leftmost) column in the index? While this violates cardinality, it makes sense (at least to me) that the first thing the query execution would do is figure out which partition(s) contain the result set, then filter from there.

What do you guys think? Is there any documentation on optimizing partitioned indexes?

View 1 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 2012 :: Trace File Sizes

Jul 30, 2014

I am in the middle of capturing a workload to try and tune a SQL instance and was wondering what kinds of sizes people capture in terms of traces. I am only 1 day into a capture and I believe a typical workload would be a week long capture and I am already at 10GB of files. I am only capturing rpc_completed and sql_batch_completed.

What sizes of workloads do other people capture and then where do you analyse them, do you have particular dedicated server for this kind of thing as at present I am looking to use my local PC. Also what rollover file sizes do people tend to use, I am currently using 1GB.

View 7 Replies View Related

SQL 2012 :: Temp Table Sort Order

Apr 3, 2013

Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.

View 5 Replies View Related

SQL 2012 :: Partitioned View Over Two Databases

Sep 8, 2014

I have database with a large table (30 Billion rows) because it is so big I separated the data in quarterly tables and created a partitioned view (with hints for the date column) about 1 billions a quarter. (all in separated filegroups). The tables themselfes are partitioned by date again, so you slice out one day

However the full-backup of grows and grows and the mainpart of it is "old" but needed data.

So I was thinking to put the older data in a separate database (with separated backup) and then point to the table in my view.

While this is technical possible (leaving out the WITH SCHEMABINDING) I wonder what negative consequences it will have.

I already had to lose "with schemabing".

I have to use separate partioning functions - for each database its own - (partition schemas where already separated due to separated filegroups)

What about query optimization, does the optimizer care that there are two databases?

View 6 Replies View Related

SQL 2012 :: Deadlocks In Partitioned Tables?

Oct 31, 2015

We are using partitions and all the table are properly aligned as per the partition keys. When this particular sp, which is inserting data to a table from a different table based on the partitionkeys is called by Web UI where threading has been applied, dead lock appears.

Let me make it more clear.

ThreadOne:
Insert into table A(partitionKey,BatchId,...)
select * from table B where partitionkey = 1
ThreadTwo:
Insert into table A(partitionKey,BatchId,...)
select * from table B where partitionkey = 2

I can see sometimes it gives deadlock for this procedure, not sure about the reason, as far as I guess since the tables are partitioned and escalation is set to Auto the deadlock should not occur.

View 2 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 2012 :: Manage Concurrency When Users Need To Create Invoice Number

May 31, 2014

I have a db to manage the creation of invoice number designed for a web application.

My problem is how to manage the concurrency when the users need to create an invoice number.

View 9 Replies View Related

Query Table Sizes

May 17, 2002

Hi,
I am looking to runa query to get the sizes of the tables in my SQL 7 DB.
I know I can access the info in Enterprise Manager, under "Tables & Indexes".
But I need to get this info via a query.
I need rows and size.
I figured out how to get rows through the sys tables:
select sysobjects.name, sysindexes.rows
from sysobjects,sysindexes
where sysobjects.name = sysindexes.name
and xtype = 'U'

Is the size of each table stored in a sys table as well? I can't find it.

View 2 Replies View Related

Views Ignoring Order By Clause

Sep 7, 2007

I have just transferred my site to a new server with SBS R2 Premium, so the site's database changed from SQL 2000 to SQL 2005.   I find that searches are now returning results in random order, even though they use a view with an Order By clause to force the order I want.
I find that the results are unordered when I test the view with Management Studio, so the issue is unrelated to my VB/ASP Net code.
Using my SQL update tool (SQL Compare, from Redgate) I find that there are no differences in the views, or the underlying tables.
Using Management Studio to test a number of views, I find that I have a general problem with all views.  For example, one of the simpler views is simply a selection of fields from one table, with an Order By clause on the tables primary key: -       SELECT     TOP (100) PERCENT GDQid, GDQUser, GDQGED, GDQOption, gdqTotalLines, GDQTotalIndi, GDQRestart, GDQCheckpointMessage,                             GDQStarted, GDQFinished, gdqCheckpointRecordCountr       FROM         dbo.GEDQueue       ORDER BY GDQid DESC
If I right-click the view (from Management Studio's Object Explorer pane), select Design from the menu to show the view's design, and then click the Execute SQL icon, the view's results are displayed perfectly, in descending order of GDQid.  However, if I select "Open View" the view's results are displayed out of order.
When I do this with the SQL 2000 database, both Design/Execute and Open View correctly display the data in the correct order.
Is there something that I should check in the SQL 2005 installation - some option that has been set incorrectly?
Regards, Robert Barnes

View 16 Replies View Related

Views Scripted In The Wrong Order

Dec 19, 2005

Is there a way to make EM script all views of a database in the order in which they depend on each other?

View 11 Replies View Related

Create Views In The Order Of Their Dependency

Feb 5, 2007

In SQL 2K...i need to write a script that writes a script that creates all views in the order that they are nested and I am feeling lazy. Does anyone have anything handy?For example if View_BOB says...CREATE VIEW VIEW_BOBASSELECT COL_1 FROM VIEW_JOHNI need the script to generate a script that creates View_JOHN before View_BOB.

View 1 Replies View Related

Incorrect Processing Order For Views

Jun 4, 2007

I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2

Essentially the problem is as follows:
1) Initial state is that merge replication of table and views is working fine
2) I then alter one view which references a new view in the same publication
3) Synchronization processes the view scripts in the wrong order regardless of the processing order
4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted

The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order


Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs

I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct

Is there an known problem in this area?

Any suggestions would be much appreciated

aero1

View 7 Replies View Related

Reporting Services :: Email Option Is Missing In Manage Subscription (SSRS 2012)

Aug 29, 2015

In the Project Web Access (PWA 2010), I have created Report Library which contains .rdl files --> Manage Subscription --> Add Subscription --> Email subscription is missing

Did i missed out some thing but other email functionalities like notifications, Approval workflow are working fine so no issue with SMTP.

View 4 Replies View Related

Table Sizes In SQL Server 2000

Nov 14, 2001

How can I get the table sizes for data and transaction logs just like we had in SQL Server 7.0 on the first screen of the Entreprise Manager?

I remember having a bar showing used space in blue and unused in magenta. I bet there are a couple of functions that can be added in a script that will retreive this info.

View 2 Replies View Related







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