SQL Server Interview Questions

Recently I have given a technical interview for a company based out from Pune. So with out further delay will list the questions below-

1. We are going to get 1 lakh records per second in our database like we have in stock market example. So how do you design database for this kind of system.

UPDATE – Answer – https://msbiskills.com/2015/08/24/sql-server-interview-question-how-to-handle-large-number-of-insertions-in-sql-server/

2. Let’s say we are in a transaction, and the transaction got failed, so sometimes we have to commit that transaction and sometimes we have to rollback. E.g. we are withdrawing money from an ATM, we got the money and account is not updated. Then in this case how this transaction is committed or vice versa.

Answer – Will write a complete post for this question. Coming Soon…

3. Suppose you are inserting data in a table and the table has an identity column. How you do get that latest identity value from that table.

Answer – You can use Max(ID) from the column and easily find out. One more method is there IDENT_CURRENT( ‘TableName’ ).

4. How do you check the table definition in SQL Server?

Answer – Alt + F1

5. Let’s says we have a table with 3 columns say column a, column b and column c. Now we create Non Clustered index on b,c & a,b. Now there a query like select name from table WHERE b LIKE ‘Pawan’ , so which index it will use.

Answer – It will use index (b,c). For details please visit – https://msbiskills.com/2015/08/10/sql-server-interview-question-which-index-will-be-used/

6. There is a sentence and you have find out first word from that, how do you do that?

Answer –

DECLARE @F AS NVARCHAR(22) = N'abd cc sdsa'
DECLARE @F1 AS NVARCHAR(6) = N'abd'

SELECT SUBSTRING(@F,0,CHARINDEX(' ',@F))
SELECT CASE WHEN CHARINDEX(' ',@F1) = 0 THEN @F1 ELSE SUBSTRING(@F1,0,CHARINDEX(' ',@F1)) END 

7. There are two tables DEPT ( DeptID, DeptName ) and Emp ( EmpID, EmpName, DeptID, MgrID )

--Create Table
CREATE TABLE Employees  (EmpID INT, EmpName VARCHAR(20), DeptID INT ,ReportsTo INT)

--Insert Data
INSERT INTO Employees(EmpID, EmpName, ReportsTo, DeptId)
  SELECT 1, 'Jacob', NULL , NULL UNION ALL
  SELECT 2, 'Rui', NULL, NULL UNION ALL
  SELECT 3, 'Jacobson', NULL, NULL UNION ALL
  SELECT 4, 'Jess', 1, 1 UNION ALL
  SELECT 5, 'Steve', 1, 1 UNION ALL
  SELECT 6, 'Bob', 1, 1 UNION ALL
  SELECT 7, 'Smith', 2, 2 UNION ALL
  SELECT 8, 'Bobbey', 2, 2 UNION ALL
  SELECT 9, 'Steffi', 3, 3 UNION ALL
  SELECT 10, 'Bracha', 3, 3 UNION ALL
  SELECT 11, 'John', 5, 5 UNION ALL
  SELECT 12, 'Michael', 6, 2 UNION ALL
  SELECT 13, 'Paul', 6, 2 UNION ALL
  SELECT 14, 'Lana', 7, 3 UNION ALL
  SELECT 15, 'Johnson', 7, 2 UNION ALL
  SELECT 16, 'Mic', 8 , 3 UNION ALL
  SELECT 17, 'Stev', 8, 2 UNION ALL
  SELECT 18, 'Paulson', 9, 3 UNION ALL
  SELECT 19, 'Jessica', 10, 2
GO


CREATE TABLE DeptEmp
(
    DeptID INT
   ,DeptName VARCHAR(100)
)
GO

INSERT INTO DeptEmp(DeptID,DeptName)
VALUES (1,'IT'),(2,'HR'),(3,'Finance')
GO

Solution
-----------------

;WITH CTE(DeptID, EmpName , EmpId, Level,FullyQualifiedName) AS 
(
     Select DeptID , E.EmpName, E.EmpID, 0 Level
     , Cast('.'+E.EmpName+'.' as Varchar(MAX)) FullyQualifiedName  
     From Employees E Where E.ReportsTo IS NULL
     UNION ALL
     Select E.DeptID , E.EmpName, E.EmpID, c.Level + 1 , c.FullyQualifiedName+'.'+E.EmpName+'.' FullyQualifiedName
     From Employees E INNER JOIN CTE c on c.EmpID = e.ReportsTo 
)
,CTE1 AS 
(	
	SELECT D.DeptName , SPACE(LEVEL*4) + H.EmpName Hierarchy,SPACE(LEVEL*4) + CAST(H.EmpID AS VARCHAR(MAX)) 'Hierarchy/EmpId'
	, EmpName
	FROM CTE H LEFT JOIN DeptEmp D ON H.DeptID = D.DeptID 
)
SELECT * FROM CTE1

8. Suppose number of columns are not fixed ? How do you design database for this kind of system?

Answer -One way is to create a single column VARCHAR(MAX) with a separator. It works like below-

CREATE TABLE TestMulCols
(
       Data VARCHAR(MAX)
)
GO

INSERT INTO TestMulCols VALUES('a,b,c,d,e')
INSERT INTO TestMulCols VALUES('r,e')
INSERT INTO TestMulCols VALUES('d,q,j,k,o,i,i,i,i,2')

;WITH CTE AS
(
       SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Rnk, Data FROM TestMulCols
)
,CTE1 AS
(
       SELECT tr.ID , tr.VALUE, CTE.Rnk FROM CTE
       CROSS APPLY (SELECT ID,Value from dbo.udf_Split(Data,',')) tr
)
SELECT * FROM CTE1
PIVOT 
(MAX([Value]) FOR ID In ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) p

Output

9. You are not able to connect to your SQL Server from .Net application? What you will check and how you will rectify the connection problem.?

Answer-

You can start by checking following things-
1. First check whether you are able to connect to the server where SQL Server is installed.
2. Your SQL Server should be up and running. Check from Services.msc or SQL Server connection manager. Connect using SSMS and check.
3. TCP/IP should be enabled in SQL Server Configuration Manager. Default port is 1433.
4. Add a port under exception in windows firewall. Port Number should be 1433 (Default One)
5. Also allow remote connections from the server. ( Server -> Properties -> Connections -> Allow remote connections to this server )

Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com