Table Variables In SQL SERVER

Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables. In many cases a table variable can outperform a solution using a temporary table, although we will need to review the strengths and weaknesses of each in this article.

Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement, as you can see in the following example

DECLARE @ProductTotals TABLE

(

  ProductID int,

  Revenue money

)

While connected to the Northwind data-base, we could write the following SELECT statement to populate the table variable.

INSERT INTO @ProductTotals (ProductID, Revenue)

  SELECT ProductID, SUM(UnitPrice * Quantity)

FROM [Order Details] GROUP BY ProductID

You can use table variables in batches, stored procedures, and user-defined functions (UDFs). We can UPDATE records in our table variable as well as DELETE records.

UPDATE @ProductTotals

  SET Revenue = Revenue * 1.15

WHERE ProductID = 62

DELETE FROM @ProductTotals

WHERE ProductID = 60

SELECT TOP 5 *

FROM @ProductTotals

ORDER BY Revenue DESC

You might think table variables work just like temporary tables (CREATE TABLE #ProductTotals), but there are some differences.

Scope

Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter. In fact, a table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.

Although you cannot use a table variable as an input or output parameter, you can return a table variable from a user-defined function – we will see an example later in this article. However, because you can’t pass a table variable to another stored procedure as input – there still are scenarios where you’ll be required to use a temporary table when using calling stored procedures from inside other stored procedures and sharing table results.

The restricted scope of a table variable gives SQL Server some liberty to perform optimizations.

Performance

Because of the well-defined scope, a table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.

Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit. For more information on why stored procedures may recompile, look at Microsoft knowledge base article 243586 (INF: Troubleshooting Stored Procedure Recompilation).

Other Features

Constraints are an excellent way to ensure the data in a table meets specific requirements, and you can use constraints with table variables. The following example ensures ProductID values in the table will be unique, and all prices are less then 10.0.

UPDATE @ProductTotals

  SET Revenue = Revenue * 1.15

WHERE ProductID = 62

DELETE FROM @ProductTotals

WHERE ProductID = 60

SELECT TOP 5 *

FROM @ProductTotals

ORDER BY Revenue DESC

You can also declare primary keys. identity columns, and default values.

UPDATE @ProductTotals

  SET Revenue = Revenue * 1.15

WHERE ProductID = 62

DELETE FROM @ProductTotals

WHERE ProductID = 60

SELECT TOP 5 *

FROM @ProductTotals

ORDER BY Revenue DESC

So far it seems that table variables can do anything temporary tables can do within the scope of a stored procedure, batch, or UDF), but there are some drawbacks.

Restrictions

You cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table (SQL Server enforces any UNIQUE or PRIMARY KEY constraints using an index).

Also, SQL Server does not maintain statistics on a table variable, and statistics are used heavily by the query optimizer to determine the best method to execute a query. Neither of these restrictions should be a problem, however, as table variables generally exist for a specific purpose and aren’t used for a wide range of ad-hoc queries.

The table definition of a table variable cannot change after the DECLARE statement. Any ALTER TABLE query attempting to alter a table variable will fail with a syntax error. Along the same lines, you cannot use a table variable with SELECT INTO or INSERT EXEC queries. f you are using a table variable in a join, you will need to alias the table in order to execute the query.

UPDATE @ProductTotals

  SET Revenue = Revenue * 1.15

WHERE ProductID = 62

DELETE FROM @ProductTotals

WHERE ProductID = 60

SELECT TOP 5 *

FROM @ProductTotals

ORDER BY Revenue DESC

You can use a table variable with dynamic SQL, but you must declare the table inside the dynamic SQL itself. The following query will fail with the error “Must declare the variable '@MyTable'.”

DECLARE @MyTable TABLE

(

  ProductID int ,

  Name varchar(10)

)

EXEC sp_executesql N'SELECT * FROM @MyTable'

It’s also important to note how table variables do not participate in transaction rollbacks. Although this can be a performance benefit, it can also catch you off guard if you are not aware of the behavior. To demonstrate, the following query batch will return a count of 77 records even though the INSERT took place inside a transaction with ROLLBACK.

DECLARE @MyTable TABLE

(

  ProductID int ,

  Name varchar(10)

)

EXEC sp_executesql N'SELECT * FROM @MyTable'

Choosing Between Temporary Tables and Table Variables

Now you’ve come to a stored procedure that needs temporary resultset storage. Knowing what we have learned so far, how do you decide on using a table variable or a temporary table?

First, we know there are situations that which demand the use of a temporary table. This in-cludes calling nested stored procedures which use the resultset, certain scenarios using dy-namic SQL, and cases where you need transaction rollback support.

Secondly, the size of the resultset will determine which solution to choose. If the table stores a resultset so large you require indexes to improve query performance, you’ll need to stick to a temporary table. In some borderline cases try some performance benchmark testing to see which approach offers the best performance. If the resultset is small, the table variable is always the optimum choice.

An Example: Split

Table variables are a superior alternative to using temporary tables in many situations. The ability to use a table variable as the return value of a UDF is one of the best uses of table vari-ables. In the following sample, we will address a common need: a function to parse a delimited string into pieces. In other words, given the string “1,5,9” – we will want to return a table with a record for each value: 1, 5, and 9.

The following user-defined function will walk through an incoming string and parse out the individual entries. The UDF insert the en-tries into a table variable and returns the table variable as a result. As an example, calling the UDF with the following SELECT statement:

SELECT * FROM fn_Split('foo,bar,widget', ',')

will return the following result set.

position value

1           foo

2           bar

3          widget

We could use the resultset in another stored procedure or batch as a table to select against or filter with. We will see why the split function can be useful in the next OdeToCode article. For now, here is the source code to fn_Split.

if exists (select * from dbo.sysobjects where id = ob-ject_id(N'[dbo].[fn_Split]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[fn_Split]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE  FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = ' ')

RETURNS @Strings TABLE

(   

  position int IDENTITY PRIMARY KEY,

  value varchar(8000)  

)

AS

BEGIN

DECLARE @index int

SET @index = -1

WHILE (LEN(@text) > 0)

  BEGIN

    SET @index = CHARINDEX(@delimiter , @text) 

    IF (@index = 0) AND (LEN(@text) > 0) 

      BEGIN 

        INSERT INTO @Strings VALUES (@text)

          BREAK 

      END

    IF (@index > 1) 

      BEGIN 

        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  

        SET @text = RIGHT(@text, (LEN(@text) - @index)) 

      END

    ELSE

      SET @text = RIGHT(@text, (LEN(@text) - @index))

    END

  RETURN

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Summary

Next time you find yourself using a temporary table, think of table variables instead. Table variables can offer performance benefits and flexibility when compared to temporary tables, and you can let the server clean up afterwards.

one more usefull link is Click here

Continue Reading →

View without Controller Action in MVC

In this quick post you will learn how a view can be rendered without its native Controller
Action method.

Why we need this?

Let’s look at the image.

In above image, you can see for each view we have matching controller action. Each of
these actions contains a single line of code. In fact, each of these actions contains
exactly the same line of code. And this is completely a needless work. Even imagine
what you will do when you have hundreds or thousands of views. Will you create
hundreds or thousands of controller actions? Off course not, then how can we fix it?

In MVC Framework, controller class includes a method HandleUnknownAction() that
executes whenever we attempt to invoke an action (or when we request a view which
has no matching action method) on a controller that does not exist.

Now we are taking the advantage of the HandleUnknownAction() method to render views
even when a corresponding controller method does not exist.

In above image you can see we don’t have Post5.cshtml, so when I tried to access the
Post5.cshtml view, it pops following error.

Exception Generated

To fix this issue, we can use simple try-catch block and redirect the user on

PageNotFound view, here’s how.

I Hope this will Help you to Learn.

Continue Reading →

What is Model and ViewModel in MVC Pattern?

Model and ViewModel are two things we always hear in MVC. And in this post I am

going to show you the differences between them.

Let’s begin with its common definition.

What is Model or Domain Model?

Actually, the word 'model' has hundreds of meaning in software development, but
here we gone talk about 'model' in MVC design pattern. I would define model as an
object that we use to send information to the database, to perform business
calculations, to render view. In other word, 'model' represent the domain of
the application helps us in saving, creating, updating and deleting records. Usually
we put all our model classes in Model folder.

What is ViewModel?

ViewModel in MVC design pattern is very similar to 'model'. The major difference
between 'Model' and ‘ViewModel’ is that we use ViewModel only in rendering views.
We put all our ViewModel classes in ‘ViewModels’ named folder, we create this folder.

Understand it with example

Let's assume, we want to implement a view page that will have three textboxes for
Username, Password and Re-enter Password. To achieve this we could design a
'Model' as given below:

    public class Login

    {

        public String Username { get; set; }

        public String Password { get; set; }

        public String RePassword { get; set; }

    }

For sake of view this model works fine. But this is actually a wrong approach because
we are trying to overcrowd the database. I can't see any use of 'RePassword'
property in database.

Now, if we take the advantage of ViewModel, we can safeguard the database from
overcrowding with fields. Here’s how, design following ‘Model’ which will be our
Domain Model:-

    //this will represent domain of the application

    public class Login

    {

        public String Username { get; set; }

        public String Password { get; set; }

    }

And then following 'ViewModel':-

    //this will help in rendering great views

    public class LoginViewModel

    {

        public String Username { get; set; }

        public String Password { get; set; }

        public String RePassword { get; set; }

    }

Now, when adding view, pick the ViewModel class to get the strongly-typed benefits.

Now the question is how do we transform the Model or Domain Model from the
‘ViewModel’? Let’s learn it.

Transforming Model from ViewModel

There are various ways to do this. In the form POST action we can create a new
object of type Login model and then assign the properties one by one and leave the
unwanted properties.

[HttpPost]

public ActionResult Login(LoginViewModel viewModel)

{

    //validate the ViewModel

    //transforming Model (Domain Model) which is Login from ViewModel which is
        LoginViewModel

    var login = new Login()

    {

        Username = viewModel.Username,

        Password = viewModel.Password

    };

    //save the login var

    return View();

}

In above code, after validating the ViewModel I’m transforming the Model or Domain
Model. So, by using this way you can stop overcrowding database with
unwanted fields.

I Hope this will help you to understand the use of Model and ViewModel.

Continue Reading →

Delegates - C Sharp

A delegate is a type safe function pointer.That is, they hold reference(Pointer) to a function. 

The signature of the delegate must match the signature of the function, the delegate points to, otherwise you get a compiler error. This is the reason delegates are called as type safe function pointers.

A Delegate is similar to a class. You can create an instance of it, and when you do so, you pass in the function name as a parameter to the delegate constructor, and it is to this function the delegate will point to.

Declaration:
A delegate is declared by using the keyword delegate, otherwise it resembles a method declaration.

delegate int delegateAdd(int x, int y);

Instantiation:

To create a delegate instance, we need to assign a method (which has same signature as delegate) to delegate.

        static int add(int a, int b)

        { 

          return a + b; 

        }

        //create delegate instance

        delegateAdd objAdd = new delegateAdd(Add);

        //short hand for above statement

        delegateAdd objAdd = Add;

Invocation:

     // Invoke delegate to call method

     int result = objAdd.Invoke(3, 6);

     //short hand for above statement

     int result = objAdd(3, 6); 

Invoking a delegate is like as invoking a regular method.

Complete Code is:

class Program

{

        delegate int delegateAdd(int x, int y);

        static int add(int a, int b)

        { return a + b; }

        static void Main(string[] args)

        {

           // Program p = new Program();

           //delegateAdd _del = p.add;

            delegateAdd _del = add;

            int result = _del.Invoke(3, 6);

            Console.Write(result);

            Console.Read();

        }

}

Output will be : 9

Type of Delegate

Delegates are one of two types:

1. Singlecast Delegate
2. Multicast Delegate

Singlecast DelegateA Singlecast delegate is derived from the System.Delegate class. It can contain a reference for one method at a time. In the above example the delegate uses a Singlecast delegate.

Multicast DelegateA Multicast delegate is derived from the System.MulticastDelegate class. It can contain references for multiple methods. For understanding multicast delegates you can consider the real-life example: of a Coffee vending machine.

A Coffee vending machine has three containers for Milk, Coffee, and Tea. If you press the Tea button then there will two containers, Milk and Tea that work together. And if you press the button for Coffee then the Milk and Coffee containers will work. So if you want to call multiple functions then you can use a multicast delegate.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Anonymous

{

    class Class1    {

        public delegate void Vender();

        public static void Milk()

        {

            Console.Write("Milk + ");

        }

        public static void Tea()

        {

            Console.Write("Tea = Tea ");

        }

        public static void Coffee()

        {

            Console.Write("Coffee = Coffee ");

        }

        static void Main()

        {

            Vender vender = new Vender(Milk);

            Console.WriteLine("\t\tPress 1 For Tea\n\t\t Press 2 For Coffee");

            int Opt = Convert.ToInt32(Console.ReadLine());

            switch (Opt)

            {

                case 1:

                    vender += Tea;

                    break;

                case 2:

                    vender += Coffee;

                    break;

                default:

                    Console.WriteLine("Invalid Option !");

                    Environment.Exit(0);

                    break;

            }

            vender();

            Console.ReadKey();

        }

    }

}

In the above program I just created the three functions, Tea, Coffee and Milk. When one is pressed there will be two functions, called Milk and Tea. If the user chooses both then again the two functions, Milk and Coffee are called. Since Milk is common to both situations I just pass Milk in the Delegate parameter. And the delegate understands "+" and "+=" for adding references of functions and "-" and "-=" for removing function references, so depending on the option used I just add a function reference with the same Delegate object vendor. And invoke the delegate vendor.

Remember, multicasting of delegate should have a return type of Void otherwise it will throw a runtime exception.

Anonymous Method In C# 1.0 you can use a delegate and pass a function reference with an initializing delegate with a function name, but C# 2.0 introduced anonymous functions.

Creating an anonymous method as an inline block of code to be passed as a delegate parameter can be done as you see in the following: 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Anonymous

{

    class Class1    {

        public delegate void Del();

        static void Main(string[] args)

        {

            Del obj = delegate() {

                Console.WriteLine( "Class1.fun");

            };

            obj();

        }

    }

}

Use of an anonymous function can reduce the lines of code. So, here in the program above you can see I just passed a block of code for a delegate parameter inside of creating a function anywhere else and passing the function name. The anonymous function uses the delegate keyword while delegate initialization writes a block of code. You can also pass a parameter for an anonymous function as follows:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace Anonymous

{

    class Class1    {

        public delegate void Del( string Mesg);

        static void Main(string[] args)

        {

            Del obj = delegate(string Mesg) {

                Console.WriteLine(Mesg);

            };

            obj("Class1.fun");

        }

    }

}

Note:
You cannot use goto, break and continue statements inside the anonymous method block if the targat is outside an anonymous block.

Difference between Delegate and Event

Continue Reading →