SQL Server - Difference between TRUNCATE and DELETE

on July 12, 2010 1 comments

Truncate and Delete both are used to delete data from the table. Both these commands will only delete the data of the specified table; they cannot remove the whole table. But they both differ from each other in many aspects like syntax, performance, resource uses, etc.

Lets take a look at syntax of these commands,

The Syntax for TRUNCATE statement is:
TRUNCATE TABLE [ { database_name.[ schema_name ]. | schema_name . } ] table_name

The Syntax for DELETE statement is:
DELETE FROM TABLE_NAME[ { database_name.[ schema_name ]. | schema_name . } ] table_name

Database_name: Is the name of the database in which the table exists. This is optional. If it is not included, the current database context is assumed.
Schema_name: Is the name of the schema in which the table exists. This is optional. If it is not included, the current database context is assumed.
Table_name : Is the name of the table to truncate or from which all rows are removed.

The Differences between Truncate and Delete

Both the statements are similar, but there are many differences that exist between them. Those similarities and differences are explained below:

1. Removes the data not the structure:
Both commands only removes rows from a table, but the table structure and its columns, constraints, indexes, and remains same. To remove the table definition in addition to its data, use the DROP TABLE statement.

2. Conditional based deletion of data:
Conditional based deletion of data means we can delete the rows of a table based on some condition specified in WHERE clause as show below,

DELETE FROM authors Where AuthorId IN (1,2,3)
  • TRUNCATE - In case of the TRUNCATE command, we can't perform the conditional based deletion because there is no WHERE clause allowed with this command.
  • DELETE - The DELETE command provides the functionality of conditional based deletion of data from the table using the WHERE clause.
3. Delete and Truncate both are logged operations:
Most of the articles from internet says: "delete is a logged operation and truncate is not a logged operation", which means when we run the delete command it logs (records) the information about the deleted rows and when we run the truncate command it doesn't log any data. But this is not true; truncate is also a logged operation but in a different way. It uses fewer system and transaction log resources than delete. The TRUNCATE command uses minimum logging resources, which is why it is faster than delete. So both delete and truncate are logged operations, but they work differently as shown below.
  • DELETE is a logged operation on a per row basis: The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. So, in case if you are deleting a huge number of records then it can cause your transaction log to grow. This means the deletion of a huge number of records will use more server resources as it logs each and every row that is deleted. That is why your transaction log will grow very rapidly. Since the delete statement records each deleted row it is also slow. Some people ask what is the use of logging each deleted row??? The answer is when you run your databases in full recovery mode, detailed logging is necessary for SQL Server to be able to recover your database to the most recent state.
  • TRUNCATE logs the deallocation of the data pages in which the data exists: TRUNCATE is faster than DELETE due to the way TRUNCATE "removes" rows from the table. It won't log the deletion of each row; instead it logs the deallocation of the data pages of the table. The TRUNCATE statement removes the data by deallocating the data pages used to store the table data and records only the page deallocation in the transaction log. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast. It is a common mistake to think that TRUNCATE is not logged. This is wrong. The deallocation of the data pages is recorded in the log file. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.
4. Behavior of Delete and Truncate for identity columns:
Both the TRUNCATE and DELETE commands behave differently against Identity columns. When we use truncate it will reset the counter used by an identity column for new rows to the seed value defined for the column. But in the case of DELETE it will not reset the counter of your identity column. Rather it maintains the same counter for new rows. In both the cases, if no seed was defined the default value 1 is used. As TRUNCATE resets the identity column counter, in the case where you want to retain the identity counter, use DELETE instead of TRUNCATE. T-SQL is providing you the two ways you can use as needed. In the case where you want all the data from the table deleted and the counter will restarting from 1, then truncate can help you. If you want to delete all the records but don't want to reset you counter, then delete is to be used.

5. TRUNCATE is a DDL command whereas DELETE is a DML command:
TRUNCATE is a DDL (data definition language) operation and DELETE is a DML (data manipulation language) operation. According to SQL SERVER it's true. But why it is so, why is TRUNCATE is DDL command and DELETE is DML command? Let's look at this;
When we run the TRUNCATE command it puts a "Schema modification (Sch-M)" lock on the table. What is "schema modification (Sch-M)"?
The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released. Now you'll ask how it blocks any modification to the table when in the case of TRUNCATE we are performing modifications because we are deleting data? But deleting the data is the one side of coin only. What we see with the internal workings of truncate is (as you read above), that it doesn't remove the data. Rather it actually deallocates the data pages. Because TRUNCATE doesn't perform any data modification in the table that is why the DELETE TRIGGER is not called. I think we are not modifying the data of the table, BUT as you know TRUNCATE resets the Identity counter of the column in the table, which means the TRUNCATE is modifying the table definition or structure, which comes under the DDL operations. Also when you are truncating a table, you can't modify or add any data to the table. So, to become a DDL operation you have to fulfill some of the conditions written below:
  • Modifying a table structure or definition comes under DDL operations, and
  • When you are modifying the table structure, you can't access the table to do any data modification.
Since TRUNCATE is doing all the activities above, that proves that TRUNCATE is a DDL operation.
Now we move to the DELETE command. In case of the DELETE command I am not sure which lock is implemented, but as we know and you can read above that DELETE command deletes the rows one by one. It is modifying the data by deleting it from the table, and because DELETE performs data modifications that is why the DELETE TRIGGER is called. The DELETE command does not modify the table structure in any manner, such as like how TRUNCATE modifies the identity column by resetting its value.
To become a DML operation you have to fulfill some of the conditions written below:
  • Modifying the table data.
  • When you are modifying the table data in the mean time you can't perform any table structure modification on the table.
Here the DELETE command is modifying the data of the table and also when delete statement is running you can't modify the table structure. So we can say that DELETE is a DML operation.

6. Behavior of Truncate and Delete for Triggers:
Both TRUNCATE and DELETE behave differently for Triggers. As you all know triggers fire whenever any data modification happens in the table. Let's take a look at both one by one:
  • TRUNCATE - When we run the TRUNCATE command to delete all the rows of a table it actually doesn't remove any row rather it deallocates the data pages. So in the case of the TRUNCATE command, triggers will not be fired because here no modification takes place. As we know that TRUNCATE is a DDL command, and DDL commands doesn't modify your data; instead they modify your table structure and definition.
  • DELETE - In case of DELETE the DELETE trigger will be fired if present and also if the INSTEAD OF and AFTER triggers for DELETE TRIGGER is present then they also will be fired. As we know that delete command is a DML command and it deletes the data on row-by-row basis. So that means delete is modifying the data by deleting it from the table. As we know delete is a DML command and trigger will be fired whenever any DML operation on the table takes place.
7. Where we can use these commands:
There are some restrictions on the use of both of these commands as specified below:
For Delete
  • The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.
For Truncate
You cannot use TRUNCATE TABLE on tables that:
  • Are referenced by a FOREIGN KEY constraint.
  • Participate in an indexed view.
  • Are published using transactional replication or merge replication.
8.  Permissions of performing TRUNCATE or DELETE operation:
For using both the statements you need some permissions on the server, which decides whether you can perform the Delete or Truncate action on the table or not. To truncate a table, you need at least ALTER permissions on the table (which is granted by default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles). To delete rows using the delete statement, you only need the DELETE permission.

SQL Server - Keyboard shortcuts

on July 05, 2010 0 comments

Below are the useful keyboard shortcuts for SSMS(SQL Server Management Studio). These shortcuts are reordered and grouped in such a way as to make it easier to find what you're looking for.

   Toggle the full-screen display - Shift+Alt+Enter
   Exit - Alt+F4
Query Window:
    Open with current connection - Ctrl+Q
    Save current - Ctrl+S
    Save All - Ctrl+Shift+S
    Close - Ctrl+F4
    Print - Ctrl+P
    Show or hide the results pane - Ctrl+R
    Toggle query & results pane - F6
    Move to previous active window - Ctrl+Shift+F6
    Move to the next active window - Ctrl+F6
    Open SQL Server Profiler - Ctrl+Alt+P
    Open Object Explorer if closed - F8
    Display Go To Line dialog box - Ctrl+G
Line indent:
    Increase - TAB
    Decrease - Shift+TAB
Make selected text:
    Upper case - Ctrl+Shift+U
    Lower case - Ctrl+Shift+L
    A comment - Ctrl+K, Ctrl+C
    Uncommented - Ctrl+K, Ctrl + U
Specify values:
    for template parameters Ctrl+Shift+M
Current Query,selected or all:
    Execute - F5 (or Ctrl+E)
    Parse - Ctrl+F5
    Display estimated execution plan - Ctrl+L
    Cancel the executing query - Alt+Break
Output results:
    in a grid - Ctrl+D
    in text format - Ctrl+T
    to a file - Ctrl+Shift+F
Scroll text:
     up one line - Ctrl+Up-arrow
     down one line - Ctrl+Down-arrow
     the last editing action Ctrl+Z
     the last undone edit - Ctrl+Y
Insert a blank line:
    above the cursor - Ctrl+Enter
     below the cursor - Ctrl+Shift+Enter
    all text in the window - Ctrl+Shift+DEL
    the word to the right of the cursor - Ctrl+Delete
    the word to the left of the cursor - Ctrl+BACKSPACE
Move the cursor:
    to the beginning of the line - Home
    to the end of the line - End
    the beginning of the document - Ctrl+Home
    to the end of the document - Ctrl+End
    Move the cursor up one screen - Page Up
    Move the cursor down one screen - Page Down
    one word to the right - Ctrl+Right-arrow
    one word to the left - Ctrl+Left-arrow
Select text from the cursor:
    to the beginning of the document - Ctrl+Shift+Home
    to the end of the document - Ctrl+Shift+End
    to the start of the current line - Shift+Home
    to the end of the current line - Shift+End
   down line by line - Shift+Down-arrow
   up line by line - Shift+Up-arrow
    the entire current document - Ctrl+A
    the word containing the cursor, or the closest word - Ctrl+W
    the current location in the editor,back to the previous location in the editor - Ctrl+=
Extend selection:
    to the top of the current window - Ctrl+Shift+Page Up
    one word to the right - Ctrl+Shift+Right-arrow
    one word to the left - Ctrl+Shift+Left-arrow
    one page up - Shift+Page Up
    one page down - Shift+Page Down
Move the cursor,extending the selection:
    to the right one word - Ctrl+Shift+Alt+Rightarrow
    to the left one word - Ctrl+Shift+Alt+Leftarrow
    to the last line in view Ctrl+Shift+Page Down
    up one line - Shift+Alt+Up-arrow
    down one line - Shift+Alt+Down-arrow
    Block (column-wise) - left Alt+Shift +Left-arrow
    Block right - Alt+Shift +Right-arrow
    Block up - Alt +Shift +Up-arrow
    Block down - Alt+Shift +Down-arrow
Search for text:
    Display the Find dialog box - Ctrl+F
    Display the Replace dialog box - Ctrl+H
    the next occurrence of the previous search text - F3
    the previous occurrence of the search text - Shift+F3
    the next occurrence of the currently selected text - Ctrl+F3
    the previous occurrence of the currently selected text - Ctrl+Shift+F3
SQL Prompt:
    Show suggestions box - Ctrl + Space
    Switch to/from column picker - Ctrl + F, Ctrl + G
    Move up/down the suggestions box filters - Ctrl + H, Ctrl + I
    Apply format - Ctrl + K, Y
    Script object as - ALTER F12
    Refresh Suggestions - Ctrl + Shift + D
    List members - Ctrl +Space or Ctrl+J
    Complete word - Alt + Right Arrow
    Parameter Information - Ctrl + Shift + Space
    Refresh Local Cache Ctrl + Shift + R
    Jump between syntax pairs - CTRL+]
    go to Next one - Ctrl+K, Ctrl+N,
    go to previous one - Ctrl+K, Ctrl+P
    Toggle (set or remove) - Ctrl+K, Ctrl+K
    Clear Bookmarks - Ctrl+K, Ctrl+H

SQL SERVER – Create and read XML File Using T-SQL

on July 01, 2010 0 comments
The below example shows you how to create XML using SQL Server.

Creating XML:

T-SQL Script to generate above XML:

SELECT ( SELECT 'White' AS Color1,
'Blue' AS Color2,
'Black' AS Color3,
'Light' AS 'Color4/@Special',
'Green' AS Color4,
'Red' AS Color5
XML PATH('Colors'),
SELECT 'Apple' AS Fruits1,
'Pineapple' AS Fruits2,
'Grapes' AS Fruits3,
'Melon' AS Fruits4
XML PATH('Fruits'),

Every XML has two elements.

1) Attributes 
2) Value


In my above example color4 has attribute along with value. Make sure to specify attribute before the value is defined otherwise it will give error.

Reading XML:

Here we will see how we can read the XML file using the SELECT statement.


SET @MyXML = '<SampleXML>
<Color4 Special="Light">Green</Color4>

a.b.value(‘Colors[1]/Color1[1]‘,‘varchar(10)’) AS Color1,
a.b.value(‘Colors[1]/Color2[1]‘,‘varchar(10)’) AS Color2,
a.b.value(‘Colors[1]/Color3[1]‘,‘varchar(10)’) AS Color3,
a.b.value(‘Colors[1]/Color4[1]/@Special’,‘varchar(10)’)+‘ ’+
a.b.value(‘Colors[1]/Color4[1]‘,‘varchar(10)’) AS Color4,
a.b.value(‘Colors[1]/Color5[1]‘,‘varchar(10)’) AS Color5,
a.b.value(‘Fruits[1]/Fruits1[1]‘,‘varchar(10)’) AS Fruits1,
a.b.value(‘Fruits[1]/Fruits2[1]‘,‘varchar(10)’) AS Fruits2,
a.b.value(‘Fruits[1]/Fruits3[1]‘,‘varchar(10)’) AS Fruits3,
a.b.value(‘Fruits[1]/Fruits4[1]‘,‘varchar(10)’) AS Fruits4
FROM @MyXML.nodes(‘SampleXML’) a(b


SQL SERVER – Find Currently Running Query – T-SQL

This is the script which I always had in my archive. Following script find out which are the queries running currently on your server.

SELECT sqltext.TEXT,
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 
While running above query if you find any query which is running for long time it can be killed using following command.

KILL [session_id]

SQL SERVER – 2005 – Find Nth Highest Record from Database Table – Using Ranking Function ROW_NUMBER

Below query shows how to find Nth highest record from database table.

USE AdventureWorks
row_number() OVER (
ORDER BY e1.Rate DESC) AS _Rank
HumanResources.EmployeePayHistory AS e1
) AS t
t._Rank = 4


SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP

Following script will create common separate values (CSV) or common separate list from tables. convert list to table. Following script is written for SQL SERVER 2005. It will also work well with very big TEXT field. 
If you want to use this on SQL SERVER 2000 replace VARCHAR(MAX) with VARCHAR(8000) or any other varchar limit. It will work with INT as well as VARCHAR.

There are three ways to do this. 1) Using COALESCE 2) Using SELECT Smartly 3) Using CURSOR.
The table is example is:

TableName: NumberTable


Output : first,second,third,fourth,fifth

Option 1: This is the smartest way.

SELECT @listStr = COALESCE(@listStr+',' , '') + NumberCols
FROM NumberTable
SELECT @listStr

Please make a note that COALESCE returns the first NOT NULL value from the argument list we pass.

Option 2: This is the smart but not the best way; though I have seen similar code many times.

SET @listStr = ''
SELECT @listStr = @listStr + NumberCols + ','
FROM NumberTable
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)

I sometime use ISNULL(NumberCols,’NullValue’) to convert NULL values to other desired value. 

Option 3: Cursor are not the best way, please use either of above options.
Above script can be converted to User Defined Function (UDF) or Storped Procedure (SP).

SQL SERVER – Random Number Generator Script

Random Number Generator:
There are many methods to generate random number in SQL Server.

Method 1 : Generate Random Numbers (Int) between Rang

---- Create the variables for the random number generation
---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

Method 2 : Generate Random Float Numbers

+ (
DATEPART(ss, GETDATE()) * 1000 )

Method 3 : Random Numbers Quick Scripts

---- random float from 0 up to 20 - [0, 20)
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
----random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())

Method 4 : Random Numbers (Float, Int) Tables Based with Time

DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
@cnt = @cnt + 1
+ (
DATEPART(ss, GETDATE()) * 1000 )
randnum, COUNT(*)
GROUP BY randnum

Method 5 : Random number on a per row basis

---- The distribution is pretty good however there are the occasional peaks.
---- If you want to change the range of values just change the 1000 to the maximum value you want.
---- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber


SQL SERVER 2005 – Search Stored Procedure Text

How to find if particular table is being used in the stored procedure?
How to search in stored procedures?
How can I do dependency check for objects in stored procedure without using sp_depends?

The below script explains how to search all procedures in SQL Server 2005.

USE AdventureWorks
--Searching for Empoloyee table
FROM sys.procedures

--Searching for Empoloyee table and RateChangeDate column together
FROM sys.procedures




Adding meta tags to blogs - SEO

If you don't know what meta tags are, and what is the importance of adding meta tags, here is the detailed description on how to add them to your blogger(blogspot) blogs or any website.

Do you know adding meta tags is so important if you want to get more traffic from search engines like Google, Yahoo, MSN, etc, especially the meta description is the most important one.

If you don't know what meta tags are,Meta tags are the tags which describes your site and tells the search engines what your site is all about.Adding meta tags is an important factor in SEO.(Search Engine Optimization).Meta tags allows search engines to index your web pages more accurately. In other words,Meta tags communicate with the search engines and tells more information about your site and make it index correctly and accurately.

We have to add two meta tags to the head section of the template.One is the Meta description which describes your site and another one is Meta Keywords which tells about your site keywords(what your site is all about)

Below are the steps to add meta tags to your blog,

1.Sign in to your blogger dashboard>click the 'layout' button[see the screenshot below]

2.Click on the 'Edit html' tab
Here is the code you have to add,

<meta content='DESCRIPTION HERE' name='description'/>
<meta content='KEYWORDS HERE' name='keywords'/>
<meta content='AUTHOR NAME HERE' name='author'/>

DESCRIPTION HERE:Write your blog description
KEYWORDS:Write the keywords of your blog
AUTHOR NAME:Write the author's name(Your name)

3.Add the below code just after this [Look at the below screenshot]

<b:include data='blog' name='all-head-content'/>

Don't forget to add description and keywords and save the changes.

Several people were filling with just the keywords in the description.DON'T DO THAT. Google and other search engines will ban your site from the search engines list if you did so. And also,don't repeat the keyword more than 3 times.

That's it!You have sucessfully added the metatags to your blogger(blogpsot) blog. If you want to check whether you added the meta tags correctly or not, just type 'meta tags analyzer tool' in the google and you will find so many sites which can check whether you have added the tags correctly or not.

Related articles:
Adding meta tags to blogger(blogspot) blogs,websites-SEO
How to add meta tags to blogger, important SEO step
Add Meta Tags to Blogger for Better SEO
Meta tags for Blogger ~ Blogger Tips and Tricks

Adding a Custom Search Engine CSE in your blog

Google Custom Search enables you to create a search engine for your website, your blog, or a collection of websites. You can fine-tune the ranking, customize the look and feel of the search results, and invite your friends or trusted users to help you build your custom search engine. You can even make money from your search engine by using your Google AdSense account.

You can create a search engine that searches only the contents of your website or blog, or you can create one that focuses on a particular topic. This below section tells you how you can define your first custom search engine,

Creating your Custom Search Engine:

Log in:

1.  Go to Google's custom-search-engine application

2.  Log in with the Google account that owns the blog

You can transfer a blog from one Google account to another one.   But here is not currently any way to transfer ownership of a CSE between Google accounts.   So if there is any chance that you might want to hand the blog over to someone else in the future, it's a good idea to make sure that items like this are owned by a Google account that can go with it.

3.  Click the Create a Custom Search Engine button (currently a very large button near the top right of the screen) as shown below,

Start your CSE:

4.  A simple wizard opens, to help you create the CSE.  Fill in these fields on the first screen.
  • Name - What you want it to be.
  • Description - Describe what it's about (mostly so you know later on, I think)
  • Language - If your blog isn't in English, it's important to change this to the correct language, so that the search works correctly.
  • Sites to search: Enter your blog's URL.   Put  www. at the beginning, and  /* at the end (this says to search all of your blog, not just the home page) . 

    Example, the sites-list for the search on this blog is http://techtipsbysatish.blogspot.com/*
  • Edition - Unless you are willing to pay $US100/year, you need to select Standard edition. 

    This displays Ads-by-Google on the search results page.  But if you are an AdSense publisher you can link these ads to your account and earn revenue if they are clicked on.
5.  Read the Terms of Service, and if you are happy with them tick the box and press Next.

6.  Choose a Style.

7.  Test the search:
  • Enter a search-term
  • Click the Search button
  • On the screen, check that the search looks and works ok.   If not, search for words from a post entered several weeks ago, in case Google is behind in indexing your blog.
  • If necessary, go back and make changes.

You can choose different themes for the search results without re-running the test-search:  just click a theme and wait a second or two for the screen to re-draw. 

8.  Create the CSE by clicking Next

Customize your CSE:

9.  Click any of the options in the list to go to the CSE Control Panel for your Google account as shown below,

Below are the brief description about these options. If you want you can get detailed descriptions here.
  • The Basics and Sites tabs have a little more information than was in the set-up wizard, but you do not need to change them
  • Refinements and Promotions let you add extra functions to your search.  They are not needed for a standard blog-search.  Refer to Google's documentation (available from the tabs) for more about them.
  • Synonyms lets you upload a customised set of word-combinations that should be treated as equivalent in your search. 

    For example, in Blogger-hints-and-tips if I always use the word "gadget" in posts, it would be sensible for me to upload "widgets" and "page elements" as synonyms since they mean the same thing, and are terms that people are likely to search for.  However even though it would be sensible, I haven't actually uploaded this list, and the custom-search here seems to work well enough.
  • Autocomplete offers searches similar to the one your reader (appears to be) typing:  by default it's turned off, but if people who search your site a used to standard Google search (which now has it on) then it may be good to turn it on

    You also have an option to manually include or exclude certain auto-complete patterns  could be useful if your blog is in a niche with very specific terms for which the standard auto-complete option is totally wrong.
  • Look and feel: Here we will have three hosting options. Select Iframe option as shown below,

    Under Choose or Customize a Style, you can pick a style:  by default it's set to the value you picked in the wizard.  There is also a Customize button, and this let you edit a number of features including the colours, how the Google brand is shown, and whether or not a logo is shown at the top of the search results.
  • Make Money: If you don't already have an AdSense account, you can apply for one from here.   Because you will be using AdSense for Search, rather than AdSense for content, the rules and processes are a little different to those for joining AdSense for your blog.
10.  Once all the customization is done, now you have to create a page in your blog to display your search results as you have selected Iframe option. To add a new page in your blog, goto posting - edit pages - add page. Copy the URL of that page and paste it as shown below,

11. Now choose the Get Code tab. Copy the Code that is shown below and paste in the page where you'd like your search box to appear.
 Copy the below code and Paste this code on page where you would like to display your search results.

 That's all. Now you have created a custom search engine for your blog. You can test it either in your blog or in the preview option. In addition to the search box on your own website/blog, you can also visit your search engine's homepage which Google has created for you in the Preview section.

Related articles:
A Custom Search Engine for Blogger
Putting a custom search-engine in your blog
Add Custom Search box to navigation/menu bar
How to Add Google Custom Search Box 

How to submit blogger sitemap to google

Before doing this, let me say what are sitemaps and how they will help us. The reason we use them is, they will help google bot to crawl, index pages of your site and tell you if there are any problems when they try to index it. It also helps you to know, how many pages exactly are indexed and what people searched in the search engine to visit your site.

Let me explain the detailed procedure on how to submit your blogger sitemap to google.

1. First,go to google webmaster tools and sign in with your gmail account.

2. After you logged in, you will see this asking you to add a site.

Add your blog url. For example, techtipsbysatish.blogspot.com/. Don't add with http or www.

3. Next, it asks you to verify your site. It is just to confirm whether you are the owner or not.
CLICK on the 'verify your site' link.


 4. There are two ways to verify your ownership but, there is only one way for the blogger users to add a metatag.

5. Copy the metatag. Now, sign into your blogger account, click the layout button, click the Edit html and paste the metatag just after the head tag and save the changes.

and click the verify button in the google webmaster tools and there will be a confirmation message saying that you have sucessfully verified.
Now, you have to submit a sitemap for google to crawl your site. Go back to the google webmaster dashboard or (home) and click the 'Add' link which is next to your blog name and in the Sitemaps column.Then you see this,

 Choose 'Add general web sitemap' from the dropdown menu and type rss.xml or atom.xml next to your blog address as shown below,

 and click the 'Add General Web Sitemap' button.

6. That's it. Now your sitemap is successfully submitted to google. Check 2 or 3 days after and you will see the detailed statistics of your blog(indexed pages,any errors,etc.)

Related articles:
Adding Google sitemap to blogger.com blog account
How to Submit the Sitemap of Your Blogger Blog to Google
Submit Blogger Sitemap to Google Webmaster
How to Submit Blogger Sitemap to Google Webmasters