Some good Interview Question for DBA

1. How can you tell if the query will be scalable in production?

Ans:- Profiler, Query Analyzer, Query Execution Plan.

2. Best Method to insert large data into database:-

Ans:- Insert with Select=> This works with data in same database.

If data is in different database then Bulk Copy and Bulk Insert is used.

3. Where do you write Business Logic in application or DB? Will you use Dynamic SQL or Stored Procedure?

Ans:- Dynamic SQl  is trade-off. Its expensive.

Stored Porcedure is optimized by DB therefore faster,it is secured and also provides injection.

Disadvantage of Stored Procedure:- Debugging is expensive and large.

4. Why do we need transaction?

Ans:- To insure atomicity. ACID properties….

5. Difference between stored procedure and functions

Ans:- 

• Stored procedure is optional using out variable to return whereas functions must  return. 
• Stored procedure can’t be used with select statement. Whereas functions can be.
• Mutating Table Error is the problem with functions. To overcome this Compound Trigger is used. If a function is used with select statement for a table and if function is updating that table that gives mutating table error.
•  Stored procedure can be implemented using execute statement whereas function can’t.

Framework3.5 VS Framework 4.0

NET framework 3.5 and 4.0 are two versions of the Microsoft .NET framework. Microsoft always comes up with various applications and frameworks so that the application development is more advanced and enhanced. Microsoft .NET Framework is a framework which is designed for Windows operating system It has a large library and supports various programming languages. It also supports interoperability and NET library is available to all programming languages which are supported by .NET. In year 2007, .NET 3.5 was released which had included more features that .NET 2.0 and .NET 3.0 could not stay in the industry for long time as it has various issues. However, .NET 4.0 was released in April 2010.

.NET 3.5 Framework

Microsoft .NET 3.5 Framework has various technologies which are able to help application developers solve the issues while developing applications. Some technologies were available in .NET 3.0 while some other technologies have been added in .NET 3.5. Some of the new technologies are mentioned as below:

• ASP.NET AJAX- The technology supports creation of web applications with much more advanced features. It is much easier for developers to develop AJAX applications.

• Language- Integrated Query- With the introduction of LINQ; the developers can create and maintain .NET Framework applications that can work well with the data.

• Windows Communication Foundation- In .NET 3.5 Framework, various challenges are addressed via Windows Communication Foundation (WCF) which is service-oriented approach.

.NET 4.0 Framework

.NET 4.0 Framework will work side by side along with older versions of .NET. The applications which run with older versions will continue to run with this version. In this version, there are new features that have been implemented are as follows:

• The CLR (Common Language Runtime) and Base Class Library (BCL) have been improved.

• New Numeric types and memory mapped files have also been introduced.

• Data Access and Modeling Improvements

• Enhancements in ASP.NET

• Improved Windows Presentation Foundation(WPF)

• Various dynamic features such as entity templates, new query filters and validation features.

• Task Parallel Support and Parallel Loop Support

Calculate Percentage of Null values over Non-Null values in SPARSE column


WHAT IS SPARSE COLUMN AND WHY DO WE USE IT?

• Designed to optimize storage space for columns with a large percentage of NULLs, 
• To designate a column as SPARSE, the column must allow NULLs. When a NULL is stored in a column designated as SPARSE, no storage space is consumed. However, non-NULL values require 4 bytes of storage space in addition to the normal space consumed by the data type.
• Unless you have a high enough percentage of rows containing a NULL to offset the increased storage required for non-NULL values, you should not designate a column as SPARSE.

You cannot apply the SPARSE property to:-

• Columns with the ROWGUIDCOL or IDENTITY property
• TEXT, NTEXT, IMAGE, TIMESTAMP, GEOMETRY, GEOGRAPHY, or user-defi ned data types
• A VARBINARY(MAX) with the FILESTREAM property
• A computed column of a column with a rule or default bound to it
• Columns that are part of either a clustered index or a primary key
• A column within an ALTER TABLE statement

If a column is having Nchar type which means it stores 2 bytes. If we have 100 records than total space will be 2*100=200. If column is having SPARSE then 4bytes are added i.e 2+4=6bytes. To find the percentage of Null values over Non-null values.

Lets assume x null values and since null values do not allocate any space

X*0 + (100-x)* 2+4= 200