SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON – A Transactional Behavior

Earlier, I had written on SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON, and I received a comment regarding when this option can be useful. On the same day, I met Jacob Sebastian—my close friend and SQL Server MVP, I discussed this question with him. During our…
Read More

SQL SERVER – Difference Temp Table and Table Variable – Effect of Transaction

Few days ago I wrote an article on the myth of table variable stored in the memory—it was very well received by the community. Read complete article here: SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth. Today, I am going to write an article…
Read More

SQL SERVER – Interesting Observation of DMV of Active Transactions and DMV of Current Transactions

This post is about a riveting observation I made a few days back. While playing with transactions I came across two DMVs  that are associated with Transactions. 1) sys.dm_tran_active_transactions – Returns information about transactions for the instance of SQL Server. 2) sys.dm_tran_current_transaction – Returns a single row that displays the…
Read More

SQL SERVER – Transaction and Local Variables – Swap Variables – Update All At Once Concept

This article is inspired from two sources. Let us learn today about how to swap variables by updating everything at once concepts.

1) My year old article – SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT
2) Discussion with SQL Server MVP – Jacob Sebastian – SQLAuthority News – Author Visit – SQL Hour at Patni Computer Systems

I usually summarize my article at the end, but this time let me summarize first and we will understand the article next.

Read More

SQL SERVER – Detailed Explanation of Transaction Lock, Lock Type, Avoid Locks

Loyal reader of this blog and “Great SQL Expert” Imran Mohammed always have good attitude towards any problem. Many times his answers very interesting to read and details are very accurate. I came across his two interesting comment on this blog and I would like to share this all of…
Read More

SQL SERVER – Two Connections Related Global Variables Explained – @@CONNECTIONS and @@MAX_CONNECTIONS

Few days ago, I was searching MSDN and I stumbled upon following two global variables. Following variables are very briefly explained in the BOL. I have taken their definition from BOL and modified BOL example to displayed both the global variable together. @@CONNECTIONS Returns the number of attempted connections, either…
Read More