Quick Reference Guide for SQL Server DBA Daily Tasks

Introduction As a SQL Server Database Administrator (DBA), daily operations can be time-consuming and require a vast amount of knowledge and experience. From managing backups to troubleshooting performance issues, a DBA's daily tasks can be daunting without a proper reference guide. That's where a cheat sheet or quick reference guide can come in handy. It...

Azure Data Studio vs SSMS – Overview

Introduction When it comes to working with SQL Server, Microsoft offers two powerful tools: Azure Data Studio and SQL Server Management Studio (SSMS). While both tools are designed for managing SQL Server, they differ in several key areas. In this article, we will explore the features and benefits of each tool, and discuss which scenarios...

T-SQL Query To Find Deadlocks In MS SQL Server

Introduction As a Backend Developer, I have frequently encountered performance issues, particularly those stemming from poorly written code or lack of indexes, which can lead to blocking conditions called "deadlocks." These situations can directly impact user experience and become more severe with frequent occurrences. This article addresses deadlock issues and reporting. Upon completion, readers will...

How To Remove Duplicate Values Without Using DISTINCT In SQL Server

Introduction Whenever we work with data, sometimes there are some possibilities of getting duplicate data, not only because of the duplicate entries but also because of the T-SQL statement. We may need to remove it as well. In this instance, most of us follow to implement the DISTINCT keyword in our T-SQL Statement. Applying DISTINCT...

Find Database Owner In SQL Server

Introduction In this simple article, let's explore different ways to find a Database owner. When a database is created, generally who creates it owns it and they have all permissions on that database. The Owner can perform maintenance of the database, grant permissions to other users, and even drop the database. Different Ways to Find...

Where are the SQL Jobs are Stored in SQL Server

Introduction In this article, We will discuss where the SQL Jobs are stored in SQL Server. Most of us know the purpose of SQL Job, A job is a specified series of actions that SQL Server Agent performs. Use jobs to define an administrative task that can be run one or more times and monitored...

How to Check SQL Server Database Size

Introduction In this simple article, I would like to share how to check DB size. Sometimes when working in a Database, we may need to know the current space occupied by that particular DB. Based on the size, we need to take action. There are three easiest ways to check it, Using T-SQL StatementUsing SP...

Find When was a SQL Server View Last Modified

Introduction In this simple tips and tricks article, I would like to share how to when was a view last modified. Let's have a look at the below T-SQL Statement SELECT name as ViewName, create_date as CreatedDt, modify_date as LastModifiedDt FROM sys.views In the above T-SQL Statement list, from the column LastModifiedDt column we can...

Find Table Creation Date In SQL Server

Introduction In this article, we will discuss how to get the created date of a table. Sometimes, we may need to know when the table was created in the particular database. Rather than relying on the row inserted date, we can see when the table was created. Finding the Created date using T-SQL SELECT name...

List All Available Time Zone – SQL Server

Introduction In this simple tips and tricks article, I would like to share how many time zones does our SQL Server supports. Well, it's pretty much easy to find by using the system table sys.time_zone_info sys.time_zone_info Here is the sample T-SQL to get the list of time zones our SQL Server supports. SELECT name AS...

To Get Total Number of Columns In a Table In SQL Server

Introduction In this simple article, lets explore to get total number of columns in a table in SQL Server Example SELECT COUNT(COLUMN_NAME) AS [Number of Columns] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'PracticalWorks' AND TABLE_SCHEMA = 'Demo' AND TABLE_NAME = 'OrderDetails' Fig. 1 Number of Columns in a Table From the INFORMATION_SCHEMA.COLUMNS table we can find...

Altering Column – From NULL to NOT NULL in SQL Server

Introduction When working in SQL Server tables, sometimes we may need to change a column that allows NULL to NOT NULL. This change is required as part of business requirement, that is definitely this column contains value or else as part of performance improvements, we may be in need to change the column from NULL...

Execution Time of Stored Procedures In SQL Server

Introduction When working in SQL Server Stored Procedures, we are keen about the execution time of it. Its very simple to find execution time of stored procedures in SQL Server. Execution Time Its simple to figure out the execution time of stored procedures using SET STATISTICS TIME ON. SET STATISTICS TIME ON EXEC [Sales].[pGetSalesDetails] After...

Different Ways To Get Table Column Names In SQL Server

Introduction In this simple article, we will discuss different ways to find the particular table's column names in SQL Server. Method One The first approach is using the system table sys.columns. This table results many columns, but we are interested only in the column name, so I am selecting name column from the sys.columns table,...

Power BI Basic Data Transformation Technique

Introduction When working in a large set of Data, definitely we cannot use the data as it is. We need to perform some refinements so that we can exactly look into the data in which we are interested. In Power BI, this process is called Data Transformation, which can be done in different ways. In...

Find Columns That Allows NULL In SQL Server

Introduction The columns in a table, that allows NULL in a database sometimes can lead to performance issues. The Nullable columns may be one of the reason to lead query to performance bad. By making columns NOT NULL can help the query to perform well. In this article we will discuss a simple way to...

SQL Server Rank Functions

Introduction As like an Aggregate functions, the Ranking functions will rank the values of specified field and categorize them according to their rank . Ranking functions are most commonly used to find the top records based on conditions, example, to find the top ranking students, top highest paid employees and etc. Ranking Functions There are...

T-SQL Query For Finding The Longest And Shortest names In A Table

Introduction In SQL Server, we can find the Longest String Value and Shortest String Value from the same table. To find the longest and shortest values, there are many ways, but in this article, we will discuss some simplest ways. Disclaimer: From this article, I am not sharing these are the only efficient ways to...

Creating T-SQL Query Shortcuts in SQL Server Management Studio(SSMS)

Introduction Using keyboard shortcuts, it saves time, isn't it?. SQL Server Management Studio is also not an exception !!!. We can add our own custom keyboard shortcuts, that too for T-SQL query. Really cool !!!! How many of you know about this feature???. Do not worry if you did not know this before. Now you...

One Drop Statement For Multiple Tables In SQL Server

Introduction When as a developer working in a database, which is in Development state, we may create some physical tables or temp tables to check the logic. Or based on the initial requirement we may have created more tables as part of Normalizing the data and our reviewer may suggest some better ideas to accommodate...

Power BI Tips and Tricks – Power BI Dashboard Tutorials

Introduction Who will say to NO to know some tips and tricks which will improve our productivity and also makes task simpler. In this article, we will discuss some important simple tips and tricks in Power BI to create Dashboards and reports. 5 Simple Tips and Tricks If we want to keep same visualization for...

Azure Data Studio Tips And Tricks

Introduction Who will say NO to when they want to improve the speed and efficiency of writing SQL Queries?. Azure Data Studio has more features to improve efficient way of writing SQL Queries. In this article, we will have a look some Tips and Tricks in Azure Data Studio Command Palette If we want to...

Select Names Ending With Vowels in MS SQL Server

Introduction Sometime we may need to select the column results that the string ends with Vowels. In this article, We will have a look, How to select the columns results ending with Vowels. In one of the articles, we discussed how to select names Starting with Vowels, please refer that article here. Example As an...

Create and Publish SQL Server Database Project With Visual Studio

Introduction Databases plays an important role in any project these days. It becomes too difficult to manage the objects such as Tables, Views, Procedures and Functions increases over the time and also difficult to manage when number of team members works in Database at the same time. And even after that when we manage the...

Customize Azure Data Studio with Dashboard Widgets

Introduction Azure Data Studio is a free multi-platform database tool with built-in support for both SQL Server on-premises and Azure SQL Databases. These databases can be accessed in Azure Data Studio for numerous tasks like query editing, data development, built-in charting of T-SQL queries, etc. Database developers or Database Administrators often have a need to...

Cycle Clipboard Ring In SSMS – Reuse Copied Items

Introduction As a developer, the most used shortcut keys are CTRL+C and CTRL+V i.e., copy and paste. But CTRL+C and CTRL+V allows us to copy paste the last item. But what happens if we want to paste more than one item that we copy or cut without going back? Cycle Clipboard Ring Cycle Clipboard Ring...

Set custom colors to differentiate between environments in SSMS

Introduction Generally we have to shift between different environments within SQL Server Management Studio, as Dev Environment, Staging, Production, resulting in the creation of multiple query tabs. With each query tab connected to different environments. This leads to unmanageable sometimes and difficult to keep track of each environment. To resolve this confusion, SSMS provides us...

Recover unsaved SQL queries in SSMS

Introduction During our productive hours, sometimes due to SSMS crashes or sudden machine reboots due to a windows update, during this time there is a possibility to lose the unsaved works / Script files. There is an option to recover our unsaved SQL queries in SSMS. How to recover unsaved SQL Queries By enabling the...

Multiple Backup Files of the SQL Server database with SSMS and T-SQL

Introduction In this article, we will learn a simple tip to take multiple backup files of one Database. Sometime we may need more than one copy of a backup file in different file location and we can achieve that in two ways, either using T-SQL Query or using SSMS. T-SQL Statement BACKUP DATABASE [PracticalWorks] TO...