T-SQL

SQL Server table hints – WITH (NOLOCK) best practices

February 14, 2018 by Ahmad Yaseen

SQL Server table hints are a special type of explicit command that is used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index or query processing operation, such index seek or table scan, to be used by the SQL Server query optimizer to build the query execution plan. The table hints can be added to the FROM clause of the T-SQL query, affecting the table or the view that is referenced in the FROM clause only.

How to implement array-like functionality in SQL Server

January 16, 2018 by Daniel Calbimonte

Introduction

I was training some Oracle DBAs in T-SQL and they asked me how to create arrays in SQL Server.

I told them that there were no arrays in SQL Server like the ones that we have in Oracle (varray). They were disappointed and asked me how was this problem handled.

Some developers asked me the same thing. Where are the arrays in SQL Server?

How to use sargable expressions in T-SQL queries; performance advantages and examples

December 22, 2017 by Esat Erkec

The challenge

One of the main tasks of a SQL Server database administrator is performance tuning. Sometimes, though, coders or developers don’t always prioritize database performance or query optimization. Here is a typical scenario

Imagine that developers create a new table and then insert some records in a test environment and test their queries to retrieve data from it
The query executed successfully and does not exhibit any symptoms of performance problems
The developer team release this table and query into production
One day you take a telephone from your colleague and he says my report is very slow
Bingo! In production, this table contains a lot of records and this is resulting in performance bottlenecks when querying it

What to choose when assigning values to SQL Server variables: SET vs SELECT T-SQL statements

November 29, 2017 by Ahmad Yaseen

SQL Server provides us with two methods in T-SQL to assign a value to a previously created local SQL variable. The first method is the SET statement, the ANSI standard statement that is commonly used for variable value assignment. The second statement is the SELECT statement. In addition to its main usage to form the logic that is used to retrieve data from a database table or multiple tables in SQL Server, the SELECT statement can be used also to assign a value to a previously created local variable directly or from a variable, view or table.

ETL optimization using SQL TRY Functions

October 24, 2017 by Sifiso Ndlovu

Introduction

An enterprise data warehouse ETL solution typically includes, amongst other steps, a data transformation step that converts source data from one data type into another. It is during this step that type conversion errors may occur and depending on the type of exception handling techniques implemented in the ETL solution (or lack thereof), frustration may occur for both ETL developers and DBAs when trying to identify and resolve type conversion errors. In this article we take a look at a trio of SQL TRY built-in functions that were introduced in SQL Server 2012, namely, TRY_PARSE, TRY_CAST, and TRY_CONVERT and how they could be utilized to reduce type conversion errors in ETL solutions and thereby saving developers needless troubleshooting exercise.

Monitoring SQL Server database status changes using T-SQL and PowerShell scripts

October 17, 2017 by Prashanth Jayaram

Monitoring a SQL Server database is a critical component of database administration. Ninety percent of the organizations expect the number of databases to increase over the next twelve months. An increase in data volumes can have negative effects on the availability of databases. Hence, SQL Server database monitoring is considered a critical responsibility of a database administrator. Organizations tend to spend a lot of their funds towards enterprise solutions. And due to the sensitive and growing nature of business and user needs, application availability is very important nowadays.

How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL

October 2, 2017 by Prashanth Jayaram

In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more.

How to create a linked server to an Azure SQL database

September 27, 2017 by Marko Zivkovic

Linked servers allow to access data from another SQL Server or another data source (e.g. Excel) by using SQL Server Management Studio (SSMS) or Transact-SQL.

This article will explain how to create and configure a linked server to retrieve data from an Azure SQL database. Also, we will explain how to solve some common problems/issues during the process of creating a linked server to an Azure SQL database.

T-SQL commands performance comparison – NOT IN vs SQL NOT EXISTS vs SQL LEFT JOIN vs SQL EXCEPT

September 22, 2017 by Ahmad Yaseen

This articles gives you a performance comparison for NOT IN, SQL Not Exists, SQL LEFT JOIN and SQL EXCEPT.

Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL

August 15, 2017 by Prashanth Jayaram

Introduction

Database availability is critical to every enterprise and conversely, unavailability directly can create a severe negative impact to the business in today’s world. As database administrators, it is important that we ensure we take all possible steps to minimize data loss. While it is naïve to think that our databases are invincible because of all such precautions, we can always aim to bring the databases back into operation as quickly as possible by meeting the RPO and RTO. This way, the business is unaffected, and we also meet our SLAs.

$C:\Users\admin\Desktop\Any\Images\NOT_IN.png$

SQL Server universal comparison quantified predicates (ANY, ALL, SOME)

July 27, 2017 by Nikhilesh Patel

SQL server provides us with comparison operators to modify subqueries. This article will start with the definition of universal quantification, with a quick brush up with real life logical examples.

How to analyze SQL Server database performance using T-SQL

July 10, 2017 by Prashanth Jayaram

The performance of a database is the most challenging and critical part of database optimization. The challenge every DBA faces is to identify the most resource-intensive databases. This article talks about the natively available features that can accomplish the task of getting the required details of the entire database at a granular level.

$C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\9.png$

How to create, configure and drop a SQL Server linked server using Transact-SQL

July 5, 2017 by Marko Zivkovic

Linked servers allow getting data from a different SQL Server instance using single T-SQL statement.

This article will explain how to create, configure and drop a SQL Server linked server using system stored procedures.

SQL Server SET Options that Affect the Query Result – SET CONCAT_NULL_YIELDS_NULL, SET NUMERIC_ROUNDABORT, SET QUOTED_IDENTIFIER, SET NOCOUNT, SET XACT_ABORT

July 4, 2017 by Ahmad Yaseen

In the previous article of this two-part series SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT, we described the first four SQL Server SET options and showed practically how setting these options ON and OFF affects the SQL Server Database Engine behavior and the query result. To recall, SQL Server SET options are a group of session-level options that control how the SQL Server behaves on the database session level, and the option value can be changed using the SET T-SQL command for the current session that you execute the SET command on.

In this article, we will describe another five SET options and see how turning it ON and OFF will change the SQL Server behavior and the query result.

SQL Server SET Options that Affect the Query Result – SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS and SET ARITHABORT

June 30, 2017 by Ahmad Yaseen

SQL Server provides us with a number of options to control SQL Server behavior on the connection level. These session-level options are configured using the SET T-SQL command that change the option value for the session on which the SET command is executed. Changing the default value of these session-level configuration affects how the session queries will be executed affecting the query result. The performed change on a session-level option will be applied to the current session until its value is reset or until the current user’s session is terminated.

How to calculate work days and hours in SQL Server

June 26, 2017 by Sifiso Ndlovu

Like any other enterprise RDBMS system, SQL Server ships with several built-in functions that make developers’ T-SQL code clean, convenient and reusable. To demonstrate the efficiency of functions, say we needed to retrieve a server name for one of our SQL Server instances. Well, one of doing this would be to write a SELECT statement that would query the system view [sys].[servers] from the master database as shown in Script 1.

How to copy an Azure SQL database using the Azure Portal, Cloud Shell and T-SQL

June 23, 2017 by Daniel Calbimonte

This article will provide an overview covering programmatically moving databases on the Azure Portal while avoiding common problems with users and logins.

Understanding the Impact of NOLOCK and WITH NOLOCK Table Hints in SQL Server

June 6, 2017 by Sifiso Ndlovu

Every once in a while, SQL Server database administrators find themselves in disagreements with their application developer counterparts – particularly when it comes to some of the latter’s Transact SQL (T-SQL) developmental practices. One of my first observations when I joined my current employer is that almost all T-SQL scripts written by application developers uses the NOLOCK table hint. However, from the interactions that I have had with these esteemed developers it doesn’t seem like they understand how the NOLOCK table hint works. Furthermore, although they seem to be aware of a distinction between NOLOCK and the WITH NOLOCK table hint, they again do not seem to comprehend how the two differ from one another. In this article, I explore the internal workings of the NOLOCK table hint and examine the implications of omitting the WITH keyword.

A T-SQL design pattern for logging process execution

May 25, 2017 by Jefferson Elias

Introduction

Context

Back in my days at school, I followed a course entitled “Object-Oriented Software Engineering” where I learned some “design patterns” like Singleton and Factory. If you are not familiar with this expression, here is a definition of a design pattern from Wikipedia:

T-SQL as an asset to set-based programming approach

April 27, 2017 by Jefferson Elias

Introduction

This article is the third and last one of a series of articles that aims to introduce set-based programming approach inside SQL Server, so using Transact SQL. In first article “An introduction to set-based vs procedural programming approaches in T-SQL”, we’ve compared procedural and set-based approaches and came to the conclusion that the second one can facilitate developer or DBA’s life. In second article “From mathematics to SQL Server, a fast introduction to set theory”, we’ve made the parallel between mathematical definition of set theory and its implementation in SQL Server. Now, we’ll discuss about some other features from T-SQL that can’t be left aside when considering sets.

T-SQL for DBAs – Three ways of using T-SQL for quick data analysis

April 26, 2017 by Timothy Smith

Background

As a database administrator, sometimes you need to identify details about a problem as quickly as possible and being able to build and analyze data for analysis will help you solve the problem. From getting information about the latest backups, to saving information about waits or indexes and comparing that to other captured metrics, we will run into issues where being able to get, store and analyze data are important for decisions to solve urgent problems.

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.