What's New for T-SQL in SQL Server 2000

SQL Server 7.0 was a significant upgrade from SQL Server 6.5 in terms of the new features available in Transact-SQL. SQL Server 2000 doesn't really provide many new Transact-SQL features over what was available in 7.0. It has a few new datatypes, the ability to create user-defined functions, and the ability to create indexed views.

New Datatypes

SQL Server 2000 introduces three new datatypes:

  • bigint

  • sql_variant

  • table

The bigint Datatype

The bigint datatype is an 8-byte integer that holds integer (whole number) values from ?263 (?9,223,372,036,854,775,808) through 263-1 (9,223,372,036,854,775,807). The bigint datatype is intended for use when integer values would exceed the range of the int datatype and can be used in all syntax locations where the int datatype is allowed. Although SQL Server will sometimes promote tinyint or smallint values to the int datatype, it will not automatically promote tinyint, smallint, or int to bigint. For example, certain functions (for example, SUM()) might accept a tinyint or smallint value and return an int as the resulting expression. These aggregate functions will not return a bigint unless the parameter expression is of type bigint.

SQL Server 2000 provides two new functions specifically for use with bigint values:

  • COUNT_BIG()? Similar to COUNT, but returns a bigint result. Use this function when the count of a number of items in a resultset would exceed the range supported by the int datatype.

  • ROWCOUNT_BIG()? Similar to @@ROWCOUNT, but returns the number of rows affected by the previous command as a bigint result. Use when the number of rows affected would exceed the range supported by the int datatype.

If you find a need to deal with integer values even greater than the range of the bigint datatype, you need to use the numeric datatype and specify a precision between 20?38 with a scale of 0.

Be aware that by default, a constant expression that exceeds the range of the int datatype will implicitly be interpreted as numeric datatypes with a scale of 0. To return a bigint expression, you will need to use the CAST or CONVERT function to convert the numeric result to a bigint.

The sql_variant Datatype

The sql_variant datatype stores values of various SQL Server?supported datatypes, except for text, ntext, image, timestamp, and sql_variant. The sql_variant datatype is similar to the variant datatype used in Microsoft Visual Basic or the DBTYPE_VARIANT datatype used in OLE DB. You can use the sql_variant datatype in column definitions, variables, parameters, and the return values of user-defined functions. When used in a column, the sql_variant datatype allows you to store values of different datatypes in different rows. For example, one row could contain an integer value, another row could contain a float value, and another could contain a character string.

The maximum size of a sql_variant is 8016 bytes; however, you do not specify a specific size for sql_variant. The size is determined by the actual data value stored in the sql_variant variable or column. You can determine the size of data in a sql_variant datatype with the DATALENGTH function. The DATALENGTH function returns only the size of the data and doesn't include the size of the metadata contained in the sql_variant.

Before a sql_variant can be used in any arithmetic or character operations or be assigned to a column or variable of a different datatype, it must first be explicitly converted to its base datatype using CAST or CONVERT.

Columns defined with a sql_variant datatype can be used in indexes and unique keys, as long as the total length of the data in the key columns does not exceed 900 bytes. The total size of the sql_variant is the number of bytes to store the data plus the size of the metadata. sql_variant columns can also be used in primary or foreign keys. A sql_variant column cannot be used in a computed column, however.

When comparing sql_variant datatypes, they adhere to the following rules based on the datatype hierarchy for datatype families, as shown in Table 26.1.

  • When comparing sql_variant expressions that have different base datatypes and the base datatypes are in different datatype families, the expression whose datatype family is higher in the hierarchy chart is considered the greater of the two expressions.

  • When comparing sql_variant expressions that have different base datatypes but are in the same datatype family, the expression whose base datatype is lower in the hierarchy chart is implicitly converted to the other datatype before the comparison is made.

  • When comparing sql_variant expressions of char, varchar, nchar, or varchar, they are evaluated based on additional criteria, including the locale code ID (LCID), the LCID version, the comparison flags used for the column's collation, and the sort ID. In other words, they are compared just like character expressions.

Table 26.1. Datatype Hierarchy and Associated Datatype Families for Comparison of sql_variant Expressions
Datatype Hierarchy Datatype Family
sql_variant sql_variant
datetime datetime
smalldatetime datetime
float approximate numeric
real approximate numeric
decimal exact numeric
money exact numeric
smallmoney exact numeric
bigint exact numeric
int exact numeric
smallint exact numeric
tinyint exact numeric
bit exact numeric
nvarchar Unicode
nchar Unicode
varchar Unicode
char Unicode
varbinary binary
binary binary
uniqueidentifier uniqueidentifier

In the following example, you are comparing a sql_variant variable containing a float value with sql_variant containing an integer value.

declare @variant1 sql_variant, 
        @variant2 sql_variant

set @variant1 = convert(float, 100.0)
set @variant2 = 123

select case when @variant1 > @variant2 then 'Variant1 is bigger'
            when @variant2 > @variant1 then 'Variant2 is bigger'
            else 'They are equal'
Variant1 is bigger

Notice that even though the value of the integer is greater than the value of the float, SQL Server reports @variant1 as being greater because the two datatypes are in different datatype families and the float datatype is higher in the datatype hierarchy.

In the next example, you are comparing two datatype values that are in the same datatype family?money and int.

declare @variant1 sql_variant, 
        @variant2 sql_variant

set @variant1 = $100.0
set @variant2 = 123

select case when @variant1 > @variant2 then 'Variant1 is bigger'
            when @variant2 > @variant1 then 'Variant2 is bigger'
            else 'They are equal'

Variant2 is bigger

Notice that in this example, SQL Server reports @variant2 as being greater because the two datatypes are in the same datatype family. SQL Server converts the integer expression to money (which is higher in the datatype hierarchy) and then compares the two values both as money datatypes.

The sql_variant datatype cannot be used in LIKE expressions or in full-text indexes.

To obtain information about the data stored in a sql_variant expression, you can use the sql_variant_property() function:

sql_variant_property(variant_expression, property) 

This function returns a sql_variant result for the property specified. The property options that can be specified are as follows:

  • BaseType? The underlying SQL Server datatype for the sql_variant value

  • Precision? The maximum number of digits to the left of the decimal point for any type of numeric data value

  • Scale? The maximum number of digits to the right of the decimal point for any type of numeric data value

  • TotalBytes? The total number of bytes required to hold the data value and the metadata

  • Collation? The collation sequence of the sql_variant

  • MaxLength? The length of the datatype of the value contained in the sql_variant


If you are assigning a string containing a datetime value to a sql_variant expression, use the convert or cast function to explicitly convert the string to a datetime first, or it will be stored simply as a character string in the sql_variant.

[View full width]
DECLARE @VARIANT1 SQL_VARIANT, @VARIANT2 SQL_VARIANT SET @VARIANT1 = 'April 23, 2001' SET @VARIANT2 = convert(datetime, 'April 23, 2001') select convert(char(10), SQL_VARIANT_PROPERTY(@VARIANT1, 'BASETYPE')) AS graphics/ccc.gifDatatype, convert(int, SQL_VARIANT_PROPERTY(@VARIANT1, 'MaxLength')) as graphics/ccc.gifMaxLength, convert(int, SQL_VARIANT_PROPERTY(@VARIANT1, 'TotalBytes')) as graphics/ccc.gifTotalBytes select convert(char(10), SQL_VARIANT_PROPERTY(@VARIANT2, 'BASETYPE')) AS graphics/ccc.gifDatatype, convert(int, SQL_VARIANT_PROPERTY(@VARIANT2, 'MaxLength')) as graphics/ccc.gifMaxLength, convert(int, SQL_VARIANT_PROPERTY(@VARIANT2, 'TotalBytes')) as graphics/ccc.gifTotalBytes GO Datatype MaxLength TotalBytes ---------- ----------- ----------- varchar 14 22 Datatype MaxLength TotalBytes ---------- ----------- ----------- datetime 8 10

The table Datatype

In addition to sql_variant, SQL Server 2000 introduces another new datatype?the table datatype. table datatypes can be used for local variables in user-defined functions, stored procedures, and batches, or as the return value of a user-defined function. Columns in a table cannot be defined with the table datatype, nor can table variables be used as stored procedure or user-defined function parameters.

The syntax to define a variable using the table datatype is similar to the CREATE TABLE syntax except it's done in a DECLARE statement and the name of the variable comes before the TABLE keyword:

DECLARE @variable TABLE ( column definition | table_constraint [, ...] ) 

Notice that constraints are allowed in table variables, but the only constraint types allowed are primary key, unique key, check, or default constraints. Column properties can be NULL, NOT NULL, or IDENTITY. Foreign key constraints are not allowed.

table variables, like other kinds of local variables discussed in the "Programming Constructs" section later in this chapter, have a well-defined scope, which is limited to the procedure, function, or batch in which they are declared. The following is a simple example of using a table variable in a batch:

declare @title_info TABLE (title_id varchar(6), 
                           title varchar(80),
                           pubdate datetime,
                           price money null)
insert @title_info select title_id, title, pubdate, price from titles
select count(*) from @title_info


The following example shows what happens if you try to access the table variable in a subsequent batch:

-- now try to access variable in a new batch 
select * from @title_info

Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@title_info'.

table variables can be treated like any other table. Any SELECT, INSERT, UPDATE, or DELETE statement can be performed on the rows in a table variable, with two exceptions:

  • SELECT INTO cannot be used to add data to a table variable because the table variable must first be created using DECLARE, and SELECT INTO creates a table.

  • You cannot specify the resultset of a stored procedure when inserting into a table variable, as in the following example:

    INSERT INTO table_variable EXEC stored_procedure 

In addition, table variables cannot participate in transactions. A ROLLBACK TRANSACTION command will not affect data added to, modified in, or deleted from a table variable. You also cannot create indexes on table variables using the CREATE INDEX command.

For more detailed examples of using the table datatype in stored procedures and user-defined functions, see Chapter 28, "Creating and Managing Stored Procedures in SQL Server," and Chapter 30, "User-Defined Functions."

User-Defined Functions

SQL Server has always had a number of built-in functions that extended the capabilities of T-SQL, but these were hard coded into SQL Server and could not be modified. They still cannot be modified, but SQL Server 2000 now supports the creation of user-defined functions.

User-defined functions are defined using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. The user-defined function name must be unique for each user in the database.

User-defined functions take 0?1,024 arguments and can return either a scalar value or a table. User-defined functions that return a scalar value can be used anywhere a constant expression can be used in your queries, just like many of the built-in functions. User-defined functions that return a table datatype can be used in queries in which a table expression can be specified.

For more information and examples on how to define and use user-defined functions, see Chapter 30.

Indexed Views

Normal views are essentially nothing more than a virtual table. The resultset returned by the view is not stored in the database as a distinct object, but is stored in and retrieved from the actual underlying table(s). A normal view is just a SELECT statement. Whenever a query references a view, the resultset is generated from existing data in the underlying table(s). For a normal view, the overhead involved to dynamically build the resultset each time the view is referenced can be substantial if the views involve complex processing against tables with many rows, such as aggregating large amounts of data or joining many rows.

To solve this problem, SQL Server 2000 provides the ability to create indexes on views. You can improve performance on complex views by creating a unique clustered index on the view. Unlike normal views, when you create a unique clustered index on a view, the view is executed and the resultset for the view is physically stored and indexed in the database, in much the same way a table with a clustered index would be stored. (See Chapter 34, "Indexes and Performance," for more information on index structures and how they are stored.)

Although an indexed view stores a resultset for the data as it existed at the time the index was created, an indexed view will automatically reflect any modifications made to the data in the underlying base tables, similar to the way an index created on a base table does. As modifications are made to the data in the base tables, they are also reflected in the data stored in the indexed view.

Indexed views can significantly improve the performance of applications where queries frequently perform certain joins or aggregations on large tables. If the applications already make use of views, they do not need to be modified to reap the performance benefits provided by indexing the views. For more information on how and when to define indexed views, see Chapter 27, "Creating and Managing Views in SQL Server."

In Case You Missed It: New Stuff Introduced in SQL Server 7.0

In case you are making the leap directly from SQL Server 6.x or earlier to SQL Server 2000, following is a brief summary of many of the new T-SQL features/changes introduced in SQL Server 7.0:

  • Support for new unicode datatypes: nchar, nvarchar, and ntext.

  • Addition of the TOP n [PERCENT] extension to the SELECT statement syntax to limit the number of rows returned for that SELECT statement only (unlike the SET ROWCOUNT N command, which set the row limit for all queries in a session).

  • Improved cursor support via the definition of local cursors and the ability to define cursor variables and return cursors as parameters from stored procedures.

  • Identifiers from 30 characters up to 128 characters. Also, the left square bracket ([) and right square bracket (]) can be used to delimit identifiers in addition to using the SQL-92 standard double quotation mark (").

  • Increased the maximum size of the char, varchar, binary, and varbinary datatypes from 255 bytes to 8000 bytes.

  • Ability to use the SUBSTRING function on text and image columns.

  • Modification to the handling of NULLs and empty strings to conform to the ANSI-92 SQL standard.


    This change is one of the primary causes of some of the compatibility problems when upgrading SQL code from pre-7.0 databases. For example, concatenating a NULL with a string now returns NULL by default unless the CONCAT_NULL_YIELDS_NULL option is turned off. For more information on upgrade compatibility issues, please see the "Upgrading from Previous Versions" section in Chapter 8, "Installing and Upgrading SQL Server."

  • Addition of the uniqueidentifier datatype for storing a globally unique identifier (GUID).

  • Additional functionality to the ALTER TABLE command to allow you to add non-nullable columns, change a column datatype, or drop columns from the table without having to export and reimport the data. For more details, see Chapter 12, "Creating and Managing Tables in SQL Server."

  • Addition of the ALTER PROCEDURE, ALTER TRIGGER, and ALTER VIEW commands, which allows you to modify the definition of a procedure, trigger, or view without having to drop them first and lose permissions or dependencies.

  • Deferred name resolution in stored procedures, triggers, and SQL batches. Table and other object names are not resolved at compile time but at runtime. This allows you to create a procedure that references a table that does not exist when the procedure is created, providing greater flexibility to applications that create tables at runtime.


    This feature also allows you to think you created a procedure successfully only to have it generate an error at runtime because you mistyped a table name or created it in the wrong database!

  • Ability to define multiple triggers for the same action on a single table. For example, a single table can now have multiple triggers for update, insert, or delete. This enhancement provides the ability to put different business rules into different triggers. For more information on defining triggers, see Chapter 29, "Creating and Managing Triggers."

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features