Sql server basic interview questions and answers pdf
File Name: sql server basic interview questions and answers .zip
- 100+ Most Popular SQL Interview Questions And Answers
- Top 50 SQL Server Interview Questions & Answers
- Top 50 SQL Interview Questions and Answers (2021 Update)
- SQL Server Interview Questions & Answers Book
A database is a collection of information in an organized form for faster and better access, storage, and manipulation. It can also be defined as a collection of tables, schema, views, and other database objects. Data warehouse refers to a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining as well as online processing. A table is a database object used to store records in a field in the form of columns and rows that holds data.
100+ Most Popular SQL Interview Questions And Answers
There are a lot of opportunities from many reputed companies in the world. If you would like to enrich your career, then visit Mindmajix - a global online training platform: SQL Server Online Course this course will help you to achieve excellence in this domain. It is the way to eliminate redundant data. It is the reverse process of normalization. It increases query performance by reducing the joins. It is used for OLAP applications.
Relationships in SQL server are explained below. Primary Key. Candidate key, Alternate key, Composite key. It is a value that will be used by a column if no value is supplied to that column while inserting data.
Lets you extend the base SQL Server data types by providing a descriptive name and format to the database. A transaction is a logical unit of work in which, all the steps must be performed or none. These are the properties of a transaction. It plays a very major part. When building a new system, or adding to an existing system, it is crucial that the design is correct.
Ensuring that the correct data is captured and is placed in the appropriate tables, that the right relationships exist between the tables, and that data redundancy is eliminated is an ultimate goal when considering performance. Planning a design should be an iterative process, and constantly reviewed as an application is developed.
It is rare, although it should be the point that everyone tries to achieve, when the initial design and system goals are not altered, no matter how slightly. Therefore, a designer has to be on top of this and ensure that the design of the database remains efficient. A developer must investigate volumes of data capacity planning , what types of information will be stored, and how that data will be accessed. If you are dealing with an upgrade to an existing system, analyzing the present data and where existing data volumes occur, how that data is accessed and where the current response bottlenecks are occurring, can help you search for problem areas in the design.
A new system would require a thorough investigation of what data will be captured, and looking at volumes of data held in other formats also will aid design. Knowing your data is just as important as knowing the constituents of your data. Also, constantly revisit your design. As your system is built, check relationships, volumes of data, and indexes to ensure that the physical design is still at its optimum.
Cursors allow row-by-row processing of the result sets. Static — Makes a temporary copy of the data and stores in tempdb and any modifications on the base table does not reflect in data returned by fetches made by the cursor. Dynamic — Reflects all changes in the base table. Forward-only — specifies that the cursor can only fetch sequentially from first to last. Each time you fetch a row from the cursor, it results in a network roundtrip, whereas a normal SELECT query makes only one roundtrip, however large the result set is.
Cursors are also costly because they require more resources and temporary storage results in more IO operations. Most of the time set-based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria:. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:. Joins are used in queries to explain how different tables are related.
Joins also let you select data from a table depending upon data from another table. Yes, very much. In SQL Server 6. From SQL Server 7. But in 7. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Till SQL Server 7. So in a way, they are called post triggers. Virtual tables — Inserted and Deleted from the basis of trigger architecture. Self-join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.
This will happen by the time when we retrieve data from the database. A JOIN selects columns from 2 or more tables. Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i. It updates information about the distribution of key values for one or more statistics groups collections in the specified table or indexed view.
It returns the most recently created identity value for the tables in the current execution scope. Then, of course, there is the security aspect, where, by building a stored procedure, you can place a great deal of security around it. When dealing with sensitive data, you can use an encrypted stored procedure to hide sensitive columns, calculations, and so on. Finally, by using a stored procedure, I feel that transactional processing becomes a great deal easier and, in fact, using nested transactions become more insular and secure.
Having to deal with transactions within code that may have front-end code, will slow up a transaction, and therefore a lock will be held for longer than necessary. Fast performing stored procedures are like several other areas within T-SQL. Revisiting stored procedures every six months or so, to ensure that they are still running at their optimum performance is essential.
However, actual techniques themselves include working with as short a transaction area as possible, as lock contention will certainly impact performance. Recompiling your stored procedures after index additions if you are unable or not wishing to restart SQL Server, will also ensure that a procedure is using the correct index if that stored procedure is accessing the table which has received the new index.
If you have a T-SQL command that joins several tables, and it takes a long time to return a value, first of all, check out the indexes.
But what you may find tends to help, is to break down the code and try to determine which join it is that is causing the performance problem. Then analyze this specific join and see why it is a problem. Also, try to look at using subqueries when you are trying to find a handful of values in the subquery statement, and there is no key on the column you are looking upon. SQL Server cursors are perfect when you want to work one record at a time, rather than taking all the data from a table as a single bulk.
However, they should be used with care as they can affect performance, especially when the volume of data increases. There will be times when it is not possible to avoid cursors, and I doubt if many systems exist without them.
If you do find you need to use them, try to reduce the number of records to process by using a temporary table first, and then building the cursor from this.
The lower the number of records to process, the faster the cursor will finish. Perhaps one of the performance gains least utilized by developers starting out in SQL Server are temporary tables.
For example, using one or more temporary tables to break down a problem into several areas could allow blocks of data to be processed in their own individual way, and then at the end of the process, the information within the temporary tables merged and applied to the underlying data. The main area of your focus should be, is there an alternative way of doing things? Even if I have to break this down into several chunks of work, can I do this work without using cursors, and so result in faster performance.
Another area that you can look at is the use of CASE statements within your query. By using a CASE statement, you can check the value within a column and make decisions and operations based on what you have found.
Although you will still be working on a whole set of data, rather than a subset found in a cursor, you can use CASE to leave values, or records as they are, if they do not meet the right criteria. Care should be taken here though, to make sure that by looking at all the data, you will not be creating a large performance impact.
Again, look at using a subset of the data by building a temporary table first, and then merging the results in afterward. Cursors can be faster if you are dealing with small amounts of data.
However, what I have found, to be rule number one, is to get a little data into your cursor as is needed.
Perhaps the best performance gain is when you can create a cursor asynchronously rather than needing the whole population operation to be completed before further processing can continue. Then, by checking specific global variables settings, you can tell when there is no further processing to take place. However, even here, care has to be taken. The asynchronous population should only occur on large record sets rather than those that only deal with a small number of rows.
Use the smallest set of data possible. Break out of the cursor loop as soon as you can. If you find that a problem has occurred, or processing has ended before the full cursor has been processed, then exit. If you are using the same cursor more than once in a batch of work, and this could mean within more than one stored procedure, then define the cursor as a global cursor by using the GLOBAL keyword, and not closing or deallocating the cursor until the whole process is finished.
A fair amount of time will be saved, as the cursor and the data contained will already be defined, ready for you to use. This is a very open-ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be:. An ER diagram or Entity-Relationship diagram is a special picture used to represent the requirements and assumptions in a system from a top-down perspective.
It shows the relations between entities tables in a database. A prime attribute is an attribute that is part of a candidate key.
The ACID properties. Atomicity, Consistency, Isolation, and Durability. A non-prime attribute is an attribute that is not a part of a candidate key. This means the transaction finish completely, or it will not occur at all.
Top 50 SQL Server Interview Questions & Answers
I have listed all the series in this post so that it can be easily downloaded and used. All the questions are collected and listed in one PDF which is here to download. If you have any question or if you want to add to any of the questions please send me a email or write a comment. In this very first blog post — various aspect of the interview questions and answers are discussed. Some people like the subject for their helpful hints and thought provoking subject, and others dislike these posts because they feel it is nothing more than cheating. What is SQL Azure?
TRICKY SQL SERVER QUERIES INTERVIEW QUESTIONS ANSWERS 4 FRESHER AND CLICK HERE TO DOWNLOAD + SQL SERVER PDF. Here you will find from basic to most puzzled/complex interview queries so that this set is for.
Top 50 SQL Interview Questions and Answers (2021 Update)
Now the client wants to insert a record after the identity value 7 with its identity value starting from Write a single query to calculate the sum of all positive values of x and he sum of all negative values of x. Although there are 4 customers not referred by Jane Smith including Jane Smith herself , the query will only return one: Pat Richards.
Our Most Popular Learning Paths.
SQL Server Interview Questions & Answers Book
Very nice collection, Pls send me PDF's to my email pavan gmail. Great article. It is really helpfull. Pls send me a copy also. Hi sir,this interview questions are very useful to me Thank u sir and Please send Pdf to my mail satish. Please send me all sql and c and asp.
Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the answer that lets the interviewer know how suitable are you for the position to which you are being interviewed. What is the difference between Clustered and Non-Clustered Index? When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table. In a Non-Clustered index, the leaf level pages do not contain data pages instead it contains pointers to the data pages.
There are a lot of opportunities from many reputed companies in the world. If you would like to enrich your career, then visit Mindmajix - a global online training platform: SQL Server Online Course this course will help you to achieve excellence in this domain. It is the way to eliminate redundant data. It is the reverse process of normalization. It increases query performance by reducing the joins. It is used for OLAP applications. Relationships in SQL server are explained below.
Top 50 sql server interview questions and answers pdf The experience, you have gotten by this interview insight you to crack technical interviews.
MySQL DBA Certification Training
Interview Questions and Answers are now updated with the latest questions and answers. I enjoyed writing questions and answers. I have gotten many emails about complete series. Top most requests were to collect series in one big post so they can be easily used. I was asked to provide links to download them so they can be printed and referred.
Download PDF 1. What are the two authentication modes in SQL Server? There are two authentication modes — Windows Mode Mixed Mode Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page. This is mainly used to capture and save data about each event of a file or a table for analysis. What is recursive stored procedure? SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively.