Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    103

    Unanswered: Can't create an index on a text field?

    Hi: I am very new to MSSQL, using SQL Server 2008 Express and SQL Server Management Studio to configure the tables. The front end is an Access project - but that's not really relevant - I think. Now, I can of course prevent duplication via the user interface, and I plan to do that anyway, but nonetheless, there are certain fields that I would like to create as indexes to prevent duplicates.

    I find that the tool will not allow me to do this with a text field. Is this a limitation of SQL, the management console, or am I simply missing it. When I try to setup the index, it shows all of my numeric fields but not my text fields.

    Any ideas on what I am missing?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, you cannot index a text column, nor ntext or image

    in addition, index columns may not exceed 900 bytes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    23
    ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

    And you can index a varbinary(max) column

    hope it helps
    jambu

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by kjambu
    And you can index a varbinary(max) column
    I can't image how an index on a binary column could work.

    Or did you mean varchar(max)?

  5. #5
    Join Date
    Nov 2008
    Posts
    23
    yess shammat, it is not easy, clean or straightforward and does not apply to all kinds of data
    Full-text indexes can be built on columns that contain formatted binary data, such as Microsoft Word documents, stored in a varbinary(max) or image column. You cannot use the LIKE predicate to query formatted binary data.

    etc etc etc conditions.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by kjambu
    Full-text indexes can be built on columns that contain formatted binary data, such as Microsoft Word documents, stored in a varbinary(max) or image column.
    OK, a fulltext index makes sense

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    apart from the fulltext index thing, the var*(max) types cannot be part of any index key.

    they can be included columns in an index though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •