Browse by Tags
» Transact SQL (RSS)
Showing page 1 of 6 (56 total posts)
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.
All we need is the following mock function that imitates a subquery:
CREATE FUNCTION dbo.BoxById ( @BoxId INT )RETURNS ...
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:
CREATE PROCEDURE dbo.ThrowsTwoExceptionsAS BEGIN ...
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 ;SET @trancount = @@TRANCOUNT ;IF @trancount = 0 BEGIN ; BEGIN TRAN ;END ...
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, and post your findings.
I've run my scripts on 2008 R2 Dev Edition. Snapshot ...
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.
I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, ...
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 @to = @to + 3 ;IF DATEPART(weekday, GETDATE()) = 3 BEGIN ...
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 not be worth it.
To accomplish deadlock-free state, we have taken the following ...
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.
The following tables implement a very common type/subtype pattern:
CREATE TABLE dbo.Vehicles( ID INT NOT ...
Suppose that Bob can retrieve all messages, as follows, and we are not happy with it:
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 FROM Bob ;
This does not work - Bob still can execute ...
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 committed isolation using row versioning provides statement-level read consistency''. ...