IntroductionIn this article we will go through the most basic and frequently asked interview questions on SQL Server. Please do not think that I am writing this article to show shortcuts to candidates who are searching for jobs on SQL Server. But I think no matter how much experience you have, an interview is a different ball game. A good project executioner can get knocked off on simple SQL Server questions. This article will be a complete series, so here's my first from the series - SQL Server Interview Questions Part 1.Feel free to write to me at or else you can also visit for more such interview questions.Happy job hunting.
Interview Questions on Database ConceptsChapter 1: Database ConceptsTwist: What is the difference between a file and a database? Can files qualify as a database?Note: Probably these questions are too basic for experienced SQL SERVER guys. But from a fresher’s point of view, it can be a difference between getting a job and being jobless. Database provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information. Secondly, the information has to be persistent, that means even after the application is closed the information should be persisted.
Finally, it should provide an independent way of accessing data and should not be dependent on the application to access the information.Ok, let me spend a few more sentences on explaining the third aspect. Below is a simple figure of a text file that has personal detail information. The first column of the information is Name, second Address and finally Phone Number. This is a simple text file, which was designed by a programmer for a specific application.
Figure 1.1: Non-Uniform Text FileIt works fine in the boundary of the application. Now, some years down the line a third party application has to be integrated with this file. In order for the third party application to be integrated properly, it has the following options:. Use the interface of the original application. Understand the complete details of how the text file is organized, example the first column is Name, then Address and finally Phone Number. After analyzing, write a code which can read the file, parse it etc.
Hmm, lot of work, right.That’s what the main difference is between a simple file and a database; database has an independent way (SQL) of accessing information while simple files do not (That answers my twisted question defined above). File meets the storing, managing and retrieving part of a database, but not the independent way of accessing data.Note: Many experienced programmers think that the main difference is that file cannot provide multi-user capabilities which a DBMS provides. But if you look at some old COBOL and C programs where files were the only means of storing data, you can see functionalities like locking, multi-user etc.
Provided very efficiently. So it’s a matter of debate. If some interviewers think of this as a main difference between files and database, accept it going in to debate means probably losing a job.(Just a note for fresher’s: Multi-user capabilities mean that at one moment of time more than one user should be able to add, update, view and delete data. All DBMS' provides this as in-built functionalities, but if you are storing information in files, it’s up to the application to write logic to achieve these functionalities).As mentioned before, DBMS provides a systematic and organized way of storing, managing and retrieving from a collection of logically related information.
RDBMS also provides what DBMS provides, but above that, it provides relationship integrity. So in short, we can say:RDBMS = DBMS + REFERENTIAL INTEGRITYFor example, in the above Figure 1.1, every person should have an Address.
This is a referential integrity between Name and Address. If we break this referential integrity in DBMS and files, it will not complain, but RDBMS will not allow you to save this data if you have defined the relation integrity between person and addresses. These relations are defined by using “Foreign Keys” in any RDBMS.Many DBMS companies claimed that their DBMS product was RDBMS compliant, but according to industry rules and regulations, if the DBMS fulfills the twelve CODD rules, it’s truly a RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc.) fulfill all the twelve CODD rules and are considered truly as RDBMS.Note: One of the biggest debates is whether Microsoft Access is an RDBMS? We will be answering this question in later section.Twist: Does SQL SERVER support all the twelve CODD rules?Note: This question can only be asked on two conditions when the interviewer is expecting you to be at a DBA job or you are complete fresher, yes and not to mention the last one he treats CODD rules as a religion. We will try to answer this question from the perspective of SQL SERVER.In 1969, Dr.
Codd laid down 12 rules, which a DBMS should adhere to in order to get the logo of a true RDBMS. Rule 1: Information Rule'All information in a relational database is represented explicitly at the logical level and in exactly one way - by values in tables.'
In SQL SERVER, all data exists in tables and are accessed only by querying the tables. Rule 2: Guaranteed Access Rule'Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.' In flat files, we have to parse and know the exact location of field values. But if a DBMS is truly an RDBMS, you can access the value by specifying the table name, field name, for instance Customers.Fields ‘Customer Name’.SQL SERVER also satisfies this rule. In ADO.NET we can access field information using table name and field names. Rule 3: Systematic Treatment of Null Values'Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.”In SQL SERVER, if there is no data existing, NULL values are assigned to it.
Note NULL values in SQL SERVER do not represent spaces, blanks or a zero value; it is a distinct representation of missing information and thus satisfies rule 3 of CODD. Rule 4: Dynamic On-line Catalog Based on the Relational Model'The database description is represented at the logical level in the same way as ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data.' The Data Dictionary is held within the RDBMS. Thus, there is no need for off-line volumes to tell you the structure of the database. Rule 5: Comprehensive Data Sub-language Rule'A relational system may support several languages and various modes of terminal use (for example, the fill-in-the-blanks mode). Figure 1.2: File Server Architecture of AccessIn client server architecture, the above limitation of the file server architecture is removed.
In client server architecture, you have two entities, client and the database server. File server is now replaced by database server. Database server takes up the load of processing any database related activity and the client does any validation aspect of database. As the work is distributed between the entities it increases scalability and reliability. Second, the network traffic also comes down as compared to file server. For example if you are requesting customers from India, database server will sort/ filter and send only Indian customer details to the client, thus bringing down the network traffic tremendously.
SQL SERVER follows the client-server architecture. Figure 1.3: Client Server Architecture of SQL SERVER. The second issue comes in terms of reliability. In Access, the client directly interacts with the Access file, in case there is some problem in the middle of a transaction, there are chances that an Access file can get corrupt.
But in SQL SERVER, the engine sits in between the client and the database, so in case of any problems in the middle of a transaction, it can revert back to its original state.Note: SQL SERVER maintains a transaction log by which you can revert back to your original state in case of any crash. When your application has to cater to a huge load demand, highly transactional environment and high concurrency, then its better to go for SQL SERVER or MSDE. But when it comes to cost and support, Access stands better than SQL SERVER.
In case of SQL SERVER, you have to pay for per client license, but Access runtime is free.Summarizing: SQL SERVER gains points in terms of network traffic, reliability and scalability whereas Access gains points in terms of cost factor.MSDE is a royalty free, redistributable and cut short version of the giant SQL SERVER database. It is primarily provided as a low cost option for developers who need a database server, which can easily be shipped and installed. It can serve as a good alternative for Microsoft Access database as it overcomes quite a few problems which Access has.Below is a complete list, which can give you a good idea of the differences:. Size of database: Microsoft Access and MSDE have a limitation of 2GB while SQL SERVER has 1,048,516 TB1. Performance degrades in MSDE 2000 when maximum number of concurrent operations goes above 8 or is equal to 8. It does not mean that you cannot have more than eight concurrent operations but the performance degrades. Eight-connection performance degradation is implemented by using SQL SERVER 2000 workload governor (we will be looking into more detail of how it works).
As compared to SQL SERVER 2000, you can have 32,767 concurrent connections. MSDE does not provide OLAP and Data warehousing capabilities. MSDE does not have support facility for SQL mail.
MSDE 2000 does not have GUI administrative tool such as enterprise manager, Query analyzer or Profiler. Figure 1.6: One-to-Many Relationship ER diagram. Many-to-manyIn this, one record in one table corresponds to many rows in another table and also vice-versa.For instance: In a company, one employee can have many skills like Java, C# etc. And also one skill can belong to many employees.Given below is a sample of many-to-many relationship.
One employee can have knowledge of multiple Technology. So in order to implement this, we have one more table Employee Technology which is linked to the primary key of Employee and Technology table. Figure 1.7: Many-to-Many Relationship ER diagramNote: A regular.NET programmer working on projects often stumbles on this question, which is but obvious. The bad part is sometimes the interviewer can take this as a very basic question to be answered and it can be a turning point for the interview.
So let's cram it.It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization.Benefits of Normalizing your database include:. Avoiding repetitive entries.
Reducing required storage space. Preventing the need to restructure existing tables to accommodate new data. Increased speed and flexibility of queries, sorts, and summariesNote: During an interview, people expect to answer a maximum of three normal forms and that's what is expected practically. Actually you can normalize database to fifth normal form. But believe this book, answering three normal forms will put you in a decent shape during an interview.The three normal forms as follows: First Normal FormFor a table to be in first normal form, data must be broken up into the smallest units possible.
In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields. Figure 1.12: Fill third normal formSo now the Total field is removed and is the multiplication of Unit price. Qty.Denormalization is the process of putting one fact in numerous places (it is vice-versa of normalization). Only one valid reason exists for denormalizing a relational design - to enhance performance. The sacrifice to performance is that you increase redundancy in a database.Note: Whenever the interviewer is trying to go above the third normal form, there can be two reasons, ego or to fail you. Three normal forms are really enough, practically anything more than that is an overdose.In fourth normal form, it should not contain two or more independent multi-valued facts about an entity and it should satisfy “Third Normal form”.So let us try to see what multi-valued facts are. If there are two or more many-to-many relationship in one entity and they tend to come to one place, it is termed as “multi-valued facts”.
Figure 1.14: Normalized to Fourth Normal form.Note: UUUHHH if you get this question after joining the company, do ask him if he himself really uses it?Fifth normal form deals with reconstructing information from smaller pieces of information. These smaller pieces of information can be maintained with less redundancy.Example: Dealers sell Product which can be manufactured by various Companies. Dealers in order to sell the Product should be registered with the Company. So these three entities have a mutual relationship within them. Figure 1.15: Not in Fifth Normal Form.The above table shows some sample data.
If you observe closely, a single record is created using lot of small information. For instance: JM Associate can sell sweets under the following two conditions:. JM Associate should be an authorized dealer of Cadbury. Sweets should be manufactured by Cadbury companyThese two smaller bits of information form one record of the above given table. So in order for the above information to be “Fifth Normal Form” all the smaller information should be in three different places.
Below is the complete fifth normal form of the database. Figure 1.16: Complete Fifth Normal FormNote: There is a huge similarity between Fourth and Fifth normal form, i.e. They address the problem of “Multi-Valued facts”.“Fifth normal form” multi-valued facts are interlinked and “Fourth normal form” values are independent. For instance in the above two questions Supplier/ Product and Supplier/ Location are not linked. While in fifth form, the Dealer/ Product/ Companies are completely linked.Note: Arrrrggghhh yes there exists a sixth normal form also.
But note guys you can skip this statement. Just in case you want to impress the interviewer.If you want a relational system in conjunction with time, you use sixth normal form. At this moment SQL Server does not support it directly.Twist: What is the relationship between Extent and Page?Extent is a basic unit of storage to provide space for tables. Every extent has a number of data pages. As new records are inserted new data, pages are allocated. There are eight data pages in an extent.
So as soon as the eight pages are consumed, it allocates a new extent with data pages.While extent is basic unit storage from a database point of view, page is a unit of allocation within extent.Page has three important sections:. Page header.
Actual data i.e. Data row. Row pointers or Row offsetPage header has information like timestamp, next page number, previous page number etc.Data rows are where your actual row data is stored.
Networking Interview Questions By Shivprasad Koirala Family
For every data row, there is a row offset which points to that data row. Figure 1.17: General view of a ExtentPages are contained in extent. Every extent will have around eight data pages. But all the eight data pages are not created at once; they are created depending on data demand.
Networking Interview Questions By Shivprasad Koirala Biography
So when a page becomes full it creates a new page, this process is called as “Page Split”.Any SQL Server database is associated with two kinds of files:.mdf and.ldf.mdf files are actual physical database files where your data is stored finally.ldf (LOG) files are actually data, which is recorded from the last time data was committed in the database. Figure 1.19: Collation according to languageNote: Different languages will have different sort orders. Case SensitivityIf A and a, B and b, etc. Are treated in the same way, then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.
Accent SensitivityIf a and A, o and O are treated in the same way, then it is accent-insensitive. A computer treats a and A differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and A 225. The ASCII value of o is 111 and O is 243.
Shivprasad Koirala Mvc Interview Questions Book
Kana SensitivityWhen Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive. Width SensitivityWhen a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.Yes, you can specify different collation sequence for both the entities differently. Links For More Such Downloads. Ben Greenberg 3-Jan-18 22:543-Jan-18 22:54Wonderful work, however the topics covered here are less important than T-SQL questions, like joins or grouping.Normal forms is really funny thing. It widely given in universities. But when you start working, it's really rare when you check your database structure with normal forms. All that logic about normal forms could be replaced with single phrase - avoid data duplication where it's possible.Moreover too much optimization could hurt your app performance, so I would extend paragraph about 'denormalization' with a couple of examples.Anyway, the article is really great, in case you want to check out T-SQL questions, check out.
Because it's cool when you know all twelve CODD rules, but it's much more important to write correct SQL queries.
Shivprasad Koirala's.NET Interview Questions 7th Edition is a comprehensive book for Computer Science undergraduates and professionals taking up interviews for.NET profiles. The book targets freshers with little knowledge of C# trying to prepare for interviews. It begins with 50 critical frequently asked questions for the candidates to prepare when faced with a shortage of time and provides more than 600 questions prepared after consulting professions from all over the field. It also focuses on in-depth concepts of C#,.NET, ASO.NET, OOP, WCF, LINQ, UML, Silverlight and MVC among many other crucial topics which interviewers favour. In addition, the book comes bundled with a DVD which includes a mock interview video which helps the candidates understand how real life interviews are held and it also includes an MS Excel spreadsheet which will help candidates gauge their readiness for the interviews.
The DVD will also prove helpful in preparing a resume for the profile, including a sample resume and.NET project guidelines along with sample code to drive the concepts home. The book will prove essential to anyone facing.NET interviews across the industry as it goes beyond the conventional texts by preparing students for negotiating salaries and guiding them from the start to the finish of the interview.About Shivprasad KoiralaShivprasad Koirala is an author and CEO of an E-Learning company based in India.He has authored numerous texts on interview questions. Some of these are.NET Interview Questions, SQL Server Interview Questions, and Networking Interview Questions. Koirala is also an ASP/ASP.Net Microsoft MVP. This is a good book for final round of brush up and quick heads-up on some of the key topics. Not all topics are covered at good length but pretty useful.But subscription to Questponddvd.com or in DVD's, most of the topics are covered in detail.
This book is for junior level developers and can not 100% relay on this book to clear any interview. But I am still giving 5 star coz of efforts put to compile all topics.Suggestion: Please do grammar check before publish. Content review needs t.