Neste post irei falar sobre um novo recurso do SQL Server 2012, FileTable.

Filetables são user-tables especializadas, com o schema pré-definido para armazenar dados FILESTREAM, arquivos e pastas, além dos respectivos atributos.

Cada Filetable representa uma hierarquia de arquivos e diretórios, onde cada registro na tabela corresponde a um específico arquivo ou diretório.

Uma das características deste novo recurso é que ao mover um arquivo para um diretório pré especificado, um registro é criado na Filetable automaticamente.

Vamos para a parte prática:

PARTE 1 - CONFIGURAÇÃO DO SERVIDOR
Para testar este recurso, primeiro temos que habilitar o FILESTREAM no serviço do SQL Server.

Abra o SQL Server Configuration Manager e localize o serviço "SQL Server", vá em propriedades e localize a aba "FILESTREAM", marque as opções conforme a imagem abaixo:




Veja que ele também cria um compartilhamento de rede. No meu caso eu acesso através do endereço: \\NOTEOZI\mssqlserver

Temos que configurar o nível de acesso do FILESTREAM, vamos utlizar o nível "Full access enabled"

use Master 
GO
exec sp_configure filestream_access_level, 2;
reconfigure;

PARTE 2 - CRIAÇÃO DO DATABASE E DA FILETABLE

Vamos criar um Database com o recurso FILESTREAM e também definir o diretório para o uso do FILESTREAM neste database.

CREATE DATABASE TesteFT
ON
PRIMARY ( NAME = TesteFT,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TesteFT.mdf'),
FILEGROUP FSTesteFTGroup CONTAINS FILESTREAM( NAME = FSTesteFT, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FSTesteFT')
LOG ON ( NAME = TesteFT_Log,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TesteFT_Log.ldf')
WITH FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'FileTables')
GO

Para verificar a criação do database com as características do FILESTREAM, utilizamos o comando:

SELECT * FROM sys.database_filestream_options WHERE database_id=DB_ID('TesteFT')

Vamos criar nossa FileTable, especificando o nome da PK e de 2 Unique Key criadas automaticamente.

Estas especificações são opcionais porém acho mais organizado e melhor documentado.

USE TesteFT
GO
CREATE TABLE Arquivos AS FileTable
WITH (
      FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME=PK_Arquivos,
      FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME=UK_ArquivosStream,
      FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME=UK_ArquivosPath
)
GO

OBS: Em alguns ambientes precisei usar a instrução "SET ANSI_NULLS ON" antes da criação da tabela

Para verificar a criação de uma FileTable utilize o comando: SELECT * FROM sys.filetables

Você pode visualizar o diretório de uma FileTable específica através do menu "Explore FileTable Directory"




Vamos fazer alguns testes, copie uns arquivos para dentro da pasta especificada e depois execute o comando:

SELECT name, file_type, cached_file_size FROM Arquivos




PARTE 3 - FULLTEXT

Para que o FullText reconheça o máximo filtros possíveis é necessário executar os comandos:

EXEC sp_fulltext_service 'load_os_resources', 1
EXEC sp_fulltext_service 'restart_all_fdhosts'

Para verificar: select * from sys.fulltext_document_types

Caso seja necessário, você pode baixar o "Microsoft Office 2010 Filter Packs" no endereço: http://www.microsoft.com/download/en/details.aspx?id=17062

Vamos criar nosso catálogo FullText para que possamos fazer nossas pesquisas aos arquivos e seus conteúdos.

CREATE FULLTEXT CATALOG FTC_Arquivo

Antes de criar nosso índice, iremos criar uma "SEARCH PROPERTY LIST" para que possamos pesquisar também as propriedades de um arquivo (Autor, Título, tag, etc).

Para obter a lista de possíveis filtros acesse o link: http://msdn.microsoft.com/pt-br/library/ee677618.aspx

Funciona para as extensões .docx, .xlsx, .pptx

CREATE SEARCH PROPERTY LIST ArqPropriedades;
GO
ALTER SEARCH PROPERTY LIST ArqPropriedades
ADD 'Titulo'
WITH
( PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
PROPERTY_INT_ID = 2
);
ALTER SEARCH PROPERTY LIST ArqPropriedades
ADD 'Autor'
WITH
( PROPERTY_SET_GUID = 'f29f85e0-4ff9-1068-ab91-08002b27b3d9',
PROPERTY_INT_ID = 4
);

Para verificar:
SELECT property_name, property_set_guid, property_int_id FROM sys.registered_search_properties




Agora sim podemos criar nosso FULLTEXT INDEX.

Na criação do você pode especificar o idioma utlizado, basta consultar(select * from sys.fulltext_languages). Português do Brasil é o 1046

CREATE FULLTEXT INDEX ON Arquivos
(
   name language 1046,
   file_stream TYPE COLUMN file_type language 1046
)
KEY INDEX UK_ArquivosStream
ON FTC_Arquivo
WITH SEARCH PROPERTY LIST = ArqPropriedades
GO

Vamos fazer algumas pesquisas:

SELECT name Arquivo, file_stream.GetFileNamespacePath(1,1) Local
FROM Arquivos
WHERE CONTAINS(file_stream, 'tabelas')

SELECT name Arquivo, file_stream.GetFileNamespacePath(1,1) Local
FROM Arquivos
WHERE CONTAINS(PROPERTY(file_stream, 'Autor'), 'Henrique')

SELECT name Arquivo, file_stream.GetFileNamespacePath(1,1) Local
FROM Arquivos
WHERE CONTAINS(PROPERTY(file_stream, 'Titulo'), 'olap')




PARTE 4 - SEMANTIC SEARCH

Vamos utilizar um novo recurso chamado Semanti Search, com ele podemos verificar ocorrencias dentro de um arquivo, comparar arquivos e outras funções.

Para começar precisamos instalar um database de semantica

Baixe o arquivo neste endereço: http://www.microsoft.com/downloads/details.aspx?familyid=67E1CEB5-5B3B-4002-B43F-7A2807E9DA5D&displaylang=pt-br

Instale na pasta indicada.
Anexar o database ao seu servidor:

CREATE DATABASE semanticsdb
ON ( FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb.mdf' )
LOG ON ( FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb_log.ldf' )
FOR ATTACH
Registrar como semantic language statistics database:
EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb'
Vamos apagar o FullText Index para recriá-lo usando semantics:
DROP FULLTEXT INDEX ON Arquivos
GO
CREATE FULLTEXT INDEX ON Arquivos
(
name language 1046 STATISTICAL_SEMANTICS,
file_stream TYPE COLUMN file_type language 1046 STATISTICAL_SEMANTICS
)
KEY INDEX UK_ArquivosStream
ON FTC_Arquivo
WITH SEARCH PROPERTY LIST = ArqPropriedades
GO

Vamos utilizar algumas funções:

SemanticKeyPhraseTable - Lista a quantidade de valores que aparecem com maior frequência dentro do arquivo.

SELECT name, keyphrase, AVG(score) AS score
FROM SemanticKeyPhraseTable (Arquivos, *)
INNER JOIN Arquivos
ON stream_id = document_key
where score>0.7
GROUP BY name, keyphrase
ORDER BY name, score DESC




Neste exemplo peguei somente as palavras com mais de 70% de frequencia
SemanticSimilarityTable - Lista a similaridade entre um arquivo indicado e os outros

DECLARE @ArqID uniqueidentifier
SELECT @ArqID = stream_id FROM Arquivos WHERE name = '[BI] Dimensão Tempo.docx'

SELECT name ArquivoComparado, score Similaridade
FROM SemanticSimilarityTable(Arquivos, *, @ArqID)
INNER JOIN Arquivos 
ON stream_id = matched_document_key
ORDER BY score DESC




Este recurso é uma novidade muito boa, principalmente para o pessoal que trabalha com GED.