Friday, October 26, 2018

What is Materialized View or Indexed View and how to create it in SQL Server

What is difference between View and a Materialized View in SQL Server
View :
The View is a logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data.
So thaat we always get the updated or latest data from original tables.
Performance of the view depends on our select query. If we want to improve the performance of view we should avoid using join statement in our query.
If we need multiple joins between tables, then use the Materialized view defined below, where we define the Index based columns which we used for joining, as we know that the index based columns are faster than a non-index based column.

Materialized View :
Materialized views are also the logical view of our data-driven by the select query but the result of the query will get stored in the table or disk, also the 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 be stored in table and table may be indexed so faster for applying joins.
Also joining is done at the time of materialized views refresh time so that no need to fire join statement every time as in case of view.
Creating a Materialized View :
If you want to create an Index on your View, you must create it with using the " WITH SCHEMEBINDING" Option.
If you want to create a Schema-Bound user defined Function that references to your View then your View must also be " SCHEMEBINDING".
The SHEMABINDING is essentially takes the things that your VIEW is depend upon(Tables/ other Views), and "Binds" them to that View. The significance of this is that, no one can make alteration (ALTER/DROP) to those underlying objects, unless you drop the Schema-Bound View first.

--------------------------------------------------------------------------------------
Example :

Creating a View with SchemaBinding Option
USE [TAMATAM]
GO
CREATE VIEW vw_EmpDeptDtls
WITH SCHEMABINDING

AS
SELECT E.[Emp_Id],
E.[Emp_Name],
E.[Gender],
E.[Joining_Date],
E.[Basic_Sal],
D.[Dept_Id],
D.[Dept_Name],
D.[Bonus_Rate],
J.[Job_Id],
J.[Job_Title]
FROM [dbo].[Emp_Test] E Inner Join [dbo].[Dept] D
ON E.Dept_Id=D.Dept_Id
Inner Join [dbo].[JobTitles] J
ON E.Job_Id=J.Job_Id
GO
--------------------------------------------------------------------------------------
Now you can create an Index on the above View to make the Query execution faster :
Create Unique Clustered Index Ind_Vw_EmpDept
On vw_EmpDeptDtls (Emp_Id,Dept_Id,Job_Id)

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

Notes :
Now if you try to make any alterations to the underlying objects...
--ALTER TABLE EMP_TEST Drop Column [Job_Id]
--ALTER TABLE EMP_TEST ALTER Column [Emp_Name] Varchar(250)
--Drop Table [Emp_Test]
You will get the following errors...
--The object 'vw_EmpDeptDtls' is dependent on column 'Job_Id'
--ALTER TABLE DROP COLUMN Job_Id failed because one or more objects access this column.
--Cannot DROP TABLE 'Emp_Test' because it is being referenced by object 'vw_EmpDeptDtls'
 
Please note that :
--You can Alter the underlying objects of the View, by removing the SchemaBinding by altering the View .
--You can drop/alter a Column from the base table, which is not used in the View.

Difference between View vs Materialized View :
a) The 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 the query result in disk or table.
b) When we create a view using any table, rowid of view is same as the original table but in case of Materialized view rowid is different (in Oracle)
c) 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.
d) Performance of View is less than Materialized view.
e) 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
f) In case of Materialized view we need an extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in the database.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

How to Create a Database in SQL Server using the T-SQL Query

T-SQL Query to Create a Database in SQL Server
We create at Database in SQL Server, using the below T-SQL Query as follows..

CREATE DATABASE MyAnalyticsDB
ON
(NAME='MyAnalyticsData',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyAnalytics_Data.mdf',
SIZE=100 MB,
MAXSIZE=500 MB,
FILEGROWTH =10 MB )
LOG ON
(NAME='MyAnalyticsLog',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyAnalytics_LOG.ldf',
SIZE=25 MB,
MAXSIZE=250 MB,
FILEGROWTH =10 MB );
 


GO

Here :
MyAnalyticsDB - The Database name

ON The ON we used in two places, one defines the location of the file where the data is stored, and other defines the location where the log is stored.
If you use the ON [PRIMARY] , that indicates that the primary (or main) file group in which to physically stores the data.
NAME='MyAnalyticsData' - The logical name of the Database file that SQL Server internally referring.
FILENAME The Filename indicating the physical name on the disc of the actual operating system file in which the data or log files(based on the sections defined above) will be stored.
By default the file will be located in below(which you can change while installation)
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\'
The Database file will be saved with .mdf(Master Data File) extension and the log file will be saved with   .ldf(Log Data File) extension.
SIZE - The size of the database. By default the size will be in Megabytes. You can specify them in KB's or GB's or even in TB's as well.
MAXSIZE - The maximum size to which Database can grow. If you don't specify the MaxSize then the it is considered to be no limit. So that it will grow till the disc drive is full.
FILEGROWTH - The Filegrowth indicates the how many bytes(in KB, MB, GB, or TB) the Database to be grow. It determines how fast it grows to the MaxSize.
When the initial size of the Database (100 MB in above example) is full then it starts expanding when new objects/data inserted in to db.

In the above example the Database size start out at 100, and then it expands first time to 110 ( at 10 MB FileGrowth rate), and second time it will increase to 120 MB.
The Database size keep increasing till it reaches to the MAXSIZE. Once it reaches the MaxSize, it will not allow to store any data further, so that it will throw errors.

The same applicable to LOG file as well. Once the Log file is full, it will not store any further logs.

sp_helpdb :

One useful system stored procedure is sp_helpdb. This stored procedure returns information about all of your databases on your server such as the size, owner, when it was created and the database settings.


Exec sp_helpdb MyAnalyticsDB

Result :


Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled

Droping a Database :

DROP DATABASE MyAnalyticsDB

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Sunday, October 14, 2018

SQL Server Rank Function to get Top N Sales from a Table

How to use Rank () Function to get Top N and Bottom N Sales from a Table in SQL Server
Suppose we have the Sales data as below. 


From this data, we wants to extracts the Top N ( Eg : Top 3) Sales from each Region.
We can do this by using the Rank() or Row_Number() function with CTE as follows..

With MyCte1
As
(
 Select *,
 Rank () Over ( Partition by [SalesRegion_Id] Order by [Net_Sales] Desc) Rank_N
 From [dbo].[FactSales]
 )
Select Top 9 * From  MyCte1 Where Rank_N IN(1,2,3)
;

Output :


With MyCte1
As
(
 Select *,
 Row_Number () Over ( Partition by [SalesRegion_Id] Order by [Net_Sales] Desc)  Rank_N From [dbo].[FactSales]
 )
Select Top 9 * From  MyCte1 Where Rank_N IN(1,2,3)

;

Output :

In the similar manner, We can find the Top N and Low N sales using the Multiple CTE as follows..

With MyCte1 As
  (
  Select *,
  Rank  () Over ( Partition by [SalesRegion_Id] Order by [Net_Sales] Desc) Rank_Max From [dbo].[FactSales]
  ),
MyCte2 AS
  (
  Select *,
  Rank () Over ( Partition by [SalesRegion_Id] Order by [Net_Sales] Asc)
Rank_Min From [dbo].[FactSales]
  )

Select Top 3 MyCte1.[SalesOrder_Id] [Top_SO_Id],MyCte2.[SalesOrder_Id] [Low_SO_Id],
MyCte1.[SalesRegion_Id],MyCte1.[Net_Sales] AS [Top_Sales],
MyCte2.[Net_Sales]
AS [Low_Sales] 
From MyCte1 Left Join
MyCte2 ON  MyCte1.[SalesRegion_Id]=MyCte2.[SalesRegion_Id]
Where (Rank_Max=1 AND Rank_Min=1)
;

Output :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Difference between SQL Server Delete,Truncate and Drop Commands

What are the Key differences between Delete,Truncate and Drop Commands
TRUNCATE :
TRUNCATE is a DDL command
TRUNCATE is executed using a table lock and whole table is locked for remove all records.
We cannot use Where clause with TRUNCATE.
TRUNCATE removes all rows from a table.
Minimal logging in transaction log, so it is performance wise faster.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
Identify column is reset to its seed value if table contains any identity column.
To use Truncate on a table you need at least ALTER permission on the table.
Truncate uses the less transaction space than Delete statement.

Truncate cannot be used with indexed views. 
Notes :
The Truncate will Fail when there is Foreign key violation, and will get the below error.
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
To avoid this error, we can set the below properties.
SET FOREIGN_KEY_CHECKS = 0; 

TRUNCATE Table TableName; 
--Set back the Check again to default.
SET FOREIGN_KEY_CHECKS = 1;

DELETE :
DELETE is a DML command.
DELETE is executed using a row lock, each row in the table is locked for deletion.
We can use where clause with DELETE to filter & delete specific records.
The DELETE command is used to remove rows from a table based on WHERE condition.
It maintain the log, so it slower than TRUNCATE.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
Identity of column keep DELETE retain the identity.
To use Delete you need DELETE permission on the table.
Delete uses the more transaction space than Truncate statement.
Delete can be used with indexed views.

 /* Deletes all records from a Table */
DELETE From TableName ;
/* Delete records based on Condition*/
DELETE From TableName Where FieldName='Value' 

DROP : 
The DROP command removes a table from the database.
All the tables' rows, indexes and privileges will also be removed.
No DML triggers will be fired.


DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.
DELETE operations can be rolled back (undone) easily, while DROP and TRUNCATE 

operations also can be rolled back if we define them in a TRANSACTION.

DROP Table TableName; 

Note :
We can rollback DELETE, TRUNCATE and DROP Operations when we defined them in a TRANSACTION with Save Points.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Saturday, October 13, 2018

SQL Server Self Join to Calculate the Age of an Incident

How to Calculate the Age of an Incident in SQL Server
Suppose we the Incidents data as follows..
Select * From [dbo].[Tbl_Incidents_All]


Now from this Data, if we wants to know/track the Age of an Incident/Case, the duration between the date of the latest Incident Status and  its the Open date of an Incident.

We can calculate using the below Self Join query as follows:

Select S1.[Incident Id],S1.[Incident Status], S2.[Inc_Open_Date],S2.[Inc_Cur_Date],
DATEDIFF(dd,S2.[Inc_Open_Date],S2.[Inc_Cur_Date]) [Inc_Age_Days]
From [dbo].[Tbl_Incidents_All] S1
Left Join (Select [Incident Id],Min([Incident Date]) [Inc_Open_Date],
                Max([Incident Date]) [Inc_Cur_Date]
    From  [dbo].[Tbl_Incidents_All]  Group by [Incident Id] ) S2
 On S1.[Incident Id]=S2.[Incident Id] and S1.[Incident Date]=S2.[Inc_Cur_Date]
 Where S2.[Inc_Cur_Date] IS NOT NULL

The result as follows..


Further, we can calculate the Incident Age in Hours, Minutes as follows...
Select S1.[Incident Id],S1.[Incident Status], S2.[Inc_Open_Date],S2.[Inc_Cur_Date],
CAST(DATEDIFF(dd, S2.[Inc_Open_Date], S2.[Inc_Cur_Date]) AS VARCHAR) [Inc_Age_Days],
CAST(DATEDIFF(hh, S2.[Inc_Open_Date], S2.[Inc_Cur_Date]) AS VARCHAR) [Inc_Age_Hours],
CAST(DATEDIFF(mi, S2.[Inc_Open_Date], S2.[Inc_Cur_Date]) AS VARCHAR) [Inc_Age_Minutes],
CAST(DATEDIFF(ss, S2.[Inc_Open_Date], S2.[Inc_Cur_Date]) AS VARCHAR) [Inc_Age_Seconds]

From [dbo].[Tbl_Incidents_All] S1
Left Join (Select [Incident Id],Min([Incident Date]) [Inc_Open_Date],
                Max([Incident Date]) [Inc_Cur_Date]
    From  [dbo].[Tbl_Incidents_All]  Group by [Incident Id] ) S2
 On S1.[Incident Id]=S2.[Incident Id] and S1.[Incident Date]=S2.[Inc_Cur_Date]
 Where S2.[Inc_Cur_Date] IS NOT NULL

Result:


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog