Return to Snippet

Revision: 31987
at September 16, 2010 22:42 by hairajeshk


Initial Code
Check this link for more information: http://www.mssqltips.com/tip.asp?tip=1868

Creating index on huge text columns:
Index will be limited to only 900 characters so try to create on 900 first 900 bytes (unique constraint or index)

--Create the table
IF OBJECT_ID('dbo.Unique900byte', 'U') IS NOT NULL
   DROP TABLE dbo.Unique900byte
GO
CREATE TABLE dbo.Unique900byte (
   ReallyLongText VARCHAR(1000) NOT NULL
   , Unique_ReallyLongText AS SUBSTRING ( ReallyLongText, 1, 900) 
   , CONSTRAINT UC_Unique900byte_Unique_ReallyLongText UNIQUE (Unique_ReallyLongText))
GO
--Insert long text 
INSERT dbo.Unique900byte SELECT REPLICATE('A', 900)+ 'This is just a filler data'
INSERT dbo.Unique900byte SELECT REPLICATE('B', 900)+ 'This is just a filler data'
--Check the data
SELECT * FROM dbo.Unique900byte

Initial URL
http://www.mssqltips.com/tip.asp?tip=1868

Initial Description
Creating index on huge text columns

Initial Title
Creating Index on Large Text columns

Initial Tags

                                

Initial Language
SQL