Leon's Weblog

October 27, 2013

SQL Server Full Text Summary

Filed under: Software Dev — Leon @ 11:23 am

Text Search Microsoft SQL Server has a convenient Full-Text search capability which is powerful and fairly easy to setup. There is, however, one glaring feature which appears to be missing. SQL Server can rank the search results but it has no built in way to summarize them. So when you store large documents in a table, you can easily tell a user which of these documents match a particular search string but you can’t easily tell the user where to look inside those document. Imagine using an internet search engine and getting a list of links without a summary of those pages.

I figured that I can’t be the first to need this functionality and expected to find plenty of solutions online. One solution that I thought looked promising is the Hit Highlight user defined function. It is easy to setup but I didn’t like the resulting summary or the performance hit (the full-text search runs faster than the summary). Another solution that I found was the ThinkHighlight function which improves performance a bit by implementing the code in a CLR Assembly but it is not free. This site compared the performance of these two solutions and it didn’t seem like the performance improvement of the CLR Assembly justified the cost. Since I didn’t find the solutions that I was looking for, I set out to make one of my own. (more…)

October 20, 2013

Encrypting Data in SQL Server Table

Filed under: Software Dev — Leon @ 4:07 pm

Encryption I was working on a project where I needed to store connection strings in a database table for use inside of a reporting engine. SQL Server offers many encryption options from encrypting entire databases to hashing individual strings so it took me some time to come up with an ideal solution. Hashing was not an option for me because I needed to be able to read back the connection strings in clear text before passing them to the reporting engine. Encrypting the entire database seemed like overkill and would create an unnecessary performance hit. Ultimately, I was designing a method to protect the data in one field of a table from users who may potentially have read-access to the database. (more…)