Basics of Minimalism

  1. Retain the essential, let go of other things
  2. Put it in Cardboard box, 90/90 day rule, If you dont use it more than 90 days let it go
  3. Cut on Social media, Check once in morning and once in evening
  4. Cut back bad habits. Develope good habits in Health, Money
  5. Remove mental clutter and get rid of things which never useful
  6. Make Priorities before starting to work

The heart of any Hibernate application is in its configuration. There are two pieces of configuration required in any Hibernate application: one creates the database connections, and the other creates the object-to-table mapping

null

To create a connection to the database, Hibernate must know the details of our database, tables, classes, and other mechanics. This information is ideally provided as an XML file (usually named hibernate.cfg.xml) or as a simple text file with name/value pairs (usually named hibernate.properties).
In XML style. We name this file hibernate.cfg.xml so the framework can load this file automatically.

hibernate.cfg.xml

<hibernate-configuration>  
    <session-factory>
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>
        <property name="connection.username">root</property>
        <property name="connection.password">pass</property>
         
        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>
 
        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
 
        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>
 
        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
 
        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">update</property>
         
        <!-- Name of the Annotated Entity class -->
        <mapping class="com.mugil.dto.UserDetails"/>
    </session-factory>
</hibernate-configuration>

hibernate.properties

hibernate.connection.driver_class = com.mysql.jdbc.Driver
hibernate.connection.url = jdbc:mysql://localhost:3307/JH
hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

We must let Hibernate know our mapping definition files by including an element mapping property in the previous config file, as shown here:
hibernate.cfg.xml

<hibernate-configuration>
 <session-factory>
 ...
 <mapping resource="table1.hbm.xml" />
 <mapping resource="table2.hbm.xml" />
 <mapping resource="table3.hbm.xml" />
 </session-factory>
</hibernate-configuration>

Once we have the connection configuration ready, the next step is to prepare the table1.hbm.xml file consisting of object-table mapping definitions
XML Mapping

<hibernate-mapping>
 <class name="com.java.latte.table1" table="TABLE1">
 <id name="id" column="ID">
 <generator class="native"/>
 </id>
 <property name="title" column="TITLE"/>
 <property name="director" column="DIRECTOR"/>
 <property name="synopsis" column="SYNOPSIS"/>
 </class>
</hibernate-mapping>
  1. The Hibernate framework reads the hibernate.cfg.xml file to create a SessionFactory, which is thread-safe global factory class for creating Sessions. We should ideally create a single SessionFactory and share it across the application.SessionFactory is defined for one, and only one, database.
  2. SessionFactory is to create Session objects.It is the Session’s job to take care of all database operations such as saving, loading, and retrieving records from relevant tables.Session objects are not thread-safe and therefore should not be shared across different classes.
  3. The Session wraps the underlying JDBC connection or J2EE data source, and it serves as a first-level cache for persistent objects bound to it.
  4. Hibernate specifies how each object state is retrieved and stored in the database via an XML configuration file. Hibernate mappings are loaded at startup and are cached in the SessionFactory. Each mapping specifies a variety of parameters related to the persistence lifecycle of instances of the mapped class

null

More on hibernate Object States here
More on hibernate Object types here

  1. A filter as the name suggests is a Java class executed by the servlet container for each incoming http request and for each http response. This way, is possible to manage HTTP incoming requests before them reach the resource, such as a JSP page, a servlet or a simple static page; in the same way is possible to manage HTTP outbound response after resource execution.
  2. The filter runs in the web container so its definition will also be contained in the web.xml file
  3. filer include three main methods:
    • init: executed to initialize filter using init-param element in filter definition
    • doFilter: executed for all HTTP incoming request that satisfy “url-pattern”
    • release resources used by the filter

web.xml

<filter>
    <filter-name>CORSFilter</filter-name>
    <filter-class>com.listfeeds.components.CORSFilter</filter-class>
    <init-param>
        <param-name>fake-param</param-name>
        <param-value>fake-param-value</param-value>
    </init-param>
</filter>
<filter-mapping>
    <filter-name>CORSFilter</filter-name>
    <url-pattern>/*</url-pattern>
</filter-mapping>

TestFilter.java

package com.listfeeds.filters;

import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Component;

public class TestFilter implements Filter {

    public void doFilter(ServletRequest req, ServletResponse res, FilterChain chain) throws IOException, ServletException {

        HttpServletResponse response = (HttpServletResponse) res;
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE");
        response.setHeader("Access-Control-Max-Age", "3600");
        response.setHeader("Access-Control-Allow-Headers", "x-requested-with");
        chain.doFilter(req, res);
    }

    public void init(FilterConfig filterConfig) {}

    public void destroy() {}

}

Filters can perform many different types of functions.

  1. Authentication-Blocking requests based on user identity.
  2. Logging and auditing-Tracking users of a web application.
  3. Image conversion-Scaling maps
  4. Data compression-Making downloads smaller
  5. Localization-Targeting the request and response to a particular locale

Request Filters can:

  1. perform security checks
  2. reformat request headers or bodies
  3. audit or log requests

Response Filters can:

  1. Compress the response stream
  2. Append or alter the response stream
  3. Create a different response altogether
  1. Spring Interceptors are similar to Servlet Filters but they acts in Spring Context so are many powerful to manage HTTP Request and Response but they can implement more sophisticated behavior because can access to all Spring context.
  2. Spring interceptor execute in Spring context so they have be defined in rest-servlet.xml file:
  3. The interceptor include three main methods:
    • preHandle: executed before the execution of the target resource
    • afterCompletion: executed after the execution of the target resource (after rendering the view)
    • posttHandle: Intercept the execution of a handler

rest-servlet.xml

<mvc:interceptors>
    <bean class="com.listfeeds.interceptors.LogContextInterceptor" />
    <bean class="com.listfeeds.interceptors.TimedInterceptor" />
</mvc:interceptors>

LogContextInterceptor.java

public class LogContextInterceptor extends HandlerInterceptorAdapter 
{
 private static final Logger log = LoggerFactory.getLogger(LogContextInterceptor.class);

 @Override
 public void afterCompletion(
  HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex)
 throws Exception 
 {
  HandlerMethod methodHandler = (HandlerMethod) handler;
  log.debug("END EXECUTION method {} request: {}", methodHandler.getMethod().getName(), request.getRequestURI());
  }
 

 @Override
 public boolean preHandle(HttpServletRequest request,
  HttpServletResponse response, Object handler) throws Exception 
  {

  } catch (IllegalArgumentException e) 
  {
   log.warn("Prehandle", e);
   return true;
  } finally 
  {
   HandlerMethod methodHandler = (HandlerMethod) handler;
   log.debug("START EXECUTION method {} request: {}", methodHandler.getMethod().getName(), request.getRequestURI());
  }
  return true;
}

Q1.What is the Difference between Filters and Interceptors?
Filter: – A filter as the name suggests is a Java class executed by the servlet container for each incoming HTTP request and for each http response. This way, is possible to manage HTTP incoming requests before they reach the resource, such as a JSP page, a servlet or a simple static page; in the same way, it’s possible to manage HTTP outbound response after resource execution.

  1. A filter dynamically intercepts requests and responses to transform or use the information contained in the requests or responses.
  2. Filters typically do not themselves create a response, but instead provide universal functions that can be “attached” to any type of servlet or JSP page.
  3. They provide the ability to encapsulate recurring tasks in reusable units. Organized developers are constantly on the lookout for ways to modularize their code.
  4. Modular code is more manageable and documentable, is easier to debug, and if done well, can be reused in another setting.

Interceptor: – Spring Interceptors are similar to Servlet Filters but they act in Spring Context so are many powerful to manage HTTP Request and Response but they can implement more sophisticated behavior because can access to all Spring context. Interceptors are used in conjunction with Java EE managed classes to allow developers to invoke interceptor methods in conjunction with method invocations or lifecycle events on an associated target class. Common uses of interceptors are logging, auditing, or profiling.

  1. Interceptor can be defined within a target class as an interceptor method, or in an associated class called an interceptor class.
  2. Interceptor classes contain methods that are invoked in conjunction with the methods or lifecycle events of the target class.
  3. Interceptor classes and methods are defined using metadata annotations, or in the deployment descriptor of the application containing the interceptors and target classes.
  4. Interceptor classes may be targets of dependency injection. Dependency injection occurs when the interceptor class instance is created, using the naming context of the associated target class, and before any @PostConstruct callbacks are invoked.

Refer Here

Q2.Spring interceptor vs servlet filter?

  1. Using Interceptor we can inject other beans in the interceptor
  2. Can use more advanced mapping patterns (ant-style)
  3. You have the target handler object (controller) available, as well as the result ModelAndView
  4. It is a bean, so you can use AOP with it (althoug that would be rare)

Q3.How to avoid multiple submission of Form to Server?

  1. Use JavaScript to disable the button a few ms after click. This will avoid multiple submits being caused by impatient users clicking multiple times on the button.
  2. Send a redirect after submit, this is known as Post-Redirect-Get (PRG) pattern. This will avoid multiple submits being caused by users pressing F5 on the result page and ignoring the browser warning that the data will be resend, or navigating back and forth by browser back/forward buttons and ignoring the same warning.
  3. Generate an unique token when the page is requested and put in both the session scope and as hidden field of the form. During processing, check if the token is there and then remove it immediately from the session and continue processing. If the token is not there, then block processing. This will avoid the aforementioned kinds of problems.

Q4.What is POST-REDIRECT-GET Pattern?
The client gets a page with a form.The form POSTs to the server.The server performs the action, and then redirects to another page.The client follows the redirect.
For example, say we have this structure of the website as below

  1. /posts (shows a list of posts and a link to “add post”) and / (view a particular post)
  2. /create (if requested with the GET method, returns a form posting to itself; if it’s a POST request, creates the post and redirects to the / endpoint)

For retrieving posts /posts/ might be implemented like this:

  1. Find the post with that ID in the database.
  2. Render a template with the content of that post.

For creating posts /posts/create might be implemented like this:

  1. If the request is a GET request for the Insert posts page Show an empty form with the target set to itself and the method set to POST.
  2. If the request is a POST request
    • Validate the fields.
    • If there are invalid fields, show the form again with errors indicated.
  3. Otherwise, if all fields are valid
    • Add the post to the database.
    • Redirect to /posts/ (where is returned from the call to the database)

null

Q1.What is the Difference between Stack and Heap?
Stack vs Heap

Q2.Does Wrapper Classes are immutable Similar to String?
Yes, Wrapper classes are immutable similar to String.

Q3.Does Wrapper Classes would be cached Similar to String Pool for Strings?
Yes.Java has Integer pool for small integers between -128 to 127 so it will behave same for Integer also similar to String Constant pool
java.lang.Boolean store two inbuilt instances TRUE and FALSE, and return their reference if new keyword is not used.
java.lang.Character has a cache for chars between unicodes 0 and 127 (ascii-7 / us-ascii).
java.lang.Long has a cache for long between -128 to +127.
java.lang.String has a whole new concept of string pool.

Q4.How String will behave in memory management incase of String Literal or String Object?

Q5.See the Below Code

class D {
    public static void main(String args[]) {
        Integer b1=127;
        Integer b2=127;
        Integer b3=128;
        Integer b4=128;
        System.out.println(b1==b2);
        System.out.println(b3==b4);
    }
}
true 
false

Why it is so?
If the value p being boxed is true, false, a byte, a char in the range \u0000 to \u007f, or an int or short number between -128 and 127, then let r1 and r2 be the results of any two boxing conversions of p. It is always the case that r1 == r2.

Q6.In which Memory would the following would be created?

int a = 0; 
Integer b = 0;

It Depends whether a and b variables are local variables or fields (static or instance) of an object.

If they are local variables:
a is on the stack.
b is on the stack (a reference) and it refers to an object in the heap.

If they are fields of an instance or class:
a is on the heap (as part of the instance or the class).
b is on the heap (as above) and it refers to an object in the heap.

Q7.Why the value of i didnt change after modify being called?

class Demo 
{ 
    public static void main(String[] args) 
    { 
        Integer i = new Integer(12); 
        System.out.println(i); 
        modify(i); 
        System.out.println(i); 
    } 
  
    private static void modify(Integer i) 
    { 
        i = i + 1; 
    } 
} 

Output

12
12
12
12

The reason again traces back to the Immutability of wrapper class.

i = i + 1;

It does the following:

  1. Unbox i to an int value
  2. Add 1 to that value
  3. Box the result into another Integer object
  4. Assign the resulting Integer to i (thus changing what object i references)

Since object references are passed by value, the action taken in the modified method does not change i that was used as an argument in the call to modify. Thus the main routine still prints 12 after the method returns.

Q8.How the array is stored in the memory?

arr[0] = new String("abc");
arr[1] = new List();

Stack has a single pointer to a location in the heap that contains the array itself. The array itself is just an array of pointers which also point to locations in the heap that contain the objects you reference.

Q9.What is Contagious memory block?
Array are “contiguous”. That means the elements are laid out end-to-end, with no discontinuities and no padding between them (there may be padding inside each element, but not between elements). So an array of 5 4-byte elements looks like this (1 underscore character per byte, the | symbols don’t represent memory).Arrays and ArrayList uses Contagious memory whereas LinkedList uses Non Contagious memory.

Contiguous

Non-Contiguous

Type inference is a feature of Java which provides ability to compiler to look at each method invocation and corresponding declaration to determine the type of arguments.
Java provides improved version of type inference in Java 8.
Here, we are creating arraylist by mentioning integer type explicitly at both side. The following approach is used earlier versions of Java.

List<Integer> list = new ArrayList<Integer>();  

In the following declaration, we are mentioning type of arraylist at one side. This approach was introduce in Java 7. Here, you can left second side as blank diamond and compiler will infer type of it by type of reference variable.

List<Integer> list2 = new ArrayList<>();   

Improved Type Inference
In Java 8, you can call specialized method without explicitly mentioning of type of arguments.

showList(new ArrayList<>());  

Example
You can use type inference with generic classes and methods.

import java.util.ArrayList;
import java.util.List;
public class TypeInferenceExample {
 public static void showList(List < Integer > list) {
  if (!list.isEmpty()) {
   list.forEach(System.out::println);
  } else System.out.println("list is empty");
 }

 public static void main(String[] args) {

  // An old approach(prior to Java 7) to create a list  
  List < Integer > list1 = new ArrayList < Integer > ();
  list1.add(11);
  showList(list1);

  // Java 7    
  List < Integer > list2 = new ArrayList < > (); // You can left it blank, compiler can infer type  
  list2.add(12);
  showList(list2);

  // Compiler infers type of ArrayList, in Java 8  
  showList(new ArrayList < > ());
 }
}

Output

11
12
list is empty

Type inference for Custom Classes

class GenericClass <X> {
 X name;
 public void setName(X name) {
  this.name = name;
 }
 public X getName() {
  return name;
 }
 public String genericMethod(GenericClass < String > x) {
  x.setName("John");
  returnx.name;
 }
}

public class TypeInferenceExample {
 public static void main(String[] args) {
  GenericClass < String > genericClass = new GenericClass < String > ();
  genericClass.setName("Peter");
  System.out.println(genericClass.getName());

  GenericClass < String > genericClass2 = new GenericClass < > ();
  genericClass2.setName("peter");
  System.out.println(genericClass2.getName());

  // New improved type inference  
  System.out.println(genericClass2.genericMethod(new GenericClass < > ()));
 }
}

Output

Peter
peter
John

Lambdas implement a functional interface.Anonymous Inner Classes can extend a class or implement an interface with any number of methods.
Variables – Lambdas can only access final or effectively final.
State – Anonymous inner classes can use instance variables and thus can have state, lambdas cannot.
Scope – Lambdas can’t define a variable with the same name as a variable in enclosing scope.
Compilation – Anonymous compiles to a class, while lambda is an invokedynamic instruction.

Syntax
Lambda expressions looks neat as compared to Anonymous Inner Class (AIC)

public static void main(String[] args) {
    Runnable r = new Runnable() {
        @Override
        public void run() {
            System.out.println("in run");
        }
    };

    Thread t = new Thread(r);
    t.start(); 
}

//syntax of lambda expression 
public static void main(String[] args) {
    Runnable r = ()->{System.out.println("in run");};
    Thread t = new Thread(r);
    t.start();
}

Scope
An anonymous inner class is a class, which means that it has scope for variable defined inside the inner class.

Whereas,lambda expression is not a scope of its own, but is part of the enclosing scope.

Similar rule applies for super and this keyword when using inside anonymous inner class and lambda expression. In case of anonymous inner class this keyword refers to local scope and super keyword refers to the anonymous class’s super class. While in case of lambda expression this keyword refers to the object of the enclosing type and super will refer to the enclosing class’s super class.

//AIC
    public static void main(String[] args) {
        final int cnt = 0; 
        Runnable r = new Runnable() {
            @Override
            public void run() {
                int cnt = 5;    
                System.out.println("in run" + cnt);
            }
        };

        Thread t = new Thread(r);
        t.start();
    }

//Lambda
    public static void main(String[] args) {
        final int cnt = 0; 
        Runnable r = ()->{
            int cnt = 5; //compilation error
            System.out.println("in run"+cnt);};
        Thread t = new Thread(r);
        t.start();
    }

Performance
At runtime anonymous inner classes require class loading, memory allocation, and object initialization and invocation of a non-static method while lambda expression is a compile-time activity and don’t incur extra cost during runtime. So the performance of lambda expression is better as compare to anonymous inner classes.

Reading a File extending Thread API

  1. ReadFile.java has a run() method which implements the reading the file code within the try with resources block
  2. In the main method start method is called over the ReadFile class instance
  3. In thread we have coded is asynchrobnous(order of execution cannot be guaranteed) which we can see from the output below

TestThread.java

package com.mugil.test;

import com.mugil.runnables.ReadFile;

public class TestThread {
	public static void main(String[] args) {
		ReadFile objReadFileThread1 = new ReadFile();
		ReadFile objReadFileThread2 = new ReadFile();
		ReadFile objReadFileThread3 = new ReadFile();
				
		objReadFileThread1.start();
		objReadFileThread2.start();
		objReadFileThread3.start();
	}
}

ReadFile.java

package com.mugil.runnables;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;

public class ReadFile extends Thread {

 public void run() {

  try (BufferedReader reader = new BufferedReader(new FileReader(new File("E:\\JavaProjects\\JavaThreads\\src\\Sample.txt")))) {
   String line = null;

   while ((line = reader.readLine()) != null) {
    System.out.println(Thread.currentThread().getName() + " reading line " + line);
   }

  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

 }
}

Output

Thread-2 reading line Line1
Thread-0 reading line Line1
Thread-0 reading line Line2
Thread-0 reading line Line3
Thread-1 reading line Line1
Thread-1 reading line Line2
Thread-2 reading line Line2
Thread-1 reading line Line3
Thread-1 reading line Line4
Thread-1 reading line Line5
Thread-0 reading line Line4
Thread-0 reading line Line5
Thread-2 reading line Line3
Thread-2 reading line Line4
Thread-2 reading line Line5

Reading a File implementing Runnable API

  1. Now in the below code the runnable API is implemented rather than extending like Thread
  2. The run() is called over instance of ReadFile rather than start() method
  3. Calling run() method will start the execution of thread in the present running thread rather than creating new Thread for execution which can been seen in output main reading line rather than Thread-N reading line

TestThread.java

package com.mugil.test;

import com.mugil.runnables.ReadFile;

public class TestThread {
	public static void main(String[] args) {
		ReadFile objReadFileThread1 = new ReadFile();
		ReadFile objReadFileThread2 = new ReadFile();
		ReadFile objReadFileThread3 = new ReadFile();
				
		objReadFileThread1.run();
		objReadFileThread2.run();
		objReadFileThread3.run();
	}
}

ReadFile.java

public class ReadFile implements Runnable {

 public void run() {

  try (BufferedReader reader = new BufferedReader(new FileReader(new File("E:\\JavaProjects\\JavaThreads\\src\\Sample.txt")))) {
   String line = null;

   while ((line = reader.readLine()) != null) {
    System.out.println(Thread.currentThread().getName() + " reading line " + line);
   }

  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
}

Output

main reading line Line1
main reading line Line2
main reading line Line3
main reading line Line4
main reading line Line5
main reading line Line1
main reading line Line2
main reading line Line3
main reading line Line4
main reading line Line5
main reading line Line1
main reading line Line2
main reading line Line3
main reading line Line4
main reading line Line5

Methods to Manage thread are available on Thread class not in Runnable. So we can pass the runnable instance as parameter like one below
TestThread.java

.
.
.
Thread objThread = new Thread(runObj);
objThread.start();
.
.

Q1.Optimistic vs. Pessimistic locking
Optimistic Locking is a strategy where you read a record, take note of a version number (other methods to do this involve dates, timestamps or checksums/hashes) and check that the version hasn’t changed before you write the record back. When you write the record back you filter the update on the version to make sure it’s atomic. (i.e. hasn’t been updated between when you check the version and write the record to the disk) and update the version in one hit.If the record is dirty (i.e. different version to yours) you abort the transaction and the user can re-start it.

This strategy is most applicable to high-volume systems and three-tier architectures where you do not necessarily maintain a connection to the database for your session. In this situation the client cannot actually maintain database locks as the connections are taken from a pool and you may not be using the same connection from one access to the next.Optimistic locking doesn’t necessarily use a version number. Other strategies include using (a) a timestamp or (b) the entire state of the row itself. The latter strategy is ugly but avoids the need for a dedicated version column, in cases where you aren’t able to modify the schema.

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. To use pessimistic locking you need either a direct connection to the database (as would typically be the case in a two tier client server application) or an externally available transaction ID that can be used independently of the connection.

In the latter case you open the transaction with the TxID and then reconnect using that ID. The DBMS maintains the locks and allows you to pick the session back up through the TxID.

Optimistic locking is used when you don’t expect many collisions. It costs less to do a normal operation but if the collision DOES occur you would pay a higher price to resolve it as the transaction is aborted.Pessimistic locking is used when a collision is anticipated. The transactions which would violate synchronization are simply blocked.
To select proper locking mechanism you have to estimate the amount of reads and writes and plan accordingly

Optimistic needs a three-tier architectures where you do not necessarily maintain a connection to the database for your session whereas Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking you need either a direct connection to the database.optimistic (versioning) is faster because of no locking but (pessimistic) locking performs better when contention is high and it is better to prevent the work rather than discard it and start over.Optimistic locking works best when you have rare collisions

Q2.What is the Need for Indexing in Database Tables?
An index can be used to efficiently find all rows matching some column in your query and then walk through only that subset of the table to find exact matches. If you don’t have indexes on any column in the WHERE clause, the SQL server have to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.The index can also be a UNIQUE index, which means that you cannot have duplicate values in that column, or a PRIMARY KEY which in some storage engines defines where in the database file the value is stored.

Q3.Clustered and Non Clustered Index
A Clustered index determines the physical order of data in a table.There can be only one clustered index per table (the clustered index IS the table). All other indexes on a table are termed non-clustered.A clustered index means you are telling the database to store close values actually close to one another on the disk. This has the benefit of rapid scan / retrieval of records falling into some range of clustered index values.

For example, you have two tables, Customer and Order:

Customer
----------
ID
Name
Address

Order
----------
ID
CustomerID
Price

If you wish to quickly retrieve all orders of one particular customer, you may wish to create a clustered index on the “CustomerID” column of the Order table. This way the records with the same CustomerID will be physically stored close to each other on disk (clustered) which speeds up their retrieval.The index on CustomerID will obviously be not unique, so you either need to add a second field to “uniquify” the index or let the database handle that for you but that’s another story.

Since the clustered index is actually related to how the data is stored, there is only one of them possible per table (although you can cheat to simulate multiple clustered indexes).

A non-clustered index is different in that you can have many of them and they then point at the data in the clustered index. You could have e.g. a non-clustered index at the back of a phone book which is keyed on (town, address)

You can have only one clustered index per table because this defines how the data is physically arranged. If you wish an analogy, imagine a big room with many tables in it. You can either put these tables to form several rows or pull them all together to form a big conference table, but not both ways at the same time. A table can have other indexes, they will then point to the entries in the clustered index which in its turn will finally say where to find the actual data.

Clustered Index

  1. Only one clustered index can be there in a table
  2. Sort the records and store them physically according to the order
  3. Data retrieval is faster than non-clustered indexes
  4. Do not need extra space to store logical structure

Non Clustered Index

  1. There can be any number of non-clustered indexes in a table
  2. Do not affect the physical order. Create a logical order for data rows and use pointers to physical data files
  3. Data insertion/update is faster than clustered index
  4. Use extra space to store logical structure

Q4.What is Staging/Factory table?
Staging table is a temporary table that is used to stage the data for temporary purpose just before loading it to the Target table from the Source Table. As the data resides temporarily, you can do various stuff on that data like
De-duping,Cleansing, Normalizing to multiple tables, De-Normalizing from multiple to a single table and Extrapolating

Q5.Staging vs Temp table?
Staging tables are permanent table just database tables containing your business data in some form or other. Staging is the process of preparing your business data, usually taken from some business application.Temporary tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.When we are doing large number of row manipulation in stored procedures. This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there. When we are having a complex join operation.

Permanent table is faster if the table structure is to be 100% the same since there’s no overhead for allocating space and building the table.

Temp table is faster in certain cases (e.g. when you don’t need indexes that are present on permanent table which would slow down inserts/updates)

Q6.What are different types of Tables?
Normal tables are exactly that, physical tables defined in your database.

Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.

Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.

Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It’s a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Q7. Procedures vs Functions?

Stored Procedures (SP) Functions (UDF – User Defined Function)
SP can return zero , single or multiple values. Function must return a single value (which may be a scalar or a table).
We can use transaction in SP We can’t use transaction in UDF.
SP can have input/output parameter. Only input parameter
We can call function from SP. We can’t call SP from function.
We can’t use SP in SELECT/ WHERE/ HAVING statement. We can use UDF in SELECT/ WHERE/HAVING statement.
We can use exception handling using Try-Catch block in SP. We can’t use Try-Catch block in UDF.

Q8.Table vs View?
Table is a preliminary storage for storing data and information in RDBMS. A table is a collection of related data entries and it consists of columns and rows.

A view is a virtual table whose contents are defined by a query. Unless indexed, a view does not exist as a stored set of data values in a database. The advantage of a view is that it can join data from several tables thus creating a new view of it

Advantages over table are

  1. We can combine columns/rows from multiple table or another view and have a consolidated view.
  2. Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view
  3. It acts as abstract layer to downstream systems, so any change in schema is not exposed and hence the downstream systems doesn’t get affected.
  4. Instead of sending the complex query to the database all the time, you can save the query as a view and then SELECT * FROM view

Q9.Can View could be indexed?
Yes. View can be indexed.The big disadvantage of indexed views is that they are recreated every time the underlying table data changes. That restricts the use of indexed views to data that does not change often, typically in a data warehouse or business intelligence environment.

Q10.Which one is Faster Optimistic or Pessimistic?
Optimistic locking assumes concurrent transactions can complete without affecting each other. So Optimistic locking is faster because no locks are enforced while doing transactions.Optimistic locking does not cause transactions to wait for each other.Optimistic locking possibly causes a transaction to fail, but it does so without any “lock” ever having been taken.
The word “optimistic” derives from exactly the “I will not be taking actual locks because I hope they won’t be needed anyway. If it turns out I was wrong about that, I will accept the inevitable failure.”

In a real world analogy. Let’s say you have to get done 2 very important tasks in one day:

  • Get a passport
  • Get a presentation done

Now, the problem is that task-1 requires you to go to an extremely bureaucratic government office that makes you wait for 4 hours in a line to get your passport. Meanwhile, task-2 is required by your office, and it is a critical task. Both must be finished on a specific day.

Case 1: Sequential Execution
Ordinarily, you will drive to passport office for 2 hours, wait in the line for 4 hours, get the task done, drive back two hours, go home, stay awake 5 more hours and get presentation done.

Case 2: Concurrent Execution
But you’re smart. You plan ahead. You carry a laptop with you, and while waiting in the line, you start working on your presentation. This way, once you get back at home, you just need to work 1 extra hour instead of 5.

In this case, both tasks are done by you, just in pieces. You interrupted the passport task while waiting in the line and worked on presentation. When your number was called, you interrupted presentation task and switched to passport task. The saving in time was essentially possible due to interruptability of both the tasks.

Concurrency, IMO, can be understood as the “isolation” property in ACID. Two database transactions are considered isolated if sub-transactions can be performed in each and any interleaved way and the final result is same as if the two tasks were done sequentially. Remember, that for both the passport and presentation tasks, you are the sole executioner.

Case 3: Parallel Execution
Now, since you are such a smart fella, you’re obviously a higher-up, and you have got an assistant. So, before you leave to start the passport task, you call him and tell him to prepare first draft of the presentation. You spend your entire day and finish passport task, come back and see your mails, and you find the presentation draft. He has done a pretty solid job and with some edits in 2 more hours, you finalize it.

Now since, your assistant is just as smart as you, he was able to work on it independently, without needing to constantly ask you for clarifications. Thus, due to the independentability of the tasks, they were performed at the same time by two different executioners.

Still with me? Alright…

Case 4: Concurrent But Not Parallel
Remember your passport task, where you have to wait in the line? Since it is your passport, your assistant cannot wait in line for you. Thus, the passport task has interruptability (you can stop it while waiting in the line, and resume it later when your number is called), but no independentability (your assistant cannot wait in your stead).

Case 5: Parallel But Not Concurrent
Suppose the government office has a security check to enter the premises. Here, you must remove all electronic devices and submit them to the officers, and they only return your devices after you complete your task.

In this, case, the passport task is neither independentable nor interruptible. Even if you are waiting in the line, you cannot work on something else because you do not have necessary equipment.

Similarly, say the presentation is so highly mathematical in nature that you require 100% concentration for at least 5 hours. You cannot do it while waiting in line for passport task, even if you have your laptop with you.

In this case, the presentation task is independentable (either you or your assistant can put in 5 hours of focused effort), but not interruptible.

Case 6: Concurrent and Parallel Execution
Now, say that in addition to assigning your assistant to the presentation, you also carry a laptop with you to passport task. While waiting in the line, you see that your assistant has created the first 10 slides in a shared deck. You send comments on his work with some corrections. Later, when you arrive back home, instead of 2 hours to finalize the draft, you just need 15 minutes.

This was possible because presentation task has independentability (either one of you can do it) and interruptability (you can stop it and resume it later). So you concurrently executed both tasks, and executed the presentation task in parallel.

Let’s say that, in addition to being overly bureaucratic, the government office is corrupt. Thus, you can show your identification, enter it, start waiting in line for your number to be called, bribe a guard and someone else to hold your position in the line, sneak out, come back before your number is called, and resume waiting yourself.

In this case, you can perform both the passport and presentation tasks concurrently and in parallel. You can sneak out, and your position is held by your assistant. Both of you can then work on the presentation, etc.

Back to Computer Science
In computing world, here are example scenarios typical of each of these cases:

Case 1: Interrupt processing.
Case 2: When there is only one processor, but all executing tasks have wait times due to I/O.
Case 3: Often seen when we are talking about map-reduce or hadoop clusters.
Case 4: I think Case 4 is rare. It’s uncommon for a task to be concurrent but not parallel. But it could happen. For example, suppose your task requires access to a special computational chip which can be accessed through only processor-1. Thus, even if processor-2 is free and processor-1 is performing some other task, the special computation task cannot proceed on processor-2.
Case 5: also rare, but not quite as rare as Case 4. A non-concurrent code can be a critical region protected by mutexes. Once it is started, it must execute to completion. However, two different critical regions can progress simultaneously on two different processors.
Case 6: IMO, most discussions about parallel or concurrent programming are basically talking about Case 6. This is a mix and match of both parallel and concurrent executions.

1 server , 1 job queue (with 5 jobs) -> no concurrency, no parallelism (Only one job is being serviced to completion, the next job in the queue has to wait till the serviced job is done and there is no other server to service it)

1 server, 2 or more different queues (with 5 jobs per queue) -> concurrency (since server is sharing time with all the 1st jobs in queues, equally or weighted) , still no parallelism since at any instant, there is one and only job being serviced.

2 or more servers , one Queue -> parallelism ( 2 jobs done at the same instant) but no concurrency ( server is not sharing time, the 3rd job has to wait till one of the server completes.)

2 or more servers, 2 or more different queues -> concurrency and parallelism

In other words, concurrency is sharing time to complete a job, it MAY take up the same time to complete its job but at least it gets started early. Important thing is , jobs can be sliced into smaller jobs, which allows interleaving.Parallelism is achieved with just more CPUs , servers, people etc that run in parallel.If the resources are shared, pure parallelism cannot be achieved, but this is where concurrency would have it’s best practical use, taking up another job that doesn’t need that resource.