Which Indexes are required for SQL Server Query ?

'

A lot of time Jr. DBA/Developer and students are not clear which Index should be created for performance tuning a query. In my last 16 years as SQL DBA, I have taken more than 500 interviews at different levels and I have seen that even very Senior DBA are not clear at times as to what index will properly support my query.

So let’s see a very brief and clear explanation on what index will improve performance of my query and why:

We will use the below Sample queries:

CREATE TABLE [dbo].[Buildings](

       [Buildingid] [int] NOT NULL,

       [BuildingName] [varchar](50) NULL,

       [BuildingLocation] [varchar](50) NULL,

PRIMARY KEY CLUSTERED

(

       [Buildingid] ASC

)

)

--INSERT SOME RECORDS

INSERT INTO Buildings values (1,'Empire State','NYC')

INSERT INTO Buildings values (2,'Building2','NDLS')

INSERT INTO Buildings values (3,'Building3','NDLS')

INSERT INTO Buildings values (4,'Building4','Mumbai')

INSERT INTO Buildings values (5,'Building5','Mumbai')

INSERT INTO Buildings values (6,'Building6','Mumbai')

INSERT INTO Buildings values (7,'Building7','LA')

INSERT INTO Buildings values (8,'Building8','LA')

INSERT INTO Buildings values (9,'Building9','LAS')

INSERT INTO Buildings values (10,'Building10','LAS')

--Clustered Index Seek

select Buildingid FROM Buildings WHERE Buildingid=1

As you can see above, since we have where clause on Clustered Index and SELECT also have a clustered column, a clustered Index seek will happen.

--Clustered Index Scan

select Buildingid FROM Buildings WHERE BuildingName='Empire State'

Now since where clause has a non clustered column, there is no way for query optimizer to fetch data for BuildingName. So as shown above, entire clustered index will be scanned. NOTE that clustered index in nothing but table itself which is sorted based on some column, BuildingId here.

--Create a supporting Index

create index IDX_Buildings_BuildingName on Buildings(BuildingName)

--Scan Is converted to non clustered Index Seek

select Buildingid FROM Buildings WHERE BuildingName='Empire State'

As you can see above, Scan Is converted to non clustered Index Seek and new index is used.

--A scan or Lookup

select BuildingLocation FROM Buildings WHERE BuildingName='Empire State'

The above query will give Clustered Index Scan or a Key Lookup depending upon amount of data. Here data is less, so optimizer decides to do a scan . Why this Happened? Because BuildingLocation is not part of any index. So there is no way for optimizer to get this data, so it has to do a scan. To do away with this scan, we need to create a covering index.

--Covering Index

create index IDX_Buildings_BuildingName2 on Buildings(BuildingName) INCLUDE(BuildingLocation)

--A Perfect Seek now

select BuildingLocation FROM Buildings WHERE BuildingName='Empire State'

Now the index IDX_Buildings_BuildingName2 is used and a perfect seek happens because query has all supporting indexes now.

Now do we really need IDX_Buildings_BuildingName any more. Not actually. Figure out why not.

Best Java Training Classes in Gurgaon | Why Learn Java ?

If you are not coming from C and C++ background, and want to learn your first programming language, I will suggest to choose Java. In this article, We will share our list of reason, and why you should learn Java programming and why we think Java is best programming language created ever.

1) Java is Easy to learn

Many would be surprised to see this one of the top reason for learning Java, or considering it as best programming language, but it is. If you have a steep learning curve, it would be difficult to get productive in a short span of time, which is the case with most of professional project.

Java has fluent English like syntax with minimum magic characters e.g. Generics angle brackets, which makes it easy to read Java program and learn quickly.

Once a programmer is familiar with initial hurdles with installing JDK and setting up PATH and understand How Classpath works, it's pretty easy to write program in Java.

2) Java is an Object Oriented Programming Language

Another reason, which made Java popular is that it's an Object Oriented Programming language. Developing OOPS application is much easier, and it also helps to keep system modular, flexible and extensible.

Once you have knowledge of key OOPS concept like Abstraction, Encapsulation, Polymorphism and Inheritance, you can use all those with Java. Java itself embodies many best practices and design pattern in it's library.

Java is one of the few close to 100% OOPS programming language. Java also promotes use of SOLID and Object oriented design principles in form of open source projects like Spring, which make sure your object dependency is managed well by using dependency Injection principle.

3) Java has Rich API

One more reason of Java programming language's huge success is it's Rich API and most importantly it's highly visible because come with Java installation. When I first started Java programming, I used to code Applets and those days Applets provides great animation capability, which amazes new programmer like us, who are used to code in Turbo C++ editor. Java provides API for I/O, networking, utilities, XML parsing, database connection, and almost everything. Whatever left is covered by open source libraries like Apache Commons, Google Guava, and others.

4) Powerful development tools e.g. Eclipse , Netbeans

Believe it or not, Eclipse and Netbeans has played a huge role to make Java one of the best programming languages. Coding in IDE is a pleasure, especially if you have coded in DOS Editor or Notepad.

They not only helps in code completion but also provides powerful debugging capability, which is essential for real world development. Integrated Development Environment (IDE) made Java development much easier, faster and fluent. It's easy to search, refactor and read code using IDEs.

Apart from IDE, Java platform also has several other tools e.g.. Maven and ANT for building Java applications, decompilers, JConsole, Visual VM for monitoring Heap usage etc.

5) Great collection of Open Source libraries

Open source libraries ensures that Java should be used everywhere. Apache, Google, and other organization has contributed lot of great libraries, which makes Java development easy, faster and cost effective.

There are framework like Spring, Struts, Maven, which ensures that Java development follows best practices of software craftsmanship, promotes use of design patterns and assisted Java developers to get there job done.

I always recommend to search for a functionality in Google, before writing your own code. There is good chance that, it's already coded, tested and available for ready to use.

6) Wonderful community support

Community is the biggest strength of Java programming language and platform. No matter, How good a language is, it wouldn't survive, if there is no community to support, help and share there knowledge. Java has been very lucky, it has lots of active forums, Stackoverflow, open source organizations and several Java user group to help everything.

There is community to help beginners, advanced and even expert Java programmers. Java actually promotes taking and giving back to community habit. Lots of programmers, who use open source, contribute as commiter, tester etc. Expert programmer provides advice FREE at various Java forums and stackoverflow. This is simply amazing and gives lot of confidence to a newbie in Java.

7) Java is FREE

People like FREE things, Don't you? So if a programmer want to learn a programming language, or a organization wants to use a technology, COST is an important factor. Since Java is free from start, i.e. you don't need to pay anything to create Java application. This FREE thing also helped Java to become popular among individual programmers, and among large organizations. Availability of Java programmers is another big think, which makes organization to choose Java for there strategic development.

8) Excellent documentation support - Javadocs

When I first saw Javadoc, I was amazed. It's great piece of documentation, which tells lot of things about Java API. I think without Javadoc documentation, Java wouldn't be as popular, and it's one of the main reason, Why I think Java is best programming language.

Not every one has time and intention to look at code to learn what a method do . RedBush Tech made learning easy, and provide an excellent reference while coding in Java. With advent of IDE, you don't even need to look Javadoc explicitly in browser, but you can get all information in your IDE window itself.

9) Java is Platform Independent

In 1990s, this was the main reason of Java's popularity. Idea of platform independence is great, and Java's tag line "write once run anywhere" was enticing enough to attract lots of new development in Java. This is still one of the reason of Java being best programming language, most of Java applications are developed in Windows environment and run in UNIX platform.

10) Java is Everywhere

Yes, Java is everywhere, it's on desktop, it's on mobile, it's on card, almost everywhere and so is Java programmers. I think Java programmer out number any other programming language professional. Though I don't have any data to back this up, but it's based on experience. This huge availability of Java programmers, is another reason, why organization prefer to choose Java for new development than any other programming language.

Having said that, programming is very big field and if you look at C and UNIX, which is still surviving and even stronger enough to live another 20 years, Java also falls in same league. Though there are lot of talk about functional programming, Scala and other JVM languages, but they need to go a long way to match community, resources and popularity of Java. Also OOPS is one of the best programming paradigm, and as long as it will be there Java will remain solid.

To get help in Java NetBeans projects and Java classes for 2nd Semester, you can contact - 997148322 or email- support@redbushtechnologies.com

Thank you
Team RedBush

C++ Project for 11th,12th CS students- Student Report Card Download FREE

This C++ mini project on STUDENT REPORT CARD has student class with data members like roll no, name, marks and grade. Member functions in this class are used for accept / display details of students and a function to calculate grade based on marks obtained by student. Student Records are stored in binary file. This menu driven program illustrates read, write, search, modify and delete operations in binary file.

To Download Click - http://redbushtechnologies.com/CPP-Projects-gurgaon.php

If you didnt understand anything, feel free to write or call us at 9971483222

Team RedBush

E-Commerce website Online Shoe Shopping | Asp.Net C# Project for MCA, M.Tech, B.Tech Students

Dear Students, above mentioned project is fully documented with detailed report and source code. It is based on ASP.NET, C# and SQL Server as database. Any student who wants to submit this as their  final year project can contact us at - +91 9971483222 .

ASP.NET C# SQL Server Project : Online Movie Ticket Booking : For B.tech, M.tech, MCA, BCA Final year students

Dear Students, above mentioned project is fully documented with detailed report and source code. It is based on ASP.NET, C# and SQL Server as database. Any student who wants to submit this as their final year project can contact us at - +91 9971483222 .

SQL DBA Training Review by New Zealander John Williams

John O'Williams flew from New Zealand for a fast track course on Microsoft SQL DBA . Listen to what he says and how he rates our trainer Mr Suresh.

If you want to enroll in a very economical course from a champion trainer , feel free to contact us at -

support@redbushtechnologies.com or register at - http://redbushtechnologies.com/contactus.php

Thank you

C plus plus Interview Questions with answers for freshers and experienced

As all engineering guys know, what it means. It  means you have started to take interviews either on campus or off campus. You are bound to know all these questions by heart as answering them will get you in any MNC. Answering C++ questions will launch your developer career. We have prepared these questions with a team of working professionals and academicians. These are absolutely FREE. Since there are more than 100 questions with answers, hence it wasn't possible to put all of them here , we are putting a LIVE link at the end of this post from where you can download the full file. So Enjoy and make steps towards your first job. Explain abstraction. - Simplified view of an object in user’s language is called abstraction. - It is the simplest, well-defined interface to an object in OO and C++ that provides all the expected features and services to the user in a safe and predictable manner. - It provides all the information that the user requires. - Good domain knowledge is important for effective abstraction. - It separates specifications from implementation & keeps the code simpler and more stable.

What is the real purpose of class – to export data? No, the real purpose of a class is not to export data. Rather, it is to provide services. Class provides a way to abstract behaviour rather than just encapsulating the bits. What things would you remember while making an interface? - A class’s interface should be sensible enough. It should behave the way user expects it to. - It should be designed from the outside in.

Explain the benefits of proper inheritance. The biggest benefits of proper inheritance are : 1. Substitutability 2. Extensibility. 1. Substitutability : The objects of a properly derived class can be easily and safely substituted for an object of its base class. 2. Extensibility : The properly derived class can be freely and safely used in place of its base class even if the properly derived class is created a lot later than defining the user code. Extending the functionalities of a system is much easier when you add a properly derived class containing enhanced functionalities.

Does improper inheritance have a potential to wreck a project? - Many projects meet a dead end because of bad inheritance. So, it certainly has the potential to wreck a project. - Small projects still have a scope to avoid the complete consequence of bad inheritance if the developers communicate and co-ordinate with an easy system design. This kind of a luxury is not possible in big projects, which means that the code breaks in a way difficult and at times impossible way to fix it.

How should runtime errors be handled in C++? - The runtime errors in C++ can be handled using exceptions. - This exception handling mechanism in C++ is developed to handle the errors in software made up of independently developed components operating in one process and under synchronous control. - According to C++, any routine that does not fulfil its promise throws an exception. The caller who knows the way to handle these exceptions can catch it.

When should a function throw an exception? - A function should throw an exception when it is not able to fulfil its promise. - As soon as the function detects a problem that prevents it from fulfilling its promise, it should throw an exception. - If the function is able to handle the problem, recover itself and deliver the promise, then the exception should not be thrown. - If an event happens very frequently then exception handling is not the best way to deal with it. It requires proper fixation.

Where are setjmp and longjmp used in C++? -Setjmp and longjmp should not be used in C++. - Longjmp jumps out of the function without unwinding the stack. This means that the local objects generated are not destructed properly. - The better option is to use try/catch/throw instead. They properly destruct the local objects.

Are there any special rules about inlining? Yes, there are a few rules about inlining : 1. Any source files that used the inline function must contain the function’s definition. 2. An inline function must be defined everywhere. The easier way to deal with this to define the function once in the class header file and include the definition as required. The harder way is to redefine the function everywhere and learn the one-definition rule. 3. Main() can not be inline.

Explain One-Definition Rule (ODR). - According to one-definition rule, C++ constructs must be identically defined in every compilation unit they are used in. - As per ODR, two definitions contained in different source files are called to be identically defined if they token-for-token identical. The tokens should have same meaning in both source files. - Identically defined doesn’t mean character-by-character equivalence. Two definitions can have different whitespace or comments and yet be identical. What are the advantages of using friend classes? - Friend classes are useful when a class wants to hide features from users which are needed only by another, tightly coupled class. - Implementation details can be kept safe by providing friend status to a tightly cohesive class.

What is the use of default constructor? - It is a constructor that does not accept any parameters. - If there is no user-defined constructor for a class, the compiler declares a default parameterless constructor called default constructor. - It is an inline public member of its class. - When the compiler uses this constructor to create an object – the constructor will have no constructor initializer and a null body.

Differentiate between class and structure. - The members of structures are public while those of a class are private. - Classes provide data hiding while structures don’t. - Class bind both data as well as member functions while structures contain only data.

Explain container class. -Class to hold objects in memory or external storage. It acts as a generic holder. - It has a predefined behaviour and a known interface. - It is used to hide the topology used for maintaining the list of objects in memory. - The container class can be of two types: 1. Heterogeneous container : Here the container class contains a group of mixed objects 2. Homogeneous container : Here the container contains all the same objects.

What is namespace? - Namespaces are used to group entities like classes, objects and functions under a name. Explain explicit container. - These are constructors that cannot take part in an implicit conversion. - These are conversion constructors declared with explicit keyword. - Explicit container is reserved explicitly for construction. It is not used by the compiler to implement an implied conversion of types.

Explain class invariant. - It is a condition that ensures correct working of a class and defines all the valid states for an object. - When an object is created class invariants must hold. - It is necessary for them to be preserved under all operations of the class. - All class invariants are both preconditions as well as post-conditions for all operations or member functions of the class.

Differentiate between late binding and early binding. What are the advantages of early binding? - Late binding refers to function calls that are not resolved until run time while early binding refers to the events that occur at compile time. - Late binding occurs through virtual functions while early binding takes place when all the information needed to call a function is known at the time of compiling. - Early binding increases the efficiency. Some of the examples of early binding are normal function calls, overloaded function calls, and overloaded operators etc.

Explain public, protected, private in C++? These are three access specifiers in C++. 1. Public : Here the data members and functions are accessible outside the class. 2. Protected : Data members and functions are available to derived classes only. 3. Private : Data members and functions are not accessible outside the class.

Explain Copy Constructor. It is a constructor which initializes it's object member variable with another object of the same class. If you don't implement a copy constructor in your class, the compiler automatically does it. When do you call copy constructors? Copy constructors are called in these situations : 1. When compiler generates a temporary object. 2. When a function returns an object of that class by value . 3. When the object of that class is passed by value as an argument to a function . 4. When you construct an object based on another object of the same class.

Name the implicit member functions of a class. 1. default constructor 2. copy constructor 3. assignment operator 4. default destructor 5. address operator

Explain storage qualifiers in C++. 1. Const : This variable means that if the memory is initialised once, it should not be altered by a program. 2. Volatile : This variable means that the value in the memory location can be altered even though nothing in the program code modifies the contents. 3. Mutable : This variable means that a particular member of a structure or class can be altered even if a particular structure variable, class or class member function is constant.

Explain dangling pointer. - When the address of an object is used after its lifetime is over, dangling pointer comes into existence. - Some examples of such situations are : Returning the addresses of the automatic variables from a function or using the address of the memory block after it is freed. In what situations do you have to use initialization list rather than assignment in constructors? - When you want to use non-static const data members and reference data members you should use initialization list to initialize them. When does a class need a virtual destructor? - If your class has at least one virtual function, you should have a virtual destructor. This allows you to delete a dynamic object through a baller to a base class object. In absence of this, the wrong destructor will be invoked during deletion of the dynamic object.

What is the type of “this” pointer? When does it get created? - It is a constant pointer type. It gets created when a non-static member function of a class is called. How would you differentiate between a pre and post increment operators while overloading? - Mentioning the keyword int as the second parameter in the post increment form of the operator++() helps distinguish between the two forms. What is a pdb file? - A program database (PDB) file contains debugging and project state information that allows incremental linking of a Debug configuration of the program. This file is created when you compile a C/C++ program with /ZI or /Zi or a Visual Basic/C#/JScript .NET program with /debug.

You run a shell on UNIX system. How would you tell which shell are you running? - To check this you can simply do the Echo $RANDOM. - The results will be : 1. Undefined variable if you are from the C-Shell, 2. A return prompts if you are from the Bourne shell, 3. A 5 digit random number if you are from the Korn shell. You could also do a ps -l and look for the shell with the highest PID.

What are Stacks? Give an example where they are useful. A Stack is a linear structure in which insertions and deletions are always made at one end i.e the top - this is termed as Last in First out (LIFO). Stacks are useful when we need to check some syntax errors like missing parentheses. Differentiate between an external iterator and an internal iterator? What is the advantage of an external iterator. An external iterator is implemented as a separate class that can be "attach" to the object that has items to step through while an internal iterator is implemented with member functions of the class that has items to step through. With an external iterator many different iterators can be active simultaneously on the same object - this is its basic advantage.  Click here for downloading all C++ interview questions.   If you face difficulty in answering any questions or you think you need help, feel free to write to us. Email- support@redbushtechnologies.com Thank you Team RedBush