CAST and CONVERT (Transact-SQL) - Truncating and Rounding Results

Here's some "puzzle" for you dudes .
Before you opening SQL Management Studio and pasting
from clipboard , answer -
what is the result for lines below?


DECLARE @D INT
SET @D = 12345

SELECT CAST(@D AS VARCHAR(2))

Using CTE

This post is a part of Mohd Nizamuddin very interesting article
Sending multiple rows to the Database from an Application
Albert

Table valued function using Number List

First I will explain the pieces of code, which form the building blocks of the final table valued function.
We need to create a number list using the CTE as below
      ;WITH
            L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
            L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
            L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
            L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
            L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
            Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
      SELECT * FROM Numbers

This CTE is creating the list of numbers from 1 to POWER(POWER(POWER(POWER(2, 2), 2), 2), 2), i.e. until 65536.
Now Consider the below code snippet, where @list and @delim variables have been assigned.

 DECLARE
 @list NVARCHAR(MAX), @delim NCHAR(1)
 SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','
 
                ;WITH
              L0 AS(SELECT 1 AS
c UNION ALL SELECT 1),
              L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
              L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
              L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
              L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
              Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
         SELECT
         @list List,
         SUBSTRING(@list, Number, CHARINDEX(@delim,
@list + @delim, Number) - Number) AS   Value,
         Number AS StartingFrom,
         CHARINDEX(@delim, @list + @delim, Number) AS DelimeterPosition
         FROM Numbers
         WHERE Number <= CONVERT(INT, LEN(@list))
         AND SUBSTRING(@delim + @list, Number,
1) = @delim

The SUBSTRING statement, cuts characters from @list starting from character position (1, 5, 11 and 16).
SUBSTRING(@list, Number, CHARINDEX(@delim, @list + @delim, Number) - Number)

The number of characters to be cut is decided by CHARINDEX which will
return 4, 10, 15, 20 in each row, where it finds the delimiter character.
CHARINDEX(@delim, @list + @delim, Number) - Number
The above SELECT only works until the number of characters present in the
@list variable due to the condition
Number <= CONVERT(INT, LEN(@list))

The duplicate values are filtered out from the output list by the "WHERE" condition created
using the SUBSTRING function which will only return a value when it finds the delimiter
SUBSTRING(@delim + @list, Number, 1) = @delim

The output of the code snippet above would be:

List	Value	Starting From	Delimiter Position
aaa,bbbbb,cccc,dddd	aaa	1	4
aaa,bbbbb,cccc,dddd	bbbbb	5	10
aaa,bbbbb,cccc,dddd	cccc	11	15
aaa,bbbbb,cccc,dddd	dddd	16	20

Table valued function using Numbered List: Implementation
Now combining all the above explained pieces of SQL, we create our
table valued function which will parse the string and return a table having two columns viz. ID and Data.
      CREATE FUNCTION [dbo].[TableFormDelimetedString]
      (
            @param      NVARCHAR(MAX),
            @delimeter  NCHAR(1)
      )
      RETURNS @tmp TABLE
      (
            ID      INT      
        IDENTITY    
          (1,    
          1),
            Data        Varchar(MAX)
      )
      BEGIN
 
            ;WITH
                  L0   AS(SELECT 1 AS c   UNION   ALL   SELECT   1),
                  L1   AS(SELECT 1 AS c FROM L0 AS   A,   L0   AS   B),
                  L2   AS(SELECT 1 AS c FROM L1 AS   A,   L1   AS   B),
                  L3   AS(SELECT 1 AS c FROM L2 AS   A,   L2   AS   B),
                  L4   AS(SELECT 1 AS c FROM L3 AS   A,   L3   AS   B),
                  Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
            INSERT INTO
               @tmp (Data)
            SELECT
             LTRIM(RTRIM(CONVERT(NVARCHAR(4000),
             SUBSTRING(@param, Number,
             CHARINDEX(@delimeter, @param + @delimeter, Number) - Number)
          ))) AS Value
         FROM   Numbers
         WHERE    Number <=
           CONVERT(INT,     LEN(@param))
          AND  SUBSTRING(@delimeter+ @param, Number, 1) = @delimeter
     RETURN
    END
Table valued function using Numbered List: Usage
So if we now invoke the above function like
SELECT * FROM [TableFormDelimetedString]('Andy:Roger:Thomas:Rob:Victor',':')

We will obtain the following result set:

ID	Data
1	Andy
2	Roger
3	Thomas
4	Rob
5	Victor

Table valued function using recursive CTE

Here again I will first explain the pieces of code,which form
the building blocks of the final table valued function.
As we know, in a recursive CTE, we have one anchor part and one recursive part.
But if we create a CTE having only the anchor part it would look something like
     DECLARE
      @list NVARCHAR(MAX), @delim NCHAR(1) 
      SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim
= ','   
      ;WITH CTETable (start, stop) AS  
      (
       SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list +@delim, 1)
      )
       SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list,
start,
              CASE
              WHEN
stop > 0
              THEN
stop - start
              ELSE
0
              END
               )))
AS Data
         start AS StartingFrom, stop
AS   DelimiterPosition
       FROM CTETable
The output of the SQL above will be like

List	Value	Starting From	Delimiter Position
aaa,bbbbb,cccc,dddd	aaa	1	4

Now by adding a recursive member to the above CTE, which iterates over the stop variable, the SQL looks like
    DECLARE
    @list NVARCHAR(MAX),
    @delim NCHAR(1)
 
    SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ',' 
    ;WITH CTETable (start, stop)  
        AS 
        (
        SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list +@delim, 1)
        UNION ALL       -- added for recursive part of CTE
        SELECT start = stop + 1, stop = CHARINDEX(@delim, @list +  
        @delim, stop+ 1) FROM CTETable WHERE
stop > 0 -- added for recursive part of CTE
        )
        SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list,
start,
        CASE
        WHEN stop > 0
        THEN stop - start
        ELSE 0
        END
        ))) AS Data
        start AS StartingFrom, stop AS DelimiterPosition
        FROM CTETable
        WHERE stop > 0

And gives the following result set

List	Value	Starting From	Delimiter Position
aaa,bbbbb,cccc,dddd	aaa	1	4
aaa,bbbbb,cccc,dddd	bbbbb	5	10
aaa,bbbbb,cccc,dddd	cccc	11	15
aaa,bbbbb,cccc,dddd	dddd	16	20

Table valued function using recursive CTE: Implementation
Finally we create a table valued function from the above code blocks, which looks like
      CREATE FUNCTION [dbo].[TableFormDelimetedStringWithoutNumberList]
      (@list NVARCHAR(MAX),
      @delim  NCHAR(1)   = ','
      )
       RETURNS @tmp TABLE
       (
        ID  INT IDENTITY   (1, 1),
        Data Varchar(MAX)
       )
      BEGIN
       ;WITH CTETable (start, stop)
        AS
        (
         SELECT start = CONVERT(bigint, 1),
           stop = CHARINDEX(@delim, @list + @delim)
         UNION ALL   -- added for recursive part of CTE
         SELECT start = stop + 1,
         stop = CHARINDEX(@delim, @list + @delim, stop + 1) -- added for recursive part of CTE
         FROM CTETable
         WHERE  stop > 0
        )
       INSERT INTO @tmp (Data)
        SELECT LTRIM(RTRIM(SUBSTRING(@list,
             start,
            CASE
            WHEN stop > 0
            THEN
            stop - start
            ELSE
            0
            END))) AS Data
            FROM CTETable
        WHERE stop > 0
     RETURN
    END
Table valued function using recursive CTE: Usage
So if we now invoke the above function like
     SELECT * FROM[TableFormDelimetedStringWithoutNumberList]('Andy:Roger:Thomas:Rob:Victor',':')
We will obtain the following result set:

ID	Data
1	Andy
2	Roger
3	Thomas
4	Rob
5	Victor

Why I like these two implementations is because the looping has been handled
by the SQL server database engine itself, which would definitely be more efficient
than explicit SQL looping code written by a developer.

The Open Closed Principle

Introduction

The open closed principle of object oriented design states:
"Software entities like classes, modules and functions should be open for extension but closed for modifications."

The Open Close Principle encourages software developers to design and write code in a fashion that adding new functionality would involve minimal changes to existing code.

Most changes will be handled as new methods and new classes.

Designs following this principle would result in resilient code which does not break on addition of new functionality.

The Open Close Principle Violation Example

The code below shows a resource allocator. The resource allocator currently handles timeslot and spaceslot resource allocation:

public class ResourceAllocator
{
    public enum ResourceType
    {
       Time,
       Space
    }
    public int Allocate(ResourceType resourceType)
    {
        int resourceId = default(int);
        switch (resourceType)
        {
            case ResourceType.Time:
                resourceId = FindFreeTimeSlot();
                MakeTimeSlotBusy(resourceId);
                break;
            case ResourceType.Space:
                resourceId = FindFreeSpaceSlot();
                MakeSpaceSlotBusy(resourceId);
                break;
            default:
                throw new InvalidOperationException ("Attempted to allocate invalid resource");
                break;
        }
        return resourceId;
   }
}

It is clear from the code below that it does not follow the Open Closed Principle.
The code of the resource allocator will have to be modified for every new resource type that needs to be supported.

This has several disadvantages:

• The resource allocator code needs to be unit tested whenever a new resource type is added.
• Adding a new resource type introduces considerable risk in the design as almost all aspects of resource allocation have to be modified.
• Developer adding a new resource type has to understand the inner workings for the resource allocator.

Modified Code to Support Open Closed Principle

The following code presents a new design where the resource allocator is completely transparent to the actual resource types being supported.
This is accomplished by adding a new abstraction, resource pool.
The resource allocator directly interacts with the abstract class resource pool:

public enum ResourceType
{
   Time,
   Space
}
public class ResourceAllocator
{
   Dictionary resourcePools = new Dictionary();

 public void AddResourcePool(ResourceType resourceType, ResourcePool pool)
 {       
    if (!resourcePools.ContainsKey(resourceType))
    {
       resourcePools.Add(resourceType, pool);
    }
 }
 public int Allocate(ResourceType resourceType)
 {
    int resourceId = default(int);
    if (resourcePools.ContainsKey(resourceType))
    {
        resourceId = resourcePools[resourceType].FindFree();
        resourcePools[resourceType].MarkBusy(resourceId);
    }
    else
    {
         throw new InvalidOperationException("Attempted to allocate invalid resource");
    }
 }
 public int Free(ResourceType resourceType, int resourceId)
 {
    if (resourcePools.ContainsKey(resourceType))
    {
       resourcePools[resourceType].Free(resourceId);
    }
    else
    {
        throw new InvalidOperationException("Attempted to free invalid resource\n");
     }
  }
}

public abstract class ResourcePool
{
   public abstract int FindFree();
   public abstract void MarkBusy(int resourceId);
   public abstract int Free(int resourceId);
}

public class TimeSlotPool : ResourcePool
{
   public override int FindFree() 
   { /*finds free time slot */ }
   public override void MarkBusy(int resourceId) 
   { /*marks slot as busy */ }
   public override int Free(int resourceId) 
   { /*releases slot */}
}

public class SpaceSlotPool : ResourcePool
{
   public override int FindFree() 
   { /*finds free space slot */ }
   public override void MarkBusy(int resourceId) 
   { /*marks slot as busy */ }
   public override int Free(int resourceId) 
   { /*releases slot */}
}

This has several advantages:

• The resource allocator code need not be unit tested whenever a new resource type is added.
• Adding a new resource type is fairly low risk as adding a new resource type does not involve changes to the resource allocator.
• Developer adding a new resource type does not need understand the inner workings for the resource allocator.

The Unit Of Work Pattern

When you're pulling data in and out of a database, it's important to keep track of what you've changed; otherwise, that data won't be written back into the database.

One of the most common design patterns that helps to form the unit, which is responsible for data persistance is the Unit of Work.

A Unit of Work keeps track of everything you do during a business transaction that can affect the database. When you're done, it figures out everything that needs to be done to alter the database as a result of your work.

The key thing about Unit of Work is that when it comes time to commit, the Unit of Work decides what to do. It carries out the inserts, updates, and deletes in the right order.

An article in the following link, discusses various aspects of this pattern and examines the issues around persistence ignorance.

http://msdn.microsoft.com/en-us/magazine/dd882510.aspx#id0420003

Enjoy...