Creating Index on Large Text columns


/ Published in: SQL
Save to your folder(s)

Creating index on huge text columns


Copy this code and paste it in your HTML
  1. CHECK this link FOR more information: http://www.mssqltips.com/tip.asp?tip=1868
  2.  
  3. Creating INDEX ON huge text COLUMNS:
  4. INDEX will be limited TO ONLY 900 characters so try TO CREATE ON 900 FIRST 900 bytes (UNIQUE CONSTRAINT OR INDEX)
  5.  
  6. --Create the table
  7. IF OBJECT_ID('dbo.Unique900byte', 'U') IS NOT NULL
  8. DROP TABLE dbo.Unique900byte
  9. GO
  10. CREATE TABLE dbo.Unique900byte (
  11. ReallyLongText VARCHAR(1000) NOT NULL
  12. , Unique_ReallyLongText AS SUBSTRING ( ReallyLongText, 1, 900)
  13. , CONSTRAINT UC_Unique900byte_Unique_ReallyLongText UNIQUE (Unique_ReallyLongText))
  14. GO
  15. --Insert long text
  16. INSERT dbo.Unique900byte SELECT REPLICATE('A', 900)+ 'This is just a filler data'
  17. INSERT dbo.Unique900byte SELECT REPLICATE('B', 900)+ 'This is just a filler data'
  18. --Check the data
  19. SELECT * FROM dbo.Unique900byte

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.