SQL Server Performance

Error - A unique column must be defined on this table/view

Discussion in 'SQL Server 2005 General Developer Questions' started by WebEyeX, Sep 6, 2008.

  1. WebEyeX New Member

    I am trying to create a full text index on a view and getting following error 'A unique column must be defined on this table/view'.
    My tables are..
    And this is how i am creating view
    CREATE VIEW [dbo].[wjt_ProductSearch]
    SELECT dbo.wjt_Products.ProductID, dbo.wjt_Products.AddedDate, dbo.wjt_Products.AddedBy, dbo.wjt_Products.DepartmentID, dbo.wjt_Products.Title,
    dbo.wjt_Products.Description, dbo.wjt_Products.SKU, dbo.wjt_Products.UnitPrice, dbo.wjt_Products.DiscountPercentage,
    dbo.wjt_Products.UnitsInStock, dbo.wjt_Products.SmallImageUrl, dbo.wjt_Products.FullImageUrl, dbo.wjt_Products.Votes, dbo.wjt_Products.TotalRating,
    dbo.wjt_Departments.Title AS DepartmentTitle
    FROM dbo.wjt_Products INNER JOIN
    dbo.wjt_Departments ON dbo.wjt_Products.DepartmentID = dbo.wjt_Departments.DepartmentID
    I do not have much knowledge about T-SQL. I am unable to figure out where is the problem. Help will be really appreciated.
  2. preethi Member

    Have you defined a primary key/Unique key on DepartmentID on the wjt_Departments table?
  3. WebEyeX New Member

    [quote user="preethi"]
    Have you defined a primary key/Unique key on DepartmentID on the wjt_Departments table?
    Yes DepartmentID on wjt_Departments table is PK.
  4. preethi Member

    Sorry For misleading you by asking that question. It should be about dbo.wjt_Products.ProductID.

    To create a fulltext index on a view, you need to have a unique index created on the view. Please read abut Indexed view to make sure whether you can create an index on it. (There are lot of conditions come into play when it comes to indexed views. IF you haven't taken any precausions, you may endup in recreating the base tables in order to create the indexed view) If not, create the full text indexes of the base tables and use them.
    Hope this helps
  5. WebEyeX New Member

    Thanks bro....I have just read an article on msdn related to Indexed view. To be honest it looks tough to me because i do not have much T-SQL knowledge. Most of the terms were unknown to me on that article. So i decided to drop the idea of using view and as you said i am using full text index directly on the table now. Next Sunday i will go through this article again and try to create indexed view for my requirement.
    Thanks again....

Share This Page