SQL Views   «Prev  Next»
Lesson 4Abstraction
Objective Power of abstraction using SQL Views

Abstraction Power of SQL Views in Microsoft SQL Server 2019: A Comprehensive Insight

In database management systems, abstraction is the process of simplifying complex systems by exposing only the necessary features to the user, thereby hiding the intricate details underneath. Microsoft SQL Server 2019, one of the leading relational database systems, leverages the power of abstraction using SQL Views. Let's delve into the profound capabilities and advantages brought about by views in this environment.
  1. Definition of SQL Views: A view in SQL Server 2019 is a virtual table derived from one or more base tables or views. It does not store data physically but presents data as if it were a table by encapsulating complex SQL queries.
  2. Simplified Querying: At its core, a view provides a simplified lens to access data. For instance, joining multiple tables, applying filters, or performing aggregations can result in verbose SQL statements. By encapsulating these complexities within a view, users can retrieve data with a straightforward `SELECT` statement from the view, as if querying from a regular table.
  3. Consistent Data Representation: As business logic or data representation requirements evolve, underlying table structures might change. Views can abstract these changes, offering a consistent interface to applications or end-users. Any alteration in table structures can be addressed within the view, ensuring dependent systems remain unaffected.
  4. Enhanced Security: Views play a pivotal role in data security within SQL Server 2019.
    1. Column Level Security: Not all columns in a table might be relevant or permissible for every user. Views can present only specific columns, thereby restricting access to sensitive or irrelevant columns.
    2. Row Level Security: By incorporating conditions within a view, it's possible to restrict data at the row level, ensuring users access only the data they're authorized to see.
  5. Logical Partitioning: In extensive databases, logical partitioning of data facilitates easier management and querying. Views can represent logical partitions, presenting data slices based on regions, timeframes, or business units, among other criteria.
  6. Optimization and Performance: SQL Server 2019's Query Optimizer intelligently interacts with views. While views encapsulate complexity, the optimizer can still make informed decisions about the best execution plan, leveraging indexed views when appropriate for performance gains.
  7. Modular Development: Just as modular programming allows developers to build applications piece by piece, views support modular database development. Developers can create views for specific functionalities, promoting reusability and maintainability.
  8. Data Integrity and Masking: Views can be set up with specific rules or filters that ensure only valid data is displayed or accessed. Moreover, data masking, a feature in SQL Server 2019, can be incorporated into views, presenting obfuscated data for privacy concerns.
  9. Schema Evolution: As the database schema evolves, views act as a buffer. While underlying tables might be refactored, views can remain unchanged, ensuring dependent applications or processes aren't disrupted.

The power of abstraction in SQL Server 2019, as epitomized by SQL Views, is a testament to the database's sophisticated design and commitment to flexibility, security, and efficiency. By leveraging views, database administrators and developers can address intricate challenges, from security to complexity, ensuring streamlined operations and robust data management.

SQL Views and Astraction

The power behind views lies in something called abstraction. Abstraction frees your program from considering how information is retrieved from the table. In this case, since you are referring to the view to get information from the table, and you are not querying the table directly.
For example, suppose your manager walks in and has changed his mind. He wants to see the results for the state of California instead of Utah.
Now you have to go update all your queries used in reports so you can see the correct state, right? This is not correct.
All you need to do is to update the view. Because all the queries reference the view for their information, you can make one change, update the state, and all your queries using that view are automatically updated as well.
Consider views whenever you perform a query repeatedly against the database with only slightly different parameters.

Why Use Views?

There are three good reasons to include views in the design of a database:
  1. As mentioned earlier, views provide a significant security mechanism by restricting users from viewing portions of a schema to which they should not have access.
  2. Views can simplify the design of a database for technologically unsophisticated users.
  3. Because views are stored as named queries, they can be used to store frequently used, complex queries. The queries can then be executed by using the name of the view in a simple query.

Like other structural elements in a relational database, views can be created and destroyed at any time. However, because views do not contain stored data but only specification of a query that will generate a virtual table, adding or removing view definitions has no impact on base tables or the data they contain. Removing a view will create problems only when that view is used in an application program and the program is not modified to work with a 1) different view or 2) base table.