Special data types-Part 2

This section continues discussion of special data types, including table and the cursor types and also traditional user-defined data types.

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.

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.

This was last published in February 2007

Dig Deeper on SQL-Transact SQL (T-SQL)



Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.