Creating Datatypes in SQL-Server

If you are familiar with any programming language, you already know all about user-defined datatypes. If not, here is a quick refresher.

• Datatype Definition
  A user-defined datatype is one that you define yourself to allow for easy storage of your own data. However, user-defined datatype is based on known existing MS SQL Server 2022 datatypes. For example, suppose you wanted to create a user-defined datatype named “address”. Because the address datatype does not exist in MS SQL Server 2022 , you must create it using an existing MS SQL Server 2022 datatype, such as varchar(30). This allows for up to 30 characters of storage. You might even want to create the user-defined datatype to not allow null values.

The following syntax

sp_addtype type [,'system_data_type'] [,'null_type']

is NOT the correct way to create a stored procedure in SQL Server 2022. Here’s why:
Issues with the Provided Syntax:

1. sp_addtype is not used for stored procedures
   • sp_addtype is a deprecated system stored procedure used to create user-defined data types (UDTs), not stored procedures.
   • In SQL Server 2022, you should use CREATE TYPE instead.
2. Stored Procedure Creation Uses CREATE PROCEDURE
   • The correct way to create a stored procedure in SQL Server is CREATE PROCEDURE, not sp_addtype.

Correct Syntax for Creating a Stored Procedure
To create a "stored procedure" in SQL Server 2022, use:

CREATE PROCEDURE MyProcedure
AS
BEGIN
    SELECT 'This is a stored procedure!';
END;

To execute the procedure:

EXEC MyProcedure;

To create a "user-defined data type", use:

CREATE TYPE myType FROM VARCHAR(50) NOT NULL;

Then, you can use it in a table as follows:

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpAddress myType
);

• ✅ Use `CREATE PROCEDURE` for stored procedures.
• ✅ Use `CREATE TYPE` for defining a user-defined data type.

In MS SQL Server 2022, there is no built-in data type specifically called "address." However, you can store address information using a combination of existing data types. Typically, an address might be stored across multiple columns in a table, using data types such as:

• VARCHAR or NVARCHAR for text fields like street address, city, state, and postal code.
• CHAR or NCHAR for fixed-length fields like state abbreviations or country codes.
• INT or BIGINT for numeric fields like postal codes (if stored as numbers).
• GEOGRAPHY or GEOMETRY for spatial data if you need to store geographic coordinates (e.g., latitude and longitude).

Example Table Structure for Storing Addresses:

CREATE TABLE Addresses (
    AddressID INT PRIMARY KEY IDENTITY(1,1),
    StreetAddress NVARCHAR(100),
    City NVARCHAR(50),
    State CHAR(2), -- Assuming a 2-character state abbreviation (e.g., "CA" for California)
    PostalCode VARCHAR(10), -- Supports formats like "12345" or "12345-6789"
    Country NVARCHAR(50),
    Latitude FLOAT, -- Optional: For geographic coordinates
    Longitude FLOAT -- Optional: For geographic coordinates
);

Notes:

1. Normalization: If you have a large dataset, you might normalize the address data into separate tables for cities, states, and countries to reduce redundancy.
2. Validation: Ensure proper validation for fields like postal codes, which may vary by country.
3. Spatial Data: If you need to perform geographic queries, consider using the GEOGRAPHY or GEOMETRY data types.

If you need a custom data type, you can create a "User-Defined Data Type (UDT)"" in SQL Server, but this is generally not necessary for addresses.

The `sp_addtype` stored procedure is deprecated and is no longer recommended for creating user-defined data types.
Instead, use `CREATE TYPE`.
Correct Syntax for Creating a User-Defined Data Type in SQL Server 2022
1. Using `CREATE TYPE`

CREATE TYPE address FROM VARCHAR(30) NOT NULL;

- This creates a "user-defined type (UDT)" named `address`, based on `VARCHAR(30)`, and ensures it cannot hold `NULL` values.
2. Using `CREATE TYPE` for Table-Defined Types
If you want to define a "structured table type", use:

CREATE TYPE AddressTableType AS TABLE
(
    Street VARCHAR(50),
    City VARCHAR(30),
    ZipCode VARCHAR(10)
);

- This is useful when passing "table-valued parameters" to stored procedures.
Usage Example
After creating the type, you can use it in table definitions:

CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY,
    CustomerAddress address -- Using the user-defined type
);

Or in a "stored procedure":

DECLARE @myAddress address;
SET @myAddress = '123 Main St';

SELECT @myAddress AS AddressValue;

`CREATE TYPE address FROM VARCHAR(30) NOT NULL;`

The following code will work after creating the user-defined datatype `address`, but there are some best practices and considerations to keep in mind.
Correct Steps:

1. Create the User-Defined Data Type (`address`)

       CREATE TYPE address FROM VARCHAR(30) NOT NULL;
       

2. Create the Table Using the User-Defined Type

   
   	
   CREATE TABLE employee 
   (
         AddressLine1 address,  -- Uses the user-defined type 
         AddressLine2 address   -- Uses the same type 
   );
   

Things to Consider

• The `address` type is based on `VARCHAR(30)`, so `AddressLine1` and `AddressLine2` will be `VARCHAR(30) NOT NULL`. If you need a nullable column, you must create a separate user-defined type that allows `NULL` values.
• The `CREATE TYPE` statement must be executed before using the type in a table.
• User-defined types (`CREATE TYPE`) are database-scoped, meaning they belong to a specific database and must be referenced accordingly.

Potential Issue: Using Table-Valued Types
If you "created a table-valued type" instead of a scalar type, the syntax would be incorrect.
For example:

CREATE TYPE AddressTableType AS TABLE
(
    Street VARCHAR(50),
    City VARCHAR(30),
    ZipCode VARCHAR(10)
);

This type CANNOT be used in `CREATE TABLE`. Instead, it is only for table-valued parameters in stored procedures.
Alternative: Inline Column Definition
If you don't need a user-defined type and want flexibility, you can simply define the columns directly:

CREATE TABLE employee 
(
    AddressLine1 VARCHAR(30) NOT NULL,
    AddressLine2 VARCHAR(30) NOT NULL
);

This avoids the dependency on a user-defined type.
Summary

• ✔ Yes, the table creation will work if `address` is a valid scalar user-defined type (`VARCHAR(30) NOT NULL`).
• ✔ Ensure the `address` type exists before table creation.
• ✔ If you need NULL values, you must modify the user-defined type (`CREATE TYPE address FROM VARCHAR(30) NULL;`).
• ✔ Avoid using table-valued types (`AS TABLE`) in column definitions.

Click the Exercise link below to practice creating your own datatype.
Create Transact Sql Datatype - Exercise
In the next lesson, we will review the material covered in the module so far.