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

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)

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

T-SQL query to search any object

This one is really handy to search for any object in your SQL Server database. Very useful if you want to quickly check that a stored procedure or view is being used elsewhere etc. Hope you find it useful too!

SELECT
syo.name
FROM syscomments syc
INNER JOIN sysobjects syo ON
syo.id = syc.id
WHERE syc.[text] LIKE '%DROP PROC%'