|
|
|
|
Browse by Tags
All Tags » Transact SQL (RSS)
-
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...
|
-
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 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...
|
-
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...
|
-
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...
|
-
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...
|
-
Suppose that Bob can retrieve all messages, as follows, and we are not happy with it: EXEC Exchange.ShowAllMessages ; Suppose that we don't want him to read anything related to messages, so our knee-jerk reaction is this: REVOKE EXECUTE ON Exchange.ShowAllMessages Read More...
|
-
When a select uses scalar or multi-statement UDFs under READ_COMMITTED_SNAPSHOT, we might not get consistent results as of the time our select began - I will provide simple repro scripts. At the time of this writing MSDN clearly states the following: Read More...
|
-
In other words, if we add a month, then subtract a month, we might not get back to the date we started from. For example: SELECT DATEADD ( MONTH , 1 , DATEADD ( MONTH , - 1 , '20100330' )) , DATEADD ( MONTH , - 1 , DATEADD ( MONTH , 1 , '20100330' )) Read More...
|
-
The biggest advantage of unit testing is the ability to make changes quickly, and with confidence that we have not broken anything with our change. Whether we need to speed up a query real quick, or to fix a bug, automated testing saves us a lot of time, Read More...
|
-
This came up on sqlservercentral.com, where I read the following claim (in a discussion thread, not in an article): " COUNT(*) is only guaranteed accurate if your isolation level is REPEATABLE READ, SERIALIZABLE, or SNAPSHOT ". I have a repro script which Read More...
|
-
I was reading Eric Lippert's blog post , and came across the following quote: "uncertainty erodes confidence in our users that we have a quality product that was designed with deep thought, implemented with care, and behaves predictably and sensibly". Read More...
|
-
My book is finished. A couple years ago I googled up "Defensive Database Programming", and came up with nothing. Naturally, I started filling up the void. Although the book is complete, I am planning to continue my research; I would appreciate any other Read More...
|
-
It is well known that SELECT * is not acceptable in production code, with the exception of this pattern: IF EXISTS( SELECT * We all know that whenever we see code code like this: Listing 1. "Bad" SQL SELECT Column1 , Column2 , Column3 , Column4 , Column5 Read More...
|
-
Even if two processes compete on only one resource, they still can embrace in a deadlock. The following scripts reproduce such a scenario. In one tab, run this: CREATE TABLE dbo.Test ( i INT ) ; GO INSERT INTO dbo.Test ( i ) VALUES ( 1 ) ; GO SET TRANSACTION Read More...
|
|
|
|
|
|