T-SQL: PARSING VALUES BASED ON NTH OCCURENCE OF A CHARACTER IN A STRING

I recently had to parse values stored in a column that had following pattern: -'Word1-Word2-Word3-Word4-' etc.
SQL Server already has the built in function CHARINDEX() which is quite helpful, but the issue is that it only returns the position of the 1st occurence.
Technically I could nest one CHARINDEX() in another one, but that would create a big piece of code, is a little hard to read plus not a dyamic solution. So, I decided to create a function that returns the values based on input parameters:

CREATE FUNCTION dbo.ParseStringValue(
@SplitCharacter nvarchar(5),
@TargetString nvarchar(50),
@NthOccurence int)

RETURNS nvarchar(50)

AS
BEGIN

DECLARE @Pos1 int = 0,
@Pos2 int = 0,
@Count int = 0,
@Result nvarchar(25)

WHILE @Count <= @NthOccurence
BEGIN

SET @pos1 = @pos2
SET @pos2 = CHARINDEX(@SplitCharacter, @TargetString, @pos1 + 1)
SET @Count = @Count +1

END

SET @Result = SUBSTRING(@TargetString, @pos1+1, (@pos2-1)- @pos1)

RETURN (@Result)
END

I will use this string for our sample '-Aziz-Sharif-blogspot-com-' and will search for the value behind the 4th occurence of the split character '-'

The function will be called in the SELECT part and I need to pass 3 parameters 
@SplitCharacter: The seperator value '-'
@TargetString: The string value - here a fixed string for demonstration, could be a column as well
@NthOccurence: The nth occurence of a character in a string I wish to be returned

SELECT dbo.ParseStringValue('-', '-Aziz-Sharif-blogspot-com-', 4)

The while loop will go through the same piece of code and stop until it reached the nth occurence:

WHILE @Count <= @NthOccurence
BEGIN

SET @pos1 = @pos2
SET @pos2 = CHARINDEX(@SplitCharacter, @TargetString, @pos1 + 1)
SET @Count = @Count +1

END

I will run the query in debug mode and break down the values of each run.

Within the loop I define the positions of the split character. Since I need to find the values between 2 characters, I have declared 2 integer variables: @pos1 and @pos2.


SET @pos1 = @pos2
SET @pos2 = CHARINDEX(@SplitCharacter, @TargetString, @pos1 + 1)

@pos2 is passed to @pos1. So @pos1 stores the most recent value and @pos2 returns the next position after @pos1 of the split character I'm searching for:
SET @pos2 = CHARINDEX(@SplitCharacter, @TargetString, @pos1 + 1)
The @pos1 + 1 part will define ther start position .

The final relevant values stored are:
@pos1 = 22 -> '-Aziz-Sharif-blogspot-com-'
@pos1 = 26 -> '-Aziz-Sharif-blogspot-com-'

Since I need to extract the value between the 2 split characters, I need to add 1 position to the start position and deduct 1 from the end position:
SET @Result = SUBSTRING(@TargetString, @pos1+1, (@pos2-1)- @pos1)

The function returns the value after the 4th occurence in the defined string

T-SQL: JOIN OPERATORS - NESTED LOOP JOIN, MERGE JOIN AND HASH JOIN

In this tutorial, I will describe the 3 Join Operators SQL Server uses and the reason why it comes to the individual selections by the Optimizer.

I will focus on the Inner Join and will stick to using 2 tables. We basically have 3 scenarios:

1) None of the tables have an index on the joining column
2) One of the two tables has an index on the joining column
3) Both columns have an index on the joining column

In the samples below I will use 2 tables from the AdventureWorks Database and modify indexes to demonstrate why the Optimizer decides to vary on the join operator. Following 2 tables will be used: Sales.SalesOrderHeader
Sales.SalesTerritory

Following query will be used throughout all samples:
SELECT  so.SalesOrderID
      ,st.Name
      ,so.OrderDate
      ,so.DueDate
      ,so.ShipDate
      ,so.SalesOrderNumber
      ,so.PurchaseOrderNumber
      ,so.AccountNumber
      ,so.CurrencyRateID
      ,so.TotalDue
  FROM Sales.SalesOrderHeader so
  INNER JOIN Sales.SalesTerritory st    ON so.TerritoryID = st.TerritoryID

NESTED LOOP JOIN

Tablename	Row Count	Indexed Column
Sales.SalesOrderHeader	31465	Clustered Index on joining column
Sales.SalesTerritory	10	No index

 








Nested Loop Join will consist out of inner (lower object in Execution Plan) and outer (upper object in Execution Plan) inputs.
The outer input gets scanned row by row and an index seek is processed on the inner table (the larger indexed table).
This is exactly the advantage of Nested Loop Joins compared to Hash and Merge Joins. The outer, smaller input, is placed in memory and is compared with an indexed outer input.
Let's have a closer look at the Execution plan by placing the cursor above the object in question. The Clustered Index Scan shows us that there was 1 Execution returning 10 rows (all rows in that table).

The Clustered Index Seek provides information that 10 Executions took place returning 31465 rows. Therefor keep in mind that Nested Loop Joins will always work well with a small outer input and a indexed inner input.

MERGE JOIN

Tablename	Row Count	Indexed Column
Sales.SalesOrderHeader	31465	Clustered Index on joining column
Sales.SalesTerritory	10	Clustered Index on joining column

The requirements for a Merge Join is that both tables are sorted on the joining column. Assuming you have an index on both columns, then the sort is already covered here.
If you force a Merge Join by passing a hint and an index is missing on one or both of the columns, the Sort Operator will be used here, which is quite expensive due to high memory and I/O resources.
Merge Join can be very fast when columns are ordered by the index. In this case matching rows are created while the sorted columns are compared for equality.
The big advantage with Merge Joins is that both outputs will be only executed once.

Although I have an index on both tables, the Optimizer will probably still go for a Nested Loop Join here, since the outer input is quite small. Merge Joins are perfect for indexed and larger tables.

HASH JOIN

Tablename	Row Count	Indexed Column
Sales.SalesOrderHeader	31465	No index
Sales.SalesTerritory	10	No Index

Hash Joins are usually selected when there is no other option, meaning the data is not sorted and nonindexed. A Hash Join consists of 2 inputs.
1) Build Input: the upper object in the execution plan and usually the smaller table since it is saved on the system and to keep used memory low
2) Probe Input: the lower object in the execution plan
Hash joins are used by the Optimizer to process large and unsorted as well nonindexed values.


In most cases the Optimizer will make the right choice, but in cases where statistics are not up to date, it may be necessary to enforce the right option.
You can do this, by either adding a OPTION (x JOIN) at the end of the query

  OPTION (LOOP JOIN)
  OPTION (MERGE JOIN)
  OPTION (HASH JOIN)

or add the hint in the inner join part of the query

  INNER LOOP JOIN
  INNER MERGE JOIN
  INNER HASH JOIN

T-SQL: SQL Server Transaction Isolation Levels

There are 4 attributes ensured to a transaction (ACID):

A: Atomicity - In a transaction either all included elements will get committed or none of them
C: Consistency - Data integrity is ensured, meaning if any failure occurs during the transaction, the previous state at begin of the transaction will be kept.
I: Isolation - A transaction in process may not be affected by other transactions.
D: Durability - Committed data is saved by the system.

There are 4 different isolation levels. A sample with SQL code for each type will be provided.

I will use the following Artist table as a basis for my samples:

SELECT [ArtistID]
               ,[Artist]
FROM [Tutorials].[stg].[Artist]SELECT [ArtistID]

ArtistID    Artist
----------- ------------
1           00Agents
2           06 Style
3           10°
4           1000 Ohm
.......

1) READ UNCOMMITTED
This isolation level allows to read changed data from other not closed transactions, which may later be rolled back.

Transaction 1:
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED

SELECT [ArtistID]
              ,[Artist]
FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

The query delivers the following result:
ArtistID    Artist
----------- -----------
1           00Agents

(1 row(s) affected)

Now I will run the following transaction, but won't commit the transaction:

Transaction 2:
BEGIN TRANSACTION

INSERT INTO [Tutorials].[stg].[Artist]
VALUES
('00Agents')

If I run the first query again, I will now also see the inserted value from above (not committed transaction)

Transaction 1:
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED

SELECT [ArtistID]
              ,[Artist]
FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

ArtistID    Artist
----------- -----------
1           00Agents
38         00Agents

(2 row(s) affected)

Instead of committing I will now rollback the transaction. Now we see the original result again.

----------- -----------
1           00Agents

(1 row(s) affected)

This scenario where uncomitted transactions can be captured, is often referred to as a "Dirty Read".


2) READ COMMITTED
This is the standard default value in SQL Server.
Here we are able to only see committed transactions:

Assuming I'm inserting values in a table, and I open a second transaction where I want to read the table, the following will happen.

Here we insert the values (but do not commit right away for demonstration reasons):

Transaction 1:
BEGIN TRANSACTION

INSERT INTO [Tutorials].[stg].[Artist]
VALUES
('00Agents')


Now we want to select values from that same table:


Transaction 2:
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

SELECT [ArtistID]
              ,[Artist]
FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

Transaction 2 will deliver no result until Transaction 1 is finished.

Once I commit the transaction with the insert values, the select query (Transaction 2) shows results:

Transaction 2:
--BEGIN TRANSACTION

--INSERT INTO [Tutorials].[stg].[Artist]
--VALUES
--('00Agents')

COMMIT TRANSACTION

ArtistID    Artist
----------- ----------
1            00Agents
39          00Agents

(2 row(s) affected)


3) REPEATABLE READ
This isolation level gurantees that data read in a transaction will deliver the same result set later in that transaction. In the sample below I will have a SELECT query, add a waitfor delay and will start a second session in that break that will update values in the underlying table and then after the break run the same SELECT statement in Session 1 to see if the changes happend.

Transaction 1
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ

BEGIN TRANSACTION

SELECT [ArtistID]
              ,[Artist]

FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

WAITFOR DELAY '00:00:05'

SELECT [ArtistID]
              ,[Artist]

FROM [Tutorials].[stg].[Artist]
WHERE Artist = '00Agents'

COMMIT TRANSACTION

 Transaction 2:

BEGIN TRANSACTION

UPDATE [Tutorials].[stg].[Artist]
SET Artist = '01Agents'
WHERE ArtistID = 1

COMMIT TRANSACTION


Although we have an update between the 2 queries, the result is the same.


ArtistID    Artist
----------- ----------
1            00Agents
39          00Agents

(2 row(s) affected)

ArtistID    Artist
----------- ----------

1            00Agents
39          00Agents

(2 row(s) affected)The reason why the change happens after Transaction 1 is finished, is due to a share lock that is kept until the transaction with Isolation Level REPEATABLE READ is finished.

4) SERIALIZABLE
This  Isolation Level is quite comparable with REPEATABLE READ. The main difference is that Isolation Level SERIALIZABLE wouldn't allow inserted rows. Updates and Deletes are locked with REPEATABLE READS, but inserts are technically possible - but not with Isolation Level SERIALIZABLE. This scenario is also known as a "Phantom Read".

T-SQL: Dynamically Create Date Ranges With Recursive Queries

In this post I will provide a method how you can dynamically create a range of values.
I recently had to get a range of month values for filtering on a data set, but I had no date table on the database and needed to find a quick way how to solve this issue without going the route of creating a table and loading values.
So I ended up writing a recursive query using a Common Table Expression.

DECLARE @MinDate date = '2015-01-01',
@EndDate date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
     
;WITH DateSerial AS
(
    SELECT @MinDate MonthDate  -- 1 Invocation of the routine

    UNION ALL
 
    SELECT DATEADD(MONTH, 1, MonthDate)   --Recursive invocation of the routine
    FROM DateSerial
    WHERE MonthDate < @EndDate)

SELECT * FROM DateSerial

OPTION (MAXRECURSION 0)

In the first part I declared a start and end date to define the range.

A recursive Common Table Expression can be broken down into 3 parts:

1) Invocation of the routine
2) Recursive invocation of the routine
3) Termination Check

In the above sample a Common Table Expression is used where a first Select picks up the start date
and a UNION ALL combines the other results that are created until the end date is reached.

Depending on how big the range is, you are very likely to receive this error message at some point:

"The maximum recursion .... has been exhausted before statement completion"

To avoid this error message, the command OPTION (maxrecursion n) lets you define how often the Common Table Expression can recurse until it reaches an error state. In my sample I set the value to 0, meaning infinite recursion.


The querey delivered follwing result:

 

SQL SERVER ADMINISTRATION: REORGANIZE OR REBUILD CLUSTERED AND NON-CLUSTERED INDEXES

In this blog entry, I would like to describe how to reorganize or rebuild indexes.

First of all, SQL server stores row or index data on 8KB sized memory Pages. For each table (no matter how big), a own page is created. If the table exceeds a certain size due to manipulation of data (e.g. new row inserts), the page will be extended („Page Split“) to a second page etc.

 

The new page will also cause the creation of a new page in the upper level with the index tree, which may also lead to a page split.

Page Spilts are time consuming, cause fragmentation and index pages aren’t phyically ordered as supposed. All this results to a decrease of performance.

Let's have a brief look on the architecture of indexes in SQL Server.

In a table without a clustered index, the data will be stored within the page unsorted („Heap“).

Indexes have a root page, which is the starting point. The tree is split between non-leaf (root and intermediate leafs) and leaf-levels.

In a clustered index, the leaf level is the actual data page. This means the data is stored here in ordered form.

The most significant difference in the index architecture between clustered and non-clustered, is that the leaf level in non-clustered indexes contain key values and not the actual data. 

In case of a heap, there is a row locator in the leaf node pointing to the correct data page.

A non-clustered index in a table that has a clustered index, will refer to the clustered index for pointing the row location.

Disadvantage here is that non-clustered indexes will have to be rebuild if the clustered index is rebuild or dropped. An advantage here is that a Reorganize of a clustered index, will mean that the non-clustered index does not have to be reorganized.

To solve the above listed issues concerning performance, indexes need to be REORGANIZEd or REBUILD.

The level of fragmentation can be analyzed with the help of system function sys.dm_db_index_physical_stats

Here is a more precise query:
SELECT sch.name  + ' ' + tab.name             AS 'Table',
idx.name                                                       AS 'Index',
stat.avg_fragmentation_in_percent,
stat.page_count,
idx.type_desc                                                AS IndexType
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS stat
INNER JOIN sys.tables  AS tab                   ON tab.object_id = stat.object_id
INNER JOIN sys.schemas AS sch               ON tab.schema_id = sch.schema_id
INNER JOIN sys.indexes AS idx                 ON idx.object_id = stat.object_id
AND stat.index_id = idx.index_id
ORDER BY stat.avg_fragmentation_in_percent DESC
If the average fragmention is between 5 and 30 %, a REORGANIZE is fine, if it's above 30% REBUILD is advised.

Following T-SQL syntax is required for REORGANIZE:

ALTER INDEX indexname

ON tablename REORGANIZE

In case of a rebuild, there is an option for a fill factor. This means the page will be filled with the percentage value defined. The rest is left open to avoid a fast fragmentation.

ALTER INDEX indexname

REBUILD WITH (FILLFACTOR = 80)

The fillfactor by default will only relate to the leaf level of the tree. If you want to also cover the other pages PAD_INDEX = ON will do the job.

ALTER INDEX indexname 
REBUILD WITH (FILLFACTOR = 80, PAD_INDEX = ON)