SQL Text vs VARCHAR

When to use VARCHAR and When to use TEXT

TEXT is used for large pieces of string data. If the length of the field exceeed a certain threshold, the text is stored out of row.
VARCHAR is always stored in row and has a limit of 8000 characters. If you try to create a VARCHAR(x), where x > 8000, you get an error:
Server: Msg 131, Level 15, State 3, Line 1
The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)
These length limitations do not concern VARCHAR(MAX) in SQL Server 2005, which may be stored out of row, just like TEXT.
Note that MAX is not a kind of constant here, VARCHAR and VARCHAR(MAX) are very different types, the latter being very close to TEXT.
In prior versions of SQL Server you could not access the TEXT directly, you only could get a TEXTPTR and use it in READTEXT and WRITETEXT functions.
In SQL Server 2005 you can directly access TEXT columns (though you still need an explicit cast to VARCHAR to assign a value for them).
TEXT is good:
  • If you need to store large texts in your database
  • If you do not search on the value of the column
  • If you select this column rarely and do not join on it.
VARCHAR is good:
  • If you store little strings
  • If you search on the string value
  • If you always select it or use it in joins.
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 TEXT or VARCHAR column. This includes using it in any JOIN or WHERE condition.
As the TEXT is stored out of row, the queries not involving the TEXT column are usually faster.
Some examples of what TEXT is good for:
  • Blog comments
  • Wiki pages
  • Code source
Some examples of what VARCHAR is good for:
  • Usernames
  • Page titles
  • Filenames
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 TEXT.
Otherwise use VARCHAR.

Comments

Post a Comment

Popular posts from this blog

What is the use of DBCC commands?

What is the use of DBCC commands? DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks. E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked. DBCC CHECKALLOC - To check that all pages in a db are correctly allocated. DBCC CHECKFILEGROUP - Checks all tables file group for any damage.
Read more

Google to FTS Syntax Cheat Sheet

Image
OPERATOR EXAMPLE DESCRIPTION   nut Searches for inflectional forms of the word nut crank arm crank AND arm Searches for documents containing inflectional forms of the words crank AND arm crank and ann. The keyword AND is optional. tire OR air Searches for documents containing inflectional forms of the words tire or air, “reflector bracket” Performs a phrase search for the phrase "reflector bracket". hardware -bracket Searches for documents containing inflectional forms of the word hardware but not the word bracket. +clamp Searches for the word darn') without generating inflectional forms. ~seat Searches for thesaurus forms of the word seat Assemb* Searches for words that begin with the prefix assemb . <washer nut> Searches for documents that contain the words washer in close proximity to the word nut
Read more

What is difference between DELETE and TRUNCATE commands?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command. TRUNCATE : TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log. TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE cannot be rolled back. TRUNCATE is DDL Command. TRUNCATE Resets identity of the table DELETE : DELETE removes rows one at a time...
Read more