Big NEWS Microsoft SQL Server Developer Edition is now free

Dear Friends,

This is one of the Big NEWS which I came across recently which is SQL Server 2014 Developer Edition is now a free Edition.

For more inside please visit below link  Microsoft SQL SERVER Developer Edition is free

Thanks & Best Regards,

Rj

 

How to use SORT control in SSIS ? TIP #115

Dear Friends,

In the series of Learn SSIS step by step this is the 5th post. Now from this post we are going to use each transformation control one by one.

So, lets start with simplest one transformation control which is “Sort”.

By the name it is clear data it will sort the data which is provided to it and give sorted output as a result. Lets understand this by below step by step example. (Here I am not writing step to start visual studio and create a new project, I am pretty much sure you are aware of this now.)

Before going further let me tell you want we are going to do  here. We will have a input result (which will be a text file) and then sort it and save in another file.

Step 1:-I renamed package to SortPackage if you want you can rename your package as well. Now drag drop a Data Flow task as shown below and double click DATA flow task

Step 2:- When you click it you will get a data flow screen where you can drag drop FLAT file source. You can choose source assistance as well. Now configure this flat file source. Means give the path of the file which you want to have as a input source. I am taking a text file which contain fruits name in different order. as shown in below figure

Right click flat file source click on EDIT option and follow the screens

Step 3:- Now once the file is configure. We have to drag drop SORT control from SSIS tool box as shown in below figure.

I also added the output of flat file source to sort control. As shown in above figure. Now configure it. Click on Sort control and you will get following screen. As show in below screen you can sort the data on any column and in any direction like Ascending or descending.

Right there is only one input column which is name so we are sorting name in Ascending order as shown below.

Step 4:- Now once this configuration is done we will save the data in new file with new name which will be sortedFruits. Now to achieve this drag drop the destination control. So I took same float file destination as shown in below figure

Step 5:- Now configure this file destination. Which means where we need to save this file and what are the columns which we required. so In current case there is only one column which is Name (Fruit name) and I am saving this file at same place with sortFruits name. so lets configure it by clicking right click on flat file destination and click on edit button

Step 6:- Once this is configured we need to run this package by pressing F5.When you run hit F5 and everything going right then in execution screen at each step you will find green right check image as shown in below screen

Step 7: Now to cross check we will first see whether that file is created or not. If created then whether we have sorted data or not. So lets open the flat files.

So, if you see at provided destination location a file is created and the data inside this file is sorted file.

I hope you like this first transformation control. till then Enjoy!!!

Thanks !!

RJ!!

How to create first basic package with SSIS ? tip #113

Dear friends,
CodeProject
In last post #112 we understood WWH (What ,Why & How ) of SSIS. Now , lets move now real quick in practical session where we will try to create a basic simple package.

The example which we are creating is well known Export data from SQL SERVER to a flat file.

Step 1:-  Open SQL SERVER Data Tool from start menu

Step 2:- Once it is open create a new project by clicking new project option. You have to select proper template as highlighted in below figure and give a name to project. As shown in below figure

Step 3:- Now drag drop data flow task control from SSIS toolbox. You can give customize message by click control’s text. I prefer this habit so down the line if  after few month or years if you need to do some maintenance or logic change you don’t need to think a lot for why this control is for.

Step 4:- Now double click on Data flow control or click on data flow tab. Now on this area you have to drag drop source assistance. When you drag drop it you will get a pop as shown below.

 

The screen source assistance is the way by which we can select the data source on which we need to perform operation.  As you are seeing in the image there are different data sources

Like SQL SERVER, Excel, Flat file, Oracle.

Although, you can select other sources also from SSIS toolbox as shown in below screen (As you are seeing there are various individual sources exists in toolbox itself so either use source assistance or drag drop individual source.It is worthless to explain here that excel source for excel file, flat file source for flat file and so on.

In this example we are selecting SQL SERVER. When you select Source Type then you have to configure connection Manager.For this we have to select “NEW” in connection manager panel and click OK button.

You will get below screen where you can give all the information related to  SQL SERVER by which our package can connect with that data source. below I am using my SQL SERVER installed on my machine and using AdventureWorks database as shown in below image.

 

Now once connection is setup. Now we have to export a particular table data in a flat file. but you are wondering which table or data which we are going to export.

Step 5:- Now to select data which whether it is entire table, or stored procedure output , or view output or just simple SQL query. for this we need to double click on OLEDB data source and then we will get following screen.

Now ,here we can choose data access mode either table or view, or SQL command ,SQL command with variable. To make this first example easy we are choosing table or view and selecting “Product table “ in below drop down for Name of the table or the view.

Step 6:- Now once you have selected table or view you can select specific columns which we need to export in flat file. For this we have to select columns option available on left side. when you click it you will get below screen.

As shown in above figure you can check uncheck the columns which you need to export in flat file. we can rename the column name as well (as I did standard Cost to MRP). If you see below image

Here I am not explaining errorout option in detail in general sense just think it is configuration step if something failed.

Step 8:-

Now, we have source which we need to export in flat file, for this we may require a destination file in which we can store the data. So, Now we drag drop destination control which will be a flat file destination control as shown in below figure.

Step 9:-  Now in above image you are seeing there are 2 arrows which is just flow direction means where the data needs to flow. Obviously in our case the data needs to flow from oledb source to flat file destination. So what we  need to do drag the blue arrow and release it on flat file destination as shown below.

 

Step 10 :- I don’t know whether you noticed or not but let me tell you here. If you see above figure data is flowing from oledb source to flat file destination which is good but on same time there is cross image in red color which means there is some error in the control. So guess what is the error ?

I think you picked right the destination is not configured. So to do this we need to double click the flat file destination.

Step 11: When you double click you will get below screen. In which you need to configure the file location and file format like whether you want a delimiter file, fixed length file and many other option as shown in below figure. In our example we are using delimiter file option.

Step 12:- When you hit OK you will get following screen where you need to configure as shown in below figure. You need to give file location with file path. if you want different delimiter the you can choose that also.

Step 12:- Now press OK you will get flat file destination editor in which you can select mapping option and just check it for your query whether all the selected columns from source are aligning or not.

Step 12:- Once we done with this you will see the cross image in red disappear. If you are still seeing this it means there is something going wrong with configuration.

Now if everything is good then we can run our first own created package by pressing F5 or with Start option in IDE.

Step 13:-  If everything is correct you will get right check in green apart from this you might be interested how many rows transfer from source to destination so that information also can be found. see below image for detail.

In our case we moved 504 rows. Now lets cross check at the destination location as well whether the file is created or not with these 504 rows.

WOW , we did it . We created our first simplest package which is export data from SQL to flat file.

I hope you enjoyed the learning. In next step we will do something more advance. mean while I request you all to do same practice and try to use excel instead of flat file.

Please do write your inputs. Let me know whether you are enjoying this series or not.

Enjoy !!!

RJ!!!

How easy or difficult to write dynamic SQL ? TIP#110

This is one of the challenge for most of the developer to write dynamic SQL. Generally we follow the approach of string concatenation.

This seems very easy but we need to cast the  parameters in VARCHAR and sometimes we stuck in single code.

I am sure this happened with all of us. 

Let’s understand first a straight forward way which we (most of  us) are  using.

In example I am using person table of Adventureworks database and it is just a simple query  which provide person row according to primary key.

Now the above query is OK but it can be write in much better way with one of the SQL server in build stored procedure which is sp_executeSQL. This is one of the best way which have certain advantage which will discuss in next tip. Now see how we can write above query in much better way

DECLARE @BusinessEntityID   INT
DECLARE @DynamicSQL NVARCHAR(200)
DECLARE @Parameters NVARCHAR(100)

SET @BusinessEntityID = 1
SET @Parameters =N’@ParameterBusinessEntityID INT’

SET @DynamicSQL = N’SELECT * FROM [Person].[Person] WHERE BusinessEntityId = @ParameterBusinessEntityID ‘
EXECUTE sp_executesql @DynamicSQL, @Parameters,  @ParameterBusinessEntityID =@BusinessEntityID

If you see above query, you will find we are not using any type conversion the statement is clear.

This is one of the best way to write dynamic query which is not only increase your statement’s readability but also increase performance of your query with certain amount.

Which we will discuss in next tip.

I hope now you are eager to use this and replace all your old fashion dynamic queries.

Please do post your feedback.

Enjoy !!!

RJ !!!

An interesting setting for NULL but don’t use it for future TIP #109

As we discussed earlier in TIP#103 for NULL in which I shared that we have to take extra care for NULL.

Now in this tip I would like to share one of the interesting setting for NULL. Although it is just for knowledge but don’t use it because it is deprecated in future version and by default you this setting is always on.

Still you are thinking for which setting I am talking about.

So , I am talking about “CONCAT_NULL_YIELDS_NULL”  . Let’s understand it by following example . By Default CONCAT_NULL_YIELDS_NULL is ON which means if anything added to NULL will be null as discussed earlier in our tip #103

Now, see what happens when we do it OFF.

So, We clearly saw in above image  when we set the CONCATE_NULL_YIELDS_NULL property to OFF it dissolved the NULL property.

My take on this property is that we should avoid it don’t try to make it OFF explicitly because if we do this then we explicitly breaking some hidden business rules.

I hope you will like this post.

Enjoy !!!

RJ !!

A big issue when try to Alter user define table type structure TIP #108

 

I hope all of you aware of  User define table type (a table value parameter) which we discussed earlier in TIP #57.

Now recently one interesting incident happened. We are using a user define table type in few stored procedure and due to some business requirement change we need to change /update data type of a particular column from TINYINT to SMALLINT.

Now this change was seems very simple you just need to change a column’s data type  but when you are going to do this, you will find this is not pretty straight forward (if the User define table type is referred in different tables).

If you go through standard steps you need to follow below steps (for  a column data type change)

1) Create a new User define table  type

2) Replaced old User define table  type with new user define table type in each stored procedures

3) Remove old User define table type.

And , I would like to say a big thanks  Mr.Norlado  who post an alternative on stackoverflow

below is the alternative steps

1) Rename the existing table type with following command

EXEC sys.sp_rename ‘dbo.StudentTableType’, ‘zStudentTableType’;

2) Create Table type with your  changes which you want

CREATE TYPE dbo.StudentTableType AS TABLE(
    StudentId INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    ClassId SMALLINT — changed from tinyInt to smallint
);
3. Update the reference in sql entities

DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + ‘.’ + referencing_entity_name
FROM sys.dm_sql_referencing_entities(‘dbo.StudentTableType’, ‘TYPE’);

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;

4. Now drop the renamed table type

DROP TYPE dbo.zStudentTableType;

This 4 steps helped me a lot.

I hope this may help you as well.

Enjoy!!!

RJ!!!

An interesting way of aliasing TIP#107

I recently gone through something and found a unique way of aliasing. I thought it must be share so other techies also aware of it (or might be you already aware of it).

See below example

SELECT *
FROM (VALUES (‘Rajat’,30),
             (‘Sandeep’,40),
             (‘Sunil’,35),
             (‘Shreya’,50),
             (‘Virendra’,45)) AS T(Name,Runs)

Now when you it  you will get following result

I hope you may like this tips.

Enjoy!!!

RJ!!!

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

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

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

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

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.

Enjoy!!!

Th

RJ!!!