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 »T-SQL


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 »

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 »

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 »

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 »
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 »
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 »
Reporting in SQL Server – Combine T-SQL and DAX queries to produce effective reports
July 27, 2016Introduction
With today’s challenging economic times it has become more and more important to manage and rectify changing sales patterns and trends.
In today’s “get together” we shall be expanding our outlook by creating efficient and effective reports utilizing SQL Server Reporting Service 2016 and T-SQL, together with the DAX code that we created in our last “fire side chat”.
Read more »
Warehousing JSON formatted data in SQL Server 2016
June 22, 2016In this article, I continue to review the exciting features available in SQL Server 2016. One such feature is the long awaited T-SQL support for JSON formatted data. In this article we take a look at how JSON support will impact data warehouse solutions.
Read more »
Querying remote data sources in SQL Server
June 10, 2016A common activity when writing T-SQL queries is connecting to local databases and processing data directly. But there will be situations in which you need to connect to a remote database that is located in a different instance in the same server or in a different physical server, and process its data in parallel with the local data processing.
SQL Server provides us with four useful methods to connect to the remote database servers, even other database server types, and query its data within your T-SQL statement. In this article, we will discuss these four methods and how to use it to query remote SQL Server databases.
Read more »
How to create and customize SQL Server Templates
May 16, 2016SQL Server Templates are SQL scripts, containing SQL code, frequently used by developers and DBAs in their daily work (e.g. Create Table, Add Column, Drop Column, Create Database, Create Logins etc.):
Read more »
SQL snippets in SQL Server Management Studio
April 22, 2016Snippets are a great productivity feature to speed up typing of repetitive T-SQL. Snippets were introduced in SQL Server 2012 as part of SQL Server Management Studio (SSMS).
Snippets are XML templates with predefined fields and values that render into T-SQL, when processed by the query window.
Read more »
Using SQL Server cursors – Advantages and disadvantages
March 23, 2016Intro
In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with a row at a time rather than the entire result set at once. In T-SQL, one way of doing this is using a CURSOR.
Read more »
Creating a gap in sequences – TSQL Stored Procedure advisor
January 6, 2016Introducing the Problem
Gaps existence in automatic sequenced columns occurs all the time. Missing identity values (or other sequencing values) occur for a variety of Reasons.
The most common reasons include: roll backed transactions, failed inserts and Deletes, large row deletes after delete commands that occur after many inserts to a sequenced table and so forth.
Read more »

Multiple options to transposing rows into columns
January 4, 2016Introduction
One of the primary functions of a Business Intelligence team is to enable business users with an understanding of data created and stored by business systems. Understanding the data should give business users an insight into how the business is performing. A typical understanding of data within an insurance industry could relate to measuring the number of claims received vs successfully processed claims. Such data could be stored in source system as per the layout in Table 1:
Read more »