Search code examples
typessyntaxsql-server-2005user-defined-types

Colon in SQL Datatype?

I've inherited a SQL Server 2005 database that has some data type syntax I've never seen before. In the Design view for a table, the following columns are defined:

Column Name: "CustomerId"
Data Type: "pCustID:varchar(10)"

Column Name: "InvoiceNumber"
Data Type: "pInvoiceNum:varchar(15)"

Does anyone know what this datatype syntax represents? Are the characters to the left of the data type purely a label? Does it define some sort of referential integrity measure or constraints?

Solution

  • pCustID and pInvoiceNum are user defined data types. 

    CREATE TYPE [dbo].[pCustID] FROM [nvarchar](10) NOT NULL

    You can the use that as datatypes for columns in tables

    create table YourTable
(
  ID pCustID
)

    In MSSMS you find the user defined data types under Programmability - User-Defined Data Types.

    When you design a table in MSSMS pCustID is presented with a : between the name of the user defined data type and the base data type.

    enter image description here