.NET Database Interview Questions - Part 3

[VelMurugan]

.NET Database Interview Questions

What is de-normalization? When do you do it and how?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It's used To introduce redundancy into a table in order to incorporate data from a related table. The related table can then be eliminated. De-normalization can improve efficiency and performance by reducing complexity in a data warehouse schema.

Explain features of SQL Server like  Scalability , Availability, Integration with Internet.

Scalability - The same Microsoft SQL Server 2000 database engine operates on Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, Windows 98, and Windows Millennium Edition. It also runs on all editions of Microsoft Windows NT version 4.0. The database engine is a robust server that can manage terabyte-sized databases accessed by thousands of users. Availability - SQL Server 2000 can maintain the extremely high levels of availability required by large Web sites and enterprise systems. Integration -The SQL Server 2000 TCP/IP Sockets communications support can be integrated with Microsoft Proxy Server to implement secure Internet and intranet communications.

What is DataWarehousing?

A data warehouse is a collection of data gathered and organized so that it can easily by analyzed, extracted, synthesized, and otherwise be used for the purposes of further understanding the data.

What is OLAP?

OLAP is an acronym for On Line Analytical Processing. It is an approach to quickly provide the answer to analytical queries that are dimensional in nature.

How do we upgrade SQL Server 7.0 to 2000?

Run the installation of the SQL Server 2000
In the Existing Installation dialog box, click Upgrade your existing installation, and then click Next.
In the Upgrade dialog box, you are prompted as to whether you want to proceed with the requested upgrade. Click Yes, upgrade my to start the upgrade process, and then click Next. The upgrade runs until finished.
In the Connect to Server dialog box, select an authentication mode, and then click Next.
If you are not sure which mode to use, accept the default: The Windows account information I use to log on to my computer with (Windows). In Start Copying Files dialog box, click Next.
Now your Sql Server would be upgraded.

What is job?

It can be defined as a task performed by a computer system. For example, printing a file is a job. Jobs can be performed by a single program or by a collection of programs.

What is Task?

Whenever you execute a program, the operating system creates a new task for it. The task is like an envelope for the program: it identifies the program with a task number and attaches other bookkeeping information to it.

How do you find the error, how can you know the number of rows affected by last SQL Statement?

Answer1
@@errors->give the last error occurred in the current DB.
Ans. select @@rowcount

Answer2.
Use @@ERROR which returns the error number for the last Transact-SQL statement executed fro knowing the error.
Use @@ROWCOUNT which returns the number of rows affected by the last statement for finding the no of rows affected.

What are the advantages/disadvantages of viewstate?

Disadvantages - Because the view state for a given page must be kept on the server, it is possible for the current state to be out of synchronization with the current page of the browser, if the user uses the Back feature on the browser to go back in the history. Advantages - On ordinary Web Forms pages, their view state is sent by the server as a hidden variable in a form, as part of every response to the client, and is returned to the server by the client as part of a postback. However, to reduce bandwidth demand when using mobile controls, ASP.NET does not send a page's view state to the client. Instead, the view state is saved as part of a user's session on the server. Where there is a view state, a hidden field that identifies this page's view state is sent by the server as part of every response to the client, and is returned to the server by the client as part of the next request.

Describe session handling in webform. How does it work and what are the limits?

Session management in ASP.NET can be done in two ways:
Using Cookies
Encoding of URLs with Session ID

Explain differences between framework 1.0 and framework 1.1?

1. Native Support for Developing Mobile Web Applications
2. Unified Programming Model for Smart Client Application Development
3. Enable Code Access Security for ASP.NET Applications
4. Native Support for Communicating with ODBC and Oracle Databases
5. Supports for IPv6

If we write any code for dataGrid methods, what is the access specifier used for that methods in the code behind file and why and how? Give an example.

We use Friends Modifer for the dataGrid methods. Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid

What is the use of trace utility?

Tracing is a very important monitoring and debugging tool for distributed, multitier applications. Such applications often contain problems that can only be observed when the application is under a heavy load and the inherent randomness of a real-life environment. Trace utility allows developers and administrators to monitor the health of applications running in real-life settings.

What are the differences between User control and Web control and Custom control?

Answer1 : Usercontrol-> control that is created as u wish.
Web Control-> any control placed in web page (web application page)
Custom Control-> same as user control with some difference.

user control custome control
1.easy to create difficult
2.no full suport for customers using
Visual studio tools Full support
3. Seperate copy of the control in each
assembly only one copy in global assembly.
4. best for static layout best for dynamic layout.


Answer2
User control
1) Reusability web page
2) We can't add to toolbox
3) Just drag and drop from solution explorer to page (aspx)
4) Good for static layout
5) Easier to create
6) Not complied into DLL

Custom controls
1) Reusability of control (or extend functionalities of existing control)
2) We can add toolbox
3) Just drag and drop from toolbox
4) You can register user control to. Aspx page by Register tag
5) A single copy of the control is required in each application
6) Good for dynamic layout
7) Hard to create
8 ) Compiled in to dll

Custom controls
1) Reusability of control
2) Pre defined Control
3) Just drag and drop from toolbox

If I have more than one version of one assemblies, then how will I use old version in my application? Give an example.

Change the assembly version number in the AssemblyInfo.vb file

How does you handle this COM components developed in other programming languages in .NET?

Answer1 : add the component in add reference window, click .NETCOM tab.

Answer1 : While adding the refferences we can handle the COM components in other .Net programming languages.

How will you register COM+ services?

Through X-Copy Deployment.

How do u call and execute a stored procedure in .NET?

system.Data;
system.Data.SqlClient;

SqlConnection sqCon = new SqlConnection("connection string");
SqlCommand sqCmd = new SqCmd();
sqCmd.Connection = sqCon;
sqCmd.CommandText = procedure_name;
sqCmd.CommandType = CommandType.StoredProcedure;
sqComd.ExecuteReader();

What are the different types of replication? How are they used?

Replication is used for distributing data and the execution of stored procedures across an enterprise. The replication technology allows you to make duplicate copies of your data, move those copies to different locations, and synchronize the data automatically so that all copies have the same data values.
The different types of replications are
a) transactional replication
b) merge replication

How do SQL Server 2000 and XML linked? What is SQL Server agent?

Every Request or the Response to or from SQL Server is converted into XML format. Its purpose is to ease the implementation of tasks for the DBA, with its full-function scheduling engine, which allows you to schedule your own jobs and scripts.

How do you create thread in .NET?

1) Import System.Threading
2) Create a new thread using new Thread() and assign the address of the method
3) Use Thread.Start method to start the execution

using System;
using System.Threading;

public class Test
{
static void Main()
{
ThreadStart job = new ThreadStart(ThreadJob);
Thread thread = new Thread(job);
thread.Start();