Abstract classes in SQL Server. Are they even possible?

Question

I have a very specific question that I am surprised no one has yet asked. It involves a concrete class model and SQL Server 2016 DDL. The main point is: In my model, some classes inherit from a base class, let's say "BaseInfo".

This class is abstract. In SQL terms it means I do not (or do I?) want to create a separate table with rows (eg. CREATE TABLE "BaseInfo"..... I do however want some of my tables (actually quite a lot of them) to have the same columns (plus some of their own). In OOP terms I want some of my classes to inherit from that table definition. Is there a way to define this in DDL, or do I have to implement this manually?

Notes:

1. I have read all the articles that are to be found on TPH, TPT, TPC. They are too simplistic, they all feature the same Person-Student-Educator Model and do not match my case. What I probably want to do is TPC, but I want changes made to BaseInfo table to automatically apply to all child classes.

2. I will not be querying BaseInfo. I'd rather do a (rare) complex query involving some (or all) of the children.

3. Changes made to BaseInfo DDL (ALTER TABLE BaseInfo...) have to be reflected to the children (preferably automatically).

4. PostgreSQL shines here with the INHERITS statement. Is there any equivalent in SQL Server (I have done research and found nothing yet)?

5. In case I get no answer or have some other breakthrough, I will go the manual approach. Save a code snippet with the BaseInfo DDL and add it to the CREATE TABLE statement of each child table.

6. The answer must respect the conceptual model definition!

Answer 1

While correct that PostgreSQL does indeed support this request, SQL Server has no similar ability. That leaves you with either placing all of the "common" columns in each table, or having a "base" table that contains the common columns that will be joined to when necessary (most likely it will not be required 100% of the time).

I would recommend against copying these "common" columns into all (most) tables as indicated here:

5. In case I get no answer or have some other breakthrough, I will go the manual approach. Save a code snippet with the BaseInfo DDL and add it to the CREATE TABLE statement of each child table.

because:

1. long-term maintenance is more difficult / error prone as there is much greater potential for the schema to get out of synch. Templating the CREATE TABLE statements is one thing, but modifications need to be coordinated. In fact, one of the requirements here is:

   3. Changes made to BaseInfo DDL (ALTER TABLE BaseInfo...) have to be reflected to the children (preferably automatically).

2. performance will generally be better when separating out the data. Please keep in mind that databases have a fundamentally different goal -- physical storage and set-based operations -- than app code. While it might seem counter-intuitive and/or "clunky" and/or against "architectural" best-practices, structuring a data model to the strengths of the particular RDBMS will yield the best results. Joins might seem to be "extra" work (and sometimes they are), but RDBMSs are specifically optimized to work in this way (smaller tables generally are faster for both querying and index maintenance -- something that shouldn't be ignored!).

3. you can get around much of the "messiness" of the JOINs by abstracting each Class + SubClass relationship into Views. These can definitely help in all SELECT cases, and even in most UPDATE cases via Updatable Views (described in detail in the MSDN page for CREATE VIEW. INSERT and DELETE statements do not work as well, but the INSERT can still be made easier by combining both tables via the OUTPUT clause of the INSERT into the BaseInfo table, and DELETE statements can handle both tables when specifying the ON DELETE CASCADE property on the Foreign Key. Here is an example to show most of this:

   /*
   DROP VIEW dbo.UpdatableView;
   DROP TABLE dbo.UpdatableViewTableB;
   DROP TABLE dbo.UpdatableViewTableA;
   */
   CREATE TABLE dbo.UpdatableViewTableA
   (
    ID INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_UpdatableViewTableA] PRIMARY KEY,
    IsActive BIT NOT NULL,
    InsertTime DATETIME2 NOT NULL CONSTRAINT [DF_UpdatableViewTableA_InsertTime]
    DEFAULT (SYSDATETIME())
   );
   CREATE TABLE dbo.UpdatableViewTableB
   (
    ID INT NOT NULL CONSTRAINT [PK_UpdatableViewTableB] PRIMARY KEY,
    Whateva NVARCHAR(4000) NULL,
    CONSTRAINT [FK_UpdatableViewTableB_UpdatableViewTableA_ID]
    FOREIGN KEY ([ID])
    REFERENCES dbo.UpdatableViewTableA ([ID])
    ON DELETE CASCADE
   );
   GO
   CREATE VIEW dbo.UpdatableView
   AS
   SELECT a.[ID], a.[IsActive], a.[InsertTime], b.[Whateva]
   FROM dbo.UpdatableViewTableA a
   INNER JOIN dbo.UpdatableViewTableB b
    ON b.[ID] = a.[ID];
   GO
   INSERT INTO dbo.UpdatableViewTableA ([IsActive]) VALUES (1);
   INSERT INTO dbo.UpdatableViewTableB ([ID], [Whateva]) VALUES (1, N'test row');
   INSERT INTO dbo.UpdatableViewTableA ([IsActive]) VALUES (1);
   INSERT INTO dbo.UpdatableViewTableB ([ID], [Whateva]) VALUES (2, N'another row');
   SELECT * FROM dbo.UpdatableView;
   UPDATE uv
   SET uv.IsActive = 0
   FROM dbo.UpdatableView uv
   WHERE uv.[ID] = 2;
   SELECT * FROM dbo.UpdatableView;
   UPDATE uv
   SET uv.[Whateva] = N'what?'
   FROM dbo.UpdatableView uv
   WHERE uv.[ID] = 1;
   SELECT * FROM dbo.UpdatableView;
   DELETE uv
   FROM dbo.UpdatableView uv
   WHERE uv.[ID] = 1;
   -- Msg 4405, Level 16, State 1, Line 59
   -- View or function 'uv' is not updatable because the modification 
   -- affects multiple base tables.
   

I have described in greater detail variations of implementing this type of data model in the following DBA.SE answers:

• Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses
• Heterogeneous ordered list of value
• Don't know how to transform variable entity into relational table
• designing a database with type and sub type
• Can't convert this type of ER diagram into SQL or relational tables

P.S. For what it's worth (and that might not be much ;-), I am not sure how much actual benefit there is to this feature since most of the time, the properties in the "base" class do have value in being queried individually, even if only in reports. This is similar to PostgreSQL's ability to overload functions, which has been recently requested on Microsoft Connect ( User defined function overloading? ) and which, from having worked with it, ended up being more pain than gain.

Answer 2

There is nothing like this in SQL Server.

What tool are you building and maintaining your schema in though, will you be using database projects? If you went the "manual" method, while there is no SQL specific templating for this in SSDT, you might be able to use T4 templates to do something similar.

If that interested you then another alternative for templating is to use BimlScript. It is used to programmatically generate SSIS packages but they've very recently started doing something similar to generate SQL DDL so you could check into that.