
#### Table of Contents

1. The problem
2. Conclusion

### 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


#### Table of Contents

1. Sample JSON
2. Parse the single fields
3. Include the JSON Array into the SQL
4. Display Scalar value from JSON
5. Keep the filter fields at each row
6. Conclusion

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


#### Table of Contents

1. BQ SQL - create procedure without parameter
2. BQ SQL - create procedure with input and output parameter
3. Next, how to make Procedure return Array Type data?

### 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


#### Table of Contents

1. Create a Stored Procedure with Input Parameter
2. Execute the Procedure
3. Create Procedure with Multi-statement
4. Run the Procedure has multi-statement

### 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


#### Table of Contents

1. Create Sample Data
2. Find list of sequence number with join with **_string_agg_**
3. Find the last number as max
4. Final Script

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


#### Table of Contents

1. Insert a data into Items Field
2. Query all keys from the dataset
3. Select Insert into the Record Type
4. Find all the unique Key from the table
5. Convert the **_record_** to JSON by using **_to_json_string()_**

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


### 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


## Table of Contents

- [Introduction](#introduction)
- [How to Check If Full-Text Search Is Enabled on SQL Server](#how-to-check-if-full-text-search-is-enabled-on-sql-server)
- [How to Get All Full-Text Indexed Columns](#how-to-get-all-full-text-indexed-columns)
- [How to Set Up an Index for Specific Tables](#how-to-set-up-an-index-for-specific-tables)
- [How to Query Data by Full Text](#how-to-query-data-by-full-text)
- [Walk Through with Example](#walk-through-with-example)
- [Reference](#reference)
- [Conclusion](#conclusion)

## Introduction

SQL Server full-text search requires installation on SQL Server. After installing full-text search, you can set up the index as follows.

## How to Check If Full-Text Search Is Enabled on SQL Server

If the following returns `1`, it means it is enabled; `0` means it is not.

```
SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')
```

## How to Get All Full-Text Indexed Columns

```
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
```

## How to Set Up an Index for Specific Tables

```
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
```

```
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
```

## How to Query Data by Full Text

The following query will search `MyTable`'s `MyColumn` for the keyword `Surface Pro`.

```
select * from MyTable where FreeText(MyColumn, 'Surface Pro')
```

If you search a column that is not full-text indexed or SQL Server does not have the full-text feature installed, you'll see the following error:

```
select * from MyTable where FreeText(MyColumnNotFullTextIndexed, 'Surface Pro')
```

```
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.
```

## Walk Through with Example

### Create a Table for Testing Full-Text Index

```
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
```

### Add Full-Text Index to the Table

Assume we'd like to use the `Text` and `Text2` columns for full-text indexing. We can add the index as follows:

```
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
```

### Run Full-Text Index Query

As you can see, the full-text search for "BUY" in the Text2 column will return all the stemming words, such as buy, bought, and buys.

```
select * from TestFullText where Text2 like '%buy%'
  select * from TestFullText where FreeText(Text2, 'buy')
  select * from TestFullText where Contains(Text2, 'buy')
```

![](https://d1p0mp3f1fx0hz.cloudfront.net/_gs75bse1y.png)

## Reference

- MSDN - Full-Text Search
- MSDN - CREATE FULLTEXT INDEX (Transact-SQL)
- MSDN - Create and Manage Full-Text Catalogs
- Create and Manage Full-Text Indexes
- MSDN - Get Started with Full-Text Search
- MSDN - Query with Full-Text Search
- Blog - SQL vs No.SQL
- MSDN - Local ID
- Blog - MongoDB Text Search

## Conclusion

SQL Server's full-text search feature enables efficient searching of text data using stemming, linguistic analysis, and more. By creating a full-text catalog and index on your desired columns, you can use `FreeText` and `Contains` predicates for powerful text queries that go beyond simple `LIKE` pattern matching.


Example of how to set up full-text search on SQL Server.

SQL Server Full Text Search Example


## Table of Contents

- [Introduction](#introduction)
- [Search Table Name by Column Name](#search-table-name-by-column-name)
- [Conclusion](#conclusion)

## Introduction

Sometimes you know a column name but don't know which table it belongs to. This SQL example shows how to search for a table name by column name.

## Search Table Name by 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.name LIKE '%MyColumn_DontKnowWhichTableIs%'
ORDER BY    TableName
            ,ColumnName;
```

## Conclusion

By querying `sys.columns` and `sys.tables`, you can quickly find which tables contain a specific column name. This is especially useful when working with large databases where you don't know the full schema.


A T-SQL code snippet to find table names when you only know the column name.

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


## Table of Contents

- [Introduction](#introduction)
- [T-SQL Paging Example](#t-sql-paging-example)
- [Conclusion](#conclusion)

## Introduction

This is an older approach to pagination in T-SQL. There are better ways to do it now (such as `OFFSET`/`FETCH`), but this is a sample example of how to do pagination at the SQL level using `ROW_NUMBER`.

## T-SQL Paging Example

```
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 > @startRow
    AND RowNum <= @endRow
ORDER BY RowNum
```

## Conclusion

This T-SQL paging approach uses `ROW_NUMBER()` to assign row numbers and then filters by a calculated range based on the page number and page size. For SQL Server 2012 and later, consider using `OFFSET`/`FETCH NEXT` for a cleaner pagination syntax.


A sample code snippet for T-SQL pagination using ROW_NUMBER.

T-SQL Paging Sample


## Table of Contents

- [Introduction](#introduction)
- [T-SQL Query Example](#t-sql-query-example)
- [Conclusion](#conclusion)

## Introduction

This post provides a sample T-SQL code snippet that demonstrates using CASE expressions, string concatenation for address formatting, and subqueries.

## T-SQL Query Example

```
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 (nolock)

inner join [TestDB].[dbo].[Address] as adr (nolock)

on pladd.AddressID = adr.AddressID
where pladd.Id = pl.Id)
from [TestDB].[dbo].[TestDB] as av (nolock)

inner join [TestDB].[dbo].[TestDB1] as pla (nolock)

on av.Id = pla.Id
where
and av.DateLastUpdated between '2014-05-01' AND '2014-05-31'
```

## Conclusion

This T-SQL snippet demonstrates common patterns including CASE expressions for conditional values, scalar subqueries for address concatenation, and date range filtering. Adapt the table and column names to fit your own database schema.


A sample T-SQL code snippet demonstrating CASE expressions, string concatenation, and subqueries.

Code Snippet about T-SQL Query


## Table of Contents

- [Introduction](#introduction)
- [Find Deadlock Information from SQL Server](#find-deadlock-information-from-sql-server)
- [Fix Deadlock](#fix-deadlock)
- [Reference](#reference)
- [Conclusion](#conclusion)

## Introduction

There are many ways to find SQL Server deadlocks or resolve deadlock issues. In this blog, I will show how to use SQL Server Profiler to detect deadlocks and one example of how to fix them.

## Find Deadlock Information from SQL Server

- Open SQL Server Profiler

- Create a new Trace file

- At the General tab, check "Save to file" and save the file to a shared folder. This is because after stopping the trace, you can analyze the trace log information.

- At the Events Selection tab, expand Locks and check "Deadlock graph". Trace Properties will show an "Events Extraction Settings" tab; then click "Save Deadlock XML events separately" and save the file to a shared folder.

- Click "Run"

- If you know where the application causes the deadlock, then go to the application and reproduce the error.

- Stop the trace.

- Go to the trace file you saved to the shared folder and search for "dead". You should find the deadlock XML.

## Fix Deadlock

My issue was fixed by running the following SQL command:

```
EXEC sp_updatestats
```

## Reference

- http://blogs.msdn.com/b/saponsqlserver/archive/2015/01/18/deadlock-analysis-examples.aspx

- http://blogs.msdn.com/b/saponsqlserver/archive/2013/01/31/deadlock-analysis-basic.aspx

- 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/

- http://sqlmag.com/database-performance-tuning/gathering-deadlock-information-deadlock-graph

## Conclusion

SQL Server Profiler is a useful tool for detecting deadlocks. By saving trace logs and deadlock XML events, you can analyze the root cause and apply fixes such as updating statistics with `sp_updatestats`.


This blog shows how to use SQL Server Profiler to find and fix deadlock issues in SQL Server.