I had a great time at DevConnections this week, It was a fairly quick trip for me, with lots of other events, but a good time. I presented two sessions and both went well. I had some good questions from the audience and people seemed to enjoy the talks. In order to make it easy to find the slides and code, I’m including them here:
Encryption in SQL Server
Searching Office Documents in SQL Server 2012
If you have any questions, please feel free to contact me.
It’s just a week until Dev Connections. This is one of the great conferences for the hybrid technology person, with a variety of technologies being talked about, all for a single admission price. I’ll be speaking at SQL Server Connections, but wandering over to the development side at times since I’m getting back into a little bit of software development as part of my job with Red Gate Software, and of course, for fun.
This fall the Dev Connections event is at the Mandalay Bay in Las Vegas. It’s a great hotel at the end of the strip with nice amenities and a good conference center. It’s easy to get to from the airport, which I like since traffic can be a pain. There’s also the Luxor next door, with Carrot Top playing. I’m hoping to sneak over there for a show.
However I’m primarily there to teach you a few things about SQL Server. I’ve rewritten a bit of my Encryption talk to add a few things and give you a good grounding in how the technology works in SQL Server. I’ll be looking at how you can encrypt your connections, secure your physical files with TDE (don’t miss the hex editor demo) and also look at data encryption using symmetric and asymmetric keys. I’ll also be talking about full-text search. I’ve reworked my talk on searching binary data to focus on extracting information from office documents.
Both of my talks are on Wednesday, October 2, 2013, but if you are looking for other SQL Server topics, there are some great ones. Denny Cherry talks partitioning, Allen White talks Powershell and SQL Server, Tim Ford talks DMVs and Stacia Misner talks BI. There are a number of other great SQL Server speakers as well as speakers to talk Windows, Visual Studio, Exchange, Sharepoint and more.
If you’re looking for a great conference to attend soon, think about registering for Dev Connections and I hope to see you in Las Vegas.
I would hope that most of us have used a thesaurus at some point in our careers. These allow us to substitute words for one another, providing for richer and more interesting communication.
Full-text search in SQL Server includes a thesaurus that you can customize for your searches. As with the thesaurus some of us use when writing, this features allows the search engine to substitute one word for another in searches.
You actually have to customize it. Here’s the default thesaurus for SQL Server 2008, which is stored in this location:
<SQL_Server_data_files_path>\MSSQL11.MSSQLSERVER\MSSQL\FTDATA\
If you look in this folder, you see a lot of XML files. These are the thesaurus files and they are named as tsxxx.xml, where xxx is the three letter language code. For English, the thesaurus is tseng.xml.
If I open up the English file, you can see there’s not much there in terms of entries.
This looks like the file that came with SQL Server 2005, if not SQL Server 2000. Note also that everything is commented out, and you need to remove these comment lines if you want to edit this file.
The configuration isn’t that complex, but let’s look at a simple example. I’ll set up a small table and create a full text index on it.
CREATE TABLE FTSTemp ( id INT , Notes varchar(8000) CONSTRAINT pk_ftstemp PRIMARY KEY (id) ) ; GO INSERT ftstemp SELECT 1, 'The quick brown fox jumped over the lazy dog' INSERT ftstemp SELECT 2, 'I run WinXP.'
I’m going to edit my thesaurus file to include these entries:
<expansion>
<sub>XP</sub>
<sub>WinXP</sub>
</expansion>
And also
<expansion>
<sub>leaped</sub>
<sub>jumped</sub>
</expansion>
I am only using expansion sets here. There are also replacement sets, but that’s for another post. In this case, when I search for any of the terms above, elements matching any other terms will be returned.
NOTE: The US English file is tsenu.xml. The UK English file is tseng.xml.
I’ll now create a full text index on this table, on the Notes column.
I can issue this search, which I expect to work:
SELECT id , notes FROM ftstemp WHERE CONTAINS(notes, 'quick')
That returns the data I expect.
Now to check the expansion set. To do that, I’ll need to use a FREETEXT query. Once I do this, I get results from both of my entries.
Note that if you edit the thesaurus, in order for your changes to show up in queries, you need to reload the Thesaurus file with this:
EXEC sys.sp_fulltext_load_thesaurus_file 1033;
The 1033 is for English. This is the LCID, which varies for each language.
If you want to broaden your searches, include acronyms, etc, then the thesaurus is a good way to do this. Beware, however, that your entries will apply to all searches on the instance, so if you have disparate applications on the same instance, you might encounter some strange results.
There are numerous components to the Full-Text Search (FTS) subsystem in SQL Server that help provide efficient, relative answers to queries. Full-text Search is a little complex, and as I’ve been working with the system in an effort to learn more about it, I decided to document how a few things work.
Word Breakers and stemmers are two interesting parts of the FTS system. They deal with certain language specific operations that help searches work better. They are related as the two items are loaded together if you use third party word breakers and stemmers.
I haven’t seen third party word breakers, but some work from other products. As an example, here’s a post to load the Greek FTS search word breaker and stemmer from Sharepoint server if you are on SQL Server 2008. It’s included in SQL Server 2012.
Let’s start with word breakers, which do just what the term implies: they break words. It would seem to be obvious that spaces are the word boundaries, and they are in English, but not necessarily in all languages. There are also the issues of characters in Asian languages like Japanese and Chinese. You can’t count spaces as the word boundaries on those languages.
Word breakers use the lexical rules of the language to determine word boundaries. Essentially they find what the words are, and then further action can be taken in building the FTS index or processing the query.
The “words” that the word breaker spits out are seen a “tokens” to the FTS index, and each can then be processed by stemmers, stoplists, thesaurus, etc.
Stemmers are an interesting part of the full-text search system. They remind me of my high school Latin classes, where we had to conjugate words. A stemmer takes a word and generates inflectional forms, or conjugations. The example in Books Online, and an easy one to understand is “run”. There are various forms of "run” that we would want to consider as equivalent when performing a search. For example, you would want to consider:
The same could be said for “lay”. That would generate
This is one of the big advantages over the LIKE predicate in that stemmers can match these forms of the word being searched for. The index would relate all of these to the core, base word.
The Books Online page for Word Breakers and Stemmers has technical information on checking what’s installed, some troubleshooting, language settings, and some drier documentation on what you can do with word breakers, but not a lot of explanatory detail.
I used a little of the information in Books Online, and some from Pro Full-Text Search in SQL Server 2008. You can read more, but unfortunately I haven’t found a lot more documentation on the details of how things work.
You would probably learn more if you write your own Word Breaker and Stemmer, and there is a sample in the Windows SDK to get you started, but that’s beyond what I want to do.
Voice of the DBA 