ROW_NUMBER – How To Use It

Facebooktwitterredditpinterestlinkedinmail

ROW_NUMBER is a function built-in to SQL Server that will return a row number for each record in your result set. You can further change resulting row number to reset the row number based on some value in the result set. I will show you examples of both below.

ROW_NUMBER Basics
To show the row number in SQL Server, you need to use the ROW_NUMBER function. This function is broken down in to two parts.

  • PARTITION BY – If you supply this parameter, then the row number will reset based on the value changing in the columns supplied. This is kinda like using a GROUP BY.
  • ORDER BY – This is the specified order of the row number that you would like. If you wanted the order of the row number to increment by an employee name (alphabetically), you do that here.

 
With this function I think examples will help paint the full picture.

In the following query, the results will show a row number with each record. The number will start at 1 and increment for every record in order of AnimalName. You can see that that the order of the row number increment is specified as a parameter in to the ROW_NUMBER() function.

AnimalID AnimalName AnimalType RowNumber
166 Alpaca Mammal 1
168 Camel Mammal 2
162 Carabao Mammal 3
171 Cat Mammal 4
163 Cattle Mammal 5
184 Chicken Bird 6
182 Deer Mammal 7
185 Duck Bird 8
186 Goose Bird 9
189 Pigeon Bird 10
188 Quail Bird 11
187 Turkey Bird 12

 
In the above example, the row number never resets. It started at 1 and kept going through all the records. But what if you wanted to reset the row number back to 1 based on a value changing in your result set. In the following example, every time that the AnimalType changes we will reset the row number back to 1. This way, each AnimalType would have it’s own set of row numbers. We accomplish this by listing the columns that we want to group the row numbers by in the PARTITION BY parameter.

AnimalID AnimalName AnimalType RowNumber
184 Chicken Bird 1
185 Duck Bird 2
186 Goose Bird 3
189 Pigeon Bird 4
188 Quail Bird 5
187 Turkey Bird 6
166 Alpaca Mammal 1
168 Camel Mammal 2
162 Carabao Mammal 3
171 Cat Mammal 4
163 Cattle Mammal 5
182 Deer Mammal 6

 
 
Good Things To Know
You can specify multiple columns in the PARTITION BY and ORDER BY parameters by separating them with a comma.

You can specify ASC or DESC in the ORDER BY parameter if you like as well.

 
Reference: http://msdn.microsoft.com/en-us/library/ms186734.aspx

SQL Server Zero Pad Left

Facebooktwitterredditpinterestlinkedinmail

In a recent project, I had the task of padding a number with zeros. SQL Server doesn’t have a built in Zero Pad Left function. SQL Server does have a Right function. You can use this to create a zero padded left string.

In the code above, I am take 10 zeros, appending a text value to it, then taking the right 10 characters. The end result is the same as padding the left with zeros.

 
SQL Server Zero Pad Left Function
You can take this a step further and create a scalar function out of it.

FORCE ORDER Query Hint

Facebooktwitterredditpinterestlinkedinmail

When querying out data, SQL Server has a ton of different query hints. One less commonly known query hint is FORCE ORDER. So here we will talk about what this query hint does and how we can use it efficiently.

FORCE ORDER
When you put this query hint on to your query, it tells SQL Server that when it executes the statement to not change the order of the joins in the query. It will join the tables in the exact order that is specified in the query. Normally the SQL Server optimizer will rearrange your joins to be in the order that it thinks will be optimal for your query to execute.

To use the FORCE ORDER query hint, you will want to add this at the end of your query.

 
Example Without FORCE ORDER

This is the actual execution plan without the FORCE ORDER hint. You can see that SQL Server starts at the table Animal and AnimalHabitat tables, then goes to the Habitat table. If you look at our query above, we clearly list the tables in the order of Habitat, AnimalHabitat, then Animal.

Execution Plan Without Force Order

 
Example With FORCE ORDER

In the screenshot below, it shows the actual execution plan with the FORCE ORDER hint. In the new query, you can see that SQL Server starts with the Habitat and AnimalHabitat tables and then moves to the Animal table. It does this because when we turn on the FORCE ORDER option, it uses the order of the tables specified in the query syntax.

Execution Plan With Force Order

 
What Is This Used For
The FORCE ORDER query hint is only used when you want to override the way that SQL Server wants to execute this query. Normally you will just let SQL Server figure out how to get the data from the database. It does such a good job at it, that you do not usually need to override this functionality.

Occasionally SQL Server will not figure out the most optimal way to pull this data out. In this case you may want to try this query hint.

SQL Server Word Sound Comparison

Facebooktwitterredditpinterestlinkedinmail

As we learned in a previous post, you can use the SOUNDEX function to understand the sound that a word makes. You can then compare it to another word to see if the sound matches. This works okay for exact sound matches. But what if you want check to see if the word sound similar to another word? And how do you find out how similar the words are? SQL Server has you covered. In SQL Server 2005 they released the DIFFERENCE function.

 
The DIFFERENCE Function
When you use the SOUNDEX function in SQL Server, it returns a 4 character code that represents the sound of the word. The DIFFERENCE function compares two of these 4 character codes and returns a value between 0 and 4. The return value indicates how many of these characters in the 4 character code were the same. Here is an example:

Word SOUNDEX Code
Frog F620
Blog B420

DIFFERENCE: 2

You can see in the example above that if you compare the SOUNDEX code of the two words, only 2 characters are identical. The ‘F’ is different from the ‘B’. The ‘6’ is different from the ‘4’. But the ‘2’ and the ‘0’ are the same between the two codes. Thus… the difference is 2.

 
Put It All Together
Now that you understand how SOUNDEX and DIFFERENCE work, you can use them to create searches that are based on “sounding like a word”. Here is an example… remember, 0 means the words sound nothing alike and 4 means that the words sound exactly alike. In my example, I am going to match on a likeness of 3… which is sounding pretty similar.

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms188753.aspx

SOUNDEX – Discovering The Sounds Of Words In SQL Server

Facebooktwitterredditpinterestlinkedinmail

Let’s face it, not everyone is a perfect speller all the time. Exact match queries are pretty easy to do in SQL Server. But, what if you wanted to correct their spelling or search based on a misspelled word? How is this possible? In SQL Server 2008+ you have the ability to do this with the SOUNDEX function.

SOUNDEX
The SOUNDEX function will take an input word and convert it in to a 4 character code representing the sound of the word. For example: goat -> G300.

So what does this 4 character code mean? The first character represents the first character in the string, the rest of the characters represent the rest of the characters in the string. Of course SQL Server does some magic to come up with this code. If you are interested more in the makeup of this code, see the MSDN link at the bottom of this post. Essentially, this 4 character code represents the sound of the word.

 
How To Use It
You can use this just like any other function in SQL Server.

An easy way to search based on word sound would be something like this:

The above query would find words like “dog”, “dig”, “dag”

 
Reference: http://msdn.microsoft.com/en-us/library/ms187384.aspx