SQL SERVER – Quiz and Video – Introduction to Basics of a Query Hint

This is follow up blog post of my earlier blog post on the same subject – SQL SERVER – Introduction to Basics of a Query Hint – A Primer. In the article we discussed various basics terminology of the query hints. The article further covers following important concepts of query hints.

  • Expecting Seek and getting a Scan
  • Creating an index for improved optimization
  • Implementing the query hint

Above three are the most important concepts related to query hint and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1) You have the following query:

DECLARE @UlaChoice TinyInt
SET @Type = 1
SELECT *
FROM LegalActivity
WHERE UlaChoice = @UlaChoice

You have a nonclustered index named IX_Legal_Ula on the UlaChoice field. The Primary key is on the ID field and called PK_Legal_ID 99% of the time the value of the @UlaChoice is set to ‘YP101′. What query will achieve the best optimization for this query?

  1. SELECT *
    FROM LegalActivity
    WHERE UlaChoice = @UlaChoice
    WITH(INDEX(X_Legal_Ula))
  2. SELECT *
    FROM LegalActivity
    WHERE UlaChoice = @UlaChoice
    WITH(INDEX(PK_Legal_ID))
  3. SELECT *
    FROM LegalActivity
    WHERE UlaChoice = @UlaChoice
    OPTION (Optimize FOR(@UlaChoice = ‘YP101′))

2) You have the following query:

SELECT *
FROM CurrentProducts
WHERE ShortName = ‘Yoga Trip’

You have a nonclustered index on the ShortName field and the query runs an efficient index seek. You change your query to use a variable for ShortName and now you are using a slow index scan.

What query hint can you use to get the same execution time as before?

  1. WITH
  2. LOCK
  3. FAST
  4. OPTIMIZE FOR
  5. MAXDOP
  6. READONLY

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 3

2) 4

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

Available at

USA: Amazon

India: Flipkart IndiaPlaza

Volume: 12345

Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Quiz and Video – Introduction to Hierarchical Query using a Recursive CTE

This is follow up blog post of my earlier blog post on the same subject – SQL SERVER – Introduction to Hierarchical Query using a Recursive CTE – A Primer. In the article we discussed various basics terminology of the CTE. The article further covers following important concepts of common table expression.

  • What is a Common Table Expression (CTE)
  • Building a Recursive CTE
  • Identify the Anchor and Recursive Query
  • Add the Anchor and Recursive query to a CTE
  • Add an expression to track hierarchical level
  • Add a self-referencing INNER JOIN statement

Above six are the most important concepts related to CTE and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1) You have an employee table with the following data.

EmpID FirstName LastName MgrID
1 David Kennson 11
2 Eric Bender 11
3 Lisa Kendall 4
4 David Lonning 11
5 John Marshbank 4
6 James Newton 3
7 Sally Smith NULL

You need to write a recursive CTE that shows the EmpID, FirstName, LastName, MgrID, and employee level. The CEO should be listed at Level 1. All people who work for the CEO will be listed at Level 2. All of the people who work for those people will be listed at Level 3. Which CTE code will achieve this result?

  1. WITH EmpList AS
    (SELECT Boss.EmpID, Boss.FName, Boss.LName, Boss.MgrID,
    1 AS Lvl
    FROM Employee AS Boss WHERE Boss.MgrID IS NULL
    UNION ALL
    SELECT E.EmpID, E.FirstName, E.LastName, E.MgrID, EmpList.Lvl + 1
    FROM Employee AS E INNER JOIN EmpList
    ON E.MgrID = EmpList.EmpID)
    SELECT * FROM EmpList
  2. WITH EmpListAS
    (SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl
    FROM Employee WHERE MgrID IS NULL
    UNION ALL
    SELECT EmpID, FirstName, LastName, MgrID, 2 as Lvl )
    SELECT * FROM BossList
  3. WITH EmpList AS
    (SELECT EmpID, FirstName, LastName, MgrID, 1 as Lvl
    FROM Employee WHERE MgrID is NOT NULL
    UNION
    SELECT EmpID, FirstName, LastName, MgrID, BossList.Lvl + 1
    FROM Employee INNER JOIN EmpList BossList
    ON Employee.MgrID = BossList.EmpID)
    SELECT * FROM EmpList

2) You have a table named Employee. The EmployeeID of each employee’s manager is in the ManagerID column. You need to write a recursive query that produces a list of employees and their manager. The query must also include the employee’s level in the hierarchy. You write the following code segment:

WITH EmployeeList (EmployeeID, FullName, ManagerName, Level)
AS (
–PICK ANSWER CODE HERE
)

  1. SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level]
    FROM Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT emp.EmployeeID, emp.FullName mgr.FullName, 1 + 1 AS [Level]
    FROM Employee emp JOIN Employee mgr
    ON emp.ManagerID = mgr.EmployeeId
  2. SELECT EmployeeID, FullName, ” AS [ManagerID], 1 AS [Level]
    FROM Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT emp.EmployeeID, emp.FullName, mgr.FullName, mgr.Level + 1
    FROM EmployeeList mgr JOIN Employee emp
    ON emp.ManagerID = mgr.EmployeeId

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 1

2) 2

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

Available at

USA: Amazon

India: Flipkart IndiaPlaza

Volume: 12345

Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Quiz and Video – Introduction to SQL Server Security

This is follow up blog post of my earlier blog post on the same subject – SQL SERVER – Introduction to SQL Server Security – A Primer. In the article we discussed various basics terminology of the security. The article further covers following important concepts of security.

  • Granting Permissions
  • Denying Permissions
  • Revoking Permissions

Above three are the most important concepts related to security and SQL Server.  There are many more things one has to learn but without beginners fundamentals one can’t learn the advanced  concepts. Let us have small quiz and check how many of you get the fundamentals right.

Quiz

1) If you granted Phil control to the server, but denied his ability to create databases, what would his effective permissions be?

  1. Phil can do everything.
  2. Phil can do nothing.
  3. Phil can do everything except create databases.

2) If you granted Phil control to the server and revoked his ability to create databases, what would his effective permissions be?

  1. Phil can do everything.
  2. Phil can do nothing.
  3. Phil can do everything except create databases.

3) You have a login named James who has Control Server permission. You want to elimintate his ability to create databases without affecting any other permissions. What SQL statement would you use?

  1. ALTER LOGIN James DISABLE
  2. DROP LOGIN James
  3. DENY CREATE DATABASE To James
  4. REVOKE CREATE DATABASE To James
  5. GRANT CREATE DATABASE To James

Now make sure that you write down all the answers on the piece of paper.

Watch following video and read earlier article over here. If you want to change the answer you still have chance.

Solution

1) 3

2) 1

3) 3

Now compare let us check the answers and compare your answers to following answers. I am very confident you will get them correct.

Available at

USA: Amazon

India: Flipkart IndiaPlaza

Volume: 12345

Please leave your feedback in the comment area for the quiz and video. Did you know all the answers of the quiz?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Four Tutorial for SQL Server 2012 New Features

One of the very common question I receive on my facebook is that if there is any tutorial for SQL Server 2012 new enhanced features and solutions. I see this demand a bit increasing as the SQL Server 2012 is more and more being adopted. Here is the list of four tutorial which is specifically created for SQL Server 2012 by Microsoft.

Multidimensional Modeling (Adventure Works Tutorial)
This tutorial teaches you how to develop and deploy an Analysis Services project that enables the employees of Adventure Works Cycles to analyze various aspects of their business.

Tabular Modeling (Adventure Works Tutorial)
This tutorial teaches you how to create a SQL Server 2012 Analysis Services tabular model that enable sales and marketing teams to easily analyze internet sales data in the AdventureWorksDW2012 data warehouse. You will build the tabular model in SQL Server Data Tools.

Tutorials and Demos for Power View
Create Power View reports and explore Power View features. View demos, videos, and tutorials that help you get started quickly with Power View and successfully build reports with interactive filters and visualizations such as bubble charts, tiles, and cards.

Tutorial: Using the hierarchyid Data Type
This tutorial is intended for users who are experienced with Transact-SQL, but are new to the hierarchyid data type. In this tutorial, you convert an existing table to a hierarchical structure, and you also create a new table to store and manage hierarchical data efficiently.

Note: The description of the course is taken from the original course description. You will need to install SQL Server 2012 AdventureWorks for all this tutorial.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Migrate a SQL Server Reports from one server to another server

How many time you have felt that there should be need of the tool which help you to migrate SQL Server Reports from one server to another server. Well, I am glad to see this migration tool for migrating reports from SQL Server 2008 R2 and later version. This tool uses powershell for migration  script. Here is the requirement of source server and target server. Source server must be native mode using Windows authentication. Target server must be SharePoint integrated mode. The web application must be using Windows classic authentication mode.

You can migrate it using any of the following methods.

  1. Command-line tool (RSMigrationTool.exe)
  2. GUI tool (RSMigrationUI.exe)

Either of the tool will generate a powershell script which will migrate the reports. This tool also generates the log file which provides the additional details regarding the migration.

Here is the link to Download the SQL Server Reporting Migration Tool.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Identify Columnstore Index Usage from Execution Plan

I think there was a time when lots of questions were coming via either email or blog comments. Nowadays, the trend seems to change. Most of the question I receive is through social media. Here is the latest question I received through Twitter. The best or worst part of Twitter is that it allows only 140 characters, so I’ve noticed that a question is easy to ask on Twitter, but an answer is difficult to provide using this social network.

The question I received at http://twitter.com/pinaldave is as follows:

How do I know if columnstore index is used by query through execution plan?

Well, my reaction was simple – “From Operators“. See, I managed to answer this in two words only. However, I was not satisfied with my own answer. The questioner did not follow up after this question but I personally did not like my answer. I felt that if he was talking about execution plan, he is indeed aware of operators. If he was aware of operators, my answer was no use to him. Suddenly, I thought I was arrogant (or at least not clear). I sent him a direct message that I would write a blog post tomorrow to explain how to figure out if columnstore index is used or not via execution plan.

If you are interested in Columnstore Index, read following related posts on my blog  for additional details:

To demonstrate this scenario, I have created the following script. I am using AdventureWorks (note: AdventureWorks Installation 60 Seconds Tutorial) for this sample database. Here are the steps which are to be followed:

  • Create a sample table
  • Insert some data
  • Create clustered index on it
  • Create nonclustered Columnstore Index on it
  • Enable execution plan in SSMS
  • Run two SELECT statement together with using clustered indexand columnstore index (use hint if needed)

Let us create environment and populate tables.

CREATE DATABASE CLAdventureWorks
GO
USE CLAdventureWorks
GO
-- Create New Table
CREATE TABLE [dbo].[MySalesOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
)
ON [PRIMARY]
GO
-- Create clustered index
CREATE CLUSTERED INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail]
( [SalesOrderDetailID])
GO
-- Create Sample Data Table
-- WARNING: This Query may run upto 2-10 minutes based on your systems resources
INSERT INTO [dbo].[MySalesOrderDetail]
SELECT S1.*
FROM AdventureWorks.Sales.SalesOrderDetail S1
GO 10
-- Create ColumnStore Index
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore]
ON [MySalesOrderDetail]
(UnitPrice, OrderQty, ProductID)
GO

Now enable the actual execution plan in SSMS.

Run following two SELECT statement together.

-- Select Table with Clustered Index (Not Columnstore)
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail] WITH(INDEX([CL_MySalesOrderDetail]))
GROUP BY ProductID
ORDER BY ProductID
GO
-- Select Table with Columnstore Index
SELECT ProductID, SUM(UnitPrice) SumUnitPrice, AVG(UnitPrice) AvgUnitPrice,
SUM(OrderQty) SumOrderQty, AVG(OrderQty) AvgOrderQty
FROM [dbo].[MySalesOrderDetail]
GROUP BY ProductID
ORDER BY ProductID
GO

Both will return the same result set. Click on Execution Plan Window.

You will see that there is different execution plan operator for regular clustered index and non-clustered columnstore index.

When you hover your mouse on both operators, they open operator tip which also clearly indicates if it is a regular clustered index and non-clustered columnstore index.

In case of the columnstore index, you will notice that the execution mode is also Batch instead of Row execution mode.

I guess now this completes the answer for the question asked to me on Twitter. Let us quickly clean up.

-- Cleanup
DROP INDEX [IX_MySalesOrderDetail_ColumnStore] ON [dbo].[MySalesOrderDetail]
GO
TRUNCATE TABLE dbo.MySalesOrderDetail
GO
DROP TABLE dbo.MySalesOrderDetail
GO

Feel free to ask me any question on social media – twitter or facebook.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A Tricky Question and Even Trickier Answer – Index Intersection – Partition Function

During yesterday’s evening, I asked a very simple question on my Facebook Page. The question was written in a jiffy and in a very light mood. While writing the question, I left a few things out, and the question did miss a few details about setup. However, as the question was not complete, it created an extremely interesting conversation in the following thread.

Here is the question: Write a select statement using a single table, using single table single time only without using join keywords, which generate execution plan with 2 join operators. Use AdventureWorks as a sample database.

I got many interesting answers to the question I posted. I must say that I learned something new from all these answers. Before I discuss my own answer, let me show the answer provided by Alphonso Jones.

Here is his answer:

SELECT Row_number() OVER (ORDER BY OBJECT_ID) num, Rank() OVER (ORDER BY OBJECT_ID DESC) num2
INTO #tmp
FROM sys.columns
-- Enable Execution Plan with CTRL+M
SELECT num, SUM(num2) OVER (Partition BY num)
FROM #tmp

When I saw this answer – I was very happy because I did not visualize it as a solution when I was asking the question. Here is the execution plan of the T-SQL code above. It’s easy to see that there are multiple joins because of the Partition Function used in the query. What an excellent participation by Alphonso Jones.

Click to Enlarge

Here is the answer which I had visualized when I asked the question. I was running the query on AdventureWorks database and executed the following query, which in turn, generated an execution plan with multiple joins:

USE AdventureWorks2012
GO
SELECT *
FROM [Purchasing].[PurchaseOrderHeader]
WHERE [EmployeeID] = 258 AND [VendorID] = 1580
GO

Look at the execution plan of the above query. You can see the joins even though I am using single table and there is no join syntax in the query.

Click to Enlarge

Personally, I liked the solution of Alphonso Jones as his solution will always generate multiple joins due to Partition Function. On the other hand, my solution is a bit tricky for it requires Indexes on the table [Purchasing].[PurchaseOrderHeader], which generates index intersection. Index Intersection is a technique which utilizes more than one index on a table to satisfy a given query.

Thanks Alphonso Jones.

Reference: Pinal Dave (http://blog.sqlauthority.com)