Introduction to Full-Text Search -In Microsoft SQL Server 2005

Full-text search allows fast and flexible indexing for keyword-based query of text data stored in a Microsoft SQL Server database. In contrast to the LIKE predicate, which only works on character patterns, full-text queries perform linguistic searches against this data, by operating on words and phrases based on rules of a particular language.

In Microsoft SQL Server 2005, full-text search delivers enterprise search functionality. Significant enhancements in the areas of performance, manageability, and functionality deliver exceptional search capabilities for applications of any size.

The performance benefit of using full-text search can be best realized when querying against a large amount of unstructured text data. A LIKE query against millions of rows of text data can take minutes to return; whereas a full-text query can take only seconds or less against the same data, depending on the number of rows that are returned.

You can build full-text indexes on columns that contain char, varchar and nvarchar data. Full-text indexes can also be built on columns that contain formatted binary data, such as Microsoft Word documents, stored in a varbinary(max) or image column. You cannot use the LIKE predicate to query formatted binary data.

To create a full-text index on a table, the table must have a single, unique not null column. For example, consider a full-text index for the Document table in Adventure Works in which the DocumentID column is the primary key column. A full-text index indicates that the word "instructions" is found at word number 24 and word number 44 in the DocumentSummary column for the row associated with a DocumentID of 3. This index structure supports an efficient search for all items containing indexed words and advanced search operations, such as phrase searches and proximity searches. For more information, see Document Table (AdventureWorks).

When processing a full-text query, the search engine returns to SQL Server the key values of the rows that match the search criteria. If you want to use a full-text query to find the documents that include the word "instructions", the DocumentID values of 3, 4, 6, 7 and 8 are obtained from the full-text index. SQL Server then uses these keys to return the matching rows.

Using SESSION_USER to return the user name of the current session

Examples

The following example declares a variable as nchar, assigns the current value of SESSION_USER to that variable, and then prints the variable with a text description.

DECLARE @session_usr nchar(30);
SET @session_usr = SESSION_USER;
SELECT 'This session''s current user is: '+ @session_usr;
GO

ISDATE() TSQL Return Value

Column value (varchar)	ISDATE return value
NULL	0
Abc	0
100, -100, 100 a, or 100.00	0
.01	0
-100.1234e-123	0
.231e90	0
$100.12345, - $100.12345, or $-1000.123	0
as100 or 1a00	0
1995-10-1, 1/20/95, 1995-10-1 12:00pm, Feb 7 1995 11:00pm, 1995-10-1, or 1/23/95	1
13/43/3425 or 1995-10-1a	0
$1000, $100, or $100 a	0

HOST_ID() -TSQL Example

The following example creates a table that uses HOST_ID() in a DEFAULT definition to record the terminal ID of computers that insert rows into a table recording orders.

CREATE TABLE Orders
(OrderID int PRIMARY KEY,
CustomerID nchar(5) REFERENCES Customers(CustomerID),
TerminalID char(8) NOT NULL DEFAULT HOST_ID(),
OrderDate datetime NOT NULL,
ShipDate datetime NULL,
ShipperID int NULL REFERENCES Shippers(ShipperID));
GO

HOST_NAME() T-SQL Example

Examples  
     

The following example creates a table that uses HOST_NAME() in a DEFAULT definition to record the workstation name of computers that insert rows into a table recording orders.

CREATE TABLE Orders
  (OrderID     int        PRIMARY KEY,
   CustomerID  nchar(5)   REFERENCES Customers(CustomerID),
   Workstation nchar(30)  NOT NULL DEFAULT HOST_NAME(),
   OrderDate   datetime   NOT NULL,
   ShipDate    datetime   NULL,
   ShipperID   int        NULL REFERENCES Shippers(ShipperID));
GO

Wildcard character - TSQL

Wildcard character	Description	Example
%	Any string of zero or more characters.	WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore)	Any single character.	WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).	WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).	WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter is not l.

Example:t-SQL Using generic syntax for finding gaps in identity values

-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON
DECLARE @minidentval column_type
DECLARE @maxidentval column_type
DECLARE @nextidentval column_type
SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY)
FROM tablename
IF @minidentval = IDENT_SEED('tablename')
SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
FROM tablename t1
WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND
@maxidentval AND
NOT EXISTS (SELECT * FROM tablename t2
WHERE t2.$IDENTITY = t1.$IDENTITY +
IDENT_INCR('tablename'))
ELSE
SELECT @nextidentval = IDENT_SEED('tablename')
SET IDENTITY_INSERT tablename OFF
-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.

-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL
DROP TABLE img
GO
CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)
INSERT img(company_name) VALUES ('New Moon Books')
INSERT img(company_name) VALUES ('Lucerne Publishing')
-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON

DECLARE @minidentval smallint
DECLARE @nextidentval smallint
SELECT @minidentval = MIN($IDENTITY) FROM img
IF @minidentval = IDENT_SEED('img')
SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
FROM img t1
WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND
NOT EXISTS (SELECT * FROM img t2
WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))
ELSE
SELECT @nextidentval = IDENT_SEED('img')
SET IDENTITY_INSERT img OFF

Examples :Using the IDENTITY property with CREATE TABLE

T-SQL


USE AdventureWorks
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)

INSERT new_employees
(fname, minit, lname)
VALUES
('Karin', 'F', 'Josephs')

INSERT new_employees
(fname, minit, lname)
VALUES
('Pirkko', 'O', 'Koskitalo')

Examples :Listing the advanced configuration options

USE master;
GO
EXEC sp_configure 'show advanced option', '1';

Example : Using nested cursors to produce report output

SET NOCOUNT ON

DECLARE @vendor_id int, @vendor_name nvarchar(50),
@message varchar(80), @product nvarchar(50)

PRINT '-------- Vendor Products Report --------'

DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From Vendor: ' +
@vendor_name

PRINT @message

-- Declare an inner cursor based
-- on vendor_id from the outer cursor.

DECLARE product_cursor CURSOR FOR
SELECT v.Name
FROM Purchasing.ProductVendor pv, Production.Product v
WHERE pv.ProductID = v.ProductID AND
pv.VendorID = @vendor_id -- Variable value from the outer cursor

OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product

IF @@FETCH_STATUS <> 0
PRINT ' <>'

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = ' ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product
END

CLOSE product_cursor
DEALLOCATE product_cursor
-- Get the next vendor.
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor

Examples :Using simple cursor and syntax

Examples :Using simple cursor and syntax

DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor

System stored procedures Description

sp_cursor_list
Returns a list of cursors currently visible on the connection and their attributes.

sp_describe_cursor
Describes the attributes of a cursor, such as whether it is a forward-only or scrolling cursor.

sp_describe_cursor_columns
Describes the attributes of the columns in the cursor result set.

sp_describe_cursor_tables
Describes the base tables accessed by the cursor.