what is the difference between text and varchar in sql

Joma Rajab

10y14.6kSQL

I have problem with query when I use distinct I got text type cannot be selected as distinct

so what is the different if I change it to varchar

please help

Thanks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bikesh Srivastava10y ago
Accepted answer
This is difference,
The VARCHAR(MAX) type is a replacement for TEXT. The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.
Using the LIKE statement is identical between the two datatypes. The additional functionality VARCHAR(MAX) gives you is that it is also can be used with = and GROUP BY as any other VARCHARcolumn can be. However, if you do have a lot of data you will have a huge performance issue using these methods.
In regard to if you should use LIKE to search, or if you should use Full Text Indexing and CONTAINS. This question is the same regardless of VARCHAR(MAX) or TEXT.
If you are searching large amounts of text and performance is key then you should use a Full Text Index.
LIKE is simpler to implement and is often suitable for small amounts of data, but it has extremely poor performance with large data due to its inability to use an index.
0
Joma Rajab10y ago
Thanks guys
0
Midhun Tp10y ago
Hi,
text datatype was used to save larger amount of data in old versions of sql.
varchar(max) is the replacement to text. You can use varchar(max) if your column is going to store large amount of data or can simply use varchar with size.
References :-
https://msdn.microsoft.com/en-IN/library/ms187993.aspx
http://maneshjoseph.blogspot.in/2010/10/difference-between-varcharmax-and-text.html
http://stackoverflow.com/questions/834788/using-varcharmax-vs-text-on-sql-server
0
Manas Mohapatra10y ago
You can't apply DISTINCT on text type column in SQL. In order to resolve your problem:

1. Use CAST()

SELECT DISTINCT CAST(Subjects as varchar(max)) from T1

2. Change column to VARCHAR(MAX) type

The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.

The text datatype is deprecated and should not be used for new development work.

ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Note: If have the huge amount of records in your DB then it may create performance overhead when you are doing DISTINCT on varchar or Text type column.
0