Sunday, November 30, 2008

OOPs FAQ in C# - Part II

What is an Object?

An object is an instance of a class. It can be uniquely identified by its name and it defines a state which is represented by the values of its attributes at a particular time.

An object can be considered a "thing" that can perform a set of activities. The set of activities that the object performs defines the object's behavior.

The state of the object changes according to the methods which are applied to it. We refer to these possible sequences of state changes as the behavior of the object. So the behavior of an object is defined by the set of methods which can be applied on it.

Objects can communicate by passing messages to each other.

What is inheritance?

Inheritance is the mechanism which allows a class A to inherit properties of a class B. We say "A inherits from B''. Objects of class A thus have access to attributes and methods of class B without the need to redefine them.

If class A inherits from class B, then B is called superclass of A. A is called subclass of B. Objects of a subclass can be used where objects of the corresponding superclass are expected. This is due to the fact that objects of the subclass share the same behavior as objects of the superclass.

However, subclasses are not limited to the state and behaviors provided to them by their superclass. Subclasses can add variables and methods to the ones they inherit from the superclass.

In the literature you may also find other terms for "superclass" and "subclass". Superclasses are also called parent classes or base classes. Subclasses may also be called child classes or just derived classes.

Inheritance Example

Like a car, truck or motorcycles have certain common characteristics- they all have wheels, engines and brakes. Hence they all could be represented by a common class Vehicle which encompasses all those attributes and methods that are common to all types of vehicles.

However they each have their own unique attributes; car has 4 wheels and is smaller is size to a truck; whereas a motorcycle has 2 wheels. Thus we see a parent-child type of relationship here where the Car, Truck or Motorcycle can inherit certain Characteristics from the parent Vehicle; at the same time having their own unique attributes. This forms the basis of inheritance; Vehicle is the Parent, Super or the Base class. Car, Truck and Motorcycle become the Child, Sub or the Derived class.

What is a Virtual Functions in class?

A virtual function is a member function of the base class and which is redefined by the derived class. When a derived class inherits the class containing the virtual function, it has ability to redefine the virtual functions.

A virtual function has a different functionality in the derived class according to the requirement. The virtual function within the base class provides the form of the interface to the function. Virtual function implements the philosophy of one interface and multiple methods (polymorphism).

The virtual functions are resolved at the run time. This is called dynamic binding. The functions which are not virtual are resolved at compile time which is called static binding. A virtual function is created using the keyword virtual which precedes the name of the function.

What is Encapsulation in Object Oriented Programming (OOPS) Languages?

Encapsulation is the procedure of covering up of data and functions into a single unit. Encapsulation (also information hiding) consists of separating the external aspects of an object which are accessible to other objects, from the internal implementation details of the object, which are hidden from other objects.

A process, encapsulation means the act of enclosing one or more items within a (physical or logical) container (Class).

Object-oriented programming is based on encapsulation. When an objects state and behavior are kept together, they are encapsulated. That is, the data that represents the state of the object and the methods (Functions and Subs) that manipulate that data are stored together as a cohesive unit.

The object takes requests from other client objects, but does not expose its the details of its data or code to them. The object alone is responsible for its own state, exposing public messages for clients, and declaring private methods that make up its implementation. The client depends on the (hopefully) simple public interface, and does not know about or depend on the details of the implementation.

For example, a HashTable object will take get() and set() requests from other objects, but does not expose its internal hash table data structures or the code strategies that it uses.

Explain the advantages of Encapsulation in Object Oriented Programming Languages.

Benefits of Encapsulation in oops: Encapsulation makes it possible to separate an objects implementation from its behavior to restrict access to its internal data. This restriction allows certain details of an objects behavior to be hidden. It allows us to create a "black box" and protects an objects internal state from corruption by its clients.

Encapsulation is a technique for minimizing interdependencies among modules by defining a strict external interface. This way, internal coding can be changed without affecting the interface, so long as the new implementation supports the same (or upwards compatible) external interface. So encapsulation prevents a program from becoming so interdependent that a small change has massive ripple effects.

The implementation of an object can be changed without affecting the application that uses it for: Improving performance, fix a bug, consolidate code or for porting.

Limitations and Restrictions of Interface

The essential idea to remember is that an interface never contains any implementation. The following restrictions and imitations are natural consequences of this:

You're not allowed any fields in an interface, not even static ones. A field is an implementation of an object attribute.

You're not allowed any constructors in an interface. A constructor contains the statements used to initialize the fields in an object, and an interface does not contain any fields!

You're not allowed a destructor in an interface. A destructor contains the statements used to destroy an object instance.

You cannot supply an access modifier. All methods in an interface are implicitly public.

You cannot nest any types (enums, structs, classes, interfaces, or delegates) inside an interface.

What is the difference between abstract class and interface?

We use abstract class and interface where two or more entities do same type of work but in different ways. Means the way of functioning is not clear while defining abstract class or interface. When functionality of each task is not clear then we define interface. If functionality of some task is clear to us but there exist some functions whose functionality differs object by object then we declare abstract class.

We can not make instance of Abstract Class as well as Interface. They only allow other classes to inherit from them. And abstract functions must be overridden by the implemented classes. Here are some differences in abstract class and interface.

An interface cannot provide code of any method or property, just the signature. We dont need to put abstract and public keyword. All the methods and properties defined in Interface are by default public and abstract. An abstract class can provide complete code of methods but there must exist a method or property without body.

A class can implement several interfaces but can inherit only one abstract class. Means multiple inheritance is possible in .Net through Interfaces.

What is a static class?

We can declare a static class. We use static class when there is no data or behavior in the class that depends on object identity. A static class can have only static members. We can not create instances of a static class using the new keyword. .NET Framework common language runtime (CLR) loads Static classes automatically when the program or namespace containing the class is loaded.

Here are some more features of static class:

  • Static classes only contain static members.
  • Static classes can not be instantiated. They cannot contain Instance Constructors
  • Static classes are sealed.

What is static member of class?

A static member belongs to the class rather than to the instances of the class. In C# data fields, member functions, properties and events can be declared static. When any instances of the class are created, they cannot be used to access the static member.

To access a static class member, use the name of the class instead of an instance variable

Static methods and Static properties can only access static fields and static events.

Like: int i = Car.GetWheels;

Here Car is class name and GetWheels is static property.

Static members are often used to represent data or calculations that do not change in response to object state.

What is the difference between value parameter and reference parameter?

A value parameter is used for "in" parameter passing, in which the value of an argument is passed into a method, and modifications of the parameter do not impact the original argument. A value parameter refers to its own variable, one that is distinct from the corresponding argument. This variable is initialized by copying the value of the corresponding argument.

A reference parameter is used for "by reference" parameter passing, in which the parameter acts as an alias for a caller-provided argument. A reference parameter does not itself define a variable, but rather refers to the variable of the corresponding argument. Modifications of a reference parameter impact the corresponding argument.

What is the use of parameter array?

A parameter array enables a many-to-one relationship: many arguments can be represented by a single parameter array. In other words, parameter arrays enable variable length argument lists.

A parameter array is declared with a params modifier in C#. There can be only one parameter array for a given method, and it must always be the last parameter specified. The type of a parameter array is always a single dimensional array type. A caller can either pass a single argument of this array type, or any number of arguments of the element type of this array type.

What is a constant?

A constant is a class member that represents a constant value: a value that can be computed at compile-time.

Constants are permitted to depend on other constants within the same program as long as there are no circular dependencies. The example

class Constants {

public const int A = 1;

public const int B = A + 1;

}

shows a class named Constants that has two public constants.

Happy Programming!!!

OOPs FAQ in C# - Part I

What is the difference between indexers and properties in C#?

Comparison Between Properties and IndexersIndexers are similar to properties. Except for the differences shown in the following , all of the rules defined for property accessors apply to indexer accessors as well.

  • Properties
    Identified by its name.
    Accessed through a simple name or a member access.
    Can be a static or an instance member.
    A get accessor of a property has no parameters.
    A set accessor of a property contains the implicit value parameter.
  • Indexers
    Identified by its signature.
    Accessed through an element access.
    Must be an instance member.
    A get accessor of an indexer has the same formal parameter list as the indexer.
    A set accessor of an indexer has the same formal parameter list as the indexer, in addition to the value parameter.

What type of class cannot be inherited?

A sealed class cannot be inherited. A sealed class is used primarily when the class contains static members. Note that a struct is implicitly sealed; so they cannot be inherited.

How do I use an alias for a namespace or class in C#?

Use the using directive to create an alias for a long namespace or class. You can then use it anywhere you normally would have used that class or namespace. The using alias has a scope within the namespace you declare it in. Sample code: // Namespace:
using act = System.Runtime.Remoting.Activation;
// Class:
using list = System.Collections.ArrayList;
...
list l = new list(); // Creates an ArrayList
act.UrlAttribute obj; // Equivalent to System.Runtime.Remoting.Activation.UrlAttribute obj

Can an abstract class have non-abstract methods?

An abstract class may contain both abstract and non-abstract methods. But an interface can contain only abstract methods.

Explain some features of interface in C# or Comparison of interface with class.

An interface cannot inherit from a class.
An interface can inherit from multiple interfaces.
A class can inherit from multiple interfaces, but only one class.
Interface members must be methods, properties, events, or indexers.

All interface members must have public access (the default).
By convention, an interface name should begin with an uppercase I.

Name two ways that you can prevent a class from being instantiated.

Ways to prevent a class from instantiated:

  • A class cannot be instantiated if it is abstract or
  • if it has a private constructor.

What's the difference between override and new in C#?

his is all to do with polymorphism. When a virtual method is called on a reference, the actual type of the object that the reference refers to is used to decide which method implementation to use. When a method of a base class is overridden in a derived class, the version in the derived class is used, even if the calling code didn't "know" that the object was an instance of the derived class. For instance:

public class Base
{
public virtual void SomeMethod()
{
}
}
public class Derived : Base
{
public override void SomeMethod()
{
}
}
...
Base b = new Derived();
b.SomeMethod();
will end up calling Derived.SomeMethod if that overrides Base.SomeMethod.

Now, if you use the new keyword instead of override, the method in the derived class doesn't override the method in the base class, it merely hides it. In that case, code like this:

public class Base
{
public virtual void SomeOtherMethod()
{
}
}
public class Derived : Base
{
public new void SomeOtherMethod()
{
}
}
...
Base b = new Derived();
Derived d = new Derived();
b.SomeOtherMethod();
d.SomeOtherMethod();

Will first call Base.SomeOtherMethod , then Derived.SomeOtherMethod . They're effectively two entirely separate methods which happen to have the same name, rather than the derived method overriding the base method.

If you don't specify either new or overrides, the resulting output is the same as if you specified new, but you'll also get a compiler warning (as you may not be aware that you're hiding a method in the base class method, or indeed you may have wanted to override it, and merely forgot to include the keyword).

Explain Abstract, Sealed, and Static Modifiers in C#

C# provides many modifiers for use with types and type members. Of these, three can be used with classes: abstract, sealed and static.

  • abstract : Indicates that a class is to be used only as a base class for other classes. This means that you cannot create an instance of the class directly. Any class derived from it must implement all of its abstract methods and accessors. Despite its name, an abstract class can possess non-abstract methods and properties.
  • sealed : Specifies that a class cannot be inherited (used as a base class). Note that .NET does not permit a class to be both abstract and sealed.
  • static : Specifies that a class contains only static members (.NET 2.0).

Which interface(s) must a class implement in order to support the foreach statement?

Required interface for foreach statement: A class must implement the IEnumerable and IEnumerator interfaces to support the foreach statement.

How do I call one constructor from another in C#?

You use : base (parameters) or : this (parameters) just before the actual code for the constructor, depending on whether you want to call a constructor in the base class or in this class.

What is the difference between struct and class in C#?

Structs vs classes in C#

Structs may seem similar to classes, but there are important differences that you should be aware of. First of all, classes are reference types and structs are value types. By using structs, you can create objects that behave like the built-in types and enjoy their benefits as well.

  • When you call the New operator on a class, it will be allocated on the heap. However, when you instantiate a struct, it gets created on the stack. This will yield performance gains. Also, you will not be dealing with references to an instance of a struct as you would with classes. You will be working directly with the struct instance. Because of this, when passing a struct to a method, it's passed by value instead of as a reference.
  • Structs can declare constructors, but they must take parameters. It is an error to declare a default (parameterless) constructor for a struct. Struct members cannot have initializers. A default constructor is always provided to initialize the struct members to their default values.
  • When you create a struct object using the New operator, it gets created and the appropriate constructor is called. Unlike classes, structs can be instantiated without using the New operator. If you do not use New, the fields will remain unassigned and the object cannot be used until all the fields are initialized.
  • There is no inheritance for structs as there is for classes. A struct cannot inherit from another struct or class, and it cannot be the base of a class. Structs, however, inherit from the base class object. A struct can implement interfaces, and it does that exactly as classes do,
  • Structs are simple to use and can prove to be useful at times. Just keep in mind that they're created on the stack and that you're not dealing with references to them but dealing directly with them. Whenever you have a need for a type that will be used often and is mostly just a piece of data, structs might be a good option.

Happy programming!!!

.Net Remoting Interview Questions

  • What is .NET Remoting?

Net remoting replaces DCOM. Web Services that uses remoting can run in any Application type i.e. Console Application, Windows Form Applications, Window Services etc. In CLR Object Remoting we can call objects across network.

  • .NET Remoting Architecture?
  1. Methods that will be called from the client are implemented in a remote object class.
  2. Client uses a proxy to call a remote object.
  3. Remote objects runs inside a process that is different from the client process
  4. For the client, the proxy looks like the real object with the same public methods.
  5. When the methods of the proxy are called, messages are created.
  6. Messages are serialized using a binary formatter class, and are sent into a client channel.
  7. The client channel communicates with the server part of the channel to transfer the message across the network.
  8. The server channel uses a formatter to deserialize the message, so that the methods can be dispatched to the remote object.
  9. The formatter and the proxy is supplied automatically.
  • What is a formatter?

A formatter is an object that is responsible for encoding and serializing data into messages on one end, and deserializing and decoding messages into data on the other end.

  • How does .NET Remoting work?

.NET remoting involves sending messages along channels. Two of the standard channels are HTTP and TCP. TCP is intended for LANs only - HTTP can be used for LANs or WANs (internet).

Support is provided for multiple message serializarion formats. Examples are SOAP (XML-based) and binary. By default, the HTTP channel uses SOAP (via the .NET runtime Serialization SOAP Formatter), and the TCP channel uses binary (via the .NET runtime Serialization Binary Formatter). But either channel can use either serialization format.

There are a number of styles of remote access:

SingleCall:
Each incoming request from a client is serviced by a new object. The object is thrown away when the request has finished. This (essentially stateless) model can be made stateful in the ASP.NET environment by using the ASP.NET state service to store application or session state.

Singleton:
All incoming requests from clients are processed by a single server object.

Client-activated object:
This is the old stateful (D)COM model whereby the client receives a reference to the remote object and holds that reference (thus keeping the remote object alive) until it is finished with it.

  • What’s Singleton activation mode?

A single object is instantiated regardless of the number of clients accessing it. Lifetime of this object is determined by lifetime lease.

  • What’s SingleCall activation mode used for?

If the server object is instantiated for responding to just one single request, the request should be made in SingleCall mode.

  • .NET Remoting Specific Advantage?

Lease-Based Lifetime :

Distributed garbage collection of objects is managed by a system called 'leased based lifetime'. Each object has a lease time, and when that time expires the object is disconnected from the .NET runtime remoting infrastructure

.Net Remoting takes a Lease-base Lifetime of the object that is scaleable

Call Context :

Additional information can be passed with every method call that is not part of the argument with the help of SOAP Header

Distributed Identities :

If we pass a reference to a remote object, we will access the same object using this reference.

  • How do you define the lease of the object?

By implementing ILease interface when writing the class code.

  • What are channels in .NET Remoting?

Channels represent the objects that transfer the other serialized objects from one application domain to another and from one computer to another, as well as one process to another on the same box. A channel must exist before an object can be transferred.

  • How can you automatically generate interface for the remotable object in .NET with Microsoft tools?

Use the Soapsuds tool.

  • What are possible implementations of distributed applications in .NET?

.NET Remoting and ASP.NET Web Services. If we talk about the Framework Class Library, noteworthy classes are in System.Runtime.Remoting and System.Web.Services.

  • When would you use .NET Remoting and when Web services?

Use remoting for more efficient exchange of information when you control both ends of the application. Use Web services for open-protocol-based information exchange when you are just a client or a server with the other end belonging to someone else.

  • What security measures exist for .NET Remoting in System.Runtime.Remoting?

None. Security should be taken care of at the application level. Cryptography and other security techniques can be applied at application or server level.

  • What’s a Windows process?

It’s an application that’s running and had been allocated memory.

  • What distributed process frameworks outside .NET do you know?

Distributed Computing Environment/Remote Procedure Calls (DEC/RPC), Microsoft Distributed Component Object Model (DCOM), Common Object Request Broker Architecture (CORBA), and Java Remote Method Invocation (RMI).

  • What’s a proxy of the server object in .NET Remoting?

It’s a fake copy of the server object that resides on the client side and behaves as if it was the server. It handles the communication between real server object and the client object. This process is also known as marshaling.

  • What’s typical about a Windows process in regards to memory allocation?

Each process is allocated its own block of available RAM space, no process can access another process’ code or data. If the process crashes, it dies alone without taking the entire OS or a bunch of other applications down.

  • Can you configure a .NET Remoting object via XML file?

Yes, via machine.config and application level .config file (or web.config in ASP.NET). Application-level XML settings take precedence over machine.config.

  • What are remotable objects in .NET Remoting?

Remotable objects are the objects that can be marshaled across the application domains. You can marshal by value, where a deep copy of the object is created and then passed to the receiver. You can also marshal by reference, where just a reference to an existing object is passed.

  • Advantage over Web Services?
  1. It works using purely Commmon Type System.
  2. It supports high speed binary over tcp/ip communication.
  • Advantage over COM/DCOM?
  1. It does not have extra interface language (IDL)
  2. It Works using purely managed code
  3. It's using Common Type System.. No Safearrays etc
  • Disadvantages
  1. It is not an open standard like web services.
  2. It is not as widespread and established ad DCOM.
  3. Less support for transactions,load balancing compared with DCOM.
  • Choosing between HTTP and TCP for protocols and Binary and SOAP for formatters, what are the trade-offs?

Binary over TCP is the most effiecient, SOAP over HTTP is the most interoperable.

  • Why do you call it a process?What’s different between process and application in .NET, not common computer usage, terminology?

A process is an instance of a running application. An application is an executable on the hard drive or network. There can be numerous processes launched of the same application (5 copies of Word running), but 1 process can run just 1 application.

  • How many processes can listen on a single TCP/IP port?

One.

Happy Programming!!!

Types of Parameters in C#

In C#, parameters are means of passing values to a method. There are four different ways of passing parameters to a method in C#. The four different types of parameters are

  • Value
  • Out
  • Ref
  • Params

1.Value parameters

This is the default parameter type in C#. If the parameter does not have any modifier it is "value" parameter by default. When we use "value" parameters the actual value is passed to the function, which means changes made to the parameter is local to the function and is not passed back to the calling part.

using System;
class ParameterTest
{
static void Mymethod(int Param1)
{
Param1=100;
}
static void Main()
{
int Myvalue=5;
MyMethod(Myvalue);
Console.WriteLine(Myvalue);
}
}

Output
5

Even though the value of the parameter Param1 is changed with in MyMethod it is not passed back to the calling part since value parameters are input only.

2. Out parameters

"out" parameters are output only parameters meaning they can only pass back a value from a function. We create a "out" parameter by preceding the parameter data type with the out modifier. When ever a "out" parameter is passed only an unassigned reference is passed to the function.

using System;
class ParameterTest
{
static void Mymethod(out int Param1)
{
Param1=100;
}
static void Main()
{
int Myvalue=5;
MyMethod(Myvalue);
Console.WriteLine(out Myvalue);
}
}
Output
100

Since, the value of the "out" parameter is passed back to the calling part. The modifier "out" should precede the parameter being passed even in the calling part. "out" parameters cannot be used within the function before assigning a value to it. A value should be assigned to the "out" parameter before the method returns.

3.Ref parameters

"ref" parameters are input/output parameters meaning they can be used for passing a value to a function as well as to get back a value from a function. We create a "ref" parameter by preceding the parameter data type with the ref modifier. When ever a "ref" parameter is passed a reference is passed to the function.

using System;
class ParameterTest
{
static void Mymethod(ref int Param1)
{
Param1=Param1 + 100;
}
static void Main()
{
int Myvalue=5;
MyMethod(Myvalue);
Console.WriteLine(ref Myvalue);
}
}

Output
105

Since, the "ref" parameter acts as both input and output. The modifier "ref" should precede the parameter being passed even in the calling part. "ref" parameters should be assigned a value before using it to call a method.

4.Params parameters

"params" parameters is a very useful feature in C#. "params" parameter are used when a variable number of arguments need to be passed. The "params" should be a single dimensional or a jagged array.

using System;
class ParameterTest
{
static int Sum(params int[] Param1)
{
int val=0;
foreach(int P in Param1)
{
val=val+P;
}
return val;
}
static void Main()
{
Console.WriteLine(Sum(1,2,3));
Console.WriteLine(Sum(1,2,3,4,5));
}
}

Output
6
15

The value passed for a "params" parameter can be either comma separated value list or a single dimensional array. "params" parameters are input only.



What is reference parameter? What is out parameters? What is difference these two?
  • a 'ref' parameter must first be initialized before being passed from the calling function to the called function but
  • a 'out' parameter need not be initialized, we can pass it directly.
  • a 'ref' parameter can be used as both input and o/p parameter.
  • a 'out' parameter can be used as only output parameter.
Happy Programming!!!

Tuesday, November 11, 2008

SQL Interview Tips - 1

What is data integrity?
  • Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid.
  • also acts as a trap for otherwise undetectable bugs within applications.
Explain constraints?
Candidate key :
  • A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data.
  • Each table may have one or more candidate keys.
  • One of these candidate keys is selected as the table primary key.
Primary Key :
  • Primary key will create column data uniqueness in the table.
  • Primary key will create clustered index by default
  • Only one Primay key can be created for a table
  • Multiple columns can be consolidated to form a single primary key
  • It won't allow null values.

Foreign key :

  • A foreign key is a field in a relational table that matches the primary key column of another table.
  • The foreign key can be used to cross-reference tables.
  • The foreign key is used to prevent actions that would destroy link between tables.
  • The foreign key also prevents that invalid data is inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

Unique Key :

  • Unique key constraint will provide you a constraint like the column values should retain uniqueness.
  • It will allow null value in the column.
  • It will create non-clustered index by default
  • Any number of unique constraints can be added to a table.

Surrogate Key / Artificial Key / Identity key:

  • The value is unique system-wide, hence never reused
  • The value is system generated
  • The value is not manipulable by the user or application
  • The value contains no semantic meaning
  • The value is not visible to the user or application
  • Surrogate Key is the solution for critical column problems. For example, the customur purchases different items in differnt locations,for this situation we have to maintain historical data.
  • By using surrogate key we can introduce the row in the datawarehouse to maintain historical data.
  • Surrogate is mainly used in slowely changing dimensions,it mantaining the uniqueness in the table.it is used to track the old value with the new one.
  • We can say "Surrogate key" is a User defined primary key

What's the difference between a primary key and a unique key?

  • Primary key wont allow nulls, unique key allow nulls.
  • Primary key constraints are more restrictive than Unique constraints
  • Unique constraints create unique non-clustered indexes by default;Pprimary key constraints create unique clustered indexes by default.
  • There can be only one clustered index on a table, so you can specify only one unique clustered or primary key clustered constraint.
What is the difference between a primary key and a surrogate key?
  • A primary key is a special constraint on a column or set of columns.
  • A primary key constraint ensures that the column(s) so designated have no NULL values, and that every value is unique.
  • Physically, a primary key is implemented by the database system using a unique index, and all the columns in the primary key must have been declared NOT NULL.
  • A table may have only one primary key, but it may be composite (consist of more than one column).
  • A surrogate key is any column or set of columns that can be declared as the primary key instead of a "real" or natural key.
  • Sometimes there can be several natural keys that could be declared as the primary key, and these are all called candidate keys. So a surrogate is a candidate key.
  • A table could actually have more than one surrogate key, although this would be unusual.
  • The most common type of surrogate key is an incrementing integer, such as an auto_increment column in MySQL, or a sequence in Oracle, or an identity column in SQL Server.
Pros and Cons of Surrogate Key :

Pros:
  • Business Logic is not in the keys.
  • Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).
  • Joins are very fast.
  • No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached - very scalable.
Cons :
  • An additional index is needed. In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).
  • Cannot be used as a search key.
  • If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.
  • Always requires a join when browsing the child table(s).
Pros and Cons of Natural Key :

Pros :

  1. No additional Index.
  2. Can be used as a search key.
Cons:
  1. If not chosen wisely (business meaning in the key(s)), then over a period of time additions may be required to the PK and modifications to the PK can occur.
  2. If using strings, joins are a bit slower as compared to the int data-type joins, storage is more as well. Since storage is more, less data-values get stored per index page. Also, reading strings is a two step process in some RDBMS: one to get the actual length of the string and second to actually perform the read operation to get the value.
  3. Locking contentions can arise if using application driven generation mechanism for the key.
  4. Can’t enter a record until value is known since the value has some meaning.
Use the tips!!!