SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 21 of 365 Next >

It is estimated that most of the data stored in an enterprise isn't contained in structured databases like SQL Server or Oracle. It's true that most of the day to day data entry that is used by multiple members of the organization is entered into Customer Relationship Management (CRM) systems or Enterprise Resource Management (ERM) systems that use a database as the storage medium, but it is also true that much of an employee's time isn't spent in these systems. Just like IT professionals, a lot of the work done throughout the day is stored in Microsoft Word, Excel or even e-mails.

There are a lot of arguments for making all of this information structured, that is, stored in rows and columns in a database. Databases are designed for critical data, they have extended search and location mechanisms, they are secure, and maintenance on the data is scheduled and logged.

But there's also an argument for allowing binary documents to carry a lot of the organization's data. If you think of the data content as needing some kind of front end, then Word and Excel fit that need. Microsoft Office applications are in almost every company, and most every employee that performs data tasks knows how to use them. They are simple and easy to understand.

But they are unstructured. What that means is that the users don't have a standard way of recording the data, they don't use the same terms within the documents, and they don't even name them or store them the same way. When data is locked away in these files you can't easily find them, or search the data they hold. Another big issue is that this data isn't always secured, because the users might save the files on a network share that isn't backed up, or on their local drives.

Since the users will often use the tools they know best, and since the data they enter is important to the organization, you need to find a way to compromise. You need to allow unstructured data in your company, but be able to find it, search within it, and you need to be able to secure it.

SQL Server has a few mechanisms that allow you to store unstructured data in a structured database table. You need to plan for using them, and you need to understand the tools the database provides to store, search and index these objects to take full advantage of these features. But before I explain the Full-Text Search service in SQL Server, you need to understand that it isn't the only way to deal with this problem.

Also, there are some tradeoffs with using SQL Server to store binary documents. Not all document types are searchable. Also, storing large Word or Excel files can make your database quite large, which complicates maintenance and recovery.

But after you've studied the problem and decided that storing the binary files in the database is the right route for you, then you need to plan for a procedural change in your organization as well as a program to insert and retrieve the data from the database. Let's take a look at each of these planning points.

Although Full-Text Search in SQL Server allows you to store binary documents in the database, it doesn't automatically do it for you. The procedural change you'll need to plan out is which documents will be stored in the database and how you will do that.

You don't need to store every document a user creates in a database. A majority of user documents are temporary or single-use only. The criteria for storage is some document that will be used by multiple people, has information that will be needed later, has a need to be safely stored, or a combination of these reasons.

The other part of the planning decision is the programming required to store and retrieve the binary documents from the database. While SQL Server Full-Text Search provides simple statements to search the documents, you don't store them with typical INSERT or UPDATE statements. For binary documents, you'll need to "stream" the data in by opening a channel, sending the documents, and then closing the channel. I'll explain that process in a tutorial, but for now we'll concentrate on where to do that more than how.

The most logical place to create a program hook to send binary documents to SQL Server is inside the Enterprise Management System used by your organization. This program, which normally follows the course of your business, is the primary place that generates the content for a lot of the e-mails, Word documents, and Excel spreadsheets. You should code an "attachments" tab, button or other control to allow users to store the document up into the system. It isn't enough to allow this storage, however. You must also think about allowing others to "check out" or work on the document, whether that should be done sequentially or in parallel, how you handle changes, whether you should store versions of the documents and so forth.

Now that you've thought about storing the documents, you need to plan for using them in your system. You can use the Full-Text Search on char, varchar, nvarchar, varbinary(max) and image data types. The process involves storing the documents (or text data), setting up the Full-Text Service, enabling and indexing the columns in the database, and then using various statements in your searches.

I've already explained a little about storing the documents, so next we need to examine the Full-Text Service. This is a separate program that runs as a service on the SQL Server system that handles the process. It does not need to be started as an administrator, but it does need to be an account that can see the hard drive on the server. If this service is not running, the feature won't work properly.

The next part of the equation is the database. A database needs to be enabled to allow Full-Text indexes. This is the default setting, but you need to check it by running the following command:

USE DatabaseName;
GO
SELECT DATABASEPROPERTY(’DatabaseName’, ’IsFullTextEnabled’);
GO

If the data is not enabled, it's easiest to use the graphical management tools to do so. It's in the Database Properties tab when you right-click the database name.

Once you've determined that the database is ready, you create the Full-Text index on the columns that hold the binary files. I'll give you an example of that in another tutorial. Once the index is created, you need to fill it, which is called a "population." This process crawls through the document, and creates a word-map for the index.

There are a couple of special things to note about the Full-Text Index. Unlike other indexes in SQL Server, a Full-Text Index is actually another structure, stored outside of the database file locations. That means that you have to back it up separately from the database. The other consideration is that the Full-Text Index is not updated with your other automatic maintenance that you have set up. Full-Text Index population is done either manually or with a separate schedule.

There's one more important consideration for Full-Text Indexes. You can only have one per table. That really isn't a big restriction, since you'll normally only store one column of data this way.

Now that you have the documents stored, the indexes created and populated and backed up, you need to work with the searches. In other tutorials we'll explore the following types of query statements:

CONTAINS (In the WHERE clause of a SELECT statement)
FREETEXT (In the WHERE clause of a SELECT statement)
CONTAINSTABLE (In the FROM clause of a SELECT statement)
FREETEXTTABLE (In the FROM clause of a SELECT statement)

For now, you need to know that you can use Full-Text Search to look not only for a specific word or phrase, but also similar words, inflections, and even thesaurus-type lookups.

Although there is a great deal of planning when using Full-Text Search, it is a feature that can greatly benefit your organization. It allows the best of two worlds: easy creation of familiar documents, and the security, accessibility and maintenance of critical data.

Informit Articles and Sample Chapters

If you're ready to try out indexing a Full-Text column, you can find the tutorial on that here.

I have a blog entry here which might be useful for the administrators among us.

Online Resources

SQL Server 2005 has even more enhancements to Full-Text Search. You can read about those here.

< Back Page 21 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address