Full Text Search in SQL Server

Full text search refers to a technique for searching a computer-stored document or database. In a full text search, the search engine examines all of the words in every stored document as it tries to match search words supplied by the user. Full-text searching techniques became common in online bibliographic databases in the 1970s. Most Web sites and application programs (such as word processing software) provide full text search capabilities. Some Web search engines, such as AltaVista employ full text search techniques, while others index only a portion of the Web pages examined by its indexing system

Full-text Index
* Regular SQL indexes stored under control of the database which they are defined. But full-text indexes stored in the file-system, but administered through the database.
* Only one full-text index can be defined per table.
* Regular SQL indexes, will be updated whenever the new rows has been added, modified, or deleted. But, Full-text index is based on population. Because, full-text service will use more system resources.

Full-text Catalogue
A full-text Catalogue contains full-text indexes in a database. Each catalogue can serve the indexing needs to one or more tables in within a database. A maximum of 256 full-text catalogs can be created on each server.

Full-text enabled Database
A database must be enabled for the full-text service. More than one full-text catalogues can be created and dropped in an enabled full-text database.


Full-text enabled Table
A table should be enabled for full-text support. While creating full-text index, the table will be associated with full-text catalogue. After the table is enabled, you can populate it with the data in columns enabled for the full-text support.

Avoid NULLs in database

Joe Celko said it best: "NULLs confuse people..."

My sentiments exactly. Of course, I don't expect to convince you by flashing a few quotes from reputable authors. Let's talk for a minute about what exactly NULLs do that cause this type of reaction. The first problem is that the definition of NULL is "unknown." So, one problem is determining whether one value is (not) equal to another value, when one or both values are NULL. This trickles down to many problems for a database engine and any associated applications. The following list details some of those problems:
 they are interpreted differently depending on compatibility level and ANSI settings;

For example, let's consider two values, x and y, that are both NULL. Since the definition of NULL is unknown, then you can't say x = y. However, with the ANSI setting ANSI_NULLs, this can be different. When this setting is FALSE, x = y ... however, when TRUE, x <> y. Confusing, no?

 the storage engine has to do extra processing for each row to determine if the NULLable column is in fact NULL -- this extra bit can have performance implications;
 they produce weird results when using calculations, comparisons, sorting and grouping;
 they create problems with aggregates and joins, such as different answers for COUNT(*) vs. COUNT(fieldname);
 they produce unpredictable results in statistics computations, particularly WITH ROLLUP and WITH CUBE;
 applications must add extra logic to handle inserting and retrieving results, which may or may not include NULL values;
 they cause unpredictable results with NOT EXISTS and NOT IN subqueries (working backwards, SQL determines that NULL columns belong or do not belong to the result set, usually for the wrong reasons);
 no language that supports embedded SQL has native support for NULL SQL values.

List Available DBCC Commands

DBCC Commands or Database Consistency Checker commands have been with SQL Server from its early ages itself. In this article we would list the available DBCC commands listed using the DBCC Help command. Many in this list are undocumented and are rarely used in practical scenarios. But these form the base for debugging complex problems. There are approximately 130 of them.

DBCC activecursors [(spid)]

DBCC addextendedproc (function_name, dll_name)

DBCC addinstance (objectname, instancename)

DBCC adduserobject (name)

DBCC auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid)

DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])

DBCC balancefactor (variance_percent)

DBCC bufcount [(number_of_buffers)]

DBCC buffer ( {'dbname' | dbid} [, objid [, number [, printopt={0|1|2} ][, dirty | io | kept | rlock | ioerr | hashed ]]])

DBCC bytes ( startaddress, length )

DBCC cachestats

DBCC callfulltext

DBCC checkalloc [('database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]

DBCC checkcatalog [('database_name')] [WITH NO_INFOMSGS]

DBCC checkconstraints [( 'tab_name' | tab_id | 'constraint_name' | constraint_id )] [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]

DBCC checkdb [('database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, DBCC TABLOCK]

DBCC checkdbts (dbid, newTimestamp)]

DBCC checkfilegroup [( [ {'filegroup_name' | filegroup_id} ] [, NOINDEX] )] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

DBCC checkident ('table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )

DBCC checkprimaryfile ( {'FileName'} [, opt={0|1|2|3} ])

DBCC checktable ('table_name'[, {NOINDEX | index_id | REPAIR}]) [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]

DBCC cleantable ('database_name'|database_id, 'table_name'|table_id, [batch_size])

DBCC cacheprofile [( {actionid} [, bucketid])

DBCC clearspacecaches ('database_name'|database_id, 'table_name'|table_id, 'index_name'|index_id)

DBCC collectstats (on | off)

DBCC concurrencyviolation (reset | display | startlog | stoplog)

DBCC config

DBCC cursorstats ([spid [,'clear']])

DBCC dbinfo [('dbname')]

DBCC dbrecover (dbname [, IgnoreErrors])

DBCC dbreindex ('table_name' [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]

DBCC dbreindexall (db_name/db_id, type_bitmap)

DBCC dbrepair ('dbname', DROPDB [, NOINIT])

DBCC dbtable [({'dbname' | dbid})]

DBCC debugbreak

DBCC deleteinstance (objectname, instancename)

DBCC des [( {'dbname' | dbid} [, {'objname' | objid} ])]

DBCC detachdb [( 'dbname' )]

DBCC dropcleanbuffers

DBCC dropextendedproc (function_name)

DBCC dropuserobject ('object_name')

DBCC dumptrigger ({'BREAK', {0 | 1}} | 'DISPLAY' | {'SET', exception_number} | {'CLEAR', exception_number})

DBCC errorlog

DBCC extentinfo [({'database_name'| dbid | 0} [, {'table_name' | table_id} [, {'index_name' | index_id | -1}]])]

DBCC fileheader [( {'dbname' | dbid} [, fileid])

DBCC fixallocation [({'ADD' | 'REMOVE'}, {'PAGE' | 'SINGLEPAGE' | 'EXTENT' | 'MIXEDEXTENT'}, filenum, pagenum [, objectid, indid])

DBCC flush ('data' | 'log', dbid)

DBCC flushprocindb (database)

DBCC free dll_name (FREE)

DBCC freeproccache

DBCC freeze_io (db)

DBCC getvalue (name)

DBCC help ('DBCC_command' | '?')

DBCC icecapquery ('dbname', stored_proc_name [, #_times_to_icecap (-1 infinite, 0 turns off)]) Use 'DBCC icecapquery (printlist)' to see list of SP's to profile. Use 'DBCC icecapquery (icecapall)' to profile all SP's.

DBCC incrementinstance (objectname, countername, instancename, value)

DBCC ind ( { 'dbname' | dbid }, { 'objname' | objid }, { indid | 0 | -1 | -2 } )

DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid | indname})

DBCC inputbuffer (spid)

DBCC invalidate_textptr (textptr)

DBCC invalidate_textptr_objid (objid)

DBCC iotrace ( { 'dbname' | dbid | 0 | -1 }, { fileid | 0 }, bufsize, [ { numIOs | -1 } [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )

DBCC latch ( address [, 'owners'] [, 'stackdumps'])

DBCC lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}]|[{'STALLREPORTTHESHOLD', stallthreshold}])

DBCC lockobjectschema ('object_name')

DBCC log ([dbid[,{0|1|2|3|4}[,['lsn','[0x]x:y:z']|['numrecs',num]|['xdesid','x:y']|['extent','x:y']|['pageid','x:y']|['objid',{x,'y'}]|['logrecs',{'lop'|op}...]|['output',x,['filename','x']]...]]])

DBCC loginfo [({'database_name' | dbid})]

DBCC matview ({'PERSIST' | 'ENDPERSIST' | 'FREE' | 'USE' | 'ENDUSE'})

DBCC memobjlist [(memory object)]

DBCC memorymap

DBCC memorystatus

DBCC memospy

DBCC memusage ([IDS | NAMES], [Number of rows to output])

DBCC monitorevents ('sink' [, 'filter-expression'])

DBCC newalloc - please use checkalloc instead

DBCC no_textptr (table_id , max_inline)

DBCC opentran [({'dbname'| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]

DBCC outputbuffer (spid)

DBCC page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ][, cache={0|1} ])

DBCC perflog

DBCC perfmon

DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}, targetfile, targetpg, order={1|0})

DBCC pintable (database_id, table_id)

DBCC procbuf [({'dbname' | dbid}[, {'objname' | objid}[, nbufs[, printopt = { 0 | 1 } ]]] )]

DBCC proccache

DBCC prtipage (dbid, objid, indexid [, [{{level, 0} | {filenum, pagenum}}] [,printopt]])

DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]

DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt = { 0 | 1} ])

DBCC rebuild_log (dbname [, filename])

DBCC renamecolumn (object_name, old_name, new_name)

DBCC resource

DBCC row_lock (dbid, tableid, set) - Not Needed

DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)

DBCC setcpuweight (weight)

DBCC setinstance (objectname, countername, instancename, value)

DBCC setioweight (weight)

DBCC show_statistics ('table_name', 'target_name')

DBCC showcontig (table_id | table_name [, index_id | index_name] [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])

DBCC showdbaffinity

DBCC showfilestats [(file_num)]

DBCC showoffrules

DBCC showonrules

DBCC showtableaffinity (table)

DBCC showtext ('dbname', {textpointer | {fileid, pageid, slotid [,option]}})

DBCC showweights

DBCC shrinkdatabase ({dbid | 'dbname'}, [freespace_percentage [, {NOTRUNCATE | TRUNCATEONLY}]])

DBCC shrinkdb is no longer supported. Please use shrinkdatabase instead

DBCC shrinkfile ({fileid | 'filename'}, [compress_size [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])

DBCC sqlmgrstats

DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} | {THREADS} | {LOGSPACE})

DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, 'THREADID'}] )]

DBCC tab ( dbid, objid )

DBCC tape_control {'query' | 'release'}[,('\\.\tape')]

DBCC tec [( uid[, spid[, ecid]] )]

DBCC textall [({'database_name'|database_id}[, 'FULL' | FAST] )]

DBCC textalloc ({'table_name'|table_id}[, 'FULL' | FAST])

DBCC thaw_io (db)

DBCC traceoff [( tracenum [, tracenum ... ] )]

DBCC traceon [( tracenum [, tracenum ... ] )]

DBCC tracestatus (trace# [, ...trace#])

DBCC unpintable (dbid, table_id)

DBCC updateusage ({'database_name'| 0} [, 'table_name' [, index_id]]) [WITH [NO_INFOMSGS] [,] COUNT_ROWS]

DBCC upgradedb (db)

DBCC usagegovernor (command, value)

DBCC useplan [(number_of_plan)]

DBCC useroptions

DBCC wakeup (spid)

DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)

clustered index in SQL Server

What is a clustered index

First off, we'll go through what a clustered index is. SQL Server has two types of indexes, clustered indexes and non-clustered indexes. Both types are organized in the same way with a b-tree structure. The difference between them lies in what the leaf-level nodes -- the lowest level of the tree -- contains. In a clustered index the leaf-level is the data, while the leaves of a non-clustered index contains bookmarks to the actual data. This mean that for a table that has a clustered index, the data is actually stored in the order of the index. What the bookmarks of the non-clustered index point to depends on if the table also has a clustered index or not. If it does have a clustered index then the leaves of non-clustered indexes will contain the clustering key -- the specific value(s) of the column(s) that make up the clustered index -- for each row. If the table does not have a clustered index it is known as a heap table and the bookmarks in non-clustered indexes are in RID format (File#:Page#:Slot#), i.e. direct pointers to the physical location the row is stored in. Later in this article we will see why this difference is important. To make sure that everyone understands the difference between a clustered index and a non-clustered index I have visualized them in these two images (clustered | non-clustered). The indexes correspond to those of this table:

CREATE TABLE EMPLOYEES
(
empid int NOT NULL CONSTRAINT ix_pkEMPLOYEES PRIMARY KEY NONCLUSTERED
, name varchar(25) NOT NULL
, age tinyint NOT NULL
)


CREATE CLUSTERED INDEX ixcEMPLOYEES ON EMPLOYEES (name)


INSERT INTO EMPLOYEES (empid, name, age) VALUES (1, 'David', 42)
INSERT INTO EMPLOYEES (empid, name, age) VALUES (2, 'Tom', 31)
INSERT INTO EMPLOYEES (empid, name, age) VALUES (3, 'Adam', 27)
INSERT INTO EMPLOYEES (empid, name, age) VALUES (4, 'John', 22)


SELECT * FROM EMPLOYEES WHERE name = 'John'
SELECT * FROM EMPLOYEES WHERE empid = 1

In the real indexes these four rows would fit on the same page, but for this discussion I've just put one row on each page. So, to return results for the first query containing WHERE name = 'John' SQL Server will traverse the clustered index from the root down through the intermediate node levels until it finds the leaf page containing John, and it would have all the data available to return for the query. But to return results for the second query, it will traverse the non-clustered index until it finds the leaf page containing empid 1, then use the clustering key found there for empid 1 (David) for a lookup in the clustered index to find the remaining data (in this case just the column age is missing). You can see this for yourself by viewing the execution plan for the queries in Query Analyzer (press Ctrl-K too see the plan).

Returning number of affected rows

By default, SQL Server returns a friendly message to report the total number of rows affected by each query. This is a great setting for debugging your applications or modifying data directly in Query Analyzer. However, your front-end application doesn't need to know the number of affected rows -- it simply needs the data. Sending this message can introduce unnecessary network overhead.

What should you do? Use "SET NOCOUNT ON" in all your stored procedures to reduce network chatter.

SQL Server Join Hints

JOIN hints can be used in a query to specify the type of JOIN the Query Optimizer is to use for the execution plan. The JOIN options are:

Loop
Merge
Hash
The syntax for a JOIN hint is (using an INNER JOIN as an example):

FROM table_one INNER [LOOP | MERGE | JOIN] JOIN table_two

Here's an example:

FROM header_table INNER LOOP JOIN detail_table

As you can see, the JOIN hint is between the type of JOIN (in this example, INNER) and the JOIN keyword. Only one JOIN hint can be used per JOIN in a query. In addition, JOIN hints can only be used when the ANSI JOIN syntax is used, not the older Microsoft JOIN syntax.

The syntax above is not the only option to add a JOIN hint to a query. In addition, you can also use OPTION clause. Using the OPTION clause specifies that the hint be used throughout all of the query. While multiple hints can be added to the OPTION clause, each query hint can be used only once. In addition, only one OPTION clause can be used per query.

Here's an example of using the OPTION clause:

OPTION (INNER) or OPTION (MERGE) or OPTION (HASH)

The Query Optimizer always tries to identify the fastest way to JOIN tables. The fastest JOIN method is the Loop JOIN, followed by the Merge and the Hash JOIN. While the Query Optimizer always tries to perform a Loop JOIN if possible, it is not always possible, and one of the other two types may have to be used.

Before you attempt to use JOIN hint to specify a JOIN type, you should first take a look at your query, and the indexing of the relevant tables, to see if you can find a way, other than using a hint, to induce the Query Optimizer to use a Loop JOIN. Keep in mind that if you specify a Loop JOIN hint for a JOIN that is currently using a HASH JOIN, that you may get worse, not better performance. So always test JOIN hints to see what their end result in performance really is.

Occasionally, you may find yourself having to perform an INNER JOIN on tables located in databases on different servers. Generally speaking, SQL Server will evaluate if the performing the JOIN will perform better on the local or remote server, and run the JOIN their accordingly. But in some cases, such as when the remote table is not a SQL Server table, this is not possible.

SQL Server provides a special JOIN hint that you can use to force a JOIN to occur on the remote server, if this is appropriate, called REMOTE. The REMOTE hint is used to specify that the JOIN operation be performed on the right table of the JOIN. You might consider this if the left table of the JOIN is the local table and the right table in the JOIN is the remote table. Of course, doing this only makes sense if the right table has more rows that the left table. REMOTE cannot be used if the tables use different collation.

SQL Server Table Hints

Index hints (a form of a table hint) are used to specify which index or indexes you want used when a query runs. When you specify one or more indexes, the Query Optimizer will use them when creating the execution plan for your query.

The syntax for a table hint is:

SELECT column_list FROM table_name WITH (INDEX (index_name) [, ...])

Where:

column_list is the list of columns you want SELECTed.

table_name is the name of the table with the relevant indexes.

index_name is the index name or index ID for the index or indexes you want used by the Query Optimizer.

Here's an example:

SELECT memberID, fname, lname FROM members WITH (INDEX(memberID_index))

While you can use an index ID instead of the index name, this is not a good idea. This is because index IDs can change if you drop and recreate the indexes differently than when you first created them.

If you want to specify multiple indexes, to force an index intersection or an index covering as a result of an index join, you can by adding each index name or ID, one after another, with each one separated by a comma.

If you want to force a table scan, use the hint: (INDEX(0)).

Index hints should only be used when the Query Optimizer, for whatever reason, does not select the available index, and if using the index provides a performance enhancement over not using an index.

The FASTFIRSTROW table hint is functionally equivalent to the OPTION FAST (number_of_rows) hint, and either one can be used interchangedly, although Microsoft recommends that you use OPTION FAST, as support of FASTFIRSTROW may be removed in future versions of SQL Server.

For queries that return large resultsets, this hint can be handy. What this hint does is to tell the Query Optimizer to retrieve X number of rows of the total resultset as fast as it can, then continue on and return the full resultset as time permits. This acts to get the initial part of your data retrieved very quickly.

On the other hand, while it generally speeds up the retrieval of the first X number of rows, the rest of the rows are often returned more slowly that they would have been if the hint had not been specified.