Data Types in SQL SERVER

Data Type means the type of data which users provided to a specific column

In SQL Server 2 types of data types available which includes

            1. System Defined Data Types

            2. User Defined Data Types.

System Defined Data Types:

              SQL Server already contains some data types called System Defined data types or Predefined Data types or Built-in Data types. System Defined Data Types again categorized into 4 types

a.      Numeric Data types

b.      String Data types

c.       Date Time Data types

d.      Special  Data types

Numeric Data types:

            These Data types are used to provide numeric information for the columns, these includes

                        Data Type                              Size

                        BIT                                          0 or 1

                        TINYINT                                1 BYTE

                        SMALLINT                            2 BYTES                   

                   *   INT                                         4BYTES

                        BIGINT                                  8BYTES

                        REAL                                     4BYTES

                        FLOAT                                   8BYTES                                

                        DECIMAL (P, S)                   5-17 BYTES

TINYINT, SMALLINT, INT, BIGINT are called Exact Numerics where as REAL, FLOAT are called Approximate Numerics.

 

String Data types:

            These Data types are used to provide character information for the columns, these includes

                        Data Type                               Size

                          CHAR [(N)]                          1BYTE

                     *   VARCHAR [(N)]                 1BYTE

                          TEXT                                    16BYTES

                        VARCHAR(MAX)                16 GB

CHAR [(N)]: It is a fixed length data type, which occupies by default 1 byte memory. When we N value then it occupies N bytes of memory where N is an integer. It follows Static memory allocation process.

VARCHAR [(N)]: It is a variable length data type, which occupies by default 1 byte memory. When we N value then it occupies N bytes of memory where N is an integer. It follows dynamic memory allocation process.

Note : The Maximum limit for N is 8000,if it is more than 8000 characters we will use TEXT or VARCHAR(MAX)

Date Time Data Type:

            These data types are used to provide date oriented information to the columns, these includes

Data Type                               Size                 Range

                        SMALLDATETIME              2 BYTES        1900-01-01 TO 2079-06-06

                   *   DATETIME                            4BYTES         1753-01-01 TO 9999-12-31

Special Data types:

These data types are used to provide miscellaneous information to the columns, these includes

Data Type                               Size

                        SMALLMONEY                   4 BYTE

            *          MONEY                                 8 BYTES       

IMAGE                                   16 BYTES

                        VARBINARY (MAX)          Unlimited

1.      SQL_VARIANT

1. Binary Data types: These stores binary values of a given string in ordered to hide the original string values.

                                       Data Type                            Size

                                      BINARY [(N)]                     1BYTE

                           *   VARBINARY [(N)]                  1BYTE

2. Unicode Data types: These Data types are used to store Unicode information, these includes

                                       Data Type                            Size

                                      NCHAR [(N)]                       2BYTE

                               *   NVARCHAR [(N)]                    2BYTE

                                          NTEXT                       16BYTES

II. User Defined Data Types:

When user create a data type then that data type is called user defined data type

Syntax:

CREATE TYPE USER_DEFINED_DATATYPE FROM SYS_DEFINED_DATATYPE

Ex:

CREATE TYPE MYINT FROM INT

CREATE TYPE MYFLOAT FROM FLOAT

CREATE TYPE CASH FROM MONEY

Joins in SQL Server

JOINS

Joins in SQL Server are used to select the data from multiple tables using a single select statement.

T-SQL provides the join concept, which allows retrieval of data from more than one table. This concept is probably the most important for RDBMS, because it allows data to be spread over many tables.

In SQL Server there existed three types of joins which includes

1.    INNER JOIN
2.    OUER JOIN
3.    CROSS JOIN

Sample Tables

EMP

EMPNO       ENAME      SAL                   DEPTNO    
----------- ---------- --------------------- -----------
11               RAGHU      10000.0000            10
22               RAZ            20000.0000            20
33              AMAR         10000.0000            10
44              MANI          15000.0000            20
55              CHARN      15000.0000            40
66              ANIL          20000.0000            50

DEPT

DEPTNO      DNAME     LOC      
----------- ---------- ----------
10               SALES          HYD
20               HR                CHE
30               IR                 BAN


1.INNER JOIN: Inner join selects the data from multiple tables based on the equality condition It means it selects only matched records from the multiple tables. For doing this Inner join operation we should have to maintain one common valued column in the multiple tables.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,………,
       TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 INNER JOIN TABLE2
       ON TABLE1.COMMON COLUMN=TABLE2.COMMON COLUMN

E.g.:

SELECT EMP.EMPNO, EMP.ENAME,
       DEPT.DEPTNO, DEPT.DNAME FROM EMP INNER JOIN DEPT
       ON EMP.DEPTNO=DEPT.DEPTNO

EMPNO       ENAME      DEPTNO      DNAME    
----------- ---------- ----------- ----------
11                 RAGHU       10              SALES
22                 RAZ             20              HR
33                AMAR          10              SALES
44                MANI           20              HR



2. OUTER JOIN: It is the extension of Inner Join operation because Inner selects only matched records from multiple tables where Outer join selects matched records as well as unmatched records. It includes

a.    Left Outer Join
b.    Right Outer Join
c.    Full Outer Join

a.    Left Outer Join: It selects matched records from both the tables as well as unmatched records from Left side table. For doing this operation we have to keep a special symbol ‘*’ at the left side of the equality condition.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,………,
       TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 LEFT OUTER JOIN TABLE2
       ON TABLE1.COMMON COLUMN =TABLE2.COMMON COLUMN

E.g.:

SELECT EMP.EMPNO, EMP.ENAME,
       DEPT.DEPTNO, DEPT.DNAME
FROM EMP LEFT OUTER JOIN DEPT
       ON EMP.DEPTNO =DEPT.DEPTNO

EMPNO       ENAME      DEPTNO      DNAME    
----------- ---------- ----------- ----------
11               RAGHU       10               SALES
22               RAZ             20               HR
33              AMAR          10               SALES
44              MANI           20               HR
55             CHARN       NULL          NULL
66             ANIL           NULL          NULL


b.    Right Outer Join: It selects matched records from both the tables as well as unmatched records from Right side table. For doing this operation we have to keep a special symbol ‘*’ at the right side of the equality condition.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,………,
       TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 RIGHT OUTER JOIN  TABLE2
    ON TABLE1.COMMON COLUMN  = TABLE2.COMMON COLUMN
E.g.:

SELECT EMP.EMPNO, EMP.ENAME,
       DEPT.DEPTNO, DEPT.DNAME
FROM EMP RIGHT OUTER JOIN  DEPT
       ON EMP.DEPTNO = DEPT.DEPTNO



EMPNO       ENAME      DEPTNO      DNAME    
----------- ---------- ----------- ----------
11               RAGHU       10          SALES
22              RAZ              20          HR
33             AMAR           10          SALES
44             MANI            20          HR
NULL        NULL          30          IR


c.    Full Outer Join: It is just combination of Left outer Join + Right outer join. It selects matched records as well as unmatched records from the given tables.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,………,
       TABLE2.COLUMN1, TABLE2.COLUMN2,……
FROM TABLE1 FULL OUTER JOIN TABLE2
       ON TABLE1.COMMON COLUMN =TABLE2.COMMON COLUMN

E.g.:

SELECT EMP.EMPNO, EMP.ENAME,
       DEPT.DEPTNO, DEPT.DNAME
FROM EMP FULL OUTER JOIN DEPT
       ON EMP.DEPTNO =DEPT.DEPTNO

EMPNO       ENAME      DEPTNO      DNAME      
----------- ---------- ----------- ----------
11               RAGHU        10               SALES
22              RAZ               20               HR
33              AMAR          10                SALES
44              MANI           20                HR
55              CHARN       NULL          NULL
66              ANIL            NULL         NULL
NULL        NULL           30              IR


CROSS-JOIN:
 It is also known as CROSS PRODUCT or CARTESIAN PRODUCT because it produces the product of multiple tables. Every row from first table is multiplied with all rows of another table. Simply it is the multiplication of two tables.

Syntax:

SELECT TABLE1.COLUMN1, TABLE1.COLUMN2,…….
                TABLE2.COLUMN1,TABLE2.COLUMN2,…….
FROM TABLE1 CROSS JOIN TABLE2

Ex:

SELECT EMP.EMPNO, EMP.ENAME,
                DEPT.DEPTNO, DEPT.DNAME
FROM EMP CROSS JOIN DEPT




EMPNO       ENAME      DEPTNO      DNAME    
----------- ---------- ----------- ----------
11                  RAGHU       10            SALES
22                  RAZ             10            SALES
33                 AMAR          10            SALES
44                 MANI           10            SALES
55                CHARN         10            SALES
66                ANIL             10            SALES
11                RAGHU        2 0            HR
22                 RAZ              20            HR
33               AMAR            20            HR
44                 MANI           20            HR
55                  CHARN       20            HR
66                  ANIL           20             HR
11                 RAGHU        30             IR
22               RAZ                30             IR
33              AMAR             30             IR
44              MANI              30             IR
55              CHARN           30             IR
66              ANIL               30             IR


SELF JOIN:

Join a table with itself by providing two table alias names is called SELF-JOIN.

Select * from DEPT, DEPT

The above statement shows an error message because it not possible to multiply a table by itself with the same name, so that we have to project the same table DEPT as two tables to the SQL Server. To show a single DEPT table as two tables to server we have to use the concept of table Alias Names.

SELECT * FROM DEPT A, DEPT B

In the above example we provided two table alias names for the single table DEPT those are A and B. Then server identifies that there are two tables available and it performs join operation in a normal way. According user point of view there existed only one table but according to Server point of view there are two tables available those are A and B.



DEPTNO      DNAME      LOC        DEPTNO      DNAME      LOC      
----------- ---------- ---------- ----------- ---------- ---------- ----------------
10                   SALES        HYD       10          SALES      HYD
20                   HR               CHE       10          SALES      HYD
30                   IR                BAN       10          SALES      HYD
10                  SALES        HYD        20          HR         CHE
20                  HR              CHE         20          HR         CHE
30                  IR               BAN         20          HR         CHE
10                  SALES       HYD        30           IR         BAN
20                  HR             CHE         30           IR         BAN
30                  IR               BAN        30           IR         BAN

NORMALIZATION in SQL Server

Normalization is process of splitting the base table into multiple tables based on the theory of Functional Dependency.
            OR
Normalization is repetitive process in order to identify the functional dependencies among the columns and to remove them. If any functional dependency is occurred after the normalization process again we have to start the same process until all functional dependencies have been removed.

To do this Normalization we have to follow rules or conditions called Normal Forms.

Un-Normalized Table

EMPNO PROJNO   ENAME  PNAME    SAL  BUD  DEPTNO  DNAME  LOC
11         (P1, P2)       ----     (Pn1, Pn2)  -----   -----        10           --------      -----
22         (P2, P3)       ----     (Pn2, Pn3)  -----   -----        10           --------      -----
33         (P1, P3)       ----     (Pn1, Pn3)  -----   -----        20           --------      -----


EMPNO and PROJNO are Primary Keys called ‘COMPOSITE PRIMARY KEY’

FIRST NORMAL FORM (1NF): According to first normal form table should contain only single values columns. But in the above un-normalized table the columns PROJNO and PNAME contains multiple values.

To make the table into first normal form we should have to split the multiple values into single values.


EMPNO PROJNO   ENAME  PNAME    SAL  BUD  DEPTNO  DNAME  LOC
11         P1         ----        Pn1         -----   -----        10           --------      -----
11         P2         ----        Pn2         -----   -----        10           --------      -----
22         P2         ----        Pn2         -----   -----        10           --------      -----
22         P3         ----        Pn3         -----   -----        10           --------      -----
33         P1         ----        Pn1         -----   -----        20           --------      -----
33         P3         ----        Pn3         -----   -----        20           --------      -----


SECOND NORMAL FORM (2NF):
According to second normal form table should be in 1NF and we should have to remove Partial Functional Dependency.

In the above table DEPTNO non-key column dependent part of the Primary key column i.e.EMPNO. It means there existed Partial functional dependency.

To make the table into second normal form we have to divide the table into multiple tables.

PROJ-INFO                   
PROJNO    PNAME    BUD   
   P1        Pn1        ------
   P2        Pn2        ------
   P3        Pn3        ------

EMP-INFO
EMPNO   ENAME   SAL   DEPTNO   DNAME   LOC
11           ----           ----       10              ------        -----
22           ----           ----       10              ------        -----
33           ----           ----       20              ------        -----


THIRD NORMAL FORM (3NF):
According to second normal form table should be in 2NF and we should have to remove Transitive Functional Dependency.

In the above EMP-INFO table non-key column DNAME dependent part on the other non- key column i.e.DEPTNO. It means there existed Transitive functional dependency.
To make the table into third normal form we have to divide the table into multiple tables.

PROJ-INFO                   
PROJNO    PNAME    BUD   
   P1        Pn1        ------
   P2        Pn2        ------
   P3        Pn3        ------

EMP-INFO      DEPT-INFO              
EMPNO   ENAME   SAL      DEPTNO   DNAME   LOC
11           ----           ----           10              ------        -----
22           ----           ----           20              ------        -----
33           ----           ----      

Views in SQL Server

A View is nothing but an image table or virtual table, which is created for a base table. A view can be created by taking all values from the base table or by taking only selected values from base table. There are two types views available in SQL Server.

1.    Simple Views
2.    Complex Views

Note: If we perform any modifications in base table, then those modifications automatically effected in view and vice-versa.

1.Simple Views: Creating View by taking only one single base table.

Syntax:
CREATE VIEW VIEWNAME [WITH ENCRYPTION]
AS SELECT * FROM TABLENAME [WHERE CONDITION]
   [WITH CHECK OPTION]

E.g.:
CREATE VIEW V1 AS SELECT * FROM EMP
INSERT INTO V1 VALUES (55,’RAVI’, 10000,10)

The above insert statement inserts the values into base table EMP as well as into view V1.

E.g.:
CREATE VIEW V2 AS SELECT * FROM EMP WHERE DEPTNO=10

INSERT INTO V2 VALUES (66,’BABBU’, 25000,10)
The above insert statement inserts the values into base table EMP as well as into view
V2.

INSERT INTO V2 VALUES (77,’AMAR’, 15000, 20)
The above insert statement inserts the values into only base table EMP but not into view
V2 because according to the definition of V2 user supplied values are invalid values. It means invalid values are inserting into base table EMP. To stop this kind of operations we have to create the view with ‘WITH CHECK OPTION’.

E.g.:
CREATE VIEW V3 AS SELECT * FROM EMP WHERE DEPTNO=10
        WITH CHECK OPTION

INSERT INTO V3 VALUES (88,’TEJA’, 25000,20)
The above insert statement cannot inserts the values into base table EMP as well as into view V3.



SP_HELPTEXT: This stored procedure is used to display the definition of a specific view.

Syntax:   SP_HELPTEXT    VIEWNAME
E.g.: SP_HELPTEXT      V1

Output: CREATE VIEW V1 AS SELECT * FROM EMP


WITH ENCRYPTION: Once we create any view with ‘WITH ENCRYPTION’ then we cannot find the definition of that particular view using SP_HELPTEXT stored procedure because this encryption option hides the definition.

E.g.:
CREATE VIEW V4 WITH ENCRYPTION
AS SELECT * FROM EMP WHERE DEPTNO=20

SP_HELPTEXT V4

Output :The text for object v4 is encrypted

To decrypt the definition of view V4 we have to follow the below approach
1.    Replace CREATE with ALTER
2.    Remove WITH ENCRYPTION keyword
3.    Select the query and press F5.
E.g.:
ALTER VIEW V4
AS SELECT * FROM EMP WHERE DEPTNO=20

SP_HELPTEXT V4

CREATE VIEW V4
AS SELECT * FROM EMP WHERE DEPTNO=20

2.Complex Views:
Creating View by taking multiple base tables.

Ex:
CREATE VIEW EMP_DEPT_VIEW
AS SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

Syntax To Create view based on another views:

SQL SERVER enables users to create views based on another view. We can create view based on another view up to 32 levels

Syntax:
CREATE VIEW VIEWNAME [WITH ENCRYPTION]
AS SELECT * FROM VIEWNAME [WHERE CONDITION]
   [WITH CHECK OPTION]
E.g.: CREATE VIEW V5 AS SELECT * FROM V1 WHERE DEPTNO=10

Syntax To Drop the Views:

DROP VIEW VIEWNAME […N]

E.g.: DROP VIEW V1, V2, V3, V4, V5