Interview Question and Answers

Each class and module should focus on a single task at a time
Everything in the class should be related to that single purpose
There can be many members in the class as long as they related to the single responsibility
With SRP, classes become smaller and cleaner
The code is less fragile

Interface Segregation Principle (ISP)

The interface-segregation principle (ISP) states that "no client should be forced to depend on methods it does not use".
This means, instead of one fat interface many small interfaces are preferred based on groups of methods with each one serving one sub-module.
The ISP was first used and formulated by Robert C. Martin while consulting for Xerox.

Open/Closed Principle

In object-oriented programming, the open/closed principle states that "software entities such as classes, modules, functions, etc. should be open for extension, but closed for modification"

Which means any new functionality should be implemented by adding new classes, attributes, and methods, instead of changing the current ones or existing ones.
Bertrand Meyer is generally credited for having originated the term open/closed principle and This Principle is considered by Bob Martin as "the most important principle of object-oriented design".

Liskov Substitution Principle (LSP)

Substitutability is a principle in object-oriented programming and it states that, in a computer program, if S is a Subtype of T, then objects of type T may be replaced with objects of type S

Which means, Derived types must be completely substitutable for their base types
More formally, the Liskov substitution principle (LSP) is a particular definition of a subtyping relation, called (strong) behavioral subtyping
This Principle is introduced by Barbara Liskov in 1987 during her conference address on Data abstraction and hierarchy
This principle is just an extension of the Open Close Principle

Dependency Inversion Principle (DIP)

High-level modules should not depend on low-level modules. Both should depend on abstractions.
Abstractions should not depend on details. Details should depend on abstractions.

What are the difference between interfaces and abstract classes

1. Abstract classes can have implementations for some of its members, but the interface can't have the implementation for any of its members.

2. Interfaces cannot have fields whereas an abstract class can have fields.

3. An interface can inherit from another interface only and cannot inherit from an abstract class, whereas an abstract class can inherit from another abstract class or another interface.

4. A class can inherit from multiple interfaces at the same time, whereas a class cannot inherit from multiple classes at the same time.

5. Abstract class members can have access modifiers whereas interface members cannot have access modifiers.

When do you choose interface over an abstract class or vice versa?

If you have an implementation that will be the same for all the derived classes, then it is better to go for an abstract class instead of an interface. So, when you have an interface, you can move your implementation to any class that implements the interface. Where as, when you have an abstract class, you can share implementation for all derived classes in one central place, and avoid code duplication in derived classes.

Why and when should we use an abstract class?

Let us understand when to use an abstract class with an example. An organisation has 2 types of employees
1. FullTimeEmployee
2. ContractEmployee

public class FullTimeEmployee
{
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int AnnualSalary { get; set; }

public string GetFullName()
{
return this.FirstName + " " + LastName;
}

public int GetMonthlySalary()
{
return this.AnnualSalary / 12;
}
}

public class ContractEmployee
{
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int HourlyPay { get; set; }
public int TotalHoursWorked { get; set; }

public string GetFullName()
{
return this.FirstName + " " + LastName;
}

public int GetMonthlySalary()
{
return this.HourlyPay * this.TotalHoursWorked;
}
}

public static void Main()
{
FullTimeEmployee fte = new FullTimeEmployee()
{
ID = 101,
FirstName = "David",
LastName = "Pie",
AnnualSalary = 60000
};

Console.WriteLine(fte.GetFullName());
Console.WriteLine(fte.GetMonthlySalary());

Console.WriteLine("-------------");

ContractEmployee cte = new ContractEmployee()
{
ID = 102,
FirstName = "Sam",
LastName = "Brooks",
HourlyPay = 100,
TotalHoursWorked = 40
};

Console.WriteLine(cte.GetFullName());
Console.WriteLine(cte.GetMonthlySalary());
}

Notice that, we have designed FullTimeEmployee and ContractEmployee classes as stand-alone classes. Regardless of the employee type, all employees in the organisation are going to have ID, FirstName and LastName properties. We also compute the FullName of any employee by concatenating their FirstName and LastName. This means that, the above two classes (FullTimeEmployee & ContractEmployee) are related and there is, a lot of common functionality duplicated in them. The problem with this design is that, tomorrow, if want to introduce MiddleName property and if we have to include it in the computation of FullName, then we have to make the same change in both the classes. So code maintainability is going to be a big issue with this design.

To avoid these issues, we can move the common functionality into a base class. Using a common base class, we are going to get rid of the duplicated code.

The obvious next question is, How should we design the base class?
1. Should we design it as an abstract class
OR
2. Should we design it as a Concrete (Non abstract) class

Let's see what's going to happen, if we design the base class as a concrete class. All the common code is now present in BaseEmployee class.
public class BaseEmployee
{
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }

public string GetFullName()
{
return this.FirstName + " " + LastName;
}

public virtual int GetMonthlySalary()
{
throw new NotImplementedException();
}
}

Notice that, now the "FullTimeEmployee" class inherits from "BaseEmployee" class and has only the code that is specific to it.
public class FullTimeEmployee : BaseEmployee
{
public int AnnualSalary { get; set; }

public override int GetMonthlySalary()
{
return this.AnnualSalary / 12;
}
}

Along the same lines, "ContractEmployee" class inherits from "BaseEmployee" class and has only the code that is specific to it.
public class ContractEmployee : BaseEmployee
{
public int HourlyPay { get; set; }
public int TotalHoursWorked { get; set; }

public override int GetMonthlySalary()
{
return this.HourlyPay * this.TotalHoursWorked;
}
}

So, with the above design we got rid of duplicated code, but we introduced another problem. Since "BaseEmployee " is a concrete (Non abstract) class, there is nothing stopping us from creating an instance of BaseEmployee class and using it. In the Main() method, someone could instantiate "BaseEmployee" class as shown below.

public static void Main()
{
BaseEmployee be = new BaseEmployee()
{
ID = 101,
FirstName = "David",
LastName = "Pie",
};

Console.WriteLine(be.GetFullName());
Console.WriteLine(be.GetMonthlySalary());
}

The above design is bad for 2 reasons
1. We only have 2 types of employees in our organisation - ContractEmployee & FullTimeEmployee. The developers should only able to instantiate ContractEmployee & FullTimeEmployee classes and not BaseEmployee class.
2. We get a run time error, if we invoke GetMonthlySalary() method on BaseEmployee class.

To get rid of the second issue, we can make the following modifications
1. Remove GetMonthlySalary() virtual method from BaseEmployee class
2. Remove "override" keyword from GetMonthlySalary() method in ContractEmployee and FullTimeEmployee classes.

With the above changes, we won't get the runtime error, but we would still be able to instantiate BaseEmployee class. So to prevent BaseEmployee class from being instantiated, let's mark it as an abstract class.

One more change is to introduce GetMonthlySalary() as an abstract method in BaseEmployee class. This will ensure that, all the classes that derive from BaseEmployee class,
1. Will either provide implementation for GetMonthlySalary() method
OR
2. The derived class will also be marked as an abstract class.

With the above changes the design looks as shown below.
public abstract class BaseEmployee
{
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }

public string GetFullName()
{
return this.FirstName + " " + LastName;
}

public abstract int GetMonthlySalary();
}

public class FullTimeEmployee : BaseEmployee
{
public int AnnualSalary { get; set; }

public override int GetMonthlySalary()
{
return this.AnnualSalary / 12;
}
}

public class ContractEmployee : BaseEmployee
{
public int HourlyPay { get; set; }
public int TotalHoursWorked { get; set; }

public override int GetMonthlySalary()
{
return this.HourlyPay * this.TotalHoursWorked;
}
}

So, in short, we would create an abstract class, when want to move the common functionality of 2 or more related classes into a base class and when, we don't want that base class to be instantiated.

Use these For better SQL Tuning

Use these For better SQL Tuning

checkpoint
go

DBCC DROPCLEANBUFFERS
go

DBCC FREESESSIONCACHE
go

DBCC FREEPROCCACHE
go

DBCC FREESYSTEMCACHE('ALL')
go

Query Performance Tuning

Improve Indexes
Create Highly-Selective Indexes
Create Multiple-Column Indexes
Avoid Indexing Small Tables
Choose What to Index
Use the Query Optimizer
Understand Response Time vs. Total Time
Index the ORDER-BY / GROUP-BY / DISTINCT Columns for Better Response Time
Rewrite Subqueries to Use JOIN
Limit Using Outer JOINs
Use Parameterized Queries
Query Only When You Must

Performance tuning

Create a primary key on each table you create and unless you are really knowledgeable enough to figure out a better plan, make it the clustered index (note that if you set the primary key in Enterprise Manager it will cluster it by default).

Create an index on any column that is a foreign key. If you know it will be unique, set the flag to force the index to be unique.

Don’t index anything else (yet).

Unless you need a different behavior, always owner qualify your objects when you reference them in TSQL. Use dbo.sysdatabases instead of just sysdatabases.

Use set nocount on at the top of each stored procedure (and set nocount off) at the bottom.

Think hard about locking. If you’re not writing banking software, would it matter that you take a chance on a dirty read? You can use the NOLOCK hint, but it’s often easier to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then reset to READ COMMITTED at the bottom.

I know you’ve heard it a million times, but only return the columns and the rows you need.

Use transactions when appropriate, but allow zero user interaction while the transaction is in progress. I try to do all my transactions inside a stored procedure.

Avoid temp tables as much as you can, but if you need a temp table, create it explicitly using Create Table #temp.

Avoid NOT IN, instead use a left outer join – even though it’s often easier to visualize the NOT IN.

If you insist on using dynamic sql (executing a concatenated string), use named parameters and sp_executesql (rather than EXEC) so you have a chance of reusing the query plan. While it’s simplistic to say that stored procedures are always the right answer, it’s also close enough that you won’t go wrong using them.

Get in the habit of profiling your code before and after each change. While you should keep in mind the depth of the change, if you see more than a 10-15% increase in CPU, Reads, or Writes it probably needs to be reviewed.

Look for every possible way to reduce the number of round trips to the server. Returning multiple resultsets is one way to do this.

Avoid index and join hints.

When you’re done coding, set Profiler to monitor statements from your machine only, then run through the application from start to finish once. Take a look at the number of reads and writes, and the number of calls to the server. See anything that looks unusual? It’s not uncommon to see calls to procedures that are no longer used, or to see duplicate calls. Impress your DBA by asking him to review those results with you.

Ways to Find Slow SQL Queries

1. Find Slow Queries With SQL DMVs ( dynamic management views)

2. Query Reporting via APM Solutions ( application performance management )

3. SQL Server Profiler (DEPRECATED!)

4. SQL Server Extended Events

5. SQL Azure Query Performance Insights

Performance Tips for SQL SELECT Statements

Check Indexes
Limit Size of Your Working Data Set
Only Select Fields You Need
Remove Unnecessary Tables
Remove OUTER JOINS
Remove Calculated Fields in JOIN and WHERE Clauses