Partitioned View not updatable ???

Partitioned View not updatable ???

• Kevin Tough

  1. drop constraint and re-create constraint

  for long term

  1. check job dbcc reindex & update statistics on tables

  2. I f you have not the job , you will create it.
  

• - Phil -

  Hi sashisme
  
  Well no that is not really what you are trying to avoid, the partinioning column being part of the key is simply a necessity for sql server to control partiioning and have reference for the partioned view. it has more to do with the inner working of the DB engine.
  
  I am not sure what your definition is for the primary key is.
  
  But lets assume it is col1, col2, col3. And your where cluase is simply based on col2 it will table scan.
  
  You need to either alter your primary key so that col2 is the root, ie define the primary key as col2, col1, col3, then you can use the same where clause.
  
  Or you can alter the where clause to be where col1 = and col2 =
  
  Hope this helps.
  
  Thanx
  
  

• Kingwillowviii

  Hi Steve, Dietz,

  When the partitioning column is "part" of the primary key, and we try to search for a row with only the partioning column in the where clause, it still scans all tables. Now, this is precisely the prob we are trying to avoid right

  Ex:

  select * from A

  where col2 = 'some date'

  However if the partitioning column was a primary key by itself ( i.e. not part of a composite primary key) we do not have this prob. In this case, only that particular table is scanned.

  Any ideas

  
  

• livingsingl

  Hi Steve

  Works like a charm, thanx a million.

  Cheers

   

  
  

• comet96

  Steve any idea how you would do this check to ensure only say january or february data I do not want to partition by year aswell, for example i want 1 January table, that holds Junuary data for any year

  Thanx

  
  

• MartinKrieger

  Books Online says:
  
  

  A partitioning column exists on each member table and, through CHECK constraints, identifies the data available in that specific table. Partitioning columns must adhere to these rules:

  • Each base table has a partitioning column whose key values are enforced by CHECK constraints. The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table. Any given value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.
    
    

  Because your check constraint uses the expression datepart(mm,Col2), it is not a check on Col2 using only BETWEEN, AND, OR, <, <=, >, >=, and =.
  
  If the data are all in a single year, you could create the tables this way:
  
  create table Table_A_Jan (
  Col1 int not null,
  Col2 datetime not null CHECK (Col2 >= cast('20060101' as datetime) and Col2 < cast('20060201' as datetime)),
  Col3 char(5) not null,
  CONSTRAINT [PKJan] PRIMARY KEY CLUSTERED
  ([Col1], [Col2], [Col3]) WITH FILLFACTOR = 95 ON [PRIMARY]
  )
  
  create table Table_A_Feb (
  Col1 int not null,
  Col2 datetime not null CHECK (Col2 >= cast('20060201' as datetime) and Col2 < cast('20060301' as datetime)),
  Col3 char(5) not null,
  CONSTRAINT [PKFeb] PRIMARY KEY CLUSTERED
  ([Col1], [Col2], [Col3]) WITH FILLFACTOR = 95 ON [PRIMARY]
  )
  
  
  

• Saratha

  Hi
  
  Well i am not the expert at partitioning, but agree 100 % somethng is worng.
  
  The check constraint on Salary which is the partioning column should see which partition the salary range is in and at most scan only that 1 partitioned table.
  
  As i cannot see for myself what your indexes and query look like i will have to take your word for it.
  
  But If Salary is the root column for the index and the query has a where clause that states Salary = X, then i must agree this makes no sense.
  
  How big is this table or each partitioned table
  Does this index exist on all partitioned tables Is the partitioned view indexed
  
  The only other time i have seen this behaviour was when the index was so fragmented it was not used.
  
  Cheers
  
  

• BillAPgh

  sashisme wrote:

  Hi Steve, Dietz, When the partitioning column is "part" of the primary key, and we try to search for a row with only the partioning column in the where clause, it still scans all tables. Now, this is precisely the prob we are trying to avoid right Ex: select * from A where col2 = 'some date' However if the partitioning column was a primary key by itself ( i.e. not part of a composite primary key) we do not have this prob. In this case, only that particular table is scanned. Any ideas

  
  
  I beleive this is a bug. Try
  
  

  select * from A where col2 = cast('some date' as datetime)

  The optimizer doesn't appear to appear to handle literals correctly - hence the need to explicitly cast to the type of the partitioning column. Ideally it would check if an implicit cast exists between the literal and the partitioning columns data type.
  

  
  

  
  
  

• peacefrog

  Hi Dietz,

  Thanks for the quick reply. Lemme give u the whole picture.

  I have 3 tables Emp1(EmpID, EmpName, Salary) , Emp2 , Emp3

  My primary key is a combination of Salary + EmpID with Salary as root.

  Check constraint on Salary.

  For Emp1 - salary between 1000 and 3000

  For Emp2 - salary between 3001 and 5000

  For Emp3 - salary > 5001

  View :

  Create view Emp

  as

  Select EmpID, EmpName, Salary from Emp1

  union all

  ------same col defn---------------------from Emp2

  union all

  -----same col defn---------------------Emp3

  Now when i do

  Select EmpID, Empname, Salary

  from Emp

  where Salary = 3221

  (I have a record that satisfies the condition)

  It scans all three tables Emp1 , Emp2, Emp3 (as shown in query plan)

  This is just an example. If this works , i plan to implement it on a DB that we are going to design. This DB will have a table which will have many million records and i plan to break it down to 2 or 3 tables.

  If the DB scans all the tables, i guess it's as good as having a single table.

  I thought partitioned views solve this prob.

  Please do comment.

  
  

• SIFIS

  Hi Dietz,

  I'll dig into it tomorrow and see if i can uncover something. Will keep you posted. Meanwhile any suggestion from your side is highly appreciated.

  Thanks,

  Sathish

  
  
• Partitioned View not updatable ???