I have variable length character data and want to store in SQL Server (2005) database. I want to learn some best practices about how to choose TEXT SQL type or choose VARCHAR SQL type, pros and cons in performance/footprint/function.
closed as not constructive by casperOne♦ May 7 '12 at 18:21
As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. If this question can be reworded to fit the rules in the help center, please edit the question.
If you're using SQL Server 2005 or later, use
These length limitations do not concern
In prior versions of SQL Server you could not access the
In SQL Server 2005 you can directly access
By selecting here I mean issuing any queries that return the value of the column.
By searching here I mean issuing any queries whose result depends on the value of the
Some examples of what
Some examples of what
As a rule of thumb, if you ever need you text value to exceed 200 characters AND do not use join on this column, use
P.S. The same applies to
P.P.S. The same applies to
As mentioned above and here,
In SQL server 2005 new datatypes were introduced:
Also, varchar(max) is stored in the table's (disk/memory) space while the size is below 8Kb. Only when you place more data in the field, it's is stored out of the table's space. Data stored in the table's space is (usually) retrieved quicker.
In short, never use Text, as there is a better alternative: (n)varchar(max). And only use varchar(max) when a regular varchar is not big enough, ie if you expect teh string that you're going to store will exceed 8000 characters.
As was noted, you can use SUBSTRING on the TEXT datatype,but only as long the TEXT fields contains less than 8000 characters.
There has been some major changes in ms 2008 -> Might be worth considering the following article when making a decisions on what data type to use. http://msdn.microsoft.com/en-us/library/ms143432.aspx