Author: Brahmanand Shukla

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

T-SQL script to find hierarchy of tables – Row-wise result

As I promised in my previous post T-SQL script to find hierarchy of tables – Columnar result – SQL Server Carpenter, here is another version of the query to get the hierarchy of tables in the form of rows. ; WITH cte_Hierarchy_Of_Tables AS ( SELECT A.[name] AS [Parent] , A.[object_id] AS [Parent_Object_ID] , A.[name] AS … Continue reading T-SQL script to find hierarchy of tables – Row-wise result