none
nvarchar(n) and nvarchar(max) different datatypes?

    Question

  • Hi Everyone,

    I recently tried what I thought should have been a simple operation:

    alter table MY_TBL alter column COL1 nvarchar(max) --currently nvarchar(500)

    to my surprise it complained about indexes containing this column:

    ALTER TABLE ALTER COLUMN COL1 failed because one or more objects access this column.

    double checking BOL, it seemed like this shouldn't have occurred. Quote from BOL:

    The modified column cannot be any one of the following:

    ...

    Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, the new size is equal to or larger than the old size, and the index is not the result of a PRIMARY KEY constraint.

    a bit more digging, and I found some people saying that nvarchar(n) and nvarchar(max) were two different datatypes, however BOL makes no such claims:

    http://msdn.microsoft.com/en-us/library/ms186939.aspx

    can anyone point me in a right direction? Are they two different datatype? If so where is this documented, and if they are not why does the alter statement fail?

    TIA,

    Mordechai


    Mordechai Danielov

    Friday, June 28, 2013 7:59 AM

Answers

  • you call it an "internal data type change". That's precisely what I'm asking. Where can I see this distinction? is it documented?

    I think the Books Online should be clearer in this regard.  The MAX data types are the same from a T-SQL perspective but treated differently in the storage engine because values may or may not be stored in-row.

    Below is an example script that shows how physical storage is affected by a change from varchar(n) to varchar(MAX).  The StringData column here is changed to BLOB Inline Data as shown by the page dump.  The implication is that every row and page in the table must be updated during the operation.

    USE tempdb;
    GO
    
    CREATE TABLE dbo.Example(
    	ExampleID int NOT NULL IDENTITY
    		CONSTRAINT PK_Example PRIMARY KEY CLUSTERED
    	,StringData varchar(10) NOT NULL 
          ); 
    INSERT INTO dbo.Example (StringData) values ('XXXXXXXXXX'); 
    GO
    
    CREATE PROC #usp_DumpExamplePage
    --this proc will dump first (and only) page of dbo.Example
    AS
    SET NOCOUNT ON;
    
    DECLARE @dbcc_results TABLE(
          PageFID     int
          ,PagePID int
          ,IAMFID int
          ,IAMPID int
          ,ObjectID int
          ,IndexID int
          ,PartitionNumber int
          ,PartitionID bigint
          ,iam_chain_type varchar(20)
          ,PageType int
          ,IndexLevel int
          ,NextPageFID int
          ,NextPagePID int
          ,PrevPageFID int
          ,PrevPagePID int
          );
    
    DECLARE 
          @DBID int
          ,@ObjectID int
          ,@PagePID int
          ,@PageFID int;
          
    --get database and object_id needed for DBCC IND
    SELECT
          @DBID = DB_ID()
          ,@ObjectID = OBJECT_ID(N'dbo.Example');
          
    INSERT INTO @dbcc_results
          EXEC sp_executesql N'DBCC IND(@DBID, @ObjectID, 1, 1) WITH NO_INFOMSGS;'
                ,N'@DBID int, @ObjectID int'
                ,@DBID
                ,@ObjectID = @ObjectID;
                
    --get file and page id of first (and only) page of this table         
    SELECT
          @PageFID = PageFID
          ,@PagePID = PagePID
    FROM @dbcc_results
    WHERE PageType = 1;
    
    --return DBCC PAGE output to client
    DBCC TRACEON(3604) WITH NO_INFOMSGS; 
    
    --dump page image
    DBCC PAGE(@DBID, @PageFID, @PagePID, 3) WITH NO_INFOMSGS; 
    
    --don't return DBCC output to client
    DBCC TRACEOFF(3604) WITH NO_INFOMSGS; 
    
    RETURN;
    GO
    
    --dump before page image
    EXEC #usp_DumpExamplePage;
    GO
    
    --change column to varchar(MAX)
    ALTER TABLE dbo.Example
    	ALTER COLUMN StringData varchar(MAX) NOT NULL;
    GO
    
    --dump after page image
    --  shows old StringData column is dropped
    --  shows new StringData column is now [BLOB Inline Data]
    EXEC #usp_DumpExamplePage;
    GO
    
    --rebuild table
    ALTER INDEX PK_Example ON dbo.Example REBUILD;
    GO
    
    --dump final page image
    --  shows old StringData column is now physically removed
    --  shows StringData column is [BLOB Inline Data]
    EXEC #usp_DumpExamplePage;
    GO
    
    --cleanup
    DROP TABLE dbo.Example;
    DROP PROC #usp_DumpExamplePage;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, June 29, 2013 12:45 PM

All replies

  • Hi Mordechai,

    Although I didn't find any documentation for it , but if you check it practically , then you will find bydefaul when you define any column as nvarchar(max) , its size would be set to -1 , which is less then the size of any nvarchra(value) columns length. Regarding difference in data type , so there is no difference in between nvarchar(value) and n varchar(max).

    http://msdn.microsoft.com/en-us/library/ms186939.aspx

    Here is a sample


    • Edited by HPS_PRS Friday, June 28, 2013 8:14 AM updated
    Friday, June 28, 2013 8:12 AM
  • Hi,

    We can not alter the index dependent columns: Try below code.

    CREATE TABLE TEST_ALTER
    (
    ID INT , NAME NVARCHAR(500)
    )
    --
    SELECT * FROM TEST_ALTER
    
    -- ALTER A COLUMN
    ALTER TABLE TEST_ALTER ALTER COLUMN NAME NVARCHAR(MAX)    -- IT WILL ALTER A COLUMN FINE.
    
    -- CREATE A CLUSTERED INDEX ON ID COLUMN
    CREATE CLUSTERED INDEX INDEX_TEST_ALTER_ID ON TEST_ALTER(ID)
    
    --DROP INDEX ID_TEST_ALTER_NAME ON TEST_ALTER
    ALTER TABLE TEST_ALTER ALTER COLUMN ID NVARCHAR(MAX) 
    
    -- FOLLWOING IS THE ERROR MESSAGE
    MSG 5074, LEVEL 16, STATE 1, LINE 1
    THE INDEX 'INDEX_TEST_ALTER_ID' IS DEPENDENT ON COLUMN 'ID'.
    MSG 4922, LEVEL 16, STATE 9, LINE 1
    ALTER TABLE ALTER COLUMN ID FAILED BECAUSE ONE OR MORE OBJECTS ACCESS THIS COLUMN.

    Thanks.


    bala krishna

    Friday, June 28, 2013 8:31 AM
  • Hi Morcechai,

    This is a tricky question. NVARCHAR(n) and NVARCHAR(MAX) are the same data type but do not behave the same. Thus it is as if they were 2 different data types. For all intents and purposes NVARCHAR(MAX) is a BLOB.

    In a nutshell, it appears that you cannot use a NVARCHAR(MAX) in an index using SQL Server 2012 SP1.

    Of course, the idea of indexing an NVARCHAR(MAX) has not crossed my mine until know, but don't use them very often.

    CREATE TABLE Test123 (COL1 NVARCHAR(255) NOT NULL)
    GO
    INSERT INTO Test123(COL1)
    VALUES('AAAAAAA'),('BBBBBBBB'),('CCCCCC')
    GO
    SET ANSI_PADDING ON
    GO
    CREATE NONCLUSTERED INDEX [IX_Test123_COL1] ON [dbo].[Test123]
    (
    	[COL1] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    GO
    

    If I try

    ALTER TABLE Test123 ALTER COLUMN COL1 NVARCHAR(MAX)

    It will fail with the message you mentioned but if I then drop the index, alter the column and try to recreate the index, index creation will fail with the following error "Msg 1919, Level 16, State 1, Line 2 Column 'COL1' in table 'dbo.Test123' is of a type that is invalid for use as a key column in an index."

    DROP INDEX [Test123].[IX_Test123_COL1]
    GO
    ALTER TABLE Test123 ALTER COLUMN COL1 NVARCHAR(MAX)
    GO
    SET ANSI_PADDING ON
    GO
    CREATE NONCLUSTERED INDEX [IX_Test123_COL1] ON [dbo].[Test123]
    (
    	[COL1] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    GO


    Kind regards M

    Friday, June 28, 2013 8:33 AM
  • In a nutshell, it appears that you cannot use a NVARCHAR(MAX) in an index using SQL Server 2012 SP1

    An add on on this, the same behaviour on SQL server 2008 R2. You cannot create an index on an NVARCHAR(MAX). BOL should be more clear about this.

    Again, not that I would index an NVARCHAR(MAX)


    Kind regards M

    Friday, June 28, 2013 8:40 AM
  • Hi bala,

    it is not entirely true that index dependent columns can not be changed. The quote from BOL that I gave states as much. You can also test for yourself:

    if col1 is nvarchar(500)

    alter table MY_TBL alter column col1 nvarchar(2000)

    will work fine.


    Mordechai Danielov

    Friday, June 28, 2013 9:10 AM
  • Hi M,

    thanks for pointing out that this is a tricky question. :) I was vexed by BOL ignoring the differences and making it sound like (max) is just a way of saying very large. I found this : http://msdn.microsoft.com/en-us/library/a1904w6t(v=vs.80).aspx interestingly enough in .NET 2.0 documentation. It seems to document the observed behavior better.


    Mordechai Danielov

    Friday, June 28, 2013 9:20 AM
  • Even the index on column with data type nvarchar(n) has limitation, because the limitation for Index Key size is 900bytes.

    The below script successfully creates an index with a warning "Warning! The maximum key length is 900 bytes. The index 'ix_3' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail."

    create table test_hunt1(c1 nvarchar(4000))
    
    create index ix_3 on test_hunt1(c1)

    If the index column is modifed with data more than 900 bytes, the operation fails.

    insert into test_hunt1 select REPLICATE('a',400)--successful insert
    go
    insert into test_hunt1 select REPLICATE('b',500)--failure

    The reason Nvarchar(max) is not applicable is , it is BLOB and its storage pattern differs.

    Also, choosing large datatypes for index key, would increase the size of B-tree, be cautious.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, June 28, 2013 9:23 AM
  • Hi Mordechai,

    You are welcomed. The BOL entry you quoted - http://msdn.microsoft.com/en-us/library/ms186939.aspx - is really poor.

    The .NET documentation is certainly more accurate.

    It should state that NVARCHAR(MAX) cannot be used in indexes, not even in the INCLUDE clause, and should clarify what this this means:

    "max indicates that the maximum storage size is 2^31-1 bytes (2 GB)"

    If a NVARCHAR(MAX) is 4,000 characters long, I would expect the column to be, give or take, a maximum of 8,000 bytes long, not 2 GB. How and why 2GB  is the maximum storage I don't know and would like to know.

    P.S.: Apologies for the usage of bold and underlining but I find fascinating that nobody that has replied to your post yet (10:50 GMT), has acknowledged it is impossible to index  a NVARCHAR(MAX) column. There quite a few posts out there suggesting it can be done.


    Kind regards M

    Friday, June 28, 2013 9:51 AM
  • Hi Sarat,

    creating an index was not really an issue for me. However ugly, there is a workaround for the index problem:

    create table My_TBL (col1 int, col2 nvarchar(max))
    create nonclustered index IX_test on my_tbl (col1) include (col2)

    I was looking for a place where large value data types are explicitly documented and their differences with their smaller cousins are explained. Such as, for example a link that I posted earlier ( http://msdn.microsoft.com/en-us/library/a1904w6t(v=vs.80).aspx).


    Mordechai Danielov

    Friday, June 28, 2013 10:00 AM
  • Hi Sarat,

    creating an index was not really an issue for me. However ugly, there is a workaround for the index problem:

    create table My_TBL (col1 int, col2 nvarchar(max))
    create nonclustered index IX_test on my_tbl (col1) include (col2)

    I was looking for a place where large value data types are explicitly documented and their differences with their smaller cousins are explained. Such as, for example a link that I posted earlier ( http://msdn.microsoft.com/en-us/library/a1904w6t(v=vs.80).aspx).


    Mordechai Danielov

    What i tried to say is even though one can create index against large column types , it is not a good practice.

    The below link doesn't have much about the datatypes, but has details about the INDEX limitations.

    http://msdn.microsoft.com/en-us/library/ms191241(v=sql.105).aspx , hope this helps.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, June 28, 2013 10:51 AM
  • it is not entirely true that index dependent columns can not be changed. The quote from BOL that I gave states as much. You can also test for yourself:

    if col1 is nvarchar(500)

    alter table MY_TBL alter column col1 nvarchar(2000)

    will work fine.

    It is true that one can alter the column if the data type is not changed.  But Mordechai is changing the nvarchar(500) to nvarchar(MAX), which is an internal data type change.  The operation will fail if an index on the column exists.

    CREATE TABLE dbo.MY_TBL(
    	col1 nvarchar(500)
    	);
    CREATE INDEX idx_MY_TBL_col1 ON dbo.MY_TBL(col1);
    GO
    ALTER TABLE dbo.MY_TBL ALTER COLUMN col1 nvarchar(MAX);
    GO

    Msg 5074, Level 16, State 1, Line 1
    The index 'idx_MY_TBL_col1' is dependent on column 'col1'.
    Msg 4922, Level 16, State 9, Line 1
    ALTER TABLE ALTER COLUMN col1 failed because one or more objects access this column.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, June 28, 2013 11:34 AM
  • varchar(max) is a special type, they are treated as LOBs. 

    The fact you cannot create an index on varchar(max) is clearly stated in BOL under CREATE INDEX

    Please see:

    http://msdn.microsoft.com/en-us/library/ms188783.aspx

    Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index.

    Friday, June 28, 2013 1:32 PM
    Moderator
  • Hi Dan,

    you call it an "internal data type change". That's precisely what I'm asking. Where can I see this distinction? is it documented?


    Mordechai Danielov

    Friday, June 28, 2013 1:43 PM
  • Absolutely, but correcting myself you can Include them in an index.


    Kind regards M

    Friday, June 28, 2013 2:11 PM
  • The "max" keyword changes the data into a "Large Value Data Type".

    Please see:

    http://msdn.microsoft.com/en-us/library/ms178158(SQL.100).aspx

    Friday, June 28, 2013 3:07 PM
    Moderator
  • Thanks Tom, but articles like this are the very source of my confusion. I quote:

    "Large-value data types exhibit the same behavior as their smaller counterparts, varchar(n)nvarchar(n)varbinary(n)."

    notice "the same behavior" claim.


    Mordechai Danielov

    Friday, June 28, 2013 3:45 PM
  • you call it an "internal data type change". That's precisely what I'm asking. Where can I see this distinction? is it documented?

    I think the Books Online should be clearer in this regard.  The MAX data types are the same from a T-SQL perspective but treated differently in the storage engine because values may or may not be stored in-row.

    Below is an example script that shows how physical storage is affected by a change from varchar(n) to varchar(MAX).  The StringData column here is changed to BLOB Inline Data as shown by the page dump.  The implication is that every row and page in the table must be updated during the operation.

    USE tempdb;
    GO
    
    CREATE TABLE dbo.Example(
    	ExampleID int NOT NULL IDENTITY
    		CONSTRAINT PK_Example PRIMARY KEY CLUSTERED
    	,StringData varchar(10) NOT NULL 
          ); 
    INSERT INTO dbo.Example (StringData) values ('XXXXXXXXXX'); 
    GO
    
    CREATE PROC #usp_DumpExamplePage
    --this proc will dump first (and only) page of dbo.Example
    AS
    SET NOCOUNT ON;
    
    DECLARE @dbcc_results TABLE(
          PageFID     int
          ,PagePID int
          ,IAMFID int
          ,IAMPID int
          ,ObjectID int
          ,IndexID int
          ,PartitionNumber int
          ,PartitionID bigint
          ,iam_chain_type varchar(20)
          ,PageType int
          ,IndexLevel int
          ,NextPageFID int
          ,NextPagePID int
          ,PrevPageFID int
          ,PrevPagePID int
          );
    
    DECLARE 
          @DBID int
          ,@ObjectID int
          ,@PagePID int
          ,@PageFID int;
          
    --get database and object_id needed for DBCC IND
    SELECT
          @DBID = DB_ID()
          ,@ObjectID = OBJECT_ID(N'dbo.Example');
          
    INSERT INTO @dbcc_results
          EXEC sp_executesql N'DBCC IND(@DBID, @ObjectID, 1, 1) WITH NO_INFOMSGS;'
                ,N'@DBID int, @ObjectID int'
                ,@DBID
                ,@ObjectID = @ObjectID;
                
    --get file and page id of first (and only) page of this table         
    SELECT
          @PageFID = PageFID
          ,@PagePID = PagePID
    FROM @dbcc_results
    WHERE PageType = 1;
    
    --return DBCC PAGE output to client
    DBCC TRACEON(3604) WITH NO_INFOMSGS; 
    
    --dump page image
    DBCC PAGE(@DBID, @PageFID, @PagePID, 3) WITH NO_INFOMSGS; 
    
    --don't return DBCC output to client
    DBCC TRACEOFF(3604) WITH NO_INFOMSGS; 
    
    RETURN;
    GO
    
    --dump before page image
    EXEC #usp_DumpExamplePage;
    GO
    
    --change column to varchar(MAX)
    ALTER TABLE dbo.Example
    	ALTER COLUMN StringData varchar(MAX) NOT NULL;
    GO
    
    --dump after page image
    --  shows old StringData column is dropped
    --  shows new StringData column is now [BLOB Inline Data]
    EXEC #usp_DumpExamplePage;
    GO
    
    --rebuild table
    ALTER INDEX PK_Example ON dbo.Example REBUILD;
    GO
    
    --dump final page image
    --  shows old StringData column is now physically removed
    --  shows StringData column is [BLOB Inline Data]
    EXEC #usp_DumpExamplePage;
    GO
    
    --cleanup
    DROP TABLE dbo.Example;
    DROP PROC #usp_DumpExamplePage;
    GO
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, June 29, 2013 12:45 PM