Friday, April 29, 2016

T-SQL Tips: Can a UDF Return Values of Multiple Datatypes?

This was a question raised in one of the forums recently.
This blog explains how we can create a user defined function (UDF) which can return values of different datatypes under different conditions.
Consider the case where we require creating a user defined function which based on the conditions have to return values of different data types. Since a user defined function can have only a single datatype for the return value we need to find a datatype which can store values for different data types. There's a datatype available in SQLServer called sql_variant which can be used for this purpose

As per MSDN sql_variant is :-

A data type that stores values of various SQL Server-supported data types.

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

So we can utilize sql_variant datatype as the return value for the user defined function which will have the ability to store and return values for all the SQLServer supported datatypes

As an illustration we can consider a user defined function as below

ALTER FUNCTION dbo.TestVariant
(
@Mode char(1)
)
RETURNS sql_variant
AS
BEGIN
DECLARE @Ret sql_variant
IF @Mode = 'i'
SELECT @Ret = 1
ELSE IF @Mode = 'v'
SELECT @Ret = 'Test'
ELSE IF @Mode = 'd'
SELECT @Ret = '2015-06-17'
ELSE IF @Mode = 'n'
SELECT @Ret = N'സ്വാഗതം'
ELSE IF @Mode = 'u'
SELECT @Ret = '9DF38CBA-D314-42D8-9DF2-E4BC3108501C'
ELSE 
SELECT @Ret = NULL

RETURN (@Ret)
END

If you analyse the function you can see that UDF returns values of different datatypes based on the input value for @Mode parameter.
Now try executing the function

SELECT dbo.TestVariant('i')

returns

1
similarly see the output for other values of @Mode parameter


As seen from the output its evident that the data type of the value returned by UDF will change based on the input value. Thus we can make use of sql_variant datatype to make sure UDF returns values of various data types.

Tuesday, April 12, 2016

Power BI Tips: Creating a Map Visualization in Power BI

Introduction

This blog explains how you can create a map visualization based report in Power BI desktop. Map visualization is one of the cool features that Power BI provides for doing analysis based on geographical locations. Its pretty useful tool when you want to show any data broken up by geographical locations.

Illustration

To illustrate this, we can consider the below scenario
Consider the case of a retail chain having stores across multiple locations. The consolidated sales figures has been captured as per the below


Now lets see how we can visualize this data using Power BI's map visualization. The cool thing about this is that we dont need to do anything special to make this happen. The map visualization is able to display the location by identifying it from our data. This is made possible by Bing map APIs which it uses under the hood. From the location details it will be able to plot locations.
Now launch the Power BI desktop and point to the above data which is contained in a Excel or a CSV file using Get Data option

Map Visualization

Once the data is ready we can add the map visualization to the Power BI page from the visualizations options


Once you add the visualization you will get below control on the page


Select the control and drag State field to Location and Sales field to Values and you will get the below

You will see that the Bing map control automatically plots the data points in the map based on the location based field (State in this case)
You can see the data by hovering over the respective points in the map as shown below


This will help you to analyse the Sales for the state from the map.
Now if you analyze the data carefully we can see that there are few states where we have details for multiple districts.

If you want to analyze the district wise share of the data then we can make a small tweak in the above map to achieve this. To make this possible drag the District field to Legend property and then map will modify as below


If you analyze this you can see that each point now turns into a pie chart kind of format showing the contribution part of each of district within the state, Hover over a part and it will show district and state information corresponding the the part along with the value for the measure Sales. This is a very helpful visualization for quick reference without even having a need to drilldown to analyze the next lower level details in a hierarchy (geography in this case).

Filled Map Visualization

Another type of map visualization available in Power BI is called filled map visualization. This will plot the data points by filling the corresponding locations in the map. The intensity of the color changes as per the plot value with larger values being displayed in darker colors.
Now lets see how a filled map will look for the same data


The visualization fills the locations (States) based on the value (Sales)

Conclusion

As you see from above example map visualization is a very good option which you can use to do a quick reference analysis of data based on geographical data. Another good thing about this is that this is available as a standard option within Power BI desktop tool is now free to download. Only if you need to deploy this to a server you would need to take a license.
Hope you enjoyed this article on map visualization in Power BI. So what are you waiting for? Download a free copy of Power BI desktop and starting trying out!

Wednesday, March 30, 2016

Windows Build 2016 : Live Streaming

Here's a golden opportunity to watch the live streaming of the Microsoft Build 2016 Conference here for those whose are unable to attend. Microsoft Build is an annual conference event held by Microsoft for Developers. And this year its being held from March 30 – April 1 in San Francisco, CA.


Wednesday, March 23, 2016

Guest Post: Variant Disaster Recovery Plans in SQL Server 2008


Andrew Jackson one of the fellow SQL professionals had expressed his desire to write a guest blog for me. Here's the guest post contributed by him on Disaster Recovery Plans

One of the foremost responsibility of a SQL Server administrator is to prepare plans for recovering data in instances of potential disasters.Nothing turns out to be more futile than a well-designed and pretested backup and restoration plan for recovering data from SQL Server in case of disasters.When a disaster recovery plan is designed the administrators keeps account of various things like the different disasters (both natural and technical), whether the plan will suit the business and environmental needs, etc.
This blog covers the various recovery plans that can be created in SQL and will help the users to understand what the disaster recovery plans in SQL Server 2008 are. In addition to this, the advantages and disadvantages associated with various techniques will also be discussed.

Various Disaster Recovery Techniques in SQL Server 2008

1) Database Mirroring

Database Mirroring is the process of creating and maintaining redundant copies of a database in SQL Server. The main purpose of database mirroring is to ensure uninterrupted database availability in downtime scenarios. It can be implemented on per-database basis i.e. one mirror database is created for every single database. It works only with full recovery model and goes unsupported in bulk-logged and simple recovery models.

Advantages  


  • It increases database availability.
  • Ensures the availability of viable database copy at the time of system upgradation
  • It guarantees that the both the server instances contain identical information.

Disadvantages


  • Continuous synchronization of the main and the mirror database may slow down the performance of SQL Server.

2) Failover Clustering

Failover clustering is the process in which a working server replaces a failed server in scenarios of hardware or software failure. A user can create a single failover cluster for a single SQL Server instance or can create a single failover cluster for multiple databases.
At times of server failure, failover clustering enables the database system to switch the instance processing automatically from a failed server to a working server. Since failover clustering is designed in order to ensure high server availability, the nodes should be situated close to each other.

Advantages


  •  Ensures high availability of SQL Server instances.
  • It occurs automatically in case the primary server fails.

Disadvantages

  • The overall cost of maintaining two servers at the same time is very high.
  •  Since the servers or the clustered nodes should be situated close to each other geographically, this cannot be used in organizations whose branches are spread globally.
  •  It does not turn out to be helpful in cases of disk array failure. 
  • Failover clustering cannot be implemented at database level or object level.

3) Peer-to-Peer Transactional Replication

Peer-to-Peer transactional replication is the process of maintaining several copies of data on multiple server instances that are referred as nodes. It is designed for applications which are intended to read or modify data in the databases that take part in replication.
For instance, if the server hosting the database becomes unavailable, the application can be modified in order to divert the traffic towards the other available servers.

Advantages


  • Improved read performance because the activity can be distributed across all the nodes
  • Processes done in a single node like the update performance, insert performance and delete performance are propagated across all the nodes

Disadvantages

  • All the databases taking part in the replication process must have identical data and schema
  •  Tables and objects cannot be included in multiple peer-to-peer operations in a single database.
  • It does not provide conflict detection or resolution.
  • There may have performance issues in SQL Server transaction replication

4) Creation Of Warm Standby Server

A warm standby server is a secondary server that runs in background. The entire data is replicated or mirrored to this secondary server regularly. This also means that there are times when both the servers do not contain exact data. A warm standby server can be created by using Log Shipping.

Log Shipping

Log shipping is the process of creating automatic backup of the primary database and transaction log files and then restoring them on a secondary server. During the process of restoration, the database exists in an exclusive mode and is unusable.

Advantages

The entire database including objects such as tables and views can be recovered
The data restoration is done faster. The fast restoration process leads to fast data recovery

Disadvantages


  • Since the database is in an exclusive lock mode during the process it will be unusable 
  • The user cannot apply changes to selective tables through the restoration process. All the changes made in the primary server are entirely restored in the secondary server.

Conclusion

The solutions discussed in this write up provides a general account of the technologies that one can use in order to safeguard their data against data disasters. It is to be noted that before deciding which methods suits your organization, a thorough investigation of the concerned method should be done from the user’s end.

Wednesday, February 24, 2016

My Interview Published on Technet Wiki

Glad to share with you all the news that my Technet Wiki had interviewed me and they've posted the interview on their official blogs.

The link is given below


Really feeling nice getting this motivation from Microsoft and being a part of the Technet Wiki community.
I thank each one of you who have been following my blogs, giving me feedback through various forums like FB, Linkedin, Gtalk etc and inspiring me to write again. This really gives me the motivation to come up with quality articles.
Meet you all soon with another quality tip...