Why full-text population update is necessary?

--The below script will show why we need to regularly update full text index, if the change_tracking is not set to Auto

--Creating database

--There are two filegroups. The second flegroup will be used for the storing the full text objects.

--But, this is not a mendetory.

Create

database FullTextSearch

on primary (name = Data, filename = 'c:\FulltextSearch\data.mdf')

, FILEGROUP fulltextfilegroup (name= fulltextgroup,filename='c:\FulltextSearch\fulltext.ndf')

log on (name = Log, filename = 'c:\FulltextSearch\log.ldf');

Use FullTextSerch;

/******Why the full text index manual upade should run regularly?********/

If

object_id('FullTextedTable') is null

Drop table dbo.FullTextedTable;

Create

table dbo.FullTextedTable

(

id int identity(1,1),

name nvarchar(100)

);

-- any single column unique index is required for population for full text index

Alter table FullTextedTable add constraint PK_FullTextedTable primary key (id);

--Insert data into table

Insert into FullTextedTable

select 'Sachin'

union all

select 'Rahul'

union all

select 'Saurav'

--Creating full text catalog

--We are setting accent sensitivity to off because we do not require it

--We explicitly set it off because by default it will same as base database and at most of the user

--created database it is on

--Check-it by running follwoing query, it's SQL_Latin1_General_CP1_CI_AS

--select databasepropertyex('FullTextSearch','collation')

Create

Fulltext CataLog FullTextCatalog

on fileGroup fulltextfilegroup

IN PATH 'c:\FulltextSearch'

with ACCENT_SENSITIVITY = off

As Default

;

--Creating full text index

--We are setting change_tracking off and set no population for not to populate index just after index is created

Create fulltext Index on FullTextedTable

([name] language 1033)

key index PK_FullTextedTable

on FullTextCatalog

with change_tracking OFF, No Population

GO

-- Now we are populating the index, this should be done when workload is less

Alter fulltext index on FullTextedTable Start Full Population

Go

Alter fulltext index on FullTextedTable set change_tracking Manual

GO

--You can check the status in below table

select * from sys.fulltext_indexes;

--query the table

select * from FullTextedTable

where CONTAINS(name,'saurav')

--Now update the third row

update FullTextedTable set name = 'Virendra'

where id = 3;

--now again running the query

select * from FullTextedTable

where CONTAINS(name,'saurav')

-- This will return 3,Virendra

-- This is not correct and this can be misleading when there are more rows

--below query will not retrun any row

select * from FullTextedTable

where CONTAINS(name,'Virendra')

--Now we run the manual update

--This will update the index

Alter fulltext index on FullTextedTable Start update Population

;

--now again running the query, this will not retrun any rows

select * from FullTextedTable

where CONTAINS(name,'saurav')

--Now this will return the data

select * from FullTextedTable

where CONTAINS(name,'Virendra')

Script to truncate log of all the user databases

/************************************************************************************
Description: This script file will truncate the log file. When we run this script for first time this can take some time. So it is advisable that we run after working hours. Though this script can be run online, it is advisable to run the script when very minimal user activity is happening.

Warning : This should not be run on the production environmnet or databases

Credentials: User who runs this must be a member of sysadmin role. SA or any other user with same privileges can run this script

Version : sql server 2005 , sql server 2008
************************************************************************************/

declare @name nvarchar(255)
declare @recovery_model_desc nvarchar(255)
declare @recovery_model_desc_temp nvarchar(255)
declare @tsql nvarchar(355)
declare @tsql1 nvarchar(355)
declare @logfileName nvarchar(max)

declare database_cursor cursor for select name,recovery_model_desc
from sys.databases where name not in ('master','msdb','model','tempdb','adventureworks')

open database_cursor

fetch next from database_cursor into @name,@recovery_model_desc

while @@fetch_status = 0
begin

Print char(13) + 'Processing staring for ' + @name + ' at ' + cast(getdate() as nvarchar(20))

select @name = '[' + @name + ']'

select @tsql = 'use ' + @name + ';select top 1 @logfileNameOUT=name from sys.database_files where type = 1'

exec sp_executesql @tsql, N'@logfileNameOUT nvarchar(max) OUTPUT',@logfileNameOUT =@logfileName OUTPUT


print @logfileName
select @logfileName = '[' + @logfileName + ']'
select @tsql = 'use ' + @name + ';dbcc shrinkfile (' + @logfileName + ',1)'
exec sp_executesql @tsql

select @recovery_model_desc_temp= case when @recovery_model_desc = 'simple' then 'full' else 'simple' end

select @tsql1 = 'alter database ' + @name + ' set recovery ' + @recovery_model_desc_temp
exec sp_executesql @tsql1

select @tsql1 = 'alter database ' + @name + ' set recovery ' + @recovery_model_desc
exec sp_executesql @tsql1


exec sp_executesql @tsql

Print 'Processing completed for ' + @name + char(13)

fetch next from database_cursor into @name,@recovery_model_desc

end

close database_cursor

deallocate database_cursor

check values in text column for lower case or upper case

Today, I came across the situation where we need to update the text column and we want all the column text to UPPER CASE. But, we do not want to update the rows for which this text column already contains the word in upper case.


use AdventureWorks
GO

if object_id('tblCheckCase') is not null
drop table tblCheckCase
GO

Create table tblCheckCase
(
id int identity(1,1) primary key,
firstName nvarchar(10)
)
GO

insert into tblCheckCase(firstName)
select 'chintak'
union all
select 'chintan'
union all
select 'dhyey'
union all
select 'VIKAS'
union all
select 'DATTU'
GO

select firstName,charindex( firstName collate SQL_Latin1_General_Cp1_CS_AS,upper(firstName)) from tblCheckCase
GO

update tblCheckCase
set firstName = UPPER(firstName)
where charindex( firstName collate SQL_Latin1_General_Cp1_CS_AS,upper(firstName)) = 0
GO

select firstName as firstNameAfter,charindex( firstName collate SQL_Latin1_General_Cp1_CS_AS,upper(firstName)) from tblCheckCase
GO

Drop table tblCheckCase
GO

Use of TOP WITH TIES clause in SELECT queries

This caluse can be used to get additional rows from the base result set with the same value .
That is if the last of returned result set consists the same vlaue of order by item for other rows, those rows will also be returned.
- This can be only used in select statemnet.
- Order by clause is necessary for using this clause
- This can also be used with PERCENT clause.

Example:-

if object_id('test_top','U') is not NULL
drop table test_top
GO
create table test_top (a int, b int)
GO
insert into test_top
select 1,1
union all
select 2,1
union all
select 3,3
union all
select 4,3
go

Now,
select top 1 b from test_top

This will return only one row.
/*
b
----
1
*/


so,
select top 1 with ties b from test_top order by b

--this will return 2 rows as

/*
b
---
1
1
*/

But when we use top 100 percent caluse "with ties" not needed.