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)

1

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

2

You will get following screen just add condition

3

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

11

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

4

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

5

When you click the new policy you will get following screen

6

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

NewPolicy

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

9

When we run the policy we will get following result

10

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 !!

RJ

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

https://www.visualstudio.com/downloads/download-visual-studio-vs

and SQL SERVER 2016 CTP from below link

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016 

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

Enjoy Learning !!!

Thanks

RJ

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

Source_And_Target_1

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

insert_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.

 

Indiandotnet_Merge

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

Operations_Merege

Now see the actual data in both the table.

ActualData_Merege

 

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

I hope this tip will help you some where.

Enjoy !!

RJ!!!

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

Calculated_Column

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

Total_Price

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

SelfJoin

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.

Enjoy!!!

Th

RJ!!!

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

Indiandotnet_Student_Source_Table

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

NULL_IS_NOT_EQUAL_TO_NULL

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

IS_NULL

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

IS_NOT_NULL

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

tblCourse_Indiandotnet

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

NULL_Return_Nothing

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

ISNOTNULL_WITH_IN 

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

I hope the above points might help you somewhere.

Enjoy !!!

Keep learning & Keep Sharing !!

Thanks

RJ

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

Thanks

RJ

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

“CREATE VIEW

AS

QUERY “

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
AS
SELECT StudentId,
       FirstName,
       LastName,
       Course,
       Detail
FROM tblStudentSource

Go

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

Indiandotnet_View_1

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

Indiandotnet_View_2

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 !!!

 

Thanks

RJ

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

Indiandotnet_tbl_Student_Source

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

Indiandotnet_BCP_1

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”

Indiandotnet_BCP_Export_Result

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

Thanks

RJ!!

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)
DECLARE @SeedDate AS DATE
SET @SeedDate = ‘2005-07-01′
WHILE @SeedDate <= @EndDate
BEGIN
  INSERT INTO @tblDateRange(salesDate) Values (@SeedDate)
  SET @SeedDate  = DATEADD(d,1,@seedDate)
END
SELECT * FROM @tblDateRange

Indiandotnet_While_Date

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)

;WITH DatesCTE
AS (
SELECT @StartDate AS SalesDate
UNION ALL
SELECT DATEADD(d,1, SalesDate) As salesDate
FROM DatesCTE
WHERE DATEADD(d,1,SalesDate) <= @EndDate)

INSERT INTO @tblDateRange(salesDate)
SELECT * FROM DatesCTE

SELECT * FROM @tblDateRange

Indiandotnet_CTE_Date_Range

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

Thanks

RJ

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.

1

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

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

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

4) Click on apply button and restart the services.

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

I appreciate your feedback.

Enjoy!!!

RJ!!!