SQL server - Could not create index on field Inqu – this is a varchar(Max) data type

Hi All,

I am getting below error while creating nonclustered index on table CP. Please advise how to fix below error.

Error: Could not create index on field Inqu – this is a varchar(Max) data type

Inqu column data type is varchar(max)
PO Number column datatype (varchar(50), PK)
CREATE NONCLUSTERED INDEX [_dta_index_CP_] ON [dbo].[CP]
(
                [Inqu] ASC,
                [PO Number] ASC
)
INCLUDE			([Sales Org],
                [Customer],
                [RevisionDate],
                [Status],
                [Product],
                [Comments],
                [Description],
                [Site Ids],
                [Region],
                [Ship to State],
                [Ship to City],
                [PO Date],
                [Received Date],
                [CPOA Register date],
                [Status Date],
                [Project Manager],
                [Responsible],
                [Ref 1],
                [Sales Order Numbers],
                [Network Numbers],
                [Acc Doc #],
                [Acc Doc Amount],
                [Acc Doc Date],
                [Type of PO],
                [Data Origin],
                [Created by],
                [Data Entry],
                [Modified by],
                [Date modification],
                [Company]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

Open in new window

sqldba2013Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Tony303Connect With a Mentor Commented:
Ooooh sorry,

Books Online.....

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.
0
 
dannygonzalez09Connect With a Mentor Commented:
That's right.. you cannot create indexes on columns with data type - Varchar(max)...

As an alternative, if the length of the values is less than 8000 characters. you might want to  change the data type to Varchar(8000) as varchar supports a max of 8000 characters only and then create an index
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
You're INCLUDEing most of the table, so I think it's time to step back and make sure you have the correct clustered index on the table to begin with.  If you want to pursue that, let me know.
0
 
sqldba2013Author Commented:
--
0
All Courses

From novice to tech pro — start learning today.