WHAT IS VIEW? TYPES OF VIEWS?

WHAT IS VIEW?

 View is nothing but parsed SQL statement which fetches record at the time of execution.

TYPES OF VIEWS?

There are mainly two type of views

a) Simple View

b) Complex View

apart from that we can also subdivided views as UpdatableViews and Read only Views.

Lastly there is an another view named as Materialized Views.

PURPOSE OF VIEWS

View is used for the purposes as stated below:

a) Security

b) Faster Response

c) Complex Query solve

Syntax is :

Create or replace view([olumn1],[column2]...)

as

Select column1,column2...

from table_name

[where condition]

[with read only],[with check option]

What is Materialized View ?

Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

Difference between View vs Materialized View in database

Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  row id of view is same as original table but in case of Materialized view row id is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table

6) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

Temporary Objects in SQL Server

Temporary Objects in SQL Server

CTE

CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

A sub query without CTE is given below :

Select * from (

Select a.addrs, e.emp,e.age from address a inner join employee e on e.eid=a.eid) temp

Where temp.age>30

Order by temp.name

By using CTE above query can be re-written as follows :

With cte (address,name,age)

As (

Select a.addrs, e.emp,e.age from address a inner join employee e on e.eid=a.eid )

Select * from cte

Where cte.age>30

Order by cte.name

When to use CTE

1.     This is used to store result of a complex sub query for further use.

2.     This is also used to create a recursive query.

Temporary Tables

In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-

1.          Local Temp Table

Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

CREATE TABLE #LocalTemp

      (

       UserID int,

       Name varchar(50),

       Address varchar(150)

      )

      GO

      insert into #LocalTemp values ( 101, 'avinash','bangalore');

      GO

      Select * from #LocalTemp

The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.

2.          Global Temp Table

Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

CREATE TABLE ##GlobalTemp

      (

       UserID int,

       Name varchar(50),

       Address varchar(150)

      )

      GO

      insert into ##GlobalTemp values ( 101, 'avinash','bangalore');

      GO

      Select * from #GlobalTemp

Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable

This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

Joins in SQL Server.

                                                                   JOINS

Types of Joins 

• Inner join 
• Outer join (right , left and full outer)
• Cross join
• Self Join             

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables
• SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
• CARTESIAN JOIN: Returns the Cartesian product of the sets of records from the two or more joined tables.

 Joins with Examples :

create table tableA ( 
    a int 
   ,b int 
);

create table tableB ( 
   a int 
  ,b int 
);



insert into tableA (a,b) values (1,2); 
insert into tableB (a,b) values (1,2); 


insert into tableA (a,b) values (11,12); 
insert into tableB (a,b) values (21,22); 

Queries on joins:

Inner Join: select * from tableA a inner join tableB b on   a.a=b.a

Result:

Left outer join:select * from tableA a left join tableB b on a.a=b.a
Result:

EXAMPLE2:   select * from tableA a left join tableB b on a.a=b.a where b.a is null

 Right outer Join:select * from tableA a right join tableB b on a.a=b.a
Result:

EXAMPLE2:select * from tableA a right join tableB b on a.a=b.a where a.a is null

Full outer join:select * from tableA a full outer join tableB b on a.a=b.a
Result:

Cross Join:select * from tableA a cross join tableB b
Result: 
                                         

Self Join: select a.a,a.b,b.a,b.b from tableA a inner join tableA b on a.a=b.a

Result:

Data ware house concepts

What i s Fact?
A summarizable  numeric value used to monitor the business flow is called the Fact/measure.
Fact will act as Key performance indicator for business.
Example: salesamount,costamount,profit,units in stock etc.

Dimension : Dimension is descriptive Text used to analyse the facts
example  product,customer

Business Key:
In a dimension table which column loading the data of OLTP table primary key is called business key.
Usage :To join the dimension tables and OLTP tables in data loading Process.

Surrogate Key:
A primary key and auto generated column in a dimension table is called Surrogate Key.
Usage: 

• Surrogate key provides uniqueness to identify a row in the dimension table 
• Surrogate key used to combined the dimension table and fact table.

Dimension Table : Dimension attributes presented in dimension table .Every dimension should have key column i.e primary key.

Fact table: Facts/measures stored in fact table .Fact table contains Foreign key referred from dimension table .

Dimension Types :

1. Standard Dimension
2. Parent child Dimension 
3. Confirmed Dimension
4. Degenerated Dimension
5.  Role Play Dimension

Fact/Measure Types :

1. Additive measure
2. semi additive measure
3. Non-additive Measure 

The three schema models we use to organize the dimension tables fact tables in dimensional modelling .

1.Star schema:

• Fact Table surrounded by dimension tables looks like star .
• Dimension tables are de-normalized.
• No relation  between dimension tables 

2.Snow Flake schema:

• Fact table surrounded by few dimension tables only.
• Dimension tables are normalized.
• Relationship between the Dimension tables .

3.Integrated Schema Model:

• Combination of star and Snowflake schema
• Confirmed dimensions 
• fact less fact tables 
• degenerated dimensions.

Introduction to database

MAR

1

Database concepts

Introduction to database

Databases 

1.System defined 

2.User Defined

System defined 

• Master database
• Model database-template to create 
• Temp database
• MS DB

User defined 

• User can create 'n' number of databases

Types of authentication 

• Windows Authentication
• SQL Server Authentication

Commanding Languages 

1. Data Definition Language (DDL)

• create 
• alter 
• drop 
• Truncate  

     2.Data Manipulation Language (DML)

• Insert
• Update
• Delete

   3.Data Query Language (DQL)

• Select
• Print

  4.Data Control Language (DCL)

• Grant 
• Deny
• Invoke

  5. Transactional Control Language 

• Commit
• Rollback

Simple Queries 

create table employee

(empid int primary key, empname varchar(100),empaddrs varchar(100))

Insert into employee(empid,empname,empaddrs)values 

(101,'ram','bangalore'),(102,'siv','hyderbad'),(103,'avi','bangalore'),(104,'sam','chennai')

select * from employee

Result:

empid empname empaddrs

101 ram bangalore

102 siv hyderbad

103 avi bangalore

104 sam chennai

alter table employee add salary money

update employee set salary= 10000 where empid=101

update employee set salary= 20000 where empid=102

update employee set salary= 30000 where empid=103

update employee set salary= 20000 where empid=104

select * from employee

Result:

empid empname empaddrs salary

101 ram bangalore 10000.00

102 siv hyderbad 20000.00

103 avi bangalore 30000.00

104 sam chennai 20000.00

Data Warehouse concepts

Defining Data Warehouse Concepts and Terminology

Definition of a Data Warehouse: “An enterprise structured repository of subject-oriented, time-variant, historical data used for information retrieval and decision support. The data warehouse stores atomic and summary data.”

Data Warehouse Properties

1. Subject-Oriented:

Data is categorized and stored by business subject rather than by application.

2. Integrated:

Data on a given subject is defined and stored once.

3. Time variant:

Data is stored as a series of snapshots, each representing a period of time

4. Non-volatile:

Typically data in the data warehouse is not updated or deleted.

Difference between Data warehouse and Data Mart

Methodology

1. Ensures a successful data warehouse
2. Encourages incremental development
3. Provides a staged approach to an enterprise wide warehouse   

     - Safe

     -  Manageable

     - Proven

     - Recommended

Modeling:

 1. Warehouses differ from operational structures:

      - Analytical requirements

      - Subject orientation

2. Data must map to subject oriented information:

      - Identify business subjects

      - Define relationships between subjects

      - Name the attributes of each subject

3. Modeling is iterative

 4. Modeling tools are available

Data Management:

1.Efficient  database server and management  tools for all aspects of data  management

2.Imperatives

     - Productive

     - Flexible

     - Robust

     - Efficient

3.Hardware, operating system and network management

Best SQL server question and answers on differences

Few question and answers on differences in SQL SERVER

Here I have posted some concepts in SQL server which I got from another source 

1.    

                           Differences between Functions and Procedures?

             

Procedures	Functions
Procedures can be used for performing business logic.	Function can be used for computations.
A procedure need not to return a value.	A function must return a value.
It can return more than one value.	Function can return only one value.
Return values using out parameters.	Returns value using Return expression.
Procedure can’t be called in SELECT statement.	Function can be called in SELECT statement.
They can use Temporary tables.	They cannot be used Temporary tables.
These can support Error Handling.	Raise error,@@ERROR are not allowed.
PRINT and EXEC statements can be written in Procedures.	PRINT and EXEC statements cannot be written in Functions.

     Differences between Temporary Table and Table Variable?

Temporary Table	Table Variable
It is created in TempDB.	It is created in the memory.
You cannot pass TT as parameter to Functions and Stored Procedures.	You can pass TV as parameter to Functions and Stored Procedures.
A TT can have indexes.	A TV can have only a primary index.
A TT can involve in Transactions, Logging (or) Locking.	A TV cannot involve in these. This makes TV faster than a TT.

     Differences between Triggers and Stored Procedures?

Procedures	Triggers
Procedures called explicitly.	Triggers called implicitly.
Procedures can be created without a table.	Triggers can’t be created without a table.
Procedures accept parameters.	Triggers won’t accept parameters.
Procedures return a value.	Triggers won’t return a value.

       Differences between Clustered index and Non clustered index?

Clustered Index	Non clustered Index
Clustered index will alter the physical representation of rows in a table.	This index will not alter the physical representation of rows in a table.
A table can have only One clustered index.	A table can have more non clustered indexes. [In 2005-249, In 2008-999]
It always arranges the data of a table in sorted order.	Data of a table is not arranged in order.
Data pages and Index pages are stored at same level.	Data pages and Index pages are stored at different levels.

  Differences between Star Schema and Snow Flake Schema?  

Star Schema	Snow Flake Schema
Here Dimensions connect to Fact table and resembles like a star.	Just like a star schema but having sub dimensions.
De normalized Data.	Normalized Data.
Less Joins.	More Joins.
More Memory.	Less Memory.
It is Easy to understand.	Complex to understand.
Indexes are more.	Indexes are less.
Performance high.	Performance low.

         Differences between Table and View?

Table	View
Table contains data.	View contains no data only stored queries.
Tables are limited (2 Billion).	No limit for views.
DML operations on any data.	DML operations on related data.
Tables can be associated to any trigger.	Views can be associated with only Instead of Trigger.

     Differences between Simple View and Complex View?

Simple View	Complex View
Simple view is based on one table.	Complex view is based on one (or) more tables.
Simple views can update. [update means allows DML operations]	Complex views  cannot update

         Differences between View and Synonym?

View	Synonym
View is a subset of table.	Synonym is a mirror of table.
View can be based on one (or) more tables.	Synonym is always based on one table.

         Differences between View and Index View?

View	Index View
When view is created the query is stored in DB.	When Index View is created the query result stored in DB.
A View doesn’t contain any data.	Index view contains data.

      Differences between UNION and JOIN?

UNION	JOIN
Accumulates the Data	Relates the Data
Two Dissimilar structures can’t be combined with UNION operator.	Two Dissimilar structures can be Joined.
It combines the result of two Select statements.	It combines the columns from 2 or more tables with joining condition.

     Differences between WHERE and HAVING clauses?

WHERE	HAVING
It is used to filter the data before Grouped.	It is used to filter the data after Grouped.
It can be used in SELECT, INSERT, UPDATE, DELETE statements.	It can be used in only SELECT statement.
Aggregation functions are not used in Where clause.	Aggregation functions are used in Having clause.

   Differences between DELETE and TRUNCATE?

DELETE	TRUNCATE
It is a DML command.	It is a DDL command.
It supports to delete the rows on conditional basis (where clause).	No conditional deletion.
It will not reset the Identity Column.	It will reset the Identity Column.
It is Logged Action.	It is not Logged Action.
It does not de allocate Data pages (memory).	It will de allocate Data pages.
In this Restoring is possible.	In this Restoring is not possible.
Slow in Execution, since it consult log file to store each deleted row.	Fast in Execution, since it does not consult log file.