SQL Server Vertical Sub-table

When the single-table data is too long, we can divide horizontally, refer to SQL Server partitioned view to achieve horizontal table, horizontal division can improve some performance of the table.

Vertical tables are relatively rarely seen and used, as this may be a problem with database design. If a table in a database has some fields that are almost never changed but are frequently queried, and the data for some fields changes frequently, it is unreasonable to put this design into the same table, and the interaction is too large. When you have a table that has changed, consider splitting the table by column, which is vertical splitting.

Because of the relatively small number of vertical tables, recently because of the existence of such a table, so the individual tinkering.

source table Design Structure:

--  source table CREATE TABLE [dbo]. [Demotab] ([Guid] [uniqueidentifier] not null,[username] [nvarchar] (+) not null,[password] [nvarchar] (+) not Null,[useraccount] [varchar] (+) not null,[amount] [numeric] (4) null,constraint [Pk_demotab] PRIMARY KEY CLUSTERED ([Guid]) goalter TABLE [dbo]. [Demotab] ADD CONSTRAINT [Df_demotab_guid] DEFAULT (Newsequentialid ()) for [guid]go--  Original is Access view (the benefit is the view layer is unchanged) CREATE view [dbo].[ vdemotab]asselect [Guid],[username],[password],[useraccount],[amount]from [dbo]. [Demotab] GO

NOTE: The primary keys are the same for each table after splitting, and the table after splitting is normalized.

Now split into two tables:

Note that you select a table as the base table, and the other tables have foreign keys to the table.

--The table  "1", with the table as "primary table", the other split table is "child table" CREATE table [dbo]. [DemoTab001] ([Guid] [uniqueidentifier] not null,[username] [nvarchar] (+) not null,[password] [nvarchar] (+) not null,constraint [PK _DEMOTAB001] PRIMARY key CLUSTERED ([Guid])) go--  primary Key default value is not required because the primary key value needs to be determined before inserting the data--alter TABLE [dbo].[ DEMOTAB001]--add CONSTRAINT [Df_demotab001_guid] DEFAULT (Newsequentialid ()) for [guid]--go--  sub-table "2", "Child table" CREATE TABLE [dbo]. [DEMOTAB002] ([Guid] [uniqueidentifier] not null,[useraccount] [varchar] (+) not null,[amount] [numeric] (4) Null,constraint [pk_ DEMOTAB002] PRIMARY key CLUSTERED ([Guid])) go--  primary Key default value is not required because the primary key value needs to be determined before inserting the data--alter TABLE [dbo].[ DEMOTAB002]--add CONSTRAINT [Df_demotab002_guid] DEFAULT (Newsequentialid ()) for [guid]--go--  If the primary table changes the PRIMARY key cascade update or DELETE (the primary key is usually not updated, you can also omit the on Update CASCADE) ALTER table [dbo]. [DEMOTAB002] ADD CONSTRAINT [Fk_demotab002_demotab001_guid] FOREIGN KEY ([GUID]) REFERENCES [DemoTab001] ([GUID]) on UPDATE CASCADE on D ELETE Cascadego

If you have previously done a single table or view operation, there may be a lot of changes to the logical layer after the split, and the federated view can be used to keep the changes to a minimum. How to connect the table depends on your personal circumstances.

--  Use Union view after splitting (INNER JOIN can also) ALTER view [dbo]. [Vdemotab] Asselect T1. [Guid],t1. [Username],t1. [Password],t2. [Useraccount],t2. [Amount] from [dbo]. [DemoTab001] T1 left JOIN [dbo]. [DEMOTAB002] T2 on T1. [Guid]=t2. [Guid] GO

At this point, to do DML operations on the table, insert, UPDATE, delete how to solve? Because the primary key is required to be scattered across multiple tables and is the same!

At this point only the trigger can be used to ensure consistency, the trigger is defined on the view, using the INSTEAD of type of the trigger.

Insert Trigger:

The [GUID] in the view [Vdemotab] is the table insertion value, and in the Insert trigger, the [GUID] of the virtual table [inserted] is unique, so you can use the [GUID] in the trigger to insert into multiple sub-tables at the same time, guaranteeing that the [GUID] of the multiple sub-table is the same!

--  Insert trigger Create TRIGGER [dbo]. [Tgr_vdemotab_insert] on [dbo]. [Vdemotab] INSTEAD of Insertas BEGIN INSERT into [dbo]. [DemoTab001] ([Guid],[username],[password]) SELECT [Guid],[username],[password] from inserted;  INSERT into [dbo]. [DEMOTAB002] ([Guid],[useraccount],[amount]) SELECT [Guid],[useraccount],[amount] from inserted; Endgo

UPDATE trigger:

Similarly, the update involves virtual tables deleted and inserted, and updates are updated on the view [Vdemotab], so the virtual table inserted includes all the fields, so the trigger needs to update multiple tables separately.

--  UPDATE trigger Create TRIGGER [dbo]. [Tgr_vdemotab_update]  on [dbo]. [Vdemotab]   INSTEAD of update  asbegin update T1 SET  t1.[ UserName] = T2. [UserName],  t1.[ Password] = T2. [Password] from [dbo]. [DemoTab001] As T1, inserted as T2 WHERE T1. [Guid] = T2. [Guid]  UPDATE T1 SET  t1.[ UserAccount] = T2. [UserAccount],  t1.[ Amount] = T2. [Amount] from [dbo]. [DEMOTAB002] As T1, inserted as T2 WHERE T1. [Guid] = T2. [Guid] Endgo

Delete trigger:

Deleting a view [Vdemotab] record, involving more than one table, is not allowed to be deleted, so as long as you delete the record of the primary table, the other tables are cascade deleted.

--  Delete trigger Create TRIGGER [dbo]. [Tgr_vdemotab_delete]  on [dbo]. [Vdemotab]   INSTEAD of Delete  asbegin    delete from [dbo].[ DEMOTAB001]    WHERE [GUID] in (SELECT [GUID] from deleted) Endgo

The design is basically complete, and now it's tested.

INSERT into [dbo]. [Vdemotab] ([Guid],[username],[password],[useraccount],[amount]) SELECT NEWID (), ' User01 ', ' pw01 ', ' account01 ', 100UNION allselect NEWID (), ' User02 ', ' pw02 ', ' account02 ', 99UNION Allselect NEWID (), ' user03 ', ' pw03 ', ' account03 ', 0GOUPDATE [Vdemotab] SET [password]= ' pw ', [amount]= '] where [Amount] >=0 and [amount]<100 and [UserName] like '%3 ' godelete from [Vdemotab] WHERE [UserName] = ' user03 ' goselect * FROM [db O]. [DemoTab001] SELECT * FROM [dbo]. [DEMOTAB002] SELECT * FROM [dbo]. [Vdemotab]

the basic operation is normal! Vertical sub-table complete!

What about performance?

Because the GUID is the primary key, using NEWID () instead of newsequentialid (), the clustered index may reorder more data when new records are added.

After a table, a single data page can store more data, but it is divided into multiple tables, data pages are increased, and GUIDs exist in each table, so there is more IO when querying data.

For update data, two tables are updated at the same time in the trigger, and even if one of the tables is updated, other tables will be affected. If the table does not update at the same time, you can use if (update (COL) in the trigger to determine the column that is updated, the corresponding base table is updated, the other tables are not updated.

The best case scenario is that the split tables are "stand-alone" without a federated view, and the queries and changes are independent, which requires changing the logical layer.

This article is from the "HELLO.KK (SQL Server)" blog, please be sure to keep this source http://blog.csdn.net/kk185800961/article/details/46740315

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL Server Vertical Sub-table