2.NonClustered Index, Advanced SQL tutorial pdf, change fill factor while creating NonClustered Index, clustered and non clustered index in sql, Clustered and Nonclustered Indexes Described, Clustered Index, clustered index in sql, Clustered Index vs. Non-Clustered Index in SQL Server, Clustered versus Non Clustered Index, Clustered vs. Nonclustered Index Structures in SQL Server, Covering Index, Covering Index in SQL, Difference Between Clustered and Non-Clustered Indexes, Difference between clustered and nonclustered index, difference between clustered and nonclustered index in sql, difference between clustered and nonclustered index in sql server with example, Differences between Clustered Index Vs NonClustered Index, Differences between Clustered Index Vs Primary Key, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Fill factor in Indexes, Filtered Index, Free Download SQL SERVER Interview questions, Indexes in detail, Learn Indexes in detail, NonClustered Index with Included columns, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions, Types of Indexes, Types of Indexes in SQL
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.
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?
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 (,,,,,,,,,)) p
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.?
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 !