SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Unique Index

  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unique Index

    Hi All
    I am trying to creat a unique index, however I get an error when I try to compile the following statement. What am I doing wrong?

    CREATE UNIQUE CLUSTERED INDEX House_Hold_ind
    ON TempTbl Upper(Substring(lname,1,10) + Substring(ad1,1,1) +
    Substring(Zip,1,3))


    Thanks
    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    according to the manual, you need to use column names, not expressions (and they need to be in parentheses)

    you'll probably have to declare a view and define the index on that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    according to the manual, you need to use column names, not expressions
    err, not quite correct, according to this manual.
    See the section entitled "Considerations when indexing computed columns and views".

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that was the page i was looking at --
    CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) ...
    if an expression were valid, it would say expression, not column

    i believe an expression is different from a computed column


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hehe, yep, you are right, I was wrong !


Bookmarks

Posting Permissions

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