In the latest release of SQL Server, Microsoft has added a number of new data types to expand the functionality of SQL Server. This article will cover some of the new SQL Server 2000 data types and how you can use them to speed up your code in one instance.
Bigint
Bigint (or "big integer") offers the same type of functionality as the integer data type but is much larger. Big int is 8 bytes in size versus its 4 byte int counterpart. In other words, a bigint value can hold whole numbers between -9,223,372,036,854,775,807 and 9,223,372,036,854,775,807. Regular integer values can only hold whole numbers between -2,147,483,648 through 2,147,483,647, which is usually fine in most instances.
Unless otherwise noted, old functions such as COUNT() will still return integer values. This will only allow you to return a record count of a little over 2 billion. A few new functions have been added to cure this problem. For example, you can now run BIG_COUNT() as you would COUNT() to return a count as a big ingteger.
SQL_Variant
SQL Variants are similar to a variant in Visual Basic. It can hold any type of data except timestamp, text, ntext, and image data types. This data type is especially useful for storing meta data where you don't know what type of data you'll be receiving. The data type can hold a maximum of 8016 bytes.
Table
My favorite new data type addition is the Table data type. It allows you to store records in limbo until you're ready to perform an action on them. They perform very similar to a temp table. When looking at the Show Plans for a large data load using a temp table and a Table data type, they're nearly identical with the exception of the table creation. The table data type does perform much faster the using temp tables since there is less recompilation that must occur. Here's an example on how to use this new data type:
DECLARE @VariableName TABLE
(columna int PRIMARY KEY,
columnb varchar(20))
INSERT INTO @VariableName VALUES (1, '123 Main St')
INSERT INTO @VariableName VALUES (2, '55 Riverside Av')
SELECT * FROM @VariableName
GO
