Created
November 10, 2022 21:58
-
-
Save JerryNixon/89b7d1adfab22d60832c9c234a346e46 to your computer and use it in GitHub Desktop.
Creating a partitioned view in SQL Server that auto-partitions INSERT & UPDATE statements
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "metadata": { | |
| "kernelspec": { | |
| "name": "SQL", | |
| "display_name": "SQL", | |
| "language": "sql" | |
| }, | |
| "language_info": { | |
| "name": "sql", | |
| "version": "" | |
| } | |
| }, | |
| "nbformat_minor": 2, | |
| "nbformat": 4, | |
| "cells": [ | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "-- drop existing data tables\r\n", | |
| "\r\n", | |
| "DROP TABLE IF EXISTS Part2022;\r\n", | |
| "DROP TABLE IF EXISTS Part2021;\r\n", | |
| "DROP TABLE IF EXISTS PartOlder;\r\n", | |
| "DROP VIEW IF EXISTS Parts;" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "24adfc68-0e50-4b51-9e33-c68a18534615", | |
| "tags": [] | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Commands completed successfully." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00" | |
| }, | |
| "metadata": {} | |
| } | |
| ], | |
| "execution_count": 1 | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">The partitioning column is a part of the PRIMARY KEY of the table.</span>\n", | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">It cannot be a computed, identity, default, or</span> <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px; box-sizing: inherit; outline-color: inherit; font-weight: 600;\">timestamp</span> <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">column.</span>\n", | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">If there is more than one constraint on the same column in a member table, the Database Engine ignores all the constraints and does not consider them when determining whether the view is a partitioned view. To meet the conditions of the partitioned view, ensure that there is only one partitioning constraint on the partitioning column.</span>\n", | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">There are no restrictions on the updatability of the partitioning column.</span>" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "d2dac666-8794-4710-af05-5f1ac8396c00" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "-- create data tables\r\n", | |
| "\r\n", | |
| "CREATE TABLE Part2022 \r\n", | |
| "(\r\n", | |
| " Id UNIQUEIDENTIFIER UNIQUE CLUSTERED\r\n", | |
| " , Year INT CONSTRAINT req2022 CHECK (Year = 2022) \r\n", | |
| " PRIMARY KEY NONCLUSTERED (Id, Year)\r\n", | |
| ");\r\n", | |
| "CREATE TABLE Part2021 \r\n", | |
| "(\r\n", | |
| " Id UNIQUEIDENTIFIER UNIQUE CLUSTERED\r\n", | |
| " , Year INT CONSTRAINT req2021 CHECK (Year = 2021) \r\n", | |
| " PRIMARY KEY NONCLUSTERED (Id, Year)\r\n", | |
| ");\r\n", | |
| "CREATE TABLE PartOlder \r\n", | |
| "(\r\n", | |
| " Id UNIQUEIDENTIFIER UNIQUE CLUSTERED\r\n", | |
| " , Year INT CONSTRAINT reqOlder CHECK (Year BETWEEN 2000 AND 2020) \r\n", | |
| " PRIMARY KEY NONCLUSTERED (Id, Year)\r\n", | |
| ");" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "f9e2a934-f86f-481c-ac1b-b8f4ee05977f" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Commands completed successfully." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.007" | |
| }, | |
| "metadata": {} | |
| } | |
| ], | |
| "execution_count": 2 | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "<span style=\"color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px; background-color: rgb(23, 23, 23);\">A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.</span>" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "35ef041a-4f04-45d3-bbff-faeb35760fc1" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "-- create partitioned view\r\n", | |
| "\r\n", | |
| "CREATE VIEW Parts AS\r\n", | |
| " SELECT * FROM Part2022\r\n", | |
| " UNION ALL SELECT * FROM Part2021\r\n", | |
| " UNION ALL SELECT * FROM PartOlder;" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "38a836d9-0d1c-40b6-9a38-44e6ae81bb7e" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Commands completed successfully." | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.001" | |
| }, | |
| "metadata": {} | |
| } | |
| ], | |
| "execution_count": 3 | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">The INSERT statement supplies values for all the columns in the view, even if the underlying member tables have a DEFAULT constraint for those columns or if they allow for null values. For those member table columns that have DEFAULT definitions, the statements cannot explicitly use the keyword DEFAULT.</span>\n", | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">The value being inserted into the partitioning column satisfies at least one of the underlying constraints; otherwise, the insert action will fail with a constraint violation.</span>\n", | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause, even if the column has a DEFAULT value defined in the corresponding member table.</span>\n", | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">Columns in the view that are an identity column in one or more of the member tables cannot be modified by using an INSERT or UPDATE statement.</span>\n", | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">If one of the member tables contains a</span> <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px; box-sizing: inherit; outline-color: inherit; font-weight: 600;\">timestamp</span> <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">column, the data cannot be modified by using an INSERT or UPDATE statement.</span>\n", | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">If one of the member tables contains a trigger or an ON UPDATE CASCADE/SET NULL/SET DEFAULT or ON DELETE CASCADE/SET NULL/SET DEFAULT constraint, the view cannot be modified.</span>\n", | |
| "- <span style=\"background-color: rgb(23, 23, 23); color: rgb(230, 230, 230); font-family: "Segoe UI", SegoeUI, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 16px;\">INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.</span>" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "7f52e313-b93e-46c2-a856-3de529f1cd08" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "-- insert data into different parts, through the view\r\n", | |
| "\r\n", | |
| "INSERT INTO Parts (Id, Year) \r\n", | |
| "VALUES \r\n", | |
| " (NEWID(), 2022)\r\n", | |
| " , (NEWID(), 2021)\r\n", | |
| " , (NEWID(), 2020)\r\n", | |
| " , (NEWID(), 2020)\r\n", | |
| " , (NEWID(), 2020)\r\n", | |
| " , (NEWID(), 2019)" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "828c19e3-6023-46f0-bbb4-92c5ec498bd1" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(6 rows affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.003" | |
| }, | |
| "metadata": {} | |
| } | |
| ], | |
| "execution_count": 4 | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "-- view the data in parts\r\n", | |
| "\r\n", | |
| "SELECT * FROM Part2022;\r\n", | |
| "SELECT * FROM Part2021;\r\n", | |
| "SELECT * FROM PartOlder;" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "6de033d4-a3aa-47d7-8869-5368ffb1b988" | |
| }, | |
| "outputs": [ | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(1 row affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(1 row affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "(4 rows affected)" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "display_data", | |
| "data": { | |
| "text/html": "Total execution time: 00:00:00.017" | |
| }, | |
| "metadata": {} | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "metadata": {}, | |
| "execution_count": 5, | |
| "data": { | |
| "application/vnd.dataresource+json": { | |
| "schema": { | |
| "fields": [ | |
| { | |
| "name": "Id" | |
| }, | |
| { | |
| "name": "Year" | |
| } | |
| ] | |
| }, | |
| "data": [ | |
| { | |
| "0": "15c446d8-a9af-4ed2-af03-bc0037cc7f9f", | |
| "1": "2022" | |
| } | |
| ] | |
| }, | |
| "text/html": [ | |
| "<table>", | |
| "<tr><th>Id</th><th>Year</th></tr>", | |
| "<tr><td>15c446d8-a9af-4ed2-af03-bc0037cc7f9f</td><td>2022</td></tr>", | |
| "</table>" | |
| ] | |
| } | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "metadata": {}, | |
| "execution_count": 5, | |
| "data": { | |
| "application/vnd.dataresource+json": { | |
| "schema": { | |
| "fields": [ | |
| { | |
| "name": "Id" | |
| }, | |
| { | |
| "name": "Year" | |
| } | |
| ] | |
| }, | |
| "data": [ | |
| { | |
| "0": "17b4ce5b-554b-46c7-8273-bb37c8a588fc", | |
| "1": "2021" | |
| } | |
| ] | |
| }, | |
| "text/html": [ | |
| "<table>", | |
| "<tr><th>Id</th><th>Year</th></tr>", | |
| "<tr><td>17b4ce5b-554b-46c7-8273-bb37c8a588fc</td><td>2021</td></tr>", | |
| "</table>" | |
| ] | |
| } | |
| }, | |
| { | |
| "output_type": "execute_result", | |
| "metadata": {}, | |
| "execution_count": 5, | |
| "data": { | |
| "application/vnd.dataresource+json": { | |
| "schema": { | |
| "fields": [ | |
| { | |
| "name": "Id" | |
| }, | |
| { | |
| "name": "Year" | |
| } | |
| ] | |
| }, | |
| "data": [ | |
| { | |
| "0": "6defe8e0-3612-4af9-bf89-88d5dccc4a4f", | |
| "1": "2020" | |
| }, | |
| { | |
| "0": "b8e05429-a642-4df3-ab3f-e193fa564f4b", | |
| "1": "2020" | |
| }, | |
| { | |
| "0": "514e5d60-75f4-4fde-b9b4-e71342ff699e", | |
| "1": "2020" | |
| }, | |
| { | |
| "0": "b146242c-43a1-44f1-ba23-fb524cecf0e3", | |
| "1": "2019" | |
| } | |
| ] | |
| }, | |
| "text/html": [ | |
| "<table>", | |
| "<tr><th>Id</th><th>Year</th></tr>", | |
| "<tr><td>6defe8e0-3612-4af9-bf89-88d5dccc4a4f</td><td>2020</td></tr>", | |
| "<tr><td>b8e05429-a642-4df3-ab3f-e193fa564f4b</td><td>2020</td></tr>", | |
| "<tr><td>514e5d60-75f4-4fde-b9b4-e71342ff699e</td><td>2020</td></tr>", | |
| "<tr><td>b146242c-43a1-44f1-ba23-fb524cecf0e3</td><td>2019</td></tr>", | |
| "</table>" | |
| ] | |
| } | |
| } | |
| ], | |
| "execution_count": 5 | |
| } | |
| ] | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment