I found an awesome SQL search tool from Red Gate this morning. SQL Search 1.0, which is free at this stage, integrates in with SQL Management Studio and presents itself as a toolbar. Click the search button and a SQL Search document tab is create allowing you to search for text (including exact match option). You can specify an object type (or all objects) and a database (or all databases) on the selected server.

The search is blindingly fast as well. Even across all databases on a server. In my case 25 databases on this particular SQL2008 instance.

You can download from http://www.red-gate.com/products/SQL_Search/index.htm/.

Attended Wardy's SQL talk this morning about SQL Server 2008 consolidation.

Many reason's to consolidate not least that you can save on SQL Server license fees.

A few very handy tools emerged that were suggested for use when determining if consolidation is appropriate but could also be used when investigating performance problems and tuning SQL Servers.

The first is Microsoft Assessment and Planning Toolkit here. This tool does more than just document your SQL instances. Good option for an IT audit document.

The other tool specific to SQL Server performance tuning is SQLIO (Disk Subsystem Benchmark Tool) here.

This tool places you SQL Server under artificial load to stress the IO subsystem under configurable circumstances. You can simulate reads and writes, specify the number of threads to use, specify the io queue length, specify random or sequential writes and more.

Random writes simulate inserts and updates where as sequential writes simulate log file writes.

I came across an interesting use of the row_number function today. Say you have a situation where you want to select the first address for an account that may have multiple addresses. You can use the row_number() function to create a query table and then select where row number = 1.

Previously you would have had to create a temporary table in a stored proc to achieve this. Very neat being able to do it this way!

SELECT *

FROM Account a

LEFT JOIN (SELECT *, row_number() OVER(partition BY AccountID

ORDER BY CASE AddressType WHEN 'main' THEN 0 WHEN 'mailing' THEN 1 ELSE 2 end) AS 'RowNumber' FROM AccountAddress ) AS addr

ON a.accountid = addr.accountid AND addr.rownumber = 1

I needed to find the length of an image column in the database. You can use the LEN function on varbinary columns but not image columns.

The DATALENGTH function however does work very nicely. 

Just a small gotcha with updateable transaction replication which I only found out the hard way, which is, Text columns are not updateable at the subscriber! Tar and feather me if everyone but me knew this but it's a pretty big gotcha if you ask me.

Luckily varchar(MAX) to the rescue and row sizes about 8092! All good after that. 

Tried to find Surface Area Configuration in SQL Server 2008 just now and found that it wasn't there. After a bit of searching I found it in

SQL Server Management Studio -> Server (right click) -> Facets. The last facet in the drop down is Surface Area Configuration.

A wall poster of the SQL Server 2008 System View has been produced. You can download in PDF or XPS format. Very cool!

Link to System Views Wall Poster (MS Site)

Had a long running process that was kicked off from an ASP.NET page which was getting the request timeout error below:

Request timed out.

Description: An unhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about the error andwhere it originated in the code.

Exception Details: System.Web.HttpException: Request timed out.

Source Error:

Stack Trace:

I knew that the SQL was not timing out because I would hgacve 

I was able to solve this by adding a httpRuntime element called executionTimeout to the web.config under the system.web section. This value is in seconds and the default is 90 (for .NET 1.0 and 1.1)  or 110 otherwise. 

The full specification for httpRuntime can be found here. 

Recently I found a wrapper around the google geocode API here. Which got me thinking about map coordinates of Australian suburbs and being able to do distance from or surrounding suburbs searches for starters.

One thing I found with using this wrapper is that I had to go to google and register for access to use the API (and I was issued a key to use with the wrapper). To register for the key I had to enter the URL that would be making calls to the Google API but I found that the calls worked either from a win forms app or an ASP.NET website both running locally (and in debug mode).

I then found a site that publish a CSV file (amoungst others) with all Australia post codes and their lats and longs here.

The only thing left was to find a routine that calculated the distance between 2 lat and longs. I have attached the SQL CLR user defined function source file to the bottom of this article. 

The only thing left was to create a SQL function that returned the results. Simply pass in the postode you are starting from and the distance you want to search from the postode. I group by postcode and get the max distance because in most instances there postcodes that cover multiple suburbs. This function retrieves the "distinct" list of souurounding suburbs.

I'm sure there can be improvements in my implementation.

GeoDistance.cs (1.17 kb)

Greg Low did an amazing talk at Tech.Ed about full text seach (FTS) in SQL2008. I hadn't had much exposure to FTS so it was good to see what it had to offer. I thought it was just an efficient way to do a like search in a text field. It's so much more. 

For example, FTS out of the box can index may common file formats i.e. doc, xls. You can get the full list of supported document types by running the following query:

select document_type, path from sys.fulltext_document_types

Here's a detailed article on how to setup a FTS catalog. One this that is not all that clear is that on the "Select Table Columns" step, if your FTS column is a data type of image you are required to select another column in the table the holds the document type of the document in the image column. This is how FTS knows which filter to use to index the data.

http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/ 

I found an excellent article that explains the capabilities of FTS. It's SQL2005 specific, however everything (to my knowledge is supported in SQL2008).

http://www.developer.com/db/article.php/3446891 

Things to investigate:

1. How to install and activate other predefined filters (PDF not supported by default),
2. For the hard core, how to write your own filter extension.