Size Limits of Sql Server Indexed columns

FYI, there is a limit on the size of a column that can be used for an index with Sql Server (and I assume other databases).  With Sql Server, a column over 900 bytes in size can not be indexed.  I would assume that the total number of bytes for an index can not be over 900 bytes, but I am not sure on that.  I tried to index my UrlAddress field, which is defined as varchar(4096) and I got a nice message box saying that this was not possible.

Wally

8 Comments

  • Yes, its 900 bytes per index ... why would you make the column that big? I believe there is a limit of around 256 characters for most servers. Make it varchar(512) just in case i'm wrong ... then your index would fit fine ... but of course its better to have smaller values for indexes .... you might want to prep a free-text search index on that column instead ...

  • Adam,



    Sorry, but you would have had to have followed my posts about Searching the Web. The problem is that a URL can easy be over varchar(256) when you include the script path, filename, and query string parameters. I thought about the full-text search option but didn't go that way do to the time required to fill a full-text search index and I need the index to be available in pretty close to real time. :-)



    Wally

  • Perhaps you could add a computed column that is only varchar(900) and just returns that much of the other field, and then try to creat an index on that.

  • Jim,



    I did one better than that. I created a hash of the UrlAddress column of type bigint and now I use the for my search also. It's not a great hash, but it works for this.



    Wally

  • Indexes can make a HUGE difference on database performance. I remember reading a case study where a query without an index took HOURs and with an index took SECONDs. Really, it can make **that big of a difference.**



    So I would HIGHLY recommend that you try to fit your URL into 900 bytes somehow. Your hash technique is clever, but it adds another computation that needs to be done. Also, where do you do the hash? See, with an index you could run numerous COUNT(*) queries that could take advantage of the index, but with a hash, you can't see that advantage.



    I recommend learning a bit more about indexes and their application and the theory behind them. I think it would be research time well spent. :-)

  • Scott,



    That's a good suggestion, but the problem is that I am walking (spidering) the web and I can't control the link of the URLs that I recieve.



    Wally

  • Just out of curiosity, what's the longest URL you have in your database? How many characters? What is it?

  • Funny cooincidence that your longest URL is 256 chars :D

Comments have been disabled for this content.