View Vs Table

Archived Topics
1

I was wondering if someone could clarify something for me.

  1. What is the difference between a view and an actual table?

  2. Does your software work with views?

  3. Do you need any special or additional commands when using views versus using tables?

  4. For both PMI and SQL is the settings the same, ie:port, etc. when working with views and tables?

  5. If I understand our guy who does the database for our time clocks the view would still be in the same instance as the table we currently use.

h

2

I answered your question on PLCS.net too: For questions 1-3:

A view is basically a saved SQL query, whereas tables are objects that store data. Views are typically used for complex queries such as JOINs from multiple tables or a SELECT query that has a lot of conditions.

Suppose we have a table things that contains 1000 rows of the columns: id, name, type, and description. I might then create a view called nathansFavoriteThings that returns 20 rows based on in-con-ceivably complex conditions that I’ve spent a lifetime optimizing.

You, as a user, would query against my view like a table:

SELECT * FROM [nathansFavoriteThings] WHERE type='food'

A few reasons you might want to use a view:

  1. Hide the complexity and modify your query in fewer places
  2. Use it as a “security tool”
  3. “Flatten” out multiple tables

I’m pretty sure that FactoryPMI sees views as tables when query browsing. You can certainly type your own queries that include views - it just passes that on to the SQL database. Views don’t really make sense for FactorySQL although they would work in an “Action Item” query.

Views are mostly about reading data from the SQL database. The writing analog would be prepared statements, which are precompiled queries, usually INSERT or UPDATE, that accept parameters. Prepared statements are usually only necessary (significantly advantageous) for very specialized applications. FactorySQL supports those as well.

4. You don’t need to change any settings to use views. They’re just like querying tables.

5. Correct, views will be accessible like tables. A SQL Server instance is basically a single running copy of the “engine”, which can support multiple databases. Your views will likely be peers to your tables on the same database of the same instance. You probably don’t need to be thinking of instances, or even separate databases for a given project.