A simple way for consistency TIP #106


Whenever we develop application of maintain application we define sets of rules or policies  like naming convention , data type, database & SQL SERVER properties (like which property should be unable or disable)  but the problem is to cross check or to enforce these properties is very tedious.

To enforce the policies SQL SERVER provided a great feature which is Policy Based Management.  This is the feature which helps you to not only write the policies or rules but also enforce and cross check whether those rules or policies is followed in your environment or not.

Although these feature is mainly for A DBA but I think it is good for a everyone who love SQL Server.

Now let me share an example which will help us to better understand Policy Based Management.

So to begin with  firstly we have to open the Policy Based Management’s interface. Which we can get in Management folder in SQL SERVER’s object explore window. (You can see below yellow highlighted)


So when you expand the Policy Management in Management feature  You will see 3 different folders which are facets , condition and policies.

So there are  around 84 in build facets which is basically properties for different conditions.

Conditions are basically simple check applied using facets.

Policies are created on condition which will enforce once it created.

Below I am creating policy to check table who has row count =0 (Little bit odd) .You can try with stored procedure name not started with sp_ (will be good start as well).

So , Now create a simple condition by right click and choosing menu New condition


You will get following screen just add condition


We are creating a simple condition which will check the table which has RowCount is 0.


Once you created this condition you will get condition in conditions folder.


Now once condition is created we have to create policy. To create policy we have to right click on policy and select new policy.


When you click the new policy you will get following screen


Now we can add check Condition which we have created as shown in below figure. We can apply policy against target Like in below snap we want policy against every table for IndiandotnetDB


We can set evaluation mode according to our need.

Once you saved the policy we can run it any time.

We can evaluate policy by right click Evaluate as shown in below figure


When we run the policy we will get following result


So , If you see above highlighted row which means a table contain  Row Count.

I hope with above steps you understand a basic way to create Policy Base Management concept.

Policy based management is surely a great tool to manage  database.

Please feel free to give a chance whether you are DBA or not.

Enjoy PBM !!

Happy learning !!


Enjoy with new release of Visual Studio and SQL SERVER :)

Dear All,

Although, I am bit late to share this information and might be possible most of you people aware of this.Recently Microsoft released Visual Studio 2015

and SQL SERVER 2016 CTP. Not needed to say that there are various awesome features in both build.

You can download latest Visual Studio 2015 from below link


and SQL SERVER 2016 CTP from below link


In future post you will be find tips & tricks related to SQL SERVER 2016 & Visual Studio 2015.

Enjoy Learning !!!



Merge statement- A simple way for DML TIP#105

When, I first saw this statement a Hindi proverb  come to my mind which is “1 teer 2 nishane” . The meaning of this proverb means a single bow which hit two different aim in one shot.

So let me explain “Merge” statement in more detail. With the help of this Merge statement we can do insert ,update and delete with a single statement.

Let’s understand this by an example. Suppose we have two tables Source and Target as shown in below figure


Now these tables will have some data so lets write query to insert some pre pop data


Now what we want follow things

1) if  employeeId of source and target tables are not matched and if employee name starts with “S” in the source table  then insert the value in target table.  (means insert Sunil & steve’s records from source to destination table)

2) If employeeId of source and target tables are matched then update the Employee name from source table to target table  (if any record matched then update the employee name which not the case with current data)

3) If employeeId of source and target tables are not matched and Employee name in target table starts with “S” then delete that row from target table. (So Sunidhi’s row will be deleted from target table)

Let’s write Merge Statement  for all the 3 above work.



Now you observed that there is one more statements which is written explicitly apart from the condition which is OUTPUT clause which helps us to determine what operation is performed.

When you run this you will get following output


Now see the actual data in both the table.



So, a simple merge statement is doing all 3 operations like update, insert ,delete.

I hope this tip will help you some where.

Enjoy !!


Proper Aliasing will help you somewhere TIP#104

Recently, When we were delivering session on “SQL SERVER” one of the persons asked why we require Aliasing so I thought this might be question in everyone’s mind.

So lets start with couple of well know statements then we will see the actual problem where we need it explicitly.

There are two type of aliasing we can do a column aliasing & table aliasing.

Aliasing might help you in readability of stored procedure or function. It helps when you retrieving any calculated or derived column which need in your application so lets starts with some example.

if you see below example we are calculating a column but it does not have column name so fetch this specific value is very difficult so we require column Aliasing here


So as you saw above figure the calculated column is with name “No column name” we can not retrieve it easily so we require aliasing


Now ,lets talk about the table aliasing. So table aliasing help you in readability and it is helpful when you require self join or same table is required in join.

Lets see in below example we did a self join and we required aliasing


The aliasing will be helpful when same table join is required multiple times.

I hope this tip might helpful to someone.

I would appreciate if you provide you inputs as well where we can utilize aliasing.




It is OK its NULL only TIP #103


We always take NULL very lightly. Like if we are designing database then whether it is necessary or not we allow the data field to accept null.We think it will not affect anything.

Actually , at some point this is not always true.

According to Microsoft NULL is an Unknown value. It is not EMPTY & ZERO. It is just unknown.

Now according to my experience NULL is very sensitive and should be handle with care.

While we are designing the database and in a particular table if a field can not be null according to business rule then please don’t allow NULL in that field for data consistency.

For example if we have a StudentEnrollment table and in the StudentEnrollment table  we have reference of StudentId which is primary key of StudentId and that can not be NULL then please don’t make the field null able.

Let me share some more interesting facts

1) Two null value not equal :-  Let me explain it with an example

Suppose I have tblStudentSource table  which has different columns and a course column which have some null values  as shown below


Now see below snap where we are comparing the null value course column. You will find in below snap that there is no result while we have null value in course


2)  Use IS NULL or IS NOT NULL  to handle null able column:-  If a column is null and you want to show all the columns which are null then instead of comparing NULL value with column as shown in above figure use IS NULL as shown in below figure


In similar way if we want result which not have null course then we can write below query


3) Handle NULL carefully with IN clause

Let me explain with an example so we have 2 tables which are tblStudentSource (as shown in above (image 1) and another table which is tblCourse as shown in below figure


Now if you see we have course column in tblStudentSource table which has values similar to tblCourse’s course column and tblStudentSource’s course column contain some NULL values

Now suppose we wrote following statement and expecting that it will return all the course which are in tblCourse


Oh !! it is not returning any course. The reason behind it is NULL is not handled properly. Now to get desire result we have to write following statement


So , be always careful when your column has null values.

I hope the above points might help you somewhere.

Enjoy !!!

Keep learning & Keep Sharing !!



Microsoft MVP–A dream came true


Dear All,

Microsoft  Most Valuable Professional “ – MVP  is one of the prestigious tag. It is one of the dreams which came true today.

Today , I got the E-mail that I am an MVP in SQL SERVER. 

I am speechless but would like to say a big thanks to everyone who provided their feedback all the time , motivated me each and everyone who helped me to learn more and more.

A special thanks to Sir Jeff Moden  who provides inputs all the times.

Thank you everyone

Thanks for inspiring Smile

Keep sharing  & keep learning



A myth about view TIP #102


I don’t know why every interviewer’s favorite question “Can we insert record using View ?” If you say Yes/No the interviewer will roaming around like so Can you update record using View?  or Can you delete record using view ?

I  hope everyone who is reading this article will be aware of what is view and how to create it ?

If not then need not to worry

“View can be see as a virtual table based on SQL SERVER result or in other world it is a layer above actual data tables” Or we can say when you want to hide actual table then you create a view wrapper”

You can easily create a View with following syntax




Let’s understand this by an example.

Suppose in I have a database with name “IndiandotnetDB” which has a  table “tblStudentSource”

Now I created a  view just for fetching records from tblStudentSource


CREATE VIEW StudentSourceView
SELECT StudentId,
FROM tblStudentSource


Now you can fetch records directly from  View as shown in below

SELECT * FROM StudentSourceView

You will get all the records from tblStudentSourceView

Now the Question “ Can you Insert record from View ?“

So the answer is specific condition you can.

In our scenario we will write following command and execute so the record will be added


So you are clear that we can Insert records from View.

In similar fashion we can update the records as shown below figure

We will following SQL statement as showing in figure in which we are going to update record no 2004 as shown in above figure


As shown record 2004’s FirstValue is updated to value “Updated”


Now in similar way we can delete the record using View.

Although there are certain other factor due to which “Insert/update/delete” is possible.

like we have only simple schema.

I will describe this later with more detail like  scenario where  we can not Insert/Uppdate/delete using view.

Till than Enjoy !!!




Bulk Copy Program (BCP)–A simple way to export data in a file TIP#101

Let’s consider a scenario where the end user require a CSV file of all the records in a table then BCP is one of the simplest way.

BCP stands for Bulk copy program. By the name you got the feeling of lots of data Smile.

Although, there are various options & parameters available with BCP but here we will talk about simple one Smile.

To understand it more clearly lets consider an example.

I have a database with name “IndiandotnetDB” and in this database I have a table with name tblStudentSource which having few records as shown in below figure


Now, the objective here is to export all the data which is in tblStudentSource table to a CSV file.

to achieve this we will use BCP command.  We are running BCP from command prompt as shown in below figure


Although there are various other options available with BCP command but for current post I choose simple one .

Now let me explain the command

BCP IndiandotnetDb.dbo.tblStudentSource OUT d:\File\output\sampleExport.csv –S . –T –c

so in above command “IndiandotnetDB” is my database

“dbo” is schema

“tblStudentSource” is  a table whose data need to be export

OUT – here out stands for export (export the table or query data)

“D:\File\output\” is folder where I want exported file

“sampleExport.csv” is file name which will be created by BCP and contains entire records after execution of BCP command

-S is stands for server I used “.” for localhost we can provide instance name as well

-T is for trusted connection (we can use – U for username – P for password)

-c is stands for character data type if you are not using this then you have to provide data type for each fields of output query result

Below is the output of the command which we run “ a sampleExport.csv file with all the records”


I hope this simple post will help you some where. I will share more details about BCP command in coming post.



Find all the dates in a date range ? TIP #100


It’s almost one month that I didn’t write anything on the blog due to some personal reason. I am really sorry for that.

Now , Lets talk about the scenario sometimes we need to generate a report of total sales in particular date range but the condition is you need to show all the dates whether there was any sales or not.

So first and most important thing for us is to determine all the dates between that particular date range and then determine total sales date wise.

To determine the all the dates which reside between from date & to date  we have 2 approches

First the classic approach with while loop  as shown below

DECLARE @StartDate AS DATE = ‘2005-07-01′
DECLARE @EndDate   AS DATE = ‘2005-07-29′
DECLARE @tblDateRange AS TABLE (salesDate DATE)
SET @SeedDate = ‘2005-07-01′
WHILE @SeedDate <= @EndDate
  INSERT INTO @tblDateRange(salesDate) Values (@SeedDate)
  SET @SeedDate  = DATEADD(d,1,@seedDate)
SELECT * FROM @tblDateRange


Now second and interesting approach

DECLARE @StartDate AS DATE = ‘2005-07-01′
DECLARE @EndDate   AS DATE = ‘2005-07-29′
DECLARE @tblDateRange AS TABLE (salesDate DATE)

AS (
SELECT @StartDate AS SalesDate
SELECT DATEADD(d,1, SalesDate) As salesDate
WHERE DATEADD(d,1,SalesDate) <= @EndDate)

INSERT INTO @tblDateRange(salesDate)

SELECT * FROM @tblDateRange


These are the 2 simple approaches which I like. I appreciate if you share other approaches which are simple & interesting.



Enjoy !!!

How to hide my SQL Server instance in network ? TIP #99


In TIP #70  we saw how to find all the running SQL SERVER instance in a network or a machine.

to revise see below image.


This tip is just opposite to tip #70 you don’t want that your co-worker see your SQL Server instance running on your machine machine. (There are several reason behind this Smile  and security is one of the most valuable aspects)

To achieve this you just need to do a very simple setting. Just follow below steps

1) Open “SQL SERVER Configuration Manager”


2) Once the screen is open right click on the instance which you want to hide from network (under  SQL SERVER network  configuration ) as shown below


3) When you click on Properties menu you will get a new screen as shown below

You need to set the value of Hide Instance option to Yes.


4) Click on apply button and restart the services.

Great , We achieved it. Isn’t it simple ?

I appreciate your feedback.