Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Killing FTS

In almost every application that I've helped build, we had a need to perform textual searches of fields. Even short fields, like names, usually need some sort of wildcard search to let users find information in the database. When we get to larger volumes, such as note or description fields, it's essential that the application allow a way to find information that doesn't exactly match some term.

SQL Server has had the Full-Text Search (FTS) subsystem for a long time. Built as an improvement over the LIKE keyword, FTS implements its own index system that better understands language and takes some burden off the developer when trying to find keywords and terms inside of a volume of text. However, this system hasn't advanced much since 2005, with limited improvements in each version, and more stable performance, but not substantially improved. New languages get added, but not development language improvement.

These days, many of the individuals that need to search text fields used ElasticSearch instead. In previous years, Lucerne was a popular choice, along with other software add-ons. In fact, most people want to use anything other than SQL Server's FTS. Even at SQLServerCentral, our experimentation with FTS led us to abandon this early on as the work required to structure a UI around the CONTAINS code, along with poor search performance, made the decision easy. Google custom search or another package are far superior.

I wonder if it's time for SQL Server to abandon FTS. Maybe their limited development resources would be better spent integrating other third party engines into SQL Server. Or maybe some of their profits are better spent licensing or purchasing a different technology for searching. Certainly they should deprecate the current FTS implementation and allow applications using it to continue to do so, but cease additional work beyond limited maintenance.

At the very least, they should move in some direction. I'm sure Microsoft could come up with ways to improve FTS, but I don't know they can do it quickly enough, or that it's a good use of developer resources. There is other technology that does this very well, so take advantage of it and build hooks that make integration simple. That might be the big search win for SQL Server databases.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
Redgate SQL ProvisionRedgate Database Devops
  Featured Contents

Exporting Stored Procedure Results to a Table

pamelamooney1966 from SQLServerCentral

A quick trick from Pamela Mooney for getting results from a proc into a table.

Free Webinar: The State of SQL Server Monitoring 2019

Press Release from Redgate

With a line up like this, it's a webinar not to be missed! Join Grant Fritchey, Chris Yates, Annette Allen, and Tony Maddonna on Tuesday June 25th as they discuss the key findings of Redgate’s 2019 State of SQL Server Monitoring survey.

Database provisioning and containers – can you help?

Additional Articles from Redgate

If you work with SQL Server, Redgate would love to talk to you about how you’re using (or thinking of using) containers, how you see databases fitting into your workflows involving them, and any challenges you’ve hit so far.

The BI Journey: The Journey Begins

Additional Articles from SimpleTalk

Gogula Aryalingam continues telling the story of the BI solution created by a business analyst intern. In this article, after the initial success, plans are made to sustain and grow the project.

From the SQL Server Central Blogs - Basic SQL Server Configuration Help for Involuntary DBAs

K. Brian Kelley from Databases – Infrastructure – Security

After my presentation at the Techno Security and Digital Forensics conference, I had a information security professional stop by to ask a few questions. He’s in the position where...

From the SQL Server Central Blogs - Implicit Conversion Insights with XE

SQLRNNR from SQL RNNR

Seldom does a DBA have the opportunity to get out in front of infrequent or random errors such as implicit conversions. More often than not, it is the privilege...

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Clearing Wait Stats

How do I reset the wait statistics in sys.dm_os_wait_stats?

Think you know the answer? Click here, and find out if you are right.

 

 

 

  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Statistics Profile Results

How do the results from SET STATISTICS PROFILE appear in SSMS?

Answer: As another result set

Explanation: The results from SET STATISTICS PROFILE appear as another result set. Ref: SET STATISTICS PROFILE - https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-profile-transact-sql?view=sql-server-2017

Discuss this question and answer on the forums

 

 

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


SQL Server 2017 - Administration
Overall Guidance and Suggestion of environment - First off thanks for your time and much appreciated in all suggestions or ideas. The environment i have is totally different than any i have ever been a part of and hoping that i can get some assistance.  It will go against pretty much everything theory based and pretty much shooting from the hip.  okay..here […]
SQL Server 2016 - Administration
Automate copy and restore of all PROD databases to a separate server - Hi, We are using ola hallengren backup script to backup databases backups. Now, I'm planning to automate copying those weekly full backups to a separate server and restore them. Can you please advise if there are any automated methods or scripts available to achieve this? Thanks, -jdc
SQL Server 2016 - Development and T-SQL
Trigger to update/Insert record - I have a table that resides on 2 different Servers with the same database name.  I want to create a trigger that updates db2.table2 if db1.table1 has a record inserted or updated and visa versa, if db1.table1 gets updated or inserted then update db2.table2.  The table has the same structure.  How can i do this […]
T-SQL - Ways to get customized rows? - We have a table where we keep rows with vanilla settings for products, and customized rows per product per customer as they see fit to override. When a customer does not override, the vanilla rows with vanilla settings are selected but when customized rows exists for products and customers, the customized rows must be returned […]
Select today's date minus 6 months - I am pretty new to SQL Server. I have created date variables in Access but need to transfer them over to SQL. The syntax is different so I need a little push in the right direction. I am doing a query that selects a date between 6 months from today and 1 month from today […]
Administration - SQL Server 2014
Index fragmentation - hello to everyone , i notice in database tha some indexes the total fragmentation is more than 50%,60%... is this bad for the database performance.is there a  way to reduse this percent, may be with rebuild index task?
Development - SQL Server 2014
Declared datetime Parameter not working correctly in sql 2014 - We have a Data base we migrated to 2014 from 2008, everything is working fine except we have a stored proc that has the following it just hangs, but if I replace the parameter in the select statement with getdate() it works. or if i change the compatibility mode to 2008. when I am debug […]
SQL 2012 - General
Plan to migrate to new SQL server... - I've been tasked with moving our production SQL databases to a new server, and in order to minimize the impact on applications we've decided to try renaming the new server to be the same as the old server. So the plan I've come up with is as follows: Set up log shipping to the new […]
SQL Server 2008 - General
SQL Server Agent - Scheduled Jobs Log - Hello, Is there a SQL log somewhere that tracks date and changes made to a Scheduled Job? What I am trying to is determine when a specific Stored Procedure has been commented out within a Scheduled Job Step. My guess is that the logs will not be this detailed, but even Modification Dates would be […]
Reporting Services
Change Shared Data Source Programatically? - We have a shared datasource which mostly will point at a report db, but occasionally will have to switch to live data, is there a way to programmatically change the db its pointed at.   Maybe as a step on failure/success of sql agent job? Thanks in advance.   (I know I can do this […]
How to create/edit RDLC SSRS Reports - I'm relly hoping someone here has had to work with the RDLC version of SSRS reports because Ive had no luck on the MSDN forums with this.  Its easy and I mean EASY to find everything about creating rdl SSRS report files but when it comes to the rdlc variant its like trying to find […]
ssrs 2012 performance issue - I have an ssrs 2012 report that takes along time to run. I would like to look at the execution plan to see what I can do to speed up the main part of a report. Can you tell me how I can look at the execution plan in the SSRS report and/or running the […]
SSRS 2016
How do I get SSRS to connect to an older version of MySQL? - Three weeks ago I was tasked with working on a SSRS report that to run against a MySQL database. Until that time I'd never done anything with MySQL, so that's a learning curve as well. The only way I know of connecting to MySQL is with an ODBC connection. (As an aside, if there's another […]
Powershell
Multiple Invoke-SQLCmd don't produce output - Hi, I am running below 3 Inovke-SQLCmd satatements on a powershell window. I am expecting and output from all 3, but only the first one gives me the data. Below is what I get as output. The commands give me output when running one at a time but not when all 3 are executed together. […]
Integration Services
Tabular Model - Newbie Question - Hi All, I am looking after my first tabular model and was wondering if it is correct that you can only create DAX measures within the measure grid in model.bin file via Visual Studio. As the previous multi dimensional model I worked on used MDX for the measures. Thanks
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -