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.
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.