SqlIntelligency: January 2017

Monday, January 30, 2017

Full Text Index helps to perform complex queries against character data. These queries can include words or phrase searching.
We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view.
The index can contain up to 1024 columns.

To create an Index, follow the steps:

Create a Full-Text Catalog
Create a Full-Text Index
Populate the Index

Background

When we want to use the full text index service we need to start the service first else it will raise the errors. So let's start with how to start the service and use the Full text index step by step.

Step 1

Create Table Tbl_Search
(
Id Int Primary Key Identity(1,1),
Title Varchar(500),
[Desc]Varchar(max)
)
Insert some rows in the created table.

Step 2

Now we will create full text index on our database and table for providing search in table. So create the Full Text Catalog on our database by using following query.CREATE FULLTEXT CATALOG FTSearch
In the query above we have created FullText Catalog with the name FTSearch.

Step 3

Now we will create full text index on our table Tbl_Search but for that we require the unique key id or primary key id so find the id of unique or primary key by using following command.SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

This command will display the all constraint names on tables present in our database from the output. Copy the Tbl_Search constraint name for creating a full text index on TblSearch.CREATE FULLTEXT INDEX ON Tbl_Search

(Title, [Desc] LANGUAGE 1033)

KEY INDEX PK__Tbl_Sear__3214EC0700551192

ON FTSearch
In the above statement you can see we are creating a FullText Index on table name with a parameter; this parameter is nothing but the column name of the table on which we want to create the full text index and language 1033 denotes the language English.

Step 4

Now it's time to search the records in a specified indexed table. For that we can write the queries like below.Select * from Tbl_Search Where Contains(Title,'Asp.Net')

Select * from Tbl_Search Where Freetext([Desc],'Asp.Net')
In the preceding queries you can see we have given a where clause with column name which is the column we want to search and what we want to search. The preceding queries retrive the rows of a table which contain ASP.Net in title column and the second query will retrieve the rows of ASP.Net in the desc column.

In some cases your queries gives an error like fdhost cannot be started. That means your FullTextIndex Service is not started; for that you have to first start the Demon Launcher for FullTextIndex service.

Step 5

For starting FullTextIndex service go to SQL Server Tool->SQL Server Configuration Manage->Service->FullTextSearch Demon Launcher if it is stopped then start this service for performing search operation with a contains and FreeText clause and restart the SQL Server instance.

Conclusion

In this way we can use the FullTextIndex on our database.

example:

e-business—searching for a product on a website:

SELECT product_id   
FROM products   
WHERE CONTAINS(product_description, ”Snap Happy 100EZ” OR FORMSOF(THESAURUS,’Snap Happy’) OR ‘100EZ’)   
AND product_cost < 200 ;


Recruitment scenario—searching for job candidates that have experience working with SQL Server:SELECT candidate_name,SSN   FROM candidates   
WHERE CONTAINS(candidate_resume,”SQL Server”) AND candidate_division =DBA;  

 A. Creating a unique index, a full-text catalog, and a full-text index

USE AdventureWorks2012;  
GO  
CREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);  
CREATE FULLTEXT CATALOG ft AS DEFAULT;  
CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume)   
   KEY INDEX ui_ukJobCand   
   WITH STOPLIST = SYSTEM;  
GO  

Creating a full-text index on several table columns

USE AdventureWorks2012;  
GO  
CREATE FULLTEXT CATALOG production_catalog;  
GO  
CREATE FULLTEXT INDEX ON Production.ProductReview  
 (   
  ReviewerName  
     Language 1033,  
  EmailAddress  
     Language 1033,  
  Comments   
     Language 1033       
 )   
  KEY INDEX PK_ProductReview_ProductReviewID   
      ON production_catalog;   
GO  
Creating a full-text index with a search property list without populating it
USE AdventureWorks2012;  
GO  
CREATE FULLTEXT INDEX ON Production.Document  
  (   
  Title  
      Language 1033,   
  DocumentSummary  
      Language 1033,   
  Document   
      TYPE COLUMN FileExtension  
      Language 1033   
  )  
  KEY INDEX PK_Document_DocumentID  
          WITH STOPLIST = SYSTEM, SEARCH PROPERTY LIST = DocumentPropertyList, CHANGE_TRACKING OFF, NO POPULATION;  
   GO  
Later, at an off-peak time, the index is populated:
ALTER FULLTEXT INDEX ON Production.Document SET CHANGE_TRACKING AUTO;  
GO

useful db-scripts

Rename database

EXEC sp_renamedb 'oldDatabase, 'newDatabase'

---------------convert column to row------------------------

declare @col_to_rows TABLE(stu_name VARCHAR(30),subject VARCHAR(10),marks int);
INSERT INTO @col_to_rows VALUES('GEORGE','ECO',77);
INSERT INTO @col_to_rows VALUES('GEORGE','HIS',99);
INSERT INTO @col_to_rows VALUES('GEORGE','MAT',64);
INSERT INTO @col_to_rows VALUES('GEORGE','GEO',85);
INSERT INTO @col_to_rows VALUES('GEORGE','SCI',98);
INSERT INTO @col_to_rows VALUES('ROBERT','ECO',71);
INSERT INTO @col_to_rows VALUES('ROBERT','HIS',90);
INSERT INTO @col_to_rows VALUES('ROBERT','MAT',84);
INSERT INTO @col_to_rows VALUES('ROBERT','GEO',95);
INSERT INTO @col_to_rows VALUES('ROBERT','SCI',58);
INSERT INTO @col_to_rows VALUES('TIMOTHY','ECO',56);
INSERT INTO @col_to_rows VALUES('TIMOTHY','HIS',55);
INSERT INTO @col_to_rows VALUES('TIMOTHY','MAT',67);
INSERT INTO @col_to_rows VALUES('TIMOTHY','GEO',54);
INSERT INTO @col_to_rows VALUES('TIMOTHY','SCI',45);

SELECT stu_name,
max(CASE WHEN subject='ECO' THEN marks ELSE 0 END) ECO,
max(CASE WHEN subject='HIS' THEN marks ELSE 0 END) HIS,
max(CASE WHEN subject='MAT' THEN marks ELSE 0 END) MAT,
max(CASE WHEN subject='GEO' THEN marks ELSE 0 END) GEO,
max(CASE WHEN subject='SCI' THEN marks ELSE 0 END) SCI
FROM @col_to_rows
GROUP BY stu_name

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

. A column has some negative values and some positive values. It is required to find the sum of negative numbers and the sum of the positive numbers in two separate columns

--select *from neg_pos

SELECT

SUM(CASE WHEN num < 0 THEN num ELSE 0 END) neg,

SUM(CASE WHEN num > 0 THEN num ELSE 0 END)pos

FROM neg_pos;

-------------------------------------------------
swap values between two rows for a table without using subquery

--ram 2000
--shyam 3000
--alex 5000
--joy 4000
-------------
--ram 2000
--shyam 5000
--alex 3000
--joy 4000
------------------------------------------------

update Employee_Test set EMP_SAL=a.EMP_SAL
from
(
SELECT
e1.emp_id, EMP_SAL=
(case e1.emp_id
when 2 then (LEAD(e1.EMP_SAL) OVER (ORDER BY e1.emp_id))
when 3 then LAG(e1.EMP_SAL) OVER (ORDER BY e1.emp_id)
else e1.Emp_Sal end
)
FROM Employee_Test e1
)a
join Employee_Test on Employee_Test.Emp_ID =a.Emp_ID

select *from Employee_Test
--------------------------------------------------

Create Database script

USE [master]

CREATE DATABASE [AuthenticationDB] ON PRIMARY

( NAME = N'AuthenticationDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER008R2\MSSQL\DATA\AuthenticationDB.mdf' ,

SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'AuthenticationDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER008R2\MSSQL\DATA\AuthenticationDB_log.ldf' ,

SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

ALTER DATABASE [AuthenticationDB] SET COMPATIBILITY_LEVEL = 100

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [AuthenticationDB].[dbo].[sp_fulltext_database] @action = 'enable'

end

ALTER DATABASE [AuthenticationDB] SET ANSI_NULL_DEFAULT OFF

ALTER DATABASE [AuthenticationDB] SET ANSI_NULLS OFF

ALTER DATABASE [AuthenticationDB] SET ANSI_PADDING OFF

ALTER DATABASE [AuthenticationDB] SET ANSI_WARNINGS OFF

ALTER DATABASE [AuthenticationDB] SET ARITHABORT OFF

ALTER DATABASE [AuthenticationDB] SET AUTO_CLOSE OFF

ALTER DATABASE [AuthenticationDB] SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE [AuthenticationDB] SET AUTO_SHRINK OFF

ALTER DATABASE [AuthenticationDB] SET AUTO_UPDATE_STATISTICS ON

ALTER DATABASE [AuthenticationDB] SET CURSOR_CLOSE_ON_COMMIT OFF

ALTER DATABASE [AuthenticationDB] SET CURSOR_DEFAULT GLOBAL

ALTER DATABASE [AuthenticationDB] SET CONCAT_NULL_YIELDS_NULL OFF

ALTER DATABASE [AuthenticationDB] SET NUMERIC_ROUNDABORT OFF

ALTER DATABASE [AuthenticationDB] SET QUOTED_IDENTIFIER OFF

ALTER DATABASE [AuthenticationDB] SET RECURSIVE_TRIGGERS OFF

ALTER DATABASE [AuthenticationDB] SET DISABLE_BROKER

ALTER DATABASE [AuthenticationDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

ALTER DATABASE [AuthenticationDB] SET DATE_CORRELATION_OPTIMIZATION OFF

ALTER DATABASE [AuthenticationDB] SET TRUSTWORTHY OFF

ALTER DATABASE [AuthenticationDB] SET ALLOW_SNAPSHOT_ISOLATION OFF

ALTER DATABASE [AuthenticationDB] SET PARAMETERIZATION SIMPLE

ALTER DATABASE [AuthenticationDB] SET READ_COMMITTED_SNAPSHOT OFF

ALTER DATABASE [AuthenticationDB] SET HONOR_BROKER_PRIORITY OFF

ALTER DATABASE [AuthenticationDB] SET RECOVERY FULL

ALTER DATABASE [AuthenticationDB] SET MULTI_USER

ALTER DATABASE [AuthenticationDB] SET PAGE_VERIFY CHECKSUM

ALTER DATABASE [AuthenticationDB] SET DB_CHAINING OFF

USE [AuthenticationDB]

Script for creating Authentication tables in existing database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[__MigrationHistory](
[MigrationId] [nvarchar](150) NOT NULL,
[ContextKey] [nvarchar](300) NOT NULL,
[Model] [varbinary](max) NOT NULL,
[ProductVersion] [nvarchar](32) NOT NULL,
CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED
(
[MigrationId] ASC,
[ContextKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetRoles](
[Id] [nvarchar](128) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserClaims](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ClaimType] [nvarchar](max) NULL,
[ClaimValue] [nvarchar](max) NULL,
[User_Id] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserLogins](
[UserId] [nvarchar](128) NOT NULL,
[LoginProvider] [nvarchar](128) NOT NULL,
[ProviderKey] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[LoginProvider] ASC,
[ProviderKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserRoles](
[UserId] [nvarchar](128) NOT NULL,
[RoleId] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUsers](
[Id] [nvarchar](128) NOT NULL,
[UserName] [nvarchar](max) NULL,
[PasswordHash] [nvarchar](max) NULL,
[SecurityStamp] [nvarchar](max) NULL,
[Discriminator] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_User_Id] ON [dbo].[AspNetUserClaims]
(
[User_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserLogins]
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_RoleId] ON [dbo].[AspNetUserRoles]
(
[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [IX_UserId] ON [dbo].[AspNetUserRoles]
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AspNetUserClaims] WITH CHECK ADD
CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id] FOREIGN KEY([User_Id])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserClaims] CHECK CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id]
GO
ALTER TABLE [dbo].[AspNetUserLogins] WITH CHECK ADD CONSTRAINT
[FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserLogins] CHECK CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]
GO
ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK ADD CONSTRAINT
[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[AspNetRoles] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]
GO
ALTER TABLE [dbo].[AspNetUserRoles] WITH CHECK ADD CONSTRAINT
[FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]
GO
USE [master]
GO
ALTER DATABASE [AuthenticationDB] SET READ_WRITE

GO

Search This Blog

Monday, January 30, 2017

Creating a full-text index on several table columns

useful db-scripts

Create Database script