T-SQL

Frank Solomon

Lever T-SQL for Pinpoint Control of ORDER BY in a Stored Procedure

June 1, 2020 by

Introduction

The T-SQL ORDER BY clause sorts SQL Server SELECT statement result sets, and it becomes important when we build stored procedures. Unfortunately, the syntax offers no flexible way to directly control the ORDER BY clause behavior with argument values. This means we don’t have an easy way to control the specific column or columns that the ORDER BY clause sorts. Additionally, SQL Server does not offer a flexible way to directly control the ascending or descending order of any ORDER BY clause column with argument values. Of course, we can certainly hard-code the ORDER BY clause in a stored procedure, but this approach becomes fixed in stone. We could try a dynamic SQL solution, involving a stored procedure code that dynamically builds and executes SQL Server statements inside a stored procedure. However, this technique becomes tricky, and it can lead to SQL injection attacks. Other techniques might rely on CASE statements, and their complexity can become overwhelming as the column count grows. This article spotlights a clean, efficient, pinpoint T-SQL stored procedure technique that directly sorts one, some, or all SELECT statement result set columns. The technique avoids dynamic SQL, and it operates directly in a stored procedure. The article also shows how to set the ascending or descending sort order of specific columns.

Read more »
Emil Drkusic
SQL Server PIVOT TABLE - the data model we'll use

Learn SQL: SQL Server Pivot Tables

May 11, 2020 by

In the previous few articles in this series, we’ve set the foundations on how to create a report. We’ll take one step further and see how to use the SQL Server PIVOT table operator. We’ll start from the simple query and slowly progress towards dynamic SQL and PIVOT. Let’s start.

Read more »
Frank Solomon
Run the DYNAMIC_ROW_NUMBER_PARTITIONS stored procedure with a T-SQL EXEC statement, and see the result set.

Lever T-SQL to dynamically define duplicate SQL Server database table rows

May 11, 2020 by

Introduction

Lever T-SQL to handle duplicate rows in SQL Server database tables article highlighted T-SQL features that detect and handle duplicate SQL Server table rows. The techniques work well, but they rely on fixed duplicate row definitions. This article extends those techniques, showing how to define duplicate rows in a dynamic way.

Read more »
Emil Drkusic
SQL Server date and time functions - the data model we'll use in the article

Learn SQL: SQL Server date and time functions

April 21, 2020 by

So far, we haven’t talked about SQL Server date and time functions. Today we’ll change that. We’ll take a close look at the ones most frequently used and mention all other date and time functions as well. This will be also the first step to create reports, including date and time functions. We’ll do that in upcoming articles in this series.

Read more »
Esat Erkec
What is pagination in SQL Server?

Pagination in SQL Server

April 14, 2020 by

Pagination is a process that is used to divide a large data into smaller discrete pages, and this process is also known as paging. Pagination is commonly used by web applications and can be seen on Google. When we search for something on Google, it shows the results on the separated page; this is the main idea of the pagination.

Read more »
Frank Solomon
Setting the target Directory

How to update the T-SQL Toolbox database

April 6, 2020 by

Introduction

In an earlier article, Solve Time Zone, GMT, and UTC problems using the T-SQL Toolbox database, I described T-SQL Toolbox, a free, open-source SQL Server database that handles time zone, date, and time calculations in a clean and efficient way. Available here at the CodePlex Archive, and here at GitLab, T-SQL Toolbox relies on time zone and time zone adjustment data in its two tables for many of its own calculations. However, T-SQL Toolbox does not update that data. I built primitive C-Sharp and VB.net applications that extract the latest time zone and time zone adjustment data from the Windows registry, and I discussed those applications in that earlier SQL Shack article. In the article, I explained that we can build SQL Server UPDATE statements with this extracted data, and then update the T-SQL Toolbox tables. This article describes a better approach and a better solution.

Read more »
Emil Drkusic
SQL Examples - the data model we'll use in the article

Learn SQL: SQL Query examples

March 31, 2020 by

In the previous article we’ve practiced SQL, and today, we’ll continue with a few more SQL examples. The goal of this article is to start with a fairly simple query and move towards more complex queries. We’ll examine queries you could need at the job interview, but also some you would need in real-life situations. So, buckle up, we’re taking off!

Read more »
Esat Erkec
Listing temporary tables in tempdb database

How to drop temp tables in SQL Server

March 23, 2020 by

Temporary tables, also known as temp tables, are widely used by the database administrators and developers. However, it may be necessary to drop the temp table before creating it. It is a common practice to check whether the temporary table exists or not exists. So, we can eliminate the “There is already an object named ‘#temptablename’ in the database” error during the temporary table creation.

Read more »
Ahmad Yaseen
Max() function example 7

SQL Server MAX() aggregate function

March 19, 2020 by

SQL Server provides us with several aggregate functions that can be used to perform different types of calculations on a set of values, and return a single value that summarized the input data set. These SQL Server aggregate functions include AVG(), COUNT(), SUM(), MIN() and MAX().

Read more »