Source code is presented as Visual Studio 2012 database project. But If you have no Visual Studio 2012, or not aware of generating database from schema, please download database backup from Downloads tab and restore it on the same instance as AdventureWorks2012.

VirtualDB Schema
The database schema lov (abbreviation of well known business term "List Of Values") has 2 metadata tables ("Entity" and "Field") and 2 data tables ("Row", "Value").
When you add/change "Field" table record, the table trigger refreshes the underling view. To create all views script manually, run the following query:
exec [lov].[usp_GenerateEntities] @EntityNamePattern='%'

3 steps to set up virtual database:
  1. Fill metadata ("Entity" and "Field") tables
  2. Generate views (automatically by "Field" table trigger, or manually by stored procedure call)
  3. Fill data (you insert data into view, trigger delivers data to "Row" and "Value" tables)
All steps and results are described in the following unit test.
Here is the result of data load:
LoadedTables.gif
Data tables after reindexing look pretty good:
DBCC SHOWCONTIG('lov.Row');
DBCC SHOWCONTIG('lov.Value');

DBCC SHOWCONTIG scanning 'Row' table...
Table: 'Row' (21575115); index ID: 1, database ID: 15
TABLE level scan performed.
- Pages Scanned................................: 4242
- Extents Scanned..............................: 537
- Extent Switches..............................: 536
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.88% [531:537]
- Logical Scan Fragmentation ..................: 0.57%
- Extent Scan Fragmentation ...................: 7.64%
- Avg. Bytes Free per Page.....................: 41.8
- Avg. Page Density (full).....................: 99.48%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC SHOWCONTIG scanning 'Value' table...
Table: 'Value' (53575229); index ID: 1, database ID: 15
TABLE level scan performed.
- Pages Scanned................................: 38562
- Extents Scanned..............................: 4821
- Extent Switches..............................: 4820
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [4821:4821]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 0.23%
- Avg. Bytes Free per Page.....................: 785.9
- Avg. Page Density (full).....................: 90.29%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now, let's switch to views.
Views.gif
Each view record has 3 additional fields:
  1. "RowId" (uniqueidentifier) unit row identifier
  2. "AddDate" (datetime) indicates when record was inserted
  3. "UpdateDate" (datetime) indicates when record was updated
All generated views support insert, update and soft delete actions controlled by instead of trigger.
One important moment - trigger allows inserting/updating multiple records by one transaction. All update changes are accurately merged. Deleting from view is logical; trigger updates "DeleteDate" field in "Row" table, and view filteres out that record.

Physical tables "Row" and "Value" have compact structure with tunned indexes. Indexes named "idx_Guard" prevent duplicating unique records. Null values are not stored in "Value" table. All "Value" table values are stored in varbiny(max) format and automatically converted in views to/from entity field data type.

Current release has the following restrictions:
  • SQL Server 2012 and higher versions only
  • Does not support user defined data types. All UDDT need to be converted into system types. Field table DataType field value should have system types only. For example, AdventureWorks UDDT "Name" must be converted into "varchar(20)", etc.
  • Reference integrity (foreign keys) between entities is not supported. But it could be enforces by Object Relational Mapping platforms like Entity Framework, LINQ to SQL or LightSwitch.

VirtualDB Manager
This application allows to manage metadata in simple manner
manager.png

Last edited May 8, 2013 at 12:39 PM by ban, version 41

Comments

montrubio Jul 30, 2013 at 2:46 PM 
Thanks