SQL Server 2016 is the most advanced version of Microsoft’s Data Platform released yet. This is obviously my favorite one as it has tremendous capabilities and enormous features. These new additions not only enhance the productivity of its users (Database Developer, DBA or Application Developer), but also enable the enterprise to use its data more effectively and efficiently.
Read more »Languages and coding


How to import/export JSON data using SQL Server 2016
March 7, 2017JSON is an abbreviation for JavaScript Object Notation. JSON is very popular and currently the most commonly used data exchange format. Most modern web and mobile services return information formatted as JSON text, all database web services, web browsers (Firefox, Internet Explorer) return results formatted as JSON text or accept data formatted as JSON. Since external systems format information as JSON text, JSON is also stored in SQL Server 2016 as text. You can use standard NVARCHAR columns to store JSON data in SQL Server 2016.
Read more »

How to Merge and Split CSV Files Using R in SQL Server 2016
February 21, 2017Introduction
From time to time, we may encounter the following scenarios when dealing with data processing:
Read more »

SQL Server Database Recovery Process Internals – database STARTUP Command
February 14, 2017A database recovery process is an essential requirement for database systems, It can be a tedious job and the process of recovery varies on lot of scenarios. The desire to improve recovery results has resulted in various procedures, but understood by few and prone to errors. In this article, I’ll illustrate the impact of stopping the database instance in the middle of a large transaction that was running and discuss several techniques and tools that are available for faster and successful recovery.
Read more »

How to Import / Export CSV Files with R in SQL Server 2016
February 9, 2017Introduction
Importing and exporting CSV files is a common task to DBAs from time to time.
Read more »

How to create a simple SQL Server database login dialog using PowerShell
January 30, 2017In this article, we’ll be creating a SQL Server login form, as shown in the example below, using PowerShell to accept user input. We’ll show the code behind the form, how to capture the user input and utilize it:
Read more »

Using Variables in Dynamic SQL
January 26, 2017Before we delve into these SQL concepts, note that I like to do all my development in SQL Management Studio. So if you want to follow along go ahead and open that up.
Read more »

How to generate random SQL Server test data using T-SQL
January 26, 2017Introduction
In this article, we will talk about generating random values for testing purposes.
I once had a customer with software that worked fine in the demo with 30 rows, but after some months, the software had more than a million rows and it became very slow. The problem was not SQL Server, the problem was the application, which was not designed for tables with millions of rows. The customer sued to the software provider and lawyers were needed to create a resolution. If the provider had tested the software with millions of rows, this problem would have never happened.
Read more »

Connecting PowerShell to SQL Server – Using a Different Account
January 24, 2017Introduction
In a previous article on Connecting PowerShell to SQL Server I went over how you use various methods in PowerShell to connect to SQL Server. In those examples though I only touched on using the current user that is running “PowerShell.exe”. In this article I want to pick up and go over how you would use the same methods, but as a different account. I will cover using both Windows Authentication (where supported) and SQL Login authentication.
Read more »

How to administer SQL Server Reporting Services (SSRS) subscriptions using PowerShell
January 23, 2017In the article Report Subscription Changes in SQL Server Reporting Services 2016, I covered several changes to standard and data-driven subscriptions that were introduced in the release of SQL Server 2016. However all of those changes related to administering report subscriptions using a GUI (i.e. Report Manager Portal, SSRS Configuration Manager). The release of SQL Server Core, SQL Server on Linux and enhancements on Windows PowerShell, reinforces the growing shift by Microsoft to have SQL Server professionals adopt scripting as one of the ways to carry out their daily tasks. In this article we continue to explore SQL Server Reporting Services (SSRS) subscription feature by discussing some of the Windows PowerShell commands that can be utilised to administer report subscriptions.
Read more »
Microsoft SQL Server Non-Contained Object Migration Deployment Procedure using Powershell
January 12, 2017As DBAs, we all face a lot of challenges while performing the migration of SQL Server databases from one server to another or even the whole servers at some times. The Database Migration process is not always a simple Backup and Restore process so we might need a huge amount of effort if we have to migrate objects which are not included in the native backups for a specified database, these objects are called Non-Contained Objects.
Read more »

Native JSON Support in SQL Server 2016
January 6, 2017With the introduction of SQL Server 2016, Microsoft introduced many new features which had taken SQL Server another step forward and they made sure that it stands in front among many major Relational Database Systems.
One such feature which has been lacking but desperately required was the native support towards JSON.
Read more »

Java HashCode in TSQL
December 28, 2016The Java HashCode method is used to determine uniqueness or similarity of strings. While implemented in Java, there can be many benefits of creating a similar or customized version of this method.
Read more »

Handy features of the dbatools Read-DbaBackupHeader command
December 21, 2016If you haven’t heard of dbatools, it’s an open source project out on GitHub dedicated to making life easier for Data Professionals. If you have an idea that you don’t see a command for, you can build it and contribute it to the project. Or you can ask if someone has time to write if for you.
Read more »

How to use SQLCMD commands in the SSMS query editor
December 13, 2016SQLCMD Mode allows creating, testing, executing SQLCMD commands or scripts in SQL Server Management Studio directly in the query editor. This option is available since SQL Server 2005.
Read more »

3 ways to improve T-SQL performance
November 22, 2016Introduction
When customers used to ask for advice to solve some T-SQL Problem, they would show me their scripts, stored procedures and cursors. I saw horrible things doing that job, some things I do not want to remember, and even some I cannot tell you about 😉 In this article, we will talk about performance problems when using while loops in T-SQL. We will also talk about problems with UNIONsand finally the use of JOINS of two or more tables using the where clause (ANSI 89) instead of using INNER, LEFT or RIGHT JOINS using the from clause (ANSI 92).
Read more »

PowerShell: Get a daily database status email
November 22, 2016The need for this script came about when I took a new job as a DBA. One of my responsibilities was to make sure all databases were available after the maintenance window which ran during the weekend. Rather than log into each database server and check that the databases were online, I had a script do the work for me and shoot me an email.
Read more »

How to create and manage T-SQL code snippets
October 28, 2016Introduction
Transact-SQL (T-SQL) snippets were first introduced in SQL Server 2012 Management Studio. T-SQL snippets are templates containing one or more T-SQL statements which you can easily use them when you develop T-SQL scripts. The main concept behind code snippets is code reuse. With code reuse you develop faster, easier and with less syntax errors. If you frequently use specific T-SQL statements, then you should consider creating snippets with these statements as they will help you a lot.
Read more »

How to effectively extract data from an OLAP cube by relying upon T-SQL
October 19, 2016Introduction
Last month I ran two Business Intelligence pre-conferences in South Africa. A interesting request arose during the course of the preconference in Cape Town. The individual wanted an approach to extracting data from an OLAP cube that would avoid intensive utilization of MDX and more reliance upon T-SQL. His main concern was with filtering the data at run time, via the report front end.
In this “fire side chat” we shall do just that, utilizing the cube that comes with the new Microsoft database “WideWorldImporters” and we shall learn how we can get from this
Read more »
How to import a sample bacpac file to an Azure SQL Database using sqlpackage and PowerShell
October 18, 2016Introduction
In our first part of this series, we showed how to import a bacpac file using the Azure Portal with Microsoft Azure Storage Explorer (MASE) and SQL Server Management Studio (SSMS) to create a Database in Azure.
In the second part, we will show how to import a bacpac file to create a Database using the sqlpackage.exe tool and PowerShell.
Read more »
5 handy Transact-SQL tips you may (or may not) know
September 23, 2016Introduction
Even if you are a SQL veteran, actually especially if you are, you sometimes get really stuck doing something in the old way not realizing that in newer versions of SQL Server some handy functions have been added that can make your life easier.
In this article, I will mention a couple of these which appeared in SQL Server 2008 or later versions or might have even been there before but most people just didn’t know about.
Read more »
How to use the xp_cmdshell extended procedure
September 12, 2016Introduction
The xp_cmdshell is a very powerful extended procedure used to run the command line (cmd). This is very useful to run tasks in the operative system like copying files, create folders, share folders, etc. using T-SQL.
In this new article, we will show some useful examples about how to use it.
Read more »
New PowerShell Cmdlets in SQL Server 2016
August 18, 2016Introduction
PowerShell is Windows Shell that can be used to automate tasks in Windows, Exchange, Azure, SQL Server and more.
Read more »
SQL backup reports with PowerShell
August 15, 2016Let me preface this post by saying that this was a process that evolved over time. It started as a simple sql statement that emailed me a csv file. From there the actual SQL code evolved to display cleaner results. When I learned what powershell could do, I spent some time learning it during my lunch breaks at work. Even when I implemented a powershell script for the first time, it still wasn’t a clean looking report, but it was still better than what we had prior. Even now there is room for improvement but I am happy with the results and hope that it can provide some help to other SQL DBA’s out there.
Read more »
Top 5 T-SQL functions introduced in SQL Server 2016
August 1, 2016One of the exciting things to look out for in a release of SQL Server are the new built-in T-SQL functions that are usually introduced. Some of the reasons for the new functions may be because SQL Server is catching up to other competitors, attempting to stay ahead of the competition or simply responding to SQL Server feature-requests from the Microsoft Connect site. In this article, I take you through my top 5 T-SQL functions released in SQL Server 2016.
Read more »