Feeds:
Posts
Comments

TRY_CONVERT is one of the new conversion functions shipped with SQL Server. This function converts expression from one data type to another data type. The beauty of this function is that if it fails to convert, it returns NULL value as a results and due to this functionality, it has an edge over CONVERT function. I started using this function wherever conversion required since SQL Server 2012, however, I received an error message this morning when I was trying to use TRY_CONVERT function in SQL Server 2014 as shown below.

Let me explain this error in detail :

Message Number: 195

Severity : 15

Error Message: ‘TRY_Convert’ is not a recognized built-in function name.

Error Generation:

Let me create a sample using TRY_Convert  function to demonstrate this error.

USE SampleDB
GO
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
SELECT @@VERSION
GO

Msg 195, Level 15, State 10, Line 5
‘TRY_CONVERT’ is not a recognized built-in function name.

Microsoft SQL Server 2014 – 12.0.2000.8 (X64)

Try_Convert .1.2

Ooopps…… I am unable to use TRY_CONVERT even though I am using SQL Server 2014 as shown above.

Resolution:

The resolution is very simple, actually, what you are trying to do is to use TRY_CONVERT function in one of the databases having compatibility less than 110 even though you are using SQL Server 2014. Lets fix this issue step by step.

Step 1: Check compatibility

USE SampleDB;
GO
SELECT compatibility_level
FROM sys.databases WHERE name = 'SampleDB';
GO
--OUTPUT

compatibility_level
——————-
100

(1 row(s) affected)

Step 2: Change compatibility
As you can see above the compatibility of the database is below 110, lets change it to enjoy the core functionality of SQL Server 2014.
Note : Test the compatibility change of your database on your test/development database first, before proceeding to production database.

USE master
GO
ALTER DATABASE SampleDB
SET COMPATIBILITY_LEVEL = 120;
GO

Step 3: Use TRY_CONVERT

USE SampleDB
GO
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
--OUTPUT

Try_Convert .1.1

Conclusion :

Remember, whenever you use NEW SQL Server functions that are compatible with specific versions / compatibility level, you MUST check the version/ compatibility before implementing those functions.

SET NOEXEC is one of my favorite SET statements. I am sure, when I explain it in detail you will love it too. Mostly, we come across in situations when we need to execute batch SQL statements (insert, update, delete) on production databases for the deployment / troubleshooting etc. Sometimes due to the syntax error, some parts of the batch statements execute and the remaining DO NOT and it creates the mess/headache for you to correct the data. And you are left with no option, but to restore the latest backup and fix the script and execute it again.

The solution for this problem is SET NOEXEC. It actually compiles the SQL Query and validate the syntax. The best/fun part is that it DOES NOT EXECUTE the SQL Query. So even if SQL Query is having errors, it DOES NOT affect the data and you are SAFE.

Let me create a sample to demonstrate, how it works.
Sample :

USE AdventureWorks2014
GO
--DROP TABLE tbl_sample
--GO
--Create a sample table
CREATE TABLE tbl_sample
(
 [ID] INT,
 [Letter] VARCHAR(1)
)
GO
--Insert a record in the table
INSERT INTO tbl_sample VALUES (1,'A')
GO
--Browse the data
SELECT * FROM tbl_sample
GO

NoEXEC.1.1

Now, let me turn on the NOEXEC and execute few statements and view the result-set.

USE AdventureWorks2014
GO
-- SET NOEXEC to ON
SET NOEXEC ON;

--Update the table
UPDATE tbl_sample SET [Letter]='B' WHERE [ID]=1
GO
--Delete the table
DELETE FROM tbl_sample  WHERE [ID]=1
GO
-- SET NOEXEC to OFF
SET NOEXEC OFF;
GO

NoEXEC.1.2

The output shows Command(s) completed successfully. It seems, that it not ONLY compiles but executes as well. Lets browse and check the impact of above queries on the data.

USE AdventureWorks2014
GO
--Browse the data to check the impact.
SELECT * FROM tbl_sample
GO

NoEXEC.1.1

As you can see above, the SQL Statements (Update & Delete) DID NOT IMPACT sample table data because NOEXEC was TURNED ON. Hence, it is PROVED that it ONLY compiles the SQL Statements and gives you either an error or Command(s) completed successfully message but it DOES NOT execute SQL Statements.

Note : Once you compiled the statement, DO NOT FORGET to turn NOEXEC off.

Paging became quite simpler & easy to script and manage by using OFFSET & FETCH NEXT keywords in SQL Server 2012 & above. I have written quite a detailed article earlier about it and implemented it in my most of the solutions wherever required. However, when you implement/use paging in your script, you face a big challenge, that is, to find the total number of records in that particular result-set.

Given below are the three methods which you can use to get the total row count from OFFSET / FETCH NEXT.
Before proceeding with the solutions, let me create a sample.

Sample :

USE AdventureWorks2014
GO
-- Create Sample Table Table
CREATE TABLE [dbo].[SalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

-- Insert bulk data into sample table
-- It may take few minutes depends upon the server performance
INSERT INTO [dbo].[SalesOrderDetail]
SELECT * FROM [SALES].[SalesOrderDetail]
GO 100

-- Verfiy the data
Select * from [dbo].[SalesOrderDetail]
GO

Method 1 : Using COUNT(*) OVER()

USE AdventureWorks2014
GO
DECLARE
  @PageSize INT = 10,
  @PageNum  INT = 1;

SELECT
  [SalesOrderID]
, [SalesOrderDetailID]
, [CarrierTrackingNumber]
, [OrderQty]
, [ProductID]
, [SpecialOfferID]
, [TotalCount]= COUNT(*) OVER()
FROM [dbo].[SalesOrderDetail]
ORDER BY SalesOrderID
  OFFSET (@PageNum-1)*@PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;
GO
--OUTPUT

row count using Offset 1.1

Method 2 : Using Common Table Expression

USE AdventureWorks2014
GO
DECLARE
    @PageSize INT = 10,
    @PageNum  INT = 1;

;WITH Main_CTE AS(
	SELECT [SalesOrderID]
	, [SalesOrderDetailID]
	, [CarrierTrackingNumber]
	, [OrderQty]
	, [ProductID]
	, [SpecialOfferID]
	FROM [dbo].[SalesOrderDetail]
)
, Count_CTE AS (
	SELECT COUNT(*) AS [TotalCount]
	FROM Main_CTE
)
SELECT *
FROM Main_CTE, Count_CTE
ORDER BY Main_CTE.SalesOrderID
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY
GO
--OUTPUT

row count using Offset 1.1

Method 3 : Using Cross Apply

USE AdventureWorks2014
GO
DECLARE @PageSize INT = 10,
        @PageNum  INT = 1;

 SELECT
  [SalesOrderID]
, [SalesOrderDetailID]
, [CarrierTrackingNumber]
, [OrderQty]
, [ProductID]
, [SpecialOfferID]
, [TotalCount]
FROM [dbo].[SalesOrderDetail]

CROSS APPLY (SELECT COUNT(*) TotalCount
FROM [dbo].[SalesOrderDetail] ) [Count]
ORDER BY SalesOrderID
OFFSET (@PageNum-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY
GO
--OUTPUT

row count using Offset 1.1

All of the above methods give you the same result-sets. Lets view their performance given below.

S.No

Method

CPU Time

Elapsed Time

1

COUNT(*) OVER()

30654 ms

40372 ms

2

Common Table Expression

11762 ms

7665 ms

3

Cross Apply

11794 ms

7373 ms

Conclusion :
On the basis of above results, I would recommend that you either use Common Table Expression or Cross Apply to get the faster results.

Note : The above queries have been tested on ~12 Million records.

Yesterday (24 March 2015), I had the opportunity to present Extended Events at SQL Server User Group meet-up. This event was held at Microsoft Office in Dubai, UAE. Generally this event is held every last Tuesday of the month. SQL Server User group (www.uaessug.com) have been organizing it extremely well for the last one year. This meet up usually consists of two comprehensive and informative sessions that focus on the SQL Server latest technology, best practice, user experience, tips & tricks. I usually attend this meet up because I get an opportunity to network with SQL Server experts face to face instead of Facebook chat :). I was the speaker in yesterday’s meet up along with Mr. Mike Mohan (CFO at Microsoft).

Following topics were covered in yesterday’s session:

  • Real Life Power BI presented by Mr. Mike Mohan – Microsoft
  • Introduction to Extended Events was by me

We focused on how a non technical user can make best use of Power BI & its integration on different devices, best practices and troubleshooting (the scariest part) throughout the event and hands on with live demos to demonstrate the practical implementations of Power BI & Extended Events.

I hope yesterday’s session was informative as usual. Our next meetup will be held in April 2015. Hope the coming sessions would witness more participants.

Some glimpses of the session:

Mr. Mike busy explaining Power BI :

IMG_0079_U

He showed us how to generate a power map and its auto zoom feature :

IMG_0080_U

Showed us, how to combine many graphs in one page :
IMG_0083_U

Displaying look n feel of Power BI on multiple devices :

IMG_0092_U

Me presenting SQL Server Extended Events :

IMG_9654_U

Showing the components of EE :
IMG_9656_U

IMG_9657_U

Thank you guys. See you next month.

SQL Server 2014 is shipped with lots of exciting features and enhancements, which I usually share with you in my blog from time to time. Today, I will discuss a new enhancement that will minimize your code. This enhancement is actually in the CREATE TABLE (Transact-SQL). Now you can actually create NONCLUSTERED index within the create statement and you do not need to alter table to add NONCLUSTERED index anymore. However, if you want to follow the old method, you can continue doing it. The old method has NOT been discontinued.

Given below are both methods, demonstrating the enhancement.

NEW Method : (Create NONCLUSTERED index within the create table statement)
In this method, we will create NONCLUSTERED index within create table statement. This script is compatible with SQL SERVER 2014 and above.

USE AdventureWorks2014
GO
--DROP TABLE Employee
--GO
CREATE TABLE Employee
(
[Emp_ID] int NOT NULL,
[LastName] varchar(255) NOT NULL,
[FirstName] varchar(255),
[Address] varchar(255),
[City] varchar(255),
[PostalCode] nvarchar(15),
CONSTRAINT pk_Emp_ID PRIMARY KEY ([Emp_ID]),
INDEX IX_Employee_PostalCode NONCLUSTERED (PostalCode) 
);

GO
--OUTPUT

How to create a nonclustered index within the create table.1.1

OLD Method : (Create NONCLUSTERED index after creation of the table)
In this method, we will create NONCLUSTERED index AFTER table creation. This script is compatible with SQL SERVER 2005 and above.

USE AdventureWorks2012
GO
--DROP TABLE Employee
--GO
CREATE TABLE Employee
(
[Emp_ID] int NOT NULL,
[LastName] varchar(255) NOT NULL,
[FirstName] varchar(255),
[Address] varchar(255),
[City] varchar(255),
[PostalCode] nvarchar(15),
CONSTRAINT pk_Emp_ID PRIMARY KEY ([Emp_ID])
)
GO

CREATE NONCLUSTERED INDEX IX_Employee_PostalCode
ON dbo.Employee (PostalCode)
GO

How to create a nonclustered index within the create table.1.1

Conclusion
As you can see above, both methods will give you the same output, however, new method will reduce the line of code. Let me know your feedback about new enhancement.

How to convert hexadecimal to binary became very interesting when I was trying to read SQL Server log. Most part of SQL Server log data is in Hexadecimal so you need to convert it from hexadecimal to multiple formats to read it properly. Generally, programmers use the hexadecimal table to convert it into binary. But I developed this solution using remainder method and used it in almost all my solutions, wherever I used SQL Server log. Given below is the script.

Script :

--DROP FUNCTION dbo.[UDF_Convert_Hex_to_Binary]
--GO
CREATE FUNCTION dbo.[UDF_Convert_Hex_to_Binary]
(
      @HEX VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN

DECLARE @BINARY VARCHAR(MAX)

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @BINARY=ISNULL(@BINARY,'')
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 128) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 64) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 32) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 16) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 8) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 4) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 2) % 2)
+ CONVERT(NVARCHAR(1), SUBSTRING(@HEX, Nums.n, 1) % 2) 

FROM N4 Nums
WHERE Nums.n<=LEN(@HEX) 

RETURN @BINARY
END
GO

Example :

Select dbo.[UDF_Convert_Hex_to_Binary](0x1cFEEE) AS [Hex to Binary]
GO
Select dbo.[UDF_Convert_Hex_to_Binary](0x2efd) AS [Hex to Binary]
GO
--OUTPUT

How to convert hexadecimal to binary.1.1

SQL Server Management Studio is a handy tool that gives us the control to easily manage SQL Server. However, in Azure SQL Server, you can use its own query windows, where you can pass query against database. In today’s article, I will explain how to connect Azure SQL Server using your (on premises) SQL Server Management Studio (SSMS).

Pre-Requisite :

Given below is a step by step approach, demonstrating how to CONNECT Azure SQL using SSMS in simple steps.

Step 1 : (Create SQL Server in Azure)
First of all, you need to create/ setup a Server in Azure SQL Server using pre-requisite article.

Note : If you already have Setup Azure SQL Server, skip this step.

Step 2 : (Configure IP Address)
Once you have setup the Azure SQL Server in the above step, you just need to select that particular Server then further select Configure to add your local Server IP address from where you want to connect Azure using SSMS. This step is most important step because if you bypass this step and unable to add IP address of your local Server here, Azure will not allow your SSMS to connect Azure SQL Server.

Connect Azure SQL using SSMS.1.1

Connect Azure SQL using SSMS.1.2

Note : If you already configured the IP address of your local SQL Server machine, skip this step.

Step 3 : (Open SSMS)
Lets open SQL Server Management Studio and try to connect Azure SQL Server. Given below is the info that you must pass at the time of connectivity as shown below and Press Connect Button. Make sure your caps lock key should be turned on/ off accordingly ;).

Server Name : Azure SQL Server Name.database.windows.net (gx8icm0cm.database.windows.net)
Login : The login name which we created in pre-requisite article step 3.
Password : The password which we created in pre-requisite article step 3.

Connect Azure SQL using SSMS.1.3

Step 4 : (Azure SQL Server Connected in SSMS)
Now, you have connected Azure SQL Server using SQL Server Management Studio as shown below.

Connect Azure SQL using SSMS.1.4

Follow

Get every new post delivered to your Inbox.

Join 2,460 other followers