|
|
|
|
Browse by Tags
All Tags » Transact SQL (RSS)
-
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...
|
-
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...
|
-
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...
|
|
|
|
|
|