Sql Server Exclusive Interview Questions and Answers (56) - Page 3

  • A joint initiative from DotNetFunda.Com and Questpond.
  • A one stop place on the internet to get trusted and proven Interview Questions (based on more than a decade of experience in this field) to ensure the success of the candidates.
  • You will find almost all Interview questions from Questpond here and this list is growing day by day with all latest and updated interview questions.

56 records found.

Get 650+ Questpond's Interview videos on discount

.NET and SQL Server Interview Question - What is Cross Join and in which scenario do we use Cross Join?

CROSS JOIN: Cross join is used to return all records where each row from first table is combined with each row in second table.
  Cross join is also called as Cartesian Product join.
  The cross join does not apply any predicate to filter records from the joined table. Programmers can further filter the results of a cross join by using a WHERE clause.


For Example:-  We have following two tables.


Look at the "Product" table:
























P_Id ProductName Cost
1 SimplePizza 100.00
2 CheezePizza 200.00
3 DoubleCheezePizza 300.00
4 ChickenPizza 250.00


Note that the "P_Id" column is the primary key in the "Product" table.


Next, we have the "SubProduct" table:
















Sub_Id SubProductName Cost
1 Cold Drink 100.00
2 Bread 15.00


Note that the "Sub_Id" column is the primary key in the "SubProduct" table.


There are lots of scenarios where we use the cross join(permutation and combination), below are the example of hotel where customer's gets the detail of combined product and its total cost, So that it is easy to select their respective choice.


Query:- select Product.ProductName,SubProduct.SubProductName,(Product.Cost+SubProduct.Cost)as TotalCost from Product cross join SubProduct


 The output look like below:








































ProductName SubProductName TotalCost
SimplePizza Coldrink 200.00
CheezePizza Coldrink 300.00
DoubleCheezePizza Coldrink 400.00
ChickenPizza Coldrink 350.00
SimplePizza Bread 115.00
CheezePizza Bread 215.00
DoubleCheezePizza Bread 315.00
ChickenPizza Bread 265.00


Regards,




.NET and SQL Server Interview Question - How does index makes search faster?


For better understanding, let us consider a simple search example which shows differences between a table, declared with index and without index.


Let's first see an example for a table which is created without declaring an index and look how exactly the SQL search engine will perform action. Below diagram will give u better idea…


In the above example, SQL search engine will search from initial till it finds the respective record and once the record is found it will basically display the record.


Further, When we create an index on any column of a table then the large data get divided like following B-Tree diagram, so that search becomes easier and faster.





                                                B-tree structure of a SQL Server index


For example:-


Suppose we have to search value 25 in an indexed column, the query engine will first look in the “Root Node” to determine which node to refer in the “Branch Nodes”. In the above example first “Branch Node” has Value 1 to 20 and the second “Branch Node” has Value 21 to 40, so the query engine will go to the second “Branch Node” and will skip the first “Branch Node” as we have to search Value 25. Same like “Branch Nodes” the query engine will operate the “Leaf Node” to retrieve respected result.


Regards,




.NET and SQL Server Interview Question - What are difference between Cluster index and Non-Cluster index?

Both of these indexes uses "B-tree" structure but in Cluster index the "Leaf Node" actually points the physical data, but in Non-Cluster index it point’s to the "Row ID" and then the "Row ID" points to the "Leaf Node" of Cluster Index.


Below is the diagram of Cluster and Non-Clustered index.


 

















Cluster Index Non-Cluster Index
A table can have only one Cluster Index as it point to the physical data. A table can have more than one Non-Cluster Index as it only points to the pointer of Cluster Index.
Physical data is stored as per Cluster Index. There is no relation of physical data.
The leaf node of Cluster Index consist of data pages. The leaf node of Non-Clusted Index contain Index row.


Regards,




.NET and SQL Server Interview Question - Difference between Stored Procedure and Function?

Function are compiled and executed at run time.
Stored Procedure are stored in parsed and compiled format in the database.

Function cannot affect the state of the database which means we cannot perform CRUD operation on the database.
Stored Procedure can affect the state of the database by using CRUD operations.

Store Procedure can return zero or n values whereas Function can return only one value.

Store Procedure can have input,output parameters for it whereas functions can have only input parameters.

Function can be called from Stored Procedure whereas Stored Procedure cannot be called from Function.

Regards,



.NET and SQL Server Interview Question - What is trigger and different types of Triggers?

Trigger is a SQL server code, which execute when a kind of action on a table occurs like insert, update and delete. It is a database object which is bound to a table and execute automatically.


Triggers are basically of two type’s namely "After Triggers" and "Instead of Triggers".
1.After Triggers:- this trigger occurs after when an insert, update and delete operation has been performed on a table.


“After Triggers” are further divided into three types
AFTER INSERT Trigger.
AFTER UPDATE Trigger.
AFTER DELETE Trigger.


Let us consider that we have the following two tables.


Create “Customer” table with the following field as you see in the below table.
























Cust_ID Cust_Code Cust_Name   Cust_Salary
1 A-31 Moosa 4500
2 A-09 Feroz 5000
3 A-16 Wasim 4000


Create “Customer_Audit” table with the following field as you see in the below table.









Cust_ID   Cust_Name Operation_Performed Date_Time


The main purpose of creating “Customer_Audit” table is to record the data which occurs on the “Customer” table with their respective operation and date-time.


Let’s begin with “After Insert Trigger”:- This trigger fire after an insert operation performed on a table.
Let us see the example of “After Insert Trigger” for better understanding.


Query:-
Create Trigger TrigInsert on Customer
For insert as 
declare @Cust_ID int;
declare @Cust_Name varchar(100);
declare @Operation_Performed varchar(100);
select @Cust_ID=i.Cust_ID from inserted i; 
select @Cust_Name=i.Cust_Name from inserted i; 
set @Operation_Performed='Inserted Record -- After Insert Trigger';
insert into Customer_Audit
(Cust_ID,Cust_Name,Operation_Performed,Date_Time) 
values(@Cust_ID,@Cust_Name,@Operation_Performed,getdate());
PRINT 'AFTER INSERT trigger fired.'


Now, insert a record into Customer table:


Query:- insert into Customer values ('A-10','Danish')


Once the insert statement is successfully done, the record is inserted into the “Customer” table and the “After Trigger” (TrigInsert) is fired and the same record is also stored into “Cutomer_Audit” table.


To see the record in “Customer_Audit” table write query as below:-


Query:- select * from Customer_Audit













Cust_ID   Cust_Name Operation_Performed Date_Time
4 Danish Inserted Record -- After Insert Trigger 2011-04-06 19:46:56.390

You can see that the same record is seen in the “Customer_Audit” table with Operation_performed and the date_time when it was updated.


Now let’s see for “After Update Trigger”:-This trigger fire after an update operation performed on a table.
Let us see the example of “After Update Trigger” for better understanding.


Query:- Create trigger TrigUpdate on Customer
For Update as 
declare @Cust_ID int;
declare @Cust_Name varchar(100);
declare @Operation_Performed varchar(100);
select @Cust_ID=i.Cust_ID from inserted i; 
select @Cust_Name=i.Cust_Name from inserted i; 
set @Operation_performed='Inserted Record -- After Insert';


if update(Cust_Name)
set @Operation_Performed='Updated Record -- After Update Trigger.';
insert into Customer_Audit
(Cust_ID,Cust_Name,Operation_Performed,Date_Time) 
values(@Cust_ID,@Cust_Name,@Operation_Performed,getdate())


PRINT 'AFTER UPDATE Trigger fired.'


Now, update a record into “Customer” table:-


Query:- update Customer set Cust_Name = 'Khan Wasim' where Cust_Code like 'A-16'


The record is updated into the Customer table and the TrigUpdate is fired and it stores a record into
“Cutomer_audit” table.


To see the record Customer_Audit table write query for that.


Query:- select * from Customer_Audit


















Cust_ID   Cust_Name Operation_Performed Date_Time
4 Danish Inserted Record -- After Insert Trigger 2011-04-06 19:46:56.390
3 Khan Wasim Updated Record -- After Update Trigger 2011-04-06 20:03:05.367

Now for, “After Delete Trigger”:-This trigger fire after a delete operation performed on a table.


In a similar way, you can code “After Delete trigger” on the table.


2.Instead of Triggers:- this trigger fire before the DML operations occur, first inserted and deleted get flourished and then trigger fires


“Instead of Triggers” are further divided into three types
Instead of INSERT Trigger.
Instead of UPDATE Trigger.
Instead of DELETE Trigger.


Let us see the example of “Instead of UPDATE Trigger” for better understanding.


Query:-
CREATE TRIGGER trgInsteadOfUpdate ON Customer 
INSTEAD OF update
AS
declare @cust_id int;
declare @cust_name varchar(100);
declare @cust_salary  int;
select @cust_id=d. Cust_ID from deleted d;
select @cust_name=d. Cust_Name from deleted d;
select @cust_salary =d.Cust_Salary from deleted d;


BEGIN
if(@cust_salary >4500)
begin
RAISERROR('Cannot delete where salary > 4500',16,1);
ROLLBACK;
end
else
begin
delete from Customer where Cust_ID =@cust_id;
COMMIT;
insert into Customer_Audit(Cust_ID,Cust_Name,Cust_Salary,Operation_Performed,Date_Time)
values(@cust_id,@cust_name,@cust_salary,'Updated -- Instead Of Updated Trigger.',getdate());
PRINT 'Record Updated -- Instead Of  Updated Trigger.'
end
END
Now, update a record into “Customer” table:-


Query:- update Customer set Cust_Name = 'Khan Wasim' where Cust_Code like 'A-09'


When you try to update Customer table it will raise an error as we have use Instead of Update trigger.


Error:- Server: Msg 50000, Level 16, State 1, Procedure trgInsteadOfUpdate, Line 15
Cannot update where salary > 4500


In a similar way, you can code “Instead Delete trigger” and “Instead Insert trigger” on the table.


Regards,




.NET and SQL Server interview questions - Select second highest salary from the table?

Let's us assume that we have the following table of Employee.



























Emp_Id
Emp_Name
Emp_Salary
1
Shiv
17000
2
Raju
13500
3
Sham
15000
4
Moosa
11000
5
Feroz
12000

Now we want to find out second highest salary from Employee table, as you see that the second highest salary is 15000 and we want to display the same.

Query:-
SELECT Emp_Name,Emp_Salary
FROM Employee e1
WHERE
2 = (SELECT COUNT(DISTINCT (e2.Emp_Salary))FROM Employee e2 WHERE e2.Emp_Salary >= e1.Emp_Salary)

The above employee table contains the second highest salary as 15000 therefore the result set will look like below output table.

Output:-








Emp_Name Emp_Salary
Sham 15000
        
If the table contains two or more same record of salary which is the second highest salary then the query will give you all the record of second highest salary as you see in the above output table.


Regards,



.NET and SQl Server interview questions :- Typical Complicated SQL query asked in .NET interviews to test your SQL CASE syntax capability?

Let's us assume that we have the following table of Employee.




























Emp_Id Emp_Name Emp_Salary
1 Shiv 17000
2 Raju 13500
3 Sham 15000
4 Moosa 11000
5 Firoz 12000


There can be a scenario we have to display employee name whose salary is greater than "some amount" or less than "some amount" for that purpose we use case statement.


Let's us consider that we have to display all the employee names from the employee table and the status like salary is greater than 13000 or lesser than 13000.


Query:

 SELECT Emp_Name,CASE 
 when (Emp_Salary>13000) then 'Greater than 13000'
 else 'Lesser than 13000'
 end as Status
 FROM Employee

Output:






















Emp_Name Status
Shiv Greater than 13000
Raju Greater than 13000
Sham Greater than 13000
Moosa Lesser than 13000
Firoz Lesser than 13000


Hence you can see that all the employee name have been displayed with their salary status in the above output.


Regards,




.NET and SQL Server interview questions - Typical Complicated SQL query asked in .NET interviews to see if you know how to split columns into a row?

let's us assume that we have the following table of Employee.

Emp_IdEmp_NameEmp_Salary_2010Emp_Salary_2011
1
Shiv1700019000
2
Raju
1350015000
3
Sham1500018000
4
Moosa1100014000
5
Feroz1200016000
                     
Now we want to merge the Emp_Salary_2010 and Emp_Salary_2011 columns into a single column as Salary.
Query:-
select Emp_Name,Emp_Salary_2010 as Salary from Employee
union
select Emp_Name,Emp_Salary_2011 as Salary from Employee
Output:-
Emp_NameSalary
Shiv17000
Shiv19000
Raju
13500
Raju
15000
Sham
15000
Sham
18000
Moosa
11000
Moosa
14000
Feroz 12000
Feroz 15000

Regards,

      


.NET and Sql Server interview questions - How will you design one to one, one to many and many to many relationships in sql server?

This is one of the most typical question asked by the interviewers.


Let’s us assume that we have the following two table of Product and Vendor with their respective fields like below.
ProductId    ProductName    Cost            //Product table
VendorID     VendorName     ProductId     //Vendor table 


Now let’s begin with how you can design one to one relationship between these two tables.


One to One relationship:-
Below diagram show one to one relationship between Product and Vendor table.


R1


In the above Product table “ProductId” is defined as a primary key and in the Vendor table “ProductId” is defined as foreign key with reference to the “ProductId” of the Product table.


In this way you can achieve one to one relationship.


One to Many relationship:-


Now we want to define one to many relationship on Vendor and Product table.
In this many records in one table correspond to the one record in another table.


Example: Every one vendor can have multiple products. So there exist one-to-many relationships between Vendor and Product table.


In order to define one to many relationship you have to add one column name as VendorId in Product table which is foreignkey reference to VendorId of Vendor table.


Below diagram show one to many relationship on Product and Vendor table.



R2


Many to Many relationship:-


In this, one record in one table corresponds to many rows in another table and also vice-versa.


For instance: In a company, one vendor can sale many products and also one product can be sold by many Vendors.
Given below diagram is a sample of many-to-many relationship. For defining many to many we have to add one more table VendorProduct which is linked to the primary key of Product and Vendor tables.


r3


In the following video, view samples of various questions asked in C#, .NET and Sql Server interview. 



SQL and .NET interview Question - What is difference between ADO and ADO.NET?























ADO ADO.NET
ADO works with connected data. This means that when you access data, such as viewing and updating data, it is real-time, with a connection being used all the time. ADO.NET uses data in a disconnected fashion. When you access data, ADO.NET makes a copy of the data using XML.
ADO allows you to create client-side cursors only. ADO.NET gives you the choice of either using client-side or server-side cursors.
ADO allows you to persist records in XML format. ADO.NET allows you to manipulate your data using XML as the primary means. This is nice when you are working with other business applications and also helps when you are working with firewalls because data is passed as HTML and XML.
ADO is statefull (TCP/IP). ADO.NET is stateless (Internet).
In ADO, we have a recordset. In ADO.NET, we have both recordset and dataset.
In recordset we can only have one table. If we want to accommodate more than one tables we need to do inner join and fill the recordset. Dataset can have multiple tables.

You will be also interested in watching the below video, which are also asked in most of the interviews and favourable question of interviewers.



.NET and SQL Server interview questions - How to obtain the entire employee name who obtains top 10 salaries?

Let’s see a simple example to see, how exactly we can obtain entire employee name who obtains top 10 salaries.

Assuming that we have following table for Employee.

EmpId
EmpName
EmpSalary
1
Alok
20000
2
Ashok
30000
3
Anand
25000
4
Akash
18000
5
Prasad
33000
6
Manoj
30000
7
Sachin
40000
8
Denish
36000
9
Rajesh
24000
10
Hari
4000
11
Ravi
25000
12
Kiran
20000
13
Shobha
30000
14
Pallavi
43000
15
Reshma
41000

Query:-
select Employee.EmpName,Employee.EmpSalary from Employee where Employee.EmpSalary in
(select distinct top(10)Employee.EmpSalary from Employee order by Employee.EmpSalary desc)
OUTPUT:-


EmpNameEmpSalary
Alok20000
Ashok30000
Anand
25000
Akash
18000
Prasad
33000
Manoj
30000
Sachin
40000
Denish
36000
Rajesh
24000
Ravi
25000
Kiran
20000
Pallavi
43000
Reshma
41000

You will be also interested in watching the below video, which are also asked in most of the interviews and favourable question of interviewers.



.NET and SQL Server interview questions - Are SQL Server Views Updatable?

View is a virtual table, which can contains data (rows with columns) from one or more table and you can retrieve data from a view.

Let’s demonstrate a simple example in which we will see that how to create a view on single table and will also see that if we update the view the respective table on which the view is created  is updated or not.

Now let first see how to create view.

Go to View folder in SQL Server > Right click on it > select New View.
v1.png

As soon as you click on New View, the following window will appear like below.

v2.png
Now, just select the table name from the list on which you wish to create a View and Click on Add then click on close. Once you click on close a new window will appear which allow you to create View on the respective column.

v3.png

After selecting the column name just save the view and give View a nice name.

v4.png

Once you have completed the above step you will see that the respective View is added in the View folder.


v5.png

Now let’s see that when we update the view the respective table is also updated or not.

Query:-

Update [Practice].[dbo].[Cust_View] set Customer_Contact = 96641122 where Customer_Name = 'Feroz'

Now just go to the table on which the view was created and check whether the table is updated or not, you will see that the table is also updated when you update the View.

Now let’s create a view based on two tables and try to update a view.

create view View_Cust as SELECT    dbo.Customer.Customer_Name, dbo.Customer.Customer_Contact,dbo.[Order].Product_Name,dbo.[Order].Price
FROM dbo.Customer
INNER JOIN dbo.[Order] ON dbo.Customer.Order_ID = dbo.[Order].Order_ID
Let’s try to Update View:

Query:-
Update [Practice].[dbo].[View_Cust] set Customer_Contact = 098767554, Price = 4000 where Customer_Name = 'Feroz'
As you can see in the above query, I am trying to update a column from the Product table and another column from the Order table and try to execute the query the compiler will throw the below error.

Error Message:- View or function 'Practice.dbo.View_Cust' is not updatable because the modification affects multiple base tables.

This means that when you try to update both the table’s column from the view then it is not allowed but you can update single table column.



SQL Server interview questions: - Can you explain and show how indexes make search faster?

SQL Server Indexes are favorites of interviewers when it comes to SQL Server interviews and .NET interviews. One of the basic questions which interviewers ask is “why do we need indexes?” and our excited developer friend shouts “PERFORMANCE”.

Then the smart interviewer drills down more asking further how does it increase performance and there’s a big “SILENCE” :-).

In this question let’s demonstrate practically how indexes improve performance and why.

Assume that we have the following “Customer” table.



Let’s first see an example for the above table without creating an index on it and let’s look how much time the SQL engine takes to fetch the required data.
 






You can see from the above image that there are no indexes.

In order to view what exact time does the SQL engine takes to retrieve the requested data, you need to set the “set statistics io on” in your query.

Query: - set statistics io on Select * from Customer where Customer.CustomerID = 1034

When you execute your query the result set will be shown along with the Message like below diagram.
 





In the above diagram you can see that the Logical reads is 17

Now let’s see an example for the same table but with “Index” created on it and let’s see the difference between the logical reads value.

When we create an index on any column of a table then data gets divided like the following B-Tree diagram, so that search becomes easier and faster. For example if we want to search 1500 it will straight go to the section 1001 – 1500 rather than looping though all records.





To create index on table follow the below steps
 





Query
: - Set statistics io on Select * from Customer where Customer.CustomerID = 1034

The result set will look by below diagram.


 


In above diagram you can see that now the “Logical read is 11” as compared to the result set of the table without index which was “Logical read 17”.

This shows that the indexes make search faster because it uses B-Tree structure to search the required result set.

One of the other ways to increase performance is by using SQL Server profiler and index tuning wizard, watch the below video to understand more about the same: -



.NET and SQL Server interview questions - Typical complicated SQL Query asked in .NET interview to test your SQL “Group by” Clause syntax capability?

Let us assume that we have the following table of Tourist with their respective rows,columns and data.




Assuming the above table, we have to display the total number of tourists from the different part of the countries.
In order to achieve the required result we have to use SQL “Group by” clause.


Query: -

select T1.TouristCountry,count(T1.TouristName) as NumberOfTourist from
Tourist T1 group by T1.TouristCountry


Output: -



Hence you can see that the total number of tourist belongs from different countries has been displayed.


You will be also interested in watching the below video: -




.NET and SQL Server interview questions - What is the difference between char (10) and nchar (10)?























char nchar
char are fixed length data-types. nchar are also fixed length data-types.   
char does not support Unicode character. nchar support Unicode character.
char reserves 1 byte(8 bits) of memory space. nchar reserves 2 bytes (16 bits) of memory space.
char support Ansi code character, which max upto 256 character.   nchar support Unicode character, which max upto 65536 character.
char(n) specifies a length of n bytes by default. nchar(n) specifies a length of n characters by default.
char does not support character of different languages. nchar support characters of different languages.

In order to view the exact difference between the char(10) and nvarchar(10), we will use DataLength.


DataLength: -


Use DATALENGTH when the actual number of bytes is required, including trailing spaces.So, in a double-byte encoding, you'll get actual bytes, not characters.


Let us assume that we have following table with their respective fields and values.





Now let’s see what will be the DataLength of the StudentName column which is declared char (10) as datatype and what will be the DataLength of the StudentAddress column which is declared nchar(10) as datatype.


Query: -

select DATALENGTH(CustomerName) as DataLengthOfchar ,DATALENGTH(CustomerAddress) 

as DataLengthOfnchar from Customer

Output: -



You will be also interested in watching the below video: -




SQL Server interview questions: - How can we improve SQL performance using SQL profiler?

By using SQL profiler we can capture the load and then feed that load to SQL Server tuning advisor. Tuning advisor scans the load and then gives out index suggestions.


Below is a simple video which explains the same in depth.



More SQL SERVER Interview Questions & Answers here

Found this useful, bookmark this page to the blog or social networking websites. Page copy protected against web site content infringement by Copyscape

 Exclusive Interview Questions and Answers Categories