
### The problem

Recently, have to resolve below sample data to find ranking per each type, thought of share this, it might have other ways of achieve this but below is what I come out with for how to resolve this problem in SQL.

Below is the sample data to describe this problem.
![result with json array](/images/big-query-group-similar-values/img1.png)

So for instance, for the **_type 3_**, the first should be **_300_** because total is **_50_**, and the rest of the order should be below, also exclude the null.

- 300 -> 50
- 7777 -> 45
- 500 -> 40
- 33333 -> 30
- 200 -> 11
- 100 -> 5

Same as for the **_type 9_**, the order should be below, also ignore the null as well.

- 3000 -> 514
- 77777 -> 458
- 5000 -> 407
- 3000 -> 304
- 333333 -> 304
- 1000 -> 50
- 2000 -> 11

Below is the BQ Script actual solve this problem, ideally just find the duplicate count for either of the column, value_a or value_b, then group data set for value_a and value_b and join the data, finally remove the target duplicate value_a or value_b

```SQL
with sample_data as (
select 1 id, 3 as type, 100 value_a, null value_b, 5 total
union all
select 1 id, 3 as type, 200 value_a, null value_b, 11 total
union all
select 1 id, 3 as type,300 value_a, null value_b, 30 total
union all
select 1 id, 3 as type,300 value_a, 444 value_b, 20 total
union all
select 1 id, 3 as type,null value_a, null value_b, 20 total
union all
select 1 id, 3 as type,null value_a, 33333 value_b, 30 total
union all
select 1 id, 3 as type,500 value_a, null value_b, 40 total
union all
select 1 id, 3 as type,600 value_a, 7777 value_b, 5 total
union all
select 1 id, 3 as type,null value_a, 7777 value_b, 40 total
union all
select 1 id, 9 as type, 1000 value_a, null value_b, 50 total
union all
select 1 id, 9 as type, 2000 value_a, null value_b, 11 total
union all
select 1 id, 9 as type,3000 value_a, null value_b, 307 total
union all
select 1 id, 9 as type,3000 value_a, 4444 value_b, 207 total
union all
select 1 id, 9 as type,null value_a, 333333 value_b, 304 total
union all
select 1 id, 9 as type,5000 value_a, null value_b, 407 total
union all
select 1 id, 9 as type,6000 value_a, 77777 value_b, 54 total
union all
select 1 id, 9 as type,null value_a, 77777 value_b, 404 total
union all
select 2 id, 3 as type,10 value_a, null value_b, 5 total
union all
select 2 id, 3 as type,20 value_a, null value_b, 10 total
union all
select 2 id, 9 as type,null value_a, null value_b, 20 total
)
, agg_value_a_duplicate as (
  select id, type, value_a, count(value_a) count
  from sample_data
  where value_a is not null
  group by id, type, value_a
  having count > 1
)
, agg_value_b_duplicate as (
  select id, type, value_b, count(value_b) count
  from sample_data
  where value_b is not null
  group by id, type, value_b
  having count > 1
)
, remove_value_b as (
  select sample_data.value_b from sample_data
  where sample_data.value_a in (
    select value_a from agg_value_a_duplicate
  )
  and sample_data.value_b is not null
)
, remove_value_a as (
  select sample_data.value_a from sample_data
  where sample_data.value_b in (
    select value_b from agg_value_b_duplicate
  )
  and sample_data.value_a is not null
)
, agg_value_a as (
  select id, type, value_a, sum(total) total
  from sample_data
  where sample_data.value_a is not null
  group by id, type, value_a
)
, agg_value_b as (
  select id, type, value_b, sum(total) total
  from sample_data
  where sample_data.value_b is not null
  group by id, type, value_b
)
, join_value_a_value_b as (
  select id, type, value_a, null as value_b, total from agg_value_a
  union all
  select id, type, null as value_a, value_b, total from agg_value_b
)
select *
from join_value_a_value_b
where (value_b is null or value_b not in (select value_b from remove_value_b))
and (value_a is null or value_a not in (select value_a from remove_value_a))
order by id, type, total desc
```

#### Conclusion

Above script does resolve the issue, but wonder if there's other way?


Use Big Query to aggregate data to find ranking


Here provide simple SQL Query to compare between `page_view`, `session_start` and `scroll` event.
Also, compare the `page_view` with other 2 to see how much difference between each days.

```SQL
with page_view as (
  SELECT event_date, count(1) as page_view_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'page_view'
  group by 1
),
session_start as (
  SELECT event_date, count(1) as session_start_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'session_start'
  group by 1
),
scroll as (
  SELECT event_date, count(1) as scroll_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'scroll'
  group by 1
)
select
page_view.event_date
, page_view.page_view_count
, session_start.session_start_count
, scroll.scroll_count
, safe_subtract(page_view.page_view_count, session_start.session_start_count) page_minus_session
, safe_subtract(page_view.page_view_count, scroll.scroll_count) page_minus_scroll
, round(safe_divide(page_view.page_view_count - session_start.session_start_count, session_start.session_start_count) * 100, 2) page_vs_session_percent
, round(safe_divide(page_view.page_view_count - scroll.scroll_count, scroll.scroll_count) * 100, 2) page_vs_scroll_percent
from page_view
inner join session_start on page_view.event_date = session_start.event_date
inner join scroll on scroll.event_date = page_view.event_date
order by event_date
```


Compare Similar DataSet in BigQuery


You can interact with JSON data with BigQuery, this blog show show to parse the JSON value and load fields with BigQuery.

### Sample JSON

This will be the JSON file using at the blog.

```JSON
{
   "id":"1",
   "first_name":"John",
   "last_name":"Doe",
   "dob":"1968-01-22",
   "addresses":[
      {
         "status":"current",
         "address":"123 First Avenue",
         "city":"Seattle",
         "state":"WA",
         "zip":"11111",
         "numberOfYears":"1"
      },
      {
         "status":"previous",
         "address":"456 Main Street",
         "city":"Portland",
         "state":"OR",
         "zip":"22222",
         "numberOfYears":"5"
      }
   ]
}
```

### Parse the single fields

At very first, assume has the variable contains the JSON string.

```SQL
declare jsonstring default '{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}';
```

To load just single fields from the JSON, we can do something as below, with **_$_** sign.

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
```

```
id | first_name | last_name
1 | John | Doe
```

### Include the JSON Array into the SQL

For the above SQL, if we try this with array, you can see the result has the whole JSON file.

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
json_extract_array(jsonstring, '$.addresses') as addresses
```

![result with json array](/images/big-query-access-json-array/img1.png)

### Display Scalar value from JSON

above script is able to show the JSON, but what if we only need the **_city_**?

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
array_agg(y)
from
  unnest(
  array(
      select
        json_extract_scalar(x, '$.city')
      from unnest(json_extract_array(jsonstring, "$.addresses")) x
    )
  ) y
```

![result with json and city field](/images/big-query-access-json-array/img2.png)

### Keep the filter fields at each row

move of the case, above result meet the result, but what if we want to show id, first_name and last_name at each row?

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
y
from
  unnest(
    array(
    select
    json_extract_scalar(x, '$.city')
    from unnest(json_extract_array(jsonstring, "$.addresses")) x
    )
  ) y
```

![result with json and city field at each row](/images/big-query-access-json-array/img3.png)

#### Conclusion

Above are list of example how to use interact with JSON and Big Query.


How to use BigQuery to Extract JSON Array


This is not the real scenario I was facing, but let's see you want to get all `event_timestamp` from a event called `page_view` for a give day in one column, and the rest of the column in the same row you just keep any `page_view` the fields.

Below is how you can achieve that at GA4 table.

``` SQL
with test as (
  select * 
  from (
    SELECT 
    *
    , row_number() over (partition by event_name) as rn
    FROM `GA4.events_intraday_20230611`
    where event_name = 'page_view'
  )
  where rn = 1
)
, test_2 as (
  SELECT 
  string_agg(safe_cast(event_timestamp as string), '_')
  , event_name
  FROM `GA4.events_intraday_20230611`
  group by event_name
  having event_name = 'page_view'
)
, test_3 as (
  select test_2.*, test.*
  from test_2
  left join test on test_2.event_name = test.event_name
)

```


How to Use Big Query to Get Single Columns Values from All Rows


### BQ SQL - create procedure without parameter

Create Procedure has no input or output parameter

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_without_parameter`()

begin
select 'test';
end;
```

Run the no input, output procedure.

```SQL
CALL `Test.sp_without_parameter`();
```

### BQ SQL - create procedure with input and output parameter

Below script will do a simple calculation with **_x_** plus **_y_** and return the **_total_**.

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_with_input_output`
(
 x int64,
 y int64,
 OUT total int64
)

begin
set total = x + y;
end;
```

This's how we can call the procedure to get the result.

```SQL
declare total int64;
CALL `Test.sp_with_input_output`(10, 20, total);
select total;
```

### Next, how to make Procedure return Array Type data?

In order for BQ Sql to return array like type, we can use **_Array_** with **_Struct_**.

First, let's create a procedure return the array custom type, in below example we want to
return list of items contains id, country, device and browser fields.

As you can see the return type is **_OUT items ARRAY<STRUCT<id int64, country STRING, device STRING, browser STRING>>_**

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_with_output_record`(OUT items ARRAY<STRUCT<id int64, country STRING, device STRING, browser STRING>>)
begin
create TEMP TABLE my_temp_table
(
  id int64,
  country string,
  device string,
  browser string
);

insert into my_temp_table
select 1 as id, 'US' as country, 'mobile' as device, 'safari' as browser union all
select 1 as id, 'CA' as country, 'desktop' as device, 'chrome' as browser
;

set items =
array(select struct(id, country, device, browser) from my_temp_table);

drop table my_temp_table;
end;

```

Next, we'll calling the stored procedure to get the list of country, device and browser.

```SQL
DECLARE items ARRAY<struct<id int64, country STRING, device STRING, browser STRING>> DEFAULT NULL;
CALL `Test.sp_with_output_record`(items);
```

Last, we want to join the **_items_** with another table to get the result.

```SQL
select * from unnest(items) as i
inner join `Test.TestLookup` as t
on i.id = t.id
```

The **_TestLookup_** table is a simple lookup table contains below values.

```
id | name
1 | my name
```

Above is how you can call BQ Stored Procedure to get custom Array type, this is useful so you can
move long procedure into small ones for its own responsibility.


How to Use Big Query SQL Procedure


## Create Service Account

Create GCP service account and add corresponding role, create new key and save the file (JSON or P12) locally.

## Load Service Account

Next, loading the file at the Node.js file

```Javascript
const options = {
    keyFileName: 'location_of_the_file.json',
    projectId: 'your_gcp_project_id'
};

const bigquery = new BigQuery(options);

const dataset = bigquery.dataset('my_dataset');

const table = dataset.table('my_table');
```

## Run Node.js locally

Add below code to the `package.json`'s script section, when run this at the command `npm run test`, I have test at MacOs with node version 18, anything below might not works.

```CMD
 "test": "npx functions-framework --target=main [--signature-type=http]",
```

## Test The Node.js with Big Query Access

Run the below code should be start http server locally, no you can verify, debug the code and make sure everything works before run the deploy.

```
npm run test
```

## How to Debug via Visual Studio Code

Visual Studio Code is a great tools as software development, and has been improve a lot since beginning, once of the feature for debug Node.js at Visual Studio Code is, you can switch the `JavaScript Debug Terminal` and run the `npm run test`, this will use the build-in debugger attached to the process.

So pretty simple for debug Node.js with Visual Studio.


This blog show how to test GCP Cloud Function locally with Node.js.

How to GCP Function locally with Node.js


### Create a Stored Procedure with Input Parameter

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_do_something`(the_id STRING)
BEGIN

select * from `Test.MyTestTable` where id = the_id;

END;
```

### Execute the Procedure

```SQL
DECLARE the_id STRING DEFAULT '2023-06-24 06:26:03.927610+00';
CALL `Test.sp_do_something`(the_id);
```

### Create Procedure with Multi-statement

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_do_something`(the_id STRING)
BEGIN

create TEMP TABLE my_temp_table
(
  country string,
    device string,
    browser string
);

INSERT INTO
my_temp_table (country,
    device,
    browser)
SELECT
  "US" AS country,
  "mobile" AS device,
  "safari" AS browser;

INSERT INTO
  `Test.MyTestTable` (id,items)
SELECT
  the_id,
  ARRAY[ STRUCT('country' AS KEY,
    my_temp_table.country AS value),
  STRUCT('browser' AS KEY,
    my_temp_table.browser AS value),
  STRUCT('device' AS KEY,
    my_temp_table.device AS value) ]
FROM
  my_temp_table;

select * from `Test.MyTestTable` where id = the_id;

END;
```

### Run the Procedure has multi-statement

Now, notice the id has new record as **_test_100_**.

```SQL
DECLARE the_id STRING DEFAULT 'test_100';
CALL `Test.sp_do_something`(the_id);
```

what if you run below SQL Script, it actually complain that table already exist.

```SQL
CALL `Test.sp_do_something`('2000');
CALL `Test.sp_do_something`('400');
```

One of the way to fix is drop the temp table at the end of the Procedure, this should fix the previous issue.

```
drop TABLE my_temp_table;
```


How to Create Stored Procedure with Big Query SQL


### get total page view count for the month

Return 202305's each date's page count

```SQL
with data as (
SELECT event_date, event_name, count(*) as total
FROM `ga4_table.events_intraday_202305*`
group by 1, 2
having event_name = 'page_view'
order by 1
)
select event_date, total from data;
```

Total event count with event_name

```SQL
with data as (
SELECT event_name, count(*) as total
FROM `ga4_table.events_intraday_202306*`
group by 1
order by 1
)

select * from data;
```


GA4 Big Query Sample Query


Assume you have these type of data sets, [1,2,3,4,5], [1,2,3,4,5,6,7], [1,2,3,4,5,6,7,8] and all with key as **_xxx_**, you want to find each array's max value from SQL.

### Create Sample Data

First let's create the sample data set, for key **_xxx_**, we want to get the 5,8,9.

```SQL
with base_data as
(
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '9' as id, 'xxx' as key union all
select '1' as id, 'yyy' as key union all
select '2' as id, 'yyy' as key union all
select '3' as id, 'yyy' as key union all
select '4' as id, 'yyy' as key union all
select '5' as id, 'yyy' as key union all
select '6' as id, 'yyy' as key union all
select '1' as id, 'zzz' as key
)
```

### Find list of sequence number with join with **_string_agg_**

This will return sequence number as string.

```SQL
, data_agg_find_sequence_no as (
select
(split(
string_agg(id, '_')
, '1_'
)) as pre_id
, key
from base_data
group by key
```

### Find the last number as max

Here, substr the last 2 character as max number of that sequence.

```SQL
, data_get_max_no_from_sequence as (
select
replace(
substr(x, -2)
, '_'
, '') as id
, key
from data_agg_find_sequence_no, unnest(pre_id) as x
```

### Final Script

This's the full script, should be able to see aggregated key with max number from the sequence number.

```SQL
with base_data as
(
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '9' as id, 'xxx' as key union all
select '1' as id, 'yyy' as key union all
select '2' as id, 'yyy' as key union all
select '3' as id, 'yyy' as key union all
select '4' as id, 'yyy' as key union all
select '5' as id, 'yyy' as key union all
select '6' as id, 'yyy' as key union all
select '1' as id, 'zzz' as key
)
, data_agg_find_sequence_no as (
select
(split(
string_agg(id, '_')
, '1_'
)) as pre_id
, key
from base_data
group by key
)
, data_get_max_no_from_sequence as (
select
replace(
substr(x, -2)
, '_'
, '') as id
, key
from data_agg_find_sequence_no, unnest(pre_id) as x
)
select * from data_get_max_no_from_sequence
where id != '';
```


This blog show how to use Big Query to find max number from a sequence number for a data set.

How to Use Big Query to Find Max Sequence Number


Create a Table so we can insert the **_Record_** Repeat type, or query the data.
The blow sample test table, we created a field name as **_items_**, which is record type with key is key and value both are string.

```SQL
CREATE TABLE `Test.MyTestTable`
(
  id STRING,
  items ARRAY<STRUCT<key STRING, value STRING>>
);

```

### Insert a data into Items Field

This SQL script will insert key/value pair data into the items **_Record_** repeated field.

```SQL
insert into `Test.MyTestTable` (id, items)
select safe_cast(current_timestamp() as string),
array[
  struct('country' as key, "UK" as value),
  struct('browser' as key, "chrome" as value),
  struct('device' as key, "desktop" as value)
]
```

### Query all keys from the dataset

This will return all keys from the table

```SQL
SELECT x.key FROM `Test.MyTestTable`, unnest(items) as x;
```

### Select Insert into the Record Type

Below example is join the **\*my_temp_table** table and as select insert into the **_record_** field.

```SQL
  create TEMP TABLE my_temp_table ( country string,
    device string,
    browser string );
INSERT INTO
  my_temp_table (country,
    device,
    browser)
SELECT
  "US" AS country,
  "mobile" AS device,
  "safari" AS browser;
INSERT INTO
  `Test.MyTestTable` (id,
    items)
SELECT
  SAFE_CAST(CURRENT_TIMESTAMP() AS string),
  ARRAY[ STRUCT('country' AS KEY,
    my_temp_table.country AS value),
  STRUCT('browser' AS KEY,
    my_temp_table.browser AS value),
  STRUCT('device' AS KEY,
    my_temp_table.device AS value) ]
FROM
  my_temp_table
```

### Find all the unique Key from the table

This query is to find unique key from the **_record_** type.

```SQL
SELECT distinct(x.key), count(*) FROM `Test.MyTestTable`, unnest(items) as x
group by 1
order by 2 desc
;
```

### Convert the **_record_** to JSON by using **_to_json_string()_**

```SQL
select id, to_json_string(items) from `Test.MyTestTable`
```


This blog demonstrate how to use GCP Big Query record type.

How to Use GCP Big Query Record Repeat Type


<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