Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

New SQL Server 2000 Data Types

By Brian Knight,

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
Total article views: 6558 | Views in the last 30 days: 0
 
Related Articles
FORUM

Count Function

Count Function with condition

FORUM

Bug in POWER function?

POWER function and BIGINT

FORUM

convert DateTime to BigInt

convert DateTime to BigInt

SCRIPT

Convert datetime to bigint

This function is used to generate the bigint value for given datetime.

FORUM

Convert alphanumeric to BigInt

Convert alphanumeric to BigInt

Tags
data types    
database design    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones