How DATEDIFF_BIG a new feature of SQL SERVER 2016 Can Keep You Out of Trouble

In the series of SQL SERVER 2016, this is a new post. in this post, we will discuss DATEDIFF_BIG and how it is helpful.
So, before jumping into directly in technical details, we all know that time is very important and every second valuable and countable but sometimes every microsecond & nanosecond is also countable . For such operations in which every microsecond & nanosecond is countable, we can use DATEDIFF_BIG function.
As you aware the BIGINT range is from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.  Here if any difference (Micro & Nano) second is out of the the mentioned range then DATEDIFF returns that value else return error(Obviously).
Below is the basic syntax if DATEDIFF_BIG although it is similar to DATEDIFF. We can say it is a extended version of DATEDIFF.
DATEDIFF_BIG( datePart, start Date, End date)
The value of datePart is same like DATEDIFF function.
For example if you want to collect millisecond difference then use ms, microsecond then mcs and for nanosecond ns.
As per the MSDN   for the Millisecond, the maximum difference between start date & end date is 24 days, 20 hours, 21 minutes and 23,647 seconds. For Second, the maximum difference is  68 years.  
Now, let see why this DATEDIFF_BIG introduced so, I am running a DATEDIFF  function in SQL SERVER 2012 and see what we get after running that query.

You can see in above query we got an error of overflow.
Now, we are calculating the same difference from DATEDIFF_BIG in SQL SERVER 2016. See, below snap for same.

Isn’t it great ? Although, I am scarred with those applications who calculate milliseconds .
Anyways, it is good to know feature.
Do provide your feedback for the post it is very valuable for us.
RJ !!!

If You Read One Article About Split String in SQL SERVER 2016s Read this One #3

In the Series of SQL SERVER 2016, this is another post. Before Jumping in detail just think if you have a comma or other separator string and if you have to split it by separator field then for such task  in previous SQL SERVER versions either you will write a function which split the string and return desire values in a column  or
you will use XML function or  might be different custom functions.
Let me explain this with below example. Suppose you have a string like below
DECLARE @FriendList AS VARCHAR(1000)
SET @FriendList ='Ravi,Suyash,Vaibhav,Shyam,Pankaj,Rajul,Javed'

Now you want output like below

Then in such cases, you will  follow 2 approaches (their might be other as well)

Approach 1:- Write  a function like below  and use it.

And once this function is created you can use like below

Approach 2 :- You can use XML option in SQL SERVER as  shown in below

So, the good news is now in SQL SERVER 2016 you don’t need to write  so many lines to split any string. In SQL SERVER 2016 a new string function is Introduced which is
STRING_SPLIT
The use of this function is very easy and below is the syntax
STRING_SPLIT (string, separator)
Now, let me show you same output using STRING_SPLIT function

Isn’t it easy ?
I hope you will like this easy way to split the string.
Provide your feedback.
RJ !!!

Do you know Compress & Decompress function in SQL SERVER 2016 ?

This is another article in the series of SQL SERVER 2016 Journey . I am pretty much sure you might aware of Gzip Compression algorithm. If not then try  this link.

So, SQL SERVER 2016 introduce this two awesome functions for Compress & Decompress the data.
Before SQL SERVER 2016 version we have data compression feature like Page & Row compression (check Previous post for it Link )which is different then this column value compression.

In SQL SERVER 2016 Compress function,  data compression is done via GZIP algorithm and return VARBINARY(MAX).

Below is the simple syntax of Compress function

Compress (Expression)

Here Expression can be nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n)

Decompress function is just opposite of  compress function. It is used to decompress the value of VARBINARY which is converted using Compress function. The only tweak is you need to cast the output of Decompress function  in specific data type to make it readable (if using varchar ,nvarchar compression) .

below is the simple syntax of Decompress
Decompress (Compressed string)

Let’s understand this via an example as shown below .

In this example I have taken 3 tables with exact same schema & data

1. 1) IndiandotnetFriends
2. 2) IndiandotnetFriends_Compress
3. 3) IndiandotneFriends_Decompress

You can see  snap in which we are inserting same data.
As the name suggested in first table normal data from Adventureworks’s person table.
In second table we are inserting compressed value of first Name  and in 3rd table we are inserting decompress value of First Name from the Compressed table.
Now, let’s check compress  & decompress table data

Now, Your might thinking that the output of both compress and decompress is not readable.
So you are right to make data readable of Decompress table we need to type cast.
See below snap for same.

Till now we know how to use this Compress & Decompress function. Now, let me share the benefit of using Compress. if you see below snap you will find that data length of compress is comparatively less than normal and decompressed data length .

Obviously, compression helps you somewhere in the overall performance of your application.
The good point is  you can pass the compress data to your .net application and decompress using GzipStream as well.

The only thing which we need to take care is type casting. Suppose your base column which compressed is VARCHAR then you need to typecast again in VARCHAR.

Now, next question is where we can use this functions. So,  we can use in compressing large object like binary data in which we save jpg, pdf , word document etc..

I hope you will be excited in using this function.

Please, share your input.
RJ!

Here Come New Ideas for DROP IF EXISTS in SQL SERVER

In the Series of SQL SERVER 2016 journey, this is our new article. In this article, we are sharing a new cool feature which introduced in SQL SERVER 2016 which is DROP IF EXISTS (DIE) .
In our development many times it happens that we need to drop a table and as a best practice we write the following syntax as shown in below figure

Now, in SQL SERVER 2016 the same task is super easy. You can write the following syntax to drop the table object

DROP TABLE IF EXISTS TABLENAME
The best part is if suppose the object does not exist then  here will be no error execution will continue.
Let me share one more example of Dropping a stored procedure.

Similar, way we can write for following data objects and with the following syntax

Procedure:- DROP PROCEDURE IF EXISTS Procedure Name

Assembly:-
DROP ASSEMBLY IF EXISTS Assembly Name

ROLENAME :-
DROP ROLE IF EXISTS ROLENAME

TRIGGER :-
DROP TRIGGER IF EXISTS Trigger Name

VIEW:-
DROP VIEW IF EXISTS View Name

RULE:-
DROP RULE IF EXISTS RULENAME\

Type:-
DROP TYPE IF EXISTS Type Name

Database:- DROP DATABASE IF EXISTS Database Name

Schema:-
DROP SCHEMA IF EXISTS Schema Name

User:-
DROP USER IF EXISTS Username

SECURITY POLICY:-
DROP SECURITY POLICY IF EXISTS Policy Name

View :-
DROP VIEW IF EXISTS View Name

FUNCTION:-
DROP FUNCTION IF EXISTS Function Name

SEQUENCE:-
DROP SEQUENCE IF EXISTS Sequence Name



Synonym:-
DROP SYNONYM IF EXISTS Synonym Name

I like this feature I am sure you will also like this.

Please, do share your feedback for blog post.
Enjoy !!

9 Amazing features of SQL SERVER 2016

Although, I know I am bit late to share this thing on our blog but it says in Indian proverb “Der aai durust aai” means it’s OK you came late but you came that is more important.
Anyways, so you all might aware that Microsoft launched SQL SERVER 2016 officially in June 2016.
You can download the SQL Server 2016 via Link.
Obviously, this is a new revolution in SQL SERVER series. You will find many great features in this version.
This post is beginning to explore all those great features and we will do deep dive in all those features. In this, post we briefly introducing those features. so, without wasting time let me share a brief introduction.
 
1) JSON in SQL SERVER :-
Is this surprising to you ? Obviously, yes. As you might aware that most of the NO SQL database use either JSON or XML. As XML feature already exists in SQL SERVER so this was time for JSON. You can play with JSON in SQL SERVER 2016.
 
2)Always Encrypted :-
If you talk about security this one the best feature. Now, you are thinking what it means. So, It means that the data in the SQL SERVER reside always in encrypted format and SQL server can perform the operation on the encrypted data without decrypting it. The encryption key can be exist in some other system. With this, feature you can secure you ensure that your data is secure from the person like DBA / Developer as well. These guys also can’t see the actual data. Isn’t it neat ?
 
3) Row Level Security :-
This is another interesting feature which helpful especially to the developers  who needs to write extra code to check this. Let me explain this with an example suppose you have a sales team who do market research and you want to restrict that each sales manager can see only those data which entered by him only in such cases you don’t need to write specific condition in your code. It can be achieved by Row Level Security.
 
4) “R” in SQL SERVER :-
For the data scientist, it is a great NEWS. As Resolution Analytics is purchased by Microsoft and It is incorporated in SQL SERVER. You can run R analysis query in SQL Server.
 
5) Temporal Table:-
The Temporal table holds the old version of ROWS of a table. It means that it maintain a copy of the old rows in the table whenever there is an update on the main table.
 
6) PolyBase :-
With the help of this feature, you can access data which exist in Azure Blob or Hadoop cluster using the same SQL server. In the nutshell, we can say this is the technology which combines both relational & non-relational database in a single umbrella which is SQL SERVER. You can run the query directly on external data like Hadoop or Azure blob storage.
 
7) Stretch Database:-
I am pretty much sure by the name you can guess this feature. So, with the Stretch database you have can store your part of the data in the cloud which depends upon your need. You can say most recent transactional data you can store in your local environment and other old data you can store in Azure.
 
8)Query store :-
Another interesting feature to help you in identifying  performance drag using Query store. When you enable this feature it automatically captures a history of queries , plans,  and statics and retain them for review and resolve the performance issues.
 
9) Mobile report:-
As mentioned earlier this is the revolution in SQL SERVER 2016. In SSRS there are many important changes introduced. Now we can import Power BI report in SSRS and apart from this you can create a mobile report which you can run on Mobile.
 
Now, we started officially SQL SERVER 2016 tutorial series.
 
Moving forward we are going to discuss all these features in details and also the couple of new things which introduced in SQL SERVER 2016.
 
enjoy.
Happy VijayDashmi.