Why is this an Index Scan and not a Index Seek?

It is using an Index Scan primarily because it is also using a Merge Join. The Merge Join operator requires two input streams that are both sorted in an order that is compatible with the Join conditions.

And it is using the Merge Join operator to realize your INNER JOIN because it believes that that will be faster than the more typical Nested Loop Join operator. And it is probably right (it usually is), by using the two indexes it has chosen, it has input streams that are both pre-sorted according your join condition (LocationID). When the input streams are pre-sorted lie this, then Merge Joins are almost always faster than the other two (Loop and Hash Joins).

The downside is what you have noticed: it appears to be scanning the whole index in, so how can that be faster if it is reading so many records that may never be used? The answer is that Scans (because of their sequential nature) can read anywhere from 10 to 100 times as many records/second as seeks.

Now Seeks usually win because they are selective: they only get the rows that you ask for, whereas Scans are non-selective: they must return every row in the range. But because Scans have a muchhigher read rate, they can frequently beat Seeks as long as the ratio of Discarded Rows to Matching Rows is lower than the ratio of Scan rows/sec VS. Seek rows/sec.

Questions?

OK, I have been asked to explain the last sentence more:

A "Discarded Row" is one that the the Scan reads (because it has to read everything in the index), but that will be rejected by the Merge Join operator, because it does not have a match on the other side, possibly because the WHERE clause condition has already excluded it.

"Matching Rows" are the ones that it read that are actually matched to something in the Merge Join. These are the same rows that would have been read by a Seek if the Scan were replaced by a Seek.

You can figure out what there are by looking at the statistics in the Query Plan. See that huge fat arrow to the right of the Index Scan? That represents how many rows the optimizer thinks that it will read with the Scan. The statistics box of the Index Scan that you posted shows the Actual Rows returned is about 5.4M (5,394,402). This is equal to:

TotalScanRows = (MatchingRows + RejectedRows)

(In my terms, anyway). To get the Matching Rows, look at the "Actual Rows" reported by the Merge Join operator (you may have to take off the TOP 100 to get this accurately). Once you know this, you can get the Discarded rows by:

RejectedRows = (TotalScanRows - MatchingRows)

And now you can calculate the ratio.

Above Resourse Link

Below List Article Reference

Merge join Vs Hash join Vs Nested loop join

Const vs. Readonly

const vs. readonly

const and readonly perform a similar function on data members, but they have a few important differences.

const

A constant member is defined at compile time and cannot be changed at runtime. Constants are declared as a field, using the const keyword and must be initialized as they are declared. For example;

public class MyClass {   public const double PI = 3.14159; }

PI cannot be changed in the application anywhere else in the code as this will cause a compiler error.

Constants must be a value type (sbyte, byte, short, ushort, int, uint, long, ulong, char, float, double, decimal, or bool), an enumeration, a string literal, or a reference to null.

Since classes or structures are initialized at run time with the new keyword, and not at compile time, you can't set a constant to a class or structure.

Constants can be marked as public, private, protected, internal, or protected internal.

Constants are accessed as if they were static fields, although they cannot use the static keyword.

To use a constant outside of the class that it is declared in, you must fully qualify it using the class name.

A read only member is like a constant in that it represents an unchanging value. The difference is that a readonly member can be initialized at runtime, in a constructor as well being able to be initialized as they are declared. For example:

readonly

public class MyClass {   public readonly double PI = 3.14159; }

or

public class MyClass {   public readonly double PI;     public MyClass()   {     PI = 3.14159;   } }

Because a readonly field can be initialized either at the declaration or in a constructor, readonly fields can have different values depending on the constructor used. A readonly field can also be used for runtime constants as in the following example:

public static readonly uint l1 = (uint)DateTime.Now.Ticks;

Notes

• readonly members are not implicitly static, and therefore the static keyword can be applied to a readonly field explicitly if required.

• A readonly member can hold a complex object by using the new keyword at initialization.

static

Use of the static modifier to declare a static member, means that the member is no longer tied to a specific object. This means that the member can be accessed without creating an instance of the class. Only one copy of static fields and events exists, andstatic methods and properties can only access static fields and static events. For example:

public class Car {   public static int NumberOfWheels = 4; }

The static modifier can be used with classes, fields, methods, properties, operators, events and constructors, but cannot be used with indexers, destructors, or types other than classes.

static members are initialized before the static member is accessed for the first time, and before the static constructor, if any is called. To access a static class member, use the name of the class instead of a variable name to specify the location of the member. For example:

int i = Car.NumberOfWheels;

Reference URL : http://en.csharp-online.net/const,_static_and_readonly

SQL SERVER – 2005 – Find Index Fragmentation Details – Slow Index Performance

Sample script :

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO


Ref Link

Javascript Math Round

The required number argument is the value to be rounded to the nearest integer.

For positive numbers, if the decimal portion of number is 0.5 or greater, the return value is equal to the smallest integer greater than number. If the decimal portion is less than 0.5, the return value is the largest integer less than or equal to number.

For negative numbers, if the decimal portion is exactly -0.5, the return value is the smallest integer that is greater than the number.

For example, Math.round(8.5) returns 9, but Math.round(-8.5) returns -8.

MSDN Ref LINK


For Example :

var a=Math.round(2.60);      [3]
var b=Math.round(2.50);      [3]
var c=Math.round(2.49);      [2]
var d=Math.round(-2.60);    [-3]
var e=Math.round(-2.5);      [-2]
var f=Math.round(-2.49);     [-2]

Script All the Stored Procedures in The Database

Script All the Stored Procedures in The Database

In Sql Server 2005 and 2008 you can script the stored procedure in Management Studio by right clicking on Store Procedure name and clicking on “Script Store Procedure as” and then “Create To”.

But if you want to script all the Stored Procedures in the database programmatically, then here is the simple T-SQL query for it -

To script All the Stored Procedures in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'P'

If the Stored Procedure is created with ENCRYPTION option then you will get the NULL in the definition column.

Similarly,

To script All the Views in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'V'

To script All the Functions in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.objects as O ON M.object_id = O.object_id WHERE O.type = 'FN'

For scripting all Triggers small modification is required, instead of sys.objects I joined thesys.triggers with sys.sql_modules.

To script All the Triggers in the Database :

SELECT    O.Name as ProcName         ,M.Definition as CreateScript         ,O.Create_Date         ,O.Modify_Date FROM sys.sql_modules as M INNER JOIN sys.triggers as O ON M.object_id = O.object_id

Resource Reference Link

Multithread Pass List Parameter to Function

 ThreadPool.QueueUserWorkItem(new WaitCallback(Function), new object[] { param1,  param2, param3, param4 });

Scripting dependency of User Defined Table Types

-- Find all referencing objects to user-defined table type in @fullObjectName parameter
-- and generate DROP scripts and CREATE scripts for them
CREATE PROC ap_FindReferences (@fullObjectName VARCHAR(200))
AS
BEGIN
    SET NOCOUNT ON

    IF (TYPE_ID (@fullObjectName) IS NULL)
    BEGIN
        RAISERROR ('User-defined table type ''%s'' does not exists. Include full object name with schema.', 16,1, @fullObjectName)
        RETURN
    END;

    WITH sources
    AS
    (
        SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(m.object_id)) RowId, definition
        FROM sys.sql_expression_dependencies d
        JOIN sys.sql_modules m ON m.object_id = d.referencing_id
        JOIN sys.objects o ON o.object_id = m.object_id
        WHERE referenced_id = TYPE_ID(@fullObjectName)
    )

    SELECT 

        'DROP ' +
            CASE OBJECTPROPERTY(referencing_id, 'IsProcedure')
            WHEN 1 THEN 'PROC '
            ELSE
                CASE
                    WHEN OBJECTPROPERTY(referencing_id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsInlineFunction') = 1 THEN 'FUNCTION '
                    ELSE ''
                END
            END
        + SCHEMA_NAME(o.schema_id) + '.' +
        + OBJECT_NAME(m.object_id)    

    FROM sys.sql_expression_dependencies d
    JOIN sys.sql_modules m ON m.object_id = d.referencing_id
    JOIN sys.objects o ON o.object_id = m.object_id
    WHERE referenced_id = TYPE_ID(@fullObjectName)
    UNION  ALL
    SELECT  'GO'
    UNION  ALL
    SELECT
        CASE
            WHEN number = RowId    THEN DEFINITION
            ELSE 'GO'
        END
     FROM sources s
    JOIN (SELECT DISTINCT number FROM master.dbo.spt_values) n ON n.number BETWEEN RowId AND RowId+1

END
GO

-- Invokes ap_FindReferences procedure and writes scripted result to .sql file 
CREATE PROC ap_WriteReferences
@typeToFind VARCHAR(200)
AS
BEGIN

    DECLARE @sqlCmd VARCHAR(500)
    DECLARE @database VARCHAR(200) = 'test'
    DECLARE @outputFile VARCHAR(500) = 'c:\refences.sql'

    SET @sqlCmd = 'sqlcmd.exe -d '+@database+' -q "EXEC ap_FindReferences '''+ @typeToFind +'''" -o '+ @outputFile +' -h-1 -y0'

    EXEC xp_cmdshell @sqlCmd

END