Cleveland DBA

SQL Server administration in Cleveland, OH

TSQL Tuesday #20: T-SQL Best Practices

Once a month, on the 2nd Tuesday, the SQL Server online community has a virtual party of sorts called T-SQL Tuesday.  T-SQL Tuesday was started by Adam Mechanic (blog|twitter) back in 2009 and has been going strong ever since.  This month, I decided to crash the party with a submission of my own.  The topic-du-jour is T-SQL Best Practices, hosted by Amit Banerjee (blog|twitter).

I’ll keep this short and sweet.

Wherever possible don’t use functions in the where clause.  Please.  Pretty please?  Why?  because it affects the way SQL Server optimizes the query and in most cases prevents proper usage of indexes.  I’ll show you what I mean.

USE AdventureWorks;
CREATE INDEX IX_Contact_LastName ON Person.Contact (LastName);

-- sad dba
SELECT LastName FROM Person.Contact
WHERE LEFT(LastName,3) = 'Alb'

-- happy dba
SELECT LastName FROM Person.Contact
WHERE LastName like 'Alb%'

Two perfectly legitimate queries, returning the same data. But take a look at the execution plans for those queries.

Even though, logically speaking, these queries are doing the same thing, the LEFT function on the first query is preventing the optimizer from using an index seek operation.  Instead, it’s scanning the entire index (much like a full table scan).  This isn’t a huge problem on such a small example, but believe me, it becomes a ginormous problem on larger tables and more complex queries.

So please, whenever possible, avoid this pitfall.  Your DBA will thank you.

July 12, 2011 Posted by | T-SQL | , , | 2 Comments

Stuff I learned today – Table Types

Since SQL Server 2000, you’ve been able to create variables of type TABLE, but you needed to define that table structure when (and each time) you declared the variable. You also couldn’t pass these table variables into a stored procedure.

SQL Server 2008 introduced the user-defined TABLE datatype. Now you can create and store custom-made TABLE types for use with variables and as parameters into your stored procedures. (They cannot, however, be used as the data type for a column within a table.)

TABLE types are created and dropped using the CREATE TYPE and DROP TYPE commands. There’s no ALTER TYPE command, so if you need to change a type, drop it and recreate it.

CREATE TYPE dbo.BookDetails
PatronID int NOT NULL,
BookID	int NOT NULL,
Title	varchar(250),
Author 	varchar(100));

So, why would you want to pass a table into a stored procedure? Imagine you work for a library and you’re writing an app to record books that people borrow, and you use a stored procedure to insert the book details into the BooksBorrowed table. Previously, if a patron borrowed multiple books, you’d have to call that procedure once for each record, right? Maybe like this:

@CustomerID int, @BookID int, @Title nvarchar, @Author nvarchar
INSERT INTO BooksBorrowed (PatronID, BookID, Title, Author, DateBorrowed)
VALUES (@PatronID, @BookID, @Title, @Author, GETDATE());

-- Now we have to run it multiple times to check out all the books Patron 1 wants

EXEC CheckOutBooks (1, 45, 'The Grapes Of Wrath', 'John Steinbeck');
EXEC CheckOutBooks (1, 532, 'Gone With The Wind', 'Margaret Mitchell');

Not very efficient, right? Now let’s try it with the BookDetails TABLE type we created earlier.

@Books BookDetails READONLY
INSERT INTO BooksBorrowed (PatronID, BookID, Title, Author, DateBorrowed)
SELECT PatronID, BookID, Title, Author, GETDATE() from @Books

-- Now we only have to run the procedure once

DECLARE @vBooks BookDetails;

INSERT INTO @vBooks VALUES (1, 45, 'The Grapes Of Wrath', 'John Steinbeck'),
			   (1, 532, 'Gone With The Wind', 'Margaret Mitchell');

EXEC CheckOutBooks @vBooks;

This reduces round-trip overhead and speeds up the entire transaction. Having a single, pre-defined definition of the TABLE type also means consistency across applications. Consistency=good. Fast transactions=gooder.

So, look it up. Play with it. Have fun, people.

June 8, 2011 Posted by | Features, T-SQL | , | Leave a comment

Stuff I learned today – hierarchyid

I’m in training this week. Since I was restricted to a local class, I decided to take one focused on development, rather than dba-related topics, and get some exposure to facets of SQL Server I don’t normally deal with on a day-to-day basis. And I figured I’d share some of my newly garnered knowledge with you.

Today’s nugget of new knowledge: the hierarchyID data type. This is a new data type in SQL 2008 that addresses the challenges of storing parent/child hierarchical relationships inside a table. Previously, developers had to use CTEs and self-joins to handle this kind of scenario. A classic example is the Employee table, where Employees have Managers who are also Employees, seen below.

USE AdventureWorks;

SELECT EmployeeID, LastName, FirstName, ManagerID
INTO EmployeeDemo
FROM HumanResources.Employee
JOIN Person.Contact ON Employee.ContactID = Contact.ContactID;

     Mgr.EmployeeID AS ManagerID, Mgr.FirstName + ' ' + Mgr.LastName AS ManagerName,
     Emp.EmployeeID AS EmployeeID, Emp.FirstName + ' ' + Emp.LastName AS EmployeeName
FROM EmployeeDemo AS Emp
LEFT JOIN EmployeeDemo AS Mgr
ON Emp.ManagerID = Mgr.EmployeeID
ORDER BY ManagerID, EmployeeID;

Another way to look at it is via a self-joining CTE. This will allow you to get the hierarchical level of the employee.

 WITH Organization(EmployeeId, LastName, Manager, HierarchyOrder)
    SELECT emp.EmployeeId, emp.LastName, emp.LastName, 1 AS HierarchyOrder
    FROM EmployeeDemo AS emp
      WHERE emp.ManagerId is Null
    SELECT emp.EmployeeId, emp.LastName, Parent.LastName, HierarchyOrder + 1
    FROM EmployeeDemo AS emp
           INNER JOIN Organization AS Parent
                 ON emp.ManagerId = parent.EmployeeId
 From Organization

That’s great and all, and pretty slick looking, but you could start to see some performance hit with a self-referencing view like that.
So let’s say I want to take my existing EmployeeDemo table and use it to create a hierarchical table, how would I do that? I’d start by creating a new table that contains a hierarchyid column.

  OrgNode hierarchyid,
  EmployeeID int,
  LastName varchar(50),
  FirstName varchar(50),
  ManagerID int

I can then use a slightly modified version of my previous CTE to populate it.

WITH Organization(path, EmployeeID)
AS (
-- This section provides the value for the root of the hierarchy
SELECT hierarchyid::GetRoot() AS OrgNode, EmployeeID
FROM EmployeeDemo AS C
-- This section provides values for all nodes except the root
CAST(p.path.ToString() + CAST(ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID) AS varchar(30)) + '/' AS hierarchyid),
FROM EmployeeDemo AS C
JOIN Organization AS p
   ON C.ManagerID = P.EmployeeID
SELECT path, o.EmployeeID, LastName, FirstName, ManagerID
FROM Organization o JOIN EmployeeDemo e ON o.EmployeeID = e.EmployeeID


So now what do I do with this new column? Well, having this new column makes the CTE unnecessary. You can traverse the hierarchy branches using built-in functions. Suppose I want to see who works under Peter Krebs, employee 21.

DECLARE @Manager hierarchyid

SELECT @Manager = OrgNode
FROM OrgChart
WHERE EmployeeID = 21 ;

FROM OrgChart
WHERE OrgNode.IsDescendantOf(@Manager) = 1
order by OrgNode;

With that query we can see that Cynthia Randall reports to Peter, and Andy Ruth reports to Cynthia, etc.

I’m definitely going to have to play around with this new datatype and all of the associated functions to get my brain wrapped around it better, but it looks pretty cool.

Some resources for further reading on the hierarchyid data type:
Populating a Table with Existing Hierarchical Data

Creating and Managing Data in a Hierarchical Table

Using hierarchyid Data Types (Database Engine)

June 6, 2011 Posted by | Features, T-SQL | , , | Leave a comment

Table Value Constructors

So I was poking around on SQLServerCentral today and I wandered into the QotD section. At random, I picked a TSQL question that introduced me to a new functionality in SQL Server 2008: table value constructors (TVC). Let me state for the record that I am not a developer (in case you hadn’t already noticed). So if I’m the last person to hear about this feature, and you’re all “duh, where the hell have you been?” feel free to move along. If you haven’t heard of this, read on.

In a nutshell, a TVC is a way of specifying multiple rows of data in a single DML statement. For example, suppose you had a table MyBooks and you wanted to insert several records into the table. In previous versions of SQL Server, you’d either have to use multiple insert statements, or a single insert with multiple SELECTs merged with a UNION ALL.

Title		varchar(100),
Author		varchar(100),
Published	varchar(4))

INSERT INTO #mybooks values('Gone With The Wind', 'Margaret Mitchell', '1936');
INSERT INTO #mybooks values('Go Dog Go', 'P.D. Eastman', 1966);
INSERT INTO #mybooks values('The Holy Bible', 'God', '');


INSERT INTO #mybooks
SELECT 'Gone With The Wind', 'Margaret Mitchell', '1936'
SELECT 'Go Dog Go', 'P.D. Eastman', 1966
SELECT 'The Holy Bible', 'God', ''

But with a TVC, you can insert multiple rows using a single INSERT statement.

DELETE #mybooks
INSERT into #myBooks VALUES ('Gone With The Wind', 'Margaret Mitchell', '1936')
							, ('Go Dog Go', 'P.D. Eastman', 1966)
							, ('The Holy Bible', 'God', '')

Be careful, though. If we query #mybooks we’ll see another new feature of SQL Server 2008.

SELECT * FROM #mybooks

Take a look at the published column for the Bible row. 0? But we inserted an empty string into a varchar column, right? In SQL 2008, you now have to be careful of implicit conversions when using a TVC (or an INSERT…SELECT…UNION ALL for that matter). From BOL:

The values specified in a multi-row INSERT statement follow the data type conversion properties of the UNION ALL syntax. This results in the implicit conversion of unmatched types to the type of higher precedence. If the conversion is not a supported implicit conversion, an error is returned.

So even though the destination column is a varchar datatype, because you’re inserting an integer (1966) SQL Server will implicitly convert all of the other values to an integer before inserting them into the varchar. (I’m sure there’s a reason for this, but it doesn’t make a lot of sense to me.)

With that in mind, if we try to execute the following insert, we should get an error.

INSERT into #myBooks VALUES ('Gone With The Wind', 'Margaret Mitchell', '1936')
							, ('Go Dog Go', 'P.D. Eastman', 1966)
							, ('The Holy Bible', 'God', 'c.34')

And we do.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘c.34’ to data type int.

But wait, there’s more! TVCs aren’t limited to INSERT statements. You can also use a TVC in the FROM clause of a query.

SELECT * FROM (VALUES('Gone With The Wind', 'Margaret Mitchell', '1936')
							, ('Go Dog Go', 'P.D. Eastman', 1966)
							, ('The Holy Bible', 'God', '')) as somebooks (title, author, published)

Or in a MERGE statement.

You probably expected an example of a MERGE statement using a TVC here.  But you're not going to get one because I'm not well-versed in MERGE statements.  Hey, I said I wasn't a developer.

April 4, 2011 Posted by | T-SQL, Uncategorized | Leave a comment

A Fairy Tale – Epilogue

When the cheers subsided and the parade was over (oh yes, there was a parade;  complete with floats and bagpipes), the DBA found herself dissatisfied.  There was a minor detail that was nagging at her.  What about those linked servers that used an alias?  With her original query, the remote server would be the alias name, and that may or may not be helpful.  Surely she could do better.

So our heroine searched the Windows registry until she found where all of the alias information was stored, and she wrote a function that used the xp_regread extended stored procedure to retrieve the target server for each alias.

IF OBJECT_ID('fn_getaliastarget', 'FN') IS NOT NULL
DROP FUNCTION fn_getaliastarget
create function dbo.fn_getaliastarget (@alias varchar(40))
returns nvarchar(250)
@regbase nvarchar(30)
,@regkey nvarchar(100)
,@dirbasepath nvarchar(250)

SET @regkey = 'Software\Microsoft\MSSQLServer\Client\ConnectTo\'

EXECUTE master..xp_regread @regbase
, @regkey
, @alias
, @dirbasepath OUTPUT

RETURN substring(@dirbasepath,CHARINDEX(',',@dirbasepath)+1,LEN(@dirbasepath))

Then she revised her query to use the new function.

SELECT @@servername as [SQLInstance]
, as [LinkedServer]
, ISNULL(dbo.fn_getaliastarget(s.data_source), s.data_source) as [RemoteServer]
, CASE l.uses_self_credential
END AS [LocalLogin]
, CASE l.uses_self_credential
ELSE l.remote_name
END AS [RemoteLogin]
FROM sys.linked_logins l
JOIN sys.servers s ON s.server_id = l.server_id AND is_data_access_enabled = 1
LEFT OUTER JOIN sys.server_principals p ON p.principal_id = l.local_principal_id
ORDER BY [LinkedServer], [LocalLogin]

And finally, the DBA wrote a version of the query for SQL Server 2000 instances, too, just for good measure.

SELECT @@servername as [SQLInstance]
, s.srvname as [LinkedServer]
, ISNULL(dbo.fn_getaliastarget(s.datasource), s.datasource) as [RemoteServer]
,  ISNULL(l.rmtloginame, 'USE SELF')     AS [RemoteLogin]
FROM master..sysoledbusers l
JOIN master..sysservers s ON s.srvid = l.rmtsrvid AND dataaccess = 1 AND isremote = 1
LEFT OUTER JOIN master..syslogins p ON p.sid = l.loginsid
ORDER BY [LinkedServer], [LocalLogin]

And she was happy. And awesome.

March 31, 2011 Posted by | General, T-SQL | , , | Leave a comment

A Fairy Tale

Once upon a time there was a developer who was concerned about the security of his application’s data.  So he decided to change the password for his application’s SQL login.  So he logged into the database and used sp_password to change his password and he was happy.

On the other end of the hall lived another developer.  This developer was not happy.  His application had suddenly stopped working and he was getting angry phone calls from users.  “But I didn’t change anything!” he cried.  And that was true.  But his application was using a linked server that mapped to the login whose password was changed by the first developer (who lived in the house that Jack built).

The second developer was very angry with the first developer.

“You can’t just go around changing passwords willy-nilly!”

The first developer got defensive.

“It’s my application and my login, how was I supposed to know you were mapped to it?”

Suddenly they stopped, and the hall got very quiet.  They both turned and looked at the DBA.

“Surely there must be some way of knowing what linked servers are mapped to what logins on what servers,” they implored.

The DBA replied, equivocally, “Let me see what I can do.”

Now this DBA was a very smart DBA (and beautiful.  like a princess, really).  She already had a program she had written that inventoried each SQL Server instance to track things like jobs, database growth, backup locations, etc.  She had even written MSRS reports against this data that listed applications on each instance, job statuses, license counts and more.  She knew it wouldn’t be difficult to gather this bit of data, too.  Once she knew where it was stored, that is.

So she started looking in the system views in the master database.  That was where sys.servers and sys.database_principals were located, after all.  It would make sense to store the linked server login mapping there, too, right?  Only, it wasn’t there!  Hmm.  Not to be discouraged, our (smart, beautiful, princess-like) heroine looked in the msdb database.  And voila!  There, in the system views:  sys.linked_logins.

And, after a bit of muttering about why the hell did they put it in msdb, she wrote the following code and included it in her program.

SELECT @@servername as [SQL Instance]
, as [Linked Server]
, s.data_source as [Remote Server]
, CASE l.uses_self_credential
END AS [Local Login]
, CASE l.uses_self_credential
ELSE l.remote_name
END AS [Remote Login]
FROM sys.linked_logins l
JOIN sys.servers s ON s.server_id = l.server_id AND is_data_access_enabled = 1
LEFT OUTER JOIN sys.server_principals p ON p.principal_id = l.local_principal_id
ORDER BY [Linked Server], [Local Login]

Now developers could know what linked servers might be impacted by a password change.  Peace and harmony reigned throughout the hall.  Hurrah!

And they lived happily ever after.

The End.

March 28, 2011 Posted by | General, T-SQL | , | Leave a comment

Plan guides and parameterization

We’ve all had this experience. A third party application is running a piece of sql that simply isn’t choosing the best query plan, and if only it would use a hash join instead of a nested loop. Or maybe it’s blocking other processes and a nolock hint would help. The problem is, you can’t change the code.

Or can you?

SQL Server plan guides effectively allow you to optimize performance of queries you can’t modify directly by attaching hints to them. Let’s say you have an application running the following query:


And after some testing, you decide it will run much better if it were using MERGE JOINs.  So you add a hint.

plan_guides_with hint

That’s all well and good, but you can’t go into the application to add that hint.  And then there’s the added complication of the usage of literal values in the sql, instead of variables.  So the optimizer will see every execution of this query for ‘Manufacturing’ as completely different from an execution for ‘Quality Assurance’.  In order for a query plan to work for any value, you’ll have to parameterize the query.  Now, you could force parameterization at the database level, but that’s a mighty big hammer for this little nail.  So, instead we’ll create a plan guide using 3 steps.

  • Step 1: We’ll use sp_get_query_template to generate a parameterized version of this query
  • Step 2: We’ll force parameterization of any query that matches this form.  
  • Step 3: We can create the plan guide. 

Sound complicated?  It’s not.  Look:

/*    Step 1: Create a parameterized version of the query. */

DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template
N'SELECT E.EmployeeID, C.LastName, C.FirstName, D.GroupName, E.Title, P.PayFrequency, P.Rate
    HumanResources.Employee E 
    inner join Person.Contact C on E.ContactID = C.ContactID
    inner join HumanResources.EmployeePayHistory P on E.EmployeeID = p.EmployeeID
            AND P.RateChangeDate = 
                (SELECT MAX(RateChangeDate) FROM HumanResources.EmployeePayHistory P2
                    WHERE P2.EmployeeID = P.EmployeeID)
    inner join HumanResources.EmployeeDepartmentHistory DH on E.EmployeeID = DH.EmployeeID
            AND DH.EndDate IS NULL
    inner join HumanResources.Department D on D.DepartmentID = DH.DepartmentID
    D.GroupName = ''Manufacturing''    AND
    E.CurrentFlag = 1
@stmt OUTPUT,            --try selecting these 2 variables to see how they get parameterized
@params OUTPUT

/*    Step 2: Force parameterization of any query that matches this form.  So, any time this 
    sql statement is executed, regardless of the literal values used, it will be parameterized.  
    Therefore all iterations of the query will look the same to the optimizer. This is critical to
    using plan guides.  */
EXEC sp_create_plan_guide N'MyTemplateGuide', 

/*    Step 3: Now that we've parameterized the query, we can apply a plan guide to it.  In this
    instance, we're giving it a hint to use a MERGE JOIN */

EXEC sp_create_plan_guide N'MyPlanGuide', 


Pretty straightforward, right?  And please note that if your query is already using parameters, you don’t need to do steps 1 and 2.  Now, let’s try that original sql again.  Notice the original code is now executing as though it has the MERGE JOIN hint:



You can see what plan guides are in your current database by querying sys.plan_guides.



And you can disable/enable a particular plan guide using sp_control_plan_guide.

disable plan_guide

And that’s it.  You’re a hero.

February 9, 2011 Posted by | SQL Tuning, T-SQL | , , | Leave a comment