Feeds:
Posts
Comments

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

I presume this will be your first step towards Azure SQL Server and I am sure you will find this article as a guideline to proceed and create SQL Server & database in Azure.
Given below is a step by step approach, demonstrating how to create them using simple steps.

Step 1 : (Create Azure Account)
First of all, you need to create a Microsoft Azure account (it is free for one month ONLY, however it asks the credit card details for verification ONLY).

Free tial account of Azure.1.1

Note : If you already have Azure account, skip this step.

Step 2 : (Select SQL Databases)
Once you create the account and Sign in to Microsoft Azure, you will be in the Microsoft Azure portal as shown below. Here you can see a lot of different exciting features, however, you need to select SQL DATABASES from the left pane. Once selected, you will see sql databases dashboard on the right hand pane. In order to create a NEW SQL SERVER, you need to select SERVERS from top and press + button at the bottom as shown below.

Azure Create SQL Server & Database 1.1

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

Step 3 : (Provide SQL Server Info)
Now you are in the SQL database server settings screen. Here you need to select the SUBSCRIPTION (if you have ONLY one then it will be selected by default), LOGIN NAME, PASSWORD and REGION (always select the nearest region which gives you the best performance) and press the CHECK MARK shown below.

Azure Create SQL Server & Database.1.2

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

Step 4 : (SQL Server Creation)
Now, your server has been started creating and it generally takes 1-2 minutes.

Azure Create SQL Server & Database.1.3

Your server has been created as shown below.

Azure Create SQL Server & Database.1.4

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

Step 5 : (Select the Server)
Now, you need to select the SQL Server you just created in the above step. (If you already have a server, you can select it). Once you selected the server, it will take you to the SQL Server dashboard. Here you need to select the Create Database as shown below.

Azure Create SQL Server & Database.1.5

Step 6 : (Create Database)
You are now in the database creation screen, here, you need to specify database settings. You need to enter database name and choose the Service tiers, performance level, collation depending upon your requirement. Once you specify all the settings, you need to press CHECK MARK button as shown below.

Azure Create SQL Server & Database 1.6

Now, your database has been created as shown below.

Azure Create SQL Server & Database 1.7

Azure Create SQL Server & Database 1.8

Let me know if you have created your first SQL Server & database in Azure and your feedback about it.

Remove-Duplicate-WordsI have been asked this question how to remove the duplicate words in a sentence, when my team and I were busy in messaging legacy application data and we had to migrate it to SQL Server 2012. Also, we had to check and clean the data if there was any duplicate word in a sentence. So I started scripting and checking multiple options to develop this solution including loop & XML, but I usually prefer XML. So I finally developed the solution using XML.

Before proceeding with the solution, I would like to create an example to demonstrate the solution.

--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
 [ID] INT IDENTITY(1,1),
 [Sentence] VARCHAR(MAX)
)
GO
INSERT INTO tbl_Sample 
VALUES ('This is the the test test script from from raresql.com')
GO
INSERT INTO tbl_Sample 
VALUES ('This should should remove duplicates')
GO

The script of this solution is given below. and can be downloadable from here.

--DROP FUNCTION dbo.[UDF_Remove_Duplicate_Entry]
--GO
CREATE FUNCTION dbo.[UDF_Remove_Duplicate_Entry]
(
      @Duplicate_Word VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
	  DECLARE @Xml XML
	  DECLARE @Removed_Duplicate_Word VARCHAR(MAX)
	  SET @Xml = CAST(('<A>'+REPLACE(@Duplicate_Word,' ','</A><A>')+'</A>') AS XML)

      ;WITH CTE AS (
	  SELECT
	  ROW_NUMBER() OVER(ORDER BY A) AS [Sno],
	  A.value('.', 'varchar(max)') AS [Column]
	  FROM @Xml.nodes('A') AS FN(A) )

	  SELECT @Removed_Duplicate_Word =(SELECT Stuff((SELECT '' + ' ' + '' + A.[Column] FROM CTE A
	  LEFT JOIN CTE B ON A.[Sno]+1=B.[Sno]
	  WHERE (A.[Column]<>B.[Column] Or B.[Sno] is NULL)
	  FOR XML PATH('') ),1,1,''))

	  RETURN @Removed_Duplicate_Word
END
GO

SELECT
[ID]
,[Sentence] As [Before Duplicate removal]
,dbo.[UDF_Remove_Duplicate_Entry]([Sentence]) As [After Duplicate removal]
FROM tbl_Sample
GO
--OUTPUT

How to remove the duplicate words in the sentence.1.1

Let me know if you come across this scenario and its solution.

In my earlier articles, I explained, how to RESTORE the backup from URL using T-SQL, SQL Server Management Studio (SSMS)  from windows Azure storage and it was quite simple & easy to understand. In this article, I will explain how to RESTORE the backup from URL using PowerShell.

Pre-Requisite :

  1. Microsoft Azure – How to create storage and container
  2. SQL SERVER 2014 – How to take backup to URL (Windows Azure Storage) – PowerShell

Let me now demonstrate in few easy steps how to RESTORE backup from URL using PowerShell.

Step 1 – Create Account, storage & container :
First of all, you need to create a windows Azure account, a storage and a container as explained in this article.

Step 2 – RESTORE backup to URL :
Once you created the the Azure account, a storage and container and made sure that your backup is available in the windows Azure storage, you can RESTORE the backup from URL using given below script. In this script, first of all I declared the variables , then created credentials. After that RESTORED the backup as shown below. Rest of the script is self explanatory. Make sure that you have downloaded PowerShell Extensions for SQL Server in case you are using Windows PowerShell ISE instead of Windows PowerShell.

#Import sql module
Import-Module sqlps

#Create & set variables
$serverPath = "SQLSERVER:\SQL\WIN-KK48BQM9IS0\DEFAULT"
$storageAccount = "raresql"
$storageKey = "/ByNUTZqJ6EcJR/VQcNmNj+zSu++iCfbcxlyWye6Ok9uhsd5jsd62sjhsd7ksdh7sksdsdlhsd4bsdhsd52ksd=="
$secureString = convertto-securestring $storageKey -asplaintext -force
$credentialName = "My_Credential"

# Create Credential
New-sqlcredential -Name $credentialName -Identity $storageAccount -Secret $secureString

# Set SQL Server instance Path
CD $serverPath 

# Set the backup file name
$backupFile = "https://raresql.blob.core.windows.net/sql-backup/AdventureWorks2012_25_Dec_2014.bak"

# Restore Database and move files
$newDataFilePath = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile ("AdventureWorks2012_Data","C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf")
$newLogFilePath = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("AdventureWorks2012_Log","C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_log.ldf")
Restore-SqlDatabase -Database "AdventureWorks2012" -SqlCredential $credentialName -BackupFile $backupFile -RelocateFile @($newDataFilePath,$newLogFilePath) 

How to RESTORE backup from URL (Windows Azure Storage) - PowerShell.1.1

How to RESTORE backup from URL (Windows Azure Storage) - PowerShell1.2

How to RESTORE backup from URL (Windows Azure Storage) - PowerShell1.3

As you can see, the messages show that your backup has been restored successfully.

Follow

Get every new post delivered to your Inbox.

Join 2,460 other followers