commands

SOUNDEX – Discovering The Sounds Of Words In SQL Server

by
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.

SELECT	SOUNDEX('dog') AS SoundOfDog

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

SELECT	*
FROM	Animal
WHERE	SOUNDEX(AnimalName) = SOUNDEX('dug')

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

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

Difference Between UNION and UNION All

by
Facebooktwitterredditpinterestlinkedinmail

The UNION operation and the UNION ALL operation perform almost the same operation.  They are both used to combine two result sets in to one result set.  The main difference between the two operations is that the UNION operation will return the unique records in the final result set.  The UNION ALL operation will return any duplicates in the final result set.

 
In the examples below, we will assume we have two tables with the following data.  Notice that the values Cat and Dog exist in both tables.
[table width=”0″ colwidth=”100|100″ colalign=”left|left”]
Table1,Table2
Alligator,Cat
Beaver,Dog
Cat,Eagle
Dog,Frog
[/table]

 
The UNION Operation

SELECT	*
FROM	Table1

UNION

SELECT	*
FROM	Table2

[table width=”0″ colwidth=”100″ colalign=”left”]
Result
Alligator
Beaver
Cat
Dog
Eagle
Frog
[/table]

Notice that the duplicate Cat and Dog values were removed from the result.

 
The UNION ALL Operation

SELECT	*
FROM	Table1

UNION	ALL

SELECT	*
FROM	Table2

[table width=”0″ colwidth=”100″ colalign=”left”]
Result
Alligator
Beaver
Cat
Dog
Cat
Dog
Eagle
Frog
[/table]

Notice that the duplicate Cat and Dog values are left in the result.

 
Speed Considerations

One final difference between UNION and UNION ALL is the speed difference.  Because the UNION operation needs to return a distinct set of values, this will take extra processing time.  A general rule is that if you know that the combined data is going to be unique already… just use the plain UNION ALL operation.  Only use the UNION operation if there are duplicates that you would like to have remove.
NOTE:  The UNION and UNION ALL operations are actually one operation.  The ALL keyword is just an attribute.  Because they are so different, I find it is easier to think of them both as separate operations.

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

SQL Server 2014 Feature – Timeout For Online Index Rebuilding

by
Facebooktwitterredditpinterestlinkedinmail

SQL Server 2014 comes with a new argument for how to handle the blocking issues that come from online index rebuilds.

 
Before SQL Server 2014

Before the existence of SQL Server 2014, if you were doing an online index rebuild… SQL Server would wait until the index rebuild could get the locks that it needs for rebuilding the index.  This can cause long running maintenance plans on very active tables.

 
SQL Server 2014 Online Index Rebuild Argument

The new functionality available in SQL Server 2014 tags on to the original index/table rebuild syntax.  It allows you to customize how you want to handle the blocking that occurs on the indexes/tables that are being rebuilt.  Here is a sample of the new syntax for managing the blocking on the index rebuild.

ALTER	INDEX ALL
ON		Animal
REBUILD	WITH
(
	SORT_IN_TEMPDB = ON,
	STATISTICS_NORECOMPUTE = OFF,
	ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES,
									     ABORT_AFTER_WAIT = BLOCKERS ) )
)

Notice in the statement above, the new WAIT_AT_LOW_PRIORITY flag that gets added to the ONLINE argument.  When you rebuild an index in SQL Server with the WAIT_AT_LOW_PRIORITY flag turned on, it will allow other operations to proceed while the index build waits for low priority locks.  This argument comes with 2 parameters.

 
MAX_DURATION

The MAX_DURATION parameter is how long you want to wait in minutes.  It must always be in minutes.

 
ABORT_AFTER_WAIT

This parameter comes with 3 options.  This is basically who loses if the rebuild process cannot get the locks that it needs.  The connection that you choose will be terminated after the timeout period.

  • NONE – Continue waiting for the lock
  • SELF – The rebuild operation will fail
  • BLOCKERS – All transactions that are blocking the rebuild will be killed

 
If you do not specify WAIT_AT_LOW_PRIORITY with you index rebuild, then SQL Server will automatically set it to the default of 0 minutes and NONE.

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

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

 

SQL Server Pagination

by
Facebooktwitterredditpinterestlinkedinmail

Paging through data is something that developers need to do frequently.  Until SQL Server 2012, the best way to achieve this is with the ROW_NUMBER function… and let’s face it… that wasn’t the easiest/most elegant thing to use.  Our prayers have been answered in SQL Server 2012.  The SQL Server team has come out with a better way of doing pagination using the OFFSET FETCH clause.

 
The OFFSET FETCH Clause

The OFFSET FETCH clause allows the client application to pull only a specified range of records.  To implement pagination using the OFFSET FETCH clause, it takes two parts… the OFFSET and the FETCH. 🙂

NOTE: To use the OFFSET FETCH clause for pagination, you must use an ORDER BY clause as well.

 
OFFSET

This part tells SQL Server to skip N number of rows in the result set.

SELECT	*
FROM	Animal
ORDER	BY AnimalName ASC
OFFSET	50 ROWS

The above statement tells SQL Server to return all of the AnimalNames in the Animal table, but only return the names after the 50th.  So 51 and beyond.

 
FETCH

This part tells SQL Server how many records to return in the result set.  If you use FETCH, you always need to use OFFSET.

SELECT	*
FROM	Animal
ORDER	BY AnimalName ASC
OFFSET	50 ROWS
FETCH   NEXT 10 ROWS ONLY

The above statement tells SQL Server to return the AnimalNames in the Animal table with row numbers 51-60.  The OFFSET clause tells it to skip 50 rows and the FETCH clause tells it to return 10 records.

 
Variable Row Counts

You are also able to use a variable for the record counts in the query.  If you wanted to do this, it would look like this:

SELECT	*
FROM	Animal
ORDER	BY AnimalName ASC
OFFSET	@RowsToSkip ROWS
FETCH	NEXT @RowsToReturn ROWS ONLY

 
 
Reference: http://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx

 

How To Run SQL Scripts Via Command Prompt

by
Facebooktwitterredditpinterestlinkedinmail

Usually, when we run SQL scripts, it is done on an ad hoc basis.  We usually run one file at a time just once.  From time to time there is the need to run a set of SQL script files.  One common reason I’ve seen for doing this is to rebuild a database for testing.  If you had all of your create tables/stored procedure commands stored in a series of SQL script files, then you could rebuild your database fairly easily by executing the SQL files in sequence.

 
SQL Server provides a tool to do this called SQLCMD.

The sqlcmd utility lets you run entire SQL script files full of t-sql via command prompt.  Leveraging this tool, you can create a batch file to run the sql script files in sequence.

 
 
How To Execute A SQL Script File Via Command Prompt With SQLCMD

Although there are TONS of command line options for executing a sql script.  Just executing a sql script file with the basic options is very easy.

C:\>SQLCMD -i"c:\scripts\DropTables.sql"

The above command uses the -i option to specify the input file of DropTables.sql.  Everything inside this file will be execute on the current database.

 
NOTE: When you create your SQL script files, I always recommend putting a USE statement at the top of your script.  This will ensure that you are using the correct database when your dropping and creating tables.

 
 
Run A Batch Of SQL Script Files

Now that you know how to run one SQL script file… running multiple should be easy.  All you need to do is create a batch file and run one script after the other.

 
Your batch file will look something like this:

SQLCMD /i"c:\scripts\DropTables.sql"
SQLCMD /i"c:\scripts\CreateTables.sql"
SQLCMD /i"c:\scripts\CreateStoredProcedures.sql"
PAUSE

 
 
What About Errors?

So what happens if the SQL script file throws an error?  The above commands I showed you will just ignore any errors.  If you want to capture your errors, you need to add the command line option /b.  This flag will tell the sqlcmd utility to stop processing the script and output a DOS ERRORLEVEL value.  You can then check the DOS ERRORLEVEL value after each execution.

SQLCMD /i"c:\scripts\DropTables.sql" /b
IF %ERRORLEVEL% > ( PAUSE )

SQLCMD /i"c:\scripts\CreateTables.sql" /b
IF %ERRORLEVEL% > 0 ( PAUSE )

SQLCMD /i"c:\scripts\CreateStoredProcedures.sql" /b
IF %ERRORLEVEL% > 0 ( PAUSE )

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