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

|||Thank you Jens. This helps clear up some things for me. I now know how I want to proceed with my project.|||

This is the most helpful information I've come across since I started working with SQL Express last year. I've ALWAYS just selected TEXT and INT datatypes because I couldn't find anything anywhere that laid bare the functionality of SQL datatypes as well as these 2 paragraphs do.

So, are you saying the IMAGE datatype is being phased out and the VARCHAR(MAX) is a preferrable alternative? Thank gawd for that bit of info, because not even an hour ago, I embarked upon my first adventure into storing images in a datatable and had selected (of course) IMAGE because it appeared to be the ideal datatype.

Of course, that is only the first of a thousand walls I'll stumble into before I actually see my images being stored (and accessed) in the table properly.

I hereby nominate Jens post for inclusion in the actual SQL documentation.

If someone will second the nomination, the motion will carry, and then be ignored entirely by the author of the actual documentation (who I believe is Gavin from the HBO series Kids In The Hall - http://en.wikipedia.org/wiki/Kids_in_the_Hall#Gavin - someone might be able to refute that though.)

|||

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

|||

Not to digress, but you seem to be well-written and exceedingly well-versed in SQL...perhaps you might could make my transition into database IMAGE storage a bit smoother?

I've assigned the Image Column's datatype to VARBINARY(MAX).

In Visual Basic Express 2005, after saving the Table Definition, I open the Table Data to enter my records. Of course, entering the typed data is no problem, but I can't copy and paste specific images into individual records.

Should I assume that I have to INSERT the image via some convoluted and complex QUERY or some otherwise bizarre code sequence...or is it just plain impossible.

If there is no method that can be summarized in 3 or 4 lines of text, don't even waste your time posting it....maybe a link to the tome that offers the instructions (other than the 'official' documentation)

|||I agree, and I second your nomination k10wn. Motion passed. Jens pearls of wisdom may now offically be ignored and buried under tons of far less worthy posts.|||

Another quick question about the datatypes:

What exactly does the 'SQL_VARIANT' type do?

|||Actually thats pretty simple:

Take a look here: http://www.akadia.com/services/dotnet_read_write_blob.html

The method getPhoto will get the byte array which can be stored in SQL Server. You can′t copy & paste the file into SQL Server. The same for getting data back, you retrieve the data as a blob / byte array and either assign that to a control or write the stream to a file.

So actually it can be summarized to 3/4 lines of code like the following:

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);

byte[] photo = br.ReadBytes((int)fs.Length);

//Clean the house
br.Close();
fs.Close();

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||SQL Variant is a untyped datatype which can store every data type in SQL Server exept thise one mentioned in the BOL (like Text, Image etc.) You probably use it if you don′t know the expected type which is entered. I would not recommend using this type and always use stongly type data.

More on this on your BOL or on: http://msdn2.microsoft.com/de-de/library/ms173829.aspx

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks alot for your time...

I guess I should clarify:

I was actually suggesting that if your reply required more than 3 or 4 sentences in actual post itself, then you needn't waste your valuable time* on my frivolous, n00b questions. If that was the case, I hoped you might could provide a hyperlink to where I could read up on it.

But here you not only replied with a concise, plethora of lines in the post itself, but you provided example code (limited to a concise 3 or 4 lines) AND you posted a link to more detailed information....

I'm not worthy....

Again, thanks alot for the help!

*Time that might be better spent working on important projects like The Jens Suessmeyer Annotated SQL Reference Guide

|||

Quite possibly the most helpful forum post I've ever clapped my ever so tired eyes on - thanks!

Craig