Updating Multiple Tables from View

 Introduction

It is a video demonstration. In this demonstration we are trying to demonstrate how we Insert/Update/Delete records from multiple tables by view. It will be informative.

You can find the video link from:

Demo for Updating Multiple Tables from View…

Hope you like it.

Posted by: MR. JOYDEEP DAS

INDEX not implemented when a Materialized view is created from another

Introduction

There is a limitation of view that we must understand is if a materialized view is created by another materialized view, in this case we cannot configured index on Second Materialized view

Please try to understand the bellow pictorial diagram.

Materialized View 1  à

                              Used to Create  à  

                                                Materialized View 2  ß Cannot Configure Index

This article is related to it.

Example to understand

Step - 1 [ Create the Base Table ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

       (

         EMPID    INT         NOT NULL,

         EMPNAME  VARCHAR(50) NOT NULL,

         EMPGRADE CHAR(1)

       );

GO

Step – 2 [ Insert Some Records ]

INSERT INTO  [dbo].[tbl_EMPLOYEE]

      (EMPID, EMPNAME, EMPGRADE)

VALUES(1, 'Joydeep Das', 'A'),

      (2, 'Sukamal Jana', 'A'),

      (3, 'Sangram jit', 'B'),

      (4, 'Souman Bhowmik', 'C');

GO

Step – 3 [ Create First VIEW ]

IF OBJECT_ID(N'dbo.view_EMPLOYEE_1', N'V') IS NOT NULL

   BEGIN

      DROP VIEW [dbo].[view_EMPLOYEE_1];

   END                        

GO

CREATE VIEW [dbo].[view_EMPLOYEE_1]

WITH SCHEMABINDING

AS

SELECT  EMPID, EMPNAME, EMPGRADE

FROM    [dbo].[tbl_EMPLOYEE];

GO 

Step – 4 [ Create Second VIEW by Using First VIEW ]

IF OBJECT_ID(N'dbo.view_EMPLOYEE_2', N'V') IS NOT NULL

   BEGIN

      DROP VIEW [dbo].[view_EMPLOYEE_2];

   END                        

GO

CREATE VIEW [dbo].[view_EMPLOYEE_2]

WITH SCHEMABINDING

AS

SELECT  EMPID, EMPNAME, EMPGRADE

FROM    [dbo].[view_EMPLOYEE_1];

GO

Step – 5 [ Creating the UNIQUE CLUSTERED Index on Second VIEW and Error occurs]

CREATE UNIQUE CLUSTERED INDEX IX_view_EMPLOYEE_2

ON [dbo].[view_EMPLOYEE_2](EMPID);

Msg 1937, Level 16, State 1, Line 2

Cannot create index on view 'MATRIXSYSDB.dbo.view_EMPLOYEE_2'

because it references another view 'dbo.view_EMPLOYEE_1'.

Consider expanding referenced view's

definition by hand in indexed view definition.

Reason for That

The reason for this is that another view over a view is difficult to maintain

What to do to solve it

Use the SELECT statement of first view within the second view.

Hope you like it.

Posted by: MR. JOYDEEP DAS

SELECT * Statement in View NOT Working

Introduction

View has some limitation. Using SELECT * within the view is not a good Idea. If we create view we must use the Columns name in the Select statement.  We also recommended using to make materialized view (WITH SCHEMABINDING options).

Here in this article we are going to discuss about the adverse reaction of using SELECT * within a view.

Let's see a simple example to illustrate our point

Step – 1 [ Create a Base Table ]

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

    (

      EMPID    INT,

      EMPNAME  VARCHAR(50)

    );

GO

Step – 2 [ Insert some Records in the base Table ]

INSERT INTO  [dbo].[tbl_EMPLOYEE]

     (EMPID, EMPNAME)

VALUES(1, 'Joydeep Das'),

      (2, 'Sukamal Jana');

GO

Step – 3 [ Create A VIEW from this Base Table ]

IF OBJECT_ID(N'dbo.view_EMPLOYEE', N'V') IS NOT NULL

   BEGIN

     DROP VIEW [dbo].[view_EMPLOYEE];

   END

GO  

CREATE VIEW [dbo].[view_EMPLOYEE]

AS

SELECT * FROM [dbo].[tbl_EMPLOYEE]; 

GO

Step – 4 [ Run both Table and View ]

SELECT * FROM [dbo].[tbl_EMPLOYEE];

SELECT * FROM [dbo].[view_EMPLOYEE];

EMPID       EMPNAME

----------- --------------------------------------------------

1           Joydeep Das

2           Sukamal Jana

EMPID       EMPNAME

----------- --------------------------------------------------

1           Joydeep Das

2           Sukamal Jana

Step – 5 [ Add another columns to Base table ]

ALTER TABLE [dbo].[tbl_EMPLOYEE]

ADD [GRADE] CHAR(1); 

GO

Step – 6 [ Update the New column with Data ]

UPDATE [dbo].[tbl_EMPLOYEE]

SET [GRADE] = 'A';

GO

Step – 7 [ Again Run both Table and View – Do we find any difference ]

SELECT * FROM [dbo].[tbl_EMPLOYEE];

SELECT * FROM [dbo].[view_EMPLOYEE];

EMPID       EMPNAME                GRADE

----------- ---------------------  ----------

1           Joydeep Das            A

2           Sukamal Jana           A

EMPID       EMPNAME

----------- ---------------------------------

1           Joydeep Das

2           Sukamal Jana

Step – 8 [ Run the sp_refreshview stored procedure ]

EXEC SP_REFRESHVIEW view_EMPLOYEE;

Step – 9 [ Again compare - Run both Table and View ]

SELECT * FROM [dbo].[tbl_EMPLOYEE];

SELECT * FROM [dbo].[view_EMPLOYEE];

EMPID       EMPNAME                            GRADE

----------- ---------------------------------

1           Joydeep Das                  A

2           Sukamal Jana                 A

EMPID       EMPNAME                      GRADE

----------- ----------------------------------

1           Joydeep Das                  A

2           Sukamal Jana                 A

Hope you like it.

Posted by: MR. JOYDEEP DAS

INFORMATION_SCHEMA

Introductions

When we are taking about any RDBMS, it should be a data dictionary or metadata. MS SQL Server has no exceptions.

In MS SQL Server there are two sources to view the metadata information.  

1.    Various System Tables

2.    INFORMATION_SCHEMA views

In this article I am trying to discuss about the INFORMATION_SCHEMA.

Point to Focus

1.    Metadata

2.    Anything wrong to pull information from system table

3.    About INFORMATION_SCHEMA view

4.    Descriptions of views

5.    Using INFORMATION_SCHEMA

Metadata

First we understand that what metadata is. In common understanding about metadata, we can say that it is "data about data". In the context of database it means "Information (data) stored about data, the structures or objects related to data".

Anything wrong to pull information from system table

As per Microsoft, information wise there is no difference but Microsoft reserves the rights to change any system table from version to version. So if an application depends on the system table can face a serious problem due to version change. Is it true? I don't find such an example in my development career. But we should remember the statutory warning of Microsoft.

About INFORMATION_SCHEMA view

SQL Server makes available the Information Schema Views through INFORMATION_SCHEMA schema. It is available in each database and storing information about all database objects contained in the respective database.  The following query is used to retrieve the views available under the INFORMATION_SCHEMA schema and their definitions.

SELECT s.name [Schema_Name],

       o.name [Object_Name],

       sm.definition [Schema_Defination]

FROM   sys.all_sql_modules sm

       INNER JOIN sys.all_objects o ON sm.object_id = o.object_id

       INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

WHERE  s.name = 'INFORMATION_SCHEMA'

ORDER BY o.name;

Here most of the views names are self describing.

Descriptions of Views

Here are the lists of views and there descriptions are mentioned bellow. For better understanding please refer to MSDN.

No	View	Description
1.	CHECK_CONSTRAINTS	Returns one row for each CHECK constraint
2.	COLUMN_DOMAIN_USAGE	Returns one row for each column that has an alias data type
3.	COLUMN_PRIVILEGES	Returns one row for each column that has a privilege that is either granted to or granted by
4.	COLUMNS	Returns one row for each column
5.	CONSTRAINT_COLUMN_USAGE	Returns one row for each column that has a constraint defined on it
6.	CONSTRAINT_TABLE_USAGE	Returns one row for each table that has a constraint defined on it
7.	DOMAIN_CONSTRAINTS	Returns one row for each alias data type that has a rule bound to it
8.	DOMAINS	Returns one row for each alias data type
9.	KEY_COLUMN_USAGE	Returns one row for each column that is constrained as a key
10.	PARAMETERS	Returns one row for each parameter of a user-defined function or stored procedure
11.	REFERENTIAL_CONSTRAINTS	Returns one row for each FOREIGN KEY constraint
12.	ROUTINES	Returns one row for each stored procedure and function
13.	ROUTINE_COLUMNS	Returns one row for each column returned by table-valued functions
14.	SCHEMATA	Returns one row for each schema
15.	TABLE_CONSTRAINTS	Returns one row for each table constraint
16.	TABLE_PRIVILEGES	Returns one row for each table privilege that is granted to or granted by
17.	TABLES	Returns one row for each table
18.	VIEW_COLUMN_USAGE	Returns one row for each column that is used in a view definition
19.	VIEW_TABLE_USAGE	Returns one row for each table that is used in a view
20.	VIEWS	Returns one row for views

Using INFORMATION_SCHEMA

Here is the simple example to understand the information schema.

1.  TABLEs

SELECT * FROM information_schema.tables

WHERE TABLE_TYPE = 'BASE TABLE';

SELECT * FROM information_schema.tables

WHERE  TABLE_TYPE = 'BASE TABLE'

       AND table_name = 'xyz';

      

SELECT * FROM information_schema.tables

WHERE  TABLE_TYPE = 'BASE TABLE'

       AND table_name = 'xyz'

       AND table_schema = 'abc';

2.  VIEWs

SELECT * FROM information_schema.tables

WHERE  TABLE_TYPE = 'VIEW';


3.  List CONSTRAINTs

it retrieves all the constraints of Table objects "XYZ"

SELECT constraint_name, constraint_type

FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE  table_name = 'xyz';


4.  List FUNCTIONs

SELECT routine_name

FROM   INFORMATION_SCHEMA.ROUTINES

WHERE  routine_type = N'FUNCTION';

Hope you like it.

Posted by: MR. JOYDEEP DAS