Monday, 24 September 2012

User Defined Functions (UDF) Limitations – Sql Server



UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF cannot do and why Stored Procedure is considered as more flexible then UDFs. Stored Procedure is more flexibility then User Defined Functions (UDF).
  1. UDF has No Access to Structural and Permanent Tables.
    • UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure).

  1. UDF Accepts Lesser Numbers of Input Parameters.
    • UDF can have upto 1023 input parameters; Stored Procedure can have upto 21000 input parameters.

  1. UDF does not Support Error Handling.
    • RAISEERROR or @@ERROR are not allowed in UDFs.

  1. UDF Returns Only One Result Set or Output Parameter.
    • Due to this it can be used in SELECT statement but cannot return multiple result set like Stored Procedure.

  1. UDF cannot Call Stored Procedure.
    • Only access to Extended Stored Procedure.

  1. UDF cannot Execute Dynamic SQL or Temporary Tables.
    • UDF cannot run dynamic SQL which are dynamically built in UDF. Temporary Tables cannot be used in UDF as well.

  1. UDF cannot Return XML.
    • FOR XML is not allowed in UDF.

  1. UDF does not support SET options.
    • SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc.).

    UDF is used for many reasons, the main reason I use it I can do repetitive task in SELECT statement as well as modularizing my frequently used code.

    Stored Procedures vs Functions (Difference between SP & UDF) – Sql Server



    Stored Procedures can contain a single SQL statement or a group of SQL statements with data flow control logic containing IF-ELSE, WHILE loop constructs, TRY-CATCH, transactions, etc.
    SPs are used to return one or many result-sets to its calling application.
    On the other hand Functions or UDFs can contain single or multiple SQL statements depending on its type. A Scalar UDF & Inline UDF can only have a single SELECT statement. And a Multi-Statement UDF can contain a body with multiple SQL statements including SELECTS, IF-ELSE, WHILE loops and DMLs but limited to manipulating table variables only.
    UDFs return a single Scalar value or a Table variable to the calling SELECT statement.

    Following are some common differences between an SP & a UDF:
    Stored Procedures:
    1.    Can be used to read and modify data.
    2.    To run an SP Execute or Exec is used, cannot be used with SELECT statement.
    3.    Cannot JOIN a SP in a SELECT statement.
    4.    Can use Table Variables as well as Temporary Tables inside an SP.
    5.    Can create and use Dynamic SQL.
    6.    Can use transactions inside (BEGIN TRANSACTION, COMMIT, ROLLBACK) an SP.
    7.    Can use used with XML FOR clause.
    8.    Can use a UDF inside a SP in SELECT statement.
    9.    Cannot be used to create constraints while creating a table.
    10. Can execute all kinds of functions, be it deterministic or non-deterministic.
      
    Functions:
    1.    Can only read data, cannot modify the database.
    2.    Can only be used with SELECT statement, JOINS & APPLY (CROSS & OUTER).
    3.    Can JOIN a UDF in a SELECT statement.
    4.    Cannot use a Temporary Table, only Table Variables can be used.
    5.    Cannot use a Dynamic SQL inside a UDF.
    6.    Cannot use transactions inside a UDF.
    7.    Cannot be used with XML FOR clause.
    8.    Cannot execute an SP inside a UDF.
    9.    Can be used to create Constraints while creating a table.
    10. Cannot execute some non-deterministic built-in functions, like GETDATE().

    Thursday, 20 September 2012

    Nullable Types in C#



    With the newest standard for the C# language, there is now support for nullable data types. This small change could be a huge help for those who deal with databases containing fields that are optional. Nullable data types can also be helpful in other situations as well.
    In basic terms, a nullable data type is one that contain the defined data type or the value of null. The ECMA-334 standard for C# provides nullable versions of all the C# value types.

    Defining Nullable Types
    Defining a nullable type is very similar to defining the equivalent non-nullable type. The difference is in the use of the ? type modifier. To define an integer, you normally would do a simple declaration:
    int myInt = 1;

    To make myInt able to store a null value, you would declare it as such:
    int? myNullableInt = 1;

    As you can see, these two variables look as though they are the same. The nullable version, however, is much different. The nullable version is actually a structure that combines a value type with a flag to indicate whether the value is null. Additionally, a nullable type has two publicly readable properties, HasValue and value. HasValue is a bool that is true if there is a value stored; otherwise, it is false if the variable is null. If HasValue is true, you can get the value of the variable. If it is false and you attempt to get the value, an exception will be thrown.
    null is now a keyword for C#. Additionally, it can be assigned to a nullable variable. The following are two valid assignments for a nullable variable:
    double? myDouble = 3.14159;
    double? myOtherDouble = null;

    As you can see, myDouble is assigned a value, but could also be assigned null. In the second statement, myOtherDouble is initialized to contain a null value—something you can't do with a non-nullable type.

    Using a Nullable Type
    A nullable type can be used in the same way that a regular value type can be used. In fact, implicit conversions are built in for converting between a nullable and non-nullable variable of the same type. This means you can assign a standard integer to a nullable integer and vice versa:
    int? nFirst = null;
           int Second = 2;
           nFirst = Second;    // Valid
           nFirst = 123;       // Valid
           Second = nFirst;    // Also valid
           nFirst = null;      // Valid
           Second = nFirst;    // Exception, Second is nonnullable.

    In looking at the above statements, you can see that a nullable and nonnullable variable can exchange values as long as the nullable variable does not contain a null. If it contains a null, an exception is thrown. To help avoid throwing an exception, you can use the nullable's HasValue property:
    if (nFirst.HasValue) Second = nFirst;

    As you can see, if nFirst has a value, the assignment will happen; otherwise, the assignment is skipped.

    Using Operators with Nullable Values: Lifted Operators
    In addition to the automatic conversions between a nullable and non-nullable variable of the same value type, there also are changes with the operators to allow them to work with nullable and non-nullable values. These operators are called lifted operators.
    Consider the following code:
    int ValA = 10;
           int? ValB = 3;
           int? ValC = ValA * ValB;

    What is stored in Val C? The value of 30 would be stored into ValC. The standard operators have been modified so that they "lift" the non-nullable values to being nullable, thus allowing the standard operations to work. Now, consider the following change:
    int ValA = 10;
           int? ValB = null;
           int? ValC = ValA * ValB;

    What would ValC contain this time? ValC would contain null. In the case where either operand is null, the result of a lifted operation also will be null. Even if you were doing addition or subtraction, it would still be null. So, ValA + ValB using the above values would result in null, not 10.
    What if ValC were not a nullable type? What does the following do then?
    int ValA = 10;
           int? ValB = null;
           int ValC = ValA * ValB;    // ValC not nullable

    This code would actually throw an exception. The result of ValA * ValB is null and a null can't be assigned to a non-nullable type. As such, an exception is thrown.

    Friday, 7 September 2012

    Word count example through Regular expression in c#


    using System.Text.RegularExpressions;
     
    public static class Validations
    {
           public static int WordCount(string inputStr)
            {
                int RetVal = 0;

                try
                {
                    // Exclude whitespaces, Tabs and line breaks
                    var RegExp = new Regex(@"[^\s]+");
                    var Matches = RegExp.Matches(inputStr);
                    RetVal = Matches.Count;
                }
                catch (Exception)
                {
                }

                return RetVal;
            }

            public static bool IsDecimalValue(string str)
            {
                if (Regex.IsMatch(str, @"^[0-9]+(\.[0-9]{1,10})?$",
                    RegexOptions.IgnorePatternWhitespace))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            public static bool IsAlphaEntry(string str)
            {
                if (Regex.IsMatch(str, @"[a-zA-Z][a-zA-Z]+",
                    RegexOptions.IgnorePatternWhitespace))
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }

            public static bool IsAlphaNumericEntry(string str)
            {
                if (Regex.IsMatch(str, @"^[0-9a-zA-Z]+$",
                    RegexOptions.IgnorePatternWhitespace))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            public static bool IsNumericEntry(string str)
            {
                if (Regex.IsMatch(str, @"^[0-9]+$",
                    RegexOptions.IgnorePatternWhitespace))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            public static bool IsValidEmailAddress(string str)
            {

                if (Regex.IsMatch(str, @"^[A-Za-z0-9_\-\.]+@(([A-Za-z0-9\-])+\.)+([A-Za-z\-])+$",
                    RegexOptions.IgnorePatternWhitespace))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }       
    }