If you are a SQL server database developer and planning to change job or need to know about common SQL server interview questions, here I am going to list them with answers.
- Which TCP/IP port does SQL Server run on?
- What are the two authentication modes in SQL Server?
- What is difference between primary key and a unique key?
- What are user defined datatypes and when you should use them?
- Define candidate key, alternate key, composite key.
- What are defaults? Is there a column to which a default can’t be bound?
- What is the difference between ROW_NUMBER, RANK, DENSE_RANK and NTILE?
- Can we use HAVING clause without GROUP BY clause?
- Name all Isolation level available in SQL Server and define them?
- Which is the Default Isolation in SQL server?
- What is NOLOCK in SQL Server and When will you use this?
- How will you identify the duplicate records in the table and how will you remove it?
- What is the difference between clustered and non-clustered index?
- What is OLTP?
- What are the different backups available in SQL Server?
- What is Mirroring in SQL Server?
Which TCP/IP port does SQL Server run on?
By default SQL Server runs on port 1433.
What are the two authentication modes in SQL Server?
There are two authentication modes –
- Windows Mode: doesn't Requires Username/password
- Mixed Mode: Requires Username/password
What is difference between primary key and a unique key?
- By default primary key creates a clustered index on the column and data is organised in sequence, where as unique key creates a non-clustered index by default
- Primary key doesn’t allow NULLs, while unique key allows one NULL only.
- We can have only one primary key in table, while there can be multiple unique keys in table.
- Primary Key is used to Identify a unique value in table, while unique key is used to prevent duplicate value in table.
What are user defined datatypes and when you should use them?
User-defined data types are based on the system data types in Microsoft SQL Server.
User-defined data types can be used when several tables must store the same type of data in a column and you must ensure that these columns have exactly the same data type, length, and NULLability.
Example, to create a user defined datatype, you can run a below query:
Create Type CountryCode
from varchar(3) NOT NULL Define candidate key, alternate key, composite key.
Candidate key: Candidate keys are those keys which is candidate for primary key of a table. In simple words we can understand that such type of keys which full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key.
Alternate key: If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
Composite Key:When we create keys on more than one column then that key is known as composite key.
What are defaults? Is there a column to which a default can’t be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can’t have defaults bound to them.
What is the difference between ROW_NUMBER, RANK, DENSE_RANK and NTILE?
ROW_NUMBER: Returns the sequence and unique number for each group based on the fields applied in PARTITION BY clause. If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.
RANK: Similar to ROW_NUMBER function and Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
NTILE: Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Can we use HAVING clause without GROUP BY clause?
Yes. We can use HAVING clause without GROUP BY clause, but aggregate function is needed.
Name all Isolation level available in SQL Server and define them?
There are 5 different Isolation Level available in SQL Server:
- Read Committed: Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.
- Read Uncommitted: Specifies that statements can read rows that have been modified by other transactions but not yet committed.
- Repetable Read: Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
- Snapshot:Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.
- Serializable: Statements cannot read data that has been modified but not yet committed by other transactions.It is highest isolation level based on pessimistic concurrency control where transactions are completely isolated from one another.
Which is the Default Isolation in SQL server?
Read Committed is the default isolation level in SQL Server.
What is NOLOCK in SQL Server and When will you use this?
NOLOCK is also called as "Dirty Read" and it is equivalent of using Read Uncommitted as a transaction isolation level.
We can read the data that have been modified by another transaction but not committed. We can use this object when the table is accessed by continuous in SELECT Query.
How will you identify the duplicate records in the table and how will you remove it?
We can identify the duplicate records by using ROW_NUMBER() with PARTITION BY clause. We can remove duplicate giving a sequence for duplicating data and removed the data by sequence id from the original data.
What is the difference between clustered and non-clustered index?
A clustered index is used to define the order or to sort the table or arrange the data by alphabetical order just like a dictionary and it is faster
A non-clustered index collects the data at one place and records at another place and it is slower than clustered index.
What is OLTP?
OLTP means Online Transaction Processing which follows rules of data normalization to ensure data integrity. Using these rules, complex information is broken down into a most simple structure.
What are the different backups available in SQL Server?
Microsoft SQL Server allows three basic types of SQL Server backup:
- Full backup: A full backup is a backup containing all the data from the database in question, such as file sets and file groups, as well as logs to ensure data recovery.
- Differential backup: To clarify, a differential backup is a backup type based on the most recent full data backup of a partial database or data files or filegroups (the base copy for the differential copying).
- Transaction log backup: A transaction log backup is a backup of transaction logs. It includes all log entries that were absent in the previous transaction log backup .
What is Mirroring in SQL Server?
Database mirroring is the process of creating and managing multiple copies of a database for the purpose of database backup, recovery and/or performance optimization.
It is a type of data backup and business continuity process that utilizes mirrored instances to enhance database availability and provide a database failover mechanism.
You may also like to read:
SQL Multiple Choice Questions and answers
Entity Framework Core Interview Questions with Answers.
ASP.NET MVC Interview Questions and Answers
OOPS interview questions in C# (With Answers)
