SQL Server Performance

errors related views containing join

Discussion in 'Getting Started' started by TriumphKai, Mar 3, 2011.

  1. TriumphKai New Member

    I'm a novice in SQL and I'm trying to study the views. Initially I created a database called "university", on which I create two tables respectively called "Student" and "Grade". And now I want to create a view named "vw_Student" which can select data from the two underlying tables.
    the code is below: (I'm using SQL 2008)CREATE
    VIEW vw_StudentAS
    SELECT
    * FROM StudentINNER
    JOIN GradeON
    Student.Student_Id = Grade.Student_Id GO
    But after I run it, the following error shows below:Msg 4506, Level 16, State 1, Procedure vw_Student, Line 3
    Column names in each view or function must be unique. Column name 'Student_Id' in view or function 'vw_Student' is specified more than once.
    I hope someone could help me with this small problem! Thanks a lot!!
  2. Adriaan New Member

    If you do SELECT * with two or more tables in the FROM clause, then all columns from all tables will be shown. In a regular query, that is not really a problem.
    What SQL Server is telling you is that your two tables have a common column name. You are trying to create a view which will have two identical column names, and this is not possible in a view.
    So the first advice is to specify the exact columns that you need from both tables (always a good idea). In case you need to include two columns with identical names, just add a column alias to either one or both of them.
  3. TriumphKai New Member

    Thank you for your brief and excellent answer!

Share This Page