Post reply

Starting with Full Text Search: Suggested links ...

  • March 28, 2017 at 2:20 am

    #323249

    Hi,

    I am starting with Full Text Search. Am reading a chapter on that in a book by well known authors, but the chapter is atrocious.
    Do you have any suggested links which are a good introduction to this? 
    (Most of our servers are 2012, though recently we have started installing 2014 too.)

    Thanks!
    Ashish

  • March 28, 2017 at 3:10 am

    #1935220

    I'm horrified to know the answer, but it's not the book by Jason Strate and me is it?

    If so, I'd love to know, what makes the chapter so very bad?

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 28, 2017 at 6:27 pm

    #1935412

    Hi Grant, 

    Sorry to make you a bit nervous 🙂
    No, it is not your book!

    I have not read your book yet. Before committing myself to reading another book, if possible, I would like to read some short introductions to FTS. Do you know of any good material available online?

    Thanks!
    Ashish

  • March 28, 2017 at 7:32 pm

    #1935414

    ash304 - Tuesday, March 28, 2017 6:27 PM

    Hi Grant, 

    Sorry to make you a bit nervous 🙂
    No, it is not your book!

    I have not read your book yet. Before committing myself to reading another book, if possible, I would like to read some short introductions to FTS. Do you know of any good material available online?

    Thanks!
    Ashish

    These are both good:
    Understanding Full-Text Indexing in SQL Server - Robert Sheldon
    Full-Text Search -- Microsoft's online documentation

    The Robert Sheldon Article is a great intro. The Microsoft documentation is pretty precise about what's involved in setting up and maintaining FTS.

    Note that setting up and maintaining FTS is not a trivial task.  4 out of 5 times I've seen it implemented it's been in environments where the same objective could be accomplished using good ol' fashioned T-SQL.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • March 28, 2017 at 9:24 pm

    #1935420

    Hi Alan,

    Thanks a lot for these links. They both look good! I will spend some time studying these.
    While cursorily glancing over both of these, I could not find any way to add documents (word, pdf, xml etc.) to a table in a database. Do you know of a simple SQL Syntax for that or some online article which explains that. I have been googling for that for over an hour, but surprisingly could not find anything ... (stackoverflow is blocked in our wonderful IT Services company!!)

    Thanks!
    Ashish

  • March 29, 2017 at 3:21 am

    #1935449

    ash304 - Tuesday, March 28, 2017 6:27 PM

    Hi Grant, 

    Sorry to make you a bit nervous 🙂
    No, it is not your book!

    I have not read your book yet. Before committing myself to reading another book, if possible, I would like to read some short introductions to FTS. Do you know of any good material available online?

    Thanks!
    Ashish

    I like Alan's suggestion of the Microsoft documentation. That's the basis for just about anything you do with SQL Server.

    Good to know it's not our book.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • March 29, 2017 at 7:50 am

    #1935494

    ash304 - Tuesday, March 28, 2017 9:24 PM

    Hi Alan,

    Thanks a lot for these links. They both look good! I will spend some time studying these.
    While cursorily glancing over both of these, I could not find any way to add documents (word, pdf, xml etc.) to a table in a database. Do you know of a simple SQL Syntax for that or some online article which explains that. I have been googling for that for over an hour, but surprisingly could not find anything ... (stackoverflow is blocked in our wonderful IT Services company!!)

    Thanks!
    Ashish

    Filetables are where you would store things like word documents. The document is stored on your server and accessable to users/applications but accessible in SQL Server as if it's just another column in a table. I've never worked with them personally but have seen cool presentations on them at SQL PASS. They seem simple to setup. You can store XML, Excel, PDFs and stuff like that in there as well.

    I did a quick google search for "sql server filetable full text search" and came across this article which looks good and should get you started : Full Text Searches on Documents in FileTables

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • March 29, 2017 at 9:06 pm

    #1935628

    Hi Alan,

    Thanks for the pointer to FileTables and the link to get started.

    As of now, I want to keep things simple for myself and so want to learn a bit of FTS without involving FileTables. Later I will expand my knowledge by including them too.

    I could find a simple way to insert documents (word, pdf etc.) into a table with a simple T-SQL command by using OPENROWSET.
    Some good links for this are:
    https://www.mssqltips.com/sqlservertip/1643/using-openrowset-to-read-large-files-into-sql-server/
    https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql

    And here are some simple commands which work fine (on SQL Server 2012):

    create table DocumentTable (
    file_name nvarchar(60),
    file_type nvarchar(60),
    document varbinary(max)
    )
    go

    insert into DocumentTable (file_name, file_type, document)
    select 'sample_text.txt', '.txt',
            * from OPENROWSET (BULK N'C:\Temp\sample_text.txt', SINGLE_BLOB) as document
    go

    insert into DocumentTable (file_name, file_type, document)
    select 'MIT Big Data Certificate.pdf', '.pdf',
            * from OPENROWSET (BULK N'C:\Temp\MIT Big Data Certificate.pdf', SINGLE_BLOB) as document
    go

    Thanks!
    Ashish

  • March 29, 2017 at 9:43 pm

    #1935632

    ash304 - Wednesday, March 29, 2017 9:06 PM

    Hi Alan,

    Thanks for the pointer to FileTables and the link to get started.

    As of now, I want to keep things simple for myself and so want to learn a bit of FTS without involving FileTables. Later I will expand my knowledge by including them too.

    I could find a simple way to insert documents (word, pdf etc.) into a table with a simple T-SQL command by using OPENROWSET.
    Some good links for this are:
    https://www.mssqltips.com/sqlservertip/1643/using-openrowset-to-read-large-files-into-sql-server/
    https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql

    And here are some simple commands which work fine (on SQL Server 2012):

    create table DocumentTable (
    file_name nvarchar(60),
    file_type nvarchar(60),
    document varbinary(max)
    )
    go

    insert into DocumentTable (file_name, file_type, document)
    select 'sample_text.txt', '.txt',
            * from OPENROWSET (BULK N'C:\Temp\sample_text.txt', SINGLE_BLOB) as document
    go

    insert into DocumentTable (file_name, file_type, document)
    select 'MIT Big Data Certificate.pdf', '.pdf',
            * from OPENROWSET (BULK N'C:\Temp\MIT Big Data Certificate.pdf', SINGLE_BLOB) as document
    go

    Thanks!
    Ashish

    Very, very cool!

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply