Synonyms in SQL Server

It is a SQL Server object.  It provides alternative names to all object except Database Name. We can create synonyms for table, views, stored Procedure, function etc.

It Provides a new layer of abstraction that protects a client application from changes made to the name or location of the base object. Remember, the name of a synonym must be Unique in a database.

Microsoft declared that a synonym cannot be the base object for another synonym, and a synonym cannot reference a user-defined aggregate function.

Operations can be done

1. SELECT
2. INSERT
3. UPDATE
4. DELETE
5. EXECUTE
6. Sub-selects

We cannot reference a synonym that is located on a linked server.

SQL query for Creating a synonym

USE database_name;

GO

CREATE SYNONYM synonyms_name

FOR Database_name.Schema name.table_name

GO

For Reference please visit

https://msdn.microsoft.com/en-us/library/ms177544.aspx

Ghost records in SQL Server

When records are deleted from a table, those records are logically removed by marking them as deleted but not physically removed from the page immediately. It has introduced as a performance optimization that makes the delete operations faster.

What exactly happens when delete command is fired?

The marked record by the delete statement in the data page is marked as ghost (stored in log file .ldf file) and is not released until the Ghost Cleanup Process takes over after the delete operation has done successfully.

Why this records needs to be there?

While performing delete operation on table, if the transaction is cancelled or need to roll back the table data.

Who will remove the Ghost record?

Ghost Cleanup Process or Shrink the database runs to deletes the records physically and frees the database space. Sometimes we can Rebuild indexes to this table also.

SQL Query for ghost records count

SELECT ghost_record_count,* FROM  

sys.dm_db_index_physical_stats(DB_ID('Database Name’),OBJECT_ID('Table name'), NULL, NULL, 'DETAILED')

To run this, we have require 5 parameters or objects mentioned below.

Will see the Output:

Difference between DELETE, TRUNCATE command in SQL Server

First of all DELETE and TRUNCATE are used to remove records from a table in database. But if you look on their system operation, you can get some difference.

DELETE: 

1. It Removes  rows from a table.
2. We can specify WHERE condition over DELETE command. If we don’t use WHERE     condition, then it will remove all the rows from a table.
3. It removes rows row-by-row one at a time and records an entry in the Transaction logs.
4. Delete use the @@ROWCOUNT function to return the number of deleted rows after operation.
5. This is a DML category, it does not change any property of a table.
6. IDENTITY columns are not re-seeded on this operation.
7. When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. 

 TRUNCATE:

1. It removes All rows from a table.
2. WHERE clause we cannot use here, so it will not filter rows.
3. IDENTITY columns will re-seeded on this operation.(After executing Truncate, the identity  value   will be reset from 1 by default)
4. It de-allocates Data Pages instead of Rows and records Data Pages instead of Rows in Transaction logs, thus is faster than DELETE.
5. This is a DDL command de-allocates Data Pages and empty them for use of other objects in the database.
6. TRUNCATE TABLE always locks the page or schema (not each row) when it is executed.

Store data or dataset in SQL server without create table (Row Constructors in SSMS)

Row Constructors in Sql server provides an option to create data set without creating table in database. It has introduced from SSMS 2008 version.

Now will see, how to do that…

Here we will create a set of data with two columns named “Id” and “Name”. The data container (dataset) name will give “Std_details”

select * FROM (VALUES (1,'Pritam'),(3,'Lisa'),(4,'Piyali')) AS Std_details(Id,Name)

Output:

We can do join with such type of queries with table. For example I have mentioned below

There is another set of data named "Marks_details"

select * from (VALUES (1,500,240),(3,500,300),(4,500,450)) AS Marks_details(Id,Total_Marks,Scored_Marks)

Output:

Now will perform Inner join between two different set of data.

SELECT Std_details.*,Marks_details.Total_Marks,Marks_details.Scored_Marks

FROM (VALUES (1,'Pritam'),(3,'Lisa'),(4,'Piyali')) AS Std_details(Id,Name)

Inner join   (VALUES (1,500,240),(3,500,300),(4,500,450)) AS

Marks_details(Id,Total_Marks,Scored_Marks)

on Std_details.Id=Marks_details.id

Output:

Rollback the TRUNCATE Command in SSMS

We know that TRUNCATE is DDL command. So, by properties, it is auto commit.

Some people says DDL operation is not logged in log file. But, in my opinion when we run TRUNCATE in SQL Server then it track the deallocated pages in log file.

If we use TRUNCATE in a truncation scope, then we can roll back the data or table.

Now will see this concept practically.

Let’s create a table called “Sample_table”

CREATE TABLE Sample_table (ID INT,name varchar(30))

INSERT INTO Sample_table values (1,'Pritam')

go

INSERT INTO Sample_table values (2,'Lisa')

go

INSERT INTO Sample_table values (3,'Sourav')

We will check the table data.

SELECT * FROM Sample_table

Output:

Now will go for Transection scope.

BEGIN TRAN

TRUNCATE TABLE Sample_table

After truncate the table, will check the table value.

SELECT * FROM Sample_table

Output:

Now, will do roll back.

ROLLBACK TRAN

After roll back  the table, will check the table value.

SELECT * FROM Sample_table

Output:

Indexed or materialized views in Microsoft SQL Server

View with Indexing applied is called Indexed or materialized view. It improves query performance because it store in the database in the same way a table with a clustered index is stored. 

Indexed or materialized view is a Schema binding version of Base table. The first index created on a view must be a unique clustered index, After that we can create more non clustered indexes.

Why unique clustered index first?

Before creating Pinter data layer of raw data, it required the data to be physically arranged. The unique clustered index do this job in view.

Where to use?

In case for high security and Structured database, if vendor does not allow to change the indexes on the base tables, then it is a useful concept for enhancing technique.

For Data Warehouse/Reporting/OLAP systems it gives benefit. 

Now will go for a sample materialized views. 

CREATE VIEW DBO.VU_EXAMPLE

(EMPLOYEE_BID,FORMATTED_NAME,JOB_NUMBER,TOTAL_WORKING_HOURS)

WITH SCHEMABINDING

AS

 

select

 EMPTIME.EMPLOYEE_BID

,EMP.FORMATTED_NAME

,EMPTIME.JOB_NUMBER

,SUM(EMPTIME.WORKING_HOURS_PER_DAY) AS TOTAL_WORKING_HOURS

FROM DBO.FACT_EMPLOYEE_TIMESHEET EMPTIME

INNER JOIN DBO.DIM_EMPLOYEE EMP ON

            EMP.DIM_EMPLOYEE_ID=EMPTIME.DIM_EMPLOYEE_ID

GROUP BY

 EMPTIME.EMPLOYEE_BID

,EMP.FORMATTED_NAME

,EMPTIME.JOB_NUMBER

See, here most of the cases I have used “DBO.” Before the table because it is schema binded. Other wise you will get error like “invalid for schema binding. Names must be in two-part format”

Here, the word  “WITH SCHEMABINDING” is mandatory.

So, Now will execute  the view with Actual exection plan, After that will open the plan and look what’s happening?

SELECT * FROM VU_EXAMPLE

Here, the view behaving same as other normal views in SSMS. Because when we call the view,then it will goes to base tables and return the result set.

We can tell the view is not yet Materialized.

Now, will Materialize the view by creating Unique cluster index of first column (EMPLOYEE_BID).

CREATE UNIQUE CLUSTERED INDEX IX_VUEXAMPLE_EMPLOYEEBID ON DBO.VU_EXAMPLE(EMPLOYEE_BID)

Now, will execute this code. While executing this will throw the error like “VU_EXAMPLE' because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list”

From  Microsoft, they have mentioned following:

·         If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

·         If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

Now we will change our create query by adding  COUNT_BIG function.

CREATE VIEW DBO.VU_EXAMPLE

WITH SCHEMABINDING

AS

 

select

 EMPTIME.EMPLOYEE_BID

,EMP.FORMATTED_NAME

,EMPTIME.JOB_NUMBER

,SUM(EMPTIME.WORKING_HOURS_PER_DAY) AS TOTAL_WORKING_HOURS

,COUNT_BIG(*) as count

FROM DBO.FACT_EMPLOYEE_TIMESHEET EMPTIME

INNER JOIN DBO.DIM_EMPLOYEE EMP ON

            EMP.DIM_EMPLOYEE_ID=EMPTIME.DIM_EMPLOYEE_ID

GROUP BY

 EMPTIME.EMPLOYEE_BID

,EMP.FORMATTED_NAME

,EMPTIME.JOB_NUMBER

And create the UNIQUE CLUSTERED INDEX over this View

CREATE UNIQUE CLUSTERED INDEX IX_VUEXAMPLE_EMPLOYEEBID ON DBO.VU_EXAMPLE(EMPLOYEE_BID)

So, Now will execute  the view with Actual execution plan, After that will open the plan and look what’s happening?

SELECT * FROM VU_EXAMPLE

Here, the view behaving  very different from normal views in SSMS. Because when we call the view,then it will cluster scan of UNIQUE CLUSTERED INDEX column and return the result set.

Apply Case statement in Where clause in SSMS

Here i will explain, how can we apply Case statement in Where clause.
Look on the example given below.

I am having a Table called DIM_DATE.
This table contain 4 column (DATE, MONTH_OF_YEAR ,WEEK_OF_YEAR,YEAR)

Now look on the below query :-

DECLARE  @Rpt_Parm_Is_Week BIT

        ,@Rpt_Parm_Month INT

        ,@Rpt_Parm_Week INT

           

SELECT DATE,MONTH_OF_YEAR ,WEEK_OF_YEAR,YEAR FROM DIM_DATE

 WHERE YEAR=2015

           

 AND MONTH_OF_YEAR = CASE WHEN @Rpt_Parm_Is_Week = 1 THEN  MONTH_OF_YEAR ELSE @Rpt_Parm_Month  END

           

 AND WEEK_OF_YEAR =  CASE WHEN @Rpt_Parm_Is_Week = 0 THEN WEEK_OF_YEAR ELSE @Rpt_Parm_Week END    

Now   will pass some value into those parameter.

 @Rpt_Parm_Is_Week = 1

 @Rpt_Parm_Month = 5

 @Rpt_Parm_Week=21

 Output: Will get only one week(21) value for particular month(5) in 2015 year.

Now   will pass fasle value into @Rpt_Parm_Is_Week parameter.

 @Rpt_Parm_Is_Week = 0

 @Rpt_Parm_Month = 5

 @Rpt_Parm_Week=21

Output: Will get whole one month(5) record for 2015 year.