CODEWITHSUNDEEP
sqlsql-serverforeign-keysdatabase-partitioningpartitioned-view
DropDropped
DropDropped

Reputation: 1273

Partitioned view on large parent and child table with foreign key

How can I do partitioned view on SQL Server (Standard edition) on two tables with foreign key - parent and child 1TB each - without changing queries called from application, so that queries are not inefficient?

Here is the schema (simplified): ( or on SQL Fiddle)

CREATE TABLE Product
(`idProduct` int, 
 `DateProduced` datetime, 
 `productName` varchar(7), 
 `description` varchar(55),
  PRIMARY KEY (`idProduct`)
);

CREATE TABLE ProductPhoto
(`idProduct` int primary key,
 `Image` BLOB);


CREATE TABLE ProductExport
( `idExport` int primary key,
  `idProduct` int, 
 `TimeExported` datetime, 
 `quantity` int);

alter table ProductPhoto
add constraint fk1_Photo foreign key (`idProduct`) references Product(`idProduct`);

alter table ProductExport
add constraint fk1_Export foreign key (`idProduct`) references Product(`idProduct`);

Here are possibilities:

a) if I make partitioned view on table Product based on DateProduced (partitions would be by month each), I cannot search by table's primary key effectively (SELECT * FROM Product WHERE idProduct=3 will search in all partitions of table Product).

b) if I make partitioned view on table Product based on idProduct, I cannot search by DateProduced effectively.

I have also problem how to make both tables partitioned.

Is it possible to somehow make a reasonable partitioned view with both tables, with no large changes for queries (search all product between dates, get product by productId), so that it wouldnt be too slow?

Upvotes: 0

Views: 399

Answers (0)

Related Questions