ASP.NET interview questions and answers

1. Explain how a web application works.

A web application resides in the server and serves the client.s requests over internet. The client

access the web page using browser from his machine. When a client makes a request, it receives

the result in the form of HTML which are interpreted and displayed by the browser.

A web application on the server side runs under the management of Microsoft Internet

Information Services (IIS). IIS passes the request received from client to the application. The

application returns the requested result in the form of HTML to IIS, which in turn, sends the result

to the client.

- Inheritance hands down methods from base to special classes

- Encapsulation – packaging everything in the box together, methods, attributes (class instance variables)

- Polymorphism – many forms, but you all inherit from a class

A class in an encapsulation of methods and attributes.  Procedures, subs, functions properties.

2. Explain the advantages of ASP.NET.

Following are the advantages of ASP.NET.

Web application exists in compiled form on the server so the execution speed is faster as

compared to the interpreted scripts.

ASP.NET makes development simpler and easier to maintain with an event-driven, server-side

programming model.

Being part of .Framework, it has access to all the features of .Net Framework.

Content and program logic are separated which reduces the inconveniences of program

maintenance.

ASP.NET makes for easy deployment. There is no need to register components because the

configuration information is built-in.

To develop program logic, a developer can choose to write their code in more than 25 .Net

languages including VB.Net, C#, JScript.Net etc.

Introduction of view state helps in maintaining state of the controls automatically between the

postbacks events.

ASP.NET offers built-in security features through windows authentication or other authentication

methods.

Integrated with ADO.NET.

Built-in caching features.

3. Explain the different parts that constitute ASP.NET application.

Content, program logic and configuration file constitute an ASP.NET application.

Content files

Content files include static text, images and can include elements from database.

Program logic

Program logic files exist as DLL file on the server that responds to the user actions.

Configuration file

Configuration file offers various settings that determine how the application runs on the server.

4. Describe the sequence of action takes place on the server when ASP.NET application

starts first time?

Following are the sequences:

IIS starts ASP.NET worker process>> worker process loads assembly in the memory>>IIS sends

the request to the assembly>>the assembly composes a response using program logic>> IIS

returns the response to the user in the form of HTML.

5. Explain the components of web form in ASP.NET

Server controls.

The server controls are Hypertext Markup Language (HTML) elements that include a

runat=server attribute. They provide automatic state management and server-side events and

respond to the user events by executing event handler on the server.

HTML controls.

These controls also respond to the user events but the events processing happen on the client

machine.

Data controls

Data controls allow to connect to the database, execute command and retrieve data from

database.

System components

System components provide access to system-level events that occur on the server.

6. Describe in brief .NET Framework and its components.

.NET Framework provides platform for developing windows and web software. ASP.NET is a part

of .Net framework and can access all features implemented within it that was formerly available

only through windows API. .NET Framework sits in between our application programs and

operating system.

The .Net Framework has two main components:

.Net Framework Class Library: It provides common types such as data types and object types

that can be shared by all .Net compliant language.

The Common language Runtime: It provides services like type safety, security, code execution,

thread management, interoperability services.

7. What is an Assembly? Explain its parts?

An assembly exists as a .DLL or .EXE that contains MSIL code that is executed by CLR.

An assembly contains interface and classes, it can also contain other resources like bitmaps, files

etc. It carries version details which are used by the CLR during execution. Two assemblies of the

same name but with different versions can run side-by-side enabling applications that depend on

a specific version to use assembly of that version. An assembly is the unit on which permissions

are granted. It can be private or global. A private assembly is used only by the application to

which it belongs, but the global assembly can be used by any application in the system.

The four parts of an assembly are:

Assembly Manifest - It contains name, version, culture, and information about referenced

assemblies.

Type metadata - It contains information about types defined in the assembly.

MSIL - MSIL code.

Resources - Files such as BMP or JPG file or any other files required by application.

8. Define Common Type System.

.Net allows developers to write program logic in at least 25 languages. The classes written in one

language can be used by other languages in .Net. This service of .Net is possible through CTS

which ensure the rules related to data types that all language must follow. It provides set of types

that are used by all .NET languages and ensures .NET language type compatibility.

9. Define Virtual folder.

It is the folder that contains web applications. The folder that has been published as virtual folder

by IIS can only contain web applications.

10. Describe the Events in the Life Cycle of a Web Application

A web application starts when a browser requests a page of the application first time. The request

is received by the IIS which then starts ASP.NET worker process (aspnet_wp.exe). The worker

process then allocates a process space to the assembly and loads it. An application_start event

occurs followed by Session_start. The request is then processed by the ASP.NET engine and

sends back response in the form of HTML. The user receives the response in the form of page.

The page can be submitted to the server for further processing. The page submitting triggers

postback event that causes the browser to send the page data, also called as view state to the

server. When server receives view state, it creates new instance of the web form. The data is

then restored from the view state to the control of the web form in Page_Init event.

The data in the control is then available in the Page_load event of the web form. The cached

event is then handled and finally the event that caused the postback is processed. The web form

is then destroyed. When the user stops using the application, Session_end event occurs and

session ends. The default session time is 20 minutes. The application ends when no user

accessing the application and this triggers Application_End event. Finally all the resources of the

application are reclaimed by the Garbage collector.

11. What are the ways of preserving data on a Web Form in ASP.NET?

ASP.NET has introduced view state to preserve data between postback events. View state can.t

avail data to other web form in an application. To provide data to other forms, you need to save

data in a state variable in the application or session objects.

12. Define application state variable and session state variable.

These objects provide two levels of scope:

Application State

Data stored in the application object can be shared by all the sessions of the application.

Application object stores data in the key value pair.

Session State

Session State stores session-specific information and the information is visible within the session

only. ASP.NET creates unique sessionId for each session of the application. SessionIDs are

maintained either by an HTTP cookie or a modified URL, as set in the application’s configuration

settings. By default, SessionID values are stored in a cookie.

13. Describe the application event handlers in ASP.NET

Following are the application event handlers:

Application_Start: This event occurs when the first user visits a page of the application.

Application_End: This event occurs when there are no more users of the application.

Application_BeginRequest: This occurs at the beginning of each request to the server.

Application_EndRequest: occurs at the end of each request to the server.

Session_Start: This event occurs every time when any new user visits.

Session_End: occurs when the users stop requesting pages and their session times out.

14. What are the Web Form Events available in ASP.NET?

Page_Init

Page_Load

Page_PreRender

Page_Unload

Page_Disposed

Page_Error

Page_AbortTransaction

Page_CommitTransaction

Page_DataBinding

15. Describe the Server Control Events of ASP.NET.

ASP.NET offers many server controls like button, textbox, DropDownList etc. Each control can

respond to the user.s actions using events and event handler mechanism.

There are three types of server control events:

Postback events

This events sends the web page to the server for processing. Web page sends data back to the

same page on the server.

Cached events

These events are processed when a postback event occurs.

Validation events

These events occur just before a page is posted back to the server.

16. How do you change the session time-out value?

The session time-out value is specified in the web.config file within sessionstate element.

You can change the session time-out setting by changing value of timeout attribute of

sessionstate element in web.config file.

17. Describe how ASP.NET maintains process isolation for each Web application?

In ASP.NET, when IIS receives a request, IIS uses aspnet_isapi.dll to call the ASP.NET worker

process (aspnet_wp.exe). The ASP.NET worker process loads the Web application.s assembly,

allocating one process space, called the application domain, for each application. This is the how

ASP.NET maintains process isolation for each Web application.

18. Define namespace.

Namespaces are the way to organize programming code. It removes the chances of name

conflict. It is quite possible to have one name for an item accidentally in large projects those

results into conflict. By organizing your code into namespaces, you reduce the chance of these

conflicts. You can create namespaces by enclosing a class in a Namespace.End Namespace

block.

You can use namespaces outside your project by referring them using References dialog box.

You can use Imports or using statement to the code file to access members of the namespaces in

code.

19. What are the options in ASP.NET to maintain state?

Client-side state management

This maintains information on the client’s machine using Cookies, View State, and Query Strings.

Cookies.

A cookie is a small text file on the client machine either in the client’s file system or memory of

client browser session. Cookies are not good for sensitive data. Moreover, Cookies can be

disabled on the browser. Thus, you can’t rely on cookies for state management.

View State

Each page and each control on the page has View State property. This property allows automatic

retention of page and controls state between each trip to server. This means control value is

maintained between page postbacks. Viewstate is implemented using _VIEWSTATE, a hidden

form field which gets created automatically on each page. You can’t transmit data to other page

using view state.

Querystring

Query strings can maintain limited state information. Data can be passed from one page to

another with the URL but you can send limited size of data with the URL. Most browsers allow a

limit of 255 characters on URL length.

Server-side state management

This kind of mechanism retains state in the server.

Application State

The data stored in the application object can be shared by all the sessions of the application.

Application object stores data in the key value pair.

Session State

Session State stores session-specific information and the information is visible within the session

only. ASP.NET creates unique sessionId for each session of the application. SessionIDs are

maintained either by an HTTP cookie or a modified URL, as set in the application’s configuration

settings. By default, SessionID values are stored in a cookie.

Database

Database can be used to store large state information. Database support is used in combination

with cookies or session state.

20. Explain the difference between Server control and HTML control.

Server events

Server control events are handled in the server whereas HTML control events are handled in the

page.

State management

Server controls can maintain data across requests using view state whereas HTML controls have

no such mechanism to store data between requests.

Browser detection

Server controls can detect browser automatically and adapt display of control accordingly

whereas HTML controls can’t detect browser automatically.

Properties

Server controls contain properties whereas HTML controls have attributes only.

21. What are the validation controls available in ASP.NET?

ASP.NET validation controls are:

RequiredFieldValidator: This validates controls if controls contain data.

CompareValidator: This allows checking if data of one control match with other control.

RangeValidator: This verifies if entered data is between two values.

RegularExpressionValidator: This checks if entered data matches a specific format.

CustomValidator: Validate the data entered using a client-side script or a server-side code.

ValidationSummary: This allows developer to display errors in one place.

22. Define the steps to set up validation control.

Following are the steps to set up validation control

Drag a validation control on a web form.

Set the ControlToValidate property to the control to be validated.

If you are using CompareValidator, you have to specify the ControlToCompare property.

Specify the error message you want to display using ErrorMessage property.

You can use ValidationSummary control to show errors at one place.

23. What are the navigation ways between pages available in ASP.NET?

Ways to navigate between pages are:

Hyperlink control

Response.Redirect method

Server.Transfer method

Server.Execute method

Window.Open script method

24. How do you open a page in a new window?

To open a page in a new window, you have to use client script using onclick="window.open()"

attribute of HTML control.

25. Define authentication and authorization.

Authorization: The process of granting access privileges to resources or tasks within an

application.

Authentication: The process of validating the identity of a user.

26. Define caching.

Caching is the technique of storing frequently used items in memory so that they can be

accessed more quickly. Caching technique allows to store/cache page output or application data

on the client on the server. The cached information is used to serve subsequent requests that

avoid the overhead of recreating the same information. This enhances performance when same

information is requested many times by the user.

27. Define cookie.

A cookie is a small file on the client computer that a web application uses to maintain current

session information. Cookies are used to identity a user in a future session.

28. What is delegate?

A delegate acts like a strongly type function pointer. Delegates can invoke the methods that they

reference without making explicit calls to those methods. It is type safe since it holds reference of

only those methods that match its signature. Unlike other classes, the delegate class has a

signature. Delegates are used to implement event programming model in .NET application.

Delegates enable the methods that listen for an event, to be abstract.

29. Explain Exception handling in .Net.

Exceptions or errors are unusual occurrences that happen within the logic of an application. The

CLR has provided structured way to deal with exceptions using Try/Catch block. ASP.NET

supports some facilities to handling exceptions using events suck as Page_Error and

Application_Error.

30. What is impersonation?

Impersonation means delegating one user identity to another user. In ASP.NET, the anonymous

users impersonate the ASPNET user account by default. You can use <identity> element of

web.config file to impersonate user. E.g. <identity impersonate="true"/>

31. What is managed code in .Net?

The code that runs under the guidance of common language runtime (CLR) is called managed

code. The versioning and registration problem which are formally handled by the windows

programming are solved in .Net with the introduction of managed code. The managed code

contains all the versioning and type information that the CLR use to run the application.

32. What are Merge modules?

Merge modules are the deployment projects for the shared components. If the components are

already installed, the modules merge the changes rather than unnecessarily overwrite them.

When the components are no longer in use, they are removed safely from the server using Merge

modules facility.

33. What is Satellite assembly?

Satellite assembly is a kind of assembly that includes localized resources for an application. Each

satellite assembly contains the resources for one culture.

34. Define secured sockets layer.

Secured Socket Layer (SSL) ensures a secured web application by encrypting the data sent over

internet. When an application is using SSL facility, the server generates an encryption key for the

session and page is encrypted before it sent. The client browse uses this encryption key to

decrypt the requested Web page.

35. Define session in ASP.NET.

A session starts when the browser first request a resources from within the application. The

session gets terminated when either browser closed down or session time out has been attained.

The default time out for the session is 20 minutes.

36. Define Tracing.

Tracing is the way to maintain events in an application. It is useful while the application is in

debugging or in the testing phase. The trace class in the code is used to diagnose problem. You

can use trace messages to your project to monitor events in the released version of the

application. The trace class is found in the System.Diagnostics namespace. ASP.NET introduces

tracing that enables you to write debug statements in your code, which still remain in the code

even after when it is deployed to production servers.

37. Define View State.

ASP.NET preserves data between postback events using view state. You can save a lot of

coding using view state in the web form. ViewState serialize the state of objects and store in a

hidden field on the page. It retains the state of server-side objects between postbacks.

It represents the status of the page when submitted to the server. By default, view state is

maintained for each page. If you do not want to maintain the ViewState, include the directive

<%@ Page EnableViewState="false" %> at the top of an .aspx page or add the attribute

EnableViewState="false" to any control. ViewState exist for the life of the current page.

38. What is application domain?

It is the process space within which ASP.NET application runs. Every application has its own

process space which isolates it from other application. If one of the application domains throws

error it does not affect the other application domains.

39. List down the sequence of methods called during the page load.

Init() . Initializes the page.

Load() . Loads the page in the server memory.

PreRender() - the brief moment before the page is displayed to the user as HTML

Unload() . runs just after page finishes loading.

40. What is the importance of Global.asax in ASP.NET?

The Global.asax is used to implement application and session level events.

41. Define MSIL.

MSIL is the Microsoft Intermediate Language. All .Net languages. executable exists as MSIL

which gets converted into machine specific language using JIT compiler just before execution.

42. Response.Redirect vs Server.Transfer.

Server.Transfer is only applicable for aspx files. It transfers page processing to another page

without making round-trip back to the client.s browser. Since no round trips, it offers faster

response and doesn.t update client url history list.

Response.Redirect is used to redirect to another page or site. This performs a trip back to the

client where the client’s browser is redirected to the new page.

43. Explain Session state management options in ASP.NET.

ASP.NET provides In-Process and Out-of-Process state management. In-Process stores the

session in memory on the web server. Out-of-Process Session state management stores data in

an external data source such as SQL Server or a State Server service. Out-of-Process state

management requires that all objects stored in session are serializable.

44. How to turn off cookies for a page?

Cookie.Discard Property when true, instructs the client application not to save the Cookie on the

user.s hard disk when a session ends.

45. How can you ensure a permanent cookie?

Setting Expires property to MinValue and restrict cookie to get expired.

46. What is AutoPostback?

AutoPostBack automatically posts the page back to the server when state of the control is

changed.

47. Explain login control and form authentication.

Login controls encapsulate all the features offered by Forms authentication. Login controls

internally use FormsAuthentication class to implement security by prompting for user credentials

validating them.

48. What is the use of Web.config file?

Following are the setting you can incorporate in web.config file.

Database connections

Error Page setting

Session States

Error Handling

Security

Trace setting

Culture specific setting

49. Explain in what order a destructors is called.

Destructors are called in reverse order of constructors. Destructor of most derived class is called

followed by its parent.s destructor and so on till the topmost class in the hierarchy.

50. What is break mode? What are the options to step through code?

Answer - Break mode lets you to observe code line to line in order to locate error.

VS.NET provides following option to step through code.

Step Into

Step Over

Step Out

Run To Cursor

Set Next Statement

51. Explain how to retrieve property settings from XML .config file.

Create an instance of AppSettingsReader class, use GetValue method by passing the name of

the property and the type expected. Assign the result to the appropriate variable.

52. Explain Global Assembly Cache.

Global Assembly Cache is the place holder for shared assembly. If an assembly is installed to the

Global Assembly Cache, the assembly can be accessed by multiple applications. In order to

install an assembly to the GAC, the assembly must have to be signed with strong name.

53. Explain Managed code an Un-managed code.

Managed code runs under the safe supervision of common language runtime. Managed code

carries metadata that is used by common language runtime to offer service like memory

management, code access security, and cross-language accessibility.

Unmanaged code doesn.t follow CLR conventions and thus, can.t take the advantages of

.Framework.

54. What is side-by-side execution?

This means multiple version of same assembly to run on the same computer. This feature

enables to deploy multiple versions of the component.

55. Define Resource Files.

Resource files contains non-executable data like strings, images etc that are used by an

application and deployed along with it. You can changes these data without recompiling the

whole application.

56. Define Globalization and Localization.

Globalization is the process of creating multilingual application by defining culture specific

features like currency, date and time format, calendar and other issues.

Localization is the process of accommodating cultural differences in an application.

57. What is reflection?

Reflection is a mechanism through which types defined in the metadata of each module can be

accessed. The System.Reflection namespaces contains classes that can be used to define the

types for an assembly.

58. Define Satellite Assemblies.

Satellite Assemblies are the special kinds of assemblies that exist as DLL and contain culturespecific

resources in a binary format. They store compiled localized application resources. They

can be created using the AL utility and can be deployed even after deployment of the application.

Satellite Assemblies encapsulate resources into binary format and thus makes resources lighter

and consume lesser space on the disk.

59. What is CAS?

CAS is very important part of .Net security system which verifies if particular piece of code is

allowed to run. It also determines if piece of code have access rights to run particular resource.

.NET security system applies these features using code groups and permissions. Each assembly

of an application is the part of code group with associated permissions.

60. Explain Automatic Memory Management in .NET.

Automatic memory management in .Net is through garbage collector which is incredibly efficient

in releasing resources when no longer in use.

SQL Server interview questions

Explain the use of keyword WITH ENCRYPTION. Create a Store Procedure with Encryption.

It is a way to convert the original text of the stored procedure into encrypted form. The stored procedure gets obfuscated and the output of this is not visible to

CREATE PROCEDURE Abc

WITH ENCRYPTION

AS

<<    SELECT statement>>

GO

What is a linked server in SQL Server?

It enables SQL server to address diverse data sources like OLE DB similarly. It allows Remote server access and has the ability to issue distributed queries, updates, commands and transactions.

Features and concepts of Analysis Services

Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of data and provides access to heaps of information including aggregation of data One can create data mining models from data sources and use it for Business Intelligence also including reporting features.

Some of the key features are:

·         Ease of use with a lot of wizards and designers.

·         Flexible data model creation and management

·         Scalable architecture to handle OLAP

·         Provides integration of administration tools, data sources, security, caching, and reporting etc.

·         Provides extensive support for custom applications

What is Analysis service repository?

Every Analysis server has a repository to store metadata for the objects like cubes, data sources etc. It’s by default stored in a MS Access database which can be also migrated to a SQL Server database.

What is SQL service broker?

Service Broker allows internal and external processes to send and receive guaranteed, asynchronous messaging. Messages can also be sent to remote servers hosting databases as well. The concept of queues is used by the broker to put a message in a queue and continue with other applications asynchronously. This enables client applications to process messages at their leisure without blocking the broker. Service Broker uses the concepts of message ordering, coordination, multithreading and receiver management to solve some major message queuing problems. It allows for loosely coupled services, for database applications.

What is user defined datatypes and when you should go for them?

User defined data types are based on system data types. They should be used when multiple tables need to store the same type of data in a column and you need to ensure that all these columns are exactly the same including length, and nullability.

Parameters for user defined datatype:

Name

System data type on which user defined data type is based upon.

Nullability.

For example, a user-defined data type called post_code could be created based on char system data type.

What is bit datatype?

A bit datatype is an integer data type which can store either a 0 or 1 or null value.

Describe the XML support SQL server extends.

SQL Server (server-side) supports 3 major elements:

a.       Creation of XML fragments: This is done from the relational data using FOR XML to the select query. 

b.      Ability to shred xml data to be stored in the database.

c.       Finally, storing the xml data.

Client-side XML support in SQL Server is in the form of SQLXML. It can be described in terms of

• XML Views:  providing bidirectional mapping between XML schemas and relational tables.
• Creation of XML Templates:  allows creation of dynamic sections in XML.

What is SQL Server English Query?

English query allows accessing the relational databases through English Query applications. Such applications permit the users to ask the database to fetch data based on simple English instead of using SQL statements.

What is the purpose of SQL Profiler in SQL server?

SQL profiler is a tool to monitor performance of various stored procedures. It is used to debug the queries and procedures. Based on performance, it identifies the slow executing queries. Capture any problems by capturing the events on  production environment so that they can be solved.

What is XPath?

XPath is an expressions to select a xml node in an XML document.

It allows the navigation on the XML document to the straight to the element where we need to reach and access the attributes.

What are the Authentication Modes in SQL Server?

a.             Windows Authentication Mode  (Windows Authentication): uses user’s Windows account

b.             Mixed Mode (Windows Authentication and SQL Server Authentication): uses either windows or SQL server

Explain Data Definition Language, Data Control Language and Data Manipulation Language.

Data Definition Language (DDL):- are the SQL statements that define the database structure.

Example:

a.       CREATE

b.      ALTER

c.       DROP

d.      TRUNCATE

e.      COMMENT

f.        RENAME

Data Manipulation Language (DML):- statements are used for manipulate or edit data.

Example:

a.       SELECT - retrieve data from the a database

b.      INSERT - insert data into a table

c.       UPDATE - updates existing data within a table

d.      DELETE

e.      MERGE

f.        CALL

g.       EXPLAIN PLAN

h.      LOCK TABLE

Data Control Language (DCL):-statements to take care of the security and authorization.

Examples:

1. GRANT
2. REVOKE

What are the steps to process a single SELECT statement?

Steps

a.             The select statement is broken into logical units

b.             A sequence tree is built based on the keywords and expressions in the form of the logical units.

c.              Query optimizer checks for various permutations and combinations to figure out the fastest way using minimum resources to access the source tables. The best found way is called as an execution plan.

d.             Relational engine executes the plan and processes the data

Explain GO Command.

Go command is a signal to execute the entire batch of SQL statements after previous Go.

What is the significance of NULL value and why should we avoid permitting null values?

NULL value means that no entry has been made into the column. It states that the corresponding value is either unknown or undefined. It is different from zero or "". They should be avoided to avoid the complexity in select & update queries and also because columns which have constraints like primary or foreign key constraints cannot contain a NULL value.

What is the difference between UNION and UNION ALL?

UNION selects only distinct values whereas UNION ALL selects all values and not just distinct ones.

UNION: SELECT column_names FROM table_name1
UNION
SELECT column_names FROM table_name2

UNION All: SELECT column_names FROM table_name1
UNION ALL
SELECT column_names FROM table_name2

What is use of DBCC Commands?

DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency. They are grouped as:

Maintenance: Maintenance tasks on Db, filegroup, index etc. Commands include DBCC CLEANTABLE, DBCC INDEXDEFRAG, DBCC DBREINDEX, DBCC SHRINKDATABASE, DBCC DROPCLEANBUFFERS, DBCC SHRINKFILE, DBCC FREEPROCCACHE, and DBCC UPDATEUSAGE.

Miscellaneous: Tasks such as enabling tracing, removing dll from memory. Commands include DBCC dllname, DBCC HELP, DBCC FREESESSIONCACHE, DBCC TRACEOFF, DBCC FREESYSTEMCACHE, and DBCC TRACEON.

Informational: Tasks which gather and display various types of information. Commands include DBCC INPUTBUFFER, DBCC SHOWCONTIG, DBCC OPENTRAN, DBCC SQLPERF, DBCC OUTPUTBUFFER, DBCC TRACESTATUS, DBCC PROCCACHE, DBCC USEROPTIONS, and DBCC SHOW_STATISTICS.

Validation: Operations for validating on Db, index, table etc. Commands include DBCC CHECKALLOC, DBCC CHECKFILEGROUP, DBCC CHECKCATALOG, DBCC CHECKIDENT, DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKDB.           

What is Log Shipping?

Log shipping defines the process for automatically taking backup of the database and transaction files on a SQL Server and then restoring them on a standby/backup server. This keeps the two SQL Server instances in sync with each other. In case production server fails, users simply need to be pointed to the standby/backup server. Log shipping primarily consists of 3 operations:

Backup transaction logs of the Production server.

Copy these logs on the standby/backup server.

Restore the log on standby/backup server.

What is the difference between a Local and a Global temporary table?

Temporary tables are used to allow short term use of data in SQL Server. They are of 2 types:

What is the STUFF and how does it differ from the REPLACE function?

Both STUFF and REPLACE are used to replace characters in a string.

select replace('abcdef','ab','xx') results in xxcdef

select replace('defdefdef','def','abc') results in abcabcabc

We cannot replace a specific occurrence of “def” using REPLACE.

select stuff('defdefdef',4, 3,'abc') results in defabcdef

where 4 is the character to begin replace from and 3 is the number of characters to replace.

What are the rules to use the ROWGUIDCOL property to define a globally unique identifier column?

Only one column can exist per table that is attached with ROWGUIDCOL property. One can then use $ROWGUID instead of column name in select list.

What is the actions prevented once referential integrity is enforced?

Actions prevented are:

·         Breaking of relationships is prevented once referential integrity on a database is enforced.

·         Can’t delete a row from primary table if there are related rows in secondary table.

·         Can’t update primary table’s primary key if row being modified has related rows in secondary table.

·         Can’t insert a new row in secondary table if there are not related rows in primary table.

·         Can’t update secondary table’s foreign key if there is no related row in primary table.

What are the commands available for Summarizing Data in SQL Server?

Commands for summarizing data in SQL Server:

List out the difference between CUBE operator and ROLLUP operator

Difference between CUBE and ROLLUP:

What are the guidelines to use bulk copy utility of SQL Server?

Bulk copy is an API that allows interacting with SQL Server to export/import data in one of the two data formats. Bulk copy needs sufficient system credentials.

·         Need INSERT permissions on destination table while importing.

·         Need SELECT permissions on source table while exporting.

·         Need SELECT permissions on sysindexes, sysobjects and syscolumns tables.

bcp.exe northwind..cust out "c:\cust.txt" –c -T

Export all rows in Northwind.Cust table to an ASCII-character formatted text file.

What are the capabilities of Cursors?

Capabilities of cursors:

·         Cursor reads every row one by one.

·         Cursors can be used to update a set of rows or a single specific row in a resultset

·         Cursors can be positioned to specific rows.

·         Cursors can be parameterized and hence are flexible.

·         Cursors lock row(s) while updating them.

What are the ways to controlling Cursor Behavior?

There are 2 ways to control Cursor behavior:

·         Cursor Types: Data access behavior depends on the type of cursor; forward only, static, keyset-drive and dynamic.

·         Cursor behaviors: Keywords such as SCROLL and INSENSITIVE along with the Cursor declaration define scrollability and sensitivity of the cursor.

What are the advantages of using Stored Procedures?

Advantages of using stored procedures are:

·         They are easier to maintain and troubleshoot as they are modular.

·         Stored procedures enable better tuning for performance.

·         Using stored procedures is much easier from a GUI end than building/using complex queries.

·         They can be part of a separate layer which allows separating the concerns. Hence Database layer can be handled by separate developers proficient in database queries.

·         Help in reducing network usage.

·         Provides more scalability to an application.

·         Reusable and hence reduce code.

What are the ways to code efficient transactions?

Some ways and guidelines to code efficient transactions:

·         Do not ask for an input from a user during a transaction.

·         Get all input needed for a transaction before starting the transaction.

·         Transaction should be atomic

·         Transactions should be as short and small as possible.

·         Rollback a transaction if a user intervenes and re-starts the transaction.

·         Transaction should involve a small amount of data as it needs to lock the number of rows involved.

·         Avoid transactions while browsing through data.

What are the differences among batches, stored procedures, and triggers?

What security features are available for stored procedures?

Security features for stored procedures:

·         Grants users permissions to execute a stored procedure irrespective of the related tables.

·         Grant users users permission to work with a stored procedure to access a restricted set of data yet no give them permissions to update or select underlying data.

·         Stored procedures can be granted execute permissions rather than setting permissions on data itself.

·         Provide more granular security control through stored procedures rather than complete control on underlying data in tables.

What are the instances when triggers are appropriate?

Scenarios for using triggers:

·         To create a audit log of database activity.

·         To apply business rules.

·         To apply some calculation on data from tables which is not stored in them.

·         To enforce referential integrity.

·         Alter data in a third party application

·         To execute SQL statements as a result of an event/condition automatically.

What are the restrictions applicable while creating views?

Restrictions applicable while creating views:

·         A view cannot be indexed.

·         A view cannot be Altered or renamed. Its columns cannot be renamed.

·         To alter a view, it must be dropped and re-created.

·         ANSI_NULLS and QUOTED_IDENTIFIER options should be turned on to create a view.

·         All tables referenced in a view must be part of the same database.

·         Any user defined functions referenced in a view must be created with SCHEMABINDING option.

·         Cannot use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, COMPUTE BY in views.

What are the events recorded in a transaction log?

Events recorded in a transaction log:

·         Broker event category includes events produced by Service Broker.

·         Cursors event category includes cursor operations events.

·         CLR event category includes events fired by .Net CLR objects.

·         Database event category includes events of data.log files shrinking or growing on their own.

·         Errors and Warning         event category includes SQL Server warnings and errors.

·         Full text event category include events occurred when text searches are started, interrupted, or stopped.

·         Locks event category includes events caused when a lock is acquired, released, or cancelled.

·         Object event category includes events of database objects being created, updated or deleted.

·          OLEDB event category includes events caused by OLEDB calls.

·         Performance event category includes events caused by DML operators.

·         Progress report event category includes Online index operation events.

·         Scans event category includes events notifying table/index scanning.

·         Security audit event category includes audit server activities.

·         Server event category includes server events.

·         Sessions event category includes connecting and disconnecting events of clients to SQL Server.

·         Stored procedures event category includes events of execution of Stored procedures.

·         Transactions event category includes events related to transactions.

·         TSQL event category includes events generated while executing TSQL statements.

·         User configurable event category includes user defined events.

Describe when checkpoints are created in a transaction log.

Activities causing checkpoints are:

·         When a checkpoint is explicitly executed.

·         A logged operation is performed on the database.

·         Database files have been altered using Alter Database command.

·         SQL Server has been stopped explicitly or on its own.

·         SQL Server periodically generates checkpoints.

·         Backup of a database is taken.

Define Truncate and Delete commands.

For more questions with answers, follow the link below:

http://www.careerride.com/SQLServer-Interview-Questions.aspx

We do not warrant the correctness of content. The risk from using it lies entirely with the user. While using this document, you agree to have read and accepted the terms of use and privacy policy.