SQL Server Questions PART - 1

Guys,


Let's discuss some of the basic questions that we might face anywhere. I will try to come up with as many as I can. It will be great to receive comments/feedback on the same.


Here we go:


Q. How many types of Indexes are there in SQL Server? What is the difference between them?


Ans: There are 2 type of indexes. Clustered and Non-Clustered. There can be only 1 Clustered index per table and 249 Non-Clustered Indexes per table. The index at the end of the book is a perfect example of Clustered Index.


In Clustered index, data is sorted & saved. Suppose there is a table Employee with Clustered Index created on Employee Name field. We have data like Abrahim, Jacob etc. Now whenever it tries to save new data with Colin, it will be inserted between Abrahim and Jacob. This is not applicable for Non-Clustered index, the data is not saved in sorted way.


Visit here for Clustered Indexes
msdn.microsoft.com/en-us/library/aa933131(v=SQL.80).aspx
Visit here for Non-Clustered Indexes
msdn.microsoft.com/en-us/library/aa933130(v=SQL.80).aspx


Q. What is the difference between UNION and UNION ALL?


Ans: There is a very small difference between UNION and UNION ALL. When we join 2 resultsets using UNION, it will internally add Distinct clause before returning final result. When we use UNION ALL, full set of data is retrieved.

To see this, go to SSMS and Enable Execution Plan, it will show the difference.


Visit here for more information:
msdn.microsoft.com/en-us/library/ms180026.aspx


Q. What is the difference between Primary key and Unique key?


Ans: We have tables & Rows and to identify each row we need Primary key. Someone can say that a Unique key can also help to identify each row. That’s true but there is a difference. Primary Key value can’t contain NULL, it must have a value whereas Unique Key can have 1 NULL value as its unique and because NULL is unknown its hard to identify/represent a row with NULL. That’s why Primary key doesn’t allow NULL.


Q. How Page Split occurs and why?


Ans. I am sure we all are aware of Indexes, especially Clustered Index because data is sorted before it is written to disk. As we have a 8K page size and further it depends on Fill factor and row size (length of each field) how much that page will be filled.


Suppose one page can accommodate maximum 2 rows in 1 page. We have same Employee table with Employee Name as primary Key. First page has 2 rows where Employee Name in 1st Row is Amar and 2nd Row is Carol. Now when we try to add a new row with Employee Name as Boris. As Boris comes before Carol so data will be inserted after Amar, but a page can hold only 2 rows so what will happen to Carol.


SQL Server pick handles/Link list end points for next page from 1st page. Create a new page, move Boris to this new page & store Boris on 1st page. Now reset the next page after 1st Page to New Page and New Page will point to the original 2nd page so that the Link list is updated.


sqlblogcasts.com/blogs/tonyrogerson/arch...appen-why-worry.aspx


Q. What are the disadvantages of having Indexes?


Ans. Insertion or saving become slow because data is sorted before it is saved and while doing so some page splits will occur and it will take its own time.


There should not be too many indexes else things will slow down heavily.


If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.


Q. What is a covering Index?


Ans. covering index, which is a form of a composite index, includes all of the columns referenced in the SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn’t have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.


On the other hand, if the covering index gets too big (has too many columns), this could actually increase I/O and degrade performance. Generally, when creating covering indexes, follow these guidelines:
• If the query or queries you run using the covering index are seldom run, then the overhead of the covering index may outweigh the benefits it provides.
• The covering index should not add significantly to the size of the key. If it does, then it its use may outweigh the benefits it provides.
• The covering index must include all columns found in the SELECT list, the JOIN clause, and the WHERE clause.


www.simple-talk.com/sql/learn-sql-server...e-query-performance/


Q. Is it possible to use Order by clause in View? If yes, how?


Ans. Generally Order by clause is not allowed in a view. In case we need to use it then we need to use TOP clause.


Select top 10 * from ViewName
Order by Field

Or

Select top 100 Percent from ViewName
Order by Field

Q. Write a query that return Nth highest salary of employees.


SELECT TOP 1 UnitPrice
FROM (
SELECT TOP 12 UnitPrice
FROM Products
ORDER BY UnitPrice DESC) a
ORDER BY UnitPrice


OR


SELECT MIN(UnitPrice) FROM Products
WHERE UnitPrice IN
(SELECT TOP 12 UnitPrice FROM Products ORDER BY UnitPrice DESC)


 
Q. There are 2 tables Customers and Orders and CustomerID is primary key in Customers table and OrderID is primary key in Orders table. CustomerID is foreign key in Orders table.


Write a query to list all Customers who have ordered at least once.

Write a query to list all Customers who are have no orders.


ANS:

--Customers with at least 1 order

Select c.CustomerId, c.ContactName, Count(OrderID)
from Customers c inner join Orders d
on c.CustomerID = d.CustomerId
group by c.CustomerID, c.ContactName
having Count(*) >= 1


--Customers with no orders

select c.CustomerId, c.ContactName, Count(OrderID) NoOfOrders
from Customers c left join Orders d
on c.CustomerID = d.CustomerId
group by c.CustomerID, c.ContactName
having Count(OrderID) = 0

or

Select c.* from Customers c left join
Orders b on c.CustomerID = b.CustomerID
where b.OrderID is null


Q. There is a table Employees with fields like EmployeeID, ReportsTo and CityId. EmployeeID is Primary Key and ReportsTo as foreign Key in Employees table.


Write a query to retrieve all Employees who are Managers.

Write a query to retrieve all employees who live in same city as their managers


Ans.

--All Employees as Managers
Select a.employeeid, a.lastName from employees a inner join employees b
on a.Employeeid = b.Reportsto
group by a.EmployeeID, a.lastname
having count(*) >0

--All Employees who live in same city as their Manager
select a.EmployeeId, b.EmployeeId, a.LastName + ',' + a.FirstName, b.LastName + ',' + b.FirstName, a.City, b.City

From Employees a inner join Employees b
on a.EmployeeID = b.ReportsTo
Where a.City = b.City


Q. Write a query to identify duplicate records in a table.
2 CASES: If we have identity field and another when we don't have identity field



ANS:


create table testWithIdentity
(
ID int Identity(1,1),
Project varchar(10),
hours int,
Activity varchar(10))
Go

Insert into testWithIdentity
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST1'
go




Select * from testWithIdentity
go


Select project, hours, activity from test
group by project, hours, activity
having count(*) > 1


--This statement will work but what about ID field, let’s add ID in select statement.


Select id,project, hours, activity from test
group by id,project, hours, activity
having count(*) > 1


--This shows 0 records as duplicate.


Let’s write our query differently:


Select a.* from test a,
test b
where a.project = b.project and a.hours = b.hours
and a.activity = b.activity
and a.id < b.id


CASE 2:


create table testWithNoIdentity
(
Project varchar(10),
hours int,
Activity varchar(10))
go


Insert into testWithNoIdentity
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST'
union all
Select 'AWARDS', 1000, 'TEST1'
go


Select * from testWithNoIdentity
go


Select * from testWithNoIdentity
group by project, hours, activity
having count(*) > 1



Q. What do we call a table that has no index?


ANS: HEAP

Q. What is the data Page size (actual and available)?


ANS:

8KB = 8196 = TOTAL ROW SIZE


8060 = AVAILABLE ROW SIZE



Q. What are magic tables (hint: Triggers / OUTPUT clause)


ANS: INSERTED and DELETED also known as Special Tables



Q. What will be the output of following snippet:

Declare @val1 varchar(10)
Declare @val2 varchar(10)
Declare @val3 varchar(10)


set @val3=null
set @val2='adadasd'
set @val1=null


Select Coalsec(@val3, @val1, @val1)

The output will be NULL


If we rewrite our statement as Select Coalsec(@val3, @val1, @val2)


It will return


‘adadasd’




Q. What does NULL mean?


ANS: UNKNOWN






Q. If I have a view with definition as Select * from table and table has 3 columns. When I execute Select * from view I see 3 columns.


Now if I alter my table and add 1 more column & execute Select * from view how many columns will be returned & why?

Ans: As View is also an object and it has definition with columns retrieved. As soon as view is created, it creates entry for all fetched columns, so even if we add another field in base table and execute query as Select * from view, it will pick only those fields which are available in its metadata / sys.Columns or syscolumns table.


To update the view schema, execute


sp_refreshview ‘ViewName’


This will refresh the schema definition of the view.



Q. There are 5 records in a table with two columns i.e name and age.


Data in Name field can be anything but age are 20,30,null,40 and null.

What will be the output of following statements:

select avg(age) from table
select count(1) from table

select count(age) from table






Ans:


create table test
(name varchar(100),
age int null)
go


insert into test
select 'ax', 20
union all
select 'bw',30
union all
select 'bw1',null
union all
select 'abw',40
union all
select 'bw1a',null
go






select avg(age) from test – 30 because 20+30+40 = 90 and null columns are not counted for calculating avg, which is a numeric value


go


select count(1) from test –- 5 total records in table are 5


go


select count(age) from test –- 3 because null is excluded, so only 3 rows with number are returned.


Go


Q. How to pass Rows to stored procedure as parameter?


ANS: Use Table Value Parameter


Q. What is the difference in DEALLOCATE and CLOSE CURSOR?


Ans: Close Cursor will close the Cursor but it can be accessed again using Open Cursor. When Deallocate cursor is given, the cursor will be removed from memory. In case that cursor is required again, then we need to create fresh cursor with DECLARE statement again.


Appreciate your feedback. Will come up with more soon.
 
Thanks
 
Sudhir