TOP 20 SQL interview questions for 2025

 

TOP 20 SQL interview questions for 2025

Hey Candidates here are the Top 20 SQL, that will help you crack the SQL interview

1. What is SQL?

SQL (Structured Query Language) is a standard language for accessing and manipulating databases. It is used for querying, updating, and managing data stored in a relational database.

2. What are the differences between SQL and MySQL?

• SQL is a standard language for accessing and manipulating databases, while MySQL is a relational database management system (RDBMS) that uses SQL as its standard database language.
• SQL is a query language, and MySQL is software for managing databases.
• SQL commands are used in various database systems, whereas MySQL is a specific implementation of an RDBMS.

3. What are DDL and DML commands? Give examples.

• DDL (Data Definition Language) commands are used to define the database structure. Examples include CREATE, ALTER, and DROP.
• DML (Data Manipulation Language) commands are used for managing data within the database. Examples include SELECT, INSERT, UPDATE, and DELETE.

4. Write a query to retrieve all records from a table.

SELECT * FROM table_name;

5. How do you Join 2 tables

Tables can be joined using various types of joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, 

and FULL JOIN. Here's an example using INNER JOIN:
SELECT *
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

6. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

• INNER JOIN returns records that have matching values in both tables.
• LEFT JOIN returns all records from the left table and matched records from the right table. If no match, NULL values are returned for columns of the right table.
• RIGHT JOIN returns all records from the right table and matched records from the left table. If no match, NULL values are returned for columns of the left table.
• FULL JOIN returns all records when there is a match in either left or right table. If there is no match, the result is NULL from the side where there is no match.

7. Explain the concept of normalization. 

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller tables and defining relationships between them. Common forms of normalization are 1NF, 2NF, 3NF, and BCNF.

8. What is indexing? How does it improve query performance? 

Indexing involves creating a data structure (index) that improves the speed of data retrieval operations on a database table. Indexes provide a quick lookup for matching rows in a table based on indexed columns, thus reducing the time required for data access.

9. Write a query to find the second highest salary from an Employee table.

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

10. What is a primary key and a foreign key?
A primary key is a column (or a combination of columns) that uniquely identifies each row in a table.
A foreign key is a column (or a combination of columns) that establishes a relationship between two 

tables by referencing the primary key of another table.

11. Explain the difference between DELETE and TRUNCATE.

DELETE is a DML command used to delete specific rows from a table based on a condition.

 It can be rolled back.

TRUNCATE is a DDL command used to remove all rows from a table, resetting it to an empty state. 

It cannot be rolled back and is faster than DELETE.

12. What is a subquery? Provide an example.
A subquery is a query within another query. It is used to return data that will be used in the main query 

as a condition.

SELECT employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments
WHERE department_name = 'Sales');

13. How do you handle transactions in SQL?
Transactions are handled using the following commands:
BEGIN TRANSACTION: Starts a transaction.
COMMIT: Saves all changes made during the transaction.
ROLLBACK: Reverts all changes made during the transaction.
14. What are stored procedures and functions?
A stored procedure is a set of SQL statements that can be executed as a single unit. It can perform 
actions such as data modification and return multiple values.
A function is a set of SQL statements that perform a specific task and return a single value. 
Functions are typically used for calculations and data manipulation.

15. Explain the concept of ACID properties in databases.
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable
 processing of database transactions:
Atomicity: Ensures that all operations within a transaction are completed; if not, the transaction
 is aborted.
Consistency: Ensures that the database remains in a consistent state before and after a transaction.
Isolation: Ensures that transactions are isolated from each other, preventing concurrent 
transactions from interfering with each other.
Durability: Ensures that once a transaction is committed, it remains permanent, even in the case 
of a system failure.
16. What is the difference between a VIEW and a TABLE?
A TABLE is a database object that stores data in rows and columns.
A VIEW is a virtual table based on the result set of a SQL query. It does not store data 
physically but provides a way to simplify complex queries and enhance security by restricting access 
to specific data.
17. How do you optimize SQL queries?
Use indexes to speed up data retrieval.
Avoid using SELECT * and only select required columns.
Use JOINs instead of subqueries when possible.
Write efficient WHERE clauses to filter data early.
Analyze query execution plans to identify performance bottlenecks.
18. What is the difference between DISTINCT and GROUP BY?
DISTINCT is used to remove duplicate rows from a result set.
GROUP BY is used to group rows that have the same values in specified columns and perform
 aggregate functions (e.g., COUNT, SUM) on them.

19. Explain the concept of database normalization.
Normalization is the process of organizing data in a database to reduce redundancy and improve 
data integrity. It involves dividing large tables into smaller tables and defining relationships between 
them. Common forms of normalization are 1NF, 2NF, 3NF, and BCNF.

20. What is the purpose of the ORDER BY clause?
The ORDER BY clause is used to sort the result set of a query by one or more columns. 
It can be used to sort data in ascending (ASC) or descending (DESC) order.

BEST LUCK !!!

SQL Server interview questions

Are you planning for your SQL interview ? Want to crack your SQL Interview ? then go for the SQL Server interview questions book, 

        This book gives you a complete idea about the SQL database. It starts with a very basic concept like what is a database, its usage, types, creation, and data storage, security, sorting, and searching for a stored procedure. This book is a complete set of interview breaking questions and answers with live examples and plenty of screenshots. This book takes you on a journey to mastering the SQL database, including SQL datatypes, functions, triggers, and stored procedures. This book also covers the latest and new features of SQL 2016, 2017 and 2019 CTP with examples.

In the beginner section, we start with very basic concepts like what is a database, why to use a database, different types of database types, what is SQL, its usages, advantage and disadvantages, SQL datatypes, its different operators and how to use them with samples.
In the intermediate section, we will learn about the different SQL functions, SQL Joins (used to fetch values from multiple SQL tables) and SQL DDL, DCL, and DTL commands.
(About the last chapters) This is the advanced section of the book where we have provided an explanation of the SQL stored procedure, triggers, and SQL view concepts, additionally, we have covered SQL core concepts like keys, indexes, injections and constraints. We have also introduced cutting-edge concepts like SSRS, SSIS, SQL Cloud database (Azure), JSON Support and a list of the new features of SQL 2016, 2017, CTP-2019 with SQL performance improvement tips. Finally, we have ended the book with a series of random SQL questions and answers.

TAGLINE 
Let us break the SQL interview with the help of SQL Server interview questions.

KEY FEATURES  

• Database Basic Concepts

• SQL Fundamentals

• DDMS, SQL Statements, and Clauses

• SQL Operators, Datatypes, and Keywords

• SQL Functions, Wildcards and Dates

• SQL Joins and CASE Statement

• SQL DDL, DCL, and DTL Statements

• SQL Stored procedures, Triggers, Views, and Transactions

• SQL Keys, Indexes, Injection, and Constraints

• SSRS, SSIS, SQL Cloud database (Azure), and JSON Support

• New features of SQL 2016, 2017, and 2019

• SQL Performance Improvement Tips

• Fuzzy Interview Questions and Answers

WHAT WILL YOU LEARN  
After reading this book, you will be able to understand SQL database concepts, handle core database activities like data security, searching, migration, and sorting. You will be able to handle the database transactions, use different SQL datatypes, functions, triggers, and stored procedures to save and retrieve data from the database. You will also be able to understand advanced SQL concepts like SQL reporting services, integration services, cloud database and new features from the latest SQL versions like 2016, 2017, and 2019.

WHO THIS BOOK IS FOR 
This book is built in such a way that it is useful for all categories such as technical or non-technical readers. This book is perfect. If you are a fresher and you want to learn about SQL, or if you are a teacher and you want to spread SQL knowledge, this book is very helpful. If you want to crack the database interview or if you are working as a DBA and you want to upgrade your knowledge, or if you are backend developer, database tester, performance , or if your role is that of a database admin, SQL developer, data analyst, mobile app developer or if you are working on core SQL concepts, this book is just right for you.
This book is very useful as it contains many simple real-time scenarios for each concept. All functionalities are explained with real SQL screenshots and database records.

CONTENTS
1. Database and SQL Basics
2. DDMS SQL Statements and Clauses
3. SQL Operators, Keywords, and Datatypes
4. SQL Operators
5. SQL Functions, Wildcards, and Dates
6. SQL Joins and CASE Statement
7. SQL DDL, DCL, and DTL Statements
8. SQL Stored Procedures, Triggers, Views, and Transactions
9. SQL Keys, Indexes, Injections, and Constraints
10. SSRS, SSIS, SQL Cloud database (Azure), and JSON Support
11. New features of SQL 2016, 2017, and 2019
12. SQL Performance Improvement Tips and Fuzzy Interview Questions

https://bpbonline.com/collections/new-relases/products/sql-interview-questions-a-complete-question-bank-to-crack-your-ann-sql-interview-with-real-time-examples

Thanks
Prasad

Find out who Delete your data from SQL table

Find out who Delete your data from SQL table

Have you lost your data from SQL table ? Have you lost your SQL table ? want to know who ran DELETE or DROP Query on your database ? Then go through this step by step article, it will help you to find the culprit

Introduction

Last month my friend has called me and said, some has delete his important data from SQL table and now no one confess it. He asked me to search a way to find culprit. After couple of here and there i got a way and now want to share with you. So, let's enjoy this article.

This article will help you find the user who fire DELETE or DROP statement on your table or database

Things we need

To search culprit, we need to read transaction log entries of database. Yes...You heard is right, you can read SQL transaction log data (i.e. LDF file).  let's begin with the steps

1.  We will create some sample table with data
2. Delete rows from it
3. Try to track the user who delete (soft or hard) data entries (Here Soft Delete means delete records using Query and Hard delete indicates delete data using 'DEL' button (or may be with mouse) from SQL table directly)

LDF :
         (Those who don't know what is LDF) LDF is a file extension for log data files these files are exist with MDF files (which contains actual data). LDF file store all transactions with time stamp and help to recover database in case of data loss.

         Now, to read LDF file we need to use 'fn_dblog' function, (which is undocumented function of SQL), after executing this function on particular database you will able to see live transaction logs and operations executed on that database.

Let's create sample database and table, with the help of following Query

CREATE DATABASE [Sample] ON  PRIMARY
( NAME = N'Sample_dat', FILENAME = N'D:\Sample\Sample.mdf' , SIZE = 13760KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10%)
 LOG ON
( NAME = N'Sample_log', FILENAME = N'D:\Sample\Sample.ldf' , SIZE = 9216KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10%)
GO

sample table:

USE [Sample]
GO
CREATE TABLE [Emp] (
    [No] INT ,
    [Name] VARCHAR (50),
    [Address] VARCHAR (50)
);

Now let's check what has been recorded in LDF logs

run 'fn_dblog' function in Sample database

select * from fn_dblog(null,null)

If you see above result pane, there are almost 35 rows are recorded for just CREATE DATABASE and CREATE TABLE script

Let's insert some rows in table

Insert into Emp values(1,'name1', 'address1')
Insert into Emp values(2,'name2', 'address2')
Insert into Emp values(3,'name3', 'address3')
Insert into Emp values(4,'name4', 'address4') 

Track DELETE Activity

Now, just go and delete all rows from database, use below simple query

Delete from Emp

Our Emp table is now empty as, we have delete all the queries

Let's examine the log table, having operation type is 'LOP_DELETE_ROWS', fire fn_dblob function again and see what you get

select * from fn_dblog(null,null) where Operation = 'LOP_DELETE_ROWS'

Result:



Above result pane show us, all the transaction rows which are having 'DELETE' entries on specific database table, you need to search for the your 'specific' table (from where you have lost your data), check out column 'AllocUnitName', this column contains your table name on which 'DELETE' statement has fired.

In our case, Table name is 'Emp', now get the transaction ID for that particular 'table' entry record, execute below query to get record of particular table

select Operation, [Transaction ID], AllocUnitName,  * from fn_dblog(null,null) 
where Operation = 'LOP_DELETE_ROWS' and allocUnitName = 'dbo.emp'

Result:



in our case, transaction ID is same, as all entries are deleted with single 'DELETE' statement (at once) (e.g.  0000:0000079f)

with the help of above transaction ID, we will find when our entries are deleted from database. for that purpose we need to search record with operation LOP_BEGIN_XACT, fire below query on database

select  [Operation], [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
 
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:0000079f' AND [Operation] = 'LOP_BEGIN_XACT'

above query will give you Start time of the transaction

Now we got the exact time when someone fire DELETE query on database, to know the activity End Time, you can try below query

SELECT
   [Begin Time], [End Time]
FROM
    fn_dblog(NULL, NULL)
WHERE
[transaction id] = '0000:000007a1' and [Operation] = 'LOP_BEGIN_XACT' or [operation] = 'LOP_COMMIT_XACT'

Here is the result of above query



Now let's find who is the culprit, we will find the real database user who fire delete query

Transaction SID column contains encrypted Hexa decimal text which is nothing but the user name who fire 'Delete' query

Fire below query to get [Transaction SID] column with the help of Transaction ID and Operation = 'DELETE'

select  [Operation], [Transaction Name], [Transaction SID]
FROM fn_dblog(NULL, NULL) where [Transaction ID] = '0000:000007a1' and [Transaction Name]='DELETE'

Output of above query is



Just you need to copy, encrypted hexadecimal contents from [Transaction SID] column and execute below query on master database, as per our result my Hexadecimal string is 0x01

SELECT SUSER_SNAME(0x01)

**SUSER_SNAME is the inbuilt function, it just checks security identification number (SID) and back with the login name associated it.

when i run above query i got below output



Yes...we finally got real culprit who fire Delete query

Track DROP activity

Similarly, if anyone DROP your table from database we can track that activity by using following queries,
Let's Drop table with below simple query

Drop table Emp

Now track activity using Transaction Name 'DROPOBJ'
check below query

SELECT [Transaction Name], Operation, [Transaction Id], [Transaction SID],  [Begin Time] 
FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ'

i got below result


Same as DELETE scenario execute below query on master database, as per our result Hexadecimal string is 0x01

SELECT SUSER_SNAME(0x01)

which is the same user 'sa'

So, to conclude

SQL store all its transactions in log table, we can read transaction log file using fn_dblob function, we can do more research on each transaction with the help of this function.  All transactions are logged with different operations,  With the help of SUSER_SNAME function we can easily trace out encrypted user name.

In my next article i will cover deep dive points on 'Reading Transaction Log of SQL (LDF)', so Please stay tuned

and Enjoy this article
**DO NOT alter enties of fn_dblog or DO NOT run these command on production unless you have backup.

*Suggestions and comments are always welcome

-Happy Tracing
Koolprasadd

FIX : A network-related or instance-specific error occurred while establishing a connection to SQL

In this article i explained you to Fix SQL error occurred while establishing a connection to remote computer, so we can say we can resolve error "Network-related or instance-specific error occurred while establishing a connection to SQL Server [2005/2008/2012]"

Background

Couple of years back when i was not much familier with SQL, i try to connect to my SQL Server from my client. I put servername and try to login with SQL Server authentication with user name and password and i got error "A network-related or instance-specific error occurred while establishing a connection to SQL Server...", after spending couple of hours on it i was able to get rid of it, this article is for those who got same error and still they are put their head in SQL to resolve the issue.

Introduction

Many times we are trying to connect to SQL server with SQL management studio either by express version or by enterprise version and we got following error
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) "
Above error is really headache for sql users.
Here is the snap of error

Fix it

To fix the issue we might need to do some configuration changes in SQL management studio (Here i use SQL 2008 for it)

Allow remote connections 
The very first thing you want to check is, your Remote Connections are enabled on your SQL Server database, to check it go through below steps
1. Select SQL server on object explorer -> Right click and click on properties -> select 'connections' -> under Remote server connections sections -> check 'Allow Remote connections to this server'
see below snap

This is the basic and simple check it may resolve your error, if not then continue with this article

Enable TCP/IP protocol
Next step is the enable the TCP/IP, just navigate to 'the SQL Server Configuration Manager' -> in left pane Open 'SQL server network configuration' -> select protocol for SQLEXPRESS in right pane select TCP/IP and make it 'enable'
see below snap

This is one of the helpful step and resolve many users issue, if the problem still continues then continue with this article

Handle Firewall port
Many times your firewall security settings does not allow SQL to pass through, you need to handle that firewall port to work with SQL stuff. To allow in firewall there need to configure couple of settings 
1. set 1433 port in TCP/IP proerpties
2. set Inbound and Outbound rules in windows firewall settings (Applicable for window 7 and above operating system)

set TCP port 1433 in configuration window as below snap shows

Now to set Inbound and Outbound rule we need to navigate to windows firewall, Just Open the Control Panel and navigate to Windows Firewall.
Click on Advanced Settings on the left hand side and you should see the Windows Firewall with Advanced Security. Select the Inboud Rules on the left hand side and click on New Rule… on the right hand side.
go through the following snap sequence it will help you to enable and set the windows firewall settings
Open 'windows firewall with advanced security' screen, select 'Inbound Rule' and click on 'New Rule', see below snap

New Inbound wizard screen' will open, Now in left hand side of the window select 'Rule Types', in right hand side select 'Port' now click 'Next'

In protocol and ports section select 'specific local ports' and give port number as '1433' (default sql port), now click 'Next'

In action section, select 'Allow the connection', and click on Next

In Name section give name and description of the rule, which help us to identify the rule in firewall, now click on Finish

Yes, we are almost done with the settings now close SQL server and restart it again and try to connect, it will resolve your issue.

Summing up

Above error is very basic Many time occur due to different conditions, if you have database installed on SQL 2008 and if you try to connect it with SQL 2005 management console then also same error occurred, when you have install a new instance of SQL server on machine then also this error arise, above are some settings that help you to get rid of the error

Suggestion and Queries most welcome

Thanks

Prasad

New features of SQL 2016

 

Introduction

SQL Server 2016 was (finally) released on June 1^st, 2016 with an initial build number of 13.0.1601.5. Microsoft build SQL 2016 keeps a lot of things in mind like Cloud first, Security enhancement, JSON support, Temporal database support, Row level security, Windows server 2016 connectivity, Non-relational database connectivity (e.g. Hadoop), rich visual effects, etc.
In this article, we will take a walk-through all fresh SQL 2016 features and cover them one by one.

Always Encrypted

 

 As the word suggests, 'Always Encrypted' feature of SQL 2016 'Always' keeps your sensitive data 'Encrypted' either at rest (local environment) or at remote (Cloud/Azure). It will help to protect data from people who may play around it like DBAs, Cloud operators, high-privileged but unauthorized users.

 

How It Works

You can set Always Encrypted to individual column (where your sensitive data resides). While configuring columns, you need to specify encryption algorithm and cryptographic keys for data protection. There are basically two keys you need to define:

1. Encryption Key for column data encryption (It will be used to encrypt data for specific column)
2. Master Key: for Encryption of column encryption keys

So basically, it's a double encryption protection, only program can access it, client application will automatically encrypt and decrypt data before fetching data from database

JSON Support

 

 

SQL 2016 gives direct support to JSON (Java Script Object Notation), SQL has the facility to read JSON format data, load them in table, support index properties in JSON columns.
JSON data will be stored in NVARCHAR type. Due to NVARCHAR type, an application has the following benefits:

• Already stored JSON data (as text) can be easily migrated on new feature.
• As NVARCHAR is supported by all SQL components so is the JSON too.

You can easily fetch data FOR JSON from SQL with the below syntax:

SELECT column, expression, column as alias
FROM table1, table2, table3
FOR JSON [AUTO | PATH]

It is a SELECT command so when we fire the above query, SQL will format each row/cell value and return as JSON object.
SQL has also provided in-built functions for JSON.

Dynamic Data Masking

 

 

This is again one of the security features of SQL 2016. As the name suggests, it just wraps MASK on your data, in short, it hides your confidential data that you don't want to display. It just avoids disclosure of your sensitive data.
After masking, SQL User with limited rights will not view original text, he can view only Masked Text, SQL has pre-defined masking functions you just need to apply it on different columns, see below:

Sr No	Functions	Applied on	Plain text (Input)	Masking text(output)
1	Default	String, Number	ABCD	xxxx
2	Email	Email text	test@test.com	txxx@xxxx.com
3	Random	Numbers	1234	684
4	Custom String	Text	RABBIT	RXXXX

To apply this on specific columns, you just need to ALTER column with 'MASKED WITH' function name, see below syntax:

//here I used function as Default(), you can change it to any of the above types
ALTER TABLE tablename ALTER COLUMN columnname MASKED WITH (FUNCTION=‚default()‘)

Row Level Security  

This is again one of the security features of SQL 2016. It allows you to secure your data row wise, in short you can define a row, that will be viewed by a particular SQL user only. So depending upon the SQL user access permission, we can restrict row level data, e.g., we can ensure if employees can view only their department data though department table is the same.
To implement Row level security, you need to define Security policy with a predicate and function.
Security policy:
We need to create a policy for security, here is simple syntax:

CREATE SECURITY POLICY fn_security ADD [FILTER | BLOCK] PREDICATE FunctionName ON TableName

In the above syntax, FILTER and BLOCK are the predicates that will either FILTER rows and display only those that are available for read or BLOCK rows for write operation.

Function: Function is a simple user defined function, but here are some restrictions for user defined function that are used in Row Level Security syntax:

• Database modification operations are not allowed
• OUTPUT INTO clause is not allowed in function
• Multiple result set should not be returned from function 

Stretch Database

As the name suggests, it gives flexibility to the user. In short, we can store portion of database to remote (Here, we can say cloud/Azure). The portion of data can be called as COLD DATA. (It is useful for those where transactional data needs to be keep for long time as industry requirement.) So we can say it's a cost-effective solution for COLD data storage, your data is available anytime for query and manage. You can access your data without changing queries either it is present on local or at stretch database.
To configure it, you need an Azure account and database instance that you need to stretch. The following snap will clear your idea.

 

Multiple TempDB

It is always a good practice to have a Multiple Temp data files, if you are working on a big crucial data, up till now (SQL 2014), you need to manually add temp db files to your database but SQL 2016 provides you temp DB configuration settings, in which you can configure Number of TempDB files at the time of SQL installation. Default number of files are 8 with default size of 64 MB will be given.
So you no longer need to configure/create it manually.

Query Store

 

 

Up till now, to check Query plan and execution statistics, we need dynamic management views in SQL but neither will it give you Query plan that executed by past/old queries nor will it store them anywhere so that you can review, but SQL 2016 provides you 'Query Store' that takes you through query plan, statistics and Query execution for current and past queries.
To enable it, just right click on database (obviously, you need SQL 2016 SSMS), go to properties. You will see 'Query store' at the left corner, select it and click on Enable 'true' or you can do it using Query as follows:

ALTER DATABASE [Database1] SET QUERY_STORE = ON

Temporal Table

Do you want to store history of your SQL table? So you want to review your old records after table updation? Then you can go with this features. SQL 2016 provides record version facility in which it keeps a history of changed record and maintains it for timely analysis. This feature can be useful for Audit, checking data trend, accidental update/delete data and many more.

How It Works

Basically, the system keeps pair of a table for history and adds two additional columns in it named 'SysStartTime' and 'SysEndTime' for start time and end time for row respectively. Live table contains current record of row, whereas history table contains previous record of row. We can fetch data from History table, with the following query:

SELECT * FROM table1 FOR SYSTEM_TIME   
BETWEEN date1 AND date2
WHERE condition; 

R Introduction

Have you stored statistical data in SQL? Want to use R to analyze it? You export data each time from SQL to R? Then your headache will now be covered in SQL 2016, because it is now with R. You can run R script on SQL. You need to install this feature at the time of SQL setup.

 

Reference

https://www.microsoft.com/en-in/server-cloud/products/sql-server/

We cannot cover all features in details, maybe I will be planning soon to cover them one by one.
Till then, you can enjoy this article.
Suggestion and queries are always welcome.

Happy querying!

**This article is already published at CodeProject

Tricky SQL Server Interview Question and answers

Are you preparing for a SQL server interview? want some tricky questions and answers that will give you break through ? Then you are at the correct place. Here i have some bunch of tricky SQL Server interview questions and answers that will really help you to go through the interview

1.With is the Sixth normal form in SQL server? - Ohhh...You may think that, i have heard about first to forth only but what is this SIXTH? Friends, do not try to impress the interviewer just skip this question.
Sixth normal form does exist but use it when you want a relational system in conjunction with time. At this moment SQL Server does not support it directly.

2. In which Files does SQL Server Actually Store Data? - SQL server has 2 data files associated with it
1. MDF : Which is actual data file storage
2. LDF : (Log data files) which stores transaction log

3. Do you know How many locks are exist in SQL Server ? - We have these many locks exist here, see below
1. Intent
2. Shared
3. Update
4. Exclusive
5. Schema
6. Bulk Update

4.What is SQL Profiler?- It is a tool that allows administrator to monitor different events and transaction of SQL server instance, You can capture and save data about each event to a file or SQL Server table to analyze later
it is not available with EXPRESS edition

5. Can i insert into a table having just one IDENTITY column?- Yes you can, Use following Query

INSERT INTO TABLE1 DEFAULT VALUES;

6. How to drop primary key from a column using Query
- See below query to drop primary key constraint

ALTER TABLE Table1 DROP CONSTRAINT PK_Table1_Col1

7. What is the difference between a DDL trigger and a DML trigger?- DDL commands are always deal with table structures, these command can update/modify table structure (CREATE, ALTER, DROP)
DMS commands are always deal with data/records not with table structure (INSERT, UPDATE, DELETE)

8. What the difference between UNION and UNIONALL?
- Union will remove the duplicate rows from the result set while UNIONALL does not remove them. So to avoid data redundancy you need to use UNION

9. What is the clustered and a non-clustered index?
- Clustered index is the physical index that reorders the way records in the table are physically stored, In non-clustered index logical order of the index does not match the physical stored order of the rows on disk

10. What's the difference between a primary key and a unique key?
- Both keys are not allow duplicate entries but primary key create a cluster index where as unique key create a non clustered index. Secondly primary key doesn't allow NULLs, but unique key allows one NULL only.
11. How to change the data type of a column- The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Here is the query to change column datataype
ALTER TABLE table_name ALTER COLUMN column_name data type

12. How to check the version of SQL server and operating system?
- With the help of the following query we can get it
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').

13. What is CHECK Constraint?
- CHECK Constraint used to limit the values that can be placed in a column

14. How can you delete duplicate records in a table where there is no primary key?
- You can take Use of the SET ROWCOUNT command. Here if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.
15. What is NOT NULL Constraint?- This constraint force the column to not accept NULL values

16. How to copy data from one table to another table?
- With the help of 'INSERT INTO SELECT' or 'SELECT INTO' queries you can copy data from a datatable to another

17. What is PIVOT
- To automatically sort, count, and total the data stored in one table we use PIVOT. It will also rotate table as rows to columns and vice versa

18. What are DBCC commands?
- These are the Database Consistency Checker commands; They will Check disk allocation consistency, Display information about recent transactions.
19. Find the 3rd MAX salary in the emp table

Select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

20. What is the difference between Trigger and Stored Procedure?- Triggers cannot be called directly they need to associated with different quires

Finisher
Suggestion/Queries always welcome

Happy Interview
- Prasad