Below is top 100 SQL Server interview question and answers with examples for freshers and intermediate developers.
What is JOIN in SQL Server?
Joins are used to retrieve data from two or more than two tables based on some related column between them.
Different types are join are shown below.
INNER JOIN (also called as JOIN): Returns matching record from both the tables.
LEFT OUTER JOIN (also called as LEFT JOIN): Returns all the record from the Left table and matching record from Right tables and non-matching record from Right table will be displayed as NULL.
RIGHT OUTER JOIN (also called as RIGHT JOIN): Returns all the record from the Right table and matching record from Left tables and non-matching record from Left table will be displayed as NULL.
FULL OUTER JOIN (also called as FULL JOIN): Returns all the rows from Left table and all the rows from Right table and whenever join condition is not met, NULL will be displayed for those fields.
What is SELF JOIN in SQL Server?
Self join is not a any type of Join. It is basically joining the table with itself.
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; --T1 and T2 are different table aliases for the same table.
What is CROSS JOIN in SQL Server?
Cross Joins produce results that consist of every combination of rows from two or more tables. That means if table A has 3 rows and table B has 2 rows, a CROSS JOIN will result in 6 rows.
What is difference between CROSS JOIN and FULL OUTER JOIN in SQL Server?
CROSS JOIN
A cross join produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no on clause because you're just joining everything to everything.
FULL OUTER JOIN
A full outer join is a combination of a left outer and right outer join. It returns all rows in both tables that match the query's where clause, and in cases where the on condition can't be satisfied for those rows it puts null values in for the unpopulated fields.
What is APPLY Operator in SQL Server?
The APPLY operator allows us to invoke a table-valued function for each row returned by an outer table expression of a query. The APPLY operator allows us to join two table expressions; the right table expression is processed every time for each row from the left table expression. Left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
There are two types of Apply Operator i.e. CROSS APPLY and OUTER APPLY.
CROSS APPLY:CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. It other words, result of CROSS APPLY doesn’t contain any row of left side table expression for which no result is obtained from right side table expression. CROSS APPLY work as a row by row INNER JOIN.
OUTER APPLY:OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function. OUTER APPLY work as LEFT OUTER JOIN.
What is difference between CHAR and VARCHAR data type?
| CHAR Data Type | VARCHAR Data Type |
|---|---|
| It is a fixed length data type. | It is a fixed length data type. |
| Used to store non-Unicode characters. | Used to store non-Unicode characters. |
| Occupies 1 byte for each character. | Occupies 1 byte for each character. |
| For example, if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example, as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column. | For example, if you declare a variable/column of VARCHAR (10) data type, it will take the number of bytes equal to the number of characters stored in this column. So, in this variable/column, if you are storing only one character, then it will take only one byte and if we are storing 10 characters, then it will take 10 bytes. And in this example, as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column. |
|
|
What is difference between NCHAR and NVARCHAR data type?
| NCHAR Data Type | NVARCHAR Data Type |
|---|---|
| It is a fixed length data type. It is similar to CHAR data type. | It is a fixed length data type. It is similar to VARCHAR data type. |
| Used to store Unicode characters (for e.g. Arabic, German and so on) and non-Unicode characters. | Used to store Unicode characters (for e.g. Arabic, German and so on) and non-Unicode characters. |
| Occupies 2 byte for each character. | Occupies 2 byte for each character. |
What is difference between LEN and DATALENGTH function?
| LEN | DATALENGTH |
|---|---|
| LEN function returns no. of characters specified in a variable/column. | LEN function returns no. of bytes used by a variable/column. |
|
|
| LEN function excludes trailing spaces. It does not count spaces from right side. | DATALENGTH function includes trailing spaces. It does count spaces from both side. |
|
|
| LEN function does not support TEXT, NTEXT AND IMAGE data types. | DATALENGTH function does support TEXT, NTEXT AND IMAGE data types. |
What is a Trigger in SQL Server?
A Trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs.
What are the two types of Triggers in SQL Server?
After Triggers: Fired after Insert, Update and Delete operations on a table.
Instead of Triggers: Fired instead of Insert, Update and Delete operations on a table.
Read more about triggers in sql server.
What are the special tables used by Triggers in SQL Server?
Triggers make use of two special tables called inserted and deleted. The inserted table contains the data referenced in an INSERT before it is actually committed to the database. The deleted table contains the data in the underlying table referenced in a DELETE before it is actually removed from the database. When an UPDATE is issued both tables are used. More specifically, the new data referenced in the UPDATE statement is contained in inserted table and the data that is being updated is contained in deleted table.
What is ACID properties?
ACID properties are shown below.
- Atomicity: It is an all-or-none proposition(task).
- Consistency: It guarantees that your database is never left by a transaction in a half-finished state.
- Isolation: It keeps transactions separated from each other until they're finished.
- Durability: It ensures that the database keeps a track of pending changes in a way that the server can recover from an abnormal termination.
What is difference between TRUNCATE and DELETE commands?
- Identity value is re-seeds when TRUNCATE is fired, whereas in case of DELETE, it doesn't.
- Truncate removes all records and doesn't support triggers where as DELETE support triggers.
- Truncate is faster compared to delete as it makes less use of the transaction log.
- Truncate is not possible when a table is referenced by a Foreign Key or tables are used in replication or with indexed views.
- TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion. In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE.
What is SQL Profiler?
SQL Server provides a graphical tool which helps system administrators to monitor T-SQL statements of Database Engine.
SQL Profiler can capture and store data related to every event to a file or a table.
SQL Server Profiler can be used:
- To create a trace.
- To store the trace results in a table.
- To watch the trace results when the trace runs.
- To replay the trace results.
- To start, stop, pause, and modify the trace results
What is a Function in SQL Server?
Function: A function is a database object in SQL Server. Basically, it is a set of SQL statements that accept only input parameters, perform actions and return the result. A function can return an only a single value or a table.
What are the properties of Functions in SQL Server?
- Unlike Stored Procedure, Function returns only single value.
- Unlike Stored Procedure, Function accepts only input parameters.
- Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).
- Like Stored Procedure, Function can be nested up to 32 level.
- User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.
- User Defined Function can't returns XML Data Type.
- User Defined Function doesn't support Exception handling.
- User Defined Function can call only Extended Stored Procedure.
- User Defined Function doesn't support set options like set ROWCOUNT etc.
What are the various types of Function available in SQL Server?
There are two types of function available in SQL Server as shown below.
1) User Defined function: User defined functions are create by a user.
2) System Defined Function: System functions are built in database functions. Below image shows the system defined functions.
How to create Function in SQL Server?
We can create two types of function (user defined) in SQL Server.
1) Table Valued Functions
2) Scalar Functions
What are the various system defined functions available in SQL Server?
Various system defined functions are shown below.
| String Functions | Numeric/Math Functions | Date/Time Functions | Advanced Functions |
|---|---|---|---|
| ASCII | ABS | CURRENT_TIMESTAMP | CASE |
| CHAR | AVG | DATEADD | COALESCE |
| CHARINDEX | CEILING | DATEDIFF | CURRENT_USER |
| CONCAT | COUNT | DATENAME | ISDATE |
| Concat with + | FLOOR | DATEPART | ISNULL |
| DATALENGTH | MAX | DAY | ISNUMERIC |
| LEFT | MIN | GETDATE | LAG |
| LEN | RAND | GETUTCDATE | LEAD |
| LOWER | ROUND | MONTH | NULLIF |
| LTRIM | SIGN | YEAR | SESSION_USER |
| NCHAR | SUM | Conversion Functions | SESSIONPROPERTY |
| PATINDEX | Configuration Functions | CAST | SYSTEM_USER |
| REPLACE | @@Version | CONVERT | USER_NA |
| RIGHT | TRY_CAST | ||
| RTRIM | TRY_CONVERT | ||
| SPACE | |||
| STR | |||
| STUFF | |||
| SUBSTRING | |||
| UPPER |
What is CURSOR in SQL Server?
A cursor is a database object which is used to retrieve data from a result set one row at a time.The cursor can be used when the data needs to be updated row by row.
In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with one row at a time rather than the entire result set at once. This can be done using cursors.
In programming, we use a loop like FOR or WHILE to iterate through one item at a time, the cursor follows the same approach and might be preferred because it follows the same logic.
Steps involved in CURSOR:
Declare Cursor: A cursor is declared by defining the SQL statement that returns a result set.
Open: A Cursor is opened and populated by executing the SQL statement defined by the cursor.
Fetch: When the cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
Close: After data manipulation, we should close the cursor explicitly.
Deallocate: Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.
DECLARE @emp_id int ,@emp_name varchar(20), @message varchar(max); PRINT '-------- EMPLOYEE DETAILS --------'; DECLARE emp_cursor CURSOR FOR SELECT emp_id,emp_name FROM Employee order by emp_id; OPEN emp_cursor FETCH NEXT FROM emp_cursor INTO @emp_id,@emp_name print 'Employee_ID Employee_Name' WHILE @@FETCH_STATUS = 0 BEGIN print ' ' + CAST(@emp_id as varchar(10)) +' '+ cast(@emp_name as varchar(20)) FETCH NEXT FROM emp_cursor INTO @emp_id,@emp_name END CLOSE emp_cursor; DEALLOCATE emp_cursor;
What is Constraint in SQL Server?
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.
Below are the common constraints available.
- NOT NULL: Ensures that a column cannot have a NULL value.
- UNIQUE: Ensures that all values in a column are different
- PRIMARY KEY: A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table.
- FOREIGN KEY: Uniquely identifies a row/record in another table.
- CHECK: Ensures that all values in a column satisfies a specific condition.
- DEFAULT: Sets a default value for a column when no value is specified.
- INDEX: Used to create and retrieve data from the database very quickly.
What is difference between CAST and CONVERT function in SQL Server?
CAST: Cast function is used to convert from one data type to another data type.
Syntax: CAST( [Expression] AS Datatype )
declare @Test int=101 select CAST(@Test as varchar)
CONVERT: CONVERT function is also used to convert from one data type to another data type. It has one additional parameter which provide styles.
Syntax: CONVERT( data_type(length), expression, style )
select convert(varchar(20),GETDATE(),107)
What are the different date time to string conversion formats available?
| Format No | Date Format Query | Result |
|---|---|---|
| 0 | select convert( varchar, getdate(), 0) | Apr 30 2019 4:10PM |
| 1 | select convert( varchar, getdate(), 1) | 04/30/19 |
| 2 | select convert( varchar, getdate(), 2) | 19.04.30 |
| 3 | select convert( varchar, getdate(), 3) | 30/04/19 |
| 4 | select convert( varchar, getdate(), 4) | 30.04.19 |
| 5 | select convert( varchar, getdate(), 5) | 30-04-19 |
| 6 | select convert( varchar, getdate(), 6) | 30 Apr 19 |
| 7 | select convert( varchar, getdate(), 7) | Apr 30, 19 |
| 8 | select convert( varchar, getdate(), 8) | 16:10:15 |
| 9 | select convert( varchar, getdate(), 9) | Apr 30 2019 4:10:15:913PM |
| 10 | select convert( varchar, getdate(), 10) | 04-30-19 |
| 11 | select convert( varchar, getdate(), 11) | 19/04/30 |
| 12 | select convert( varchar, getdate(), 12) | 190430 |
| 13 | select convert( varchar, getdate(), 13) | 30 Apr 2019 16:10:15:913 |
| 14 | select convert( varchar, getdate(), 14) | 16:10:15:913 |
| 20 | select convert( varchar, getdate(), 20) | 2019-04-30 16:10:15 |
| 21 | select convert( varchar, getdate(), 21) | 2019-04-30 16:10:15.913 |
| 22 | select convert( varchar, getdate(), 22) | 04/30/19 4:10:15 PM |
| 23 | select convert( varchar, getdate(), 23) | 2019-04-30 |
| 24 | select convert( varchar, getdate(), 24) | 16:10:15 |
| 25 | select convert( varchar, getdate(), 25) | 2019-04-30 16:10:15.913 |
| 100 | select convert( varchar, getdate(), 100) | Apr 30 2019 4:10PM |
| 101 | select convert( varchar, getdate(), 101) | 04/30/2019 |
| 102 | select convert( varchar, getdate(), 102) | 2019.04.30 |
| 103 | select convert( varchar, getdate(), 103) | 30/04/2019 |
| 104 | select convert( varchar, getdate(), 104) | 30.04.2019 |
| 105 | select convert( varchar, getdate(), 105) | 30-04-2019 |
| 106 | select convert( varchar, getdate(), 106) | 30 Apr 2019 |
| 107 | select convert( varchar, getdate(), 107) | Apr 30, 2019 |
| 108 | convert( varchar, getdate(), 108) | 16:10:15 |
| 109 | convert( varchar, getdate(), 109) | Apr 30 2019 4:10:15:913PM |
| 110 | convert( varchar, getdate(), 110) | 04-30-2019 |
| 111 | convert( varchar, getdate(), 111) | 2019/04/30 |
| 112 | convert( varchar, getdate(), 112) | 20190430 |
| 113 | convert( varchar, getdate(), 113) | 30 Apr 2019 16:10:15:913 |
| 114 | convert( varchar, getdate(), 114) | 16:10:15:913 |
| 120 | convert( varchar, getdate(), 120) | 2019-04-30 16:10:15 |
| 121 | convert( varchar, getdate(), 121) | 2019-04-30 16:10:15.913 |
| 126 | convert( varchar, getdate(), 126) | 2019-04-30T16:10:15.913 |
| 127 | convert( varchar, getdate(), 127) | 2019-04-30T16:10:15.913 |
| 130 | convert( varchar, getdate(), 130) | 25 ????? 1440 4:10:15:913PM |
| 131 | convert( varchar, getdate(), 131) | 25/08/1440 4:10:15:913PM |
What is deferred name resolution in SQL Server?
Let's understand deferred name resolution with an example. Consider the stored procedure shown below.
Create procedure usp_GetData
as
Begin
Select * from Employee
End
If Employee table does not exist and if you execute the above SQL code, the stored procedure usp_GetData will be successfully created without errors. But when you try to call or execute the stored procedure using Execute usp_GetData, you will get a run time error stating Invalid object name 'Employee'.
So, at the time of creating stored procedures, only the syntax of the sql code is checked. The objects used in the stored procedure are not checked for their existence. Only when we try to run the procedure, the existence of the objects is checked. So, the process of postponing, the checking of physical existence of the objects until runtime, is called as deffered name resolution in SQL server.
Functions in sql server does not support deferred name resolution. If you try to create an inline table valued function as shown below, we get an error stating Invalid object name 'Employee' at the time of creation of the function itself.
Create function fnGetEmployees()
returns table
as
return Select * from Employee
So, this proves that, stored procedures support deferred name resolution, where as functions does not. Infact, this is one of the major difference between functions and stored procedures in sql server.
