SQL Server Common Table Expression vs Temp Table

In this tip we look at how to use Common Table Expressions CTE in SQL Server including the syntax, use cases, using more than 1 CTE at the same time and recursive CTEs with a parameter.

SQL Server Cursor Example

In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors?

DEADLOCK_PRIORITY

When it detects a deadlock, SQL Server will, by default, pick the process the has the lowest cost to rollback. This can present a potential issue if the process is a business critical process. The DEADLOCK_PRIORITY allows us to have a bit of control over whether or not a process is chosen as the victim.

Diagnosing Why SYSUTCDATETIME is Faster than SYSDATETIME

On my machine, code that calls SYSDATETIME() 10 million times in a loop takes about 11.6 seconds to execute, while with SYSUTCDATETIME() it takes 4.3 seconds. Why is SYSUTCDATETIME() so much faster than SYSDATETIME()?

Using VALUES to construct an in-line table.

This has come up a few times recently, I find it r...

T-SQL Tuesday #114 Puzzle Party – Roundup

Here’s my round-up for this month’s T-SQL Tues...

Find Occurrences of Three and Four-Part Names on a SQL Server Instance

Hunt down this stuff before stuff starts to break!

ISNULL vs COALESCE

Find identical duplicate indexes

The purpose of this query is to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. If you have a need to...
The...

How to delete a lot of data from a SQL Server database

How to delete data efficiently When we delete data from a table in SQL Server, we can often find that the delete is slower than even the original insert,...
The...