Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-03-17
4
Medium Priority
?
594 Views
Last Modified: 2014-03-19
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

0
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 12

Accepted Solution

by:
Tony303 earned 400 total points
ID: 39936220
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
 
LVL 5

Assisted Solution

by:dannygonzalez09
dannygonzalez09 earned 300 total points
ID: 39937242
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 300 total points
ID: 39937279
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
 

Author Closing Comment

by:sqldba2013
ID: 39939248
--
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question