SQL Server TSQL Articles

Performance Impact of Using SELECT COUNT(*) In Queries

I am not sure how this statement came into common usage, however, it seems to be regularly used by developers. Count(*) will use a table scan or Clustered index scan and so will eventually impair performance. In the case of a large table, a table scan or clustered index scan will impair your query performance, […]

Efficient T-SQL Code – A Case of Incorrect Use of a UNION Operator

A couple of days back, I had a developer complaining me the slow performance of some T-SQL which was causing the application to throw a Timeout Expired error. During Investigation, I noticed that the developer made an incorrect use of a UNION operator which was the culprit. In this article, I will demonstrate the issue […]

VARCHAR (MAX), NVARCHAR (MAX) and NTEXT Data Types

Despite NTEXT and TEXT being deprecated in SQL Server for some time they are still both used in production systems. In this article, I will briefly demonstrate the difference between a VARCHAR (MAX), NVARCHAR (MAX) and the NTEXT data types, and the impact on performance from using NTEXT/TEXT. Let’s create a table named Student using […]

SQL Server T-SQL Tuning – NOT IN and NOT Exists

Following on from the previous TSQL performance tuning article in this series, we now focus on the important topic of T-SQL “Not IN” And “Not Exists “ Not in and Not Exists commands : In some circumstances we will need to select/update/delete orphan data – that is data does not exists in the major table […]

SQL Server T-SQL Tuning -TVF and Scalar Functions

In the first of this series we looked at TSQL tuning for temp tables, table variables and the Union command , we now turn our attention to User Defined Functions – either Table-Valued Functions or Scalar Functions. A UDF is very convenient for centralising business logic as we can specify a set of business logic […]

SQL Server T-SQL Tuning – Temp Tables, Table Variables and Union

In this article series we will focus on the primary points of T-SQL performance tuning. In this first installment I will lopok at tuning temp tables, table variables and the Union command. Temp Tables and Table Variables Most database developers are used to breaking down code into small chunks using  temp tables or table variables  […]

Tuning your SQL Query – Generating a Proper Execution Plan.

Recently one of my developers came to and complained of slow performance on a particular query. During the investigation I noticed that the primary cause was the poor query execution plan. In this article, I will discuss the approach which I used to improve the execution plan. For demonstration purpose, I will be creating a […]

New T-SQL Features in SQL Server 2012

Paging Data Paging is a big issue for developers as it is required for many applications but entails many performance problems.  Developers have used different workarounds to support paging. For example, assume we need to display several pages of the HumanResource.Employee object with 5 rows per page. Below is the query you need to execute […]

New Built-In TSQL Functions in SQL Server 2012

New Built-in Functions in SQL Server 2012 SQL Server 2012 (Code named SQL Server Denali) introduces 14 new built in functions in four different categories with one existing function (LOG) being modified to have an optional second base parameter: Category Function Name Conversion functions PARSE TRY_CONVERT TRY_PARSE Date and Time functions DATEFROMPARTS DATETIME2FROMPARTS DATETIMEFROMPARTS DATETIMEOFFSETFROMPARTS […]

Building Ranges using T-SQL

We often use excel for any running repetitive calculations since it is a very convenient user friendly interface which allows us to just enter a formula in a cell and  simply  drag and drop the  formulas to generate a complete output. For example, I had a Products listing, with ID and Quantity for each product. […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |