Difference between Star schema and Snowflake schema

Star Schema	Snowflake Schema
1.  In the Star schema the    fact table was surrounded by multiple dimension tables which are directly connected to the fact through Foreign key	1.  One or more dimension    tables are indirectly connected to the Fact table through the intermediate Dimension tables
2. Both Fact and Dimension tables are in de-normalized form	2. Dimension Tables are in Normalized form but Fact Table is still in de-Normalized form
3. Less number of Joins	3. Large number of Joins
4. Has Redundant data	4. No redundancy
5. Not easy to maintain and change	5. Easy to maintain and change

SQL Server - Deleting Duplicate Records

An easy method for deleting the duplicate records from a table is by using the Common table expressions.

Let us create a table - State with columns StateID and StateName of data types Int and Varchar(20) respectively.

Create table State (StateID Int,StateName varchar(10))
Go
Insert into State
Select 1, 'XXXX'
Union All
Select 2, 'YYYY'
Union All
Select 1, 'XXXX'
Union All
Select 3, 'ZZZZ'
Union All
Select 2, 'YYYY'
Union All
Select 1, 'XXXX'
Union All
Select 3, 'ZZZZ'
Union All
Select 1, 'XXXX'
Union All
Select 2, 'YYYY'
Union All
Select 2, 'YYYY'
Union All
Select 1, 'XXXX'
Union All
Select 3, 'ZZZZ'

Now if we check the data in the State table, it will return 12 records with duplicate values as shown below

Select * from State 

 

Now our intention is to delete the duplicate records so that only 3 distinct records must exist.

For this we can use the Common table expression as below.

With DistinctStates(StateID,StateName,RecCount) as
(Select StateId, StateName, row_number() over(Partition by StateId,StateName Order by StateId) RecCount from State)
Delete from DistinctStates where RecCount > 1

Once we execute the above script all the duplicate Records will be deleted from the table resulting 3 distinct records

Select * from State 

SQL SERVER - ISNULL() vs COALESCE()

Here is the Differences between ISNULL() and COALESCE()

ISNULL() is non-standard and provides less functionality.
COALESCE() is ANSI standard. and is more maintainable, more functional code

ISNULL() Replaces NULL with the specified replacement value
COALESCE() returns first non null value among a list of arguements

ISNULL() uses two arguements only
COALESCE() can use any number of arguements

ISNULL()  returns the same data type as of the first arguement.
COALESCE() is not guaranteed to retrun the same data type as of its first arguement.

Let us see this through an example. At first, Let us declare a variable @str of data type varchar(3). @Str will contain NULL value as we didn't assigned any value to it. Now,

ISNULL(@Str,'ABCDEFGHIJ') retruns 'ABC' , the first three characters of the replacement value as @Str is of datatype Varchar(3).

Where as COALESCE(@Str,'ABCDEFGHIJ') retruns the full string as it does not depend on the  data type of the first arguement.

Declare @str Varchar(3)
Select ISNULL(@STR,'ABCDEFGHIJ') 'ISNULL VALUE', COALESCE(@STR,'ABCDEFGHIJ')  'COALESCE VALUE'

Output :

SQL SERVER – Uses of COALESCE() Function

Coalesce()  can be used to return the first Non-Null argument value among a series of values specified. If all the parameter/arguement values are null, then this  function will return the NULL value.

Examples:

Select Coalesce(NULL,NULL,34,NULL,23)  'First Non-Null Value'

Output :

Select Coalesce('',NULL,'','XYZ')  'First Non-Null Value'

Output :

Declare @Str varchar(10)
Select Coalesce(NULL,@Str,NULL)  'First Non-Null Value'

Output :

Let us consider a table which contains the marks of all the students who belongs to either Engineering or Medical streams.

If the student belongs to the Engineering stream, then the last two columns will have NULL values  where as if he belongs to Medical streams then the Mathematics column will have a null value.

Now Let us see how the query follows.

Create Table  StudentMarks(Id int identity(1,1), GroupId int , Mathematics int , Physics int ,Chemistry int , Zoology int ,Botany int )

Insert Into StudentMarks (GroupId, Mathematics, Physics, Chemistry, Zoology, Botany)
Select 1,100,96,98,NULL,NULL
Union All
Select 1,90,95,94,NULL,NULL
Union All
Select 2,NULL,94,99,89,97
Union All
Select 2,NULL,99,99,99,97

Now here is the query to calculate the total marks that each student, by using the Coalesce() 

Select ID, GroupId, Coalesce(Mathematics,0) + Coalesce(Physics,0) + Coalesce(Chemistry,0) + Coalesce(Zoology,0) + Coalesce(Botany,0) 'Total Marks'
From StudentMarks

Output :

Again Coalesce() can be used to Build Comma-Delimited String.

Here is an example to Build a Comma-Delimited String that contains all the columns of a table using the Coalesce() function.

SQL SERVER – CASE Expression \ Case Statement with Example

Case statement in SQL Server checks a list of conditions
and returns one of the multiple possible result values. Either it compares a single expression or Evaluates a list of Boolean expressions in order to retrun the result

Case statement can be used either in the Data Retrieval Statement-SELECT or in the Data Manipulation Statements- UPDATE and DELETE or in the SET statements.

Syntax:

CASE expression
     WHEN value_expression1 THEN result_expression1 WHEN value_expression2 THEN result_expression2
     [ ELSE else_result_expression ]
END

Or

CASE
     WHEN Boolean_expression1 THEN result_expression1
     WHEN Boolean_expression2 THEN result_expression2
     [ ELSE else_result_expression ]
END

Let us see this through a simple example. Let us first create a table called Student with columns StudentId,  Exam1, Exam2 and Exam3 

Example :

Create table Student
(StudentId int, Exam1 int,Exam2 int,Exam3 int) 

Insert Into Student
Select 1,89,80,90
Union All
Select 2,69,83,95
Union All
Select 3,35,70,90
Union All
Select 4,49,82,60
Union All
Select 5,99,98,95

Now,let us have a glance on the data that we have inserted

Select * From Student

Now Let us see the gradings of the students depending upon the following criteria :

If Total Marks >= 250 then Grade = 'A'
If Total Marks between 200 and 250 then Grade = 'B'
If Total Marks between 100 and 200 then Grade = 'C'
If Total Marks < 100 then Grade = 'E'

Now In order to see the Grades, we can use the Case statement as follows:

Select StudentId,                                                           Case WHEN (Exam1+Exam2+Exam3) > 250 THEN 'A'
       WHEN (Exam1+Exam2+Exam3) between 200 and 250 THEN 'B'
       WHEN (Exam1+Exam2+Exam3) between 100 and 200 THEN 'C' 
        Else 'D'
END 'Grade'
From Student

SQL SERVER – NULLIF() vs ISNULL()

NULLIF() Returns a null value if the two specified expressions are equal. If the Two expressions are not equal then it will return the first expression's value. Whether the returned value is NULL or NOT NULL, NULLIF() will return the same data type as the first expression 

Syntax  : NULLIF(expression1,expression2)

Example 1 :

Select NULLIF(100,50*2)  'NULLIF Value'

OutPut :

Example 2 :

Select NULLIF(2*2,2*7)  'NULLIF Value'

OutPut :

Example 3 :

Select NULLIF(20-2,19)  'NULLIF Value'

OutPut :

ISNULL() Replaces the NULL value with the specified expression value.

Syntax : ISNULL(check expression,replacement value)

Example 1 :

Select ISNULL(null,12) 'ISNULL VALUE'

OutPut :

Example 2:

Select ISNULL(marks,0)  'Marks'  from Student

OutPut :

SQL SERVER – Difference between DML Triggers and DDL Triggers

As we discussed earlier in the previous  article,  DML Triggers will be fired automatically whenever Data Manipulation statements like INSERT,UPDATE and DELETE are executed where as DDL triggers will fire with the Data definition statements like CREATE, ALTER and DROP.       

Now let us see the differences between these two types of triggers. 

DML Triggers	DDL Triggers
1. Operate on INSERT, UPDATE and DELETE	1. Operate on CREATE, DROP and ALTER
2. Applied on Tables and views	2. Applied on Databases and servers
3. Can be used as INSTEAD OF TRIGGERS	3. Cannot be used as INSTEAD OF TRIGGERS
4. Creates INSERTED and DELETED tables	4. Cannot Create INSERTED and DELETED tables
5. DML triggers run either Before or After a T-SQL statement is completed	5. DDL triggers run only after a T-SQL statement is completed