Indexed v.s. Partitioned Views
Indexed View
Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create more nonclustered indexes (https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15).
An indexed view (sometimes referred to as a materialized view), is a view that has been made into more than just a simple stored query by creating a clustered index on it. By doing this, it basically makes it into a copy of data in a physical structure much like a table.
The typical use of indexed views is for reporting, and typically reporting in a reporting database where lots of data modifications are not taking place.
Partitioned View
Partitioning a table effectively splits the contents of the table into multiple “tables” known as partitions (which are not transparent to users). The main advantage of partitioning is that you can store different parts of your data on different physical units, using filegroups.
For instance, you can store old data that isn’t referenced very often on slower drives, or perhaps on drives that have better read performance and less write performance (since this data doesn’t change), whereas you can keep your current data on drives with good overall read/write performance. Partitioning in SQL Server, however, is an Enterprise Edition feature, and as such, may not be available to you if you haven’t got the budget.
Luckily, you can create your own partitioning. In essence, you have to manually design tables that represent each “partition”, and then bunch all those tables together using UNION ALL operators in a view. This view is your “partitioned view”, and it works in many ways like a partitioned table would (https://sqlsunday.com/2014/08/31/partitioned-views).
SQL Server Partitioned Views enable you to logically split a huge amount of data that exist in large tables into smaller pieces of data ranges, based on specific column values, and store this data ranges in the participating tables. To achieve this, a CHECK constraint should be defined on the partitioning column to divide the data into data ranges. Then, a partitioned view that combines SELECTs from all participating tables as one result set using UNION ALL operator, will be created.
The CHECK constraint is used to specify which table contains the requested data when selecting data from the view, which is similar to defining the portioning function in the table partitioning feature. The check constraint is used also to improve query performance, If the CHECK constraint is not defined in the participating tables, the SQL Server Query Optimizer will search in all participating tables within the view to return the result (https://www.sqlshack.com/sql-server-partitioned-views).
The PRIMARY KEY constraint must be involved in the partitioning for this to work. For example,
USE SQLShackDemo GO
CREATE TABLE Shipments_Q1 ( Ship_Num INT NOT NULL, Ship_CountryCode CHAR(3) NOT NULL, Ship_Date DATETIME NULL, Ship_Quarter SMALLINT NOT NULL CONSTRAINT CK_Ship_Q1 CHECK (Ship_Quarter = 1), CONSTRAINT PK_Shipments_Q1 PRIMARY KEY (Ship_Num, Ship_Quarter) ); GO CREATE TABLE Shipments_Q2 ( Ship_Num INT NOT NULL, Ship_CountryCode CHAR(3) NOT NULL, Ship_Date DATETIME NULL, Ship_Quarter SMALLINT NOT NULL CONSTRAINT CK_Ship_Q2 CHECK (Ship_Quarter = 2), CONSTRAINT PK_Shipments_Q2 PRIMARY KEY (Ship_Num, Ship_Quarter) ); GO CREATE TABLE Shipments_Q3 ( Ship_Num INT NOT NULL, Ship_CountryCode CHAR(3) NOT NULL, Ship_Date DATETIME NULL, Ship_Quarter SMALLINT NOT NULL CONSTRAINT CK_Ship_Q3 CHECK (Ship_Quarter = 3), CONSTRAINT PK_Shipments_Q3 PRIMARY KEY (Ship_Num, Ship_Quarter) ); GO CREATE TABLE Shipments_Q4 ( Ship_Num INT NOT NULL, Ship_CountryCode CHAR(3) NOT NULL, Ship_Date DATETIME NULL, Ship_Quarter SMALLINT NOT NULL CONSTRAINT CK_Ship_Q4 CHECK (Ship_Quarter = 4), CONSTRAINT PK_Shipments_Q4 PRIMARY KEY (Ship_Num, Ship_Quarter) );
CREATE VIEW DBO.Shipments_Info WITH SCHEMABINDING AS SELECT [Ship_Num],[Ship_CountryCode],[Ship_Date],[Ship_Quarter] FROM DBO.Shipments_Q1 UNION ALL SELECT [Ship_Num],[Ship_CountryCode],[Ship_Date],[Ship_Quarter] FROM DBO.Shipments_Q2 UNION ALL SELECT [Ship_Num],[Ship_CountryCode],[Ship_Date],[Ship_Quarter] FROM DBO.Shipments_Q3 UNION ALL SELECT [Ship_Num],[Ship_CountryCode],[Ship_Date],[Ship_Quarter] FROM DBO.Shipments_Q4
Using the check constraints, SQL Server can determine that the data we’re looking for can only exist in one of the tables. The performance impact from this difference in query plan can be considerable, particularly if the history table contains millions of rows (https://sqlsunday.com/2014/08/31/partitioned-views).
Question 1:
There are no foreign key relationships between TBL1 and TBL2. You need to minimize the amount of time required for queries that use data from TBL1 and TBL2 to return data. What should you do?
Question 2:
There are no foreign key relationships between TBL1 and TBL2. You need to minimize the amount of time required for queries that use data from TB1 and TBL2 to return data. What should you do?