Alexander Kuznetsov

Inline UDFs do not prevent parallel execution plans

Alexander Kuznetsov — Wed, 20 Feb 2013 14:23:00 GMT

Lots of things can prevent parallel execution plans, but inline UDFs are not one of them. Inline UDFs are essentially macros - they are at least as fast as CTEs. As such, the optimizer can produce exactly the same parallel execution plans whether if our...(read more)

Using constraints to enforce uniqueness of ordered sets of rows

Alexander Kuznetsov — Mon, 28 Jan 2013 20:10:00 GMT

Even the simplest data integrity rules are not easy to implement in the database if instead of individual rows we need to deal with groups or subsets. For example, making sure that a column value is unique in a table is as trivial as creating a unique...(read more)

The client code that handles timeouts

Alexander Kuznetsov — Thu, 10 Jan 2013 21:05:00 GMT

After timeouts we need to make sure that active transactions, if any, are rolled back. All timeout handling must be done on the client. This post provides the implementation and unit tests. Implementation The following class extends SqlCommand and rolls...(read more)

After the timeout

Alexander Kuznetsov — Wed, 09 Jan 2013 18:23:00 GMT

After the timeout, we may sometimes, but not always, end up with an outstanding transaction. If this is the case, we need to rollback the transaction ourselves. Otherwise the consequent commands which use the same connection might not work as expected....(read more)

Book Review: Pro SQL Server 2008 Relational Database Design and Implementation

Alexander Kuznetsov — Mon, 24 Sep 2012 16:56:00 GMT

Investing in proper database design is a very efficient way to cut maintenance costs. If we expect a system to last, we need to make sure it has a good solid foundation - high quality database design. Surely we can and sometimes do cut corners and save...(read more)

Awesome videos on Agile

Alexander Kuznetsov — Mon, 18 Jun 2012 20:45:00 GMT

The following videos are a must-see for anyone interested in Agile methodology: Decisions, Decisions Embracing Uncertainty Patterns of Effective Delivery Enjoy!...(read more)

Yet another use of OUTER APPLY in defensive programming

Alexander Kuznetsov — Wed, 06 Jun 2012 21:58:00 GMT

When a SELECT is used to populate variables from a subquery, it fails to change them if the subquery returns nothing - and that can lead to subtle bugs. We shall use OUTER APPLY to eliminate this problem. Prerequisites All we need is the following mock...(read more)

Catching multiple exceptions on the client is robust and easy

Alexander Kuznetsov — Wed, 06 Jun 2012 17:30:00 GMT

Maria Zakourdaev has just demonstrated that if our T-SQL throws multiple exceptions, ERROR_MESSAGE() in TRY..CATCH block will only expose one. When we handle errors in C#, we have a very easy access to all errors. The following procedure throws two exceptions:...(read more)

Avoiding nested transactions might not improve performance.

Alexander Kuznetsov — Wed, 08 Feb 2012 18:09:00 GMT

Beginning a transaction only when @@TRANCOUNT=0 might not improve performance at all. At least, I did not notice any difference whatsoever. No matter if I use this pattern: BEGIN TRAN ; -- (snip) COMMIT ; or a more complex one: DECLARE @trancount INT...(read more)

Using XACT_ABORT ON may be faster than using TRY...CATCH

Alexander Kuznetsov — Wed, 01 Feb 2012 22:25:00 GMT

To ensure atomicity of transactions, we can use XACT_ABORT ON or wrap the transaction in TRY block and rollback in CATCH block. In some cases, the XACT_ABORT ON approach uses noticeably less CPU. I am posting repro scripts. Please run them, tweak them,...(read more)

Wrapping related changes in a transaction may use less CPU.

Alexander Kuznetsov — Wed, 01 Feb 2012 22:02:00 GMT

Wrapping related changes in a transaction is a good way to ensure data integrity. Besides, in some cases it just runs noticeably faster, using less CPU. As usual, I am posting repro scripts, which you can run, tweak, and see for yourself. Environment...(read more)

Yet another gotcha: variables' scopes do not end where they should.

Alexander Kuznetsov — Wed, 25 Jan 2012 22:09:00 GMT

Be careful: unlike most other languages, T-SQL does not limit variables' scope to the block where the variable has been defined. For example, the following snippet compiles and runs: -- @to is not in scope yet -- the line below would not compile --SET...(read more)

Running OLTPish system without deadlocks, and loving it.

Alexander Kuznetsov — Wed, 04 Jan 2012 22:31:00 GMT

Our OLTPish mixed load system has not had a single deadlock since last April, and we just love it. I would not make any blanket statements, but I think in our case being deadlock-free just makes a lot of practical sense. Of course, in other cases in might...(read more)

Refactoring large live OLTP tables without downtime

Alexander Kuznetsov — Tue, 03 Jan 2012 18:11:00 GMT

Refactoring tables does not have to be such a big deal. We do not have to shut modifications down, migrate all data into new structure, deploy modified modules, and do it all at once, while the system is down. Doing all migration at once may be risky,...(read more)

"Trusted" Foreign Keys Allow Orphans, Reject Valid Child Rows

Alexander Kuznetsov — Mon, 17 Oct 2011 17:15:00 GMT

In SQL 2008 R2, MERGE does not implement foreign keys properly. I will show both false negatives (valid rows are rejected) and false positives - orphan rows that are allowed to save. False Negatives The following tables implement a very common type/subtype...(read more)