Posted in Development, Uncategorized

SQL Server – DROP IF EXISTS in SQL Server 2016

Hi friends, in SQL Server 2016 to check the existence of the object a new optional clause IF EXISTS gets introduced which can be used with the existing DROP statement.

Previously, within IF clause we were checking whether the object is exist or not.

Here is the syntax that we were using in previous versions of SQL server:

–Previous version syntax

IF OBJECT_ID(‘dbo.Test_IfExists’,’U’) IS NOT NULL

DROP TABLE Test_IfExists

IF EXISTS (SELECT * FROM sys.objects where name = ‘Test_IfExists’)

DROP TABLE Test_IfExists

Now, in SQL Server 2016 we can rewrite the above statements in this way –

—DROP IF EXISTS 2016

DROP TABLE  IF EXISTS Test_IfExists

Cool, see the length of code is reduced now and looks simpler :).

Syntax of DROP IF EXISTS i.e. DIE is –

DROP object_type IF EXISTS object_name

Some object_type that be included in DROP clause are like –

Tables, Database, Function, Trigger, Stored Procedure, Column, User, View, Schema etc……

Similarly we can drop the column from a table in this manner –

Syntax

ALTER object_type object_name

DROP object_type If EXISTS object_name

Here we drop the column of a table –

ALTER TABLE Test_Ifexists

DROP COLUMN If EXISTS Name

Hope you like this post !!

Advertisements
Posted in Development, Uncategorized

Steps to create SQL database at Azure

Hi friends, in this blog we will learn how we can create the SQL database at Microsoft Azure.

Login into your Azure account and move to portal.

  1. Move to left side pane of portal and click on SQL databases.

1

2. Currently no database is available so we click on create a SQL database link. A new window will pop up like this –

2

Fill the required details in the create database window as Name of the database, collation of the database etc. I kept the rest of the details as default only you can change the settings as per your requirements.

3. Also select the option ‘New SQL database server’ option to create a new database server in SERVER point. Click on Arrow to further proceed to fill new database server details.

3

Fill the details Login name and Password to login into the server. Also, select your region and click on correct sign to complete this window.

4. In the SQL database pane we can see the new entry of the sql database that we created.

4

5. We can also check the server details from the Servers tab.

5

6. After creating the database, we can also get the option to export the database.

6

7. Click on Export link to export the database. A new window of Export Database gets opened.

7

Specify the filename by what name you want to export the database with extension .bacpac.

8. Now, choose the option to create the new storage account. Again, a new window gets opened of Create Storage Account. Fill the required details as URL, new container name and click on OK sign.

8

Once the database gets created we get the option of opening the database in Visual studio.

9. Click on Open In Visual Studio option. You also get the prompt to add the IP address in the firewall setting, allow that and proceed further.

9

10. After creating firewall rule allowing connection from your IP address, click Open.

11

12

After performing all the above steps, a new azure sql database is ready.

Hope you like this post.

Posted in Uncategorized

Steps to install SQL Server CTP 3 on Windows Azure

Hi friends, SQL Server 2016 CTP 3 is available now and it can be downloaded on Azure. So I tried to install SQL 2016 CTP 3 on Microsoft Azure this time. In this blog I will guide you to install SQL Server 2016 CTP 3 on Microsoft Azure. Primary thing for this is you should have an account of Microsoft Azure before doing any installation. If you don’t have Azure account you can Sign Up and create an account for a month as a free trial.

I already have SQL Server 2016 CTP 2 version on my laptop but this time to install it on Azure, I also first created a trail account on Microsoft Azure and then install SQL Server 2016 CTP 3 version.

To download the SQL Server CTP 3 on Azure visit the download page at Microsoft site –

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

  1. Expand the SQL Server 2016 CTP 3 on Azure at above given site and expand the download section.0

2. Click on Sign In to login into your Azure account.

3. After login, download window appear. Select a deployment model and click on By default, it select Classic so I didn’t make any changes and go with it.

1

4.  After this, it will ask you to create a new virtual machine. Enter a name for new virtual machine in Hostname and also provide Username and password to login into new virtual machine. I entered the name of new VM as SQL2016.

2

5. While creating Virtual Machine, you can also change your Location. By default it is selected as East US. In my case I changed it to SouthEast Asia.

3

6. At the time of creating VM you can change your pricing and storage as per your requirement. By default, it was selected as Standard_DS12 which comes with 4 core CPU and 28 GB storage.

4

7. In resource group, name your new resource group. Be default it is Group and I kept that only.

6
8.  Click on Create button after filling all the information. It will start creating SQL Server 2016 CTP 3 on new VM.

7

9. Once VM is created, you can verify it in Virtual Machines section located at left pane of the window.

8

10. Now, to login into the VM click on Connect option present at bottom pane of window. A file with name with extension .rdp gets downloaded. I checked in my downloads section and find a file woth name rdp.

9

11. Double click on SQL2016.rdp file. A remote desktop connection window pop ups on your screen. Click on Connect.

12. After clicking on connect, it will ask for credentials that you provided while creating VM. Click on OK button.

10

13. You will login into new VM. Select Microsoft SQL Server Management studio in Apps window and click on it to open SSMS window.

11

14. SQL Server 2016 CTP 3 window is opened now.12

15. Enter the login credentials and click on Connect in SSMS.13

Now, playground is ready to play with new features of SQL Server 2016.

So friends, these are steps you need to follow to install the SQL Server 2016 CTP 3 version on Azure. Hope you like this post.

 

Posted in Performance Tuning, Uncategorized

How Client Statistics helps in improving performance in SQL Server

Client Statistics is SQL Server data tool which is very helpful in determining the statistics that how much data received from server to the client side. In other words, client statistics helps in analyzing the traffics load like packets/bytes sent and received at client – server side. When we run a script or query in T-SQL editor, we can enable Client statistics to collect statistics like application profile, time statistics and network statistics which help in checking the efficiency of the script.

Client statistics are grouped into 3 categories:

  1. Query Profile Statistics
  2. Network Statistics
  3. Time Statistics

Let’s briefly look out about the categories of Client Statistics which gives you a picture what these categories show.

Query Profile Statistics

Query profile statistics section shows the information about the query execution like:

How many INSERT, UPDATE, DELETE and SELECT statements are executed in query editor window?

How many rows appeared in query output combining rows of multiple SELECT statements?

How many transaction occur in query window?

Network Statistics

Similarly, network statistics shows the data about size of data send and receive from server to client in form of Packets and Bytes.

Time Statistics

Time statistics shows the data about time taken by server in sending and receiving data.

How to enable Client Statistics?

When you have to work with Client Statistics first we need to enable it using SSMS.

Method 1

Press Shift + Alt + S

CS_1

Method 2

Move to the Query option in Menu Option and click on Include Client Statistics.

CS_2

Method 3 –

Right click in the query window and select Include Client statistics

CS_3

Now let’s put some extra light on Client Statistics internals. As per below screenshot I have executed two SELECT statement in a single query editor pane. As the query gets executed one more tab ‘Client Statistics’ appears in output window.

CS_4

As we can see the screenshot there are some different color Arrows signs (Black, Green and Red) which makes the looks more significant. Green sign signifies improvement in statistics while Red sign signifies degradation in statistics. Black arrow signifies that the value is unchanged from previous run.

Let’s execute the query one more time to get the picture clearer.

CS_5

Every time when a query executed a Trail tabs gets added with latest one in descending order. Average tab is the average of all Trail tab values.

There is also a row with name ‘Client Execution Time’ which shows at which time the query gets executed in T-SQL editor when Client Statistics was enabled.

Now we will see the calculation of each category one by one:

Query Profile Statistics

We don’t have any INSERT, UPDATE, DELETE statement in query window so value of ‘Number of INSERT, UPDATE and DELETE statement’ is 0.

Value of ‘Rows affected by INSERT, UPDATE and DELETE statement’ is 0 as no such statements are present.

Value of ‘Number of SELECT statements’ is 2 as you can see we have 2 select statement in query window.

Value of ‘Rows returned by SELECT statement’ is 4 which is sum of output of each individual Select statement.

Value of ‘Number of transaction’ is 0 as no transaction is happening currently in query window.

 

Network Statistics

Value of ‘Number of server roundtrips’ is 1 as only a single time we sent the request to the server and received a reply.

Value of ‘Number of TDS packets sent’ is 1 which represents the number of packets sent to server.

Value of ‘Number of TDS packet received’ is 1 which represent the number pf packets received from the server.

Value of ‘Number of bytes sent’ is 2 which represent the number of bytes sent to server in TDS packets.

Value of ‘Number of bytes received’ is 2 which indicates the number of bytes received from server in TDS packets’

Time Statistics

Value of ‘Client processing time’ is 20 which indicates the time spent by the driver in processing.

Value of ‘wait time on server replies’ is 30 which indicates the waiting time for the driver spent to get the reply from the server.

Value of ‘Total execution time’ is sum of values of Client processing time + Wait time on server replies (20 + 30 = 50).

This is simple case of client statistics. To get some interesting information now let’s disable the Client statistics from the tab and execute the query one more time. Again enable the Client statistics from any method which is mentioned above and execute the query one more time.

Now one more Trail tab is added and also Red arrows increases. You will observe that values of Trail1 and Trail2 is almost identical while value Trial3 is totally different.

CS_6

Why Trail3 values are different as we run the query only a single time after enabling the Client statistics?

The answer to this is – Trail3 value is the sum of number of execution of query when Client statistics was disabled and sum of the values when Client statistics was enabled again. Means, whenever a query gets executed it will added all the previous values till client statistics is enabled.

Values in Query profile statistics is showing the sum of previous execution value including query execution after enabling.

Value of Number of SELECT statement is 4 [2(Query executed when statistics was disabled) + 2 (Query executed when statistics enabled again)]

Same is with value of Rows returned by SELECT statement i.e. 34 [17 (When client statistics was disabled) + 17 (When query statistics enabled again)]

In Network statistics also values are showing values of sum of query execution when client statistics was disabled and sum of values when client statistics was enabled. You also see Red arrow signs which indicates the degradation in statistics when compare with Trial2.

Same calculation is with Time statistics.

How to reset/clear the Client statistics data?

Go to the Query tab >> Reset Client Statistics.

CS_7

In summary, client statistics also helps you in analyzing the script performance by monitoring the response time of query, including client server execution time, data send in packets/bytes.

That’s all folks. Hope you will like it.