Category Archives: MSSQL

Calculating Statistical Values in T-SQL: Mean (Average), Median, Mode and Range

Sometimes you may need to calculate statistical values based on a set of values you have in a table. Let’s start with explaining what these values represent, in case you don’t already know. For the purposes of this post, we … Read More »»»

Posted in MSSQL | Tagged , , , , , , , | Leave a comment

How To Flush PRINT Buffer in T-SQL, Or Output Messages In Real Time

When debugging SQL scripts, oftentimes the developer or DBA would want to see what is happening as the script executes. While it is possible to set up breakpoints in SSMS and step-into/-over code, sometimes all that one wants is to … Read More »»»

Posted in MSSQL | Tagged , , , , , , , , , , | Leave a comment

Find a String Value in the Whole Database: Searching for Text in Tables and Columns

Once in a while you may come across a problem when you are not familiar with the database structure you are dealing with. You may have to deal with the front end only and values that are only presented to … Read More »»»

Posted in MSSQL | Tagged , , , , , , , , , , , , , | Leave a comment

Avoiding Deadlock Transaction Errors by Using ROWLOCK Hint in T-SQL

When updating a single row data in a table, it may take a nasty while if the table is big and you have a condition in a WHERE clause on a column that is not indexed. In this case SQL … Read More »»»

Posted in MSSQL | Tagged , , , , , , , , , , , , | 1 Comment

How to Use NOLOCK and ISOLATION LEVEL to Optimize T-SQL Query Performace

In this article, we will discuss how a NOLOCK hint can help improve performance of queries. Suppose you have a table: CREATE TABLE Users ( UserId INT PRIMARY KEY IDENTITY(1, 1) NOT NULL, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT … Read More »»»

Posted in MSSQL | Tagged , , , , , , , , , , , , | 1 Comment

Saving Bulk Modified or Deleted SQL Database Data for Recovery

Sometimes you need to modify or remove data in bulk in a SQL database. Whatever the reason is, it is always a good practice to store such changes so that if something goes wrong you would be able to restore … Read More »»»

Posted in MSSQL | Tagged , , , , , , , , , | Leave a comment

What is CLR, How to Check If It is Enabled and How to Enable It

CLR stands for Common Language Runtime and is a technology developed by Microsoft that allows you to use managed .NET code in SQL Server environment. In other words, you can write your stored procedures, triggers and user-defined functions, aggregates and … Read More »»»

Posted in MSSQL | Tagged , , , , , , | 1 Comment

How to Insert Multiple Rows at Once in T-SQL

Sometimes you want a statement that will insert multiple rows in a table all at once. It’s easy to do with MySQL, but here comes T-SQL – how do I do this? There are a number of ways to do … Read More »»»

Posted in MSSQL | Tagged , , , , , | Leave a comment

How to Calculate a Median in T-SQL

Expectantly, you’d assume that Microsoft had built in a function to calculate a median of a set of values. Expect less! Or, rather none. There is no built-in function in T-SQL to calculate a median. I do not know the … Read More »»»

Posted in MSSQL | Tagged , , , , , , , | Leave a comment

How to Easily Toggle a Bit Column in the Database

Surely at some point you had a bit field in the database that indicated a yes or a no or some other binary flag. In this post, I’m going to give you three examples of how to toggle that bit. … Read More »»»

Posted in MSSQL | Tagged , , , , | Leave a comment