COMPUTED COLUMNS IN SQL SERVER

Lots of time even simple concepts which we think is easy and whole world knows,many times very few knows about them.One of them is the computed columns in sequel server.Many people thinks that as soon as the computed columns are created,the column is physically come into existence and now the data is stored in that column as usual.In fact its not true;

Essentially there are two kinds of computed columns

1.Virtual column: where the data is not physically stored in the table, values are calculated each time it is referenced in a query

2.Persisted: Once you mark column as persisted, it is computed right away and stored in the data table.and hence it occupies the database space.

(we use the PERSISTED keyword for creating persisted computed columns)

Example:

ALTER TABLE dbo.TblEmployee ADD

FullName_P AS (FirstName+' '+LastName) PERSISTED

Another important thing we should keep in mind is we cant create indexes on virtual computed columns,whereas can be in persisted provided computed definition is deterministic.

Example: we wouldn't be permitted to index columns containing a call to the GETDATE() function.

Xml parsing in MS SQL Server

OpenXml:

1.The OPENXML function provides a rowset view over an XML document.

2.OPENXML allows the data in XML document to be treated just like the columns and rows of your database table i.e., xml tags as columns and the value as rows.

Advantages:

Data can be inserted / updated very quickly and efficiently without multiple trips to the database.

Example:

If 100 records is to inserted/updated, then the traditional SQL method is using 100 insert/update methods,which will result in degradation in performance.Using XML, these 100 trips can be reduced to 1 trip. This increases the performance of your application.

Basic Syntax

OPENXML ( i_doc int [in], row_pattern nvarchar[in], [flags byte[in]] )

[WITH(SchemaDeclaration|TableName) tblalias]

Arguments
i_doc is the document handle of the internal representation of an XML document.
This handle is obtained by calling the system stored procedure sp_xml_preparedocument(will be discussed)
row_pattern is the XPath query used to identify the nodes to be processed as rows.
flags indicates the mapping between the XML data and the relational rowset. (optional parameter)

0 - Defaults to attribute-centric mapping.
1 - Attribute-centric mapping. (Combined with XML_ELEMENTS)
2 - Element-centric mapping. (Combined with XML_ATTRIBUTES)
3 - Combined with XML_ATTRIBUTES or XML_ELEMENTS

SchemaDeclaration is the schema definition of the form:

ColName ColType [ColPattern | MetaProperty][, ColName ColType [ColPattern | MetaProperty]...]

Eg: WITH (EMPLOYEENAME VARCHAR(30), EMPLOYEESALARY INTEGER)

TableName is the table name that can be given, instead of Schema Declaration, if a table exists.

The WITH clause provides a table format using either SchemaDeclaration or specifying an existingTableName.

System Stored Procedures for OpenXML

SQL Server provides system stored procedures that are used in conjunction with OPENXML:

• sp_xml_preparedocument
• sp_xml_removedocument

---->To write queries using OPENXML, you must first create an internal representation of the XML document by calling sp_xml_preparedocument

syntax: sp_xml_preparedocument hdoc OUTPUT,@xmlText

---->Removes the internal representation of the XML document specified by the document handle usingsp_xml_removedocument

syntax: sp_xml_removedocument hdoc

Examples

CREATE PROCEDURE dbo.TestOpenXML
(  @strXML VARCHAR(2000)
)
AS
DECLARE @XMLDocPointer INT
EXEC sp_xml_preparedocument @XMLDocPointer OUTPUT, @strXML

BEGIN TRANSACTION

INSERT INTO Employees(EmployeeName, EmployeeSalary, DeptID)
SELECT NAME, SALARY, DEPTID
FROM OPENXML(@XMLDocPointer,'/RECORD/EMPLOYEE',2)
WITH (NAME VARCHAR(30) ‘@NAME’, SALARY INT ‘@SALARY’, DEPTID INT '@DEPTID')
COMMIT

EXEC sp_xml_removedocument @XMLDocPointer
RETURN

THank YOu VEry MUch, hope you enjoyed. if have any query all are always welcome,don't hesitate to leave a comment.Good Day.

Generics in Dotnet

Generic programming is a style of computer programming in which algorithms are written in terms of to-be-specified-later types that are then instantiated when needed for specific types provided as parameters.

Generic programming is about generalizing software components so that they can be easily reused in a wide variety of situations.

Generics in dotnet

In dot net 1.1 a concept of collections came..

But the problem behind this is they store objects and since everything derives from the base classObject, every type can be put into the collection.There is, therefore, effectively no type checking at all

Worse, each time you take an object out of a collection you must cast it to the correct type, which incurs a performance hit, and makes for ugly code (and if you mis-cast, throws an exception). Further, if you add a value type (e.g., an integer) to the collection, the integer isimplicitly boxed (another performance penalty), and explicitly unboxed when you take it out of the collection (yet another performance penalty and more casting).

Generics To The Rescue

• Generics are the most powerful feature of C# 2.0
• Generics allow you to define type-safe classes (or methods/interfaces/strucutures or delegates)without compromising type safety, performance, or productivity.
• Generics permit classes, structs, interfaces delegates, and methods to be parameterized by the types of data they store and manipulate.

Examples

public class Stack
{
object[] items;
int count;
public void Push(object item) {...}
public object Pop() {...}
}

Here we are using an object array to store the Data...

Stack stack = new Stack();
stack.Push(new Customer());

Customer c = (Customer)stack.Pop();

stack.push(1);

stack.push("My name is Sukesh");

int i=(int)stack.pop();//we know it will give error but compiler wont have to wait still runtime :(

Now just imagine u can push any value in the stack from integer,float to a complex class object.Storing object provides a little bit flexibility but with drawbacks like

No type safety and performance degradation as we discussed earlier.

so how the generic class will be written?lets move towards it.

public class Stack
{
T[] items;
int count;
public void Push(T item) {...}
public T Pop() {...}
}

When the generic class Stack is used, the actual type to substitute for T is specified. In the following example, int is given as the type argument for T:

Stack<int> stack = new Stack<int>();
stack.Push(3);
int x = stack.Pop();

stack.push("Sukesh");//Type mismatch error-->The Program Not even compile.

It was a breif introduction to generics.

we will discuss in depth about generics (adding constraints,Generics with multiple parameters and lots of other) in later blogs, till then bye bye.

Code Optimization Techniques for .net applications

Compiler optimization

• Technically It is the process of tuning the output of a compiler to minimize or maximize some attribute of an executable computer program.
• In simple terms it means improving the performance of your application.

Application is nothing but a program which is written to perform some specified task.

Why Optimization is required

But is optimization really necessary?ask yourself. Consider you are doing some important work and suddenly u get a busy cursor...like u are watching movie on youtube and an interesting part is there but suddenly buffering starts.

Some Tips for optimizing application

• Avoid unncessary variablesLook at the following example
  

  r=Radius of circle
  p=piare
  a=p*r*r;
  

  is it right, no because u know value of 'p' will always be 3.14,So don't create variable for this type of conditions,Just say area=3.14*r*r;
• Avoid unnecessary cpu allocation

  read a
  final_value=a*100*12*56;
  write final_value
  

  now all of us know 100*12*56 is equal to 67200 so why to put work for cpu, which we can do.just write final_value=a*67200;
• Avoid conditional loops inside Iteration loops like
  

  for(int x=1;x<7;x++)
  {
       //k and total are treated as random variables
       if(k>99)
       {
         total+=x; 
       }
       else
       {
        total-=x;
       }
  }
  

  replace this with
  

  if(k>99)
  {     
       for(int x=1;x<7;x++)     
       { 
         total+=x; 
       }
  }
  else
  {
       for(int x=1;x<7;x++)
       { 
         total-=x; 
       }
  }
  

• String Or String BuilderTake a right decision what to use string or StringBuilder.
• We want to store some string data which is going to be manipulated(like a new string will be appended or may be removed from the existing one) many times(considerable more than twice), in this situation StringBuilder will be better as string is immutable.
• But for small operations, means we want to store some string data which will be hardly one or two times manipulated string will be better choice.
• Comparing Non-Case-Sensitive StringsIn an application sometimes it is necessary to compare two string variables, ignoring the cases.
• Traditional style : string1.ToLower()==string2.ToLower()
• Better1 : string.Compare(string1,string2)==0
• Use of collection classes instead of arrays,again use of generic collection classes.
• Use String.Empty when want to compare with blank stringex:-replace if(s=="") with if(s==string.Empty)
• Use structures instead of classes if type is going to be have less functionality.
• Avoid creating global variables.
• Avoid unnecessary try catch blocks(try to avoid exceptions by compile time logic).
• 11.Avoid Division Operations

(In the subsequent posts,we will back with some techniques.
Responses are always appreciated.Feel free to put your suggestion for optimations.)
:)