Difference between DELETE and TRUNCATE in SQL Server


I found many people confused between delete and truncate in SQL Server when there is a need to delete all the records from a table. In this quick post I have tried to compile the difference between them. Have a look…

DELETE (Without Where condition)

TRUNCATE

1.Delete without where condition or any restriction deletes all the rows from a table but one at a time, so it deletes records through a  loop for all the rows. 1.Truncate deallocates data pages from the table, thus deleting all the rows at once. It doesn’t go for any loop and thus faster than delete.
2.Delete is DML (Handles table data) 2.Truncate is DDL (Handles Table)
3.Delete can be rollback. 3.Truncate can’t be rollback using log file, once the transaction is complete.
Explanation: Delete writes data to log file before committing but, Truncate doesn’t. Delete removes one row at a time and deleted row is stored in transaction log but, Truncate deallocates the data pages in a table and stores only this deallocation in the transaction log.
4.On Delete all delete triggers will be fired. 4.No trigger will be fired upon Truncate command.
5.Delete is slower as compared to Truncate. 5.Truncate is faster than Delete
6. Delete (without condition) doesn’t reset the identity column value, if present. 6. Truncate command resets the value to initial value or seed for identity column.
7. Delete command acquires table and/or page and row locks for the whole table. 7.Truncate acquires only table and page locks for the table. Since no row lock are used less memory is required.
8. Delete can leave empty pages in table since as page removal requires a table lock which doesn’t necessarily happen. 8.Truncate removes all pages. No empty pages are left behind in a table.

I hope this will help you …

Please comment if you have any question or, suggestion.

Thanks !!!

SQL Server : Use CTE to get subject wise top N marks


Introduction :

In this post we will see, how one can get top N records category wise. I have made the title of this post as subject wise top N marks secured by students, because I found this is the most asked questions in various forums and blogs for this kind of questions and thus I have decided to show the demonstration using example for the same scenario.

Objective :

The objective of this post is to use Common Table Expression (CTE) to resolve our problem i.e,  SELECT TOP N records category wise . Then we will analyse it line-by-line.

Table Structure :

To demonstrate the example, we need a table with following stucture

Column Name

Data Type

MarksId int
ExamId int
StudentId int
SubjectId int
MarksSecured float

For a quick try, you can run the following script to create a table with the above structure.


CREATE TABLE [dbo].[Marks](
 [MarksId] [int] NOT NULL,
 [ExamId] [int] NULL,
 [StudentId] [int] NULL,
 [SubjectId] [int] NULL,
 [MarksSecured] [float] NULL,
 CONSTRAINT [PK_ExamMarks] PRIMARY KEY CLUSTERED
(
 [MarksId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Sample Data :

Yes, We need some sample data to check whether our query is working or not ! You can manually insert some data to the table but if you are in hurry then you can execute following script.


/*For subject 1*/
INSERT INTO dbo.Marks VALUES(1,1,1,1,80)
INSERT INTO dbo.Marks VALUES(2,1,2,1,70)
INSERT INTO dbo.Marks VALUES(3,1,3,1,90)
INSERT INTO dbo.Marks VALUES(4,1,4,1,82)
INSERT INTO dbo.Marks VALUES(5,1,5,1,60)
/*For subject 2*/
INSERT INTO dbo.Marks VALUES(6,1,1,2,40)
INSERT INTO dbo.Marks VALUES(7,1,2,2,70)
INSERT INTO dbo.Marks VALUES(8,1,3,2,50)
INSERT INTO dbo.Marks VALUES(9,1,4,2,90)
INSERT INTO dbo.Marks VALUES(10,1,5,2,85)
/*For subject 3*/
INSERT INTO dbo.Marks VALUES(11,1,1,3,92)
INSERT INTO dbo.Marks VALUES(12,1,2,3,70)
INSERT INTO dbo.Marks VALUES(13,1,3,3,30)
INSERT INTO dbo.Marks VALUES(14,1,4,3,60)
INSERT INTO dbo.Marks VALUES(15,1,5,3,44)

Demonstration :

Interesting !!!

Our demonstration section consists only a single code/query snippet which is a CTE.


WITH CTETopMarks AS (
 SELECT
 ROW_NUMBER() OVER ( PARTITION BY SubjectId ORDER BY MarksSecured DESC ) AS Sl_No,
 MarksId, StudentId, SubjectId, MarksSecured
 FROM dbo.Marks
)
SELECT SubjectId, StudentId, MarksSecured
FROM CTETopMarks
WHERE Sl_No <= 2

The expected result of this query is top 2 marks secured by students for each subject.

Result :

SubjectId

StudentId

MarksSecured

1

3

90

1

4

82

2

4

90

2

5

85

3

1

92

3

2

70

This is the result which we were anticipating.

Analysis :

Let try to understand the solution script.

To understand it, we need to know

->Common Table Expression(CTE)

->Row_Number()

You can read these in details in MSDN.

Common Table Expression(CTE) :

A CTE consists of an expression name for the CTE, a query defining the CTE and a SELECT statement to run the CTE.

Syntax:

WITH expression_name

AS

( CTE_query_definition )

SELECT <column_list>

FROM expression_name;

ROW_NUMBER() :

We use this to generate row no. for each row based on a ORDER BY clause for each category defined by PARTITION BY.

Syntax: 
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
Let execute only the query definition of the CTE and see the result-

SELECT
 ROW_NUMBER() OVER ( PARTITION BY SubjectId ORDER BY MarksSecured DESC ) AS Sl_No,
 MarksId, StudentId, SubjectId, MarksSecured
 FROM dbo.Marks
Sl_No MarksId StudentId SubjectId MarksSecured

1

3

3

1

90

2

4

4

1

82

3

1

1

1

80

4

2

2

1

70

5

5

5

1

60

1

9

4

2

90

2

10

5

2

85

3

7

2

2

70

4

8

3

2

50

5

6

1

2

40

1

11

1

3

92

2

12

2

3

70

3

14

4

3

60

4

15

5

3

44

5

13

3

3

30

Here, records are partitioned by SubjectId and the records under each partition/SubjectId is sorted by descending order, generating a sequential no i.e, Sl_No.

Now, we only need to filter the result with a condition to show only top 2 marks from each SubjectId. To do this, we have used CTE with a WHERE condition which will filter Sl_No those are less or equal to 2.

Conclusion :

We understood basic syntax of CTE and ROW_NUMBER() in SQL Server. Now we are able to resolve a problem where we need to show N numbers of records for each category. Someone can ask that why there is a field in the table, named ExamId although we are not using it anywhere ?? You can insert more data to the table with different ExamIds and change the CTE accordingly to see result for different examinations.

I hope this article will be useful to you.

Thanks for reading.

Please put your valuable comments and suggestions.

Android : Using CheckBox with Example


Introduction:

In this post we will learn to use checkboxes in Android Application.

What is a CheckBox ?

Checkboxes are the GUI elements which provide facility to select multiple options (from zero to all) from an available list of options. It is very useful while developing any kind of application and Android is not an exception. A CheckBox can have two states i.e, either checked or Unchecked . We can make our logic according to these two states of a checkbox. In general, it can be said that it is not much different from any HTML input(<input type=”checkbox” name=”name1″ value=”value1″ />), ASP.NET checkbox(<asp:CheckBox ID=”CheckBox1″ runat=”server”>) etc. with very few differences.

Pre-requisites :

The reader of this post doesn’t need to have a depth knowledge on Android apps development, in fact this is for beginners who have just started developing android apps and want to know the basics. This post may not help you in real-world project development but, is a short demonstration of CheckBox control in Android.

If you are a beginner and want to know the whole thing right from the start like installing Android SDK and creating your first application etc then you may check these post..

Installing Android SDK on windows – Complete steps

Creating ‘HelloWorld’ application in Android with Eclipse Indigo

Installing [dot]apk file on Emulator in Windows

Simple example of Intent

Using Button & Click-Event with example

CheckBox in Android :

To demonstrate a very simple example using checkbox,  I have dragged a TextView and two CheckBox to my main.xml and did the basic setting-up kind of things like id,text,textColor,background,width,height etc as per my need.

Now create a OnClickListener which will later listen to our on-click events on the two checkboxes. Then put a very simple logic in the onClick method to show text of the checked checkbox(s) in the TextView and let reset the text of the TextView if none of these two checkboxes are checked.

Code :

main.xml


<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
 android:layout_width="fill_parent"
 android:layout_height="fill_parent"
 android:orientation="vertical" >

<TextView
 android:id="@+id/tvDetails"
 android:layout_width="fill_parent"
 android:layout_height="wrap_content"
 android:layout_weight="0.17"
 android:textSize="22dp"
 android:background="@android:color/white"
 android:textColor="@android:color/black" />

<CheckBox
 android:id="@+id/cbSuvendu"
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:text="@string/suvendu" />

<CheckBox
 android:id="@+id/cbWordPress"
 android:layout_width="match_parent"
 android:layout_height="wrap_content"
 android:text="@string/wordpress" />

</LinearLayout>

strings.xml


<?xml version="1.0" encoding="utf-8"?>
 <resources>

<string name="hello">Hello World, CheckBoxTutorialActivity!</string>
 <string name="app_name">First CheckBox Tutorial</string>
 <string name="suvendu">www.suvendugiri.com</string>
 <string name="wordpress">www.wordpress.com</string>

</resources>

Activity.java


package com.suvendu.tutorial.cb;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.CheckBox;
import android.widget.TextView;

public class CheckBoxTutorialActivity extends Activity {
    TextView tv;
    CheckBox cbS;
    CheckBox cbW;
    OnClickListener checkBoxListener;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        cbS=(CheckBox)findViewById(R.id.cbSuvendu);
        cbW=(CheckBox)findViewById(R.id.cbWordPress);
        checkBoxListener =new OnClickListener() {

            @Override
            public void onClick(View v) {
                tv=(TextView)findViewById(R.id.tvDetails);
                tv.setText("I Like ");
     
                if(cbS.isChecked()) {
                    tv.setText(tv.getText().toString()+" "+ cbS.getText().toString());
                }

                if(cbW.isChecked()) {
                    tv.setText(tv.getText().toString()+ " "+cbW.getText().toString());
                }

                if(!cbS.isChecked()&amp;&amp;!cbW.isChecked()) {
                    tv.setText("");
                }
            }
        };

        cbS.setOnClickListener(checkBoxListener);
        cbW.setOnClickListener(checkBoxListener);
    }
}

Download :

You can download the full project here. Download

Or, you can just download the apk here.Download

Conclusion :

Final words- Just download the project and run it using your emulator and if you have any doubts or suggestions, please place them in the comments. In the next post we will see the same example using radio buttons.

Visual Studio Tips: Shortcut for Collapse/Expand code blocks


While developing windows/web – application using Visual Studio, several times we need to collapse/expand the code blocks/regions for clear reading and easy to manage the whole page of code. We do it by clicking the +/- sign on the left of the start of code block. But, when there is a good no. of code blocks/sections/regions then clicking each +/- sign is difficult. If the code blocks are large in size i.e, contain many lines of code then practically it is again a hard task to manage the code.

This difficulty can be avoided if we use following shortcuts –

1.  CTRL + M + M       —–>  Collapse/Expand current section

2.  CTRL + M + O       —–>   Collapse all regions

3.  CTRL + M + L       —–>   Expand all regions

 

You may also like-

Shortcut for Bookmark in Visual Studio

An alternate to CASE WHEN of SQL Server for few scenarios


Purpose of this quick post is to present an alternative way to CASE WHEN of SQL Server.

To execute the demonstration, take a look at the following example.


SELECT CustomerName,Address,Mobile FROM Customers ORDER BY CustomerName

This SQL query will retrieve the list of Customers from Customer table. Let Address and Mobile are the fields which allow NULL. It means the result of the query may return NULL for these two fields. Now we will use the result set returned by the above query to bind a Datalist or a Gridview.Let consider for Datalist, then we will use following code on .aspx page to bind these fields


Address : <%#DataBinder.Eval(Container.DataItem, "Address")%>

Mobile   :<%#DataBinder.Eval(Container.DataItem, "Mobile")%>

But, here problem is that when there is no data against one/both of these fields then on browser it will show nothing for the eval data. To avoid this we generally re-write the query using CASE WHEN as-


SELECT CustomerName,CASE WHEN Address IS NULL THEN '--Not Available--' ELSE  Address END AS  Address,CASE WHEN Mobile IS NULL THEN '--Not Available--' ELSE Mobile END AS Mobile FROM Customers ORDER BY CustomerName

The above query ensures that there is no blank space for the above said two field in the browser.

Now, for some scenario where I don’t want to set it from the server and I want an alternative then I may use Javascript to achieve this. Here the query will be same as the first query but change required on the .aspx code.

1.Declare a small Javascript method


function ReplaceEmptyFields(orig, repl) {

//orig : Origional string

//repl: String to be replaced with
 if (orig == "") {
 document.write(repl);
 }
 else {
 document.write(orig);
 }
 }

2.Call the above Javascript in place of Databinder as below

Address : <script language="javascript">ReplaceEmptyFields('<%#DataBinder.Eval(Container.DataItem, "Address")%>', "--Not Available--")</script>
Mobile : <script language="javascript">ReplaceEmptyFields('<%#DataBinder.Eval(Container.DataItem, "Mobile")%>', "--Not Available--")</script>

This will provide the same result as previous example using CASE WHEN. Few of the advantages of using this includes html formatting and style can be applied to the result. For example, if I’ll change the above code to show ‘–Not Available–‘ in Gray color so that it will be more easy to read then the code on .aspx page will be like-


Address : <script language="javascript">ReplaceEmptyFields('<%#DataBinder.Eval(Container.DataItem, "Address")%>', "<font color='gray'>--Not Available--</font>")</script>
Mobile : <script language="javascript">ReplaceEmptyFields('<%#DataBinder.Eval(Container.DataItem, "Mobile")%>', "<font color='gray'>--Not Available--</font>")</script>

This post is based on my experience and experiments, so if you find something missing or, if you have a better idea then please share in  the comments.