Table
The table data type is used to store a recordset for later processing. In some ways, this data type is similar to a temporary table. You cannot use this type to define a column. It can only be used as a local variable to return the value of a function.
NOTE
You will find more information about table variables in the "Table Variables" section later in this chapter, and more information about table-valued functions in Chapters 4 and 9.
The Cursor Data Type
This is a special kind of data type that contains references to cursors. You will see in the "Cursors" section later in this chapter that cursors are programming constructs that are designed to allow operations on records one at a time. It is not possible to define a column of this type. It can be used only for variables and stored procedure output values.
Transact-SQL User-defined Data Types
You can define custom data types in a database. Traditional user-defined data types are defined in Transact-SQL. We will describe them in this segment of the book.
In SQL Server 2005, it is possible to define user-defined data types in .NET as well. We will describe the .NET user-defined data types in Chapter 13.
These new types are based on system-defined data types and are accessible only in the database in which they are defined. You can define them from Enterprise Manager or using the system stored procedure sp_addtype:
Exec sp_addtype Phone, varchar(20), 'NOT NULL'
Exec sp_addtype typPostalCode, varchar(7), 'NULL'
The first parameter is the name of the new data type, the second parameter is the system-defined data type on which it is based, and the third parameter defines the nullability of the new data type. When the command is executed, the server adds the type to the sys.systype system view of the current database.
New types can be based on any system-defined type except timestamp.
TIP
A fascinating aspect of user-defined data types is that you can change them in one step across the database. For example, if you decide that decimal(19,6) is not big enough for your monetary values, you can replace it with decimal(28,13). You can simply run the script that first changed the data type and then re-create all database objects that are referencing it. This feature is very useful during the development stage of a database. Unfortunately, when a database is already in the production phase, tables contain data, and this feature becomes a lot more complicated.
The designers of Microsoft SQL Server have included one special data type with the server—sysname. It is used to control the length of Transact-SQL identifiers. When the server is working in default mode, the length of this type is set to 128 characters. When the compatibility level is set to 65 or 60, the length is shortened to 30 characters. You should use it to define columns and variables that will contain Transact-SQL identifiers.
Basic Transact-SQL Programming Constructs
Home: Introduction
Tip 1: T-SQL identifiers
Tip 2: Database object qualifiers
Tip 3: Character String data types
Tip 4: Date, time and number data types
Tip 5: Special data types-Part 1
Tip 6: Special data types-Part 2
Tip 7: Local variables
Tip 8: Global variables
Tip 9: Table variables
Tip 10: Flow control statements
Tip 11: Blocks, looping and conditional statements
Tip 12: Unconditional and scheduled execution
Tip 13: Transact SQL cursors
Tip 14: Cursor related statements
Tip 15: Problems and justified use of cursors
The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.
