newbie: table versus view? where is the table

A view is the same as a query.

Also, many applications will use views in the same way that you can use a table, where the connection is made to the view and not the table.

> the DB I am working on
Which database?  SQL Server?

create view  [view Employees] as
select Employees.employee_number [Employee Number],
         Employees.first_name [First Name],
         Employees.last_name [Last Name],
         Employees.ss_number [SS Number]
from Employees

Based on the view creation statement, you should have a Employees table. Otherwise, the view will not be able to be queried. Your view name is actually 'view Employees'.

>Here's my question: where is the table?  Is there one?
yes, the table is what is mentionned in the FROM clause, ie Employees.

this view returns some columns of all the rows for that table, giving some new names to the columns (for the output, not changing the actual table column names)

views can be used
* to present data in a different way
* to give more granular permissions on data (hiding rows/columns)


see also stored functions which allows parameters to be passed to the query.

OK, I am not a complete idiot (as my wife would suggest), all the replies make sense.  

Here's what threw me: there is no "Employees" table under tables in Enterprise Manager!  There is employees_, but no employees (or Employees).  (all the other tables are lower case, and they are all duplicated with xxxxxname, and xxxxxname_

Is there a way to hide a table?

>Here's what threw me: there is no "Employees" table under tables in Enterprise Manager!
well, 2 things:
* running the view, does it return rows?
* possibly you have another VIEW called Employees ...

>Which database?  SQL Server?
Yes.

>* running the view, does it return rows?
Yes.

>* possibly you have another VIEW called Employees ...
No luck, but the same patter is duplicated under views (for every pair of tables (xyz and xyz_) there is a pair of views named "view Xyz" and "view Xyz_).  For tables, there is only employees_.  For views, there is "view Employees" and "view Employees_".

Thanks for trying.  This has gnawed at me for months.

What kind of rights do you have on the database?

>What kind of rights do you have on the database?

Pretty much unlimited as far as I know.  How to tell?

>You may have a hidden employee table or a compatibility view as described in this link:
>http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1089808,00.html

It's an older version of SQL (not 2005).  Enterprise Manager says 8.0.

If you have sysadmin or SA (same thing) then you can not have anything hidden from you.

In Query Analyzer, Can you run this query - SELECT * FROM employees

what does this query return:

select * from sysobjects where [name] = 'Employees'

>in Query Analyzer, Can you run this query - SELECT * FROM employees

Yes, and it differs slightly from opening the "view Employees".  There is a row_id column that is returned in this query, that does not show up in the Employees view results.

>what does this query return:
>select * from sysobjects where [name] = 'Employees'

bunch of stuff.  Basically a one row result with name/id/xtype/etc (employees, 1597509020, V, etc).

FWIW: In Query analyzer the situation I've described is replicated under the "User Tables" browser.  All the tables have "dbo." prefixed, but there are still two of most tables except employees, only "dbo.employees_".  And no, "employees" does not show up under the "System Tables" folder.

And on the 7th day, the decrepit man finally found the *other* view!

There are *three* views: "view Employees", "view Employees_", and now I see the one I have been missing, a view named "employees".  It's a long list, 98% of which starts with "view ....", so it was way up on top.  But in it, I see the queries to real tables.  Rubber, road, reality.

Thanks for the surf.  This had been gnawing at me.  Sorry I was missing it.