Calculate the median by using Transact SQL

The statistical median is the value which separates a dataset into halves – one comprises greater values, and the other comprises lesser ones. For a specified dataset, it can be considered as the “middle” value. For example, in the dataset {1, 3, 3, 4, 5, 6, 7, 8, 9}, the median is 5, which is fourth largest, and fourth smallest number in the dataset.

To calculate the median of any dataset, we first need to arrange all values from the dataset in a specific order. After arranging the data, we must determine the middle value of the specified dataset. If the dataset contains an odd number of values, than the middle value of the entire dataset will be considered as a median. Read More

Several Ways to Insert Split Delimited Strings in a Column

In this article, I will demonstrate several ways to split the delimited string and insert it in into a column of a table in SQL Server. You can do it using the following methods:

  1. Convert delimited string into XML, use XQuery to split the string, and save it into the table.
  2. Create a user-defined table-valued function to split the string and insert it into the table.
  3. Split the string using STRING_SPLIT function and insert the output into a table. Read More

How to Properly Use the T-SQL IsNumeric Function

This article is focused on the T-SQL (Transact-SQL) IsNumeric function and its proper use in day-to-day SQL scripting tasks.

We will also see why it is important to understand how and why IsNumeric can be used – both incorrectly and correctly.

There may be some better alternatives to IsNumeric depending on the context. However, in the cases we’re going to cover in this article, I see this function as the best possible choice. Read More

T-SQL Datetime Data Type

Introduction

Data types are attributes that specify the kind of data that objects such as columns, local variables, expressions, and parameters can hold. Across the RDBMS world, data types are typically grouped into string, numeric, and date data types.

T-SQL supports 6 date and time data types namely:

  1. Datetime
  2. Smalldatetime
  3. Date
  4. Time
  5. Datetime2
  6. Datetimeoffset

The first two data types are considered as legacy versions of the newer ones. In this article, we focus on the date data types and, specifically, on the datetime and datetime2 data types available in SQL Server. Table 1 gives details of the various date and time data types available in SQL Server. Read More

Concatenation in Transact-SQL

Introduction

Assuming you maintain a table containing customer data, and your boss asks you to send him the current list of customers and their phone numbers. You would typically extract the data and send him a spreadsheet with rows and columns. You could also decide to be a bit stylish and send him the required information in a more people-friendly format. SQL Server provides functions that allow us to achieve this by combining English expressions with data within tables to present an output that is easier for non-technical people to read. These functions can also be put to more subtle uses.

Read More

Dealing with NULLs in SQL Server

What is NULL? NULL or the NULL marker is the way we represent an unknown value in SQL, by SQL I am referring to the standard Structured Query Language, not MS SQL Server. The last sentence takes us a little back to 1969 when the standard was first defined by Dr. E.F. Codd. NULL becomes necessary because we need to think in terms of what is called three-valued predicate logic. A predicate is the property of an expression that either holds or does not hold. At face value, one would think of two possibilities: TRUE or FALSE. However, there is a third possibility: UNKNOWN. Read More

Using DATEADD, DATEDIFF and DATEPART T-SQL Functions in Simple Terms

This article focuses on developing a basic understanding of how to use one of the most common Transact-SQL date functions: DATEADD, DATEDIFF, and DATEPART.

In this article, I also stressed the importance of properly using these date functions in daily date manipulations followed by some interesting scenarios in which these date functions can be used in a collaborative way to solve slightly complex date calculations.

Since these functions are primarily used in date manipulations, let us first try to understand what we mean by date manipulation.

Read More

Understanding GROUPING and GROUPING_ID Functions in SQL Server

The ROLLUP and CUBE operators are used to return results aggregated by the columns in the GROUP BY clause.

The GROUPING and GROUPING_ID functions are used to identify whether the columns in the GROUP BY list are aggregated (using the ROLLUP or CUBE operators) or not.

There are two major differences between the GROUPING and GROUPING_ID Functions.

They are as follows:

  • The GROUPING function is applicable on a single column, whereas the column list for the GROUPING_ID function has to match the column list in the GROUP BY clause.
  • The GROUPING function indicates whether a column in the GROUP BY list is aggregated or not. It returns 1 if the result set is aggregated, and 0 if the result set is not aggregated.

On the other hand, the GROUPING_ID function also returns an integer. However, it performs the binary to decimal conversion after concatenating the outcome of all of the GROUPING functions.

In this article, we will see the GROUPING and GROUPING_ID functions in action with the help of examples. Read More