#t-sql scripts

Navigating the vast landscapes of data within a SQL Server instance can sometimes feel like exploring uncharted territories. Particularly in environments where databases grow to monumental sizes, housing billions of rows, identifying the largest tables becomes not just a matter of curiosity but a crucial task for optimizing performance, storage, and management strategies. Why Focus on Large…

In today’s data-driven world, the ability to efficiently manage and analyze vast amounts of information is paramount. SQL Server, a cornerstone technology in the realm of database management, offers a powerful feature known as Columnstore Indexes. These indexes are designed to dramatically improve query performance, making them an indispensable tool for businesses that rely on data analytics. In…

T-SQL: Find if “xp_cmdshell” is configured on your SQL Server instance

Hi Friends,

How are you doing?

Today, we will see how to check if the “xp_cmdshell” is already configured on your SQL Server instance or not. We can check the other configurations as well using the same T-SQL.

What is “xp_cmdshell”?

“xp_cmdshell” allow us to run windows commands by using SQL Server, but we need to enable this!

We can check if this configuration is already configured on this…

T-SQL: Find the location of data and log files of the databases within a SQL Server instance.

Hi Friends,

Hope, you are doing fine!

Today, please find below the T-SQL script tip to find the location of the data & log files available within a SQL Server instance. The script is as below:

T-SQL:

SELECT name AS NameOfFile, physical_name AS FileLocation FROM sys.master_files

Snapshot:

Happy Coding :)

T-SQL to find I/O usage stats by databases within the SQL Server instance

Hi Friends,

Hope, you are doing fine!

Today, we will see how to extract the I/O statistics for data and log files within a SQL Server instance, with the help of dynamic management view (DMV) named as (“sys.dm_io_virtual_file_stats“).

T-SQL Script:

-- T-SQL to find I/O usage stats by databases within -- the SQL Server instance (Database level IO usage stats) SELECT DB_NAME(database_id) AS…

T-SQL Fun: Guess your age based on two parameters

Hi Friends,

Hope, you are doing fine! Today is the last day of 2016 and I hope that this year brought a lot of happiness and good lessons to all of our life. I wish you all a very happy and prosperous new year 2017 in advance!

Today, I will share a T-SQL for fun to demo a trick to find your age, based on two parameters. I know that this could be also done directly in T-SQL using many date’s…

“With Result Sets ()” – SQL Server 2012 T-SQL’s New feature

Hi Friends,

Hope, you are doing fine!

Today, I will let you know the new feature (“With Result Sets ()“) introduced in MS SQL Server 2012 version for the T-SQL Development.

But, before that, let’s understand the case where the importance of this new feature comes into the picture!

Problem Case:

Suppose, you have designed one stored procedure which returns some resultset back to the user (let’s…