T-SQL

SQL Server Code Review Checklist for Developers

April 26, 2017 by Samir Behara

In a software development life cycle, Code Review plays an integral role in improving the product quality. Having a Code Review Checklist is indispensable since it ensures that the best practices are followed and reviews are performed consistently. It is essential for developers to be aware of the coding guidelines while working on their code changes. Catching a bug early in the process is inexpensive and easier to resolve, than compared with a bug caught later in the game. Having all the common mistakes added to the checklist document is a great way to create awareness and ensure good code quality over a period of time.

From mathematics to SQL Server, a fast introduction to set theory

April 25, 2017 by Jefferson Elias

Introduction

In the previous article of this series “An introduction to set-based vs procedural programming approaches in T-SQL”, we’ve seen from a simple example that we could find actual benefit from learning set-based approach when writing T-SQL code.

In this article, we will carry on in this way by having a look at what a set is and what we can do with it in a mathematical point of view and how it’s implemented and provided to us in SQL Server. We will also have a look at more “realistic” examples using Microsoft’s AdventureWorks database.

An introduction to set-based vs procedural programming approaches in T-SQL

April 24, 2017 by Jefferson Elias

Introduction

Most IT professionals started their studies or careers by learning programming languages like Basic, Cobol, C, Pascal, Java and so on. Those languages produce results using a sequence of operations or procedures. For this reason, this approach is called procedural programming.

SQL Server lock issues when using a DDL (including SELECT INTO) clause in long running transactions

April 12, 2017 by Simon Liew

Abstract

This article explains lock issues in SQL Server with DDL statements including SELECT INTO clause.

Use of explicit transaction is common in SQL Server development. Sometimes a developer might inadvertently specify a Data Definition Language clause within a long running explicit transaction, similar to the structure below, within a SQL Server object such as a stored procedure.

Some uncommon but useful T-SQL and Database Engine Enhancements in SQL Server 2016

March 20, 2017 by Musab Umair

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.

Using Variables in Dynamic SQL

January 26, 2017 by Aamir Syed

Before 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.

How to generate random SQL Server test data using T-SQL

January 26, 2017 by Daniel Calbimonte

Introduction

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.

Java HashCode in TSQL

December 28, 2016 by Ed Pollack

The 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.

3 ways to improve T-SQL performance

November 22, 2016 by Daniel Calbimonte

Introduction

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).

How to create and manage T-SQL code snippets

October 28, 2016 by Artemakis Artemiou

Introduction

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.

How to effectively extract data from an OLAP cube by relying upon T-SQL

October 19, 2016 by Steve Simon

Introduction

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

5 handy Transact-SQL tips you may (or may not) know

September 23, 2016 by Minette Steynberg

Introduction

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.

Top 5 T-SQL functions introduced in SQL Server 2016

August 1, 2016 by Sifiso W. Ndlovu

One 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.

Reporting in SQL Server – Combine T-SQL and DAX queries to produce effective reports

July 27, 2016 by Steve Simon

Introduction

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”.

Warehousing JSON formatted data in SQL Server 2016

June 22, 2016 by Sifiso W. Ndlovu

In 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.

Querying remote data sources in SQL Server

June 10, 2016 by Ahmad Yaseen

A 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.

How to create and customize SQL Server Templates

May 16, 2016 by Marko Zivkovic

SQL 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.):

SQL snippets in SQL Server Management Studio

April 22, 2016 by Marko Zivkovic

Snippets 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.

Using SQL Server cursors – Advantages and disadvantages

March 23, 2016 by Sergiu Onet

Intro

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.

Creating a gap in sequences – TSQL Stored Procedure advisor

January 6, 2016 by Eli Leiba

Introducing 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.

Multiple options to transposing rows into columns

January 4, 2016 by Sifiso W. Ndlovu

Introduction

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:

Common SQL syntax errors and how to resolve them

April 2, 2014 by Milena Petrovic

In the SQL Server Management Studio, errors can be tracked down easily, using the built in Error List pane. This pane can be activated in the View menu, or by using shortcuts Ctrl+\ and Ctrl+E Read more »