All Categories

  • SQL Server Full Text Search Example

    1/5/2017 -
    Example of setup the full-text search on SQL Server SQL Server full-text search requires installing on SQL Server, after install the full-text search you can setup the index as follows. How to check Full-Text Search is enable at SQL Server? If following return 1 means enabled, 0 means is not. SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') How to get all full text indexed column 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.
    SQL
  • Code Snippet about T-SQL Query

    6/1/2014 -
    What's this, well some kind of script need to get some kind of report. Boring task.
    SQL
  • T-SQL Paging Sample

    10/2/2015 -
    This is old way, there's better way to do it, but kind of sampel example of how to do pagination at sql level 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.
    SQL
  • Code Snippet - Use T-SQL to Search Table Name by Column Name

    1/1/2016 -
    SQL example to search table name in case if you only know the column name 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.
    SQL
  • How to Find Deadlock Information from SQL Server

    2/2/2014 -
    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. Find deadlock information from sql server Open SQL Server Profiler Create new Trace file 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. 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. Click "run"  If you know where the application cause the dead lock then go to the application and re cause the error. Stop the trace. Go to the trace file you saved to shared folder, and search "dead", you should find the deadlock xml.
    SQL