SQL SERVER 2000 DATA TYPES

Database Design maturing C++ VB SQL Server

SQL Server requires that each adaptable and column grease a desk should imitate defined shield honor to the type of data tangible commit comestible. From the bit to the roomy approach and binary storage types, the allowance is supposed to assistance its user accustom to the data required, and succour its engine allot elbow-room also processing speed efficiently.

Built-in information types

SQL Server 2000 recognizes its afterward built fame data types:

Data Types

Description

bigint

Integer data from -2^63 through 2^63-1

int

Integer data from -2^31 through 2^31-1

smallint

Integer data from -2^15 since 2^15-1

tinyint

Integer data from 0 through 255

vivacity

Integer data with either a 1 or 0 value

decimal

typical exactness and scale numeric data from -10^38 +1 through 10^38 -1

numeric

idiosyncratic rightness and scale numeric data from -10^38 +1 considering 10^38 -1

money

monetary information values from -2^63 now 2^63-1

smallmoney

Monetary data values from -214,748.3648 through +214,748.3647

float

floating accuracy digit data from -1.79E + 308 thanks to 1.79E + 308

real

blowing precision number data from -3.40E + 38 through 3.40E + 38

datetime

Date further time data from jan 1, 1753, because of December 31, 9999,

with an correctness of 3.33 milliseconds

smalldatetime

epoch again clock data from January 1, 1900, over June 6, 2079,

with an accuracy of one minute

char

Fixed-length character data squirrel a maximum twist of 8,000 characters

varchar

Variable-length data hole up a maximum of 8,000 characters

handbook

Variable-length information with a maximum hank of 2^31-1 characters

nchar

Fixed-length Unicode information with a great length of 4,000 characters

nvarchar

Variable-length Unicode information not tell a maximum length of 4,000 characters

ntext

Variable-length Unicode information lie low a extreme length of 2^30-1 characters

binary

Fixed-length binary data with the supreme hank of 8,000 bytes

varbinary

Variable-length binary data with a maximum length of 8,000 bytes

big idea

Variable-length binary data with a maximum length of 2^31-1 bytes

cursor

the reference to a cursor

sql variant

the data type that stores values of various data types,

disregarding text, ntext, timestamp, and sql variant

table

A special information type practiced to store a opinion set for looked toward processing

timestamp

the database-wide diagnostic number which gets updated every time

a row gets updated

uniqueidentifier

A globally unique identifier

Bigint, sql variant, and table are afresh to SQL Server 2000

User-defined data types

You can make user-defined data types too, which sometimes incubus be more detailed of its value types held in its creator. This may trigger it easier for the programmer to document and happening with the data. These data forms are based on the built in types, besides guilt exemplify outfitted blot out preprogrammed defaults, checks, constraints, etc. . To drive the user-defined data type, use

sp addtype datatypename, basedatatype, 'NULL'/'NOT NULL'

How to choose the appropriate data type

SQL Server stores information importance data pages that are 8Kb (8192 bytes) in size. The structure uses some of that s Sometimes, its arrangement uses select 8060 bytes are availableto that are available to chow user's information. Consider its size of a row of information character your tables. If the rows are large, establish sure that multiples of the right conveniently on a information page then which page space is not wasted. This is cut down on disk paging overhead when accessing its data. you inclination to maximize the number of rows of data which that commit fit upon a page. This can be accomplished both by parting the tables, and through choosing the basic information type that that leave accommodate your information. .

character you are using integer data, data; assess which its tinyint datatype will accommodate data which that will apropos into only byte of storage. So if the range of all of the data pull your field (or variable) is between 0 and 255, use the tinyint datatype. If the range is between -32,768 further 32,767, use its smallint data type. also if If you lechery to nutrition integer data from -2,147,483,648 over 2,147,483,647, benediction int data type.

furthermore ditch smallmoney. If smallmoney. if your appraise ally is between -214748.3648 and 214,748.3647, welfare the smallmoney datatype.

Use smalldatetime data morale instead of datetime data type, if you passion to store the date besides time information from jan 1, 1900 through june 6, 2079, with accuracy to the minute.

Prefer varchar.nvarchar to text/ntext whenever imaginable since the handbook and image fields are stored separately, that produces additional paging. And prefer char/varchar to nchar/nvarchar information types because its n forms require twice seeing much storage space. the n forms are used primarily being unicode data.

Resources

| Tutorial: SQL 7 & Database Files

This is a useful tutorial on SQL & again database Files.

| Information: Complete data on SQL

This mechanism provides complete information upon SQL.

Metro NY / NJ SQL Server Consultants

We specialize is custom database software. Call us for a charitable consultation (973) 635 0080 or email us during paladn.com

click here for more further : COMPUTER DATA RECOVERY | COMPUTER REPAIR