Encrypting Views

      Using 'SP_HELPTEXT' (SP_HELPTEXT <View name>)we can see the view code. If we create a view with "WITH ENCRYPTION" then we can't see the view code.

Example:

Sample Employee Table creation with data

Sample Department table creation with data

View Creation Script:

CREATE VIEW EMPLOYEE4

WITH ENCRYPTION

AS

SELECT A.ID

      ,A.FirstName

      ,A.LastName

      ,A.Gender

      ,A.Dept_ID

      ,B.Name

      ,B.Location

FROM EMPLOYEE A

JOIN DEPT B 

ON A.Dept_ID = B.DeptID

Checking view source code: 
     If we try to view the view code it will through the message.

SP_HELPTEXT EMPLOYEE4

WITH CHECK OPTION

Creating a View without "WITH CHECK OPTION":

        We created a view with filter condition. If it is a Simple view then we can perform DML operations.

Example:

Employee table script

View creation script:

CREATE VIEW VW_EMPLOYEE2

AS

SELECT

ID

,FirstName

,LastName

,Gender

,Designation

,ManagerID

,Dept_ID

FROM EMPLOYEE

WHERE Dept_ID = 10

View output:

The view will display department 10 data only, because view is created with where condition.

 

    

Inserting data into 'VW_EMPLOYEE2':

I am going to insert two records one is with department id 10 other one is 20.

Insert stmt:

INSERT INTO VW_EMPLOYEE2

(ID,FirstName,LastName,Gender,Designation,ManagerID,Dept_ID)

VALUES (112,'A','Z','M',NULL,NULL,10),

(113,'B','Y','F',NULL,NULL,20)

View output:

We can see only 10th department data using view, because view is having where condition. We can see the newly inserted records in the table.

 

Table output:

Creating a View with "WITH CHECK OPTION":

        We created a view with filter condition and 'WITH CHECK OPTION'. If it is a Simple view then we can  insert the data. The record must satisfied the filter condition, other wise it will throw error.

Example:

View creation script:

CREATE VIEW VW_EMPLOYEE3

AS

SELECT

                ID

                ,FirstName

                ,LastName

                ,Gender

                ,Designation

                ,ManagerID

                ,Dept_ID

FROM EMPLOYEE

WHERE Dept_ID = 10 

WITH CHECK OPTION

Insert stmt:

INSERT INTO VW_EMPLOYEE3

(ID,FirstName,LastName,Gender,Designation,ManagerID,Dept_ID)

VALUES (113,'B','Y','F',NULL,NULL,20) 

Error msg:

                                                Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

SCHEMABINDING a VIEW

Without Schemabinding:

          If we create a view without schemabinding, then we can drop the tables which are used in the view. If we drop the table(s) then view will not work.

Example:
     Employee table script

     View creation script:

CREATE VIEW VW_EMPLOYEE

 AS

 SELECT

   ID

   ,FirstName

   ,LastName

   ,Gender

   ,Designation

   ,ManagerID

   ,Dept_ID

       FROM EMPLOYEE

     We can drop the table which is used in the view.

DROP TABLE EMPLOYEE

     If we try to select the view then it will through error.

With Schemabinding:

          If we create a view without schemabinding, then we cannot drop the tables which are used in the view. If we try to drop the table(s) then it will throw error.

Example:
     Employee table script

     View creation script:

CREATE VIEW VW_EMPLOYEE1

WITH SCHEMABINDING

AS

 SELECT

   ID

   ,FirstName

   ,LastName

   ,Gender

   ,Designation

   ,ManagerID

   ,Dept_ID

       FROM DBO.EMPLOYEE

     We canot drop the table which is used in the view.
       DROP TABLE EMPLOYEE

Views

        View is a virtual table. The columns in a view are columns from one or more physical tables in the database. View is similar to a table, but it is not stored in the database. It is a query stored as an object. User defined views are two types.

• Simple views
• Complex views

See also:

• SCHEMABINDING
• WITH CHECK OPTION
• Encrypting Views

Complex View

        If we create a view on more than one table then the view is complex view.

Example:

            Below link is having sample table creation:

                        Employee table

                        Department table

            View Creation:

CREATE VIEW Sample_Comp_View

AS

SELECT

      E.ID

      ,E.FirstName

      ,E.LastName

      ,E.Gender

      ,E.Designation

      ,E.ManagerID

      ,E.Dept_ID

      ,E.Salary

      ,E.Commission

      ,E.HireDate

FROM EMPLOYEE E

JOIN DEPT D ON E.DEPT_ID = D.DeptID

Simple View

       If we crate a view on a single table, then the view is simple view. We can perform DML operation on simple view. Those operations will reflect on the base table.

Syntax:

CREATE VIEW <View Name>

AS

SELECT Column-1,Column-2,….., Column-N

FROM <Table Name>

Example:

Sample table creation:

CREATE TABLE Sample_Tbl (ID int, Name varchar(20))

INSERT INTO Sample_Tbl VALUES (1,'A')

View Creation:

CREATE VIEW Sample_View

AS

SELECT ID, NAME FROM  Sample_Tbl

Commonly used String Functions

            Several functions are available in SQL server, in those commonly used functions are

• String functions
• SUBSTRING()
• LTRIM()
• RTRIM()
• LEFT()
• RIGHT()
• REPLACE()
• LOWER()
• UPPER()
• LEN()
• ASCII()
• CHAR()
• REVERSE()
• CHARINDEX()
• PATINDEX()
• STUFF()

SUBSTRING(): Using SUBSTRING function we can extract part of the string from given string.

            Syntax: SUBSTRING (Expression, start, length)

            Example: SELECT SUBSTRING('SQL server',1,3);

LTRIM(): It removes leading blank spaces of a string.

            Syntax: LTRIM (String)

            Example: SELECT LTRIM('   SQL SERVER')

RTRIM(): It removes trailing blank spaces of a string.

Syntax: RTRIM (String)

            Example: SELECT RTRIM('SQL SERVER   ')

LEFT(): Returns the left most characters of a string.

Syntax: LEFT (string, length)

Example: SELECT LEFT('SQL SERVER',3)

RIGHT(): Returns the right most characters of a string.

Syntax: RIGHT (string, length)

Example: SELECT RIGHT('SQL SERVER',6)

REPLACE(): Returns a string with all the instances of a substring replaced by another substring.

Syntax: REPLACE (find, replace, string)

Example:

SELECT REPLACE('This is SQL','SQL','structured query language')

LOWER(): Using this function we can change the string case to lower.

            Syntax: LOWER (String)

            Example: SELECT LOWER('SQL Server')

UPPER(): Using this function we can change the string case to upper.

            Syntax: UPPER (String)

            Example: SELECT UPPER('SQL Server')

LEN(): This function returns length of the string.

            Syntax: LEN (String)

            Example: SELECT LEN('SQL Server')

ASCII(): It returns the ASCII code value of the leftmost character of a character expression.

            Syntax: ASCII (String)

            Example: SELECT ASCII('SQL Server')

CHAR(): It converts an int ASCII code to a character.  

            Syntax: CHAR (Integer expression)

           

            Example: select CHAR(83)

REVERSE(): It returns a character expression in reverse order. 

            Syntax: REVERSE (String)

            Example: SELECT REVERSE('SQL Server')

CHARINDEX (): Char Index returns the first occurrence of a string or characters within another string.

Example: I want to find the first occurrence of the letter ‘r’ from the string “SQL Server”.

           

            SELECT CHARINDEX('R','SQL Server')

PATINDEX(): As a contrast PatIndex is used to search a pattern within an expression.

            Syntax: PATINDEX ( '%pattern%' , expression)

Here the first argument takes a pattern with wildcard characters like '%' (meaning any string) or '_' (meaning any character).

Example: SELECT PATINDEX('%BC%','ABCD')

STUFF(): Using this function we can replace specific length of characters with another set of characters.

Syntax: STUFF (character_expression1, start, length, character_expression2)

            Example: SELECT STUFF('SQL Server is useful',5,6,'Database')