Sunday, October 20, 2013

Which is better Union or Union All- Sql Query optimization

Let us know the answer of following queries-
Que- What is Union and Union ALL in SQL sever

In this article, I am explaining both with most of unique differences in point of SQL query optimization.

Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.

  • UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
  • The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.
  • A UNION statement effectively does a SELECT DISTINCT on the results set. 

  • UNION ALL is much faster than UNION
  • The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
  • Union ALL will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
  • If you know that all the records returned are unique from your union, use UNION ALL instead, because it gives faster results.

Difference between Union & Union ALL

 /* Declare First Table */
INSERT INTO @TblWeekDays VALUES('Sunday')
INSERT INTO @TblWeekDays VALUES('Monday')
INSERT INTO @TblWeekDays VALUES('Tuesday')
INSERT INTO @TblWeekDays VALUES('Wednesday')
INSERT INTO @TblWeekDays VALUES('Thursday')

/* Declare Second Table */
INSERT INTO @TblDays VALUES('Saturday')

Union example in sql server

Difference between Union and Union ALL in SQL

Difference between Union and Union ALL in SQL

Difference between Union and Union ALL in SQL

Let me know what do you think about this article. If you have any complain or suggestion regarding improvement please feel free to write your valueble comment, I will update article accordinly

Sql Server queries optimization

SQL Server

Thursday, October 17, 2013

Dot Net Tutorials with Code samples and Interview Questions

ASP .Net

Visual Studio

Saturday, October 12, 2013

Define checkpoint and types of checkpoint in sql server

Find the solution of following queries-

A checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash

Why check points
The Database Engine performs modifications to database pages in memory—in the buffer cache—and does not write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.

Categorization of Checkpoints
Checkpoints are categorized into four categories-
1. Automatic Checkpoint
2. Indirect Checkpoints
3. Internal Checkpoints
4. Manual Checkpoint

Automatic Checkpoint
  • It is issued automatically from the background based on the [recovery interval] defined.
  • [Recovery Interval] parameter is specified in server level.
  • Default [recovery interval] value for automatic checkpoint is 0(zero) in which target recovery interval is 1 minute.
  • Query to define the [recovery interval] 
                    EXEC SP_CONFIGURE '[recovery interval]','seconds'

Indirect Checkpoints

These are new added to SQL Server 2012, provide a configurable database-level alternative to automatic checkpoints. In the event of a system crash, indirect checkpoints provide potentially faster, more predictable recovery time than automatic checkpoints. Indirect checkpoints offer the following advantages:
  • Indirect checkpoints can reduce overall database recovery time.
  • Indirect checkpoints enable you to reliably control database recovery time by factoring in the cost of random I/O during REDO. 
  • This enables a server instance to stay within an upper-bound on recovery times for a given database (except when a long-running transaction causes excessive UNDO times).
  • Indirect checkpoints reduce checkpoint-related I/O spiking by continually writing dirty pages to disk in the background.
  • Query to produce Indirect checkpoint

Internal Checkpoints

Internal checkpoint are generated only when following events occurs:
  •     Database files have been added or removed by using ALTER DATABASE.
  •     A database backup is taken.
  •     A database snapshot is created, whether explicitly or internally for DBCC CHECK.
    An instance of SQL Server is stopped by stopping the SQL Server (MSSQLSERVER) service . Either action causes a checkpoint in each database in the instance of SQL Server.

    Bringing a SQL Server failover cluster instance (FCI) offline.

Manual Checkpoint
  • It is issued when user executes T-SQL Command CHECKPOINT.
  • [Checkpoint duration] parameter is specified in database level.
  • [Checkpoint duration] parameter specifies requested amount of time in seconds for the checkpoint to complete.
  • Query to issue Manual checkpoint
CHECKPOINT [ checkpoint_duration ]
Here checkpoint_duration parameter specifies a requested amount of time, in seconds, for the checkpoint to complete

Thursday, October 10, 2013

How to change laptop keyboard step by steps process

Let us find the solution of following questions-

How to change your laptop keyboard step by steps

change laptop keyboard step by steps processI am using Compaq Presario C 740 laptop from last 7 years. This was working fine but before some days my laptop stopped working automatically. I went to a shop and purchased a new keyboard that was compatible with my laptop and applied the following steps-  An external keyboard is an easy to replace device. The user can look for a new keyboard it is easy to replace but for a built-in keyboards its more difficult to replace, but it is still possible. The user can purchase a new keyboard for the make and model laptop that has the broken keyboard on it. Then, the user can follow these basic steps to remove the old keyboard and install the new one.

1.  Turn off the laptop. Remove the battery pack, if possible.

2.  Turn the laptop upside down.

3.  Examine the bottom to determine which screws are connected to the keyboard. These are usually indicated by a small keyboard icon. There may also be screws underneath the battery pack.

4.  Find and remove all the screws holding the laptop and keyboard together.

5.  Turn the laptop back over.

6.  Pull up the plastic strip at the top of the keyboard.

7.  Unscrew the screws holding the keyboard in place underneath this strip.

8.  Lift up the keyboard to expose the data cable connecting it to the laptop.

9.  Pull the data cable out of the connector. You may have to lift up some small plastic connectors holding the cable in place to remove it.

10.  Set the old keyboard aside.

11.  Slide the data cable of the new keyboard into the connector.

12.  Screw the keyboard back in. Press the plastic strip back into place, and flip the laptop over to reinsert the rest of the screws.

Power up the laptop and test the keyboard. If the keyboard is installed properly, the computer should recognize it automatically. There is usually no need to download keyboard driver software. 

For more info click here  

Wednesday, October 2, 2013

Best secure payment gateways for online transaction

Payment Gateway

A payment gateway is an e-commerce application service provider service that authorizes credit card payments for e-businesses, online retailers, bricks and clicks, or traditional brick and mortar     - Wiki

On the basis of survey for Aug,2013 this is the ranking list of top 10 Payment gateways-
1) Leaders Merchant Services
2) Authorize.Net

3) Total Merchant Services
4) Credit Card Processing Specialists
5) VeriCheck
7) PayPal
9) First Data Corporation
10) PaySimple
Details and Features of Some famous Payment Gateways

Authorized Dot Net Payment gateway
  • Accepts: Visa, MasterCard, American Express, Discover, Diner’s Club, JCB
  • Sign up fee: $99.00 *
  • Transaction fees: $0.10 each *
  • Additional fee: Monthly Gateway fee – $20 * and Batch fee – $25 *
  • Countries supported: 190+ countries. However, just U.S citizens can create an account with this payment gateway.
  • Click here for getting started with
Amazon Payment gateway
Google Checkout Payment gateway
Paypal Payment gateway
2Checkout payment gateway
  • Card Type: MasterCard, Visa, Discover, American Express, Diners , JCB, Debit cards with the Visa or MasterCard logo, PIN debit cards with the Visa or MasterCard logo, PayPal
  • Sign up fee: 0
  • Transaction fees: $0.45 + 5.5% each.
  • Additional fee: Monthly fee – $10.99
  • Countries supported: 200+ countries worldwide (List)
quickpay payment gateway
  • Card Type: MasterCard, Visa, Maestro, Discover, American Express, Diners, JCB and more (List)
  • Sign up fee: 0
  • Transaction fees: €0.015 – €0.067 each.
  • Additional fees: Monthly fee: €25
  • Countries supported: All European countries.
  • Click here for getting started with Quickpay
Samurai by FeeFighters payment gateway

strip payment gateway

dwolla payment gateway
payza payment gateway

Wepay payment gateway

braintree payment gateway
worldpay payment gateway
  • Card Type: Visa Credit and Debit, MasterCard, American Express, JCB, Diners, Laser, ELV, PayPal
  • Sign up fee: £75
  • Transaction fees: From 1.9% + 10 pence
  • Additional fee: Monthly fee – £15
  • Countries supported: 36 countries (List)
  • Click here for getting started with Worldpay
Skrill (Moneybookers)
skrill moneybookers payment system
  • Card Type: Credit card (Visa, MasterCard, American Express, JCB, Diners Club), Debit card (Maestro, Visa Electron) and more.
  • Sign up fee: 0
  • Transaction fees: 1.9% + €0.25 to 2.9% + €0.25 each (for European Merchants, it is different with other regions (details)
  • Countries supported: 200+ countries
  • Click here for getting started with Skrill
If your favorite online payment system doesn't in the above list, please discuss it as your comments with the reason why you choose it over other online payment systems.

For more details about features please click here

Powered by Code Imagine