SQL Coalesce Example

What does this SQL do?

SELECT COALESCE(FirstName + ' ' + MiddleName, FirstName) + ' ' + LastName FROM Customer

If MiddleName is NULL, then FirstName + '' + MiddleName would be NULL, and so would return FirstName + ' ' + LastName.

If MiddleName is not NULL, it would return FirstName + ' ' +MiddleName + ' ' + LastName

Calling a WCF client proxy properly

There is a lot of discussion on how to use the WCF client proxy correctly. This is one use case where you do NOT want to use the elegant using statement, whether you are instantiating the client proxy directly or creating a service factory to create the WCF channel. In other words, don't do either of these:

using ( var client = new WcfClientProxy())
{
    .........
    .........
}

Or

using (var factory = new ChannelFactory<ISomeService>())
{
     var channel = factory.CreateChannel()
     ..........
     ..........
}


This is because the channel could have faulted ( a timeout, a connection fault etc) before dispose can be called with the using statement (the last } is where dispose is called).

The accepted best practice is to

• wrap the call to the WCF Service using a try catch block
• call close on the proxy at the end of the call in the try block
• call abort (immediately calls close) at the end of each catch statement

Not doing so could leave a lot of open and non-usable channels and connections on your Server serving the WCF requests, thereby degrading performance significantly.

SO Reference
http://stackoverflow.com/questions/573872/what-is-the-best-workaround-for-the-wcf-client-using-block-issue

MSDN article
http://msdn.microsoft.com/en-us/library/aa355056.aspx

And here's an interesting blog on it
http://adamprescott.net/2013/07/18/wcf-client-closal-disposal/

Did you know - AngularJS custom Service and Promise trivia

If you are writing a custom Angular service that says goes out and

• does a $http.get() and
• uses  .then() promise

the .then() promise will actually end up returning another promise that you can wire up to in your controller(where you are using this custom service). Also, note that in this scenario you are likely to use the revealing module pattern - like using a factory to return an object that you need instantiate, in order to use the custom service. This is different from the regular service singleton you get when using the built-in angular services.

Sql Update Stats Tip

Auto Update Stats only works with the default sampling which are changes that affect 20% of the total number of rows. For very large tables or even for smaller tables with a highly jagged distribution of data, we often need to manually update the stats using the FULLSCAN option. Otherwise the stats will remain highly skewed and the cardinality estimates that the Sql Optimizer depends on will be unreliable.

Note that FULLSCAN is not the panacea of all skewed data and if the data distribution is very jagged, it may not help much in updating the stats. That's where creating filtered stats comes handy, but that's another topic.

Command to check the histogram of the stats for an index, run
DBCC SHOW_STATISTICS('tablename', 'indexname')

Another option (Sql2008 and higher) to automatically trigger updating the stats (rather than the 20% default threshold) on say large tables is by turning on the trace 2371 like so

DBCC TRACEON(2371, -1)  -- the -1 is for all sessions
GO

DBCC TRACESTATUS
GO

The downside of turning on this trace is it results in recompilation of all queries accessing the table.


From SQL2008 onwars, the DMV sys.dm_db_stats_properties is useful in determining how much changes have taken place in the table before the update stats would be triggered. Of particular interest is the modification_counter column using the query

--Examine stats of a table
SELECT
     s.name,
     p.object_id,
     p.stats_id,
     p.last_updated,
     p.rows,
     p.rows_sampled,
     p.steps,
     p.unfiltered_rows,
     p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties
  (s.object_id, s.stats_id) AS p
WHERE s.object_id = OBJECT_ID('tablename')


References:
http://msdn.microsoft.com/en-us/library/dd535534(SQL.100).aspx
http://support.microsoft.com/kb/2754171

Plan Cache Pollution

Sql Server will cache plans for "safe" statements but not in the way you might expect. For example, if we pass different integer values for a parameter in a safe statement, Sql Server may cache tinyint, smallint, int and bigint plans, thereby leading to Plan Cache pollution.

If Sql Server deems a set of ad hoc identical statements to be unsafe, then it will not parameterize them and end up creating a separate plan for each of the statement. For instance WHERE predicates with = and LIKE may throw up different plans, depending on the data selectivity. So Sql Server may use a non-clustered index that is available or decide to go for a full table scan, even for the same LIKE - depending on what parameter value has been used.

Now compiling the identical queries each time and having the separate plans may be good too as it will be optimized for each statement. The downside is the plan bloat and non-reuse of already compiled plans.

Likewise, when using variables as the predicate parameters, SQL Server deems them to be unsafe, so even if we pass in a highly selective value for the parameter, the optimizer will not use the non- clustered index available and will treat the variable value as "Unknown", and choose an index scan plan. This is because the variable value is not known until runtime to Sql Server.

So using a variable in an sd hoc sql statement fundamentally changes the way Sql Server uses the optimizer!

--------------------------------------------------------------------------------------
Plan Cache pollution with Dynamic String Execution

Even though it is a safe statement, with string concatenation and no strongly type parameter, this will lead to plan cache pollution.

declare @sql nvarchar(4000), @memberId INT = 12345;
select @sql = N'Select * from member where memberId="
                           + CONVERT(nvarchar(10), @memberId);

Exec (@sql);

To make it strongly typed, convert the INT variable back to its data type after the string concatenation within the string like so:

declare @sql nvarchar(4000), @memberId INT = 12345;
select @sql = N'Select * from member where memberId= CONVERT(INT,'
                           + CONVERT(nvarchar(10), @memberId) + N')';

Exec (@sql);

This will result in just one plan, no matter how many times you execute it with different values for the INT parameter. So this is a good way to right dynamic sql with strongly typed parameters.

Avoiding Sql Injection

1. Use QUOTENAME to wrap user supplied parameters to prevent Sql Injection y delimiting it. Only works with sys objects - table names, column names etc with nvarchar 128. 
2. REPLACE all single quotes with double quotes for simple string parameters and delimit the parameters.
3. Make sure that all parameters passed in are validated at the client application layer
4. For Dynamic String Executions in Stored procedure, you can even create a login-less user with very low privileges to execute that DSE within the stored procedure. This is done by restricting the execution context with EXECUTE AS

Sql Plan Cache

Running ad hoc statements (both Sql Server deemed safe and unsafe statements) pollute the plan cache. Running the same statements using sp_ExecuteSql (strongly typed and parameterized) helps avoid plan cache pollution for safe statements. But with unsafe statements, the forced plan caching by sp_ExecuteSql causes inaccurate estimates and related issues.
-------------------------------------------------------------------------------------------
Unlike adhoc statements that will create separate plans for different data types even for the same safe statement, sp_ExecuteSql will force one plan using strongly type parameterization. Consider the 2 batches below using sp_executesql(safe statement in this case):

declare @sql nvarchar(4000)
select @sql = N'Select n.* from member n where n.memberId = @memberId';
EXEC [sp_Executesql] @sql, N'@memberId INT', 123;

declare @sql nvarchar(4000)
select @sql = N'Select n.* from member n where n.memberId = @memberId';
EXEC [sp_Executesql] @sql, N'@memberId INT', 123456789;

Here a single plan will be in the plan cache and the same plan will be used in both the batch executions. So better plan reuse here. The plan like this is very stable and very beneficial to reuse the plan across multiple executions.

The catch is for unsafe or unstable plans, reusing the same plan forced by sql_executesql can actually be detrimental to sql performance!
------------------------------------------------------------------------------------------
From Pinal Dave

http://blog.sqlauthority.com/2010/08/28/sql-server-plan-cache-retrieve-and-remove-a-simple-script/

Running adhoc queries from say SSMS against Production would just pollute the plan cache and can cause cache bloat.
This query captures plans used only once in its lifetime

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;


You can see how much memory is already bloated by not-so-useful queries. If you want to remove any large plan cache which you do not think is useful to you, you can run the following command to remove it:

DBCC FREEPROCCACHE(plan_handle)
------------------------------------------------------------

From http://sqlmag.com/database-performance-tuning/sql-server-plan-cache-junk-drawer-your-queries

Script to find inefficient plans from the cache

--Missing Indexes
;WITH XMLNAMESPACES(DEFAULT
N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT dec.usecounts, dec.refcounts, dec.objtype
  , dec.cacheobjtype, des.dbid, des.text,deq.query_plan
FROM sys.dm_exec_cached_plans AS dec
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
WHERE
deq.query_plan.exist
(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple
/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY dec.usecounts DESC

--Convert Issues
;WITH XMLNAMESPACES(DEFAULT
N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
cp.query_hash, cp.query_plan_hash,
ConvertIssue =
  operators.value('@ConvertIssue','nvarchar(250)'),
Expression =
  operators.value('@Expression','nvarchar(250)'),
  qp.query_plan
FROM sys.dm_exec_query_stats cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY query_plan.nodes('//Warnings/PlanAffectingConvert')
  rel(operators)

Async methods in ASP.NET 4.5

This one is an useful read from Scott Hanselman's post, particularly the comments from Stephen Cleary

http://www.hanselman.com/blog/TheMagicOfUsingAsynchronousMethodsInASPNET45PlusAnImportantGotcha.aspx

Friday, August 30, 2013 9:54:09 AM UTC

@Ben, @Daniel: You should not use Task.Run on the server side. It makes sense on the client (to free up the UI thread), but it does not make sense on the server side (you're freeing up a worker thread... by using another worker thread). This is covered in an excellent video on Channel9.

@Daniel: You should not use Task.Result in async code; it's easy to cause deadlocks.

@Ben: All three tasks are running when you call the first await, so even though that method is suspended, the other tasks are already running.

@MarcelWijnands: It's the difference between "wait for the first task, then continue the method, wait for the second task, then continue the method, wait for the third task, then continue the method" and "wait for all tasks, then continue the method". The Task.WhenAll approach has less switching back and forth.

Also from Stephen, never block on async code on a synchronous method (using Task.Wait()) like so

Figure 3 A Common Deadlock Problem When Blocking on Async Code

1.public static class DeadlockDemo
2.{
3.  private static async Task DelayAsync()
4.  {
5.    await Task.Delay(1000);
6.  }
7.  // This method causes a deadlock when called in a GUI or ASP.NET context.
8.  public static void Test()
9.  {
10.    // Start the delay.
11.    var delayTask = DelayAsync();
12.    // Wait for the delay to complete.
13.    delayTask.Wait();
14.  }
15.}

A scenario when to use Func/Action

Can you tell the difference between the following 2 code snippets?

1) Using a Func/Action within a static method

public static class ExpensiveMethodWithAFuncClass<T>
{
      public static readonly Func<T> ExpensiveMethod= ExpensiveMethodImplementation();
      private static Func<T> ExpensiveMethodImplementaion
     {
           //some expensive operations here
          // returns a compiled delegate
     }
}

2)  Same class without using a Func<T>

public static class ExpensiveMethodClass<T>
{
      public static T ExpensiveMethod
     {
         //build the expression here, compile and invoke the expression
      }
}

Answer :  In 1) when we call ExpensiveMethod, the implementation ExpensiveMethodImplementation() is executed only once.

In the case of 2) we will execute ExpensiveMethod every time we call it.

 If the implementation is an expensive operation, 1) will benefit from using the delegate Func<T>

Data Binding in WPF

To use Data Binding in WPF implement INotifyPropertyChanged (INPC) for your datasource (typically a POCO object) which will serve as the datacontext for your View.

Implementing INPC requires calling an event called PropertyChanged from within your POCO class, whenever one of its property changes. The signature for the event is

public event PropertyChangedEventhandler PropertyChanged;

To call this event, by convention we define a helper method called OnPropertyChanged like so

private void OnPropertyChanged([CallerMemberName] string title="")
{
     if (PropertyChanged != null)
     {
          PropertyChanged(this, new PropertyChangedEventArgs(caller));
      }
}

This helper method should be called from every  property setter in the POCO class and that's how we get  Data Binding in WPF.

private string _title;
public string Title
{
   get { return _title;}
   set {
           _title = value;
           OnPropertyChanged();
         }
}

Simple and neat!  Do ko.observable() and $watch sound familiar?

Using a User Defined Table Type as an alternative to Sql Bulk Insert in SQL Server

Recently I had to solve a problem while importing a relatively large .CSV file to SQL Server from a C# service application code. This had to be done without using an ETL tool like SSIS. The .CSV files would come in the form of a Zip file uploaded by an ASP.NET MVC Web application user.

Some sample rows of the data with the header row in the .CSV file are as below

Id, Some Description field, Another field, Yet another field, A Guid field
1046348264289,"Comma, in this field",no comma here,,48B6B3FE-C2E1-4B01-AB4E-90FBC98E1F81
1046348264289,"Comma, in this field",no comma here,,58B6B3FE-C2E1-4B01-AB4E-90FBC98E1F82
1046348264277,"Comma, in this field",no comma here,,48B6B3FE-C2E1-4B01-AB4E-90FBC98E1F81

The requirement was to capture only the first and last fields in every row. There could be thousands of rows in a single file, and the zip file could consist of many such files.

I initially started off by parsing the uploading file in C# and capturing only the required fields in a structure, that I would later turn into one big concatenated string, sending it down to a stored procedure for further parsing and inserting in the target table. I started off by writing the parsing routine and data structure used to capture the data below.

        public static Dictionary<string, List<string>> ParseCsv(Stream stream)
        {
            var mapDictionary = new Dictionary<string, List<string>>();
            using (var parser = new TextFieldParser(stream))
            {
                stream.Position = 0;
                parser.Delimiters = new string[] { "," };

                parser.ReadLine(); //read first line to advance and ignore the header fields

                while (!parser.EndOfData)
                {
                    string[] fields = parser.ReadFields();

                    if (fields == null) continue;

                    List<string> guids;
                    if (!mapDictionary.TryGetValue(fields.FirstOrDefault(), out guids))
                    {
                        guids = new List<string>();
                        guids.Add(fields.LastOrDefault());
                        mapDictionary.Add(fields.FirstOrDefault(), guids);
                    }
                    else
                    {
                        guids.Add(fields.LastOrDefault());
                    }              
                }
            }

            return mapDictionary;
        }

I would later use the routine below to create the string to send down the stored procedure

        private void SaveMappings()

        {

            var mapped = new StringBuilder();



            foreach (var pair in _mapDictionary)

            {

                var guidsSb = new StringBuilder();

                foreach (var item in pair.Value)

                {

                    guidsSb.Append(String.Format("{0},", item));

                }

                var guids = guidsSb.ToString().TrimEnd(',');

                mapped.Append(String.Format("{0},{1}|", pair.Key, guids)); 

                     //1046348264289,48B6B3FE-C2E1-4B01-AB4E-90FBC98E1F81,58B6B3FE-C2E1-4B01-AB4E-90FBC98E1F82|

            }



            //process all the mappings in one call to the backend

            _repository.RunStoredProcedure(mapped.ToString());

        }

However, this turned out to be overtly complex in processing the data in the stored procedure and had to use a recursive string split table valued function inside the stored procedure as well. Not very performant and error prone to say the least.

The Good News is from Sql Server 2008, I could simply make use of  the User Defined Data Table Type and send down the data I need to insert in the back-end as a temporary table!! No complex string parsing in the stored procedure anymore.


The User Defined Data table Type method:

First, simply define the table structure you need in SQL like so

CREATE TYPE [dbo].[ItemTopicMap] AS TABLE(

        [ItemIdentifier] [bigint] NOT NULL,

        [TopicId] [uniqueidentifier] NOT NULL

)

GO

Next, simplify the earlier C# .CSV parsing routine like below. Note the use of the ADO.NET DataTable.

        public static DataTable ParseCsv(Stream stream)

        {

            var dt = new DataTable();

            dt.Columns.Add("ItemIdentifier", typeof(string));

            dt.Columns.Add("TopicId", typeof(Guid));



            using (var parser = new TextFieldParser(stream))

            {

                stream.Position = 0;

                parser.Delimiters = new string[] { "," };

                parser.ReadLine(); //read first line to advance and ignore the header fields



                while (!parser.EndOfData)

                {

                    string[] fields = parser.ReadFields();



                    if (fields == null) continue;



                    if (fields.FirstOrDefault().Length > 10 &&

                        IsValidGuid(fields.LastOrDefault()))

                    {

                        dt.Rows.Add(fields.FirstOrDefault(), fields.LastOrDefault());

                    }

                    else

                    {

                        //log line out to bad file with filename and linenumber

                    }

                }

            }



            return dt;

        }

Then inside the repository's execute stored procedure method, do something like this

  public void RunStoredProcedure(DataTable dataTable)

  {

       var conStr = ConfigurationManager.ConnectionStrings["DefaultConn"].ConnectionString;

            using (var connection = new SqlConnection(connectionString))

            {

                connection.Open();

                var proc = new SqlCommand("InsertItemTopics", connection);

                proc.CommandType = CommandType.StoredProcedure;



                proc.Parameters.AddWithValue("@ItemTopicMap", dataTable);

                proc.Parameters["@ItemTopicMap"].TypeName = "ItemTopicMap";



                proc.CommandTimeout = 180;

                proc.ExecuteNonQuery();

                connection.Close();

            }

   }

and Voila, no more further parsing from a huge string in the back-end!!

Note that the data table type defined here is used as some sort of temporary table on the back-end that we can read from in a stored procedure for any further validation and ultimately inserting or upserting the data in the actual destination table.

For completeness, a simplified version of the stored procedure InsertItemTopics is shown below:

CREATE PROCEDURE [dbo].[InsertItemTopics]

( @ItemTopicMap ItemTopicMap READONLY )

AS

BEGIN

    SET NOCOUNT ON



        TRUNCATE TABLE ItemTopicMapDestination

        INSERT INTO ItemTopicMapDestination(ItemIdentifier, TopicId)

                SELECT itemIdentifier, TopicId FROM @ItemTopicMap

END

The JavaScript Module Pattern

It allows private members that can be used to maintain state using closure, and exposing only public members. So only a public API is exposed, keeping everything within the closure (nested function using the private variables) as private. This pattern is similar to an IIFE, save that an object is returned instead of result of a function execution.

Note that JavaScript don't have getters and setters or access modifiers, so we use closures and function scopes to simulate private members and maintain state inside a module.

//Shopping Cart Module
var shoppingCart = (function(){
      var cart= [];  //private
      
      //public API
      return {
            addItem: function(value) {
                    cart.push(value);
            },
            itemCount: function() {
                   return cart.length;
           },
           totalPrice: function() {
                    var i = this.itemCount(),
                               p = 0;

                    while(i--) {
                          p += cart[i].price;
                    }
                   return p;
           }
     }      
}());

The shopping cart module is self-contained in a global object called shoppingCart. The cart array is private and only exists within the module's closure. The addItem and itemCount methods can access the cart array as it is within the scope of the outer function.

The module returns an object that gets assigned to shoppingCart as it is immediately invoked. this way we can interact with the shoppingCart object as follows:

//call an API method of the module
shoppingCart.addItem({item:'Effective JavaScript',price:8.99});

//this will work
console.log(shoppingCart.itemCount());
console.log(shoppingCart.totalPrice());

//this will return undefined
console.log(shoppingCart.cart);

Notice that the methods defined in the shoppingCart module have been effectively namespaced with shoppingCart and "avoids" global collisions. Of course, the issue of global variable declaration still remains if we have another global variable called shoppingCart declared.

Also, an object literal can be used to define a module by just assigning it to a variable! This is sufficient if we don't need to maintain state or require private variables. Object literals cannot instantiate using the new operator and we can simply start using the object declared as an instance.

Why use Modules in JavaScript?

The short answer : Mimic classes and code organization!

It mimics classes for code encapsulation - not inheritence per se. 
Javascript has prototypal inheritence, where we can dynamically add members to the prototype property of the class/object we define. The members added are shared across all instances of classes created AFTER we have defined the prototype members - akin to static members. The members defined if any, in the class(function) itself belong to individual instances with reference to the "this" keyword.

A module can be a distinct unit of functionality, like a navigation module, a filter module, an email notification module etc. We can think of it as a JavaScript equivalent of a .NET component. Decomposing your code into modules helps maintainability of your application by keeping them loosely coupled and removing dependencies where possible.

• Allows encapsulation of private members giving it an object-oriented flavor
• Avoids function name clashes with other parts of the JavaScript code
• So helps in code organization
• By immediately invoking the function expression, returns only the object container or function with the values of the private members remembered through closure. So returns only a public API in the forma of an object, keeping everything else private using closure.
• Very similar to IIFE, except an object is returned, rather than a function.

Sample module to avoid global scope contamination using Closure and the Revealing Module Pattern:

var module = (function () {
   var privateVariable; //private member

  /* a private function uses the private variable in a closure .. (read nested function) 
      Allows the private member to be declared (and initialized) only once and exposed indirectly
      without  polluting the global namespace */
   var privateFunction = function (x) {
         /* uses the private variable */
        };
  
   /* Another private function - just named public here because we are going to expose it outside
      the module defintion */
   var publicFunction = function () {
         privateFunction (); /* Can call the private function */
   };

   /* returns an object literal */    return {
      PublicFunction : publicFunction  /* exposed as a public function here! */
  } 
}()); /* Outer function is immediately executed here */

/* Note that this is a Singleton pattern as it is immediately executed here, and we cannot create
   multiple instances of this module */

In Modern JavaScript design patterns, these 4 object-oriented design patterns

1. Prototype Pattern
2. Module Pattern
3. Revealing Module Pattern
4. Revealing Prototype Pattern

can be implemented using

• AMD - allows modules to be loaded asynchronously without care for script loading order
• CommonJS module - Module dependencies have to worry about the order while loading script in browser
• ECMAScript Harmony - the next version of JavaScript!