Questions and Answers
1) What you thing about the WebPortal ?
Answer: Web portal is nothing but a page that allows a user to customize his/her homepage. We can use Widgets to create that portal we have only to drag and drop widgets on the page. The user can set his Widgets on any where on the page where he has to get them. Widgets are nothing but a page area that helps particular function to response. Widgets example are address books, contact lists, RSS feeds, clocks, calendars, play lists, stock tickers, weather reports, traffic reports, dictionaries, games and another such beautiful things that we can not imagine. We can also say Web Parts in Share Point Portal. These are one of Ajax-Powered.
2) How to start Outlook,NotePad file in AsP.NET with code ?
Answer: Here is the syntax to open outlook or notepad file in ASP.NET VB.NET Process.Start("Notepad.exe") Process.Start("msimn.exe"); C#.NET System.Diagnostics.Process.Start("msimn.exe"); System.Diagnostics.Process.Start("Notepad.exe");
3) What is the purpose of IIS ?
Answer: We can call IIS(Internet Information Services) a powerful Web server that helps us creating highly reliable, scalable and manageable infrastructure for Web application which runs on Windows Server 2003. IIS helps development center and increase Web site and application availability while lowering system administration costs. It also runs on Windows NT/2000 platforms and also for above versions. With IIS, Microsoft includes a set of programs for building and administering Web sites, a search engine, and support for writing Web-based applications that access database. IIS also called http server since it process the http request and gets http response.
4) What is main difference between GridLayout and FormLayout ?
Answer: GridLayout helps in providing absolute positioning of every control placed on the page. It is easier to develop page with absolute positioning because control can be placed any where according to our requirement. But FormLayout is little different only experience Web Developer used this one reason is it is helpful for wider range browser. If there is absolute positioning we can notice that there are number of DIV tags.But in FormLayout whole work are done through the tables.
5) How Visual SourceSafe helps Us ?
Answer: One of the powerful tool provided by Microsoft to keep up-to-date of files system its keeps records of file history once we add files to source safe it can be add to database and the changes add by different user to this files are maintained in database from that we can get the older version of files to. This also helps in sharing, merging of files.
2)
6) What is Partial Classes in Asp.Net 2.0?
partial classes mean that your class definition can be split into multiple physical files. Logically, partial classes do not make any difference to the compiler. During compile time, it simply groups all the various partial classes and treats them as a single entity.
Benefits of Partial Classes
One of the greatest benefits of partial classes is that they allow a clean separation of business logic and the user interface (in particular, the code that is generated by the Visual Studio Designer). Using partial classes, the UI code can be hidden from the developer, who usually has no need to access it anyway. Partial classes also make debugging easier, as the code is partitioned into separate files. This feature also helps members of large development teams work on their pieces of a project in separate physical files
7) What is an HTTP handler in ASP.NET? Can we use it to upload files? What is HttpModule?
The HttpHandler and HttpModule are used by ASP.NET to handle requests. Whenever the IIS Server receives a request, it looks for an ISAPI filter that is capable of handling web requests. In ASP.NET, this is done by aspnet_isapi.dll. Same kind of process happens when an ASP.NET page is triggered. It looks for HttpHandler in the web.config files for any request setting. As in machine.config default setting, the .aspx files are mapped to PageHandlerFactory, and the .asmx files are mapped to the WebServiceHandlerFactory. There are many requests processed by ASP.NET in this cycle, like BeginRequest, AuthenticateRequest, AuthorizeRequest, AcquireRequestState, ResolveRequestCache, Page Constructor, PreRequestHandlerExecute, Page.Init, Page.Load, PostRequestHandlerExecute, ReleaseRequestState, UpdateRequestCache, EndRequest, PreSendRequestHeaders, PreSendRequestContent.
Yes, the HttpHandler may be used to upload files.
HttpModules are components of .NET that implement the System.Web.IHttpModule interface. These components register for some events and are then invoked during the request processing. It implements the Init and the Dispose methods. HttpModules has events like AcquireRequestState, AuthenticateRequest, AuthorizeRequest, BeginRequest, Disposed , EndRequest, Error, PostRequestHandlerExecute, PreRequestHandlerExecute, PreSendRequestHeaders, ReleaseRequestState, ResolveRequestCache, UpdateRequestCache
8) How the find the N the maximum salary of an employee?
Ans.
SELECT MIN (SALARY )
FROM UEXAMPLE1
WHERE SALARY IN (SELECT DISTINCT TOP 4 SALARY FROM UEXAMPLE1 ORDER BY SALARY DESC)
Or this will return more then one record with same salary
select * from bank where bal=
(SELECT top 1 (bal )
FROM bank
WHERE bal IN (SELECT DISTINCT TOP 3 bal FROM bank ORDER BY bal DESC)order by bal)
9) What is @@connection?
The Query Designer supports the use of certain SQL Server constants, variables, and reserved column names in the Grid or SQL panes. Generally, you can enter these values by typing them in, but the Grid pane will not display them in drop-down lists. Examples of supported names include:
· IDENTITYCOL If you enter this name in the Grid or SQL pane, the SQL Server will recognize it as a reference to an auto-incrementing column.
· Predefined global values You can enter values such as @@CONNECTIONS and @@CURSOR_ROW into the Grid and SQL panes.
· Constants (niladic functions) You can enter constant values such as CURRENT_TIMESTAMP and CURRENT_USER in either pane.
· NULL If you enter NULL in the Grid or SQL panes, it is treated as a literal value, not a constant.
10) How to copy one total column data to another column?
Ans.
Update Set COLUMN2 = COLUMN1
Here copy column1 to column2
11) What is Global.asax?
Ans.
Global.asax is a file used to declare application-level events and objects. Global.asax is the ASP.NET extension of the ASP Global.asa file. ...
the ASP.NET page framework assumes that you have not defined any application/Session events in the application.
12) What is boxing and UnBoxing?
Ans:
Boxing
Lets now jump to Boxing. Sometimes we need to convert ValueTypes to Reference Types also known as boxing. Lets see a small example below. You see in the example I wrote "implicit boxing" which means you don't need to tell the compiler that you are boxing Int32 to object because it takes care of this itself although you can always make explicit boxing as seen below right after implicit boxing.
Int32 x = 10; object o = x ; // Implicit boxing Console.WriteLine("The Object o = {0}",o); // prints out 10 //----------------------------------------------------------- Int32 x = 10; object o = (object) x; // Explicit Boxing Console.WriteLine("The object o = {0}",o); // prints out 10
Unboxing
Lets now see UnBoxing an object type back to value type. Here is a simple code that unbox an object back to Int32 variable. First we need to box it so that we can unbox.
Int32 x = 5; object o = x; // Implicit Boxing x = o; // Implicit UnBoxing
So, you see how easy it is to box and how easy it is to unbox. The above example first boxs Int32 variable to an object type and than simply unbox it to x again. All the conversions are taking place implicitly. Everything seems right in this example there is just one small problem which is that the above code is will not compile. You cannot Implicitly convert a reference type to a value type. You must explicitly specify that you are unboxing as shown in the code below.
Int32 x = 5; object o = x; // Implicit Boxing x = (Int32)o; // Explicit UnBoxing
Lets see another small example of unboxing.
Int32 x = 5; // declaring Int32 Int64 y = 0; // declaring Int64 double object o = x; // Implicit Boxing y = (Int64)o; // Explicit boxing to double Console.WriteLine("y={0}",y);
This example will not work. It will compile successfully but at runtime It will generate an exception of System.InvalidCastException. The reason is variable x is boxed as Int32 variable so it must be unboxed to Int32 variable. So, the type the variable uses to box will remain the same when unboxing the same variable. Of course you can cast it to Int64 after unboxing it as Int32 as follows:
Int32 x = 5; // declaring Int32 Int64 y = 0; // declaring Int64 double object o = x; // Implicit Boxing y = (Int64)(Int32)o; // Unboxing and than casting to double Console.WriteLine("y={0}",y);
13) What is WPF? WCF?
Windows Presentation Foundation, it is to develop the applications which contains Rich Graphics. Example Google Earth.
Windows Communication Foundation is the framework to develop the SOA (Service Oriented Architecture) based applications. Which applications can be communicated with any applications that are developed using any other technologies?
14) what is web service?
Web services are frequently just Web APIs that can be accessed over a network, such as the Internet, and executed on a remote system hosting the requested services
The W3C Web service definition encompasses many different systems, but in common usage the term refers to clients and servers that communicate over the HTTP protocol used on the Web. Such services tend to fall into one of two camps: Big Web Services and RESTful Web Services.
"Big Web Services" use XML messages that follow the SOAP standard and have been popular with traditional enterprise. In such systems, there is often machine-readable description of the operations offered by the service written in the Web Services Description Language (WSDL). The latter is not a requirement of a SOAP endpoint, but it is a prerequisite for automated client-side code generation in many Java and .NET SOAP frameworks (frameworks such as Spring, Apache Axis2 and Apache CXF being notable exceptions). Some industry organizations, such as the WS-I, mandate both SOAP and WSDL in their definition of a Web service.
15) How to know whether the Table ‘xyz’ exists or not?
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Country_Master]') AND type in (N'U'))
16) Difference between an Interface and Abstract class?
Ans.
(1) An abstract class may contain complete or
incomplete methods. Interfaces can contain only the
signature of a method but no body. Thus an abstract class
can implement methods but an interface can not implement
methods.
(2) An abstract class can contain fields,
constructors, or destructors and implement properties. An
interface can not contain fields, constructors, or
destructors and it has only the property's signature but no
implementation.
(3) An abstract class cannot support multiple
inheritance, but an interface can support multiple
inheritance. Thus a class may inherit several interfaces
but only one abstract class.
(4) A class implementing an interface has to
implement all the methods of the interface, but the same is
not required in the case of an abstract Class.
(5) Various access modifiers such as abstract,
protected, internal, public, virtual, etc. are useful in
abstract Classes but not in interfaces.
(6) Abstract classes are faster than interfaces.
17) What is IL?
Ans.
A language that is generated from programming source code, but that cannot be directly executed by the CPU. Also called "bytecode," "p-code," "pseudo code" or "pseudo language," the intermediate language (IL) is platform independent. It can be run in any computer environment that has a runtime engine for the language.
In order to execute the intermediate language program, it must be interpreted a line at a time into machine language or compiled into machine language and run.
18) What is difference between VBScript and JavaScript?
Ans.
Vbscript will be run on IE
JavaScript runs on any browser
Vbscript is developed by ms
Java script by Sunmicrosystems
Vb not case sensitive
Java is case sensitive
19) What is ObjRef object in remoting?
ObjRef is a searializable object returned by Marshal() that knows about location of the remote object, host name, port number, and object name.
20) How can we create custom controls in ASP.NET?
Custom controls are user defined controls. They can be created by grouping existing controls, by deriving the control from System.Web.UI.WebControls.WebControl or by enhancing the functionality of any other custom control. Custom controls are complied into DLL’s and thus can be referenced by as any other web server control.
Basic steps to create a Custom control:
1. Create Control Library
2. Write the appropriate code
3. Compile the control library
4. Copy to the DLL of the control library to the project where this control needs to be used
5. The custom control can then be registered on the webpage as any user control through the @Register tag.
21) What is the Pre-Compilation feature of ASP.NET 2.0?
Previously, in ASP.NET, the pages and the code used to be compiled dynamically and then cached so as to make the requests to access the page extremely efficient. In ASP.NET 2.0, the pre-compilation feature is used with which an entire site is precompiled before it is made available to users.
There is a pre-defined folder structure for enabling the pre-compilation feature:
* App_Code: stores classes
* App_Themes: stores CSS files, Images, etc.
* App_Data: stores XML files, Text Files, etc.
It is a process where things that can be handled before compilation are prepared in order to reduce the deployment time, response time, increase safety. It’s main aim to boost performance.
It also helps in informing about the compilation failures.
During development, it allows you to make changes to the web pages and reuse it using the same web browser to validate the changes without compiling the entire website.
During deployment, it generates the entire website folder structure in the destination. All the static files are copied to the folder and bin directory would later on contain the compiled dll.
* App_GlobalResources: stores all the resources at global level E.g. resx files, etc
* App_LocalResources: stores all the resources at local/Page level
22) What is the purpose of Server.MapPath method in Asp.Net?
In Asp.Net Server.MapPath method maps the specified relative or virtual path to the corresponding physical path on the server. Server.MapPath takes a path as a parameter and returns the physical location on the hard drive. Syntax
Suppose your Text files are located at D:\project\MyProject\Files\TextFiles
If the root project directory is MyProject and the aspx file is located at root then to get the same path use code
//Physical path of TextFiles
string TextFilePath=Server.MapPath("Files/TextFiles");
C# interview questions and answers
1. What’s the advantage of using System.Text.StringBuilder over System.String? StringBuilder is more efficient in the cases, where a lot of manipulation is done to the text. Strings are immutable, so each time it’s being operated on, a new instance is created.
2. Can you store multiple data types in System.Array?
No.
3. What’s the difference between the System.Array.CopyTo() and System.Array.Clone()?
The first one performs a deep copy of the array, the second one is shallow.
4. How can you sort the elements of the array in descending order?
By calling Sort() and then Reverse() methods.
5. What’s the .NET datatype that allows the retrieval of data by a unique key?
HashTable.
6. What’s class SortedList underneath?
A sorted HashTable.
7. Will finally block get executed if the exception had not occurred?
Yes.
8. What’s the C# equivalent of C++ catch (…), which was a catch-all statement for any possible exception?
A catch block that catches the exception of type System.Exception. You can also omit the parameter data type in this case and just write catch {}.
9. Can multiple catch blocks be executed?
No, once the proper catch code fires off, the control is transferred to the finally block (if there are any), and then whatever follows the finally block.
10. Why is it a bad idea to throw your own exceptions?
Well, if at that point you know that an error has occurred, then why not write the proper code to handle that error instead of passing a new Exception object to the catch block? Throwing your own exceptions signifies some design flaws in the project.
11. What’s a delegate?
A delegate object encapsulates a reference to a method. In C++ they were referred to as function pointers.
12. What’s a multicast delegate?
It’s a delegate that points to and eventually fires off several methods.
13. How’s the DLL Hell problem solved in .NET?
Assembly versioning allows the application to specify not only the library it needs to run (which was available under Win32), but also the version of the assembly.
14. What are the ways to deploy an assembly?
An MSI installer, a CAB archive, and XCOPY command.
15. What’s a satellite assembly?
When you write a multilingual or multi-cultural application in .NET, and want to distribute the core application separately from the localized modules, the localized assemblies that modify the core application are called satellite assemblies.
16. What namespaces are necessary to create a localized application? System.Globalization, System.Resources.
17. What’s the difference between // comments, /* */ comments and /// comments?
Single-line, multi-line and XML documentation comments.
18. How do you generate documentation from the C# file commented properly with a command-line compiler?
Compile it with a /doc switch.
19. What’s the difference between c and code XML documentation tag?
Single line code example and multiple-line code example.
20. Is XML case-sensitive?
Yes, so and are different elements.
21. What debugging tools come with the .NET SDK?
CorDBG – command-line debugger, and DbgCLR – graphic debugger. Visual Studio .NET uses the DbgCLR. To use CorDbg, you must compile the original C# file using the /debug switch.
22. What does the This window show in the debugger?
It points to the object that’s pointed to by this reference. Object’s instance data is shown.
23. What does assert() do?
In debug compilation, assert takes in a Boolean condition as a parameter, and shows the error dialog if the condition is false. The program proceeds without any interruption if the condition is true.
24. What’s the difference between the Debug class and Trace class? Documentation looks the same. Use Debug class for debug builds, use Trace class for both debug and release builds.
25. Why are there five tracing levels in System.Diagnostics.TraceSwitcher? The tracing dumps can be quite verbose and for some applications that are constantly running you run the risk of overloading the machine and the hard drive there. Five levels range from None to Verbose, allowing to fine-tune the tracing activities.
26. Where is the output of TextWriterTraceListener redirected?
To the Console or a text file depending on the parameter passed to the constructor.
27. How do you debug an ASP.NET Web application?
Attach the aspnet_wp.exe process to the DbgClr debugger.
28. What are three test cases you should go through in unit testing?
Positive test cases (correct data, correct output), negative test cases (broken or missing data, proper handling), exception test cases (exceptions are thrown and caught properly).
29. Can you change the value of a variable while debugging a C# application?
Yes, if you are debugging via Visual Studio.NET, just go to Immediate window.
30. Explain the three services model (three-tier application).
Presentation (UI), business (logic and underlying code) and data (from storage or other sources).
31. What are advantages and disadvantages of Microsoft-provided data provider classes in ADO.NET?
SQLServer.NET data provider is high-speed and robust, but requires SQL Server license purchased from Microsoft. OLE-DB.NET is universal for accessing other sources, like Oracle, DB2, Microsoft Access and Informix, but it’s a .NET layer on top of OLE layer, so not the fastest thing in the world. ODBC.NET is a deprecated layer provided for backward compatibility to ODBC engines.
32. What’s the role of the DataReader class in ADO.NET connections?
It returns a read-only dataset from the data source when the command is executed.
33. What is the wildcard character in SQL?
Let’s say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would involve ‘La%’.
34. Explain ACID rule of thumb for transactions.
Transaction must be Atomic (it is one unit of work and does not dependent on previous and following transactions), Consistent (data is either committed or roll back, no “in-between” case where something has been updated and something hasn’t), Isolated (no transaction sees the intermediate results of the current transaction), Durable (the values persist if the data had been committed even if the system crashes right after).
35. What connections does Microsoft SQL Server support?
Windows Authentication (via Active Directory) and SQL Server authentication (via Microsoft SQL Server username and passwords).
36. Which one is trusted and which one is untrusted?
Windows Authentication is trusted because the username and password are checked with the Active Directory, the SQL Server authentication is untrusted, since SQL Server is the only verifier participating in the transaction.
37. Why would you use untrusted verificaion?
Web Services might use it, as well as non-Windows applications.
38. What does the parameter Initial Catalog define inside Connection String?
The database name to connect to.
39. What’s the data provider name to connect to Access database? Microsoft.Access.
40. What does Dispose method do with the connection object?
Deletes it from the memory.
41. What is a pre-requisite for connection pooling? Multiple processes must agree that they will share the same connection, where every parameter is the same, including the security settings.
1) what is ssrs in sql server 2005?
Reporting Services (SSRS) provides a full range of ready-to-use tools and services to help you create, deploy, and manage reports.
2 )what are the basic differences between clustered & non-clustered indexes?
A clustered index is a speical type of index that orders the records in the way they are stored physically on the disk. A table can have only one clustered index. The leaf nodes of clustered index contain data pages.
A non-clustered index is a specail type of index in which logical order of records don't match with physical order of records. A table can have multiple non-clustered indexes. The leaf nodes contain index rows.
3) 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.
A linked server allows remote access. Using this, we can issue distributed queries, update, commands, and transactions across different data sources.
A linked server has an OLE DB provider and data source.
4) What is SQL service broker?
A service broker allows you to exchange messages between applications using SQL server as the transport mechanism. Message is a piece of information that needs to be shared. A service broker can also reject unexpected messages in disorganized format. It also ensures the messages come only once in order. It provides enhanced security as messages are handled internally by the database.
SQL service broker provides asynchronous queuing functionality to SQL server. Once message is sent to the SQL server, the client can continue with some other task instead of waiting for any notification from the server.
5) 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.
English query allows the developer to question the database using English rather than SQL queries. The English query tool has enhanced features like support for oracle, a graphical user interface to query the database etc.
6) Explain the phases a transaction has to undergo.
The several phases a transaction has to go through are listed here. Database is in a consistent state when the transaction is about to start.
1. The transaction starts processing explicitly with the BEGIN TRANSACTION statement.
2. Record is written to the log when the transaction generates the first log record for data modification.
3. Modification starts one table at time. Here database is in inconsistent state.
4. When all of the modifications have completed successfully and the database is once again consistent, the application commits the transaction.
5. If some error it undoes (or rolls back) all of the data modifications. This process returns the database to the point of consistency it was at before the transaction started.
* Active state: This phase is divided into two states:
Initial phase: This phase is achieved when the transaction starts.
Partially Committed phase: This is achieved when the transactions final statement has been executed. Even though the final statement is finished execution, the transaction may abort due to some failure.
* Failed state: This state is reached when the normal execution fails.
Aborted state: A transaction is aborted when the system feels it needs to be failed. This state should not have any effect on the system and thus all changes done until it were aborted; are rolled back.
* Committed: After the transaction is successfully executed, it enters the committed state. In this state all changes are committed. These committed changes cannot be undone or aborting.
7) 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.
Log shipping enables high availability of database. It the process of shipping the transaction log to another server. It copies the replica of the database. Both the databases are in synch. In case of failure of primary server or database, the secondary server can be used. In this process, another server called as monitor that tracks the history and status of backup and restore operations.
8) 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
Authentication modes in SQL Server:
Windows: Allows user to authenticate based on the MS Windows account credentials.
Mixed Mode: Allows users to connect either through Windows authentication or an SQL Server authentication mode. Administrator might maintain user accounts in SQL Server.
9) Describe in brief system database.
Master Database
Master database is system database. It contains information about server’s configuration. It is a very important database and important to backup Master database. Without Master database, server can't be started.
MSDB Database
It stores information related to database backups, DTS packages, Replication, SQL Agent information, SQL Server jobs.
TEMPDB Database
It stores temporary objects like temporary tables and temporary stored procedure.
Model Database
It is a template database used in the creation of new database.
The system database contains information/metadata for all database present on an SQL Server instance. The system database stores information regarding logins, configuration settings, connected servers etc. It also holds various extended stored procedures to access external processes and applications.
Major system databases :
*
Master: Core system database to mange Sql Server instance.
*
Resource: Responsible for physically storing all system objects.
*
TempDB: This is a temporary database used to store temporary, tables, cursors, indexes, variables etc.
*
Model: This acts as a template database for all user created databases.
*
MSDB: Database to manage SQL Server agent configurations.
*
Distribution: Database primarily used for SQL Server replication.
*
ReportServer: Main database for reporting services to store metadata and other object definitions.
*
ReportServerTempDB: Acts as a temporary storage for reporting services.
10) What are the events recorded in a transaction log?
The start and end of each transaction
Every data modification
Every extent allocation or deallocation
The creation or dropping of a table or index
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.
11) What is RAID (Redundant Array of Inexpensive disks)? Explain its level.
RAID is a mechanism of storing the same data in different locations. Since the same data is stored, it is termed as redundant. The data is stored on multiple disks which improves performance. The drive’s storage is divided into units ranging from a sector (512 bytes) up to several megabytes. This is termed as disk stripping.
There are NINE types of RAID plus an additional non-redundant array (RAID-0). However, RAID levels 0, 1, and 5 are the most commonly found.
* RAID 0: This level does involve stripping but no redundancy of data. Offers the best performance at the cost of NO fault tolerance.
* RAID 1: This level is termed as data mirroring consisting of at least two drives that duplicate the storage of data. No stripping involved. Often used for multi user system for best performance and fault tolerance.
* RAID 2: It involves stripping with some disks storing error checking and correcting (ECC) information.
* RAID 5: Consists of 3 or more disks in a way that protects data against loss of any one disk.
* RAID 6: Has stripped disks with dual parity.
* RAID 10: uses both striping and mirroring.
* RAID 53: Merges the features of RAID level 0 and RAID level 3
------
RAID controller is used when one drive fails and the other is still running well. The controller will automatically rebuild the data from the other devices and restores the same to the crashed system. Hence the RAID controller technology, depending on the importance of the data, is used to restore the data automatically from the other systems.
12) What are the lock types?
SQL server supports following locks
Shared lock
Update lock
Exclusive lock
Shared lock
Shared Lock allows simultaneous access of record by multiple Select statements.
Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading.
If update process is going on then read command will have to wait until updating process finishes.
Update locks
This lock is used with the resources to be updated.
Exclusive locks
This kind of lock is used with data modification operations like update, insert or delete.
--------
Main lock types:
*
Shared: Applied to read only operations where the data is not modified. E.g.: Select statements.
*
Update: Applied to resources which can be updated. It resolves dead locks in case of multiple sessions are reading, locking or updating resources later.
*
Exclusive: Used for operations involving data modification. E.g.: Insert, Update, and Delete. This ensures that multiple updates are not made to the same data at the same time.
*
Intent: Establishes a lock hierarchy. E.g.: Intent shared Intent exclusive and Shared with intentexclusive.
*
Schema: Used when schema dependent operations are being executed. E.g.: Schema modification and Schema stability.
*
Bulk update: Used while bulk copying of data and Tablock is specified.
13) Explain the use of NOLOCK query optimizer hint.
NOLOCK table hint used using WITH clause, is similar to read uncommitted. This option allows dirty reads are allowed. Using this option, shared locks cannot be issues to other transactions. This prevents existing transactions to not read incorrect data.
------------
NOLOCK is used to improve concurrency in a system. Using NOLOCK hint, no locks are acquired when data is being read. It is used in select statement. This results in dirty read - another process could be updating the data at the exact time data is being read. This may result in users seeing the records twice.
------------
NOLOCK would be deprecated in the future releases. It has been replaced by READUNCOMMITTED.
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations.
They are ignored by the SQL query optimizer in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
They specify that dirty reads are allowed. Transactions can modify data while it is being read by a transaction. Shared locks are not issued to prevent it.
Exclusive locks set by other transactions do not block the current transaction from reading the locked data.
Allowing dirty reads provide higher concurrency but it may generate errors for a transaction and present users with data that was never committed or may have the records displayed twice.
14) What is blocking?
When one connection from an application holds a lock and a second connection requires a conflicting lock type
15) What is database replicaion?
The process of copying/moving data between databases on the same or different servers.
Snapshot replication,
Transactional replication,
Merge replication
16) Define COLLATION.
Collation is the order that SQL Server uses for sorting or comparing textual data. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary
17) List out the difference between CUBE operator and ROLLUP operator.
Difference between CUBE operator and ROLLUP operator
CUBE operator is used in the GROUP BY clause of a SELECT statement to return a result set of multidimensional (multiple columns) nature.
Example:
A table product has the following records:-
Apparel Brand Quantity
Shirt Gucci 124
Jeans Lee 223
Shirt Gucci 101
Jeans Lee 210
CUBE can be used to return a result set that contains the Quantity subtotal for all possible combinations of Apparel and Brand:
SELECT Apparel, Brand, SUM(Quantity) AS QtySum
FROM product
GROUP BY Apparel, Brand WITH CUBE
The query above will return:
Apparel Brand Quantity
Shirt Gucci 101.00
Shirt Lee 210.00
Shirt (null) 311.00
Jeans Gucci 124.00
Jeans Lee 223.00
Jeans (null) 347.00
(null) (null) 658.00
(null) Gucci 225.00
(null) Lee 433.00
ROLLUP:- Calculates multiple levels of subtotals of a group of columns.
Example:
SELECT Apparel,Brand,sum(Quantity) FROM Product GROUP BY ROLLUP (Apparel,Brand);
The query above will return a sum of all quantities of the different brands.
---------
CUBE generates a result set that represents aggregates for all combinations of values in the selected columns.
ROLLUP generates a result set that represents aggregates for a hierarchy of values in the selected columns.
---------
CUBE ROLLUP
It’s an additional switch to GROUP BY clause. It can be applied to all aggregation functions to return cross tabular result sets. It’s an extension to GROUP BY clause. It’s used to extract statistical and summarized information from result sets. It creates groupings and then applies aggregation functions on them.
Produces all possible combinations of subtotals specified in GROUP BY clause and a Grand Total. Produces only some possible subtotal combinations.
18) Define @@Error and raiseerror.
Raiseerror is used to produce an error which is user defined or used to invoke an existing error present in sys.messages. They are most commonly used in procedures when any condition fails to meet.
Example:
SELECT COUNT(*) INTO :rows FROM student
WHERE studentid = : studentid;
IF :rows <> 0 THEN
RAISE ERROR 1 MESSAGE 'Student id exists in the "Student" table.';
ENDIF;
@@error is used to hold the number of an error. When a T-SQL statement is executed, @@error value is set to 0 by the SQL server. If an error occurs, the number of that error is assigned as a value.
Example: the value of @@error can be checked for “0” value to be safe.
------
@@Error
* It is system variable that returns error code of the SQL statement.
* If no error, it returns zero.
* @@Error is reset after each SQL statement.
Raiseerror
Raiseerror command reports error to client application.
---------
SQL Server provides @@Error variable that depicts the status of the last completed statement in a given set of statements. If the statement was executed successfully the variable holds 0 value else it holds the number of the error message that occurred. Raiseerror is used to send messages to applications using the same format as a system error or warning generated by SQL Server engine. It can also return a user defined message. RAISEERROR is often used to help in troubleshooting, check values of data, returns variable value based messages, cause an execution to jump to a CATCH from TRY.
19) When do we use the UPDATE_STATISTICS command?
UPDATE_STATISTICS updates the indexes on the tables when there is large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account
20) Define Local temporary table and global temporary table.
Local temporary table is created by prefixing name with pound sign like (#table_name). Global temporary table is created by prefixing name with Double pound sign like (##table_name).
Local temporary table is dropped when the stored procedure completes. Global temporary tables are dropped when session that created the table ends
