SQL Server – Creating Column with Default and Foreign Key as Metadata-Only Operation

sql server

It is possible to create a new column with a default value as a metadata-only operation (not a size-of-data operation):

ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL 
    DEFAULT 0;

But is it possible to also create such a column that references another table as a metadata-only operation?

ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL 
    DEFAULT 0 
    REFERENCES dbo.MyThing(MyThingId);

In theory it's possible because there should only be one value to check in the referenced table. However, in my example, the foreign key creation seems to be a size-of-data operation.

Best Answer

You can do this, but only if you don't check the FK on creation. The Foreign Key will be enforced for subsequent INSERT and UPDATEs, but won't be trusted by the Query Optimizer. EG:

drop table if exists MyReallyBigTable
drop table if exists MyThing
go
select o.* into dbo.MyReallyBigTable
from sys.objects o, sys.columns c

go
create table MyThing(MyThingId int primary key )
insert into MyThing(MyThingId) values (0)

set statistics io on
go
ALTER TABLE dbo.MyReallyBigTable
ADD MyThingId INT NOT NULL 
    DEFAULT 0 
    --REFERENCES dbo.MyThing(MyThingId);
go
alter table MyReallyBigTable
with nocheck
add constraint  fk_MyReallyBigTable_MyThing 
foreign key (MyThingId) references MyThing(MyThingId)
set statistics io off

You can come back later when you have time for a table scan and check all the rows to make the FK trusted:

select name, is_not_trusted 
from sys.foreign_keys
go
alter table MyReallyBigTable with check check constraint fk_MyReallyBigTable_MyThing
go
select name, is_not_trusted 
from sys.foreign_keys

Related Solutions

Sql-server – How to create a Foreign Key to a Compound Primary Key with a single column

Yes, you can do this with a COMPUTED column, It's a workaround really as the column has to be PERSISTED to be used for the foreign key constraint so it consumes storage space:

CREATE TABLE dbo.OtherTable (
    ID INT PRIMARY KEY IDENTITY(1,1),
    Blah VARCHAR(100),
    FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL
) ;

ALTER TABLE dbo.OtherTable WITH CHECK 
  ADD CONSTRAINT [FK__OtherTable__Blah] 
    FOREIGN KEY (FieldName, Blah) 
    REFERENCES Lookup (FieldName, Value) ;

Tested at SQL-Fiddle.

The table can then be used as if the FieldName does not exist. You could even define a view that does not include this column and make your applications use that view: SQL-Fiddle-2

The syntax you hoped for, although looking pretty, has not been implemented in any DBMS I know of:

ALTER TABLE dbo.OtherTable WITH CHECK 
  ADD CONSTRAINT [FK__OtherTable__Blah] 
    FOREIGN KEY('This', Blah) 
    REFERENCES Lookup (FieldName, Value) ;

Note however that the workaround with the computed columns would be 100% equivalent to this syntax, if only SQL-Server removed in the future the restriction of using only persisted columns. You could add a Connect item with your request.

For the question whether there is any other DBMS that allows this, no, there isn't. Check also a related question:

Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?

If you really don't want to use this method (because of the additional storage requirements, alternative paths would be to enforce the constraint via procedures or triggers (Note that you would have to write procedures or triggers for both tables involved.)

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

Best Answer

Related Solutions

Sql-server – How to create a Foreign Key to a Compound Primary Key with a single column

Sql-server – deteriorating stored procedure running times

Related Question