What s New for T-SQL in SQL Server 2000
What's New for T-SQL in SQL Server 2000SQL 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 DatatypesSQL Server 2000 introduces three new datatypes:
The bigint DatatypeThe bigint datatype is an 8-byte integer that holds integer (whole number) values from “2 63 ( “9,223,372,036,854,775,808) through 2 63-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:
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 DatatypeThe 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.
Table 26.1. Datatype Hierarchy and Associated Datatype Families for Comparison of sql_variant Expressions
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' end go ------------------ 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 = 0.0 set @variant2 = 123 select case when @variant1 > @variant2 then 'Variant1 is bigger' when @variant2 > @variant1 then 'Variant2 is bigger' else 'They are equal' end go ------------------ 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 :
The table DatatypeIn 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 go ----------- 18 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 go 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:
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 FunctionsSQL 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 ViewsNormal 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.0In 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:
 |
Datatype, convert(int, SQL_VARIANT_PROPERTY(@VARIANT1, 'MaxLength')) as 
EAN: 2147483647
Pages: 503