Query to find Nonclustered Index clashing with Clustered Index

It's being quite a long time when I made the last post. Actually, I was lacking a topic that should trigger me to write. Finally, I got one when I was working on one of the Performance Tuning assignments. Performance Tuning is a mix of wisdom and exploring the unknown, especially when you have a … Continue reading Query to find Nonclustered Index clashing with Clustered Index

Lesser-known facts of Explicit Transactions

Transactions are of various kinds in SQL Server such as Autocommit, Implicit, Explicit and Batch-scoped. I personally found this article quite helpful if you wish to understand Implicit Transactions. When we talk about transaction, three statements BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION immediately comes to our mind. You must be aware of these. If … Continue reading Lesser-known facts of Explicit Transactions

Behavior of numeric data types with ”

As we all know, there are various numeric data types, such as TINYINT, INT, SMALLINT, BIGINT, NUMERIC, DECIMAL, SMALLMONEY, MONEY and FLOAT. I was working on a SSIS package, which had staging table with all varchar columns. The files were loaded into the staging table, gets validated for the data type, length etc. and the … Continue reading Behavior of numeric data types with ”

Query to list all the procedures and triggers not having SET NOCOUNT ON defined

It is a tiny query in size but can play a crucial role to avoid overhead, caused due to missing SET NOCOUNT ON definition in the stored procedures and triggers. Read more about SET NOCOUNT here. Here are few useful articles talking about why it's important to add SET NOCOUNT ON. SET NOCOUNT ON Improves … Continue reading Query to list all the procedures and triggers not having SET NOCOUNT ON defined