Beyond SoundEx - Functions for Fuzzy Searching in MS SQL Server

Quite often we come across a requirement where we may need to perform some sort of fuzzy string grouping or data correlation. For example, we may want to correlate the customer records of a database by identifying records that are similar but not necessarily exactly the same (due to spelling mistakes for example). Obviously a simple group by, will not successfully group such data. We will need to employ what is commonly referred to as a distance algorithm or a string metric in order to determine how close 2 string values are.More...

SQL Server script to auto-create indexes on all Foreign Key Columns

If you want a quick way to set some default indexes up in your database the best candidates for your indexes would be the foreign keys. They are usually highly selective and many of our queries tend to have some sort of filter on the foreign keys, this makes them good candidates for an index. More...

4 Features for C# 4.0

Plenty of powerful new language features have fairly recently become mainstream with the launch of VS 2008 in Nov 2007 that supports C# 3.0, amongst which:

  • Implicitly Typed Local Variables
  • Extension Methods
  • Lambda Expressions
  • Object and Collection Initializers
  • Anonymous Types
  • Query Expressions
  • Automatically Implemented Properties

Although most of us are still trying to fully absorb the above features, there has been some speculation going on about what sort of features we would like to see in the next version of C# and Jeremy Miller has posted a question with what he would like to see.

Here are some of the language features I would really like to see in C#: More...

SQL CLR without DLL dependency for your production server

Have you been shying out of putting extra functions on your SQL Server 2005 using SQL CLR simply because you would like to avoid DLL Hell?!

Well, the good news is that you need not be so worried, since SQL Server does not link to the dll itself, but instead it embeds the assembly in the database. What's even better is that you can deploy your assembly by simply scripting it.

Lets do this with an example, in the previous post there is a sample that shows how to create support for regular expressions using SQL CLR. To deploy this on your production server without having to copy any url's you simply need to open Sql Server Management Studio and go to:
DB > Programmability > Assemblies > AssemblyName > RightClick > ScriptAssembly As > Create To > Clipboard | File | New Window

like so: More...

Regular Expressions In MS SQL Server using CLR

In this post I'll show just how easy it is to make your SQL Server 2005 database support Regular Expressions through what is known as SQL CLR

Just Fire up Visual Studio and create a new library project (I called it TextFunctions)

Add a new Class and Call it Regular Expressions and simply paste in the following Code: More...

Disable Text Wrapping in Excel Export of Reporting Services

Well, i guess that's a pretty elaborate title, but it's what this post is about. How you text-wrapping when exporting to Excel via SQL Server Reporting services 2005 on the web be disabled.

It is unfortunate that there is no configuration option in when authoring a report to control whether the text can wrap or not. More...

Quick Tip: Use Hashbytes to create a Hash in TSQL in SQL Server

Just a quick tip.
I was unaware until recently that SQL Server 2005 has nicely built in support for hashing and it is called hashbytes

HashBytes ( '<algorithm>', { @input | 'input' } )
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1

Parameters are the algorithm you wish to hash to and the input string to hash.
Here is a sample along with the return values commented in the next line:

Select HashBytes('MD2', 'Hello world!') 
--0x63503D3117AD33F941D20F57144ECE64

Select HashBytes('MD4', 'Hello world!') 
--0x0D7A9DB5A3BED4AE5738EE6D1909649C

Select HashBytes('MD5', 'Hello world!') 
--0x86FB269D190D2C85F6E0468CECA42A20

Select HashBytes('SHA', 'Hello world!') 
--0xD3486AE9136E7856BC42212385EA797094475802

Select HashBytes('SHA1', 'Hello world!') 
--0xD3486AE9136E7856BC42212385EA797094475802


MSDN:
http://msdn2.microsoft.com/en-us/library/ms174415.aspx

Expose your objects over HTTP with minimal coding

Quite often we create systems that make use of web services so as to expose our Data, or Business methods for client applications to consume them. In a classic 3 tier scenario it may be either the data layer exposed through the web service as a data service, or the business layer exposed through the web service as a business service. I would say that both are perfectly valid for exposure through a web service depending on our needs and requirements. But since web services already exist, why make a custom method and use this method instead of web services? The following paragraph contains some points I dont like when coding standard microsoft web services. More...

C# 3.0 Anonymous type support is incomplete

For those who have been playing with Linq and getting up to speed with the latest C# 3.0 features, you are most likely aware of the new language feature of Anonymous Types.

An anonymous type allows us to create and an instance of a an object whose type has not been defined in our code but whose properties are inferred from the object initializer.

The following example illustrates this by creating an instance of a variable of an unknown type that has 3 properties More...

Yield return and Iterators use case: looping through the days between a date span

When you find yourself coding a loop to iterate through days, there are 2 ways to do it. Use iterators, use for (int... ). Iterators are not baked into dates so you would have to build them yourself, let's have a look at how to do this.More...