Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘Programming’ Category

On vs. Where

Posted by sqlwiseguy on September 30, 2009

Does it matter if you put your criteria in the ON clause or the WHERE clause?  Well, as with most things SQL the answer is, “It depends”.  If you are dealing with INNER JOIN’s then it really doesn’t matter because the query optimizer is smart enough to come up with the same execution plan for both.  For example these 2 queries evaluate to the same execution plan:

SELECT
SOD.SalesOrderDetailID
,
SOH.SalesOrderID
FROM
Sales.SalesOrderHeader AS SOH
JOIN
Sales.SalesOrderDetail AS SOD
ON
SOH.SalesOrderID = SOD.SalesOrderID
WHERE
SOH.OrderDate >= '7/1/2004'
AND
SOH.OrderDate <
'8/1/2004'

SELECT
SOD.SalesOrderDetailID
,
SOH.SalesOrderID
FROM
Sales.SalesOrderHeader AS SOH
,
Sales.SalesOrderDetail AS SOD
WHERE
SOH.SalesOrderID = SOD.SalesOrderID
AND
SOH.OrderDate >= '7/1/2004'
AND
SOH.OrderDate < '8/1/2004'

Execution Plan

The old SQL 6.5 OUTER JOIN syntax (*= and =*) has been discontinued beginning with SQL Server 2005, so you have to do the JOIN for OUTER JOIN’s in the ON as demonstrated in this code:

SELECT

   SOD.SalesOrderDetailID,

   SOH.SalesOrderID

FROM

   Sales.SalesOrderHeader AS SOH LEFT JOIN

   Sales.SalesOrderDetail AS SOD

        ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE

   SOH.OrderDate >=‘7/1/2004’ AND

   SOH.OrderDate <‘8/1/2004’

Now let’s create a sandbox to play in.

If OBJECT_ID('sales.orders', 'U') Is Not Null
Begin
Drop Table
sales.orders;
End;

If OBJECT_ID('sales.order_items', 'U') Is Not Null
Begin
Drop Table
sales.order_items;
End;

If SCHEMA_ID('sales') Is Not Null
Begin
Drop Schema
sales;
End;

Go

Create Schema
sales;

Go
/*
Tables to hold sample data
*/
Create Table sales.orders
(
order_id INT IDENTITY(1,1)PRIMARY KEY,
customer_id INT
);

Create Table sales.order_items
(
order_detail_id INT IDENTITY(1, 1)PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT
)

/*
Load Sample data
*/
INSERT INTO sales.orders (customer_id)
SELECT TOP 5
AO.[object_id]
FROM
sys.all_objects AS AO;

INSERT INTO sales.order_items
(
order_id,
product_id,
quantity
)
SELECT
1,
1,
7
Union ALL
Select
2,
1,
4
Union ALL
Select
3,
2,
6
Union ALL
Select
4,
2,
11
Union ALL
Select
5,
3,
1;

Now we want to return all the customers who have placed an order, but we only want to return the items where the quantity is greater than 5.  Here is method 1:
Select
O.customer_id,
OI.order_id,
OI.product_id,
OI.quantity
From
sales.orders AS O LEFT OUTER JOIN
sales.order_items AS OI ON
O.order_id = OI.order_id
Where
OI.quantity > 5;

This returns:

customer_id order_id    product_id  quantity
----------- ----------- ----------- -----------
3 1 1 7
7 3 2 6
8 4 2 11

Hmmm, we know we have orders from five customers, but this only returns the three rows.  Let’s look at the execution plan:

image

What’s that nest loops (inner join) operator?  Well, by putting the criteria for the RIGHT (second) table in the WHERE clause we essentially converted our LEFT OUTER JOIN to an INNER JOIN.  The correct way to get the data we want would be this way:

SELECT

    O.customer_id,

    OI.order_id,

    OI.product_id,

    OI.quantity

FROM

    sales.orders AS O LEFT OUTER JOIN

    sales.order_items AS OI ON

        O.order_id = OI.order_id AND

        OI.quantity > 5;

This returns what we would expect to see:

customer_id order_id    product_id  quantity
----------- ----------- ----------- -----------
3 1 1 7
5 NULL NULL NULL
7 3 2 6
8 4 2 11
17 NULL NULL NULL

And here is the execution plan:

image

Where you can see the Nested Loops (left outer join) operator.

So yes, it does matter where you put your criteria when dealing with OUTER JOINS.

Posted in Code Example, Development, Programming, SQL Server | Leave a Comment »

Venturing into Powershell

Posted by sqlwiseguy on September 23, 2009

As you probably know if you read my blog regularly, I recently put together a presentation called, Dive into the Default Trace, and as part of that presentation I wanted to provide examples of different ways to archive the Default Trace files.  Here are the option I considered:

  1. fn_trace_gettable to load the data into a table.
  2. SSIS to archive the files.
  3. PowerShell to archive the files.
  4. xp_cmdshell and the copy command.

I immediately eliminated #4 because I don’t have xp_cmdshell enabled and because Chad Miller (@cmille19) had said in his Powershell talk that anything that requires xp_cmdshell is a good candidate for Powershell.  So I sent Chad an email asking him where to start and he was kind enough to help out.  I got a “working” script together and sent it off to Chad for review and he kindly told me my errors (there were several) and included a modified script that cleaned up my code.  I did make a couple of minor modifications after receiving the script from Chad.  The idea and starting point were mine, even if much of the finished product is Chad’s.  So here’s the code (please comment if you have any improvements):

# Written with much Assistance from Chad Miller 
# (http://chadwickmiller.spaces.live.com/Blog)
param ($serverName
)

#######################
function
Get-SqlData
{
param([string]$serverName=$(throw 'serverName is required.'
),
[
string]$databaseName=$(throw 'databaseName is required.'
),
[
string]$query=$(throw 'query is required.'
))

Write-Verbose
"Get-SqlData serverName:$serverName
databaseName:$databaseName query:$query"

$connString =
"Server=$serverName;Database=$databaseName;
Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString
)
$dt = New-Object
"System.Data.DataTable"
[void]$da.fill($dt
)
$dt

}
#Get-SqlData

#######################
function
Get-TraceInfo
{
param($serverName
)

$qry =
@"
SELECT
RIGHT(T.path, CharIndex('\',
Reverse(T.path))-1) as current_file,
Substring(T.path, 1, Len(T.Path) -
CharIndex('\', Reverse(T.path))+ 1) as trace_path
FROM
sys.traces T
WHERE
T.is_default = 1
"@

Get-SqlData $serverName 'master'
$qry

}
#Get-TraceInfo

#######################
# MAIN
#######################

if ($serverName -eq $null
)
{
$serverName =
$env:COMPUTERNAME
}

$result = Get-TraceInfo
$serverName
$copy_path
= $result.trace_path +
'Archive'

if ((Test-Path -Path $copy_path) -eq $false
)
{
New-Item -Path $result.trace_path -Name 'Archive' -ItemType
directory
}

Get-ChildItem -Path $result.trace_path -Exclude $result
.current_file |
where {$_.name -like "*.trc"} | Move-Item -destination
$copy_path

Once I knew what cmdlet’s I needed the PowerShell was probably the second easiest one to implement.  I know SSIS fairly well and it got a bit complex because of having to exclude the file currently in use.  Using fn_trace_gettable is simple because it allows me to use T-SQL which I have many years experience in.

Using PowerShell is really a matter of knowing what cmdlet’s you need, once you have that, the built in help (get-help) is very good, so you can just about do anything.  PowerShell is very powerful and flexible.  It can actually do so much that I think you need to be careful.  For example, the New-Item cmdlet can create just about any item depending on the ItemType you use.  ‘

So while I definitely recommend that any DBA learn some PowerShell I also recommend being very careful and Test, Test, Test!

Posted in Code Example, Development, PowerShell, Professional Development, Programming, Scripts, SQL Server | 3 Comments »

Learning some javascript

Posted by sqlwiseguy on July 24, 2009

As part of my work I need to be a “Jack of all trades” meaning I not only need to be able to do SQL Server DBA and development tasks, but I also need to be capable of doing .NET and VFP development.  Yes, I did say VFP (Visual FoxPro for those who are too young to know what it is). 

The major project in which I am involved is using SQL Server and ASP.NET (C#) MVC.  Fortunately I had downloaded Scott Guthrie’s NerdDinner tutorial when it first came out and have just finished going through it.  In the tutorial I came across some code like this:

if (item.Description !== undefined) {
description = item.Description;
}

Well, I had never seen the !== operator before and assumed that it was a typo and was supposed to be:

if (item.Description != undefined) {
description = item.Description;
}

since I know that == the equality comparison operator and != is the inequality comparison operator.  Well, then I found the same operator later in the code, so I thought, “Hmmm, maybe this isn’t a typo, but a real operator?”, so off to www.google-vs-bing.com to see what I could find.  Well, I didn’t find anything helpful on page 1 of either search engine’s results.  Next a quick question on Twitter.  Ahhh, several replies that, “Yes !== and === are operators and they compare type AND value”.  Having always programmed in strongly typed languages this is new and interesting to me.  So basically in javascript 1 == “1” is true, but 1 === “1” is false.

I still would rather be in SQL, but at least now I am starting to understand more about javascript.

Posted in Development, Professional Development, Programming, SqlServerCentral | 1 Comment »

An Explanation of Why to Use Stored Procedures

Posted by sqlwiseguy on May 9, 2009

I wish I was a smart as the guys I interact with on sites like SQLServerCentral and TwitterPaul Nielsen, @PaulNielsen on Twitter, has an excellent blog post today on Why Use Stored Procedures that I think is a must read for everyone involved with SQL Server.  I am a huge advocate of stored procedures and Paul does much better job explaining the why’s than I ever could. 

So thanks Paul, now I have someplace to point the nay-sayers.

Posted in Blogs, Development, Lessons, Programming, SQL Server, Standards | Leave a Comment »

Software Developers, Remember that People Need to Use Your Software

Posted by sqlwiseguy on May 7, 2009

As a hybrid DBA\Developer I had the message of the title reinforced through a negative experience with a web site this past week.  Please bear with me as I tell a fairly long story/rant to illustrate my point.

The web site I had an issue with is www.sunpass.com.  SunPass is Florida’s prepaid toll system and they provide a web site to manage your account.  There are 2 devices you can purchase, a portable transponder and a SunPass Mini Sticker transponder which applies to your windshield and is only good for 1 vehicle.  My wife and I purchased a SunPass Mini and I went to the web site and activated it and I set up my account so that it would auto-replenish using one of my credit cards when the balance dipped below a certain threshold. 

Well, last week we traded in the vehicle that had the Mini in it, so we bought a new Mini for our new vehicle.  This is when the trouble started.  I went to the SunPass web site to activate the new Mini.  I logged into my account and nowhere on my account home page was there a link to activate a new transponder or remove the old transponder.  I thought, “Well, the package says I can activate it online and I activated the first one online so I know it has to be here somewhere.”  So I double–checked and nope, not there.  I went back to the SunPass home page and, lo and behold, the “Existing Customers activate a New Transponder” link was on that page instead of my account home page.  Alright that’s not the most intuitive interface, but I only spent about 10 minutes trying to find it, but at least I found it.  So I clicked on the link and here is what happened:

SunPassError Wait a minute, I don’t have a commercial account, what’s going on?  So I lookup the customer agreement and here is the definition of a commercial account:

5.2 Commercial Accounts: Primarily for companies or businesses with corporate owned, leased or rented vehicles and/or trailers. A current Federal Employer Identification Number (FEIN) must be provided to open an account of this type. FEIN information must be kept current and may be requested by a SunPass® representative at any time. The minimum opening balance for a Commercial Account is dependent on the User’s estimated monthly usage, but must be a minimum of $50.00. User shall be notified of any changes to the required replenishment amount on their monthly Account Summary Statement. For Commercial accounts to qualify for tax exempt status, User must provide a valid tax exemption certificate at the time of account activation.

Nope, I didn’t provide any of this information when I setup my account so, why am I getting this message?  I found the contact us link and sent them a message asking why I was getting the message.  As expected I got a non-response response:

Please call customer service

The next day I got a letter from SunPass telling me that I had a negative balance and I needed to call them to pay up.  Now I’m not only confused, but I’m a getting a bit ticked off, I mean, I did setup my account to auto-replenish, why isn’t it happening?  Back to the web site to make sure auto-replenish is setup, yup, it is.  So I call customer service to deal with the issues.   Here are the answers I got:

  1. Can’t add transponder because I have a commercial account.
      The reality is that when they added the new Mini’s they could not treat them like regular transponders, why I don’t know, so if you open your account with a Mini your account is treated like a commercial account.  So you have to call to activate any new Mini’s on your account.  I asked “How was I supposed to know that?” and the answer is you have to call.  So I suggested that they fix the message to tell me the REAL reason the web site doesn’t do what asked.
  2. Can’t delete the old transponder.
      Again, because it is a Mini it is treated differently and they assume you will disable it by taking it off the windshield so you don’t need to ever disable the transponder.  I asked, “How was I supposed to know that?” and the answer is you have to call.
  3. Auto-replenish isn’t working.
      Apparently the system had a “glitch”, so while the web site is showing that auto-replenish is setup it really never was.  I asked, “How was I supposed to know that?” and the answer is you have to call.

So now to the point of the post. What could the developers/designers done differently to provide a better user experience.

  1. Put account related activities on the account home page.  So an add transponder link on my account home page in addition to the main home page would have made life much simpler and intuitive.
  2. Provide a message that conveys the real reason functionality is not available.  Tell me that you cannot add a Mini to an existing account, not that I have a Commercial account.  In my opinion, the real answer is to not provide the functionality at all.  If account type = Commercial do not show the Add transponder link.
  3. Catch errors and tell the user that their efforts were unsuccessful.  If something goes wrong in setting up auto-replenishment tell me!  If it happens in a batch at the end of the day, send me an email telling me it failed.  Don’t wait to send me a letter when I’m already in the hole.

Please keep the end user in mind when designing and developing your applications.  Don’t frustrate your customers, whether internal or external, with poor design.

Posted in Expectations, Lessons, Problems, Programming, Rants, Standards, Suggestions, Technology | 1 Comment »

JumpstartTV Featured Content

Posted by sqlwiseguy on April 21, 2009

My video, Using fn_trace_gettable to Import Trace Results, is the featured content on JumpstartTV today.  Check it out.  JumpstartTV is a site that features short (< 5 min) how to videos focused on SQL Server.

Posted in Community, JumpstartTV, Online Presence, Programming, Published Articles, SQL Server | Leave a Comment »

Error Handling in SSIS re-featured on SQLServerCentral

Posted by sqlwiseguy on April 3, 2009

I am honored that my first ever published article, Error Handling in SSIS, was re-featured on SQLServerCentral today.  I don’t know how Steve selects articles to feature a second time, but I assume it was because they were popular.  Here are a couple of earlier blog posts about this article/subject:

Error Handling in SSIS Published

SSIS Error Logging Custom Component

Posted in Development, Professional Development, Programming, SQL Server, SqlServerCentral, SSIS | Leave a Comment »

What’s ownership chaining?

Posted by sqlwiseguy on March 25, 2009

Edit: 04-27-2009 K. Brian Kelley corrrectly pointed out in a comment that objects can still be owned by users in 2005 and later.  See his blog post to see how.

Edit:  While the code section is not as nice as I would like it is better.  Thanks for sticking with me.

I recently answered a question on SSC about how you granting execute rights on a stored procedure allows you to access tables without having any permissions granted on the tables.  The way that SQL Server implements this it through ownership chaining.  Here is the BOL (SQL Server 2005) definition of Ownership Chains:

When multiple database objects access each other sequentially, the sequence is known as a chain. Although such chains do not independently exist, when SQL Server 2005 traverses the links in a chain, SQL Server evaluates permissions on the constituent objects differently than it would if it were accessing the objects separately. These differences have important implications for managing security.
Ownership chaining enables managing access to multiple objects, such as multiple tables, by setting permissions on one object, such as a view. Ownership chaining also offers a slight performance advantage in scenarios that allow for skipping permission checks.

How Permissions Are Checked in a Chain

When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Prior to SQL Server 2005, objects were owned by users, and, in most cases, designers and developers used the dbo user to own common objects.  In my experience it was very rare for objects to be owned by a “real” user. Thus if the customers table and orders table are both owned by dbo, then you can create a customer_orders_get stored procedure owned by dbo, grant user Joe execute permissions on the stored procedure, and Joe would be able to successfully use the stored procedure to query the customers and orders tables without having select permissions on the tables.  This is pretty easy to understand, as long all the objects are dbo.object_name I can access them using ownership chaining.
SQL Server 2005 introduced schemas which removed the use of users as owners.  Now objects belong to schemas, not users.  From BOL:

Beginning in SQL Server 2005, each object belongs to a database schema. A database schema is a distinct namespace that is separate from a database user. You can think of a schema as a container of objects. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

The question now is, how does ownership chaining work when using schemas?  As the last line of the BOL quote states, a schema is owned as well, and ownership chaining in SQL Server 2005 and greater is based on schema ownership.  So if schema A is owned by dbo then you would be able to use ownership chaining to access objects in dbo from schema A and vice versa.  I think the best way to explain is with an example:

USE [master]
GO

-- drop the DB if it exists
IF EXISTS ( SELECT
name
FROM
sys.databases
WHERE
name = N'oc_test' )
DROP DATABASE [oc_test]
GO

USE [master]
GO

-- create the test database
CREATE DATABASE [oc_test] ON PRIMARY
(
NAME = N'oc_test',
FILENAME = N'C:\Data\oc_test.mdf' ,
SIZE = 194560KB,
MAXSIZE = 1024000KB,
FILEGROWTH = 10240KB
)
LOG ON
(
NAME = N'oc_test_log',
FILENAME = N'C:\Logs\oc_test_log.ldf',
SIZE = 204800KB,
MAXSIZE = 204800KB,
FILEGROWTH = 10240KB
)
GO

-- switch to the new databse
USE oc_test ;

GO

-- drop the user if it exists
IF EXISTS ( SELECT
1
FROM
sys.database_principals AS DP
WHERE
DP.[name] = 'test_user' )
BEGIN
DROP USER test_user ;
END

-- create our test user
CREATE USER test_user WITHOUT LOGIN
WITH DEFAULT_SCHEMA = dbo ;
GO

-- drop proc and schema
IF OBJECT_ID('ownership_chaining.table_one_ins')
IS NOT NULL
BEGIN
DROP PROCEDURE ownership_chaining.table_one_ins ;
END

GO

IF SCHEMA_ID('ownership_chaining') IS NOT NULL
BEGIN
DROP SCHEMA ownership_chaining ;
END
GO

-- create our test schema, notice it is "owned" by dbo
CREATE SCHEMA ownership_chaining AUTHORIZATION dbo ;

GO

-- drop our test table if it exists
IF OBJECT_ID('dbo.table_one') IS NOT NULL
BEGIN
DROP TABLE dbo.table_one ;
END

/*
create a test table NOTICE it is in the dbo
schema which is owned by dbo
*/
CREATE TABLE dbo.table_one
(
column_one INT IDENTITY(1, 1),
column_two VARCHAR(10) NOT NULL
) ;

GO

/*
Create the test procedure in the
ownership_chaining schema which
is owned by dbo
*/
CREATE PROCEDURE ownership_chaining.table_one_ins
@column_two VARCHAR(10)
AS
INSERT INTO
dbo.table_one (column_two)
VALUES
(@column_two) ;

RETURN ;

GO

/*
give out test user execut permissions on the
procedure
*/
GRANT EXECUTE ON ownership_chaining.table_one_ins
TO test_user ;

GO

-- change user context
EXECUTE AS USER = 'test_user' ;

-- execute the procedure
EXEC ownership_chaining.table_one_ins
@column_two = 'Test 1' ;

-- go back to my login
Revert ;

-- verify that it worked which it should
SELECT
*
FROM
dbo.table_one AS _TO ;

/*
Now change the owner of the ownership_chaining
schema so that it is no longer owned by dbo
thus breaking the chain
*/
ALTER AUTHORIZATION ON SCHEMA::ownership_chaining
TO db_owner ;

-- change user context
EXECUTE AS USER = 'test_user' ;

/*
using try - catch so I get the revert as
I expect failure
*/
BEGIN TRY
EXEC ownership_chaining.table_one_ins
@column_two = 'Test 1' ;
END TRY
BEGIN CATCH
-- show the error message
SELECT
ERROR_MESSAGE() AS error ;

-- return to my user
revert ;
END CATCH

-- verify data
SELECT
*
FROM
dbo.table_one AS _TO ;
I hope this helps clear up what I found to be a somewhat confusing topic.

Posted in Development, Programming, SQL Server, SqlServerCentral | 6 Comments »

How’s your commentary?

Posted by sqlwiseguy on January 7, 2009

Steve Jones had a good editorial on SQLServerCentral, A Dearth of Comments, this past Monday. One way I measure a good editorial is the discussion it generates and this one generated an interesting one.

There are generally 2 camps for commenting code:

  1. My code is self-documenting.
  2. People in this camp usually have more verbose method, property, and variable names.

  3. I want the next person to know what I was doing and why. Oh, and I want to be able to remember when I go back in 6 months.
  4. People in this camp sometimes over comment. This folks will take a Insert Into table Select From tableB and put this comment at the top “this inserts a row into tableA from tableB”.

I have to admit that in practice I am more in the first camp, although I’d like to move toward, but not all the way into the second camp. When I was learning to program, my boss\mentor was and, to the best of my knowledge, is firmly entrenched in the first camp. The fact that I have had to spend some time debugging and upgrading his code certainly is playing a part in my desire to do more commenting. Here are a couple of things I picked up from the SSC discussion that I am going to try to implement as I move forward:

  1. Comment why I am doing what I am doing and why I am doing it this particular way. Any good developer should be able to figure out what the code does, but not necessarily the why. Thanks Roger Plowman and those who seconded his comment.
  2. Comment so a novice, either in programming or business knowledge, can understand what the code does and why. Several people mentioned it in the thread.
  3. Create the why and how comments BEFORE beginning. Really this is putting together a plan, and will probably be the hardest one for me as I like to “dive” right in. Thanks Jay Holovacs and others who mentioned this.
  4. Push for and get an intranet Wiki or Knowledge Base. My first job had one and it was great. Where I’m at now does not, which means the developer who wrote it, supports it. Not that I don’t want to support my code, but I don’t need to be the only one who can do it. Thanks again to Roger Plowman and others.

I’ve always done a good job with a header on stored procedures and triggers (see this post), but not within the code or in my VB or C# code.

So now you know that my “commentary” is mediocre at best, but how’s yours?

Posted in Programming, SqlServerCentral, Suggestions | Leave a Comment »

Triggers in SQL Server

Posted by sqlwiseguy on August 28, 2008

As I was browsing the active threads on SQLServerCentral today I happened on this post about a problem with a trigger. I did not post on the thread because someone had already made the point I would have made. The problem was that the poster had created a trigger that would not work on a batch insert or update. Something like this:

CREATE TRIGGER test_ins_upd ON dbo.test
After
INSERT, UPDATE
AS

DECLARE @test_id INT,
@test_name VARCHAR(25)

SELECT
@test_id = test_id,
@test_name = test_name
FROM
inserted

IF @test_name LIKE 'a%'
BEGIN
--Do something
END
ELSE
BEGIN
--Do something else
END

I seem to see this mistake on the majority of forum posts about triggers. SQL is a set-based language and triggers deal with sets as well. I have to admit that when I was new to SQL Server I made the same mistake, but only once. I had thought that since we only allowed access to the database through stored procedures, and these procedures only acted on 1 row that I would be safe. Well, I quickly learned I was wrong. What about what I did? Or, another developer?

The first thing a new person to SQL needs to learn and really understand is that their code needs to work with sets. This will help newbies to write better performing code and help them avoid errors like this one.

Here are a couple of good blog posts about triggers:

The Trouble with Triggers by Conor Cunningham
Triggers…Evil? by Louis Davidson

Posted in Programming, SQL Server | Leave a Comment »