Reasons for using SQL Views instead of SQL Tables

stiasta

I would like to have a discussion about alle the reasons for using a SQL view instead of SQL table (Normal in NAV).
Also, it would be great to name the pros and cons of using a view.

The reason i could think of using a SQL view is that
- it could give a performance boost for really big tables.
- it can be created from a subset of one or many tables
- does not hold the data itself, therefore it does not take up much space.

More reasons?

kriki

-can be slow because the view is executed at the moment and (it can be a complicated query).
+To speed up the slow view, you can create an indexed view on it.
+generally easy to change something in the query without changing the table (if you don't change the type of the fields or adding/removing fields).
-native restore (if you do a native backup for some reason) can give problems. You must first create the view.
+you select only the fields you really need, so it is possible to create good indexes on the real tables the view is using, speeding up performance.


Another possibility is to create a stored procedure and run it and then read the data from it using ADO.
+the logic to get the data can be a lot more complicated than with views
-you need to run the stored procedure and then receive the data using ADO.
+native restore will not give problems (but running the query will if you didn't create the stored procedure).

GRIZZLY

I think the main reason why developers could use views is the transferring of data from NAV to another DB, probably located on the other SQL Server.
I used this kind of integration when sending SMS (there was a separate DB with a special service) and batch terminals in the warehouse.

bbrown

Neither is ideal for all situations. We could go on and list many pros and cons for each. The idea is to select the option that provides the best solution for your particular needs.

stiasta

I am going to create a summary of all the pros and cons up untill now. If there are more pros and cons, and changes i will try to incorporate these into the summary.

**General pros and cons:
+ it could give a performance boost for really big tables.
+ it can be created from a subset of one or many tables
+ does not hold the data itself, therefore it does not take up much space.
+To speed up the slow view, you can create an indexed view on it.
+generally easy to change something in the query without changing the table (if you don't change the type of the fields or adding/removing fields).
+you select only the fields you really need, so it is possible to create good indexes on the real tables the view is using, speeding up performance.

- can be slow because the view is executed at the moment and (it can be a complicated query).
- native restore (if you do a native backup for some reason) can give problems. You must first create the view.
- If a NAV backup is executed the data from the dynamics nav table that uses the SQL view as a source is not included in the backup.

**Creating a stored procedure, run it and then read the data from it using ADO.
+the logic to get the data can be a lot more complicated than with views
+native restore will not give problems (but running the query will if you didn't create the stored procedure).

-you need to run the stored procedure and then receive the data using ADO.

**Using view to send and/or retrieve data from other databases
+ Communication between two or more databases

kriki

Another small remark:
-send/receive data between different databases: if the DB are on different servers and you are using linked servers (define these in SQL Server), it is best to PULL data and not to PUSH data (meaning: read data FROM another database, but avoid writing data TO another database) because pulling data is faster than pushing data.