Monday, May 26, 2008

Java Interview Questions

Q) What is difference between Java and C++?


A) (i) Java does not support pointers. Pointers are inherently insecure and troublesome. Since pointers do not exist in Java. (ii) Java does not support operator overloading. (iii) Java does not perform any automatic type conversions that result in a loss of precision (iv) All the code in a Java program is encapsulated within one or more classes. Therefore, Java does not have global variables or global functions. (v) Java does not support multiple inheritance.


Java does not support destructors, but rather, add the finalize() function. (vi) Java does not have the delete operator. (vii) The <<>> are not overloaded for I/O operations


Q) Opps concepts



Polymorphism
Ability to take more than one form, in java we achieve this using Method Overloading (compile time polymorphism), Method overriding (runtime polymorphism)
Inheritance
Is the process by which one object acquires the properties of another object. The advantages of inheritance are reusability of code and accessibility of variables and methods of the super class by subclasses.
Encapsulation
Wrapping of data and function into a single unit called encapsulation. Ex:- all java programs.
(Or)
Nothing but data hiding, like the variables declared under private of a particular class are accessed only in that class and cannot access in any other the class. Or Hiding the information from others is called as Encapsulation. Or Encapsulation is the mechanism that binds together code and data it manipulates and keeps both safe from outside interference and misuse.
Abstraction
Nothing but representing the essential futures without including background details.
Dynamicbinding
Code associated with a given procedural call is not known until the time of the call at runtime. Dynamic binding is nothing but late binding.

Q) class & object?
class
class is a blue print of an object
Object
instance of class.

Q) Object creation?
Object is constructed either on a memory heap or on a stack.


Memory heap
Generally the objects are created using the
new keyword. Some heap memory is allocated to this newly created object. This memory remains allocated throughout the life cycle of the object. When the object is no more referred, the memory allocated to the object is eligible to be back on the heap.

Stack
During method calls, objects are created for method arguments and method variables. These objects are created on stack.


Q) System.out.println()
println() is a methd of java.io.printWriter.
“out” is an instance variable of java.lang.System class.

Q) Transient & volatile


Transient --> The transient modifier applies to variables only, the object are variable will not persist. Transient variables are not serialized.



Volatile --> value will be changed unexpectedly by the other part of the program, "it tells the
compiler a variable may change asynchronously due to threads"



Q) Access Specifiers & Access modifiers?
Access Specifiers A.S gives access privileges to outside of application (or) others, they are Public, Protected, Private, Defaults.

Access Modifiers A.M which gives additional meaning to data, methods and classes, final cannot be modified at any point of time.





















































Private



Public



Protected



No modifier



Same class



No



Yes



Yes



Yes



Same package Subclass



No



Yes



Yes



Yes



Same package non-subclass



No



Yes



Yes



Yes



Different package subclass



No



Yes



Yes



No



Different package non-subclass



No



Yes



No



No




Q) Default Values






























long



-2^63 to 2^63 –1 à 0L



double



0.0d



Int



-2^31 to 2^31 –1 à 0



float



0.0f



Short



-2^15 to 2^15 –1 à 0



Boolean



false



Byte



-2^7 to 2^7 –1 à 0



char



0 to 2^7 –1 à null character (or) ‘\u 0000’




Q) Byte code & JIT compiler & JVM & JRE & JDK
Byte code is a highly optimized set of instructions. JVM is an interpreter for byte code. Translating a java program into byte code helps makes it much easier to run a program in a wide variety of environment.

JVM is an interpreter for byte code
JIT (Just In Time) is a part of JVM, it compiles byte code into executable code in real time, will increase the performance of the interpretations.
JRE
is an implementation of the Java Virtual Machine, which actually executes Java programs.
JDK is bundle of software that you can use to develop Java based software, Tools provided by JDK is

(i) javac – compiler (ii) java – interpretor (iii) jdb – debugger (iv) javap - Disassembles


(v) appletviewer – Applets (vi) javadoc - documentation generator (vii) javah - 'C' header file generator



Q) Wrapper classes


Primitive data types can be converted into objects by using wrapper classes. These are java.lang.package.



Q) Does Java pass method arguments by value or by reference?


Java passes all arguments by value, not by reference



Q) Arguments & Parameters


While defining method, variable passed in the method are called parameters. While using those methods, values passed to those variables are called arguments.



Q) Public static void main (String [] args)


à We can overLoad the main() method.


à What if the main method is declared as “Private”?


The program compiles properly but at runtime it will give "Main method not public." Message


à What if the static modifier is removed from the signature of the main method?


Program compiles. But at runtime throws an error "NoSuchMethodError".


à We can write “static public void” instead of “public static void” but not “public void static”.


à Protected static void main(), static void main(), private static void main() are also valid.


à If I do not provide the String array as the argument to the method?


Program compiles but throws a runtime error "NoSuchMethodError".


à If no arguments on the command line, String array of Main method will be empty or null?


It is empty. But not null.


à Variables can have the same name as a method or a class


 


Q) Can an application have multiple classes having main() method?


A) Yes it is possible. While starting the application we mention the class name to be run. The JVM will look for the Main method only in the class whose name you have mentioned. Hence there is not conflict amongst the multiple classes having main method.


 


Q) Can I have multiple main methods in the same class?


A) No the program fails to compile. The compiler says that the main method is already defined in the class.



Q) Constructor


The automatic initialization is performed through the constructor, constructor has same name has class name. Constructor has no return type not even void. We can pass the parameters to the constructor. this() is used to invoke a constructor of the same class. Super() is used to invoke a super class constructor. Constructor is called immediately after the object is created before the new operator completes.



à Constructor can use the access modifiers public, protected, private or have no access modifier


à Constructor can not use the modifiers abstract, static, final, native, synchronized or strictfp


à Constructor can be overloaded, we cannot override.


à You cannot use this() and Super() in the same constructor.




Class A(

A(){

System.out.println(“hello”);


}}



Class B extends A {


B(){


System.out.println(“friend”);


}}



Class print {


Public static void main (String args []){


B b = new B();


}


o/p:- hello friend



Q) Diff Constructor & Method











































Constructor



Method



Use to instance of a class


Grouping java statement


No return type



Void (or) valid return type



Same name as class name



As a name except the class method name, begin with lower case.



“This” refer to another constructor in the same class



Refers to instance of class


“Super” to invoke the super class constructor



Execute an overridden method in the super class


Inheritance” cannot be inherited


Can be inherited



We can “overload” but we cannot “overridden


Can be inherited



Will automatically invoke when an object is created


Method has called explicitly




Q) Garbage collection


G.C is also called automatic memory management as JVM automatically removes the unused variables/objects (value is null) from the memory. User program cann't directly free the object from memory, instead it is the job of the garbage collector to automatically free the objects that are no longer referenced by a program. Every class inherits finalize() method from java.lang.Object, the finalize() method is called by garbage collector when it determines no more references to the object exists. In Java, it is good idea to explicitly assign null into a variable when no more in use, calling System.gc() and Runtime.gc(), JVM tries to recycle the unused objects, but there is no guarantee when all the objects will garbage collected. Garbage collection is a low-priority thread.


 



G.C is a low priority thread in java, G.C cannot be forced explicitly. JVM may do garbage collection if it is running short of memory. The call System.gc() does NOT force the garbage collection but only suggests that the JVM may make an effort to do garbage collection.
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->

Q) How an object becomes eligible for Garbage Collection?


A) An object is eligible for garbage collection when no object refers to it, An object also becomes eligible when its reference is set to null. The objects referred by method variables or local variables are eligible for garbage collection when they go out of scope.



Integer i = new Integer(7);
i =
null;

Q) Final, Finally, Finalize


Final: - When we declare a sub class a final the compiler will give error as “cannot subclass final class” Final to prevent inheritance and method overriding. Once to declare a variable as final it cannot occupy memory per instance basis.


à Final class cannot have static methods


à Final class cannot have abstract methods (Because of final class never allows any class to inherit it)


à Final class can have a final method.



Finally: - Finally create a block of code that will be executed after try catch block has completed. Finally block will execute whether or not an exception is thrown. If an exception is thrown, the finally block will execute even if no catch statement match the exception. Any time a method is about to return to the caller from inside try/catch block, via an uncaught exception or an explicit return statement, the finally clause is also execute.



Using System.exit() in try block will not allow finally code to execute



Finalize: - some times an object need to perform some actions when it is going to destroy, if an object holding some non-java resource such as file handle (or) window character font, these resources are freed before the object is going to destroy.



Q) Can we declare abstract method in final class?


A) It indicates an error to declare abstract method in final class. Because of final class never allows any class to inherit it.



Q) Can we declare final method in abstract class?


A) If a method is defined as final then we can’t provide the reimplementation for that final method in it’s derived classes i.e overriding is not possible for that method. We can declare final method in abstract class suppose of it is abstract too, then there is no used to declare like that.



Q) Superclass & Subclass


A super class is a class that is inherited whereas subclass is a class that does the inheriting



Q) How will u implement 1) polymorphism 2) multiple inheritance 3) multilevel inheritance in java?


A) Polymorphism – overloading and overriding


Multiple inheritances – interfaces.


Multilevel inheritance – extending class.



Q) Overloading & Overriding?


Overloading (Compile time polymorphism)


Define two or more methods within the same class (or) subclass that share the same name and their number of parameter, order of parameter & return type are different then the methods are said to be overloaded.


· Overloaded methods do not have any restrictions on what return type of Method (Return type are different) (or) exceptions can be thrown. That is something to worry about with overriding.


· Overloading is used while implementing several methods that implement similar behavior but for different data types.



Overriding (Runtime polymorphism)


 



When a method in a subclass has the same name, return type & parameters as the method in the super class then the method in the subclass is override the method in the super class.
The access modifier for the overriding method may not be more restrictive than the access modifier of the superclass method.

 


· If the superclass method is public, the overriding method must be public.


· If the superclass method is protected, the overriding method may be protected or public.


· If the superclass method is package, the overriding method may be packagage, protected, or public.


· If the superclass methods is private, it is not inherited and overriding is not an issue.


· Methods declared as final cannot be overridden.


 


The throws clause of the overriding method may only include exceptions that can be thrown by the superclass method, including its subclasses.



member method can be overriden, not member variable


class Parent{


int i = 0;


void amethod(){


System.out.println("in Parent");


}


}


class Child extends Parent{


int i = 10;


void amethod(){


System.out.println("in Child");


}


}


class Test{


public static void main(String[] args){


Parent p = new Child();


Child c = new Child();


System.out.print("i="+p.i+" ");


p.amethod ();


System.out.print("i="+c.i+" ");


c.amethod();


}


}


o/p: - i=0 in Child i=10 in Child



Q) Final variable


Once to declare a variable as final it cannot occupy memory per instance basis.



Q) Static block


Static block which exactly executed exactly once when the class is first loaded into JVM. Before going to the main method the static block will execute.


 


Q) Static variable & Static method


Static variables & methods are instantiated only once per class. In other words they are class variables, not instance variables. If you change the value of a static variable in a particular object, the value of that variable changes for all instances of that class.




Static methods can be referenced with the name of the class. It may not access the instance variables of that class, only its static variables. Further it may not invoke instance (non-static) methods of that class unless it provides them with some object.
a member is declared a static it can be accessed before any object of its class are created.
Instance variables declared as static are essentially global variables.
If you do not specify an initial value to an instance & Static variable a default value will be assigned automatically.
Methods declared as static have some restrictions they can access only static data, they can only call other static data, they cannot refer this or super.

Static methods cant be overriden to non-static methods.
Static methods is called by the static methods only, an ordinary method can call the static methods, but static methods cannot call ordinary methods.

 



 


 




Static methods are implicitly "final", because overriding is only done based on the type of the objects
They cannot refer “this” are “super” in any way.
Q) Class variable & Instance variable & Instance methods & class methods
Instance variable
à variables defined inside a class are called instance variables with multiple instance of class, each instance has a variable stored in separate memory location.
Class variables
à you want a variable to be common to all classes then we crate class variables. To create a class variable put the “static” keyword before the variable name.
Class methods
à we create class methods to allow us to call a method without creating instance of the class. To declare a class method use the “static” key word .
Instance methods
à we define a method in a class, in order to use that methods we need to first create objects of the class.
Q) Static methods cannot access instance variables why?
Static methods can be invoked before the object is created; Instance variables are created only when the new object is created. Since there is no possibility to the static method to access the instance variables. Instance variables are called called as non-static variables.



Q) String & StringBuffer
String is a fixed length of sequence of characters, String is immutable.


StringBuffer represent growable and writeable character sequence, StringBuffer is mutable which means that its value can be changed. It allocates room for 16-addition character space when no specific length is specified. Java.lang.StringBuffer is also a final class hence it cannot be sub classed. StringBuffer cannot be overridden the equals() method.



Q) Conversions



String to Int Conversion: -
int I = integer.valueOf(“24”).intValue();
int x = integer.parseInt(“433”);
float f = float.valueOf(23.9).floatValue();


Int to String Conversion :-


String arg = String.valueOf(10);



Q) Super()


Super() always calling the constructor of immediate super class, super() must always be the first statements executed inside a subclass constructor.


 


Q) What are different types of inner classes?



A) Nested top-level classes- If you declare a class within a class and specify the static modifier, the compiler treats the class just like any other top-level class. Any class outside the declaring class accesses the nested class with the declaring class name acting similarly to a package. e.g., outer.inner. Top-level inner classes implicitly have access only to static variables. There can also be inner interfaces. All of these are of the nested top-level variety.



Member classes - Member inner classes are just like other member methods and member variables and access to the member class is restricted, just like methods and variables. This means a public member class acts similarly to a nested top-level class. The primary difference between member classes and nested top-level classes is that member classes have access to the specific instance of the enclosing class.

Local classes - Local classes are like local variables, specific to a block of code. Their visibility is only within the block of their declaration. In order for the class to be useful beyond the declaration block, it would need to implement a more publicly available interface. Because local classes are not members the modifiers public, protected, private and static are not usable.

Anonymous classes - Anonymous inner classes extend local inner classes one level further. As anonymous classes have no name, you cannot provide a constructor.

Inner class inside method cannot have static members or blocks




Q) Which circumstances you use Abstract Class & Interface?
--> If you need to change your design make it an interface.
--> Abstract class provide some default behaviour, A.C are excellent candidates inside of application framework. A.C allow single inheritance model, which should be very faster.


Q) Abstract Class


Any class that contain one are more abstract methods must also be declared as an abstract, there can be no object of an abstract class, we cannot directly instantiate the abstract classes. A.C can contain concrete methods.


Any sub class of an Abstract class must either implement all the abstract methods in the super class or be declared itself as Abstract.


Compile time error occur if an attempt to create an instance of an Abstract class.


You cannot declare “abstract constructor” and “abstract static method”.


An “abstract method” also declared private, native, final, synchronized, strictfp, protected.


Abstract class can have static, final method (but there is no use).


Abstract class have visibility public, private, protected.


By default the methods & variables will take the access modifiers is , which is accessibility as package.


An abstract method declared in a non-abstract class.


An abstract class can have instance methods that implement a default behavior.


A class can be declared abstract even if it does not actually have any abstract methods. Declaring such a class abstract indicates that the implementation is somehow incomplete and is meant to serve as a super class for one or more subclasses that will complete the implementation.


A class with an abstract method. Again note that the class itself is declared abstract, otherwise a compile time error would have occurred.




Abstract class A{
Public abstract callme();
Void callmetoo(){
}
}



class B extends A( void callme(){

}
}



class AbstractDemo{
public static void main(string args[]){
B b = new B();
b.callme();
b.callmetoo();
}
}

 Q) When we use Abstract class?
A)
Let us take the behaviour of animals, animals are capable of doing different things like flying, digging, Walking. But these are some common operations performed by all animals, but in a different way as well. When an operation is performed in a different way it is a good candidate for an abstract method.



Public Abstarctclass Animal{
Public void eat(food food) {
}
public void sleep(int hours) {
}
public abstract void makeNoise()
}



public Dog extends Animal
{
public void makeNoise() {
System.out.println(“Bark! Bark”);
}
}



public Cow extends Animal
{
public void makeNoise() {
System.out.println(“moo! moo”);
}
}

Q) Interface 
Interface is similar to class but they lack instance variable, their methods are declared with out any body. Interfaces are designed to support dynamic method resolution at run time. All methods in interface are implicitly

abstract
, even if the abstract modifier is omitted. Interface methods have no implementation;



Interfaces are useful for?
a) Declaring methods that one or more classes are expected to implement
b) Capturing similarities between unrelated classes without forcing a class relationship.

 



 




c) Determining an object's programming interface without revealing the actual body of the class.Why Interfaces?
“ one interface multiple methods “ signifies the polymorphism concept.
Interface has visibility public.
Interface can be extended & implemented.
A
n interface body may contain constant declarations, abstract method declarations, inner classes and inner interfaces.
All methods of an interface are implicitly Abstract, P
ublic, even if the public modifier is omitted.
An interface methods cannot be declared
protected, private, strictfp, native or synchronized.
All Variables are implicitly final, public, static fields.
A compile time error occurs if an interface has a simple name the same as any of it's enclosing classes or interfaces.
An Interface can only declare constants and instance methods, but cannot implement default behavior.

top-level
interfaces may only be declared public, inner interfaces may be declared private and protected but only if they are defined in a class.
A class can only extend one other class.
A class may implements more than one interface.
Interface can extend more than one interface.
Interface A
{
final static float pi = 3.14f;
}

 



class B implements A
{
public float compute(float x, float y) {
return(x*y);
}
}

 



class test{
public static void main(String args[])
{
A a = new B();
a.compute();
}
}



Q) Diff Interface & Abstract Class?
A.C may have some executable methods and methods left unimplemented. Interface contains no implementation code.
An A.C can have nonabstract methods. All methods of an Interface are abstract.
An A.C can have instance variables. An Interface cannot.
An A.C must have subclasses whereas interface can't have subclasses
An A.C can define constructor. An Interface cannot.
An A.C can have any visibility: public, private, protected. An Interface visibility must be public (or) none.
An
A.C can have instance methods that implement a default behavior. An Interface can only declare constants and instance methods, but cannot implement default behavior.



Q) What is the difference between Interface and class?
A class has instance variable and an Interface has no instance variables.
Objects can be created for classes where as objects cannot be created for interfaces.
All methods defined inside class are concrete. Methods declared inside interface are without any body.

 



Q) What is the difference between Abstract class and Class?
Classes are fully defined. Abstract classes are not fully defined (incomplete class)
Objects can be created for classes, there can be no objects of an abstract class.



Q) What are some alternatives to inheritance?
A)
Delegation is an alternative to inheritance. Delegation means that you include an instance of another class as an instance variable, and forward messages to the instance. It is often safer than inheritance because it forces you to think about each message you forward, because the instance is of a known class, rather than a new class, and because it doesn’t force you to accept all the methods of the super class: you can provide only the methods that really make sense. On the other hand, it makes you write more code, and it is harder to re-use (because it is not a subclass).


 



Q) Serializable & Externalizable
Serializable --> is an interface that extends serializable interface and sends data into streams in compressed format. It has 2 methods writeExternal(objectOutput out), readExternal(objectInput in).
Externalizable
is an Interface that extends Serializable Interface. And sends data into Streams in Compressed Format. It has two methods, writeExternal(ObjectOuput out) and readExternal(ObjectInput in)



Q) Internalisation & Localization
Internalisation -- Making a programme to flexible to run in any locale called internalisation.
Localization -- Making a programme to flexible to run in a specific locale called Localization.



Q) Serialization
Serialization is the process of writing the state of the object to a byte stream, this is useful when ever you want to save the state of your programme to a persistence storage area.



Q) Synchronization
Synchronization is a process of controlling the access of shared resources by the multiple threads in such a manner that only one thread can access one resource at a time. (Or) When 2 are more threads need to access the shared resources they need to some way ensure that the resources will be used by only one thread at a time. This process which is achieved is called synchronization.


(i) Ex: - Synchronizing a function:
public synchronized void Method1 () {
}
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->(i) Ex: - Synchronizing a block of code inside a function:
public myFunction (){
synchronized (this) {
}
}

 



(iii) Ex: - public Synchronized void main(String args[])
But this is not the right approach because it means servlet can handle one request at a time.

 



(iv) Ex: - public Synchronized void service()
Servlet handle one request at a time in a serialized manner

 



Q) Different level of locking using Synchronization?
A)
Class level, Object level, Method level, Block level

 



Q) Monitor
A monitor is a mutex, once a thread enter a monitor, all other threads must wait until that thread exist the monitor.

 



Q) Diff = = and .equals()?
A)
== Compare object references whether they refer to the sane instance are not.
equals ()
method compare the characters in the string object.



StringBuffer sb1 = new StringBuffer("Amit");
StringBuffer sb2= new StringBuffer("Amit");
String s1 = "Amit";
String s2 = "Amit";
String s3 = new String("abcd");
String s4 = new String("abcd");
String ss1 = "Amit";



String s1 = "abc";
String s2 = new String("abc");

s1 == s2 F
s1.equals(s2))


Q) Marker Interfaces (or) Tagged Interfaces :-
An Interface with no methods. Is called marker Interfaces, eg. Serializable, SingleThread Model, Cloneable.


Q) URL Encoding & URL Decoding
URL Encoding is the method of replacing all the spaces and other extra characters into their corresponding Hex Characters and URL Decoding is the reverse process converting all Hex Characters back their normal form.


Q) URL & URLConnection
URL is to identify a resource in a network, is only used to read something from the network.
URL url = new URL(protocol name, host name, port, url specifier)

URLConnection can establish communication between two programs in the network.
URL hp = new URL(“www.yahoo.com”);
URLConnection con = hp.openConnection();



Q) Runtime class
Runtime class encapsulate the run-time environment. You cannot instantiate a Runtime object. You can get a reference to the current Runtime object by calling the static method Runtime.getRuntime()


Runtime r = Runtime.getRuntime()
Long mem1;
Mem1 = r.freeMemory();
Mem1 = r.totalMemory();


Q) Execute other programs
You can use java to execute other heavy weight process on your multi tasking operating system, several form of exec() method allow you to name the programme you want to run.


Runtime r = Runtime.getRuntime();
Process p = null;
Try{
p = r.exce(“notepad”);
p.waiFor()
}



Q) System class System class hold a collection of static methods and variables. The standard input, output, error output of the java runtime are stored in the in, out, err variables.


Q) Native Methods Native methods are used to call subroutine that is written in a language other than java, this subroutine exist as executable code for the CPU.


Q) Cloneable Interface
Any class that implements the cloneable interface can be cloned, this interface defines no methods. It is used to indicate that a class allow a bit wise copy of an object to be made.


Q) Clone
Generate a duplicate copy of the object on which it is called. Cloning is a dangerous action.


Q) Comparable Interface
Classes that implements comparable contain objects that can be compared in some meaningful manner. This interface having one method compare the invoking object with the object. For sorting comparable interface will be used.
Ex:- int compareTo(Object obj)


Q) Class
Class encapsulate the run-time state of an object or interface. Methods in this class are
























static Class forName(String name) throws ClassNotFoundException



getClass()



getClassLoader()



getConstructor()



getField()



getDeclaredFields()



getMethods()



getDeclearedMethods()



getInterface()



getSuperClass()



Q) java.jlang.Reflect (package)
Reflection is the ability of software to analyse it self, to obtain information about the field, constructor, methods & modifier of class. You need this information to build software tools that enables you to work with java beans components.


Q) InstanceOf Instanceof means by which your program can obtain run time type information about an object.
Ex:- A a = new A();

a.instanceOf A;

Q) Java pass arguments by value are by reference? A) By value

Q) Java lack pointers how do I implements classic pointer structures like linked list? A) Using object reference.

Q) java. Exe Microsoft provided sdk for java, which includes “jexegentool”. This converts class file into a “.Exec”  form. Only disadvantage is user needs a M.S java V.M installed.

Q) Bin & Lib in jdk?
Bin contains all tools such as javac, appletviewer and awt tool.
Lib contains API and all packages.

Sunday, May 25, 2008

SQL Server 2005 Server-Level Fixed Roles

SQL Server 2005 Server-Level Fixed Roles

SQL Server 2005 Server-Level Fixed Roles


Fixed server roles are server-wide in their scope. Each member of a fixed server role can add other logins to that same role


The fixed server roles are:



* bulkadmin
* dbcreator
* diskadmin
* processadmin
* securityadmin



SQL Server Fixed Database-Level Roles

SQL Server Fixed Database-Level Roles


Fixed database roles are defined at the database level and exist in each database. Members of the db_owner and db_securityadmin database roles can manage fixed database role membership; however, only members of the db_owner database role can add members to the db_owner fixed database role.


The fixed database roles are the following:
* db_accessadmin
* db_backupoperator
* db_datareader
* db_datawriter
* db_ddladmin


For the full version of article please visit http://sqldbpool.blogspot.com/

Wednesday, May 14, 2008

Oracle Databse Questions and Answers


  1. What is Log Switch? - The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.

  2. What is On-line Redo Log? - The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.

  3. Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace? - All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.

  4. What are the steps involved in Database Startup? - Start an instance, Mount the Database and Open the Database.

  5. What are the steps involved in Instance Recovery? - Rolling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held by transactions in process at the time of the failure. Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.

  6. Can Full Backup be performed when the database is open? - No.

  7. What are the different modes of mounting a Database with the Parallel Server? - Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.

  8. What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode? - Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.

  9. What are the steps involved in Database Shutdown? - Close the Database, Dismount the Database and Shutdown the Instance.

  10. What is Archived Redo Log? - Archived Redo Log consists of Redo Log files that have archived before being reused.

  11. What is Restricted Mode of Instance Startup? - An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.

  12. What is Partial Backup? - A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

  13. What is Mirrored on-line Redo Log? - A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks, changes made to one member of the group are made to all members.

  14. What is Full Backup? - A full backup is an operating system backup of all data files, on- line redo log files and control file that constitute ORACLE database and the parameter.

  15. Can a View based on another View? - Yes.

  16. Can a Tablespace hold objects from different Schemes? - Yes.

  17. Can objects of the same Schema reside in different tablespaces? - Yes.

  18. What is the use of Control File? - When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

  19. Do View contain Data? - Views do not contain or store data.

  20. What are the Referential actions supported by FOREIGN KEY integrity constraint? - UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.

  21. What are the type of Synonyms? - There are two types of Synonyms Private and Public

  22. What is a Redo Log? - The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

  23. What is an Index Segment? - Each Index has an Index segment that stores all of its data.

  24. Explain the relationship among Database, Tablespace and Data file.? - Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace

  25. What are the different type of Segments? - Data Segment, Index Segment, Rollback Segment and Temporary Segment.

  26. What are Clusters? - Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

  27. What is an Integrity Constrains? - An integrity constraint is a declarative way to define a business rule for a column of a table.

  28. What is an Index? - An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

  29. What is an Extent? - An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

  30. What is a View? - A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

  31. What is Table? - A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

  32. What is a Synonym? - A synonym is an alias for a table, view, sequence or program unit.

  33. What is a Sequence? - A sequence generates a serial list of unique numbers for numerical columns of a database’s tables.

  34. What is a Segment? - A segment is a set of extents allocated for a certain logical structure.

  35. What is schema? - A schema is collection of database objects of a User.

  36. Describe Referential Integrity? - A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.

  37. What is Hash Cluster? - A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.

  38. What is a Private Synonyms? - A Private Synonyms can be accessed only by the owner.

  39. What is Database Link? - A database link is a named object that describes a “path” from one database to another.

  40. What is a Tablespace? - A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together

  41. What is Rollback Segment? - A Database contains one or more Rollback Segments to temporarily store “undo” information.

  42. What are the Characteristics of Data Files? - A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a tablespace.

  43. How to define Data Block size? - A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.

  44. What does a Control file Contain? - A Control file records the physical structure of the database. It contains the following information. Database Name Names and locations of a database’s files and redolog files. Time stamp of database creation.

  45. What is difference between UNIQUE constraint and PRIMARY KEY constraint? - A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls. 47.What is Index Cluster? - A Cluster with an index on the Cluster Key 48.When does a Transaction end? - When it is committed or Rollbacked.

  46. What is the effect of setting the value “ALL_ROWS” for OPTIMIZER_GOAL parameter of the ALTER SESSION command? - What are the factors that affect OPTIMIZER in choosing an Optimization approach? - Answer The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

  47. What is the effect of setting the value “CHOOSE” for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command? - The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.

  48. What is the function of Optimizer? - The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

  49. What is Execution Plan? - The combinations of the steps the optimizer chooses to execute a statement is called an execution plan.

  50. What are the different approaches used by Optimizer in choosing an execution plan? - Rule-based and Cost-based.

Thursday, May 8, 2008

SQL Server 2005 Interview Questions

Download SQL Server Interview Question


• If I want to see what fields a table is made of, and what the sizes of the
fields are, what option do I have to look for?
Sp_Columns 'TableName'

• What is a query?
A request for information from a database. There are three general methods for posing queries:
# Choosing parameters from a menu: In this method, the database system presents a list of parameters from which you can choose. This is perhaps the easiest way to pose a query because the menus guide you, but it is also the least flexible.
# Query by example (QBE): In this method, the system presents a blank record and lets you specify the fields and values that define the query.
# Query language: Many database systems require you to make requests for information in the form of a stylized query that must be written in a special query language. This is the most complex method because it forces you to learn a specialized language, but it is also the most powerful.

• What is the purpose of the model database?
It works as Template Database for the Create Database Syntax

• What is the purpose of the master database?
Master database keeps the information about sql server configuration, databases users etc

• What is the purpose of the tempdb database?
Tempdb database keeps the information about the temporary objects (#TableName, #Procedure). Also the sorting, DBCC operations are performed in the TempDB

• What is the purpose of the USE command?
Use command is used for to select the database. For i.e Use Database Name

• If you delete a table in the database, will the data in the table be deleted too?
Yes

• What is the Parse Query button used for? How does this help you?
Parse query button is used to check the SQL Query Syntax

• Tables are created in a ____________________ in SQL Server 2005.
resouce database(System Tables)

• What is usually the first word in a SQL query?
SELECT

• Does a SQL Server 2005 SELECT statement require a FROM?
NO

• Can a SELECT statement in SQL Server 2005 be used to make an assignment? Explain with examples.
Yes. Select @MyDate = GetDate()

• What is the ORDER BY used for?
Order By clause is used for sorting records in Ascending or Descending order

• Does ORDER BY actually change the order of the data in the tables or does it just
change the output?

Order By clause change only the output of the data

• What is the default order of an ORDER BY clause?
Ascending Order

• What kind of comparison operators can be used in a WHERE clause?











































OperatorMeaning
= (Equals)Equal to
> (Greater Than)Greater than
< (Less Than)Less than
>= (Greater Than or Equal To)Greater than or equal to
<= (Less Than or Equal To)Less than or equal to
<> (Not Equal To)Not equal to
!= (Not Equal To)Not equal to (not SQL-92 standard)
!< (Not Less Than)Not less than (not SQL-92 standard)
!> (Not Greater Than)Not greater than (not SQL-92 standard)

• What are four major operators that can be used to combine conditions on a WHERE
clause?


OR, AND, IN and BETWEEN

• What are the logical operators?















































OperatorMeaning
ALLTRUE if all of a set of comparisons are TRUE.
ANDTRUE if both Boolean expressions are TRUE.
ANYTRUE if any one of a set of comparisons are TRUE.
BETWEENTRUE if the operand is within a range.
EXISTSTRUE if a subquery contains any rows.
INTRUE if the operand is equal to one of a list of expressions.
LIKETRUE if the operand matches a pattern.
NOTReverses the value of any other Boolean operator.
ORTRUE if either Boolean expression is TRUE.
SOMETRUE if some of a set of comparisons are TRUE.

•In a WHERE clause, do you need to enclose a text column in quotes? Do you need to enclose a numeric column in quotes?
Enclose Text in Quotes (Yes)
Enclose Number in Quotes (NO)

• Is a null value equal to anything? Can a space in a column be considered a null value? Why or why not?
No NULL value means nothing. We can't consider space as NULL value.

• Will COUNT(column) include columns with null values in its count?
Yes, it will include the null column in count

• What are column aliases? Why would you want to use column aliases? How can you embed blanks in column aliases?
You can create aliases for column names to make it easier to work with column names, calculations, and summary values. For example, you can create a column alias to:
* Create a column name, such as "Total Amount," for an expression such as (quantity * unit_price) or for an aggregate function.
* Create a shortened form of a column name, such as "d_id" for "discounts.stor_id."
After you have defined a column alias, you can use the alias in a Select query to specify query output

• What are table aliases?
Aliases can make it easier to work with table names. Using aliases is helpful when:
* You want to make the statement in the SQL Pane shorter and easier to read.
* You refer to the table name often in your query — such as in qualifying column names — and want to be sure you stay within a specific character-length limit for your query. (Some databases impose a maximum

length for queries.)
* You are working with multiple instances of the same table (such as in a self-join) and need a way to refer to one instance or the other.

• What are table qualifiers? When should table qualifiers be used?
[@table_qualifier =] qualifier
Is the name of the table or view qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

• Are semicolons required at the end of SQL statements in SQL Server 2005?
No it is not required

• Do comments need to go in a special place in SQL Server 2005?

No its not necessary

• When would you use the ROWCOUNT function versus using the WHERE clause?

Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.
Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:
* Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
* Preserve @@ROWCOUNT from the previous statement execution.
* Reset @@ROWCOUNT to 0 but do not return the value to the client.
Statements that make a simple assignment always set the @@ROWCOUNT value to 1.

• Is SQL case-sensitive? Is SQL Server 2005 case-sensitive?

No both are not case-sensitive. Case sensitivity depends on the collation you choose.
If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

CREATE TABLE mytable
(
mycolumn VARCHAR(10)
)
GO

SET NOCOUNT ON

INSERT mytable VALUES('Case')
GO

SELECT mycolumn FROM mytable WHERE mycolumn='Case'
SELECT mycolumn FROM mytable WHERE mycolumn='caSE'
SELECT mycolumn FROM mytable WHERE mycolumn='case'

You can alter your query by forcing collation at the column level:

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE'

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'case'

SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case'

-- if myColumn has an index, you will likely benefit by adding
-- AND myColumn = 'case'

• What is a synonym? Why would you want to create a synonym?

SYNONYM is a single-part name that can replace a two, three or four-part name in many SQL statements. Using SYNONYMS in RDBMS cuts down on typing.
SYNONYMs can be created for the following objects:

* Table
* View
* Assembly (CLR) Stored Procedure
* Assembly (CLR) Table-valued Function
* Assembly (CLR) Scalar Function
* Assembly Aggregate (CLR) Aggregate Functions
* Replication-filter-procedure
* Extended Stored Procedure
* SQL Scalar Function
* SQL Table-valued Function
* SQL Inline-table-valued Function
* SQL Stored Procedure

Syntax
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >

< object > :: =
{
[ server_name.[ database_name ] . [ schema_name_2 ].| database_name . [ schema_name_2 ].| schema_name_2. ] object_name
}

• Can a synonym name of a table be used instead of a table name in a SELECT statement?
Yes

• Can a synonym of a table be used when you are trying to alter the definition of a table?
Not Sure will try

• Can you type more than one query in the query editor screen at the same time?
Yes we can.

• While you are inserting values into a table with the INSERT INTO .. VALUES option, does the order of the columns in the INSERT statement have to be the same as the order of the columns in the table?
Not Necessary

• While you are inserting values into a table with the INSERT INTO .. SELECT option, does the order of the columns in the INSERT statement have to be the same as the order of the columns in the table?
Yes if you are not specifying the column names in the insert clause, you need to maintain the column order in SELECT statement

• When would you use an INSERT INTO .. SELECT option versus an INSERT INTO .. VALUES option? Give an example of each.
INSERT INTO .. SELECT is used insert data in to table from diffrent tables or condition based insert
INSERT INTO .. VALUES you have to specify the insert values

• What does the UPDATE command do?
Update command will modify the existing record

• Can you change the data type of a column in a table after the table has been created? If so,which command would you use?

Yes we can. Alter Table Modify Column

• Will SQL Server 2005 allow you to reduce the size of a column?
Yes it allows

• What integer data types are available in SQL Server 2005?

Exact-number data types that use integer data.




























Data typeRangeStorage
bigint -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)8 Bytes
int -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)4 Bytes
smallint -2^15 (-32,768) to 2^15-1 (32,767)2 Bytes
tinyint 0 to 2551 Byte


• What is the default value of an integer data type in SQL Server 2005?

NULL

• What is the difference between a CHAR and a VARCHAR datatype?

CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters. The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).

You should use CHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.

• Does Server SQL treat CHAR as a variable-length or fixed-length column?
SQL Server treats CHAR as fixed length column

• If you are going to have too many nulls in a column, what would be the best data type to use?
Variable length columns only use a very small amount of space to store a NULL so VARCHAR datatype is the good option for null values

• When columns are added to existing tables, what do they initially contain?

The column initially contains the NULL values

• What command would you use to add a column to a table in SQL Server?

ALTER TABLE tablename ADD column_name DATATYPE

• Does an index slow down updates on indexed columns?
Yes

• What is a constraint?

Constraints in Microsoft SQL Server 2000/2005 allow us to define the ways in which we can automatically enforce the integrity of a database. Constraints define rules regarding permissible values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using triggers, stored procedures, rules, and defaults, as a method of implementing data integrity rules. The query optimizer also uses constraint definitions to build high-performance query execution plans.

• How many indexes does SQL Server 2005 allow you to have on a table?
250 indices per table

• What command would you use to create an index?
CREAT INDEX INDEXNAME ON TABLE(COLUMN NAME)

• What is the default ordering that will be created by an index (ascending or descending)?
Clustered indexes can be created in SQL Server databases. In such cases the logical order of the index key values will be the same as the physical order of rows in the table.
By default it is ascending order, we can also specify the index order while index creation.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )

• How do you delete an index?
DROP INDEX authors.au_id_ind

• What does the NOT NULL constraint do?
Constrain will not allow NULL values in the column

• What command must you use to include the NOT NULL constraint after a table has already been created?
DEFAULT, WITH CHECK or WITH NOCHECK

• When a PRIMARY KEY constraint is included in a table, what other constraints does this imply?
Unique + NOT NULL

• What is a concatenated primary key?

Each table has one and only one primary key, which can consist of one or many columns. A concatenated primary key comprises two or more columns. In a single table, you might find several columns, or groups of columns, that might serve as a primary key and are called candidate keys. A table can have more than one candidate key, but only one candidate key can become the primary key for that table

• How are the UNIQUE and PRIMARY KEY constraints different?
A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

• What is a referential integrity constraint? What two keys does the referential integrity constraint usually include?

Referential integrity in a relational database is consistency between coupled tables. Referential integrity is usually enforced by the combination of a primary key or candidate key (alternate key) and a foreign key. For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. The relational database management system (RDBMS) enforces referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used would be determined by the referential integrity constraint, as defined in the data dictionary.

• What is a foreign key?

FOREIGN KEY constraints identify the relationships between tables.
A foreign key in one table points to a candidate key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no candidate keys with that value. In the following sample, the order_part table establishes a foreign key referencing the part_sample table defined earlier. Usually, order_part would also have a foreign key against an order table, but this is a simple example.

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO

You cannot insert a row with a foreign key value (except NULL) if there is no candidate key with that value. The ON DELETE clause controls what actions are taken if you attempt to delete a row to which existing foreign keys point. The ON DELETE clause has two options:

NO ACTION specifies that the deletion fails with an error.

CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also deleted.
The ON UPDATE clause defines the actions that are taken if you attempt to update a candidate key value to which existing foreign keys point. It also supports the NO ACTION and CASCADE options.


• What does the ON DELETE CASCADE option do?

ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.

ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables.


• What does the ON UPDATE NO ACTION do?

ON DELETE NO ACTION
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE is rolled back.

ON UPDATE NO ACTION
Specifies that if an attempt is made to update a key value in a row whose key is referenced by foreign keys in existing rows in other tables, an error is raised and the UPDATE is rolled back.

• Can you use the ON DELETE and ON UPDATE in the same constraint?
Yes we can.
CREATE TABLE part_sample
(part_nmbr int PRIMARY KEY,
part_name char(30),
part_weight decimal(6,2),
part_color char(15) )

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION ON UPDATE NO ACTION,
qty_ordered int)
GO

Download SQL Server Interview Question

Database Certifications

Database Certifications


The Information Technology industry is experiencing a certification craze! Whether you're looking for a valuable career credential or a structured approach to learning, database certifications are a good place to start.


Database Certification Spotlight: Office Specialist
If you use Microsoft Access as a regular part of your job, or you hope to gain employment in the field of user-level databases, you may wish to consider the Microsoft Office Specialist (MOS) program's Accesstrack. This program (formerly known as the Microsoft Office User Specialist or MOUS program) allows users of the popular desktop database to prove their knowledge in a structured exam environment.

Database Certification Spotlight: OCP
Want to prove your Oracle Database Administration prowess to the world? Check out the Oracle 9i certification program!


Database Certification Spotlight: MCDBA
Interested in earning your Microsoft Certified Database Administrator (MCDBA) credential? Get off to the right start with this informative article!

Certification Spotlight: MCDBA
Interested in earning your Microsoft Certified Database Administrator (MCDBA) credential? Get off to the right start with this informative article!

Certification Spotlight: Microsoft Office Specialist
Looking to prove that you know your stuff when it comes to Microsoft Access? The Microsoft Office Specialist program may be just what you're looking for!


Certification Spotlight: Oracle Certified Professional
Want to prove your Oracle Database Administration prowess to the world? Check out the Oracle 9i certification program!

Get Certified!
Are you a database professional seeking to polish your resume in the hopes of landing a better gig? Perhaps you're just getting started in database administration and you'd like to establish your credentials in the field.

Brainbench Certification Programs
Brainbench.com offers free online certfication programs for numerous Microsoft and Oracle database product lines as well as general database techniques.

Cramsessions for Microsoft Certifications
Cramsession.com's Microsoft exam study sheets provide all of the information you need to pass your certification exam in a brief summary. If you're planning to take a Microsoft test, definitley give these guides a look!

Cramsessions for Oracle Certifications
Cramsession.com brings you concise study sheets for all of the Oracle certification exams. These are a definite "must read" for certification candidates.

Microsoft Certified Database Administrator
Think you're an expert at SQL Server? The MCDBA program gives you the chance to prove your mettle to current and future employers. Check out this link for the program details.

IBM DB2 Certifications
Obtain details on the various professional certification programs available from IBM for the DB2 Universal Database. Programs include database administrator, business intelligence and applications development.

Oracle Certified Professional Program Fact Sheet
This concise fact sheet from Oracle offers an overview of the various tracks in the OCP program, training options and exam information. A definite "must read" before beginning your quest for Oracle certification.

Sybase Professional Certification Programs
Detailed information on the Sybase database professional and Sybase PowerBuilder developer programs direct from the source.



Database Mirroring Operating Modes

Database Mirroring Operating Modes


Database Mirroring is configured for three different operating modes. These modes are high availability, high performance and high protection. Each offers a different set of functionality and must be understood in order to select the appropriate configurations.




  • High Availability Operating Mode

    • The High Availability Operating Mode provides durable, synchronous transfer of data between the principal and mirror instances including automatic failure detection and failover. With this functionality comes a performance hit. This is because a transaction is not considered committed until SQL Server has successfully committed it to the transaction log on both the principal and the mirror database. As the distance between the principal and the mirror increases, the performance impact also increases.

    • In addition, there is an continuous ping process between all three nodes (if a witness is used) to detect failover. If witness server is not visible from the mirror, you must either reconfigure the operating mode for the database mirroring session or turn off the witness. Alternatively, you can manually fail over a database mirroring session at the mirror in High Availability Mode by issuing the ALTER DATABASE SET PARTNER FAILOVER command at the principal. The same command can also be issued if you have to take principal down for maintenance.



  • High Performance Operating Mode

    • With the High Performance Operating Mode the overall architecture acts as a warm standby and does not support automatic failure detection or failover. The data transfer between the principal and mirror instances is asynchronous. As such, this mode provides better performance and permits geographic separate between the principal and mirror SQL Server instances. Unfortunately, this mode increases latency and can lead to greater data loss in the event of primary database failure if it is not managed properly.



  • High Protection Operating Mode

    • The High Protection Operating Mode operates very similar to the High Availability Mode except the failover and promotion (mirror to principal) process is manual. With this mode the data transfer is synchronous. This mode is typically not recommended except in the event of replacing the existing witness SQL Server. After replacing or recovering the witness SQL Server, the operating mode should be changed back to High Availability Operating Mode



SQL Server 2005 Editions

SQL Server 2005 Editions


<!--[if !supportLists]-->ü <!--[endif]-->SQL Server Express


<!--[if !supportLists]-->ü <!--[endif]-->SQL Server Workgroup


<!--[if !supportLists]-->ü <!--[endif]-->SQL Server Developer


<!--[if !supportLists]-->ü <!--[endif]-->SQL Server Standard


<!--[if !supportLists]-->ü <!--[endif]-->SQL Server Enterprise


<!--[if !supportLists]-->ü <!--[endif]-->SQL Server Compact

System Databases in SQL Server 2005



SQL Server System Databases
  • Master

    • Purpose - Core system database to manage the SQL Server instance. In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema. In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.

    • Prominent Functionality

      • Per instance configurations

      • Databases residing on the instance

      • Files for each database

      • Logins

      • Linked\Remote servers

      • Endpoints



    • Additional Information

      • The first database in the SQL Server startup process

      • In SQL Server 2005, needs to reside in the same directory as the Resource database





  • Resource

    • Purpose - The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.

    • Prominent Functionality

      • System object definition



    • Additional Information

      • Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects

      • Prior to SQL Server 2005 the system related data was stored in the master database

      • Read-only database that is not accessible via the SQL Server 2005 tool set

      • The database ID for the Resource database is 32767

      • The Resource database does not have an entry in master.sys.databases





  • TempDB

    • Purpose - Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.

    • Prominent Functionality

      • Manage temporary objects listed in the purpose above



    • Additional Information

      • Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state





  • Model

    • Purpose - Template database for all user defined databases

    • Prominent Functionality

      • Objects

      • Columns

      • Users



    • Additional Information

      • User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases

      • The database configurations such as the recovery model for the Model database are applied to future user defined databases





  • MSDB

    • Purpose - Primary database to manage the SQL Server Agent configurations

    • Prominent Functionality

      • SQL Server Agent Jobs, Operators and Alerts

      • DTS Package storage in SQL Server 7.0 and 2000

      • SSIS Package storage in SQL Server 2005



    • Additional Information

      • Provides some of the configurations for the SQL Server Agent service

      • For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database





  • Distribution

    • Purpose - Primary data to support SQL Server replication

    • Prominent Functionality

      • Database responsible for the replication meta data

      • Supports the data for transaction replication between the publisher and subscriber(s)





  • ReportServer

    • Purpose - Primary database for Reporting Services to store the meta data and object definitions

    • Prominent Functionality

      • Reports security

      • Job schedules and running jobs

      • Report notifications

      • Report execution history





  • ReportServerTempDB

    • Purpose - Temporary storage for Reporting Services

    • Prominent Functionality

      • Session information

      • Cache