Explore the Unknown
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... Continue Reading →
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... Continue Reading →
Introduction When working on large-scale projects, we might have created many tables, or later some point we might have changed the structure as part of the requirements. There is a huge possibility of tables that are not required or unused for a long time. A good practice is to remove/delete those by identifying them. In... Continue Reading →
Introduction In this article, we will discuss how to get the list of table names that contain specific columns. Sometimes we may need to find the table names which contain similar column names or we may know the column name but, we may not remember the table name. By using a simple query we can... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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,... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
Introduction In a single transaction (MERGE Statement) we can perform Insert, Update and Delete records on a existing table based on the result comparison between important columns with another table. Which also means we no longer needs to write multiple T-SQL statements to handle Insert, Update, Delete. MERGE Statements To perform MERGE, we need two... Continue Reading →
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... Continue Reading →
Introduction When working in the SQL Server, we may have to check some other databases other than the current one which we are working. In that scenario we may not be sure that does we have access to those Databases?. In this article we discuss the list of databases that are available for the current... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
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... Continue Reading →
Introduction In this article, we will discuss the difference between CURRENT_TIMESTAMP , GETDATE() , SYSDATETIME() GETUTCDATE() in SQL Server. Even though all four SQL Server function returns the current date-time in SQL Server, there are some subtle differences between them. The main difference between GETDATE() and SYSDATETIME() is that GETDATE returns current date and time... Continue Reading →
Introduction In this article we will learn the trick to Move a Table from One Schema to Another Schema. Sometimes accidentally we might have given the incorrect name of a schema for the table while creating. But we can move the newly created table from one schema to another schema. Syntax ALTER SCHEMA NewSchemaName TRANSFER... Continue Reading →
Introduction In this article, I will share few SQL Tips for Boosting our SQL Query Performance. We nobody likes delay in response, right !!. Here are some simple changes that we have to make our query perform faster. The following is the flow of this write-up, Use Schema NameAlways Select ONLY required ColumnsUse NOT EXISTS... Continue Reading →
Introduction Sometime we may need to select the column results that the string starts with Vowels. In this article, We will have a look, How to select the columns results starting with Vowels. Example As an example, we have a StudentDetails table, first lets select all the results from the table. SELECT [StudentId] ,[FirstName] ,[LastName]... Continue Reading →
Introduction Sometimes when we encounter an error message for a T-SQL query while debugging. The SQL Server shows the error message along with the line number where the error has occurred. In this article, I will give a simple tip to enable the Line Number feature in SSMS. This feature is applicable to all versions... Continue Reading →
Introduction Sometimes we may need to get all the tables and views from a database in MS SQL Server. By using T-SQL Statement sp_tables, we can retrieve a list of tables and views. Syntax sp_tables [ @table_name = 'Table name.' ] , [ @table_owner = 'The database user who created the table.' ] , [... Continue Reading →
Introduction In the Database, we may be using Stored Procedures. Most of the time we are concerned about the performance of the Stored Procedures. In this article, I would like to share a few techniques to improve the stored procedure's performance to get better results in less time. Optimization Techniques SET NOCOUNT ON/OFF - When... Continue Reading →
Introduction Sometime we may need almost all the columns from a table result. In this scenario we use Star(*) in our select statement i.e. as SELECT * FROM TableName. Using Star(*) in the select statement is BAD. The best approach is selecting the table with column names. In this article we will learn how quickly... Continue Reading →
Introduction Sometime, we may need to store non-English characters or multiple languages in our database. In this article, we will have a look at how to store non-English characters in SQL Server. VARCHAR vs NVARCHAR We can store non-English Characters in our database table using the data type NVARCHAR(). The N stands for Unicode. It... Continue Reading →
Introduction In this article, We will discuss how to backup our database in MS-SQL Server using T-SQL Statements. We need to use BACKUP DATABASE statement to create full database backup, along with the name of Database and device to store the backup file. Syntax BACKUP DATABASE database_name TO device_name Example BACKUP DATABASE PracticalWorks TO DISK... Continue Reading →
Introduction While working in SQL Server Management Studio (SSMS), we may need to open many query windows. But each time we may feel difficult to recognize the important query window which we need to refer frequently. There is an option, we can Pin the tabs which are mostly frequently referred. Pinning Tabs We can pin... Continue Reading →
Introduction Sometime we may write a long query in our query window and we may need to refer the objects or other part of the T-SQL frequently. For that we may be scrolling up and down in the query window. But we have an awesome option for that in the SQL Query Window, which many... Continue Reading →
Learn With Sundar 