SQL Server User-defined Functions

User-defined functions in SQL Server (UDFs) are key objects that each developer should be aware of. Although they are very useful in many scenarios (WHERE clauses, computed columns, and check constraints), they still have some limitations and bad practices that can cause performance issues. Multi-statement UDFs might incur significant performance impacts, and this article will specifically discuss these scenarios.

CodingSight - SQL Server User-defined Functions
Read More

WhoIsActive Runner

Nowadays, within the SQL Server DBA community, it is extremely likely that we use, or at the very least have heard of, the famous stored procedure sp_WhoIsActive developed by Adam Machanic.

During my time as a DBA, I used the SP to immediately check what’s going on inside a particular SQL Server instance when it is getting all the “finger-pointing” that a particular application is running slow.

However, there are occasions where such issues become recurring and require a way to capture what’s going on to find a potential culprit. There are also scenarios where you have several instances serving as the backend for 3rd party applications. The Stored Procedure could work potentially well to find our culprits.

CodingSight - sp_WhoIsActive
Read More

Top-10 SQL Server Management Studio (SSMS) Add-ins You Should Use in 2021

If you deal with SQL infrastructure, especially Microsoft SQL Servers, you deal with SSMS. The SQL Server Management Studio is the default choice for all professionals working with databases. It is not the only option, but it is the leader, backed by Microsoft and equipped with all the necessary SQL Server developer tools.

Still, even with all its advantages, SSMS is not perfection. It needs improvements as it often lacks the necessary or simply helpful functionality. Most SQL specialists inevitably get into situations where SSMS can’t do what they need. Or can’t do it how they want.

CodingSight - Top-10 SQL Server Management Studio (SSMS) Add-ins You Should Use in 2021
Read More

Simple SQL Server Functions to Solve Real-World Problems

Introduction

Today’s article comes from a scenario we experienced in practice some time ago. We managed a card transaction system referred to as Postilion, and we had to update the data in a column containing IP addresses as part of the string with a new IP address. This was necessary because the IP address of the server hosting the solution would typically change due to switchover or the data restore to a UAT environment.

The data about the server was stored in the databases, and there was no way to update the same from the application without going row by row. Thus, we had to come up with an efficient solution using the LEN and SUBSTRING (or REPLACE) SQL Server functions.

CodingSight - Solving Real-World Problems with Simple SQL Server Functions
Read More

SQL DEFAULT Constraint to Insert Column with a Default Value to SQL Server Table

To insert data into a table having columns with default constraints, we can use the DEFAULT constraint to a default value in a table. This article will cover the following aspects:

  • The DEFAULT constraint and required permission to create them
  • Adding a DEFAULT constraint while creating a new table.
  • Adding a DEFAULT constraint into an existing table.
  • Modifying and Viewing the constraint definition using T-SQL scripts and SSMS.
CodingSight - Insert Column with a Default Value to an Existing Table
Read More