|
|
|
|
Browse by Tags
All Tags » Defensive programming (RSS)
-
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...
|
-
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...
|
-
Yesterday I delivered a session named "Develop T-SQL defensively" at East Iowa SQL Saturday. Thanks to those who attended, and many thanks to Michelle Ufford, Chris Leonard, Ed Leighton-Dick, and other volunteers, who organized the event. Here are the Read More...
|
-
Yesterday I delivered a session named "Developing T-SQL to survive concurrency" at East Iowa SQL Saturday. Thanks to everyone who attended, and thanks to Michelle Ufford, Chris Leonard, Ed Leighton-Dick, and other volunteers, who organized the event. Read More...
|
-
In a few weeks I will be meeting a lot of old friends and new folks at SQL Saturday #50 in Iowa City . I'll do two presentations: on defensive database programming and on coding for high concurrency. I really like this whole SQL Saturday thing. For me 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...
|
-
My second take at the same problem I blogged about yesterday: To optimize a query, we frequently have to explicitly tell optimizer some information which it does not realize by itself. Short term, this works, but long term we run the risk that what we Read More...
|
-
To optimize a query, we frequently have to explicitly tell optimizer some information which it does not realize by itself. Short term, this works, but long term we run the risk that what we are telling to the optimizer may be no longer relevant. This Read More...
|
-
The schedule is so interesting that I want to get a clone and be in two sessions at the same time. I think of this event as our local PASS Summit Lite – all the benefits like attending sessions and meeting interesting people, but without having to put Read More...
|
-
We can begin a transaction under snapshot isolation, but we cannot switch to it in the middle of an outstanding transaction. For example, the following procedure looks good and passes a smoke test: CREATE PROCEDURE dbo.SelectCountry @CountrySymbol CHAR Read More...
|
-
To avoid deadlocks, one of the most common recommendations is "to acquire locks in the same order" or "access objects in the same order". Clearly this makes perfect sense, but is it always feasible? Is it always possible? I keep encountering cases when Read More...
|
-
Neither UPDATE … IF (@@ROWCOUNT = 0) INSERT nor IF EXISTS(...) UPDATE ELSE INSERT patterns work as expected under high concurrency. Both may fail. Both may fail very frequently. MERGE is the king - it holds up much better.Let us do some stress testing Read More...
|
-
Some T-SQL code is written under the assumption that either a TRY block successfully completes or a CATCH block is invoked. Most likely, this is the case. However, there is a third, although rare, possibility – the TRY block may fail, and the CATCH one Read More...
|
-
Although there are many discussions about which kind of cursor or loop performs the best, there is no doubt which loops perform the worst – the infinite ones of course. Whenever you write a loop, you need to make sure that it never runs infinitely. I Read More...
|
|
|
|
|
|