Radus Blog

This is some blog description about this site

SQL Developers Questionnaries

 

 

Question:- What is your approach on designing a Database?

 

Answer:- First approach using life cycle and then ERD and create a prototype of your application and test the model.

 

Question:- What are the advantages and disadvantages of Normalization?

 

Answer:-Advantages: reduce the redundancy, conceptually cleaner, avoid null values and increase storage efficiency.

 

Disadvantages: complex query can put database down. Also too many joins required to produce reports.

 

Question:- When will you go for Normalization or De-normalization?

 

Answer:- It depends upon the condition at what level data base should be normalization and de-normalization.

 

Question:- Define third normal form and give an example of placing a model in third normal form.

 

Answer:- Define: No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflecting in all tables that link to the birthdate table.

 

Question:- What are constraints in SQL Server?

 

Answer:- Unique key constrain, Primary key constrain, check constrain, foreign key constrain, not null constrain, default constrain.

 

Question:- What is SQL Profiler?

 

Answer:- SQL Server Profiler is a rich interface to create and manage traces and analyze and replay trace results. The events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. Creating trace, Watching trace, Storing trace, Replaying trace.

 

Question:- What is an execution plan?

 

Answer:- Execution plans can tell you how a query will be executed, or how a query was executed. They are, therefore, the DBA's primary means of troubleshooting a poorly performing query. Rather than guess at why a given query is performing thousands of scans, putting your I/O through the roof, you can use the execution plan to identify the exact piece of SQL code that is causing the problem. For example, it may be scanning an entire table-worth of data when, with the proper index, it could simply backpack out only the rows you need. All this and more is displayed in the execution plan.

 

Question:- What is use of EXCEPT clause? How it differs from NOT IN clause

 

Answer:- EXCEPT clause is used to return records that are returned by the first query and NOT the second query. The number of fields and data types of both the queries should be the same. NOT In clause Consider this code that returns all the customers who don't have an order in the Orders table.

 

Question:- Explain the difference between RECOMPILE query hint and WITH RECOMPILE option.

 

Answer:- RECOMPILE query hint can be added to a stored procedure as query hint. This allows only that query in the stored procedure to recompile. WITH RECOMPILE option recompiles the whole stored procedure which might not be needed if the stored procedure has many queries and only some of them need to be recompiled.

 

Question:- What the difference is between and when to choose what: char, varchar, nvarchar, text, varchar (max)?

 

Answer:- nchar and nvarchar can store Unicode character.char and varchar cannot store Unicode characters. char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don't use up all that space. varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.

 

Question:- What are “Table Scan’s” and “Index Scan’s”?

 

Answer:- A table scan is where the table is processed row by row from beginning to end. An index scan is where the index is processed row by row from beginning to end. If the index is a clustered index then an index scan is really a table scan.

 

Question:- What are indexes? What are types of indexes?

 

Answer:- : Index is a method used for faster retrieval of records different types of indexes are

1.primary key index

2.unique index

3.bitmap index

4.hash index

5.function based index

6. B-tree index on table.

7.Virtual index

 

Question:-What is nested join, hash join and merge join in SQL Query plan?

 

Answer:- In nested joins, for each tuple in the outer join relation, the system scans the entire inner-join relation and appends any tuples that match the join-condition to the result set.

Merge join: Merge join If both join relations come in order, sorted by the join attribute(s), the system can perform the join trivially, thus: It can consider the current group of tuples from the inner relation which consists of a set of contiguous tuples in the inner relation with the same value in the join attribute.

Hash join: A hash join algorithm can only produce equial-joins. The database system pre-forms access to the tables concerned by building hash tables on the join-attributes.

Question:- How do you see the SQL plan in textual format?

 

Answer:- SET SHOWPLAN_TEXT ON

To quickly analyze a slow-running query, examine the query execution plan to determine what is causing the problem.

SET SHOWPLAN_TEXT causes SQL Server to return detailed information about how the statements are executed.

 

Question:-Is it a good database design to create indexes on the table in which lot of inserts occurs?

 

Answer:- Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance. Instead, I will focus on those indexes that are implemented most commonly in a SQL Server database.

 

Question:- What is “Fill Factor” concept in indexes? What is the best value for “FillFactor”?

 

Answer:- : Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100.

 

Question:- Define “Index statistics”.

 

Answer:- : Index statistics contain information about the distribution of index key values. By distribution, I mean the number of rows associated with each key value. SQL Server uses this information to determine what kind of execution plan to use when processing a query. As you insert, update, or delete rows in a table, the statistics on the updated table become out of date. They become out of date because SQL Server doesn't update the statistics every time you change a row in a table.

 

Question:- How can we see statistics of an index?

 

Answer:- Once statistics have been created, SQL Server then determines when to update those statistics based on how out-of-date the statistics might be. SQL Server identifies out of date statistics based on the number of inserts, updates, and deletes that have occurred since the last time statistics were updated, and then recreates the statistics based on a threshold. The threshold is relative to the number of records in the table. This means as you add and update records in a database table the index statistics slowly get out of date, until SQL Server determines you have added and/or updated enough records and then it recreates the statistics.

 

Question:- Define Fragmentation. How can we measure Fragmentation?

 

Answer:- Fragmentation can be defined as any condition that cause more than optimal amount of disk I/O to be performed in accessing a table or cause the longer disk. Optimal performance of SELECT queries occurs when the data pages of tables are contiguous as possible and pages are fully packed as possible. Fragmentation breaks rule and reduce the performance of the queries. Fragmentation can happen in two level . One is file system level fragmentation which is called as Logical/Physical Disk Fragmentation and Index level fragmentation. Each of them are described in the below sections.

 

Question:- What are the criteria to be considered while selecting an index?

 

Answer:- The selecting an index can be Use index search selection criteria dialogue

 

Question:- How will you implement a tree structure (one employee reports to another who is an employee of the organization itself) in design? What are the challenges in managing this type of tables?

 

Answer:- Tree structures are very useful in implementing hierarchical structures which are helpful for software developers to develop applications which are more realistic and tangible to the customers who will use them.

 

Question:- What is a Heap Table and when will you make heap tables?

 

Answer:- If a table is a heap and does not have any nonclustered indexes, then the entire table must be examined (a table scan) to find any row. This can be acceptable when the table is tiny, such as a list of the 12 regional offices of a company. When a table is stored as a heap, individual rows are identified by reference to a row identifier (RID) consisting of the file number, data page number, and slot on the page. The row id is a small and efficient structure.

 

Question:- What is collation and how will you define a collation at column level?

 

Answer:- Collations can be specified at the server, database, column, expression, and identifier levels. When you install an instance of SQL Server, you specify the default server collation for that instance. Each time that you create a database, you can specify the default collation used for the database. If you do not specify a collation, the default collation for the database is the default collation for the server instance. Whenever you define a character column, variable, or parameter, you can specify the collation of the object. If you do not specify a collation, the object is created by using the default collation of the database.

 

Question:- What is the difference between an OLTP, ODS, Data Warehouse and a Data Mart? What has been your level of experience in designing these types of systems?

 

Answer:- OLTP- handles real time transactions which inherently have some special requirements.

ODS- its final integration point ETL process where we load the data in ODS before load the values in target.

 

Question:- Illustrate physical database architecture in brief.

 

Answer:- The physical database architecture describes how the database and files are organized in a SQL server.

Pages and extents: these describe how the data is stored

Physical Database Files and File groups :- describes the operating system files used to store data and logs.

Space Allocation and Reuse :- Describes the algorithms used for space allocation.

Table and Index Architecture :- Describes the way pages for tables can be indexed

 

Question:- What is RAIDs, explain RAID 5

 

Answer:- The combining physical drives and presenting them as a single hard drive to the operating system, RAID technology allows storage pros to store the same data in different places on multiple disks. For organisations considering a RAID deployment, there are multiple factors that need to be looked at, particularly the available levels of RAID technology and the specific needs of their data storage infrastructure.

 

Question:- What are the different isolation levels in SQL

 

Answer:- : Isolation levels come into play when you need to isolate a resource for a transaction and protect that resource from other transactions. The protection is done by obtaining locks. What locks need to be set and how it has to be established for the transaction is determined by SQL Server referring to the Isolation Level that has been set. Lower Isolation Levels allow multiple users to access the resource simultaneously (concurrency) but they may introduce concurrency related problems such as dirty-reads and data inaccuracy. Higher Isolation Levels eliminate concurrency related problems and increase the data accuracy but they may introduce blocking.

 

Question:- What is share lock?

Answer:- locking that allows different types of resources to be locked by a transaction. To minimize the cost of locking, SQL Server locks resources automatically at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency, but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions, but has a lower overhead because fewer locks are being maintained.

 

 

.allblock_box_v1{border-style:solid;border-width:1px;border-color:#ff5500;}
Tags: Untagged
Hits: 804
Rate this blog entry
Aanal has not set their biography yet

Comments

Please login first in order for you to submit comments