Table type

Question

Table type

Admingod

SSCertifiable

Points: 5997
More actions
May 22, 2019 at 2:51 pm

#3646170

I just want to validate my use of a custom type. I'm doing a query on that I need to pass an unknown number of string values to. I created a custom table type, with a script that looks like, What length should I make the field, for it to be of use to others? I'm using it to hold values obtained from the field Column, which is only 10 characters in length. The length in the X table field Column is 2600, though I'll never have need for that many, in this particular use case.
CREATE TYPE [dbo].[ColumnTableType] AS TABLE(
[Column] [nvarchar](30) NULL
)
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rVadim Hall of Fame Points: 3969 More actions · Answer 1

Your question is somewhat contradictory (or may be I'm reading it wrong). If you want the type to be used by others for any number of strings of any length in any language, then the type should be NVARCHAR(MAX). If it's for a limited use where length is 10 or less, then NVARCHAR(10).

--Vadim R.

Admingod SSCertifiable Points: 5997 More actions · Answer 2

Thanks that helps.

I'm guessing that the "if exists then drop" needs to be there, just like any other database object.

IF TYPE_ID(N'dbo.ColumnTableType') IS NOT NULL

BEGIN

DROP TYPE dbo.ColumnTableType

END

GO

CREATE TYPE dbo.ColumnTableType AS TABLE

(

Column NVARCHAR(MAX) NULL

)

GO

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

Here's a quicker way. And are you sure you want to allow NULLs?

DROP TYPE IF EXISTS dbo.ColumnTableType;

CREATE TYPE dbo.ColumnTableType AS TABLE
(
    Column NVARCHAR(MAX) NULL
);

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jonas.gunnarsson 52434 Ten Centuries Points: 1240 More actions · Answer 4

I'm using it to hold values obtained from the field Column, which is only 10 characters in length.

You probably what a table type holding your strings of length 10, that's make more sense. Is the values unique? The column should be defined as not null.

Maybe some thing like this;

drop type if exists dbo.String10Collection;
create type dbo.String10Collection as table
(
    Id nvarchar(10) not null,
    primary key clustered (Id asc)
    with (ignore_dup_key = off)
);
go
execute sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The identity of length 10.', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TYPE',@level1name=N'String10Collection', @level2type=N'COLUMN',@level2name=N'Id'
execute sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A unique collection of strings with length of 10.', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TYPE', @level1name=N'String10Collection';
go