Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Archive for the ‘Code Example’ 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.

Advertisements

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 »

The Deception of IsNumeric()

Posted by sqlwiseguy on April 14, 2009

Has anyone else ever used the IsNumeric() function to try to eliminate this error (or similar):

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

yet still get the error? Isn’t that what the function is for?  Here’s what is says in BOL:

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0

But run this code (generates a list of ASCII Characters):

;WITH cteNumbers AS
   
(
   
SELECT TOP 255
        ROW_NUMBER
() OVER(ORDER BY NAME) AS n
   
FROM
       
sys.all_columns AS AC
   
)
   
SELECT
       
n AS ASCII_code,
       
CHAR(n) AS [character],
       
ISNUMERIC(CHAR(n)) AS is_numeric
   
FROM
       
cteNumbers
   
WHERE
       
n <=255 AND
       
ISNUMERIC(CHAR(n)) = 1

And you’ll see that the first 10 rows and last 8 rows returned are not what I would consider numeric.  Or how about this:

SELECT
   
ISNUMERIC('20,00')
GO
SELECT
   
CONVERT(DECIMAL(9,2), '20,00')

The first statement returns 1, while the second fails. 

Here is the last interesting behavior of IsNumeric() in relation to Cast/Convert provided by Frank Kalis on this SQLServerCentral thread:

SELECT ISNUMERIC(0X0e) AS E, ISNUMERIC(0X0fAS F

E           F          
----------- -----------
0           0

(1 row(s) affected)

WHILE

SELECT CAST(0X0e AS INT) AS E, CAST(0X0f AS INT) AS F

E           F          
----------- -----------
14          15

The moral of the story is that IsNumeric() <> CanBeConvertedToNumeric().

So what is the answer?  I don’t know.  You would need to customize the solution to meet your specific situation.  Jeff Moden suggests an IsAllDigits function in this thread on SQLServerCentral for instances where you want to eliminate rows with non-numeric characters.

Do you have anything that you recommend?

Posted in Bugs, Code, Code Example, Problems, SQL Server, SqlServerCentral | Leave a Comment »

Simple, but effective code example

Posted by sqlwiseguy on December 23, 2008

The other day I answered this question at SQLServerCentral, “Need a date function to return every Wednesday of the year”, and got a couple of kudos, one from Jonathan Kehayias and one from Chris Morris (frequent contributor to the SSC forums), so I thought I’d post the code here and then explain it a bit. So here’s the code:

SELECT TOP 366
   IDENTITY
(INT, 1, 1) AS n
INTO
  
#nums
FROM
  
sys.all_objects
  
  
SELECT
      
DATEADD(DAY, n, '1/1/2008')
  
FROM
      
#nums
  
WHERE
      
DATEPART(weekday, DATEADD(DAY, n, '1/1/2008')) =
              
CASE @@DateFirst
                  
WHEN 7 THEN 4
                  
WHEN 6 THEN 5
                  
WHEN 5 THEN 6
                  
WHEN 4 THEN 7
                  
WHEN 3 THEN 1
                  
WHEN 2 THEN 2
                  
ELSE 3
              
END      
DROP TABLE
#nums

The first section just creates a temporary numbers table and you can do that several ways, I just opted for a simple one. If you don’t have a numbers table already, you should as there are many good uses for it.

Then I just run a simple select against the temporary numbers table adding each number to the first day of the year. The WHERE clause then just compares the value returned from the DATEPART function (weekday) to the value for Wednesday based on the @@Datefirst value. According to Books on line @@Datefirst:

SET DATEFIRST indicates the specified first day of each week. The U.S. English default is 7, Sunday.

Language settings affect date information.

This can also be affected by using SET DATEFIRST if you want it to be different from the default for the language. This case statement is important if you expect the code to work on any database in any SQL Server install.

Ideally to solve a problem like this you would have a dates table as Jonathan recommends in the thread.

For more information on a numbers table check out these articles:

http://www.sqlservercentral.com/articles/TSQL/62867/

http://www.sqlservercentral.com/articles/Advanced+Querying/2547/

Posted in Code Example, Numbers Table, SQL Server, SqlServerCentral | Leave a Comment »