Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: How to create unique field or sequence in view

    Hi,

    I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view. Please help explain the steps to achieve this. Giving example or link will be very helpful for me as I am a newbie in mssql.

    Thanks.

    Asif

  2. #2
    Join Date
    Sep 2010
    Posts
    153
    Quote Originally Posted by asifisyed View Post
    Hi,

    I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view. Please help explain the steps to achieve this. Giving example or link will be very helpful for me as I am a newbie in mssql.

    Thanks.

    Asif

    Go through the basics which says, we collect column names from table(s) to create views. It's not possible to add extra column.

    If you need a unique combination then i would suggest you to create an index on composite columns of views i.e if there are 3 columns in views which make data unique then create an index on these views' columns.

    You cannot create normal index on views. We have a solution called "Indexed view". Check or try to create index on a view (for sql 2005 or higher version)

    Create Indexed Views

  3. #3
    Join Date
    Sep 2010
    Posts
    153
    Quote Originally Posted by asifisyed View Post
    Hi,

    I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view. Please help explain the steps to achieve this. Giving example or link will be very helpful for me as I am a newbie in mssql.

    Thanks.

    Asif

    Morev, Remember that views are temporary tables. They do not contain data. If you're thinking of sequencial number or auto-incremental number, how would you delete them?

    Creating an indexed view will do, i feel else I would suggest you to create a lookup_table and insert data into that table by having an identity or sequential field.


    Thanks

  4. #4
    Join Date
    Jun 2012
    Posts
    5

    How to create unique field or sequence in view

    Sorry, I have 4 tables. Say Table a ,b,c,d. In our view. sometimes table a, b,c are unique and sometimes a,b,d are unique. Tables must be join using Left join. That is why I was looking for some solution to have a unique sequence in the view.

    Thanks.

    Asif

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Getting a unique ID is easy, just use row_number():
    Code:
    select a.*,
           row_number() over (order by a.id) as overall_id
    from a
      left join b on a.id = b.aid
      left join c on b.id = c.bid
      left join d on c.id = d.cid
    But I don't think that can be indexed.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Row_Number will create a sequence.
    You can also use new_id() to create a GUID on the fly.
    Or, you can construct a unique column as a composite of the natural keys of the source tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    WHY do you must need it?

    What value will some arbitrary "Unique" value give you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you explain what you are trying to do in "real world" terms instead of using database terms? I suspect that there is some clean, easy answer but that answer may lie outside of your experience. Someone may be able to offer you an elegant solution if you describe what you want to accomplish in the real world instead of describing the problem that you are having in the database.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2012
    Posts
    5

    How to create unique field or sequence in view

    Thank you so much for attention. I am using MSSQL 2008 R2.

    All I want is to create index on my view. However, because of the complex join of tables view cannot have primary key even combining multiple field as mentioned above in my posting.

    Unique combinations are table (aId,bId,cId) or (aId,bId,dId). Because it's a view it is also possbile that only data of Table a exists but table b,c,or d datas have not been created.

    I am running report based on this view and I want to create index in view fields. That is why I was thinking to have a field unique which can be made as clustered unique key and be able to create nonclustered index on my desired field in the view.

    Life is pretty complex!!

    Cheers!!

    asif

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    WHY do you want to create the index?
    Performance reasons?
    Complex constraints?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jun 2012
    Posts
    5
    Yes, performance reason. I expect if I can create index on search
    Criteria field , report will run fast.

    Thanks.

    Asif

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, but I hope you've tried addressing the problem but indexing the columns in the underlying tables first...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jun 2012
    Posts
    5
    Obviously, that has already been done. But Does it mean there is no need to create indexes on view.

    Thanks.

    asif

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    LOL!
    I've learned not to assume that anything "obvious" has been done.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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