{"id":18,"date":"2006-04-11T11:12:33","date_gmt":"2006-04-11T10:12:33","guid":{"rendered":"http:\/\/www.galhano.com\/blog\/?p=18"},"modified":"2006-04-11T11:46:07","modified_gmt":"2006-04-11T10:46:07","slug":"sql-server-2000-data-types","status":"publish","type":"post","link":"https:\/\/galhano.com\/blog\/?p=18","title":{"rendered":"SQL Server 2000 Data Types"},"content":{"rendered":"

Built-in data types SQL Server 2000 ships with 27 built-in (system) data types<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Data Types<\/font><\/strong> <\/font><\/th>\nDescription<\/font><\/strong> <\/font><\/th>\n<\/tr>\n
bigint <\/font><\/td>\nInteger data from -2^63 through 2^63-1 <\/font><\/td>\n<\/tr>\n
int <\/font><\/td>\nInteger data from -2^31 through 2^31 – 1 <\/font><\/td>\n<\/tr>\n
smallint <\/font><\/td>\nInteger data from -2^15 through 2^15 – 1 <\/font><\/td>\n<\/tr>\n
tinyint <\/font><\/td>\nInteger data from 0 through 255 <\/font><\/td>\n<\/tr>\n
bit <\/font><\/td>\nInteger data with either a 1 or 0 value <\/font><\/td>\n<\/tr>\n
decimal <\/font><\/td>\nFixed precision and scale numeric data from -10^38 +1 through 10^38 -1 <\/font><\/td>\n<\/tr>\n
numeric <\/font><\/td>\nFixed precision and scale numeric data from -10^38 +1 through 10^38 -1 <\/font><\/td>\n<\/tr>\n
money <\/font><\/td>\nMonetary data values from -2^63 through 2^63 – 1 <\/font><\/td>\n<\/tr>\n
smallmoney <\/font><\/td>\nMonetary data values from -214,748.3648 through +214,748.3647 <\/font><\/td>\n<\/tr>\n
float <\/font><\/td>\nFloating precision number data from -1.79E + 308 through 1.79E + 308 <\/font><\/td>\n<\/tr>\n
real <\/font><\/td>\nFloating precision number data from -3.40E + 38 through 3.40E + 38 <\/font><\/td>\n<\/tr>\n
datetime <\/font><\/td>\nDate and time data from January 1, 1753, through December 31, 9999,<\/font>with an accuracy of 3.33 milliseconds <\/font><\/td>\n<\/tr>\n
smalldatetime <\/font><\/td>\nDate and time data from January 1, 1900, through June 6, 2079,<\/font>with an accuracy of one minute <\/font><\/td>\n<\/tr>\n
char <\/font><\/td>\nFixed-length character data with a maximum length of 8,000 characters <\/font><\/td>\n<\/tr>\n
varchar <\/font><\/td>\nVariable-length data with a maximum of 8,000 characters <\/font><\/td>\n<\/tr>\n
text <\/font><\/td>\nVariable-length data with a maximum length of 2^31 – 1 characters <\/font><\/td>\n<\/tr>\n
nchar <\/font><\/td>\nFixed-length Unicode data with a maximum length of 4,000 characters <\/font><\/td>\n<\/tr>\n
nvarchar <\/font><\/td>\nVariable-length Unicode data with a maximum length of 4,000 characters <\/font><\/td>\n<\/tr>\n
ntext <\/font><\/td>\nVariable-length Unicode data with a maximum length of 2^30 – 1 characters <\/font><\/td>\n<\/tr>\n
binary <\/font><\/td>\nFixed-length binary data with a maximum length of 8,000 bytes <\/font><\/td>\n<\/tr>\n
varbinary <\/font><\/td>\nVariable-length binary data with a maximum length of 8,000 bytes <\/font><\/td>\n<\/tr>\n
image <\/font><\/td>\nVariable-length binary data with a maximum length of 2^31 – 1 bytes <\/font><\/td>\n<\/tr>\n
cursor <\/font><\/td>\nA reference to a cursor <\/font><\/td>\n<\/tr>\n
sql_variant <\/font><\/td>\nA data type that stores values of various data types,<\/font>except text, ntext, timestamp, and sql_variant <\/font><\/td>\n<\/tr>\n
table <\/font><\/td>\nA special data type used to store a result set for later processing <\/font><\/td>\n<\/tr>\n
timestamp <\/font><\/td>\nA database-wide unique number that gets updated every time<\/font>a row gets updated <\/font><\/td>\n<\/tr>\n
uniqueidentifier <\/font><\/td>\nA globally unique identifier <\/font><\/td>\n<\/tr>\n<\/table>\n

User-defined data types<\/font><\/h3>\n

SQL Server 2000 supports user-defined data types too. User-defined data types provide a mechanism for applying a name to a data type that is more descriptive of the types of values to be held in the object. Using user-defined data type can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type. The user-defined data types are based on the system data types and can be used to predefine several attributes of a column, such as its data type, length, and whether it supports NULL values. To create a user-defined data type, you can use the sp_addtype<\/strong> system stored procedure or you could add one using the Enterprise Manager. When you create a user-defined data type, you should specify the following three properties: <\/font><\/p>\n

    <\/p>\n
  • Data type’s name.<\/li>\n
  • Built-in data type upon which the new data type is based.<\/li>\n
  • Whether it can contain NULL values.<\/li>\n

    <\/font><\/font><\/ul>\n

    The following example creates a user-defined data type based on money data type named cursale<\/strong> that cannot be NULL: <\/font><\/font><\/p>\n\n\n
    \n
    EXEC sp_addtype cursale, money, 'NOT NULL' GO<\/font><\/pre>\n<\/td>\n<\/tr>\n<\/table>\n
    Both system and  user-defined data types are used to enforce data integrity. It is very important  that we put forth a lot of effort while designing tables: the better you design  your tables, the more time you can work without any performance problems. In an  ideal case, you never will update the structure of your tables. <\/font><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"
    Built-in data types SQL Server 2000 ships with 27 built-in (system) 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 through 2^15 – 1 tinyint Integer data from 0 through 255 bit Integer data with either a 1 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"close","ping_status":"close","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-18","post","type-post","status-publish","format-standard","hentry","category-sql-server","author-admin"],"_links":{"self":[{"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/18","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=18"}],"version-history":[{"count":0,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/18\/revisions"}],"wp:attachment":[{"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=18"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=18"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=18"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}