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

SQL Server Analysis Services (SSAS) Tutorial by MSSQLTips.com

Guys,

SQL Server Analysis Services (SSAS) is the technology from the Microsoft Business Intelligence stack, to develop Online Analytical Processing (OLAP) solutions. In other terms, we can use SSAS to create cubes using data from data marts / data warehouse for deeper and faster data analysis.

Cubes are multi-dimensional data sources which have dimensions and facts (also known as measures) as its basic constituents. From a relational perspective dimensions can be thought of as master tables and facts can be thought of as measureable details. These details are generally stored in a pre-aggregated proprietary format and users can analyze huge amounts of data and slice this data by dimensions very easily. Multi-dimensional expression (MDX) is the query language used to query a cube, similar to the way T-SQL is used to query a table in SQL Server.

Simple examples of dimensions can be product / geography / time / customer, and similar simple examples of facts can be orders / sales. A typical analysis could be to analyze sales in Asia-pacific geography during the past 5 years. Think of this data as a pivot table where geography is the column-axis and years is the row axis, and sales can be seen as the values. Geography can also have its own hierarchy like Country->City->State. Time can also have its own hierarchy like Year->Semester->Quarter. Sales could then be analyzed using any of these hierarchies for effective data analysis.

A typical higher level cube development process using SSAS involves the following steps:

1) Reading data from a dimensional model
2) Configuring a schema in BIDS (Business Intelligence Development Studio)
3) Creating dimensions, measures and cubes from this schema
4) Fine tuning the cube as per the requirements
5) Deploying the cube

This tutorial will step through a number of topics that we need to understand in order to successfully create a basic cube. Gigh level outline is as follows: 

• Design and develop a star-schema
• Create dimensions, hierarchies, and cubes
• Process and deploy a cube
• Develop calculated measures and named sets using MDX
• Browse the cube data using Excel as the client tool

In this tutorial, we will also try to develop an understanding of OLAP development from the eyes of an OLTP practitioner.

Click here to read tutorial.


Happy Learning

Sudhir 

SQL Server Integration Services Tutorial by MSSQLTIPS.com

Guys,

SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations; i.e. Extract, Transform and Load data. While ETL processing is common in data warehousing (DW) applications, SSIS can do much more than that e.g. when we create a Maintenance Plan using SQL Server Management Studio (SSMS) an SSIS package is created, when we export or import data, an SSIS package is created.

At a high level, SSIS provides the ability to:

• Retrieve data from just about any source
• Perform various transformations on the data; e.g. convert from one type to another, convert to uppercase or lowercase, perform calculations, etc.
• Load data into just about any source
• Define a workflow  

In this tutorial we will step through a number of topics that we need to understand in order to successfully build an SSIS package. Our high level outline is as follows:

• Creating SSIS packages with SQL Server Management Studio (SSMS)
• Business Intelligence Development Studio (BIDS)
• Creating a simple SSIS package in BIDS
• Deploying SSIS packages
• Executing SSIS packages

Click here to read tutorial.

 

Happy learning

 

Sudhir

SQL Server Reporting Services Tutorial by MSSQLTips.com

Guys,

SQL Server Reporting Services 2008 (SSRS) is a feature included in the SQL Server 2008 product. We use SSRS to design, develop, test, and deploy reports. SSRS leverages Business Intelligence Development Studio (BIDS) developer tool for all aspects of authoring and deploying reports.

In this tutorial we will step through a number of topics that we need to understand to successfully build a report. The tutorial will run through following topics:

• Reporting Services Components
• Install Reporting Services
• Business Intelligence Development Studio (BIDS)
• Install Sample Database
• Create a Simple Report with the Wizard
• Create a Simple Report with the Report Designer
• Deploy Reports
• Configure Report Manager Security 

Click here to read tutorial.

Happy Learning

Sudhir

SSWUG Free Expo Event: Understanding SQL Server Analysis Services

Guys,

SSWUG.ORG’s virtual expo will review various aspects of SQL Server Analysis Services (SSAS), which enables the server to be used for analytical processing and data mining. Through in-depth sessions with four of the leading experts in the information technology (IT) and business intelligence (BI) fields, we will see many demonstrations and examples on designing, creating and managing data from multiple sources. By the end of our event, we should have the tools and understanding needed to bring added functionality, automation and insight into your data. 

Please register now to learn from four acclaimed SSAS experts for free on Friday, May 20! Click here to Register.

Friday, May 20, 2011
9 a.m. - 1 p.m. PDT

Sessions will cover the following topics:

 

• Common Mistakes with SSAS Cube Designs  Sudhir
• SSAS Partitioning and Aggregation Strategies 
• Properly Using the SSAS Query Cache 
• Overcoming SSAS Implementation Issues 
• Building a Scalable SSAS Solution 
• Best Practices for Performance and Tuning Techniques