2nd Tip :
In SQL Server 2000, the row size is limited to 8060 bytes of physical data stored in the database, which means if want to keep data of size more than 8060 bytes in one row then you can’t.
But this row size doesn’t apply to TEXT, i.e TEXT is always stored outside row (means you can keep data more than 8060 characters using TEXT data type) but VARCHAR is always stored inside row and the max limit is 8000 characters. That means either we have to divide the table into smaller parts or we have to use TEXT data type.
Then a Question may come to your mind, why not use TEXT datatype everywhere we want, is there anything wrong with it?
And the answer is YES there are some problems with TEXT datatype in SQL SERVER. The problems (and some of their solutions) are –
- We can’t use TEXT data type in any aggregate function, like COUNT().
- But you can cast TEXT data type to VARCHAR and call aggregate function on them like – COUNT(CAST([COLUMN_NAME] AS VARCHAR(50))).
- We can’t compare TEXT data type using ‘ = ‘.
- Instead of comparing TEXT data type using ‘ = ‘, we can use LIKE or IS NULL.
- We can’t use DISTINCT keyword for TEXT data type.
- We can’t GROUP BY any TEXT data type column.
- We can’t use TEXT data type in UNION clause.
- Because UNION uses DISTINCT keywords by default, so instead of using UNION we can use UNION ALL for TEXT data types.
- TEXT data type is obsolete in SQL Server’s higher version.
So, taking TEXT as data type for a column has some disadvantages but if we have no other choices then its better to handle TEXT data type properly in queries.