Ttykuu

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?

I personally use the latter, more generic version in my systems. I have two tables that I can think of off the top of my head: UniqueIntegerTable and UniqueStringTable - as you can imagine, they are defined as follows:

CREATE TYPE Utils.UniqueIntegerTable AS TABLE (
 [Value] INT NOT NULL PRIMARY KEY
);
CREATE TYPE Utils.UniqueStringTable AS TABLE (
 [Value] NVARCHAR(50) NOT NULL PRIMARY KEY
);

I prefer to have generic TVP's so that I don't clog up my schema's with multiple types that are basically the same. The performance is exactly the same as if you defined an explicit type as in your first example and it has the benefit that it creates less code for me to maintain.

I know one argument that I have previously heard for using explicit types that are bound to tables is that it is easier to understand their usage. I personally don't agree with this. There is nothing preventing me from defining a stored procedure using the wrong type (but has the correct shape for my needs). Instead, I can give the variable a good name to infer usage and the contents of the table:

CREATE PROCEDURE dbo.UpdateEmployees (
 @employeeIds Utils.UniqueIntegerTable READONLY
)
BEGIN
SET NOCOUNT ON;

-- Use table as needed.

END
GO

In terms of naming conventions, I don't know of any official standard but the one I use is to append Table to the end of the type name. I know that this is not really all that different from prefixing things with tbl but I'm ok with it in this instance. As with all naming conventions, pick one that you feel is easy to work with it - but once you do, stick to it. Naming conventions are only useful if you are consistent.