Find Missing date Query

Ø  Source table

  select Distinct  CONVERT (DATE, EntDt) as dt from  dbo.K_RPT_EMAIL_STATUS

  order by CONVERT (DATE, EntDt)  asc

Ø  Create Function

Create FUNCTION [dbo].[GetAllDaysInBetween](@FirstDay DATETIME, @LastDay DATETIME)

RETURNS @retDays TABLE

(

    DayInBetween DATETIME

)

AS

BEGIN

    DECLARE @currentDay DATETIME

    SELECT @currentDay = @FirstDay

    WHILE @currentDay <= @LastDay

    BEGIN

        INSERT @retDays (DayInBetween)

            SELECT @currentDay

        SELECT @currentDay = DATEADD(DAY, 1, @currentDay)

    END

    RETURN

END

Ø  Final Query

SELECT  DayInBetween AS missingDate

       FROM dbo.GetAllDaysInBetween('2014-11-25', '2015-11-24') AS AllDaysInBetween

  WHERE NOT EXISTS

      (select Distinct  CONVERT (DATE, EntDt) as dt from  dbo.K_RPT_EMAIL_STATUS

        WHERE CONVERT (DATE, EntDt)  = AllDaysInBetween.DayInBetween  )

SQL Client Statistics

SQL Server Management Studio: “Include Client Statistics” Button

This button is located on the SQL Editor toolbar.

Open a query editor window, click on it to highlight it, write a query, and execute it. Your results will look like Figure 1.

Figure 1 – Client Statistics

Some really valuable information can be found here, such as the number of INSERT, DELETE, UPDATE, and SELECT statements – think of how useful that can be when tuning a complex stored procedure. You can see the number of rows affected or returned, and the number of transactions executed.

The network statistics lets you see how much traffic is moving from your client to the server and back.

The time statistics tells you how much time was spent processing on the client versus how much time was spent waiting for the server. These figures are in milliseconds.

That’s useful when you run a query once, but its usefulness becomes more apparent when you are tuning a query. Statistics will be shown for the ten most recent executions, and they will be averaged, as shown in Figure 2. Thus, as you are making changes to a query – perhaps changing the conditions in the WHERE clause, or comparing an AVERAGE to a window function – the statistics will update. You can, at a glance, compare the changes. You’ll even see green arrows for improved performance, and red arrows for worse performance.

Figure 2 – Client Statistics for multiple executions of a query

Entity Framework

Entity Framework

ADO.NET is a very strong framework for data access. 

ADO.NET has been around since many years and there are a lot of systems running over ADO.NET. Developers who are totally oblivious to the concept of ORMs will probably be asking "What is Entity Framework? What are the benefits of using it and is it an alternative to ADO.NET?"

Well, to answer the first question about what is Entity Framework, Entity Framework is an Object Relational Mapper (ORM). It basically generates business objects and entities according to the database tables and provides the mechanism for:

1.       Performing basic CRUD (Create, Read, Update, Delete) operations.

2.       Easily managing "1 to 1", "1 to many", and "many to many" relationships.

3.       Ability to have inheritance relationships between entities.

and to answer the second question, the benefits are:

1.       We can have all data access logic written in higher level languages.

2.       The conceptual model can be represented in a better way by using relationships among entities.

3.       The underlying data store can be replaced without much overhead since all data access logic is present at a higher level.

and finally, the last question that whether it is an alternative to ADO.NET, the answer would be "yes and no". Yes because the developer will not be writing ADO.NET methods and classes for performing data operations and no because this model is actually written on top of ADO.NET, meaning under this framework, we are still using ADO.NET. So let us look at the architecture of Entity Framework (diagram taken from MSDN):

More Read: Click here

SQL Function

SQL has many built-in functions for performing calculations on data.

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

More Study à http://www.w3schools.com/sql/sql_functions.asp

Abstraction

Abstraction :

·         Abstraction refers to the act of representing essential features without including the background details or explanations.

·         Abstraction defines way to abstract or hide your data and members from outside world.

·         Classes use the concept of abstraction and are defined as a list of abstract attributes.

·         Simply speaking Abstraction is hiding the complexities of your class or struct or in a generic term Type from outer world.

·         This is achieved by means of access specifiers.

Access Modifier	Description (who can access)
Private	Only members within the same type.  (default for type members)
Protected	Only derived types or members of the same type.
Internal	Only code within the same assembly. Can also be code external to object as long as it is in the same assembly.  (default for types)
Protected internal	Either code from derived type or code in the same assembly. Combination of protected OR internal.
Public	Any code. No inheritance, external type, or external assembly restrictions.

Code Example :

Namespace AbstractionExample

       Public MustInherit Class Shape

               Private _area As Single

               Private _perimeter As Single

               Public Property Area() As Single

                       Get

                               Return _area

                       End Get

                       Set

                               _area = value

                       End Set

               End Property

               Public Property Perimeter() As Single

                       Get

                               Return _perimeter

                       End Get

                       Set

                               _perimeter = value

                       End Set

               End Property

               Public MustOverride Sub CalculateArea()

               Public MustOverride Sub CalculatePerimeter()

       End Class

End Namespace

Advantages of abstraction are the hiding of implementation details, component reuse, extensibility, and testability. When we hide implementation details, we reveal a cleaner, more comprehensible and usable interface to our users. We are separating our interface from our implementation, and this makes component reuse more practical. Many, if not all of the object-oriented concepts we have discussed throughout this document play a role in the abstraction principle. Working together, their end goal is the same, to produce software that is flexible, testable, maintainable, and extensible.

DLL vs EXE

EXE:

1.    It's a executable file

2.    When loading an executable, no export is called, but only the module entry point.

3.    When a system launches new executable, a new process is created

4.    The entry thread is called in context of main thread of that process.

DLL:

1.    It's a Dynamic Link Library

2.    There are multiple exported symbols.

3.    The system loads a DLL into the context of an existing process.

Ø  Well the most obvious difference is that EXE files are used for launching an application (it contains a startup function etc), whereas DLLs are loaded into an application (ie they can't run by themselves).

SQL Error_Message()

SQL SERVER – Scope of ERROR_MESSAGE

SQL Server supports Error handling efficiency. You can do this with the help of TRY and CATCH blocks. If there is an error you can get the exact error message using the system function ERROR_MESSAGE().

I will demonstrate it using the following code

BEGIN TRY
SELECT 1+'SQL' AS RESULT
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ERROR
END CATCH

The result you get is

ERROR
——————————————————————————
Conversion failed when converting the varchar value ‘SQL’ to data type int.

It is because number 1 cannot be added to a string

But ERROR_MESSAGE will work only inside the CATCH block. If you use it outside of it, you will get nothing

BEGIN TRY
SELECT 1+'SQL' AS RESULT
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ERROR
END CATCH
SELECT ERROR_MESSAGE() AS ERROR

If you execute the above, you get the following two result sets

Resultset 1:

ERROR
——————————————————————————-
Conversion failed when converting the varchar value ‘SQL’ to data type int.

Resultset 2:

ERROR
——————————————————————————-
NULL

As you see ERROR_MESSAGE() returns NULL when used outside of CATCH block.