Basic Difference Between Tables and Views in SQL Server

This blog has been written with the motive of giving readers a quick overview about the difference between Tables and Views in SQL Server. We can create several types of tables and views in SQL Server which can be used for various purposes in databases. Let’s see what are the major differences between Tables and Views in SQL Server database.

What is a Table in SQL Server?

In SQL Server a table is a combination of data arranged in rows and columns. In the table it is easy to compare the information and the data is much easier to read. On the other hand, we can say that the set of data elements with is combination of vertical columns and horizontal rows known as tables.

Type of Tables in SQL Server

There are three types of tables that can be created in SQL Server database. These three types of tables are discussed below:

1. Regular User Table
2. Temporary Table
3. Global Temporary Table

1. Regular User Table

The actual table which binds the data of users for processing and reporting purposes in the future known as a regular user table. The regular user tables are also called as a physical table because, after creating a table, it resides physically on the database until you delete or drop them.

Syntax:

CREATE TABLE [dbo].[Table_Name]
    (
    [Table_Name _ID] [int] NOT NULL,
    [Field1] [varchar](200) NULL,
    [Field2] [int] NULL
    )
    ON [PRIMARY]

2. Temporary Table

As its name implies, a temporary table is a table that holds the temporary data for the intermediate results of multiple queries. These tables will be dropped automatically once the stored procedure is executed or if the session is over. But it is recommended that you must drop temporary tables when you no longer require them.

Syntax:

CREATE TABLE #Table_Name
     (
     [Table_Name _ID] [int] NOT NULL,
     [Field1] [varchar](200) NULL,
     [Field2] [int] NULL
     )

3. Global Temporary Tables

A table which is available for all sessions in databases is known as global temporary tables. The global temporary tables are same like temporary table and these types of tables can be dropped automatically after the expiration of last session. If even a single session is active, then these tables will not be deleted.

Syntax:

CREATE TABLE ## Table_Name
     (
     [Table_Name _ID] [int] NOT NULL,
     [Field1] [varchar](200) NULL,
     [Field2] [int] NULL
     )

NOTE:

It is possible to clone the regular user table, temporary table and global temporary table.

What is a View in SQL Server?

SQL views are an essential part of any database but it is ignored by the database developers. A view can be stated as a virtual table that does not exist until it not invoked by a name in SQL Server statements. So we can say that there is no existence of view in SQL Server databases unless it is not envoked.

How to Create a View?

It's very simle to create a view in SQL Server. You can use the script given below to create a view:

Syntax:

CREATE VIEW VIEW_NAME
    AS
    SELECT_STATEMENT

Types of Views in SQL Server

There are two types of views in SQL Server which are as follows:

1. System Defined View
2. User-Defined View

1. System Defined View

The system defined Views are predefined these are already present in the SQL Server master database file. The system defined Views can be automatically attached to the user defined databases.

The system defined view can be classified in three parts:

  a. Information Schema View

As its name implies, a view in SQL Server which supports displaying the information related to the database. In SQL Server there are twenty types of schema views. To create a schema view a query needs to be executed. The information schema views are based on the catalog view definition.

Syntax:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
   where TABLE_NAME='Employee’

  b. Catalog View

The catalog view is another type of view in SQL Server. The catalog view is used to access the metadata from multiple databases. These type of views are not affected by changes in the underlying catalog tables. We can say that the catalog view shows only the information used by SQL Server Database Engine.

Syntax:

SELECT NAME AS object_name
   SCHEMA_NAME(schema_id) AS schema_name
   FROM sys.objects
   WHERE

  c. Dynamic Management View

A Dynamic Management View in SQL Server is designed by using the views and table-valued functions. Some dynamic management views can apply on the whole server and these are accumulated in the master database. Some of these are specifically designed for each database. All the dynamic management views are residing in the sys schema. The designing of a Dynamic Management View starts with dm_ in the name.

Syntax:

SELECT connection_id,session_id,client_net_address,auth_scheme
   FROM sys.dm_exec_connections

2. User Defined View

As its name implies, a view defined from user side is known as user defined view. The user defined views are created by users as per their need. There are no classifications to create the User Defined Views.

Syntax:

CREATE VIEW
     AS
     SELECTE
     FROM

Conclusion

In this article, I have tried to show the difference between Tables and Views In SQL Server databases. The tables are sometimes called as the catalog for a database. There are three types of tables. In SQL a view is an example of "Virtual Table" like an object in the database. We can encrypt a View definition for making it secure.