Built-in data types in SQL Express: best practices?

Greetings,

I think these should be rather simple questions, yet I spent a number of hours last night digging through the forums here and msdn and couldn't find any satisfactory answers. Basically, there tend to be types of information that are commonly saved in most databases, like names, addresses, phone numbers, email addresses, etc...and there are a variety of built in data types in SQL Server. What are the best built in datatypes for some of the common entries in a sql database. Also, there are a number of character based types and I am curious why one would be more useful in certain situations than another. Why is there char( ), nchar( ), varchar( ), nvarchar( ) and text datatypes Why so many Also, what is the "text" datatype and when is it most likely to be used There is very little about the text type that I can find in the msdn or SQL Server docs...aside from the fact that it's text. On top of all this, there's numerous binary types as well. I'm really not getting the reason behind all these different basic types and why I would want to use one over the other in any specific instance.

TIA,

Mark

Hi,

there are sveeral things to keep in mind if you choose a datatype:

-Size: In your example CHAR / NCHAR / VARCHAR /NVARCHAR. THe NTypes requires twice the storage capacity than the other types, because they are unicode ready and are able to store unicode which the others aren¡¯t. So, if you have unicode you should take them, otherwise you can leave them out. THE VAR types are variable in size, which means that not the whole length is reserved in SQL Server at storage time if its not used yet. Sample: You have a VARCHAR(4000) and a CHAR(4000) column. They both can store up to 4000 characters, but the CHAR column will reserve the space even if you enter only 1 character. The other one will only store the one character. if additional data is inserted in that column a pointer will point to the rest of the string. So you will have to decide between storage capacity or access speed. In the numeric section you might have noticed TINYINT / SMALLINT / INT /BIGINT. If you only want to store for example state of a specific thing like "Enrolled" with the ID 1, "Approved" with the ID 2 and so on for about 10 entried int he table you would probably take TINYINT because this is capaple for 256 entries (range is 0-255). For number above 255 you will take the next appropiate type like SMALLINT with a range of 0-32768. So in conclusion you will take the data type that will fit your problem but not waste any space in your database, becausse bigger datatypes will need more space for reservation.

-Functionality: For some Datatype you will have a lack of functionality. Let me explain: VARCHAR and Text both stores strings. TEXT can store up to 2GB, so why not always take TEXT. Beside that TEXT will be deprected in further version as there is a new data type which is called VARCHAR(MAX) text can support only some string operations in SQL Server. Also the client haviour of text is different, which forces you to specially handle it within your client code. So use VARCHAR(MAX) to store text bigger than 8000 character in a row. Some datatype like the TEXT and IMAGE won¡¯t even allow Grouping whereas VARCHAR(MAX) will allow it.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Thanks for that

If you store binary data in SQL Server 2005, you should use the VARBINARY(MAX) type, for Text you should choose the VARCHAR(MAX) type.

Image is sort of a misunderstanding word as you don¡¯t actually only store images in there :-D

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---