To create index on varcharcolumn

Question

To create index on varcharcolumn

raghu-sql

Mr or Mrs. 500

Points: 517
More actions
August 23, 2011 at 3:12 am

#390247

hi,
can we create an index on an a coloumn on datatype varchar/nvarchar having maximum size?
ex :
create table example
(
id int primary key identity(1,1),
name varchar(max)
)
create nonclustered index XL_example on example(name)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

FreeHansje SSChampion Points: 11751 More actions · Answer 1

Use of the index on a varchar will not be (much) slower, other things may be taken into consideration, like maintenance overhead, updates and deletes, diskspace and such. Here is a post on testing with a varchar index:

It's one page scrooll down, Lee Crain.

Greetz,
Hans Brouwer

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Not on it's own, no.

IF OBJECT_ID('tempdb..#example') IS NOT NULL

BEGIN

DROP TABLE #example

END

CREATE TABLE #example (id INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(MAX))

CREATE NONCLUSTERED INDEX IDX_NC_temp_example_name ON #example(name)

But you could use it in an "INCLUDE"

IF OBJECT_ID('tempdb..#example') IS NOT NULL

BEGIN

DROP TABLE #example

END

CREATE TABLE #example (id INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(MAX))

CREATE NONCLUSTERED INDEX IDX_NC_temp_example_name ON #example(id) INCLUDE(name)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

However if you do, the entire varchar(max) is duplicated.

There's a more basic issue here. Why is a name column up to 2 billion characters long? Surely a name is on the order of 50 at most?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

You may consider to use "CHECKSUM" method. It's inteded for buidling hash indexes, especially to improve indexing speed for indexing long character columns (as you have).

You can read more and find examples: http://msdn.microsoft.com/en-us/library/ms189788.aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

deepak.a SSCertifiable Points: 5330 More actions · Answer 5

The maximum key length is 900 bytes and if you create the nonclustered index that is having > 900 bytes. Index will create (expect non-key columns i.e., Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns) and any DML opertaion against that column will fail.