Thursday, December 26, 2013

Define Fill Factor. what is SQL data page

Define Fill Factor. what is SQL data page

Find the solution of following queries-

As we know the data in the table is stored in the data pages of the database according to the order of the values in the indexed columns. It means When the value of clustered or non clustered index is created or changed, SQL Server reorganizes the storage of the data in the table.

What is Fill Factor
Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of  Page with size 8K.Avery page can store many rows it is tottaly depends upon the size of the row.

What is the default value for a Fill Factor
The default value for a Fill Factor is 100. If the value of fill factor is 100, It means there is no or very little empty space left in the page.

Measurement of Fill Factor at the server level
1: Use the following script to  measure the Fill Factor at the server level
Syntax-
              SELECT   * FROM sys.configurations  WHERE name ='fill factor (%)'

What is fill factor in sql server


2: Use the following script to  measure the Fill Factor at the table/index level
Syntax-
              USE YourDatabaseName;
              SELECT OBJECT_NAME(OBJECT_ID) Name, type_desc, fill_factor
              FROM sys.indexes

measure the Fill Factor at the table/index level


Note: Never forget the fill factor is implemented only when the index is created; it is not maintained after the index is created as data is added, deleted, or updated in the table.

Sunday, November 24, 2013

Difference between Ceiling, Floor and Round in SQL Server

SQL Server rounding functions-Ceiling, Floor and Round

In SQL queries sometimes we need to round off some decimal  or float values, at that time we always think that which option to be applied while we have three different kinds of system defined SQL rounding functions-Ceiling, Floor and Round.

CEILING
Get the value on the right side of the decimal and returns the smallest integer greater or equal to, the specified values.

FLOOR
Get the value on the right side of the decimal and returns the largest integer less or equal to the specified values (only number)

ROUND
Rounds a positive or negative value to a specific length.

Example of SQL rounding functions


Difference between Ceiling, Floor and Round in SQL Server

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
  • 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
  • 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 */
DECLARE @TblWeekDays TABLE (Days VARCHAR(10))
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 */
DECLARE @TblDays TABLE (Din VARCHAR(10))
INSERT INTO @TblDays VALUES('Friday')
INSERT INTO @TblDays VALUES('Saturday')
INSERT INTO @TblDays VALUES('Sunday')
INSERT INTO @TblDays VALUES('Monday')

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

C#
ASP .Net
WCF

MVC
Visual Studio
Other

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
ALTER DATABASE … SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

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
6) 2Checkout.com
7) PayPal
8) SecurePay.com
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 Authorize.net
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
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
payza payment gateway


Wepay payment gateway

braintree payment gateway
Worldpay
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
Conclusion
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

Sunday, September 29, 2013

Pass complex object to Web API in MVC

ASP.NET Web API is one of the most powerful recent addition to ASP.NET framework. Sometimes, you have to post a form data using jQuery-JSON to Web API or MVC method, which have so many input fields. Passing each and every input field data as a separate parameter is not good practice, even when you have a strongly typed-view. The best practice is, pass a complex type object for all the input fields to the server side to remove complexity.


Let us know- how to pass complex types object to the Web API and MVC methods to remove complexity at server side and make it simple and useful with the help of this example.



Model Class

Suppose you have the following Product class and repository for product.
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public string Category { get; set; }
public decimal Price { get; set; }
}

interface IProductRepository
{
Product Add(Product item);
//To Do : Some Stuff
}

public class ProductRepository : IProductRepository
{
private List<Product> products = new List<Product>();
private int _nextId = 1;

public ProductRepository()
{
// Add products for the Demonstration
Add(new Product { Name = "Computer", Category = "Electronics", Price = 23.54M });
Add(new Product { Name = "Laptop", Category = "Electronics", Price = 33.75M });
Add(new Product { Name = "iPhone4", Category = "Phone", Price = 16.99M });
}

public Product Add(Product item)
{
if (item == null)
{
throw new ArgumentNullException("item");
}

// TO DO : Code to save record into database
item.Id = _nextId++;
products.Add(item);

return item;
}
//To Do : Some Stuff
}


View (Product.cshtml)
<script type="text/javascript">
//Add New Item by Web API
$("#Save").click(function () {

//Making complex type object
var Product = {
Id: "0",
Name: $("#Name").val(),
Price: $("#Price").val(),
Category: $("#Category").val()
};

if (Product.Name != "" && Product.Price != "" && Product.Category != "") {
//Convert javascript object to JSON object
var DTO = JSON.stringify(Product);
$.ajax({
url: 'api/product', //calling Web API controller product
cache: false,
type: 'POST',
contentType: 'application/json; charset=utf-8',
data: DTO,
dataType: "json",
success: function (data) {
alert('added');
}
}).fail(
function (xhr, textStatus, err) {
alert(err);
});

}
else {
alert('Please Enter All the Values !!');
}

});

</script> 
<div>
<div>
<h2>Add New Product</h2>
</div>
<div>
<label for="name">Name</label>
<input type="text" id="Name" title="Name" />
</div>

<div>
<label for="category">Category</label>
<input type="text" id="Category" title="Category" />
</div>

<div>
<label for="price">Price</label>
<input type="text" id="Price" title="Price" />
</div>
<br />
<div>
<button id="Save">Save</button>
<button id="Reset">Reset</button>
</div>
</div>


Web API Controller
public class ProductController : ApiController
{
static readonly IProductRepository repository = new ProductRepository();
public Product PostProduct(Product item)
{
return repository.Add(item);
}

Difference between shadow and override inheritance in c#

Let's know the solution of following questions
Ques: What is difference between shadow and override inheritance in c#
Ques: Shadowing and Overriding in C# dot net
Ques: Inheritance features in C# dot net 
"Shadowing and overriding are both used when a derived class inherits from a base class, and both redefine one declared element with another. But there are significant differences between the two"
When you extend a class, you shadow fields with the same name in the base class and override virtual methods with the same name and parameter list in the base class. Overriding makes the base class method invisible. Shadowing a field only hides the field from view. You can still explicitly touch the hidden shadowed field if you wish. You can't touch an invisible overridden method.

Example
class Base
    {
        public void PrintOnScreen()
        {
            Console.WriteLine("Base Method");
        }
    }
    class Derive : Base
    {
        public new void PrintOnScreen()
        {
            Console.WriteLine("Derive Method");
        }
    }
    class MethodShadowingDemo
    {
        public static void Main(string[] args)
        {
            Base objDerive = new Derive();
            objDerive.PrintOnScreen();
            Console.ReadLine();
        }
    }

Note:
  • The calling sequence consists of the element type (FunctionSubOperator, or Property), name, parameter list, and return type. You cannot override a procedure with a property, or the other way around. You cannot override one kind of procedure (FunctionSub, or Operator) with another kind.
  • If you do not specify either Shadows or Overrides, the compiler issues a warning message to help you be sure which kind of redefinition you want to use. If you ignore the warning, the shadowing mechanism is used.
  •  If the shadowing element is inaccessible in a further derived class, shadowing is not inherited. For example, if you declare the shadowing element as Private, a class deriving from your derived class inherits the original element instead of the shadowing element.

 
Powered by Code Imagine