SQL Server Vertical Sub-table

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

The vertical sub-table is relatively rare to see and use, because this may be a database design problems. Suppose a table in a database has some fields that are almost never changed but are often queried, and the data for some fields changes frequently. Such a design in the same table is unreasonable, mutual influence is too big. In the case of a table that has changed, it is possible to consider splitting the table by column, that is, vertical splitting.

Due to the relatively small number of cases of vertical tables, the recent 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--  was originally an access view (with the advantage that the view layer is unchanged) CREATE view [dbo].[ vdemotab]asselect [Guid],[username],[password],[useraccount],[amount]from [dbo]. [Demotab] GO

Note: The primary key for each table after splitting is the same. And the split table 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.

--  sub-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, due to the need to determine the primary key value before inserting 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, due to the need to determine the primary key value before inserting 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 generally 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

Assume that the previous operation was for a single table or view. The logical layer modifications can be quite numerous after splitting. To keep the changes to a minimum, you can operate with a federated view. 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

The problem is that you want to perform 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. In the Insert trigger, the [Guid] of the virtual table [inserted] is unique. So it is possible to insert the [GUID] into multiple sub-tables at the same time in a trigger, guaranteeing that the [GUID] of multiple tables 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 contains 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 multiple tables, does not agree to delete, so just delete the "primary table" record. Other sub-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 basic design is complete, now test.

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 again sort more data when new records are added.

After the table, a single data page can store a lot of other data, but in a number of tables, data pages also increased, the same time the Guid in each table, so the query data IO will be many other.

for updating data. Two tables are updated at the same time in a trigger. Even if you update one of the tables, other sub-tables will be affected.

Assume that the table is updated after a different time. The ability to use if (update (COL)) in a trigger to infer the updated column is to update the corresponding base table, and the other tables are not updated.

The best case scenario is that the split tables are "standalone". Without federated views, 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

SQL Server Vertical Sub-table