Category: Career & Interviews

  • DBA Roles and Responsibilities

    Click Me to Check How to be DBA?

    DBA Responsibilities

    • Installation, configuration and upgrading of Microsoft SQL Server/MySQL/Oracle server software and related products.
    • Evaluate MSSQL/MySQL/Oracle features and MSSQL/MySQL/Oracle related products.
    • Establish and maintain sound backup and recovery policies and procedures.
    • Take care of the Database design and implementation.
    • Implement and maintain database security (create and maintain users and roles, assign privileges).
    • Database tuning and performance monitoring.
    • Application tuning and performance monitoring.
    • Setup and maintain documentation and standards.
    • Plan growth and changes (capacity planning).
    • Work as part of a team and provide 7×24 supports when required.
    • Do general technical trouble shooting and give consultation to development teams.
    • Interface with MSSQL/MySQL/Oracle for technical support.
    • ITIL Skill set requirement (Problem Management/Incident Management/Chain Management etc)

    Types of DBA

    1. Administrative DBA – Work on maintaining the server and keeping it running. Concerned with backups, security, patches, replication, etc. Things that concern the actual server software.
    2. Development DBA – works on building queries, stored procedures, etc. that meet business needs. This is the equivalent of the programmer. You primarily write T-SQL.
    3. Architect – Design schemas. Build tables, FKs, PKs, etc. Work to build a structure that meets the business needs in general. The design is then used by developers and development DBAs to implement the actual application.
    4. Data Warehouse DBA – Newer role, but responsible for merging data from multiple sources into a data warehouse. May have to design warehouse, but cleans, standardizes, and scrubs data before loading. In SQL Server, this DBA would use DTS heavily.
    5. OLAP DBA – Builds multi-dimensional cubes for decision support or OLAP systems. The primary language in SQL Server is MDX, not SQL here

    Application DBA- Application DBAs straddle the fence between the DBMS and the application software and are responsible for ensuring that the application is fully optimized for the database and vice versa. They usually manage all the application components that interact with the database and carry out activities such as application installation and patching, application upgrades, database cloning, building and running data cleanup routines, data load process management, etc.

    Download SQL Server Interview Question

  • Networking Interview Questions

    What is IPv6?

    Internet Protocol version 6 (IPv6) is a network layer IP standard used by electronic devices to exchange data across a packet-switched internetwork. It follows IPv4 as the second version of the Internet Protocol to be formally adopted for general use.  ipv6 it is a 128 bit size address. This is total 8 octants each octant size is 16 bits separated with “:”, it is in hexa decimal format. These 3 types:

    1. unicast address
    2. multicast address
    3. anycast address

    loopback address of ip v6 is ::1

    What is subnet?

    A subnet allows the flow of network traffic between hosts to be segregated based on a network configuration. By organizing hosts into logical groups, subnetting can improve network security and performance.

    What is Subnet Mask?

    A mask used to determine what subnet an IP address belongs to. An IP address has two components, the network address and the host address. For example, consider the IP address 150.215.017.009. Assuming this is part of a Class B network, the first two numbers (150.215) represent the Class B network address, and the second two numbers (017.009) identify a particular host on this network.

    Subnetting enables the network administrator to further divide the host part of the address into two or more subnets. In this case, a part of the host address is reserved to identify the particular subnet. This is easier to see if we show the IP address in binary format.

    What is Default Gateway?

    a gateway is a device on a network that acts as an entrance to another network. In more technical terms, a gateway is a routing device that knows how to pass traffic between different subnets and networks. A computer will know some routes (a route is the address of each node a packet must go through on the Internet to reach a specific destination), but not the routes to every address on the Internet. It won’t even know all the routes on the nearest subnets. A gateway will not have this information either, but will at least know the addresses of other gateways it can hand the traffic off to. Your default gateway is on the same subnet as your computer, and is the gateway your computer relies on when it doesn’t know how to route traffic.

    The default gateway is typically very similar to your IP address, in that many of the numbers may be the same. However, the default gateway is not your IP address.

    Describe how the DHCP lease is obtained.
    It’s a four-step process consisting of (a) IP request, (b) IP offer, © IP selection and (d) acknowledgement.

    What’s the difference between forward lookup and reverse lookup in DNS?
    Forward lookup is name-to-address, the reverse lookup is address-to-name.

    How can you recover a file encrypted using EFS? Use the domain recovery agent.

    What is LMHOSTS file?
    It’s a file stored on a host machine that is used to resolve NetBIOS to specific IP addresses.

    How can you force the client to give up the dhcp lease if you have access to the client PC?
    ipconfig /release

  • 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.

  • 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?

    Operator Meaning
    = (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?

    Operator Meaning
    ALL TRUE if all of a set of comparisons are TRUE.
    AND TRUE if both Boolean expressions are TRUE.
    ANY TRUE if any one of a set of comparisons are TRUE.
    BETWEEN TRUE if the operand is within a range.
    EXISTS TRUE if a subquery contains any rows.
    IN TRUE if the operand is equal to one of a list of expressions.
    LIKE TRUE if the operand matches a pattern.
    NOT Reverses the value of any other Boolean operator.
    OR TRUE if either Boolean expression is TRUE.
    SOME TRUE 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 type Range Storage
    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 255 1 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

  • SQL Server Interview Questions for DBA/Developer

    • What is a DDL, DML, DCL, TCL and DSPL concept in RDBMS world?
    The Data Definition Language (DDL) includes,

    CREATE TABLE – creates new database table
    ALTER TABLE – alters or changes the database table
    DROP TABLE – deletes the database table
    CREATE INDEX – creates an index or used as a search key
    DROP INDEX – deletes an index

    The Data Manipulation Language (DML) includes,

    SELECT – extracts data from the database
    UPDATE – updates data in the database
    DELETE – deletes data from the database
    INSERT INTO – inserts new data into the database

    The Data Control Language (DCL) includes,

    GRANT – gives access privileges to users for database
    REVOKE – withdraws access privileges to users for database

    The Transaction Control (TCL) includes,

    COMMIT – saves the work done
    ROLLBACK – restore the database to original since the last COMMIT

    DSPL – Database Stored Procedure Language came to relational databases relatively late in the game – and thus the languages used for triggers, event handlers, and stored procedures are completely different among the database vendors. Oracle’s PL/SQL is quite different even in statement syntax from SQL Server’s Transact SQL which in turn differs again from DB2’s Stored Procedure language. And of course given the underlying differences in DDL, DML, and DCL it is inevitable that the stored procedure languages would vary in content as well as syntax.

    Define candidate key, alternate key, composite key.
    A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

    A key formed by combining at least two or more columns is called composite key.

    How to Reset the Identity Values?

    You can set the identity values using 1. DBCC CHECKIND(TABLENAME,RESEED,0) 2. Truncate table.

    What are “GRANT”, “REVOKE’ and “DENY’ statements?

    GRANT
    Creates an entry in the security system that allows a user in the current database to work with data in the current database or execute specific Transact-SQL statements.
    Syntax
    Statement permissions:

    GRANT { ALL | statement [ ,…n ] }
    TO security_account [ ,…n ]

    Object permissions:

    GRANT
    { ALL [ PRIVILEGES ] | permission [ ,…n ] }
    {
    [ ( column [ ,…n ] ) ] ON { table | view }
    | ON { table | view } [ ( column [ ,…n ] ) ]
    | ON { stored_procedure | extended_procedure }
    | ON { user_defined_function }
    }
    TO security_account [ ,…n ]
    [ WITH GRANT OPTION ]
    [ AS { group | role } ]

    REVOKE
    Removes a previously granted or denied permission from a user in the current database.

    Syntax
    Statement permissions:

    REVOKE { ALL | statement [ ,…n ] }
    FROM security_account [ ,…n ]

    Object permissions:

    REVOKE [ GRANT OPTION FOR ]
    { ALL [ PRIVILEGES ] | permission [ ,…n ] }
    {
    [ ( column [ ,…n ] ) ] ON { table | view }
    | ON { table | view } [ ( column [ ,…n ] ) ]
    | ON { stored_procedure | extended_procedure }
    | ON { user_defined_function }
    }
    { TO | FROM }
    security_account [ ,…n ]
    [ CASCADE ]
    [ AS { group | role } ]

    DENY
    Creates an entry in the security system that denies a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships.

    Syntax
    Statement permissions:

    DENY { ALL | statement [ ,…n ] }
    TO security_account [ ,…n ]

    Object permissions:

    DENY
    { ALL [ PRIVILEGES ] | permission [ ,…n ] }
    {
    [ ( column [ ,…n ] ) ] ON { table | view }
    | ON { table | view } [ ( column [ ,…n ] ) ]
    | ON { stored_procedure | extended_procedure }
    | ON { user_defined_function }
    }
    TO security_account [ ,…n ]
    [ CASCADE ]

    • What is Stored Procedure?
    A stored procedure is a collection of T-SQL statements. The stored procedure stored in the system tables of the User Database in SQL Server. The system tables used in stored procedure is sysObjects, sysDepends and sysComments.

    Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data and stored procedure also returns the output parameter. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
    e.g. sp_help,sp_helpdb (Alt + F1), sp_renamedb, sp_depends etc.

    Stored Procedure can takes 1024 input and returns the 1024 output parameters.

    • What is Trigger?
    Trigger are used to enforce the business rules in the RDBMS. A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the RDBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed. The RDBMS automatically fires the trigger as a result of a data modification to the associated table.
    Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

    There are two types of trigger in SQL Server 1. After Trigger 2. Instead of Trigger

    Nested Trigger: Like the stored procedure trigger can also be nested upto 32 levels. A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger. For the nested trigger user has to define the execution order of the trigger.

    The trigger will create two magic tables inserted and deleted which contains the structure of table on which it excutes.

    • What is View?
    A view is one type of virtual tables which only stores the SELECT query without data. User can perform the Insert/Update/Delete operation on the view. View can give us the better security.

    User can define the index on views. The Instead of trigger can fire on the view.

    • What is Index?
    Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

    Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it’s row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

    If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
    • What is the difference between clustered and a non-clustered index?

    There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

    A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. SQL Server can create 249 Non-clustered index per table.

    • What is cursors?
    Cursors allow row-by-row prcessing of the resultsets. The system table used in the cursor operation is sysCursors. The cursor can be local or global.

    Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

    Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network round trip, where as a normal SELECT query makes only one round trip, however large the result set is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors.

    What is the use of DBCC commands?
    DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
    E.g. DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked.
    DBCC CHECKALLOC – To check that all pages in a db are correctly allocated.
    DBCC CHECKFILEGROUP – Checks all tables file group for any damage.

    • What is a Linked Server?
    Think of a Linked Server as an alias on your local SQL server that points to an external data source. This external data source can be Access, Oracle, Excel or almost any other data system that can be accessed by OLE or ODBC–including other MS SQL servers. An MS SQL linked server is similar to the MS Access feature of creating a “Link Table.”

    Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

    • What’s the difference between a primary key and a unique key?
    Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

    • What is a NOLOCK?
    Using the NOLOCK query optimizer hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. The result is dirty read/uncommited data read. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete.

    • What is difference between DELETE & TRUNCATE commands?
    Delete: Delete is logged operation. Trigger can fire on the delete operation. Delete can use the where clause. Can be used in foreign key relationship tables and remove the data from the child table if the ON DELETE CASCADE is specified.
    DELETE Can be Rolled back.
    DELETE is DML Command.
    DELETE does not reset identity of the table.

    TRUNCATE
    TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
    TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
    TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. Truncate resets the identity value.

    We cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
    Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
    TRUNCATE can be Rolled back if it is used between Begin/Commit/Rollback transactions.
    TRUNCATE is DDL Command.
    TRUNCATE Resets identity of the table.

    Difference between Function and Stored Procedure?
    UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Stored procedures cannot be.
    UDFs can return the table variable.

    Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

    We can not write the configuration statements in UDFs. UDs can return only one value whereas SPs can return 1024 output parameters.

    When is the use of UPDATE_STATISTICS command?
    This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.

    • What types of Joins are possible with Sql Server?
    Joins are used in queries to explain how different tables are related. Joins also let us select data from a table depending upon data from another table.
    Types of joins: SELF JOINs, MERGE JOINs, INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

    • What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
    Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. It is the good practice to use WHERE clause with the Group By for the better performance result.

    • What is SQL Profiler?

    It is a tool which help us to profiling the activities at the database level. It is the good practice to use the profiler from the different machine rather than the production machine.

    SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. We can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performance by executing too slowly.
    Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data iscollected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

    • What is User Defined Functions?
    User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

    • Which TCP/IP port does SQL Server run on? How can it be changed?

    SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number.both on client and the server.

    • What are the authentication modes in SQL Server? How can it be changed?
    Windows mode and mixed mode (SQL & Windows).
    • Where are SQL server users names and passwords are stored in sql server?
    They get stored in master db in the sysxlogins table.

    Which command using Query Analyzer will give you the version of SQL server and operating system?
    SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY(‘edition’), SELECT @@version

    • What is SQL server agent?

    It is one of the services provided by SQL Server. Used for the scheduling purpose. To start the this service from the dos prompt you can write net start sqlserveragent
    • What is @@ERROR?
    The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

    • What is Raiseerror?
    Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn’t change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NT application event log.

    • What is log shipping?
    Log shipping is the process of automating the backup of database and transaction log files on a
    production SQL server, and then restoring them onto a standby server. Enterprise Editions only
    supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db can be used this as the Disaster Recovery plan. The key feature of log shipping is that is will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.

    • What is the difference between a local and a global variable?
    User can create local temporary table using Single(#) and global temporary table using (##)A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
    A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection are closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
    What command do we use to rename a db?
    sp_renamedb ‘oldname’ , ‘newname’
    If someone is using db it will not accept sp_renmaedb. In that case first bring db to single user using sp_dboptions. Use sp_renamedb to rename database. Use sp_dboptions to bring database to multi user mode.

    • What are the different types of replication? Explain.
    The SQL Server 2000-supported replication types are as follows:
    · Transactional
    · Snapshot
    · Merge
    Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Snapshot replication is best used as a method for replicating data that changes infrequently or where the most up-to-date values (low latency) are not a requirement. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

    Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and applied to Subscribers.

    Merge replication is the process of distributing data from Publisher to Subscribers, allowing the
    Publisher and Subscribers to make updates while connected or disconnected, and then merging the updates between sites when they are connected.
    What are the OS services that the SQL Server installation adds?
    MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)

    • What does it mean to have quoted_identifier on? What are the implications of having it off?
    When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

    • What is the STUFF function and how does it differ from the REPLACE function?
    STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start,length, replacement_characters), string_expression is the string that will have characters substituted,start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.
    REPLACE function to replace existing characters of all occurance. Using this syntax
    REPLACE(string_expression, search_string, replacement_string), where every incidence of
    search_string found in the string_expression will be replaced with replacement_string.

    • Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?
    SELECT COUNT(*) FROM table1
    SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

    What is the basic functions for master, msdb, model, tempdb databases?
    The Master database stores the information about the sql server configuration, databases, users etc.

    The msdb database stores information regarding database backups, SQL Agent information, DTS packages, backup and restore history, SQL Server jobs, and some replication information such as for log shipping.
    The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
    The model is essentially a template database used in the creation of any new user database created in the instance.

    • What are primary keys and foreign keys?
    Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.
    Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

    • What is data integrity? Explain constraints?
    Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications.
    A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
    A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered.The unique key constraints are used to enforce entity integrity as the primary key constraints.

    A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
    A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

    A NOT NULL constraint enforces that the column will not accept null values. The not null constraints
    are used to enforce domain integrity, as the check constraints.

    • What is Identity?
    Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set.

    • What is BCP? When does it used?
    BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

    How do you load large data to the SQL server database?
    BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.

    • How to know which index a table is using?
    SELECT table_name,index_name FROM user_constraints

    • How to copy the tables, schema and views from one SQL server to another?
    Microsoft SQL Server 2000 Data Transformation Services (DTS) is a set of graphical tools and
    programmable objects that lets user extract, transform, and consolidate data from disparate sources into single or multiple destinations.

    • What is Self Join?
    This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.

    • What is Cross Join?
    A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

    • Which virtual table/Magic Tables does a trigger use?
    Inserted and Deleted.

    • List few advantages of Stored Procedure.
    · Stored procedure can reduced network traffic and latency, boosting application performance.
    · Stored procedure execution plans can be reused, staying cached in SQL Server’s memory,
    reducing server overhead.
    · Stored procedures help promote code reuse.
    · Stored procedures can encapsulate logic. You can change stored procedure code without
    affecting clients.
    · Stored procedures provide better security to your data.

    What is an execution plan? When would you use it? How would you view the execution plan?
    An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.