Category: TSQL and SQL Client Code

ROWVERSION/TIMESTAMP skipping rows problem

Do you use ROWVERSION/TIMESTAMP to pull changed data? If you do, you might experience a problem – not all changed rows are pulled, some are skipped. How can that be, if we pulled entire range of rowverion changes with no

Tagged with: , , , ,

Single-Row Deadlock

Introduction Demonstration of the smallest possible deadlock: only one statement, one table, one row. Without transactions (no BEGIN TRANSACTION). Even RCSI (Read Committed Snapshot Isolation) is turned ON to eliminate shared locks. Everything is “by the book” as Books Online

Tagged with: ,

Query with optional filters (catch-all query)

In almost every database we have a process that filters the same data with different filters. Typically, query is within a stored procedure that has many filtering parameters that are optional. The problem is – a query optimized builds only ONE plan that will handle every filter combination you throw at it.

Tagged with: , , ,

Formatting TSQL code

How do I format my TSQL code? Everybody has it’s own preferences on formatting SQL or TSQL code. I follow few very simple rules to achieve better readability, less bugs and  – you wont’ believe – faster code execution! Code

CommandTimeout – How to handle it properly – code example

Properly closing a connection to Sql Server in DotNet is more challenging than one might think. Classic code looks something like this MSDN example:

Tagged with: , ,

CommandTimeout – How to handle it properly?

ADO.Net Data Provider for SQL Server (System.Data.SqlClient namespace in System.Data.dll assembly) has two common classes we often use: SqlConnection and SqlCommand. SqlCommand class has CommandTimeout property (do not mix it with the ConnectionTimeout which stops long login process). But, do

Tagged with: ,

Sort records without ORDER BY?

How often you see some smartie “optimizes” the query by removing ORDER BY, justifying that query always goes by that index, and index is in desired order for the result? Or they say: “I executed this query thousands of times

READ COMMITTED sucks!

Are you still using READ COMMITTED transaction isolation level? Default transaction isolation level on all SQL Server versions (2000-2014) has serious inconsistency problems, by design. Not many people are familiar how and why is it happening. I am writing this

Top
Visit Us On TwitterVisit Us On FacebookVisit Us On LinkedinVisit Us On YoutubeVisit Us On Google PlusCheck Our Feed