What
are the differences between TEXT and VARCHAR(MAX) [TEXT VS VARCHAR(MAX)]?
Which
data type should use for defining a column in a table [TEXT VS VARCHAR(MAX)]?
TEXT
|
VARCHAR(MAX)
|
||||||||||
Bytes
|
It consumes 1 byte per character.
It is present since
SQL Server 6.5 version.
|
It consumes 1 byte per character.
It is introduced in SQL Server 2005 |
|||||||||
Data Length
|
The maximum number of NON
UNICODE characters for this data type can hold unlimited by defining as TEXT.
Maximum storage size
is (2147483647) 2^31-1 bytes.(2 GB).
|
The maximum number of NON
UNICODE characters for this data type can hold unlimited by defining as
NVARCHAR(MAX).
MAX indicates that the
maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual
length of the data entered + 2 bytes.
|
|||||||||
Data
Storage
Type
|
Used to store variable
length value as String.
|
Used to store variable
length value as String.
|
|||||||||
Index
|
Index can’t be created
on TEXT data type if needed. It throws the exception error when we create an
index on this data type.
|
Index can’t be created
on VARCHAR(MAX) data type even though
it is needed. It throws the exception error when we create an index on this
data type.
|
|||||||||
Storage
|
SQL server will use the
value of TEXT data type that is stored in out of row in a separate LOB (Large Object) data pages. The row in the data table will only have
16 bytes pointer to LOB data pages where the actual data is stored.
Does not allow UNICODE
characters and content is variable size for storing.
Note:
Unicode Example:
(Japanese, Korean,
etc.)
NVARCHAR exactly occupies
2 bytes.
Changing Text Data Type Default Storage Behavior.
EXEC sp_tableoption
@TableNamePattern = 'dbo.Employee',
@OptionName =
'text in row',
@OptionValue =
7000
The @OptionValue parameter value can be:
0/OFF (Default Value): Text
type column value is stored out-of-row.
1/ON: Text Type Column
value is stored in-row as long as the Text type column value is less than or
equal to 256 bytes.
Integer value from 24
through
7000: specifies the
number bytes up to which the text type column value is stored in row.
|
SQL server will store
the value of VARCHAR(MAX) in a row if it more than 8000 bytes/characters then it
will store the value out of row.
i.e. It uses the
normal data pages until the content actually fills 8k of data. When overflow
happens on it, data would be stored as old TEXT Data type and a pointer is
replacing the old content.
Does not allow UNICODE
characters and content is variable size for storing.
Changing Text Data Type Default
Storage Behavior.
EXEC sp_tableoption
@TableNamePattern = 'dbo.Employee',
@OptionName =
'text out of row',
@OptionValue =
1
The @OptionValue parameter value can
be:
0/OFF (Default) : Varchar(Max) column
values are stored in-row as long as the value length is less than or equal to 8000 bytes and enough
space is available in row.
1/ON : VARCHAR(MAX) column values are
always stored out-of-row even though when enough space is available in row.
|
|||||||||
Query for Checking
whether the data is stored in “in a
row” OR “out of row”.
|
SELECT alloc_unit_type_desc,
page_count
FROM
sys.dm_db_index_physical_stats
(DB_ID('TestDB'),
OBJECT_ID('dbo.Employee_Text'),
NULL,
NULL , 'DETAILED')
|
SELECT alloc_unit_type_desc,
page_count
FROM
sys.dm_db_index_physical_stats
(DB_ID('TestDB'),
OBJECT_ID('dbo.TestVarMax'),
NULL,
NULL , 'DETAILED')
|
|||||||||
Use When?
|
Use TEXT data type
when content (data) size is variable, does not have any UNICODE characters and
/or NULL and content length is exceeds 8000 bytes/characters.
NOTE:
Microsoft is
suggesting to use VARCHAR(MAX) instead
of Text data type for storing the
large amount of data in a single column as it will be removed in a future versions of MS
SQL Server.
|
Use VARCHAR(MAX) data
type when content (data) size is variable, does not have any UNICODE
characters and/or NULL and content length is exceeds 8000 bytes/characters.
|
|||||||||
Optional Parameter
value
|
There is no optional parameter
value for TEXT data type.
|
The optional parameter value MAX [VARCHAR(MAX)]
is not specified in the variable declaration or column definition then it is
considered as 1.
DECLARE @VarChar varchar = 'VarChar'
PRINT DATALENGTH(@VarChar)
OUTPUT: 1
|
|||||||||
Data Conversion
|
For read, write or
update TEXT data using the SELECT, INSERT or UPDATE statements and special
statements like READTEXT, WRITETEXT and UPDATETEXT also can be
used.
|
You can
directly read, write or update VARCHAR(MAX) data using the SELECT, INSERT or
UPDATE statements no special statements READTEXT,
WRITETEXT and UPDATETEXT can be used.
|
|||||||||
Example
|
USE Pubs database
to perform the following examples.
CREATE TABLE [dbo].[Employee_Text](
[EmployeeID] [int] NOT NULL,
[EmployeeName] [varchar](50) NULL,
[EmployeeDetail] Text NULL,
CONSTRAINT
[PK_Employee_Text_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO dbo.Employee_Text VALUES
(1, 'Govindaraj
Kanniappan', 'Govindaraj
Kanniappan text details'),
(2,'Govindaraj
Anusha', 'Govindaraj
Anusha varchar text details')
SELECT alloc_unit_type_desc,
page_count
FROM
sys.dm_db_index_physical_stats
(DB_ID('TestDB'),
OBJECT_ID('dbo.Employee_Text'),
NULL, NULL , 'DETAILED')
SELECT EmployeeName,
EmployeeDetail FROM dbo.Employee_Text WITH (NOLOCK)
UPDATE dbo.Employee_Text SET EmployeeDetail =
'This is text data .....' WHERE EmployeeID =
2
DELETE dbo.Employee_Text WHERE
EmployeeDetail =
'This is text data .....'
Operator = and GROUP BY can’t be
used in TEXT data type, see the error below on DELETE
Error:
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are
incompatible in the equal to operator.
Reads the second
through twenty-sixth characters of the pr_info column in the pub_info table.
DECLARE @ptrval varbinary(16);
SELECT @ptrval = TEXTPTR(pr_info)
FROM
pub_info pr INNER JOIN
publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1
25;
Puts the text
pointer into the local variable @ptrval, and then WRITETEXT places the new
text string into the row pointed to by @ptrval.
DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr, publishers
p
WHERE p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten
publication.';
GO
Puts the text pointer into the local
variable @ptrval, and then uses UPDATETEXT to update a spelling error
DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(pr_info)
FROM
pub_info pr, publishers p
WHERE p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval
88 1 'b';
|
CREATE TABLE [dbo].[Employee_VarMax](
[EmployeeID] [int] NOT NULL,
[EmployeeName] [varchar](50) NULL,
[EmployeeDetail] [varchar](max) NULL,
CONSTRAINT
[PK_Employee_VarMax_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO dbo.Employee_VarMax VALUES
(1, 'Govindaraj
Kanniappan', 'Govindaraj
Kanniappan varchar max details'),
(2,'Govindaraj
Anusha', 'Govindaraj
Anusha varchar max details')
-- Read Varchar
Max data
SELECT EmployeeName,
EmployeeDetail FROM dbo.Employee_VarMax WITH (NOLOCK)
-- Update
Varchar Max data
UPDATE dbo.Employee_VarMax SET EmployeeDetail =
'This is varchar Max data .....' WHERE EmployeeID =
2
-- Delete
using Varchar Max data
DELETE dbo.Employee_VarMax WHERE
EmployeeDetail = 'This
is varchar Max data .....'
|
|||||||||
Performance
|
The VARCHAR(MAX) data
type is a replacement for TEXT data type.
LIKE statement is
identical between the two data types.
Additional
functionality = and GROUP BY can’t be used in TEXT data type but it can be on
VARCHAR(MAX) data type
|
||||||||||
Text is deprecated from SQL Server 2005.
ReplyDelete