Data types in SQL Server

Hi,
Can someone please tell me what are all the data types in MS SQL Server 2005 Express used for ?
I have only used Access before and the data types in Access are -

DATA TYPE INFORMATION STORED
Autonumber - A number that is assigned automatically and never changes thereafter.
Currency - Amount in the currency format chosen.
Date/Time - Date and Time. The Format property chosen on the general tab to date alone or time alone or both in different formats.
Hyperlink - Hyperlink addresses.
Lookup - Values that come from another table, a query, or a list of values that are supplied. Select the Lookup Wizard data type to set the lookup field automatically.
Memo - Large bodies of text - upto 64,000 characters in length.
Number - True numbers such as quantities.
OLE Object - Any OLE object such as a picture, sound, or word processing document.
Text - Any written text upto 255 characters in length, numbers, hyphens and nonnumeric charaters.
Yes/No - Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off.

I am familiar with the data types given above, and I couldn't find any similar data types in SQL Server. I do not have any programming knowledge and I use the GUI tools of SQL Server Management Studio Express and Visual Basic 2005 Express. All the data types I found there (listed below) seemed alien to me and I couldn't make head or tail out of it. Can someone please be kind enough to list out all the types of data stored under the information stored column, after the corresponding data type from SQL server, that I have mentioned below ? I need to know what exactly is stored in each data type. I have already checked the SQL Server 2005 books online for the data types but could not find anything of substance there.

DATA TYPE INFORMATION STORED
bigint
binary (50)
bit
char (10)
datetime
decimal (18,0)
float
image
int
money
nchar (10)
ntext
numeric (18,0)
nvarchar (50)
nvarchar (max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
timestamp
tinyint
uniqueidentifier
varbinary (50)
varbinary (max)
varchar (50)
varchar (max)
xml

Also can someone please tell me what are the data types in SQL Server that correspond to the all the data types from Access ?
I especially need to know the data type for Yes/No (boolean); OLE Object; memo; and Lookup, in SQL Server.

I would like to know if there is any data type or format for a data type for storing 'telephone numbers'. This is of special interst to me, as I would like to store data in particular fields in my 'contacts' database as telephone nos, and create a button on my forms that allows users to dial the selected tel no in the record by cliking the 'dial' button, which would dial the no using the windows dialer provided by Win XP, using a normal V9.0 voice/data/fax dialup modem. I have tired to insert a button in Access before, which opens an application, in this case, the windows dialer, and then the user can switch windows, get the required phone no from the record, switch back to the dialer, and enter the no and dial. But, instead of this tedious process, wouldn't it be easier if the tel no is stored as a separate data type instead of a normal number data type and all the user has to do is select the tel no in the record on the form and (maybe) right click it and select the option to dial on the menu that appears.
Or, even if there is no right click menu, the user can select the tel no record, and then click on the dial button on the form and it would dial the no. I think the Windows Address Book has a similar feature. If there is a way to it, can someone please tell me about it ?

I request you to please give me all the directions using the GUI tools of SQL Server Management Studio Express and Visual Basic Express, and not anything from T-SQL or any other programming language as I do not know anything about programming, and it wouldn't help me at all.
You're speaking to a rookie here who has just used the GUI tools of MS Access before. But I can copy paste a bit of code here and there, and if I understand it, I might even try modifying it a bit to meet my requirements.

Greetings.

If you are to use SQL Server (any version) you MUST understand SQL. Changing from Access to SQL Server Express is not a good idea if you want to stay away from programming, either.

There is nothing in your scenario I can see that makes me think you need to use SQL Server (Express), Access is a more logical choice for you. That is, unless you want to learn programming.

Hope this helps

|||

I think in this case I might have to start learning some programming along the way. BTW, what programming languages, apart from T-SQL, were you refering to, Gorm ? Because, I know that while T-SQL is mostly English-like syntax, and would be relatively easy for me to learn, I am very, very bad at maths and I guess that doesn't bode well for learning any programming, does it ?

And, the reason I chose to switch from Access to SQL Server, was because I had begun to face many limitations in Access, especially in finance related databases. ( I will come back for more help in designing the structure of financial databases later ).

Meanwhile, while I'm learning, could someone please help me out with the datatypes ?

|||? Hi Rishi, Here's a short description of SQL Server's datatypes. More information can easily be found in Books Online. Numeric datatypes For numeric information, you have three subcategories: Whole numbers: bit (0 or 1), tinyint (0 - 255), smallint (-32,768 - 32,767), int (approx -2 billion - 2 billion), bigint (approx -9 quintillion - 9 quintillion). A larger range comes with a larger storage size - which might induce a performance penalty. Also, the bit datatype has some funny behaviour on conversions, so you might wish to avoid it. Fixed point: numeric(n,m) or decimal(n,m). These are synonymous. The first number (n) gives the total number of digits; the second number (m) is the number of digits after the decimal point. So numeric(5,2) would be used to store values from -999.99 up to 999.99. Floating point: float or real (again, synonyms - sort of; see Books Online for the details). Of limited use, except in some scientific applications. Never use it for monetary amounts, because rounding in the base-2 notation that is used internally can cause strange behaviour. Date/time datatypes Datetime and smalldatetime. Datetime represents dates from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second. Smalldatetime represents dates from January 1, 1900 through June 6, 2079, with accuracy to the minute, but uses less storage size. There are no adta types for only date, only time, or for timespans. Character/text datatypes Char(n) and nchar(n) for fixed-length character strings of length n (shorter strings will be padded with spaces); varchar(n) and nvarchar(n) for variable-length character strings of maximum length n (uses n [or n*2 for nvarchar] bytes of storage, plus 2 extra bytes to store the current length); varchar(max) and nvarchar(max) are special versions of varchar and nvarchar for very long character strings (up to 2 billion bytes). The difference between char/varchar and nchar/nvarchar is that nchar/nvarchar accept Unicode characters, but use 2 bytes to store each character. There are also the text and ntext datatypes, but the are only included for compatibility with earlier versions, and they will disappear in a future version. Binary strings Binary(n), varbinary(n), and varbinary(max) are very much like char and varchar, except that they are used to store binary data instead of string data. And image, like text and ntext, exists for compatibility only. Other That leaves us with some special datatypes: cursor, table, sql_variant, uniqueidentifier, and xml. The xml datatype is new and will probably be used more and more over the coming months as it's capabilities are explored by the users; the other four have all proven to be useful is some situations - but none of them are relevant if you are new to SQL Server. For much more details on all datatypes, I suggest you consult Books Online. -- Hugo Kornelis, SQL Server MVP <Rishi Khetan@.discussions.microsoft.com> schreef in bericht news:8cc5461b-5253-4e95-b9fe-77cdca82a5e1@.discussions.microsoft.com... Hi, Can someone please tell me what are all the data types in MS SQL Server 2005 Express used for ?I have only used Access before and the data types in Access are - DATA TYPE INFORMATION STOREDAutonumber - A number that is assigned automatically and never changes thereafter.Currency - Amount in the currency format chosen.Date/Time - Date and Time. The Format property chosen on the general tab to date alone or time alone or both in different formats.Hyperlink - Hyperlink addresses.Lookup - Values that come from another table, a query, or a list of values that are supplied. Select the Lookup Wizard data type to set the lookup field automatically.Memo - Large bodies of text - upto 64,000 characters in length.Number - True numbers such as quantities.OLE Object - Any OLE object such as a picture, sound, or word processing document.Text - Any written text upto 255 characters in length, numbers, hyphens and nonnumeric charaters.Yes/No - Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. I am familiar with the data types given above, and I couldn't find any similar data types in SQL Server. I do not have any programming knowledge and I use the GUI tools of SQL Server Management Studio Express and Visual Basic 2005 Express. All the data types I found there (listed below) seemed alien to me and I couldn't make head or tail out of it. Can someone please be kind enough to list out all the types of data stored under the information stored column, after the corresponding data type from SQL server, that I have mentioned below ? I need to know what exactly is stored in each data type. I have already checked the SQL Server 2005 books online for the data types but could not find anything of substance there. DATA TYPE INFORMATION STOREDbigintbinary (50)bitchar (10)datetimedecimal (18,0)floatimageintmoneynchar (10)ntextnumeric (18,0)nvarchar (50)nvarchar (max)realsmalldatetimesmallintsmallmoneysql_varianttexttimestamptinyintuniqueidentifiervarbinary (50)varbinary (max)varchar (50) varchar (max)xml Also can someone please tell me what are the data types in SQL Server that correspond to the all the data types from Access ?I especially need to know the data type for Yes/No (boolean); OLE Object; memo; and Lookup, in SQL Server. I would like to know if there is any data type or format for a data type for storing 'telephone numbers'. This is of special interst to me, as I would like to store data in particular fields in my 'contacts' database as telephone nos, and create a button on my forms that allows users to dial the selected tel no in the record by cliking the 'dial' button, which would dial the no using the windows dialer provided by Win XP, using a normal V9.0 voice/data/fax dialup modem. I have tired to insert a button in Access before, which opens an application, in this case, the windows dialer, and then the user can switch windows, get the required phone no from the record, switch back to the dialer, and enter the no and dial. But, instead of this tedious process, wouldn't it be easier if the tel no is stored as a separate data type instead of a normal number data type and all the user has to do is select the tel no in the record on the form and (maybe) right click it and select the option to dial on the menu that appears.Or, even if there is no right click menu, the user can select the tel no record, and then click on the dial button on the form and it would dial the no. I think the Windows Address Book has a similar feature. If there is a way to it, can someone please tell me about it ? I request you to please give me all the directions using the GUI tools of SQL Server Management Studio Express and Visual Basic Express, and not anything from T-SQL or any other programming language as I do not know anything about programming, and it wouldn't help me at all.You're speaking to a rookie here who has just used the GUI tools of MS Access before. But I can copy paste a bit of code here and there, and if I understand it, I might even try modifying it a bit to meet my requirements.|||

Thanks a lot Hugo, you've been a great help.

But I've still got a few questions -

How do I store images in my database, and what datatype should I select for it, since you mention that the image datatype is only there for compatibility reasons and will dissappear in the near future ?

What datatype should I select for storing Yes/No or the checkbox type of value. ( Like in Access, you could select the boolean data type and then you got a choice as to what it displayed in the table view, either a checkbox, or a Yes/No value.)

What datatype should I select for storing a Lookup value from another table ?

Are the money and small money datatypes used for storing currency values, as per the currency type set in Windows Regional Settings ?

What are the timestamp and uniqueidentifier datatypes used for ?

I couldn't find what I was looking for in Books Online, so I would be really grateful if you could also provide me with the relevant links for the information I am looking for in Books Online.

Rishi.

|||? Hi Rishi, That's a lot of questions!! >>How do I store images in my database, and what datatype should I select for it, since you mention that the image datatype is only there for compatibility reasons and will dissappear in the near future ? Use varbinary(max), the replacement datatype for image. You can do many things with varbinary(max) that you could never do with image; that's the reason why the image datatype will be phased out. >>What datatype should I select for storing Yes/No or the checkbox type of value. ( Like in Access, you could select the boolean data type and then you got a choice as to what it displayed in the table view, either a checkbox, or a Yes/No value.) I'd recommend a CHAR(1) with a CHECK constraint to limit the contents to 'Y' and 'N' (or 'T' and 'F'), plus a NOT NULL constraint. There are also a lot of people who recommend using a numeric datatype and use e.g. 1 for true and 0 for false, but I find that the character values are much more obvious when reviewing table data. I would recommend against using the BIT datatype. It is merely confusing, since it is somehow similar to a Boolean (the official name for Yes/No) datatype, yet doesn;t behave like a true Boolean at all. >>What datatype should I select for storing a Lookup value from another table ? In a real relational design, there are no "lookup" tables and "other" tables - just tables. During information analysis, you will determine if a list of valid values for a column should be modeled as a CHECK constraint, or as a table. I'll use some examples to explain. Case #1: You have to store a currency code. You will, of course, use the three-letter ISO standard codes. The company will only do business in US Dollas, European Euro's, or Japanese Yen; no change to this policy is expected. No data needs to be stored that is functionally dependant on the currency code. In this case, you'll use a hard-coded dropdown (or other input thingie) in the front-end and a CHECK constraint in the database: CREATE TABLE SomeTable (....., CurrencyCode char(3) NOT NULL CHECK (CurrencyCode IN ('USD', 'EUR', 'JPY')), ...... ) Case #2: You have to store a currency code. You will, of course, use the three-letter ISO standard codes. Because the list of accepted currencies is subject to frequent change, or because some other information is functionally dependant on the currency code, you design a table of Currencies. The ISO currency code will of course be the primary key, and hence also the datatype to use for referencing columns. The dropdown (or other input thingie) in the front-end will be built at run-time by reading the Currencies table; integrity is guarded in the DB by a FOREIGN KEY constraint: CREATE TABLE Currencies (CurrencyCode char(3) NOT NULL PRIMARY KEY, -- Other columns ) CREATE TABLE SomeTable (....., CurrencyCode char(3) NOT NULL REFERENCES Currencies(CurrencyCode), ...... ) Case #3: Departments in the company are identified by name. These names can be up to 60 characters. For performance reasons, you decide to use a surrogate key for all references to the Departments table. You decide on an integer datatype with the IDENTITY property. The dropdown (or other input thingie) in the front-end will be built at run-time by reading the Departments table (make sure that the department name is shown; the front-end should remember the corresponding surrogate key value); integrity is guarded in the DB by a FOREIGN KEY constraint: CREATE TABLE Departments (DeptID int NOT NULL PRIMARY KEY IDENTITY, DeptName varchar(60) NOT NULL UNIQUE, -- Other columns ) CREATE TABLE OtherTable (....., DeptID int NOT NULL REFERENCES Departments(DeptID)), ...... ) * Note: output should use views or stored procedure that return only DeptName; DeptID is intended for internal use only and must never be shown to the end user!!!! >>Are the money and small money datatypes used for storing currency values, as per the currency type set in Windows Regional Settings ? Better not use money and smallmoney at all. In some ways, they behave very much the same as decimal(19,4) / decimal(10,4). The main differences are that they are formatted somewhat different when output, and that they can introduce some very nasty rouding problems in certain calculations. DECLARE @.a money, @.b money, @.c decimal(19,4), @.d decimal(19,4)SET @.a = 123.45SET @.b = 67.89SET @.c = 123.45SET @.d = 67.89 SELECT (@.a / @.b) * @.bSELECT (@.c / @.d) * @.d >>What are the timestamp and uniqueidentifier datatypes used for ? Timestamp is the most inadequately named datatype ever - the contents of a timestamp column have no relation whatsoever with current time. In SQL Server 2000, a new name (rowversion) was introduces. I believe (and hope!) that Microsoft intend to shift to using only the name rowversion and get rid of the name timestamp. If a table has a column with the rowversion (or timestamp) column, then that column will automatically be changed on every insert or update of the row. You can not set values to this column yourself. This is useful to implement optimistic locking: store the rowversion when reading data for display on the screen; when the user is finished making changes and wants to save them back to the DB, check if the rowversion is unchanged before updating the data. If it has changed, someone else has changed the data while the user was busy; frontend can take action (or ask user for action to take). The uniqueidentifier is used for storing globally unique identifiers (GUID) - a 16-byte value, usually represented in hexadecimal form, in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (e.g. 6F9619FF-8B86-D011-B42D-00C04FC964FF). They are designed for very specific scenario's where surrogate key values have to be generated at different locations. Typically only in replication scenario's. >>I couldn't find what I was looking for in Books Online, so I would be really grateful if you could also provide me with the relevant links for the information I am looking for in Books Online. Did you use Books Online on the Internet, or do you have them installed on your computer? In the first case, I recommend you to download the complete package and install them locally. I like the search capabilities of the "local install" Books Online version better than the search capabilities of the online version. -- Hugo Kornelis, SQL Server MVP|||There appears to be quite alot of knowledge on this forum, I have 1 simple question I would like answered:

How do I create a data record that auto increments? Or does that require additional coding?

Thanks!

|||? Hi owned, >>How do I create a data record that auto increments? Or does that require additional coding? Pick one of the numeric datatypes, then add the IDENTITY property. CREATE TABLE MyTable (KeyColumn int NOT NULL IDENTITY, OtherCol varchar(20) NOT NULL, PRIMARY KEY (KeyColumn), UNIQUE (OtherCol) ) go INSERT INTO MyTable (OtherCol) VALUES ('First') INSERT INTO MyTable (OtherCol) VALUES ('Second') SELECT * FROM MyTable go DROP TABLE MyTable go -- Hugo Kornelis, SQL Server MVP|||

Thanks Hugo,

Although I couln't understand your examples since I don't know any programming ;-), that's allright. I got the info that I needed. You've been very helpful. And I've been refering to the Internet version of Books Online as I thought since the name was books 'Online', so the online version would be more comprehensive and would have more material. Anyway, 180 MB is a HUGE download size, so I don't think I'll be getting the offline version anytime soon.

|||Hi ! This is great forum for data type.
I also have question:
What is a good data type for email, password, Phone Number and ISBN number?
Thanks!|||

Best bet is to use VARCHAR with appropriate length for each. There is no specific types for them in SQL. All logics to make sure data entered is in correct format should be handled by your application (email format validation, password rule validation, etc.).