
<h2>Example of setup the full-text search on SQL Server</h2>
<p>
  SQL Server full-text search requires installing on SQL Server, after install
  the full-text search you can setup the index as follows.
</p>
<h3>How to check Full-Text Search is enable at SQL Server?</h3>
<p>
  If following return 1&nbsp;means enabled<code></code>, <code>0</code> means is
  not.
</p>
<pre
  class="language-sql"
><code class="language-sql">SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')</code>
</pre>
<h3>How to get all full text indexed column</h3>
<pre class="language-sql"><code class="language-sql">select t.name, sc.name
from
    sys.fulltext_index_columns ft
    inner join sys.columns sc
        on sc.[object_id] = ft.[object_id]
        and sc.[column_id] = ft.[column_id]
	join sys.tables  t   ON sc.object_id = t.object_id</code>
</pre>
<h3>How to setup index for specific tables</h3>
<pre
  class="language-sql"
><code class="language-sql">CREATE FULLTEXT CATALOG MyFullTextSearch; 
CREATE UNIQUE INDEX MyIndex ON MyTable(TheUniqueKey);  
CREATE FULLTEXT INDEX ON MyTable.MyDescriptionColumn  
(  
    MyDescriptionColumn
        TYPE COLUMN FileExtension  
        Language 1033  
)  
KEY INDEX MyIndex ON MyFullTextSearch
WITH CHANGE_TRACKING AUTO
GO </code></pre>
<pre
  class="language-sql"
><code class="language-sql">CREATE FULLTEXT CATALOG MyFullTextSearch; 

CREATE UNIQUE INDEX MyIndex ON MyTable(Id);  

CREATE FULLTEXT INDEX ON MyTable(MyColumn)
KEY INDEX MyIndex ON MyFullTextSearch
WITH CHANGE_TRACKING AUTO
GO </code>
</pre>
<h3>How to Query data by full text</h3>
<p>
  The following Query will search <code>MyTable</code>'s
  <code>MyColumn</code> for key word <code>Surface Pro</code>
</p>
<pre class="language-sql"><code class="language-sql">
select * from MyTable where FreeText(MyColumn, 'Surface Pro')
</code>
</pre>
<p>
  If you search for a column is not full-text indexed or SQL Server is not
  installed full-text feature, you'll see following errors.
</p>
<pre class="language-sql"><code class="language-sql">
select * from MyTable where FreeText(MyColumnNotFullTextIndexed, 'Surface Pro')
</code></pre>
<pre
  class="language-sql"
><code class="language-sql">Msg 7601, Level 16, State 2, Line 15
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'MyColumnNotFullTextIndexed' because it is not full-text indexed.
</code>
</pre>
<h2>Walk through with Example</h2>
<h3>Create a table use for test full-text index</h3>
<pre class="language-sql"><code class="language-sql">
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestFullText](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Text] [nvarchar](max) NULL,
	[Text2] [nvarchar](max) NULL,
 CONSTRAINT [PK_TestFullText] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO
SET IDENTITY_INSERT [dbo].[TestFullText] ON 

GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (1, N'I am your father, boogle', N'I buy')
GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (2, N'father boogle', N'he buys')
GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (3, N'farther', N'they buy')
GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (4, N'Cooking', N'I bought')
GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (5, N'cooks', N'I''m buying')
GO
INSERT [dbo].[TestFullText] ([Id], [Text], [Text2]) VALUES (6, N'COOK', NULL)
GO
SET IDENTITY_INSERT [dbo].[TestFullText] OFF
GO
</code>
</pre>
<h3>Add Full-Text Index to the Table</h3>
<p>
  Assume we'd like to use&nbsp;<code>Text</code> and
  <code>Text2</code>&nbsp;columns the o use full-text index, we can add as
  following.
</p>
<pre
  class="language-sql"
><code class="language-sql">CREATE FULLTEXT CATALOG MyTestFullTextTableTextSearch; 
CREATE UNIQUE INDEX MyIndex2 ON TestFullText(Id);  
CREATE FULLTEXT INDEX ON TestFullText(Text, Text2)
KEY INDEX MyIndex2 ON MyTestFullTextTableTextSearch
WITH CHANGE_TRACKING AUTO
GO 
</code>
</pre>
<h3>Run Full-Text index query</h3>
<p>
  As you can see, the full-text search for the BUY at the Text2 column will
  return all the sterming word, such as buy, bought, buys.
</p>
<pre class="language-sql"><code class="language-sql">
  select * from TestFullText where Text2 like '%buy%'
  select * from TestFullText where FreeText(Text2, 'buy')
  select * from TestFullText where Contains(Text2, 'buy')
</code>
</pre>
<p>
  <img
    class="img-responsive"
    src="https://d1p0mp3f1fx0hz.cloudfront.net/_gs75bse1y.png"
    alt=""
  />
</p>
<h4>Reference</h4>
<ul>
  <li>
    <a
      href="https://docs.microsoft.com/en-us/sql/relational-databases/search/full-text-search#overview"
      target="_blank"
      >MSDN - Full-Text Search</a
    >
  </li>
  <li>
    <a
      href="https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql"
      >MSDN - CREATE FULLTEXT INDEX (Transact-SQL)</a
    >
  </li>
  <li>
    <a
      href="https://docs.microsoft.com/en-us/sql/relational-databases/search/create-and-manage-full-text-catalogs"
      >MSDN - Create and Manage Full-Text Catalogs</a
    >
  </li>
  <li>
    <a
      href="https://docs.microsoft.com/en-us/sql/relational-databases/search/create-and-manage-full-text-indexes"
      >Create and Manage Full-Text Indexes</a
    >
  </li>
  <li>
    <a
      href="https://docs.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search"
      >MSDN - Get Started with Full-Text Search</a
    >
  </li>
  <li>
    <a
      href="https://docs.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search"
      >MSDN - Query with Full-Text Search</a
    >
  </li>
  <li>
    <a
      href="http://sql-vs-nosql.blogspot.com/2013/11/indexes-comparison-mongodb-vs-mssqlserver.html"
      >Blog - SQL vs No.SQL</a
    >
  </li>
  <li>
    <a
      href="https://msdn.microsoft.com/en-us/library/ms912047(v=winembedded.10).aspx"
      >MSDN - Local ID</a
    >
  </li>
  <li>
    <a href="../../blog/post/mongodb-text-search" target="_blank"
      >Blog - MongoDB Text Search</a
    >
  </li>
</ul>


Example of how to setup the full-text search on SQL Server.

SQL Server Full Text Search Example


<h2>
  SQL example to search table name in case if you only know the column name
</h2>
<pre class="language-sql">
SELECT      t.name AS 'TableName', c.name  AS 'ColumnName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%MyColumn_DontKnowWhichTableIs%'
ORDER BY    TableName
            ,ColumnName;

</pre>


Code Snippet - Use T-SQL to Search Table Name by Column Name


<h2>
  This is old way, there's better way to do it, but kind of sampel example of
  how to do pagination at sql level
</h2>
<pre class="language-sql">
declare @requestMaxReturnItems int
declare @requestPageNumber int
declare @startRow int
declare @endRow int
declare @totalPageByMaxReturnNumber int
declare @totalCount int;

-- temp test

-- request from ui
-- max items return from this sql
set @requestMaxReturnItems = 10;
-- which page number to return
set @requestPageNumber = 1
--set @startRow = 1
--set @endRow = 10

select @totalCount = count(*) FROM [OrangeGrid].[dbo].[DiagnosticLog];
set @totalPageByMaxReturnNumber = @totalCount / @requestMaxReturnItems;
set @startRow = (@requestPageNumber - 1) * @requestMaxReturnItems;
set @endRow = @requestPageNumber * @requestMaxReturnItems;

print @totalPageByMaxReturnNumber

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY YourTable.CreateDate desc ) AS RowNum, *
          FROM      YourTable
        ) AS t
WHERE   RowNum &gt; @startRow
    AND RowNum &lt;= @endRow
ORDER BY RowNum
</pre>


T-SQL Paging Sample


<pre class="language-sql"><code>
select pl.Id as 'ID',
MyType = 
case 
		when exists (select * from [TestDB].[dbo].SomeTable fsl (nolock) where fsl.Id = pl.Id) then
		'Type1' 
		else 
		'Type2' 
End
,		
av.AttachmentId as 'Another ID',
Convert(varchar, av.DateLastUpdated, 101) as 'Date', 
Convert(varchar, av.DateEntered, 101) as 'Date 2',
Address = 
(select TOP 1
RTRIM(adr.Address1) + '' + COALESCE(adr.Address2,'') + ',' + adr.CityName + ',' + COALESCE(adr.CountyRegionName,'') + ',' + adr.StateProvCode + RTRIM(adr.PostalZipCode)as 'Address'
from [TestDB].[dbo].[TestDBAddress] as pladd<span style="font-family: 'Helvetica Neue', Helvetica, Arial, Helvetica, sans-serif;"> (nolock) </span><br />
inner join [TestDB].[dbo].[Address] as adr<span style="font-family: 'Helvetica Neue', Helvetica, Arial, Helvetica, sans-serif;"> (nolock) </span><br />
on pladd.AddressID = adr.AddressID
where pladd.Id = pl.Id)
from [TestDB].[dbo].[TestDB] as av<span style="font-family: 'Helvetica Neue', Helvetica, Arial, Helvetica, sans-serif;"> (nolock) </span><br />
inner join [TestDB].[dbo].[TestDB1] as pla<span style="font-family: 'Helvetica Neue', Helvetica, Arial, Helvetica, sans-serif;"> (nolock) </span><br />
on av.Id = pla.Id
where 
and av.DateLastUpdated between '2014-05-01' AND '2014-05-31'

</code></pre>


Code Snippet about T-SQL Query


<p>
  There're many way to find sql server deadlock or resolve the issue. At this
  blog, I will show how to use SQL Server Profiler to show deadlock or one
  example how to fix it.
</p>
<h2>Find deadlock information from sql server</h2>
<ul>
  <li>Open SQL Server Profiler</li>
  <li>Create new Trace file</li>
  <li>
    At General Tab check Save to file, and save file to shared folder. This is
    because after stop trace, you can analytic the trace log information.
  </li>
  <li>
    At Events Selection tab, expend Locks and check "Deadlock graph". Trace
    Properties will show "Events Extraction Settings" tab, then click "Save
    Deadlock XML events separately" and save file to shared folder.
  </li>
  <li>Click "run"&nbsp;</li>
  <li>
    If you know where the application cause the dead lock then go to the
    application and re cause the error.
  </li>
  <li>Stop the trace.</li>
  <li>
    Go to the trace file you saved to shared folder, and search "dead", you
    should find the deadlock xml.&nbsp;
  </li>
</ul>
<h2>Fix deadlock</h2>
<p>My issue was fixed by run following sql command.</p>
<pre class="language-sql">EXEC sp_updatestats</pre>
<h2>Reference</h2>
<ul>
  <li>
    <a
      href="http://blogs.msdn.com/b/saponsqlserver/archive/2015/01/18/deadlock-analysis-examples.aspx"
      target="_blank"
      >http://blogs.msdn.com/b/saponsqlserver/archive/2015/01/18/deadlock-analysis-examples.aspx</a
    >
  </li>
  <li>
    <a
      href="http://blogs.msdn.com/b/saponsqlserver/archive/2013/01/31/deadlock-analysis-basic.aspx"
      target="_blank"
      >http://blogs.msdn.com/b/saponsqlserver/archive/2013/01/31/deadlock-analysis-basic.aspx</a
    >
  </li>
  <li>
    <a
      href="http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/"
      target="_blank"
      >http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/</a
    >
  </li>
  <li>
    <a
      href="http://sqlmag.com/database-performance-tuning/gathering-deadlock-information-deadlock-graph"
      target="_blank"
      >http://sqlmag.com/database-performance-tuning/gathering-deadlock-information-deadlock-graph</a
    >
  </li>
</ul>


Category SQL

SQL Server Full Text Search Example

Code Snippet - Use T-SQL to Search Table Name by Column Name

T-SQL Paging Sample

Code Snippet about T-SQL Query

How to Find Deadlock Information from SQL Server