Control SQL Server 2012 with gestures using Microsoft Kinect!

Funny demo by Microsoft people at PASS Summit 2011. Enjoy :)

Round UP with CEILING

Rounding up in TSQL is bit tricky but if you want just to round up a decimal value to the nearest integer then you can use tsql Math function CEILING function to achieve this easily.

CEILING function return the next integer that is larger than the number entered.

SELECT CEILING(43.98) -- Returns 44
SELECT CEILING(43.18) -- Returns 44
SELECT CEILING(0.18) -- Returns 1
SELECT CEILING(-0.18) -- Returns 0
SELECT CEILING(-43.18) -- Returns -43

MSDN reference : http://msdn.microsoft.com/en-us/library/ms189818.aspx

View SQL Server Plan Cache

The major reason we use Stored Procedures is because it allows SQL Server to cache the Execution plan so it can be reused. This gives a performance boost as the Query Optimizer doesn't have to go through the expensive steps of choosing the right query plan for the query.

But have you ever actually seen whether your stored procedure is actually has a plan cached in the server or whether it is been reused? There are quite a lot of scenarios Query Optimizer is forced to do a recompile of a Procedure which can be a huge performance hit. It is entirely a different topic for another article which I will write in the future. 

In this article I am going to show you how you can view whether a procedure is cached or not, how many times it has been reused, etc.This is the query which you can use to view these information;

SELECT UseCounts,RefCounts, Cacheobjtype, Objtype, 
ISNULL(DB_NAME(dbid),'MissingDB') AS DBName, TEXT AS SQLSTMT 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 

This will return below columns;

UseCounts	Number of times the plan has reused since caching
RefCounts	Number of times the other cached objects reused this plan
Cacheobjtype	Object type out of one of the following;                                                        Compiled Plan Parse Tree Extended Proc CLR Compiled Functions CLR Compiled Procedures
Objtype	Object type with ragards to the database (SP, View, Trigger,  etc)
DBName	Database name which the cached object belongs to
SQLSTMT	SQL Statement Cached

However execution plans are not kept in the cache forever. They removed from the cache if one of the following criteria's met;

• More memory is required by the system
• The "age" of the plan has reached zero
• The plan isn't currently being referenced by an existing connection

Aging is happens using a formula that multiplies the estimated cost of the plan by the number of times it has been used (UseCounts). There is a SQL Process called "Lazywriter" (Love the name) which is responsible for removing plans from the cache.


You can see this at work, first select a particular SP. Then simply restart the SQL Server Service and then run the above script. You wont see your SP in the cache, then run the SP. Then run the above script again, then you can see your SP is cached. Running again you will see the UseCounts column increases if your SP is not recompiling at each run.

This is a good way to be sure your SP is not recompiling as there are lot of situations where it does.

Formatting Dates in SSIS

When you want to format a date in a SSIS package it is not straightforward. However there are two ways you can do this.

First method is formatting the date from your TSQL. You can use the CONVERT() function to do this;

CONVERT(VARCHAR(20),GETDATE(),101) -- 08/25/2011
CONVERT(VARCHAR(20),GETDATE(),103) -- 25/08/2011
CONVERT(VARCHAR(20),GETDATE(),105) -- 25-08-2011
CONVERT(VARCHAR(20),GETDATE(),112) -- 20110825

For more formatting codes with CONVERT() function please refer this http://www.sql-server-helper.com/tips/date-formats.aspx

The second method is converting it in a expression in SSIS. Below is a sample code for that;

(DT_WSTR,4)YEAR(GETDATE()) + "/" + RIGHT(“00″ + (DT_WSTR,2)MONTH(getdate()),2) +  "/" + RIGHT(“00″ + (DT_WSTR,2)DAY(getdate()),2)


This will format the date to the format of  YYYY/MM/DD. You can switch the statements and format as you desires.


Please leave a comment if you have any questions regarding this.

FIXED - Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Problem :
After installing and configuring a SQL Server instance when I try to connect to it using SSMS from another machine, I got the below error;

TITLE: Connect to Server
------------------------------
Cannot connect to mmsdevelopment.
------------------------------
ADDITIONAL INFORMATION:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476


Solution :
After digging in to the issue I found that windows firewall on the server is enabled and it refuses any connections to SQL Server used port (1433). The solution is adding this TCP port (1433) to the Windows firewall exceptions list.

This is a technet article which details the steps of adding a port to Exception list
http://technet.microsoft.com/en-us/library/cc784523(WS.10).aspx

Apart from this specific scenario there are other situations which can cause the same error to appear. Below are the steps you should take to rectify this as detailed in the MSDN;

Cause	Resolution
Server name was typed incorrectly.	Try again with the correct server name.
The SQL Server service on the server is not running.	Start the instance of SQL Server Database Engine.
The TCP/IP port for the Database Engine instance is blocked by a firewall.	Configure the firewall to permit access to the Database Engine.
Database Engine is not listening on port 1433 because it has been changed, or because it is not the default instance, and the SQL Server Browser service is not running.	Either start the SQL Server Browser service, or connect specifying the TCP/IP port number.
The SQL Server Browser service is running but UDP port 1434 is blocked by a firewall.	Either configure the firewall to permit access to the UPD port 1434 on the server, or connect specifying the TCP/IP port number.
The client and server are not configured to use the same network protocol.	Using SQL Server Configuration Manager, confirm that both the server and the client computers have at least one enabled protocol in common.
The network cannot resolve the server name to an IP address. This can be tested using the PING program.	Fix the computer name resolution problem on your network or connect using the IP address of the server. This is not a SQL Server problem. For assistance, see your Windows documentation or your network administrator.
The network cannot connect using the IP address. This can be tested using the PING program.	Fix the TCP/IP problem on your network. This is not a SQL Server problem. For assistance, see your Windows documentation or your network administrator.

Create table script from Excel file

In practice always the database design is not done using CASE tools. Some times there are projects comes from clients with database table definitions in Excel files. Trying to create tables from these Excel definitions can be a tedious task for large tables. So here is a spreadsheet sample template you can use to minimize your effort. This helped me to create few tables within matter of minutes as oppose to few hours it would have taken.

The A1 Cell should contain the table name and the last row's G column should contain a close bracket ")". After pasting your table structure in between simply copy the whole G column values and run it on the SQL Server database and you are done.

Download the template from here

Update sequential number column over another column values

This is something I had to do recently. If you don't get this right this can be really complex.

Lets say you have a table named Invoice which has data as in the below screenshot. So you want to create a new ID column which will be auto incremented for each Customer. i.e CustomerID 1 will have 1,2,3,4 values and then for CustomerID 2 it will again start from 1..

You can do this using a after update trigger. But in order to populate column for existing data what would you do. Most of you might say use a Cursor, but more often than not there is a SET based query we can use to do things Cursors do. So here also you can use ROW_NUMBER() OVER to accomplish this task.

Lets create the table for this example and lets keep it simple;

CREATE TABLE dbo.Invoice
(
InvoiceID INT IDENTITY(1,1) PRIMARY KEY,
CustomerID INT,
Amount MONEY,
PAID BIT DEFAULT 0
)

Now lets insert some test data;

INSERT INTO dbo.Invoice VALUES 
(1,200,0),
(1,250,0),
(1,400,0),
(1,320,0),
(2,120,0),
(2,50,0),
(3,10,0),
(4,150,0),
(4,100,0),
(4,140,0)

Now lets add the new column to be populated;

ALTER TABLE dbo.Invoice 
ADD CustomerInvoiceNumber INT

Now is the real query which populates the column based on the CustomerID;

;WITH CTE (InvoiceID,CustomerInvoiceNumber) AS 
(
SELECT InvoiceID,ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY InvoiceID) FROM dbo.Invoice 
)

UPDATE I 
SET CustomerInvoiceNumber = CTE.CustomerInvoiceNumber
FROM 
dbo.Invoice I INNER JOIN CTE ON I.InvoiceID = CTE.InvoiceID

We are using a CTE because we can not use PARTITION BY in the Update clause directly. This will update the CustomerInvoiceNumber Column with the desired values as below;