Sample Header Ad - 728x90

Generic TVP tradeoffs?

6 votes
1 answer
360 views
Is there a best practice or strategy for table types used in TVPs? For instance, given the following:
CREATE TABLE dbo.Colors (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Shapes (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Materials (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Items (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
    ColorId int FOREIGN KEY REFERENCES dbo.Colors (ID),
    ShapeId int FOREIGN KEY REFERENCES dbo.Shapes (ID),
    MaterialId int FOREIGN KEY REFERENCES dbo.Materials (ID),
);
If you implemented a stored procedure for searching items that needed to support selecting multiple colors, multiple shapes, and multiple materials via TVPs (think checkbox lists in the UI), would you create three separate table types, one for every TVP, or would you create a single type for using it across all three? In other words, this:
CREATE TYPE dbo.ColorIds AS TABLE (Id int);
CREATE TYPE dbo.ShapeIds AS TABLE (Id int);
CREATE TYPE dbo.MaterialIds AS TABLE (Id int);
GO

CREATE PROCEDURE dbo.SearchItems
    @ColorIds ColorIds READONLY,
    @ShapeIds ShapeIds READONLY,
    @MaterialIds MaterialIds READONLY
AS
BEGIN
    PRINT 'Do something here'
END
GO
Versus this:
CREATE TYPE dbo.Ids AS TABLE (Id int);
GO

CREATE PROCEDURE dbo.SearchItems
    @ColorIds Ids READONLY,
    @ShapeIds Ids READONLY,
    @MaterialIds Ids READONLY
AS
BEGIN
    PRINT 'Do something here'
END
GO
The sample is deliberately contrived; the real use case consists of a lot more tables which although have different columns, all have a ID int primary key. Because of this, I personally am _much more_ inclined to do the latter. It's far less overhead, but I'm curious to know if there are any cons I should be aware of in doing this. This is of course for TVPs and TVPs _only_ (I would never mix different entities in a _real_ table, or any other structure of a more permanent nature.) While at it, what is your naming convention for naming table types and TVPs?
Asked by Daniel Liuzzi (163 rep)
Mar 17, 2019, 04:12 PM
Last activity: Mar 17, 2019, 05:17 PM