Blog Page

T-SQL IIF Function

sqlsafety 0 Comments

I recently posted about new functions in T-SQL CONCAT and CHOOSE.  To keep in line with these posts this article focuses on the IIF function, which is also new to SQL Server 2012.

For those who are familiar with SSRS, Excel, or Access this function is a long time coming to T-SQL.  IIF works very much like a simplified CASE statement in that it evaluates an expression and will return a scalar value based on whether the expression evaluates to true or false.

IIF accepts three arguments the first of which is the boolean expression that will be evaluated, if this does not evaluate to a boolean expression an error will be returned.  The second argument is the value that will be returned if the expression evaluates to true while the third argument is returned if the expression evaluates to false.  A basic example is displayed below which illustrates that the value “True” will be returned since 1 = 1:
SELECT IIF(1=1, ‘True’, ‘False’);

Unlike using CHOOSE IIF does not require compatible data types for both the true and false return values.  Below shows how using incompatible data types will fail using CHOOSE, but is permitted using IIF:

–Both work
SELECT IIF(1=1, 1, ‘False’);
GO

SELECT IIF(1=1, CONVERT(XML, ‘<Root></Root>’), ‘False’);
GO

–Fails with Operand type clash: xml is incompatible with int
SELECT CHOOSE(CONVERT(XML, ‘<Root></Root>’), ‘False’) AS ‘FAILURE’;
GO

The arguments passed within IIF can be static values, as shown above, variables and columns and the expression supports AND/OR:

SELECT CONCAT(FirstName, IIF(MiddleName IS NULL, ‘ ‘, ‘ ‘ + MiddleName), ‘ ‘, LastName)AS FullName
FROM Person.Person;

SELECT IIF(MiddleName IS NULL
OR
LastName LIKE ‘Abercr%’, ‘This was NULL or Abercrombie ‘, ‘ ‘ + MiddleName)
FROM Person.Person;
GO

IIF functions very much like a CASE statement and as such can be nested up to 10 levels.  The true usefulness of using IIF is that it is meant for simple evaluation and although nesting IIF is possible these situations are more easily created and readable using a CASE statement.  The below query shows nesting IIF 11 times and the resulting error message, again not pretty and is easily replaced with CASE:

NestedIIF

Another interesting point is that IIF can be remoted to other servers and is sent as a semantically equivalent CASE statement.

MSDN fully documents IIF here.

SSRS NOT LIKE expression

sqlsafety 2 Comments

I had the recent request to provide the ability to filter data in a report that was not like specific pattern.  This requirement is not uncommon or difficult when placed on the dataset using a parameter:

SELECT FirstName
FROM Person.Person
WHERE FirstName NOT LIKE @filter

The difficulty was that the report was being presented from a snapshot so the filter needed to be placed in the form of an expression.  Again, this does not seem overly difficult except that there is no NOT LIKE comparison operator in SSRS, but fortunately we can embed VB.NET in reports.

In this example I have a single report using my local instance of SQL 2008 and the AdventureWorks2008 as the data source and a dataset populated using the below query:

SELECT FirstName
FROM Person.Person

From the Report menu select Report Properties and enter the below VB.NET code in the Custom Code box:

   
Public Function NotLike(ByVal val As String, ByVal filter As String)As Boolean
If val.Contains(filter) Then
   Return False
Else
   Return True
End If
End Function

 

So you should end up with something like this

 

Now let’s get an idea of how this works by displaying the results in a table side by side with the FirstName column from our dataset.
While in the Design tab of BIDS drag and drop a table from the toolbox on the design work surface.  In the first details field of the table drag and drop your FirstName column from your dataset from the Report Data tab.  Right click the second details field and choose Expression and enter the following expression:

=code.NotLike(Fields!FirstName.Value, “ld”)

Now preview the report and take a look at what our embedded code is providing:

We can see that all first names that do not contain “ld” returns True, so they are NOT LIKE the filter text we entered, while names that do contain the filter, like Donald, returns False.

We need to create a parameter that will hold the user provided provided  filter value.  Go back to the Design tab and from the Report Data tab right click the Parameters folder and choose Add New Parameter.  The parameter will be called Comparison and the prompt will be Comparison string and will be a text data type:

From the Report Data tab right click the dataset and select Dataset Properties:

Go to the Filters tab and add a filter.  In the Expression open the expression builder and enter the below:
=code.NotLike(Fields!FirstName.Value, Parameters!Comparison.Value)
In the Value text box enter =True:

Now click the preview tab again and in the Comparison string parameter prompt enter ld and click View Report.  The data set is now filtered on the expression utilizing our embedded code only where a value of True is returned representing values that are not like the provided string comparison.

This is a rather simplistic example of utilizing embedded code to filter a report, but should provide a good starting point.  If you are interested in reviewing more in depth information on embedded code within SSRS or creating and using custom code references in expressions then please take a look at the documentation on MSDN:

SSIS Event Propagation

sqlsafety 2 Comments

Event Propagation

In a recent post outlining how to report errors in SSIS I briefly discussed event propagation in an SSIS package. Creating the sample package for this post re-introduced me to some of the intricacies and nuances of event handlers. In my initial post I provided a link to a very insightfulvideo showing event propagation, sometimes referred to as “bubbling up”, by Jamie Thomson, but decided to follow up with more detail and examples.

The easiest way to begin to understand event propagation is by creating simple script tasks in a package that displays a message box saying “Script Task is executing”.

MessageBox.Show(“Script Task is executing”);

Within the script tasks PostExecute event handler tab add a script task that, again, displays a message box saying “PostExecute event handler from Script task”

MessageBox.Show(“PostExecute event handler from Script task”);

Finally add a script task to the PostExecute event handler for the package displaying a message box, but include the system variable System::SourceName as a ReadOnlyVariable. The message box text will say “Package PostExecute event handler called from “ and concatenate the task name that has “bubbled up

clip_image002[1]

MessageBox.Show(“Package PostExecute event handler called from ” + Dts.Variables[“SourceName”].Value.ToString());

Execution the existing package will result in displaying 4 different message boxes.

1. Message box showing the script task is executing

2. Message box showing the PostExecute event handler of the script task

3. Message box showing the package PostExecute event handler of the PostExecute script task

4. Message box showing the package PostExecute event handler of the initial script task

Again to give credit where it is due, Jamie Thomson’s video outlines this first example.

This sufficiently demonstrates how ALL package executables have associated event handlers, not just control flow tasks, and how each event handler will bubble up from the lower level tasks to any associated container(s) and finally the package. In this example the script task executed a script task on PostExecute and both script task and PostExecute script task bubbled up to the package PostExecute.

The above example is simple enough, but let’s add another degree of difficulty by adding a sequence task to the existing package and placing the script task in it. Finally add a script task to the sequence containers on PostExecute event handler that will display a message box saying “PostExecute event handler from Sequence container”.

MessageBox.Show(“PostExecute event handler from Sequence Container ”) + Dts.Variables[“SourceName”].Value.ToString()) ;

Despite the fact that only a single task container was added the results display how confusing event propagation can be. Executing the package will now result in the following message boxes to be displayed:

clip_image005

clip_image006

clip_image007

clip_image008

clip_image009

clip_image010

clip_image011

clip_image012

clip_image013

clip_image014

clip_image015

clip_image016

WOW!!! There are 12 total message boxes 11 of which were raised from the PostExecute event handlers. In order to try and outline the events in their proper order let me introduce a diagram of the package, its tasks, and the event handlers.

clip_image004

The diagram shows a single package containing a sequence container that has a PostExecute event handler and a script task with a PostExecute event handler. Upon executing the package each task will have a post execute event raised. The script task will bubble up to the sequence container task, which will in turn bubble up to the package. To further cloud propagation each script task in each event handler will also raise a PostExecute event which will also propagate.

Suppressing Propagation

There will obviously come a time where you will want to suppress events from bubbling up throughout a package and there are several methods available to do this. The first method is to set the System::Propagate variable within an event handler to false. Setting the propagate variable to false indicates that the event should not be propagated to a higher level. To provide a more direct example create a package with an execute SQL task that upon successful execution goes to a data flow task. The execute SQL task will create a table in the AdventureWorks2012 database called pkerror with a single column that has a primary key.

IF EXISTS(SELECT * FROM sys.tables WHERE name = ‘pkerror’)
BEGIN
DROP TABLE
pkerror
END
CREATE TABLE
pkerror(
col1 INT PRIMARY KEY
);
GO

The data flow task should have an oledb source that uses a union query to insert duplicate values, thereby causing a primary key violation error.

SELECT 1 AS pkey
UNION ALL
SELECT 1 AS pkey

In the data flow tasks OnError event handler include a script task that references the System::Propagate variable as ReadWrite and use the C# script below in the script body:

MessageBox.Show(“OnError event handler from DuplicateKey”);

Dts.Variables[“Propagate”].Value = false;

Finally place a script task in the OnError event handler of the package that displays a message box:

MessageBox.Show(“OnError event handler from Package”);

The control flow and data flow tabs should look similar to the diagram below:

Control Flow

clip_image018

Data Flow

clip_image019

Executing the package with the OnError data flow event handler set not to propagate will result in three message boxes showing that the data flow’s OnError even is triggered.

clip_image020

This might seem a bit confusing since a message box displays three separate times, but that is because three errors are raised. What is not raised is a message box showing that the event was raised from the package level. By commenting out the:

Dts.Variables[“Propagate”].Value = false;

in the PostExecute event handler script of the script task and re-running the package will display 6 message boxes, 3 from the OnError event handler of the data flow task and 3 from the OnError event handler of the package, which are propagated from the data flow task.

Another method that can be used to block event propagation is to set the higher level event handler “Disable” property to “True”:

clip_image001

If you experiment with the PostExecute event handler you will find that this is probably more desirable than setting the Propagate variable to false, and insures that the higher level event handler will not fire. The question often arises as how to dynamically define the “Disable” property of higher level event handlers. This can easily be done by creating a variable that will be used in the event handlers “Disable” property as an expression. The value of the variable can be set to True or False from a script task the same as the System::Propagate variable.

image

If you are interested in the sample packages that I created for this post please email me at sqlsafety@gmail.com and I will send them out.
If you are interested in how propagation affects the FailParentOnFailure property please refer to this post.

SQL Saturday Puerto Rico

sqlsafety 1 Comment

I am on my flight back home to Florida and only wish I could have spent a day or two more in San Juan. This was an incredible event and my only regret is not being able to stay longer. I arrived in San Juan at my hotel late Friday afternoon and thought I would try to catch up on some sleep before the speaker/volunteer dinner. Wasn’t I surprised when I woke up at 10pm!?!? D@%*!!! I decided to suck it up and went back to sleep, what can I say? I was still hung over from SQL Saturday Costa Rica the week before.

I woke early Saturday morning, and was obviously well rested, and ready for action. After checking in I met up Jack Corbett, @unclebiguns and we discussed the fact that Florida is the best state to live in.  Sorry bro.  I saw an opening and took the shot 🙂

After the opening I attended Jack’s session on an introduction to extended events and prepared for my first session.  Jack had an awesome presentation and did a great job on presenting the basics of extended events.

My first session followed Jack’s and was focused on the new features available in T-SQL.  I was determined to prove that my intelligence and stunning good looks are only matched with my sense of humor.  THAT’S RIGHT!!! Regardless of what my family says I AM FUNNY!!  It is difficult to gauge exactly how my presentation was received, I am a bit biased, I do know for a fact that I spelled “T-SQL” correctly.  HOOYAH!!

Lunch was incredible and we ate outside on the third floor terrace and the weather was beautiful. After lunch I was able to catch part of Eddie Wuerch’s, @eddiew, session on partitioning.  I presented my final session on dealing with errors in SSIS and after was able to kick back and enjoy the rest of the event.

One of the best things about this event being held at the convention center in San Juan is the fact that there is a very nice restaurant right across the walkway where many of us met after. Amazingly enough this establishment also served cervaza. Sé!!  Coincidencia increíble!  I got to enjoy dinner with Jack, Eddie, Guillermo Caicedo, Jose Rivera, and Alan Koo.  Adding to the night’s festivities was the restaurant’s staff dressing in their best 80’s outfit, just because it was their best didn’t make it good.  We began to reminisce about the music and fashion trends of the era and were enjoying ourselves until someone reminded us how old we were!!  Jack took it upon himself to right the ship and immediately pointed out that he may be old, but I am older.  Thanks bro!!  Nice to know you got my 6.  😉

I can’t thank everyone enough for such an awesome event.  Guillermo, Alan, and everyone involved did an awesome job and made this a great experience.  I am looking forward to attending next year!

Just as a side note Jack and Eddie…  Bobby Brown was first in the group New Edition before he went on his own.  I didn’t cheat!!  I waited until I left the restaurant to Google that!

 

T-SQL PARSE Function

sqlsafety 0 Comments

Recently I completed several posts on functions new to T-SQL in SQL Server 2012, IIF, CHOOSE, CONCAT, and FORMAT, and decided it was time to expand on this theme and introduce PARSE. PARSE is new to T-SQL and relies on the presence of the .NET framework Common Language Runtime (CLR). There are two required arguments and one optional argument that are passed into the function.

  1. String value
    1. Value that is to be parsed to the defined data type
    2. Unicode variable character up to 4,000 characters
  2. Data type
    1. The data type that the string value is to be converted to
    2. See below for supported data types
  3. Culture
    1. Optional argument that identifies the culture the string is formatted in
  4. See below for culture settings

The data types that are supported to parse a value to are limited to only numeric and data and time, a full list of supported data types and cultures are outlined on MSDN here.

The easiest way to understand the way PARSE works is to begin working with it. Take a look at some of the examples and results below:

SELECT PARSE(‘$10.14’ AS MONEY USING ‘en-US’);
GO
Results 10.14

SELECT PARSE(‘€345,98’ AS money USING ‘de-DE’);
GO
Results 345.98
SELECT PARSE(FORMAT(GETDATE(), ‘D’, ‘de-DE’) AS DATETIME2 USING ‘de-DE’);
GO
Results 2013-01-16 00:00:00.0000000
SELECT FORMAT(GETDATE(), ‘D’, ‘de-DE’);
GO
Results Mittwoch, 16. Januar 2013

The queries and results are straight forward and what would be expected until the last query which formats the current date and time in German and then uses PARSE to return the German data and time back to English.

So the question of “Why include another conversion function?  We already have CAST and CONVERT.” is usually the first question I will be asked when presenting PARSE as a conversion option.  The true power and flexibility that I see  with PARSE over CAST or CONVERT is the ability to convert a string to a numeric or date and time value without having to use string functions to manipulate the input value.  Consider working with date and time values from multiple countries.  Based on the region you might be forced to manipulate the positions of the day, month, year, etc. in order to be able to convert them to a defined local.  Also consider data that has been imported from outside sources, comma delimited, Excel, or other such sources, that represents currency.  If the value(s) are preceded with a regional currency marker, “$” or “€”, then you once again would be forced to standardize the input value before applying CAST or CONVERT.

PARSE does provide an excellent means of applying consistent conversion of character data to a numeric or date and time format with little manipulation, but there are several considerations that you must take into account.  The first is that since PARSE does require the CLR so additional overhead is required and it will not be remoted, as this might cause an error on the remote server.  The other consideration to keep in mind is how PARSE treats NULL values.  When a NULL value is passed  into parse in place of the string value an error will be raised UNLESS the NULL value is passed in through a parameter or variable assignment.  The below code sample displays how PARSE treats NULL values in both cases.

SELECT PARSE(NULL AS DATETIME);
GO
Results
Msg 8116, Level 16, State 1, Line 1
Argument data type NULL is invalid for argument 1 of parse function.

DECLARE @value VARCHAR(10) = NULL
SELECT PARSE(@value AS DATETIME);
GO
Results NULL

This post will hopefully provide you with enough insight to begin using PARSE in your queries.

The above sample script can be downloaded here.

SSIS IF Expression as a CASE Statement

sqlsafety 1 Comment

SSIS expressions support using an IF kind of syntax as a means of conditional assignment.  The syntax is straight forward and documented on MSDN here:

boolean_expression?expression1:expression2

An example of using IIF in an SSIS expression would be like this:

1 == 1 ? “True” : “False”

This expression is evaluated as If 1 is equal to 1 then show “True” else show “False”.  Simple enough unless your evaluation for conditional is more complex and not just True or False.  Consider that you must make a conditional assignment in an expression of a numeric value that is between 1 and 10.  The variable assignment is made based on this logic:

If the value is between 1 and 3 the assignment should be “Small”

If the value is between 4 and 6 the assignment should be “Medium”

If the value is between 7 and 9 the assignment should be “Large”

If the value is 10 the assignment should be “Largest”

If the value falls outside the expected range the assignment should be “HUGE”

This type of logic is easy enough with a CASE statement, but unfortunately SSIS expressions don’t support CASE or have a similar function.  You could nest your IF, but that can become confusing and be quite ugly.  This statement can actually be written using the SSIS conditional syntax.  So consider an SSIS package with two variables, the first called Random holds an integer value that will be evaluated.  A variable called Case is a string and the value will be dynamically assigned based upon the value of the variable Random.  The SSIS expression to meet the above logic would be written as this in the expression of Case:

@[User::Random]  >= 1  && @[User::Random] <= 3 ? “Small”  :
@[User::Random]  > 3  && @[User::Random] <= 6 ? “Medium” :
@[User::Random]  > 6  && @[User::Random] <= 9 ? “Large” :
@[User::Random]  == 10 ? “Largest”   : “HUGE”

To illustrate how this works create a package with the variables Random and Case.  Drag and drop two script tasks on the control flow pane, one that will assign a random integer between 1 and 13 to the Random variable and display message box displaying the value and the second script task to display the value of the Case variable.

image Assign the Random variable as a ReadWrite Variable in the first Script task

image

The C# script body assigning a random value between 1 and 13 is:

Random rnd = new Random();
int value = rnd.Next(1, 13);
Dts.Variables[“Random”].Value = value;
MessageBox.Show(Dts.Variables[“Random”].Value.ToString());

Configure the second script task to show the value of the Case variable, which is dynamically set based on the Random value, in a message box.  Insure that the Case variable is configured as a Readonly variable in the script task:

image

The C# code to display the value is:

 

MessageBox.Show(Dts.Variables[“Case”].Value.ToString());

Executing the package will result in a message box showing the Random value being displayd in a message box followed by the Case value.

image

image

To insure that the Case assignment is working create another variable called loop that is an integer.  Drop a For Loop container onto the control flow pane and configure it as shown below:

image image

     Now executing the package will loop through both message boxes 10 times and display the conditional value of the Case variable based on the Random variable value.

This syntax can be used in any object that accepts expressions in SSIS including derived column transformations.

You can download the package used in this post from my site here.

 

SSISEndtoEnd

SSIS Precedence Constraints

sqlsafety 2 Comments

Precedence constraints provide a means of controlling the flow of a package based upon a tasks succeeding, failing, or completing, and offers the ability to evaluate a variable value with task completion to direct package path.  The most basic precedence constraint allows defining the flow of a package based upon task success, failure, or completion.

The following example will provide a simplistic example of a precedence constraint used only the task completion status.  The package contains a variable called Constraint that is a string data type which will be used to capture each task status.  Again to simplify this example a sequence container containing three script tasks are added to to demonstrate success and failure completions.

image     From the diagram it is obvious that There is a primary script task in the sequence container that begins the package.  Based upon the Random Success Failure script tasks completion status will dictate whether the package flow will move onto the Success or Failure script task.

In order to demonstrate the execution path configure the Random Success Failure script task set the ReadWriteVariables of the task to use the Constraint variable

image      Configure the C# script to randomly define whether the task succeeds or fails with the following code:


Random rnd
= new Random();
int value = rnd.Next(1, 3);
MessageBox.Show(value.ToString());

if (value == 1)

{
Dts.Variables[“Constraint”].Value
= “Success”;
MessageBox.Show(Dts.Variables[“Constraint”].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.Variables[“Constraint”].Value
= “Fail”;
MessageBox.Show(Dts.Variables[“Constraint”].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}

}

This code will dynamically assign a value between one and two that will, using if…else, define the task success or failure.  Configure each Success and Failure task to use the Constraint variable as a ReadOnly variable and add the message box Show method to display the Constraint variable value when either task executes.

image


MessageBox.Show
(Dts.Variables[“Constraint”].Value.ToString());

Executing the package will first display a message box showing the random value of 1 or 2

image

Then a message box will show the value assigned to the Constraint variable, Success if the random value is or False if 2.

image Finally a third message box is displayed from the Success or Failure script task again displaying the Constraint variable value.

imageimage       Precedence constraints based on task success, failure, or completion provides a means of execution path control, but is obviously limited.  Now let’s consider a more complicated execution path requirement.  To demonstrate this copy and paste the existing sequence container renaming the copy Expression and Constraint and connect the new container to the existing with the on success precedence constraint.

image     With the new sequence container added consider that a new execution path is defined.  The sequence container, and scripts within, should only be run during the week and not on weekends.  This is a common requirement and can be accomplished using a precedence constraint that evaluates a variable.

In order to accomplish this type of execution path we must first create a variable that will be used to evaluate the day of the week.  Create a variable called WeekDay that is a data type integer and uses the following expression to define its value:
(DATEPART(“dw”, GETDATE()) == 1 ||  DATEPART(“dw”, GETDATE()) == 7)  ? false : true

The above expression uses an if…else syntax that says if the day of the week is equal to 1, Sunday, OR the day of the week is equal to 7, Saturday, then the value is false, otherwise the value is true.  Refer to this post to look more at the if…else syntax in SSIS expressions and this post for more information on variables in SSIS.

The current on success precedence constraint connecting our two sequence containers will need to be configured to meet the execution logic.  From within the Precedence Constraint Editor configure the constraint to use both expression and constraint and in the expression text box enter @WeekDay == True:

image      This says that the Expression and Constraint sequence container should only be executed when the Constraint Only sequence container completes successfully and the day of the week is not Saturday or Sunday.

Finally add a message box to the Random Success Failure script task in the Constraint Only package that will display the value of the WeekDay variable:

MessageBox.Show(“Is it a weekday? ” + Dts.Variables[“WeekDay”].Value.ToString());

Executing the package results in the message boxes being displayed showing the values of the variables and also shows that if the WeekDay variable returns True then the Expression and Constraint sequence container will execute.

image      Obviously since I am working hard on a Saturday the Expression and Constraint sequence container will not execute.

image      We can now see that the precedence constraint evaluation worked and stopped despite the success of the first sequence container execution was stopped since the WeekDay variable evaluated to False.

This post outlined the use of precedence constraints and how the execution path can be defined based upon a package, container, or tasks success, failure, or completion as well as using variables to dynamically define the execution path.  You can find documentation on MSDN that provides more information on configuring precedence constraints here.

The sample package referenced in this post can be downloaded from my site here.

SSISEndtoEnd

Count Down to SQL Saturday Puerto Rico

sqlsafety 0 Comments

Less than a day left before I leave for SQL Saturday #205 in San Juan Puerto Rico!!  After just unpacking from Costa Rica last week it is time to take off again.  I am fired up to leave and am looking forward to speaking at and attending this event.  Hope to see you there!

¡Menos de un día a la izquierda antes de salir para SQL Sábado #205 en San Juan Puerto Rico!

 

TOP n, TOP n Percent And TOP n WITH TIES

sqlsafety 2 Comments

The TOP option has been available in T-SQL for some time, but enhancements were made in SQL 2005 that allow the use of a variable with TOP along with using TOP with an INSERT, UPDATE, or DELETE statement. The TOP options is ideal for limiting a data set when trying to obtain a data sample from a table(s), but can also be used to qualify a specific data set.

Consider being requested to show the 6 most expensive list prices of all available products. This could easily be done with TOP, but there are some internals to understand. The below code creates a table that is populated with the sample data from the AdventureWorks2008 database Production.Product table. A clustered index is added to the table on the ProductNumber to demonstrate some of the possible pitfalls and/or misunderstandings of using TOP.

USE AdventureWorks2008;
CREATE TABLE topcost(
Name NVARCHAR(50),
ProductNumber NVARCHAR(25),
ListPrice MONEY);
GO
CREATE CLUSTERED INDEX cl_lprice
ON topcost(ProductNumber);
GO

INSERT topcost
SELECT Name,
               ProductNumber,
               ListPrice
FROM Production.Product
WHERE ProductNumber > ‘H’

Based on the request for the 6 most expensive list prices of all products the immediate reaction is to whip up a quick and dirty TOP query. This appears easy enough, but both the request and knee jerk response need a bit more clarification to assure that the desired results are obtained. Ignoring the underlying data structure and assuming that sql server knows what is required then the below query would work:

SELECT TOP 6 Name,
               ProductNumber,
               ListPrice
FROM topcost

The results would be far from what was requested as the list price is in no apparent order:

LL Mountain Handlebars HB-M243 44.54

ML Mountain Handlebars HB-M763 61.92

HL Mountain Handlebars HB-M918 120.27

LL Road Handlebars HB-R504 44.54

ML Road Handlebars HB-R720 61.92

HL Road Handlebars HB-R956 120.27

The first issue is the fact that the table has a clustered index on the ProductNumber which enforces the physical ordering of the leaf level data. This means that the data is stored based on the ProductNumber, and as ascending or descending order was not specified in the creation of the clustered index, in ascending order. To assure that that the 6 most expensive list prices are returned the query must contain an ORDER BY clause qualifying this requirement and as the default behavior of ORDER BY is to return in ascending order the opposite, descending, must be noted:

****The presence of a clustered index does not necessarily assure that the result set will be returned in that order. If ordering is important than an ORDER BY clause should always be used to assure consistency.

SELECT TOP 6 Name,
               ProductNumber,
               ListPrice
FROM topcost
ORDER BY ListPrice DESC

The results are close to what is requested now:

HL Road Rear Wheel RW-R820 357.06

HL Mountain Rear Wheel RW-M928 327.215

ML Road Rear Wheel RW-R762 275.385

Touring Rear Wheel RW-T905 245.01

ML Mountain Rear Wheel RW-M762 236.025

HL Mountain Seat Assembly SA-M687 196.92

The only possible problem arising now is not visible unless we change the query to include a few more rows into the result set

SELECT TOP 9 Name,
               ProductNumber,
               ListPrice
FROM topcost
ORDER BY ListPrice DESC
The results now show that there are three products with a list price of 196.92 before this tie is broken with a lower price:

HL Road Rear Wheel RW-R820 357.06

HL Mountain Rear Wheel RW-M928 327.215

ML Road Rear Wheel RW-R762 275.385

Touring Rear Wheel RW-T905 245.01

ML Mountain Rear Wheel RW-M762 236.025

HL Mountain Seat Assembly SA-M687 196.92

HL Road Seat Assembly SA-R522 196.92

HL Touring Seat Assembly SA-T872 196.92

All-Purpose Bike Stand ST-1401 159.00

The initial request from for the 6 most expensive products was very specific, but the option of returning the TOP results, including ties, is often overlooked. To return a complete result set including ties requires minimal changes to the query:

SELECT TOP 6 Name,
               ProductNumber,
               ListPrice
FROM topcost
ORDER BY ListPrice DESC
The final result would be:

HL Road Rear Wheel RW-R820 357.06

HL Mountain Rear Wheel RW-M928 327.215

ML Road Rear Wheel RW-R762 275.385

Touring Rear Wheel RW-T905 245.01

ML Mountain Rear Wheel RW-M762 236.025

HL Mountain Seat Assembly SA-M687 196.92

HL Road Seat Assembly SA-R522 196.92

HL Touring Seat Assembly SA-T872 196.92

A total of 8 rows, the extra two are the result of having identical values to the 6th row. as a result of using WITH TIES.

TOP also supports using PERCENT, which returns the requested percent sampling of the result set:

SELECT TOP 5 PERCENT WITH TIES Name,
               ProductNumber,
               ListPrice
FROM topcost
ORDER BY ListPrice DESC
The above query returns 15 rows out of the 261. 13.xx rows would be 5 percent, but as WITH TIES was used the additional row(s) were returned:

HL Road Rear Wheel RW-R820 357.06

HL Mountain Rear Wheel RW-M928 327.215

ML Road Rear Wheel RW-R762 275.385

Touring Rear Wheel RW-T905 245.01

ML Mountain Rear Wheel RW-M762 236.025

HL Mountain Seat Assembly SA-M687 196.92

HL Road Seat Assembly SA-R522 196.92

HL Touring Seat Assembly SA-T872 196.92

All-Purpose Bike Stand ST-1401 159.00

ML Road Seat Assembly SA-R430 147.14

ML Touring Seat Assembly SA-T612 147.14

ML Mountain Seat Assembly SA-M237 147.14

LL Road Seat Assembly SA-R127 133.34

LL Mountain Seat Assembly SA-M198 133.34

LL Touring Seat Assembly SA-T467 133.34

In SQL Server 2005 enhancements were made to the TOP option including allowing the ability to pass an expression to TOP, there by no longer requiring using dynamic t-sql for such a query:

DECLARE @i BIGINT
SET @i = 6
SELECT TOP (@i) Name,
               ProductNumber,
               ListPrice
FROM topcost
ORDER BY ListPrice DESC

The results set is again 8 rows.

******Unlike passing a static numeric value with TOP a variable must be passed between parenthesis in order to parse. Using parenthesis with numeric values will also parse and may be good practice to assure consistency in writing your queries.

SQL 2005 also introduced the ability to use TOP in INSERT, UPDATE, DELETE. There are several ways to implement this, the below creates a new table mirroring the topcost table, created above, to exhibit the differences in each technique:

CREATE TABLE trans(
Name NVARCHAR(50),
ProductNumber NVARCHAR(25),
ListPrice MONEY);
GO
INSERT TOP (2) INTO trans

SELECT Name,
               ProductNumber,
               ListPrice
FROM topcost;
GO
The above use of TOP in the INSERT statement will insert the the first two rows from the result set based on the order the results are returned:

LL Mountain Handlebars HB-M243 44.54

ML Mountain Handlebars HB-M763 61.92

If you were again tasked with inserting the top 6 rows based on the most expensive list price then the below query would be more appropriate:

INSERT trans

SELECT TOP 6 WITH TIES Name,
               ProductNumber,
               ListPrice
FROM topcost
ORDER BY ListPrice DESC ;
GO
This will insert the 8 expected rows based on the ListPrice in descending order.

The below will clean up the tables used for these examples

DROP TABLE topcost;
GO
DROP TABLE trans;
GO


Hit Counter provided by Sign Holders