SQL Server – Group by and Group by Rollup

In this article, we will see Group By and Group By Rollup in SQL Server. Group By The GROUP BY clause in the SQL server groups rows with the same values into summary rows. Group By is used with aggregate functions. Some of the aggregate functions are Count(), Sum(), Max(), Min(), AVG() Group By Clause … Read more

Mastering SQL Server: Essential Tips for Beginners

In this article, We will cover all the topics of SQL Server. This will work as SQL Server notes for beginners. SQL Server is a relational database management framework created by Microsoft. It is profoundly adaptable and runs on Windows-based machines. Since the arrival of the SQL Server’s first version, the SQL server has continued … Read more

Trace and Detect a Deadlock with SQL Server Profiler

In this article, we will see about deadlocks in SQL and how to trace and detect a deadlock with SQL Server Profiler. Let’s start with an introduction to Deadlock. What is Deadlock? Deadlock in SQL server is a scenario where two or more resources are trying to access the same resource which is mutually being … Read more

How do you remove duplicate records with different status in SQL Server?

In this article, I will explain a scenario where the user has to remove duplicate records with different statuses. Scenario Consider below the table with 2 columns (Employee Name and Status) Now, write a query to fetch a unique Employee Name like this – If the Employee Name is unique then select it and if … Read more

JSON functions and examples in SQL Server

In this blog, we will see the use of JSON functions and examples in SQL Server. We will also see how to query and modify JSON data using SQL Server Query.As you already know JSON stands for JavaScript Object Notation, which is a lightweight computer data interchange format.We can use JSON data in SQL Server tables … Read more

Differences between Stored Procedure and Functions

Very frequent question asked in interview is Differences between Stored Procedure and Functions.Below are the differences between these 2. 1) Functions must return a value. Stored procedure need not be.2) Stored procedures can be called using EXEC command where as Functions can be called from another SQL Statement.3) Stored Procedures are precompiled. Functions are not precompiled.4) Generally Stored procedures are … Read more

Important SQL Query asked in Interview

Important SQL Query asked in InterviewSQL query to check a record in table, if exist then insert else update the record. BEGIN  IF EXISTS (SELECT * FROM [users] WHERE email=@email)    UPDATE [users] SET updated_date = GETDATE() WHERE email=@emailELSE    INSERT INTO [users] (uid,first_name,last_name,email,password,gender,is_active,reg_date) VALUES (NEWID(),@fname,@lname,@email,@password,@gender,1,GETDATE())ENDStored procedure to return id of last inserted record:@id int … Read more

SQL Server Interview Questions.

In this blog, we will see some of the important SQL Interview Questions and Answers. 1. What is a Primary Key? The primary key is used to uniquely identify a record. A table can not have more than one primary key.If a table has userid, email, passport no. and userid is the primary key then how to maintain the … Read more

All about sp_addlinkedserver syntax

sp_addlinkedserver linked 2 servers. Once sp_addlinkedserver query executes, query can be run against these servers. Run below queries one by one to link, to provide login credentials and to access data EXEC sp_addlinkedserver @server=’ServerName’ EXEC sp_addlinkedsrvlogin ‘ServerName‘, ‘false’, NULL, ‘username’, ‘password’ select * from [ServerName].[DBName].[DBO].[TableName] Once your requirement is done, you may drop the linking by … Read more

How to link 2 SQL DB servers

If someone wants to get data from 2 tables from 2 different SQL DB server. Then execute below stored procedures before running your query. Step1: exec sp_addlinkedserver    @server=db-server-name Step2: EXEC sp_addlinkedsrvlogin db-server-name, false, NULL, id, password Once you executed above SPs successfully, you can check linked server list by executing below SP. exec sp_linkedservers