Non Clustered indexes per tabel

In Sql Server 2008 32 Bit addition ,following are the limit for Non Cluster Index and Cluster Index

Clustered indexes per table: 1

None clustered per table: 999

Nested sub queries: 32

Nested trigger levels: 32

Parameters per stored procedure: 2100

Parameters per user-defined function: 210

Best way to display default image if specified image file is not found ?

Many times in grid/repeater or even in image display we have a problem if we don’t find any image related to that ID/Record and to replace “X” sign or image not found message we need to do programming but here is the
Quick solution which saves lot’s of your time and also your server processing time.
Just put following code in your in your image control.


onerror="this.onerror=null;this.src='Noview.jpg'">

How to call javascript code from server side

One can call JavaScript client side function directly from code behind using RegisterStartupScript. If one wants JavaScript to be embedded into codebehind, then make use of RegisterClientScriptBlock:

<script>
function fnShowMessage()
{
       alert(" Invoke Javascript function from Server Side Code Behind ");
 }
 </script>

protected void Button1_Click(object sender, EventArgs e)
{
        ClientScript.RegisterStartupScript (GetType(),"Javascript", "javascript: fnShowMessage(); ",true);
}
protected void Button2_Click(object sender, EventArgs e)
{
       ClientScript.RegisterClientScriptBlock(GetType(), "Javascript",  "<script>alert('Record Added Successfully')</script>");
}

Merge Statement – One Statement for INSERT, UPDATE, DELETE

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

In our example we will consider three main conditions while we merge this two tables.

1.    Delete the records whose marks are more than 250.

2.    Update marks and add 25 to each as internals if records exist.

3.    Insert the records if record does not exists.

Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);

There are two very important points to remember while using MERGE statement.

·         Semicolon is mandatory after the merge statement.

·         When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.