Dougbert's Blog

FILESTREAM filegroups can contain multiple files in SQL Server 2012

dougbert@dougbert.com — Mon, 27 Feb 2012 10:59:00 -1200

This post describes a new FILESTREAM feature in SQL Server 2012 that enhances the I/O scalability of FILESTREAM data.

In SQL Server 2012, a FILESTREAM filegroup can now contain more than one file.

You can improve I/O scalability for FILESTREAM data by placing different files within the same FILESTREAM filegroup on different volumes. This feature eliminates the need for complicated workarounds that use partitioning and multiple FILESTREAM filegroups.

The following example creates the BlobStore1 database. The database is created with one row filegroup and one FILESTREAM filegroup, FS. The FILESTREAM filegroup contains two files, FS1 and FS2. Then the database is altered by adding a third file, FS3, to the FILESTREAM filegroup.

Thank you to SQL Server tester Sandu Chirica for this Transact-SQL code sample, which will also appear in the CREATE DATABASE topic in SQL Server 2012 Books Online.

-- This example assumes that the directory C:\BlobStore exists.
-- The file sizes in this example are arbitrary.

USE [master]
GO

CREATE DATABASE [BlobStore1]
CONTAINMENT = NONE
ON PRIMARY 
( 
    NAME = N'BlobStore1', 
    FILENAME = N'C:\BlobStore\BlobStore1.mdf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1MB
), 
FILEGROUP [FS] CONTAINS FILESTREAM DEFAULT 
(
    NAME = N'FS1',
    FILENAME = N'C:\BlobStore\FS1',
    MAXSIZE = UNLIMITED
), 
(
    NAME = N'FS2',
    FILENAME = N'C:\BlobStore\FS2',
    MAXSIZE = 100MB
)
LOG ON 
(
    NAME = N'BlobStore1_log',
    FILENAME = N'C:\BlobStore\BlobStore1_log.ldf',
    SIZE = 100MB,
    MAXSIZE = 1GB,
    FILEGROWTH = 1MB
)
GO

ALTER DATABASE [BlobStore1]
ADD FILE
(
    NAME = N'FS3',
    FILENAME = N'C:\BlobStore\FS3',
    MAXSIZE = 100MB
)
TO FILEGROUP [FS]
GO

I hope you find this new feature in SQL Server 2012 useful!

-Doug

Querying for registered search properties and lists in SQL Server 2012

dougbert@dougbert.com — Wed, 22 Feb 2012 07:22:00 -1200

This post discusses the new Property Search feature in SQL Server 2012 "Denali" Full-Text Search. In particular, it shows you how to get a list of the search properties that you've registered and the search property lists to which they belong by using a JOIN.

SQL Server 2012 "Denali" now lets you search specifically for the values of built-in or custom document properties, such as the "Author" of a Microsoft Word document. However, before you can run queries, you have to register the properties that you want to be able to search.

For more information about property search in SQL Server 2012 Full-Text Search, see Search Document Properties with Search Property Lists.

Let's walk through the steps of registering some search property lists and search properties. Then we'll show how to query this information.

Thank you to Venkatraman Parameswaran, friend and SQL Server tester, for the code samples in this blog post.

1. Choose a database. First, select the database that you want to use for your testing. The following example creates a new test database just to be safe:

use master 
go
if exists(select * from sys.databases where [name] = 'testDB1') drop database testDB1
go
create database testDB1
go
use testDB1
go

2. Create search property lists. Now let's create a few search property lists for our testing. Later we'll add search properties to these lists. Search property lists are simply convenient containers for organizing groups of search properties.

--  Create a few search property lists.
create search property list searchpropertylist_1;
create search property list searchpropertylist_2;
create search property list searchpropertylist_3;
go

3. Add search properties. Now let's add some search properties to the search property lists that we've created. For more information about how to find the values that you need to write these statements, see Find Property Set GUIDs and Property Integer IDs for Search Properties. Watch for a future blog post about this subject.

-- Add a few properties to each list. Add some to more than one list.
ALTER SEARCH PROPERTY LIST searchpropertylist_1  ADD N'System.Author'
  WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
  PROPERTY_INT_ID = 4, PROPERTY_DESCRIPTION = N'System.Author');
ALTER SEARCH PROPERTY LIST searchpropertylist_1  ADD N'System.Subject'
  WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
  PROPERTY_INT_ID = 3, PROPERTY_DESCRIPTION = N'System.Subject');
ALTER SEARCH PROPERTY LIST searchpropertylist_1  ADD N'System.Title'
  WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9', 
  PROPERTY_INT_ID = 2, PROPERTY_DESCRIPTION = N'System.Title');    

ALTER SEARCH PROPERTY LIST searchpropertylist_2  ADD N'System.Category'
  WITH (PROPERTY_SET_GUID = 'd5cdd502-2e9c-101b-9397-08002b2cf9ae',
  PROPERTY_INT_ID = 2, PROPERTY_DESCRIPTION = N'System.Category');
ALTER SEARCH PROPERTY LIST searchpropertylist_2  ADD N'System.Comment'
  WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
  PROPERTY_INT_ID = 6, PROPERTY_DESCRIPTION = N'System.Comment');
ALTER SEARCH PROPERTY LIST searchpropertylist_2  ADD N'System.Company'
  WITH (PROPERTY_SET_GUID = 'd5cdd502-2e9c-101b-9397-08002b2cf9ae',
 PROPERTY_INT_ID = 15, PROPERTY_DESCRIPTION = N'System.Company');
ALTER SEARCH PROPERTY LIST searchpropertylist_2  ADD N'System.ContentStatus'
  WITH (PROPERTY_SET_GUID = 'd5cdd502-2e9c-101b-9397-08002b2cf9ae',
  PROPERTY_INT_ID = 27, PROPERTY_DESCRIPTION = N'System.ContentStatus');    

ALTER SEARCH PROPERTY LIST searchpropertylist_3  ADD N'System.Author'
  WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
  PROPERTY_INT_ID = 4, PROPERTY_DESCRIPTION = N'System.Author');
ALTER SEARCH PROPERTY LIST searchpropertylist_3  ADD N'System.Category'
  WITH (PROPERTY_SET_GUID = 'd5cdd502-2e9c-101b-9397-08002b2cf9ae',
  PROPERTY_INT_ID = 2, PROPERTY_DESCRIPTION = N'System.Category');
ALTER SEARCH PROPERTY LIST searchpropertylist_3  ADD N'System.Keywords'
  WITH (PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
  PROPERTY_INT_ID = 5, PROPERTY_DESCRIPTION = N'System.Keywords');

GO

4. Query for registered search property lists. Now we're ready to run some queries about the items that we've registered. First, let's query for the search property lists that we've created.

-- Query for all registered search property lists.
select * from sys.registered_search_property_lists 
go

Results:

property_list_id name                   create_date             modify_date             principal_id
---------------- ---------------------- ----------------------- ----------------------- ------------
5                searchpropertylist_1   2012-02-21 10:35:04.463 2012-02-21 10:35:04.510 1
6                searchpropertylist_2   2012-02-21 10:35:04.477 2012-02-21 10:35:04.560 1
7                searchpropertylist_3   2012-02-21 10:35:04.480 2012-02-21 10:35:04.560 1

(3 row(s) affected)

5. Query for registered search properties. Now let's query for all the search properties that we've registered across all search property lists.

-- Query for all registered search properties across all lists.
select * from sys.registered_search_properties
go

Results:

property_list_id property_id property_name          property_set_guid                    property_int_id property_description
---------------- ----------- ---------------------- ------------------------------------ --------------- --------------------
5                1           System.Author          F29F85E0-4FF9-1068-AB91-08002B27B3D9 4               System.Author
5                2           System.Subject         F29F85E0-4FF9-1068-AB91-08002B27B3D9 3               System.Subject
5                3           System.Title           F29F85E0-4FF9-1068-AB91-08002B27B3D9 2               System.Title
6                1           System.Category        D5CDD502-2E9C-101B-9397-08002B2CF9AE 2               System.Category
6                2           System.Comment         F29F85E0-4FF9-1068-AB91-08002B27B3D9 6               System.Comment
6                3           System.Company         D5CDD502-2E9C-101B-9397-08002B2CF9AE 15              System.Company
6                4           System.ContentStatus   D5CDD502-2E9C-101B-9397-08002B2CF9AE 27              System.ContentStatus
7                1           System.Author          F29F85E0-4FF9-1068-AB91-08002B27B3D9 4               System.Author
7                2           System.Category        D5CDD502-2E9C-101B-9397-08002B2CF9AE 2               System.Category
7                3           System.Keywords        F29F85E0-4FF9-1068-AB91-08002B27B3D9 5               System.Keywords

(10 row(s) affected)

6. Query for a combined list of search properties and search property lists. Finally we're ready to use a JOIN to get a combined list of search properties and the search property lists to which they belong.

--  Query to associate properties with the property list.
select A.property_name AS 'Search Property',
    B.[name] AS 'Search Property List',
    A.property_set_guid,
    A.property_int_id
from sys.registered_search_properties A
join sys.registered_search_property_lists B
on A.property_list_id = B.property_list_id
order by B.name
go

Results:

Search Property        Search Property List  property_set_guid                    property_int_id
---------------------- --------------------- ------------------------------------ ---------------
System.Author          searchpropertylist_1  F29F85E0-4FF9-1068-AB91-08002B27B3D9 4
System.Subject         searchpropertylist_1  F29F85E0-4FF9-1068-AB91-08002B27B3D9 3
System.Title           searchpropertylist_1  F29F85E0-4FF9-1068-AB91-08002B27B3D9 2
System.Category        searchpropertylist_2  D5CDD502-2E9C-101B-9397-08002B2CF9AE 2
System.Comment         searchpropertylist_2  F29F85E0-4FF9-1068-AB91-08002B27B3D9 6
System.Company         searchpropertylist_2  D5CDD502-2E9C-101B-9397-08002B2CF9AE 15
System.ContentStatus   searchpropertylist_2  D5CDD502-2E9C-101B-9397-08002B2CF9AE 27
System.Author          searchpropertylist_3  F29F85E0-4FF9-1068-AB91-08002B27B3D9 4
System.Category        searchpropertylist_3  D5CDD502-2E9C-101B-9397-08002B2CF9AE 2
System.Keywords        searchpropertylist_3  F29F85E0-4FF9-1068-AB91-08002B27B3D9 5

(10 row(s) affected)

I hope you find this new feature in SQL Server 2012 useful! Look for more blog posts about property search.

-Doug

Excel driver now supported on server for use by SSIS

dougbert@dougbert.com — Wed, 25 Jan 2012 05:44:00 -1200

A year ago, I wrote a blog post to caution SSIS users that the ACE Provider and its Excel driver were not supported by Microsoft for use on the server.

That position has changed - in certain circumstances! - as I learned today from SSIS dev manager David Noor. The download page for the ACE Provider now contains the following new paragraph:

The Office System Drivers are only supported under certain scenarios, including: ...

2.To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive).

SSAS Program Manager Dave Wickert explains why the last requirement is critical:

ACE uses the impersonated user’s Windows temp folder to read-write its data. Therefore if your application is using impersonation with an account that does not have a profile on the server (not an uncommon situation), then ACE will not be able to create its temp files.

This is good news for all those who are using SSIS with Excel. The Office team that owns the ACE Provider finally heard you roar!

-Doug

Get the Office 2010 Filter Pack for search in SQL Server 2012

dougbert@dougbert.com — Thu, 17 Nov 2011 05:30:00 -1200

SQL Server 2012 “Denali” installs the latest Microsoft word breakers and stemmers. However, it does not install the latest filters for Microsoft Office documents. If you want to use Full-Text Search or Semantic Search to index documents created by the most recent versions of Microsoft products, you have to download the latest filters.

Get the latest filters

To get the latest Microsoft filters:

Download the appropriate version – 32-bit or 64-bit – from Microsoft Office 2010 Filter Packs.
Then install Service Pack 1 for the Filter Pack through Microsoft Update or by downloading the 32-bit or 64-bit version.

In a moment, SQL Server will be able to search all the document types supported by the filters included in the filter pack:

Legacy Office Filter (97-2003; .doc, .ppt, .xls)
Metro Office Filter (2007; .docx, .pptx, .xlsx)
Zip Filter
OneNote filter
Visio Filter
Publisher Filter
Open Document Format Filter

Load the newly-installed filters

I said, “in a moment,” because first you have to tell SQL Server to begin using the newly-installed filters. SQL Server does not automatically use all filters installed on the computer. Run the following Transact-SQL commands:

Load the new filters:
EXEC sp_fulltext_service 'load_os_resources', 1
Update the list of components maintained by SQL Server:
EXEC sp_fulltext_service 'update_languages'
(This step may not be necessary to use the filters. However it is necessary to ensure up-to-date output about the installed filters from the sp_help_fulltext_system_components system stored procedure.)
Restart the filter daemon host processes:
EXEC sp_fulltext_service 'restart_all_fdhosts'

How to fix a gotcha related to Office 97-2003 documents (.doc, .xls, etc.)

After you install the new filters, SQL Server continues to use the older version of the filter DLL (OFFFILT.DLL) for Microsoft Office 97-2003 documents. This can prevent the proper indexing of documents in certain cases – for example, an Excel spreadsheet embedded inside a Word document is properly indexed by the newer version of the filter, but not by the older version.

To resolve this issue and ensure that SQL Server uses the newly-installed version of OFFFILT.DLL for legacy Office documents, do the following:

Delete this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSearch\Filters\.doc
Load the alternate filters:
EXEC sp_fulltext_service 'load_os_resources', 1
Restart the filter daemon host processes:
EXEC sp_fulltext_service 'restart_all_fdhosts'

To confirm that SQL Server is now using the newer version of OFFFILT.DLL in the Common Files\Microsoft Shared\Filters folder, you can run the following query:

select * from  sys.fulltext_document_types where document_type = '.doc'

This should return the following information, which confirms that the newly-installed filter DLL is now being used:

“.doc       64F1276A-7A68-4190-882C-5F14B7852019     C:\Program Files\Common Files\Microsoft Shared\Filters\OFFFILT.DLL 2010.1400.4746.1000  Microsoft Corporation”

Rebuild full-text indexes after installing new filters

To see the effects of the newly-installed filters, you have to repopulate existing full-text indexes where the indexed columns contain documents that are supported by the new filters.

I hope this information helps you with Full-Text and Semantic Search in SQL Server 2012!

Let me know how I'm doing! Please comment on my blog, or send email to dougbert@dougbert.com.

For technical support, however, I recommend the SQL Server Search forum on MSDN.

-Doug

Recompile VSTA scripts programmatically in SSIS

dougbert@dougbert.com — Wed, 16 Nov 2011 11:50:00 -1200

You can use the SQL Server Integration Services API to reconfigure a package dynamically, or even to create an entire package from scratch. Books Online doesn't say much about this. But the SSIS product team has provided the Package Generation Sample, and various bloggers and authors have described the process and the code.

What if you want to add Script Tasks or Script Components programmatically? I blogged about this in 2008 - Adding a VSTA Script task programmatically.

And now the big question. What if you want to recompile the VSTA scripts?

The information in this blog post applies to SQL Server 2008 and 2008 R2. After publishing this post, I learned from SSIS developer Matt Masson that the sequence of API calls to recompile VSTA scripts has changed for SQL Server 2012 "Denali." Matt will publish a blog post with this new information. I will post a link here when that information becomes available.

1. How to recompile VSTA scripts programmatically

The answer is implicit in the code sample that I provided – just load the script into the VSTA IDE, then close the IDE. Here are the critical lines from the code sample in my old post, where task is a ScriptTask:

// This method loads solution and builds it

task.ScriptingEngine.LoadScriptSolution(path);

// This one closes IDE and saves binary code

 task.ScriptingEngine.CloseIDE(true);

This question was also answered in the SSIS forum by SSIS developer Silviu Guea. Here's an excerpt, where task is again a ScriptTask:

            VSTATaskScriptingEngine script =

                new VSTATaskScriptingEngine(task.ScriptStorage);
            // load existing script from it's storage and load it in the designer
            script.LoadScriptFromStorage();
            // re-save it triggering a rebuild
            script.SaveScriptProjectFile();
            // close the designer... don't forget this or you'll leak processes
            script.CloseIDE(true);

Recently a customer from Brazil asked me how to recompile. After some unkind thoughts about his familiarity with search, I Binged "ssis recompile script programmatically." In fact, I could not find a satisfactory answer, and I found the question unanswered in several places. (Hint: Search for "precompile." What a difference 1 letter makes.)

2. A command-line app for recompiling all scripts in a package

I pestered my buddy Silviu to ask whether there was more to say on this subject. In return he sent me the attached C# command-line application to rebuild all the Script Tasks and Script Components in a package. You may be able to compile the code and use it "as is," or you may want to study it to learn the steps in the process.

updatescripts.zip (4.26 kb)

2.1 Launching the command-line app

Here is a sample command line:

updatescripts.exe c:\work\mypackage.dtsx c:\temp\output.dtsx /tasks /components

2.2 Rebuilding Script Tasks

Here are the lines of code from the app that rebuild each Script Task, where task is again a ScriptTask:

            VSTATaskScriptingEngine dte = new VSTATaskScriptingEngine(task.ScriptStorage);
            dte.LoadScriptFromStorage();
            dte.SaveScriptProjectFile();
            dte.CloseIDE(true);

2.3 Rebuilding Script Components

Here are the lines of code from the app that rebuild each Script Component, where comp is a ScriptComponentHost:

            comp.ShowIDE();
            comp.CloseIDE();

(There are some important lines of code that come before these simple steps, to determine whether the IDTSComponentMetaData100 is in fact a Script Component, to instantiate its CManagedComponentWrapper, and then to retrieve the ScriptComponentHost that is its InnerObject.)

I hope you find this application and sample code useful.

Let me know how I'm doing! Please comment on my blog, or send email to dougbert@dougbert.com.

For technical support, however, I recommend the SSIS forum on MSDN.

-Doug

updatescripts.zip (4.26 kb)

How near is NEAR in SQL Server 2012 Full-Text Search?

dougbert@dougbert.com — Wed, 02 Nov 2011 04:43:00 -1200

SQL Server 2012 “Denali” Full-Text Search introduces a new custom proximity operator, which we’ve been calling “customizable NEAR.” The new NEAR operator lets you query with 2 optional requirements that you could not previously specify:

The maximum gap between the search terms.
The order of the search terms. (For example, “John” must appear before “Smith.”)

Here is a partial example of the new NEAR operator:

CONTAINSTABLE (Documents, Content, 'NEAR((John, Smith), 4, TRUE)')

I think you will understand the simple syntax at first glance. I intend to write a blog post soon about customizable NEAR in general. But today, let’s focus on a question that has gained importance since we exposed the optional max_gap argument, which is the 2nd argument in the preceding example: How near is NEAR?

1. The generic NEAR operator (pre-SQL Server 2012)

1.a. How the generic NEAR operator determines proximity

The logic of the generic NEAR operator is simple:

When used with CONTAINSTABLE, it considers nearness significant and returns a Rank > 0 if the 2 words are within 50 words of each other. (Oracle’s generic proximity operator used a boundary of 100 words.) CONTAINSTABLE returns a match with a Rank = 0 if the 2 words are found, but are more than 50 words apart.
When used with CONTAINS, NEAR effectively becomes an AND query, and returns a match if the 2 words are found in the same row or document.

1.b. The generic NEAR operator is deprecated in SQL Server 2012

The custom proximity operator is a new operator, and not an extension of the existing NEAR operator. The generic NEAR operator is deprecated in SQL Server 2012. It still works in SQL Server 2012, but should not be used in new development.

2. The SQL Server 2012 customizable NEAR operator

The custom proximity operator now lets you specify “how near” you want search terms to be with an optional max_gap argument. In this example, you want to find documents where “dog” and “cat” appear within 4 words of each other, and where "dog" appears before "cat":

'NEAR((dog, cat), 4, TRUE)'

2.1 Simple definition of the gap

In a matching fragment that begins and ends with one of the search terms, the gap is the number of non-search-terms between them.

Stopwords or noise words are included in the count.

2.2 BUT the distance between search terms is logical and not absolute

For example, terms within different sentences within a paragraph are treated as farther apart than terms in the same sentence, regardless of their actual proximity, because we assume that they are less closely related. Terms in different paragraphs are treated as being even farther apart.

If a match spans the end of a sentence, paragraph, or chapter, the actual gap is increased by 8, 128, or 1024, respectively. So in this example… “I see the cat. The dog also sees her.” …the gap between “cat” and “dog” is 1 + 8 = 9, because of the sentence break.

What if your documents aren’t text documents, but are Word documents or Excel spreadsheets or PowerPoint presentations? Then interpreting things such as table breaks, different worksheets, or bullet points on a slide is the responsibility of the IFilter for each document type. It is up to the IFilter to map each construct in the document format to either a word, sentence, paragraph or chapter.

It’s not possible to constrain your search to terms that appear in the same sentence or in the same paragraph. However we believe that the new custom proximity operator is simple and powerful enough to satisfy the majority of our customers’ needs in relation to proximity searches.

2.3 Where can I see the calculated gap between terms?

You can use the dynamic management view, sys.dm_fts_parser, to see how the gap between terms is calculated. Here's an example that uses the sample sentence from the preceding section:

SELECT occurrence, LEFT(display_term, 16), special_term 
    FROM sys.dm_fts_parser('"I see the cat. The dog also sees her."', 1033, 0, 0)

Here are the results, which confirm the gap of 9 (14 – 4 – 1) between "cat" and "dog":

occurrence                   special_term
----------- ---------------- ----------------
1           i                Noise Word
2           see              Noise Word
3           the              Noise Word
4           cat              Exact Match
12          END OF FILE      End Of Sentence
13          the              Noise Word
14          dog              Exact Match
15          also             Noise Word
16          sees             Exact Match
17          her              Noise Word
25          END OF FILE      End Of Sentence

(11 row(s) affected)

2.4 Specifying a value for the max_gap argument

The max_gap argument is optional. If you specify a value, it can be any numeric value allowed by a 4-byte integer. (Frankly, a gap of 2,147,483,647 would seem to suggest that 2 terms are not closely related!)

Naturally, queries only return documents or rows that satisfy the max_gap requirement. (With the generic NEAR operator, on the other hand, CONTAINSTABLE previously returned rows where the gap exceeded its boundary of 50, but assigned a Rank of 0 (zero) to those rows.)

A matching gap is <= max_gap. It’s not possible to require a specific gap, neither less nor more, between search terms.

If you want to specify a value for the optional 3rd order argument, then you also have to specify a value for max_gap.

2.5 Omitting max_gap or specifying MAX

Instead of specifying an integer value, you can also specify the keyword MAX, or omit max_gap. MAX is the default value. When MAX is specified, then queries return any documents or rows that contain the search terms, regardless of the gap between them. However matches where the gap is > 100 have a rank of 0 (zero).

2.6 Two simple examples to summarize the behavior of max_gap

NEAR((dog, cat), 6)

The query only returns rows that contain both dog and cat, with 6 terms or fewer between them.
Rows are ranked by proximity, and no returned row can have Rank = 0.

NEAR((dog, cat))

This is equivalent to using the MAX default - NEAR((dog, cat), MAX).
The query returns all rows that contain both dog and cat.
Rows have a Rank = 0 if the gap between terms is > 100.

2.7 An example with multiple matches

Query: NEAR((A, B), 10)

Data: AB…(10 terms)…AB…(10 terms)…AB

Matches: 5

The 1st AB pair.
The first B…A.
The 2nd AB pair.
The 2nd B…A.
The 3rd AB pair.

2.8 An example with multiple search terms

Query: NEAR((wine, cheese, “nearby stores”), 5)

Data: This wine and cheese can be found in nearby stores.

Matches? Yes. The count of non-search terms (“and,” “can be found in”) is <= the max_gap of 5.

Data: This wine and cheese can sometimes be found in nearby stores.

Matches? No. The count of non-search terms (“and,” “can sometimes be found in”) is > the max_gap of 5.

Thank you to the members of the SQL Server Full-Text Search development team for patiently answering my questions as I prepared this blog post!

Follow the Full-Text Search team blog if also you’re interested in occasional updates about full-text search.

-Doug

More sample queries for Semantic Search in SQL Server 2012

dougbert@dougbert.com — Mon, 31 Oct 2011 09:33:00 -1200

Earlier today I published a blog post about Looping over document similarity details in Semantic Search on the MSDN blog of the SQL Server Full-Text Search team. That post introduces the 3 built-in rowset functions that are provided by Semantic Search in SQL Server 2012 “Denali.” It also describes how to do something for which we don’t provide a built-in set-based function – that is, iterate over the documents that are similar to a source document, and ask why each document is similar. Please take a look!

I had fun playing with semantic search and testing my code. Here are a few more examples of the queries that you can perform with semantic search. Don't laugh at my SQL code! I haven't written much T-SQL while working mostly with SSIS for several years.

The examples in this post use the semanticsimilaritydetails_foreach function created in the blog post linked above.

The examples in this post also use a collection of SQL Server white papers published on MSDN and TechNet – mostly in Microsoft Word format – saved into a SQL Server database by using a SQL Server 2012 FileTable. With this approach, I can easily save BLOBs into the database simply by dragging and dropping the files in Windows Explorer. The database table is named WhitePapers, and it has the fixed schema common to all FileTables.

1. How many white papers are about ETL?

Query:

SELECT COUNT(*) AS 'Number of ETL White Papers'
    FROM semantickeyphrasetable (WhitePapers, *) AS SKP
    WHERE SKP.keyphrase = 'etl'
GO

Results:

Number of ETL White Papers
--------------------------
30

(1 row(s) affected)

These results tell me that my table contains 30 documents in which “etl” was identified as a statistically significant key phrase. (In version 1 of semantic search, only one-word terms are supported.)

2. What are the top matching white papers that are also about ETL?

Query:

DECLARE @Title NVARCHAR(255)
DECLARE @DocumentID hierarchyid

-- Source white paper - "We Loaded 1TB in 30 Minutes with SSIS, and So Can You."
SET @Title = '1TBin30MwithSSIS.docx'

-- Get the ID of the source document from its title.
SELECT @DocumentID = path_locator
    FROM WhitePapers
    WHERE name = @Title

-- Get the top n key phrases for each matching document.
DECLARE @DetailsCount smallint = 3

-- Get the top n matching white papers that are also about ETL.
SELECT TOP (10) LEFT(WP.name, 32) AS 'Document',

                LEFT(SSD.keyphrase, 16) AS 'Key Phrase',

                SSD.score AS 'Score'
    FROM WhitePapers AS WP
            CROSS APPLY

                semanticsimilaritydetails_foreach(@DocumentID, WP.path_locator, @DetailsCount)

            AS SSD
    WHERE WP.path_locator <> @DocumentID
        AND SSD.keyphrase = 'etl'
    ORDER BY Score DESC
GO

Results:

Document                         Key Phrase       Score
-------------------------------- ---------------- -------------
HighVolETLConsid.docx            etl              0.6152592
Implementing a Microsoft PDW usi etl              0.4083139
HubSpokeOverview.docx            etl              0.3958832
DataSrcISPkgs.docx               etl              0.3768547
SSIS_die_zweite.docx             etl              0.3722264
HubSpokeImpl.docx                etl              0.3580182
BestPractDWSQL2008.docx          etl              0.3551803
ScaleUpDWinSQL2008.docx          etl              0.3497615
OracleSSIS.docx                  etl              0.3446308
SSIS2008Connectivity.docx        etl              0.3446308

(10 row(s) affected)

These results show me the top 10 white papers that are similar to the source document, where “etl” is among the top 3 key phrases that were used to identify the white paper as similar to the source document.

(Note that the semantic indexing also captured a German-language article about ETL.

3. What are the similarities between my source document and other documents?

Query:

DECLARE @Title NVARCHAR(255)
DECLARE @DocumentID hierarchyid

-- Source white paper - "We Loaded 1TB in 30 Minutes with SSIS, and So Can You."
SET @Title = '1TBin30MwithSSIS.docx'

-- Get the ID of the source document from its title.
SELECT @DocumentID = path_locator
    FROM WhitePapers
    WHERE name = @Title

-- Get the top n key phrases for each matching document.
DECLARE @DetailsCount smallint = 3

-- Get the top n key phrases in matching white papers.
SELECT TOP (10) LEFT(SSD.keyphrase, 16) AS 'Key Phrase',

                COUNT(SSD.keyphrase) AS 'Count'
    FROM WhitePapers AS WP
            CROSS APPLY

                semanticsimilaritydetails_foreach(@DocumentID, WP.path_locator, @DetailsCount)

            AS SSD
    WHERE WP.path_locator <> @DocumentID
    GROUP BY SSD.keyphrase
    ORDER BY COUNT DESC
GO

Results:

Key Phrase       Count
---------------- -----------
sql              149
server           111
sqlserver        30
cpu              24
etl              23
msdn             21
aspx             15
data             11
microsoft        10
tcp              10

(10 row(s) affected)

These results show me the top 10 key phrases that were used to identify other white papers as similar to the source document, where those key phrases are among the top 3 shared key phrases.

Have fun with Semantic Search! For more information see the documentation.

Be sure to check out the killer demo of FileTable and Semantic Search from SQL Server evangelist Roger Doherty and his team. This example includes a tag cloud to visualize the relative importance of key phrases and to find similar documents at a glance.

Custom ADO.NET providers make interesting data available to SSIS

dougbert@dougbert.com — Tue, 04 Oct 2011 10:42:00 -1200

The ADO.NET providers coming out of RSSBus make some interesting data sources available to SQL Server Integration Services packages through the ADO.NET Connection Manager and Source adapter. The documentation for each of these is available online and includes screen shots. Free trial downloads are also available.

Imagine how you could creatively transform, if not your fortune, at least your QuickBooks data, or filter a large volume of SMTP email, or report on the output of your PowerShell scripts.

The following ADO.NET providers are in production and for sale:

QuickBooks.
PowerShell.
Google Services.
Salesforce.com.

The following providers are in beta as of this writing (10/4/2011), but available for trial download:

SharePoint.
Twitter.
Email (POP and SMTP).
Facebook.
Google Spreadsheets.
Amazon SimpleDB.

Plus, since these are standard ADO.NET providers, you can hook them up through Server Explorer in Visual Studio to explore. Here's an example of what the QuickBooks provider exposes:

At the bottom of the page, you can also vote on which provider you'd like to see next from RSSBus. Options include Microsoft Dynamics CRM, and eBay, SAP, Siebel, and Peachtree.

Thanks to Eric Madariaga of RSSBus (@emadari on Twitter) for bringing these providers to my attention.

-Doug

Books Online slims down at last on the Denali diet

dougbert@dougbert.com — Mon, 18 Apr 2011 10:29:00 -1200

SQL Server Books Online is flabby and bloated. The structure and style are inconsistent. You can't find what you want. I know. I use BOL too when I'm writing T-SQL or learning an unfamiliar feature.

We're fixing that

We're fixing that, at least in SQL Server "Denali." We're reducing BOL itself - that is, the "product documentation" - to the minimum that you need to use the product successfully. This core documentation will now include just 2 things:

An introduction to each significant feature and its prerequisites.
How To information that lets you use the product successfully, with only the minimum of required conceptual information.

How bad is it?

Take a look at this horrifying example from the Bad Old Days. This is the existing table of contents for SQL Server Full-Text Search. It's a jumble of concepts, how to's, and "grouping" topics that don't even contain any useful content. (Please scroll patiently - this post continues below.)

How are we going to make that better?

In the future, we just give you the how-to information that you need to use the feature successfully, along with a feature overview. Here's an example. These are the new topics for Statistical Semantic Search in SQL Server "Denali." Contrast this with what you saw above.

What you get	Why it's better
	Distinct topic types, and only a few of them overview tasks reference Related tasks grouped together in comprehensive topics Install and configure Query or use Manage and monitor

What you get

Why it's better

Distinct topic types, and only a few of them

overview
tasks
reference

Related tasks grouped together in comprehensive topics

Install and configure
Query or use
Manage and monitor

What's inside?

Here's what one of those consolidated How To topics looks like inside.

What you get	Why it's better
	Related tasks and required concepts all in one topic. Tasks first, background information second if you need it. Concise task descriptions! (And you're right, I still need to add a line of sample code there.)

What you get

Why it's better

Related tasks and required concepts all in one topic.

Tasks first, background information second if you need it.

Concise task descriptions! (And you're right, I still need to add a line of sample code there.)

When will we see these improvements in SQL Server Books Online?

In CTP3 for SQL Server "Denali" later this year.

This is a big deal

We're finally going to start writing a lightweight manual of customer tasks and solutions, and stop writing a verbose catalog of Microsoft product features. We hope you'll like it.

-Doug

Welcome to my Dougbert blog at its new address

dougbert@dougbert.com — Mon, 18 Apr 2011 10:24:00 -1200

The address of my Dougbert blog has changed slightly. Please re-subscribe to my blog feed at this new address:

Feed address: http://feeds.feedburner.com/DougbertsBlog
Web address: http://dougbert.com/blog/

I plan to continue blogging about SSIS and other SQL Server features.

I will not be posting any more on the old blog (http://dougbert.com/blogs/dougbert/). I intend to leave the past posts there. If you've saved or shared links to my past blog posts, they should remain valid.

Why the change?

I now work on SQL Server technologies other than SSIS on the documentation team at Microsoft. So this blog will expand beyond just SSIS.
My old blog used the free version of Community Server software. This has been discontinued and is now about 3 years old. I wanted to switch before the software died and took my blog down with it. I'm now up and running on BlogEngine.Net 2.0.

Thank you for continuing to follow me.

-Doug