Basic/fresher level
1. )What is the basic difference between ASP and ASP.NET?
Ans-The basic difference between ASP and ASP.NET is that ASP is interpreted; whereas, ASP.NET is compiled. This implies that since ASP uses VBScript; therefore, when an ASP page is executed, it is interpreted. On the other hand, ASP.NET uses .NET languages, such as C# and VB.NET, which are compiled to Microsoft Intermediate Language (MSIL).
2.) In which event are the controls fully loaded?
Ans-Page load event guarantees that all controls are fully loaded. Controls are also accessed in Page_Init events but you will see that view state is not fully loaded during this event.
3. )What is the lifespan for items stored in ViewState?
Ans-The items stored in ViewState live until the lifetime of the current page expires including the postbacks to the same page.
4.) What is the use of the
tag in the web.config file?
Ans-The
tag is used to configure the session state features. To change the default timeout, which is 20 minutes, you have to add the following code snippet to the web.config file of an application:
5.) What is the difference between ASP session and ASP.NET session?
Ans-ASP does not support cookie-less sessions; whereas, ASP.NET does. In addition, the ASP.NET session can span across multiple servers.
6.) Explain the validation controls. How many validation controls in ASP.NET 4.0?
Ans-Validation controls are responsible to validate the data of an input control. Whenever you provide any input to an application, it performs the validation and displays an error message to user, in case the validation fails.
ASP.NET 4.0 contains the following six types of validation controls:
CompareValidator – Performs a comparison between the values contained in two controls.
CustomValidator – Writes your own method to perform extra validation.
RangeValidator- Checks value according to the range of value.
RegularExpressionValidator – Ensures that input is according to the specified pattern or not.
RequiredFieldValidator – Checks either a control is empty or not.
ValidationSummary – Displays a summary of all validation error in a central location.
7.) What is the difference between an HtmlInputCheckBox control and an HtmlInputRadioButton control?
Ans-You can select more than one HtmlInputCheckBox control from a group of HtmlInputCheckBox controls; whereas, you can select only a single HtmllnputRadioButton control from a group of HtmlInputRadioButton controls.
8.) What are the HTML server controls in ASP.NET?
Ans-HTML server controls are similar to the standard HTML elements, which are normally used in HTML pages. They expose properties and events that can be used programmatically. To make these controls programmatically accessible, you need to specify that the HTML controls act as a server control by adding the runat=”server” attribute.
9.)What is State Management? How many ways are there to maintain a state in .NET?
Ans-State management is used to store information requests. The state management is used to trace the information or data that affect the state of the applications.
There are two ways to maintain a state in .NET, Client-Based state management and Server-Based state management.
The following techniques can be used to implement the Client-Based state management:
View State
Hidden Fields
Cookies
Query Strings
Control State
The following techniques can be used to implement Server-Based state management:
Application State
Session State
Profile Properties
10.)What is a Cookie? Where is it used in ASP.NET?
Ans-Cookie is a lightweight executable program, which the server posts to client machines. Cookies store the identity of a user at the first visit of the Web site and validate them later on the next visits for their authenticity. The values of a cookie can be transferred between the user’s request and the server’s response.
11.) What are events and delegates?
Ans-An event is a message sent by a control to notify the occurrence of an action. However it is not known which object receives the event. For this reason, .NET provides a special type called Delegate which acts as an intermediary between the sender object and receiver object.
12.) What is a private assembly?
Ans-A private assembly is local to the installation directory of an application and is used only by that application.
13.) What is a shared assembly?
Ans-A shared assembly is kept in the global assembly cache (GAC) and can be used by one or more applications on a machine.
14.) How do you disable AutoPostBack?
Ans-Hence the AutoPostBack can be disabled on an ASP.NET page by disabling AutoPostBack on all the controls of a page. AutoPostBack is caused by a control on the page.
15.) What is the difference between Server.Transfer and Response.Redirect?
Ans-Response.Redirect involves a roundtrip to the server whereas Server.Transfer conserves server resources by avoiding the roundtrip. It just changes the focus of the webserver to a different page and transfers the page processing to a different page.
Response.Redirect can be used for both .aspx and html pages whereas Server.Transfer can be used only for .aspx pages.
Response.Redirect can be used to redirect a user to an external websites. Server.Transfer can be used only on sites running on the same server. You cannot use Server.Transfer to redirect the user to a page running on a different server.
Response.Redirect changes the url in the browser. So they can be bookmarked. Whereas Server.Transfer retains the original url in the browser. It just replaces the contents of the previous page with the new one.
16.) What method do you use to explicitly kill a user’s session?
Ans-Session.Abandon()..
17.) How to manage pagination in a page?
Ans-Using pagination option in DataGrid control. We have to set the number of records for a page, then it takes care of pagination by itself.
18.) What is ADO .NET and what is difference between ADO and ADO.NET?
Ans-ADO.NET is stateless mechanism. I can treat the ADO.Net as a separate in-memory database where in I can use relationships between the tables and select insert and updates to the database. I can update the actual database as a batch.
19.) How JavaScript and jQuery are different?
Ans-JavaScript is a language While jQuery is a library built in the JavaScript language that helps to use the JavaScript language.
20.) Is jQuery replacement of Java Script?
Ans-No. jQuery is not a replacement of JavaScript. jQuery is a different library which is written on top of JavaScript. jQuery is a lightweight JavaScript library that emphasizes interaction between JavaScript and HTML.
21.) What is the difference between .js and .min.js?
Ans-jQuery library comes in 2 different versions Development and Production/Deployment. The deployment version is also known as minified version. So .min.js is basically the minified version of jQuery library file. Both the files are same as far as functionality is concerned. but .min.js is quite small in size so it loads quickly and saves bandwidth.
22.)Difference between $(this) and ‘this’ in jQuery?
Ans-this and $(this) refers to the same element. The only difference is the way they are used. ‘this’ is used in traditional sense, when ‘this’ is wrapped in $() then it becomes a jQuery object and you are able to use the power of jQuery.
Hide Copy Code
$(document).ready(function(){
$(‘#spnValue’).mouseover(function(){
alert($(this).text());
});
});
In below example, this is an object but since it is not wrapped in $(), we can’t use jQuery method and use the native JavaScript to get the value of span element.
Hide Copy Code
$(document).ready(function(){
$(‘#spnValue’).mouseover(function(){
alert(this.innerText);
});
});
23.) What is the use of jquery .each() function?
Ans-The $.each() function is used to iterate over a jQuery object. The $.each() function can be used to iterate over any collection, whether it is an object or an array.
24.) What is the difference between jquery.size() and jquery.length?
Ans-jQuery .size() method returns number of element in the object. But it is not preferred to use the size() method as jQuery provide .length property and which does the same thing. But the .length property is preferred because it does not have the overhead of a function call.
25.) What is the difference between $(‘div’) and $(‘
‘) in jQuery?
Ans-$(‘
‘):This creates a new div element. However this is not added to DOM tree unless you don’t append it to any DOM element.
$(‘div’) : This selects all the div element present on the page.
26.) What is the difference between event.PreventDefault and event.stopPropagation?
Ans-event.preventDefault(): Stops the default action of an element from happening.
event.stopPropagation(): Prevents the event from bubbling up the DOM tree, preventing any parent handlers from being notified of the event. For example, if there is a link with a click method attached inside of a DIV or FORM that also has a click method attached, it will prevent the DIV or FORM click method from firing.
27.) Can you call C# code-behind method using jQuery? If yes,then how?
Ans-Yes. We can call C# code-behind function via $.ajax. But for do that it is compulsory to mark the method as WebMethod.
28.) Can we share a view across multiple controllers?
Ans-Yes, it is possible to share a view across multiple controllers by putting a view into the shared folder.
29.) How to enable Attribute Routing?
Ans-By adding a Routes.MapMvcAttributeRoutes() method to the RegisterRoutes() method of the RouteConfig.cs file.
30.) What is dependency Injection?
Ans-Dependency Injection is one of the design pattern, which allows to inject dependency on Object, instead of object resolving dependency.
Intermediate level
1. )ASP.Net Page Life Cycle.
Ans-There are few events which gets generated during the page execution like: Page_BeginRequest, Page_Init, Page_Load, Page_Prerender, Page_Render, Page_Unload etc
2. )What are types: Value Type and Reference Type?
Ans-Value type holds data directly, Value type stored in the stack memory, we can get the direct value of the value types. Value type data type can’t be null.
Reference types: This type doesn’t hold the data directly. They hold the address on which the actual data present. They stored in heap memory, Can have default values.
We can make and work with null reference type.
3.) CLR and DLR?
Ans-CLR (Common Language Runtime) is the utility in the .Net framework to run the application. It is the run-time engine which actually executes the application with many responsibilities like taking care of memory management, versioning, CasPol etc.
DLR is new with .Net 4.0 which is the Dynamic Language Runtime and used to run the application on the fly wherever required. CLR runs as statically while DLR runs dynamically.
4.) What is assembly?
Ans-Assembly is the collection of classes, namespaces, methods, properties which may be developed in different language but packed as a dll. So we can say that dll is the assembly.
There are 3 types of assemblies- Private Assembly, Shared Assembly, and Satellite Assembly.
5.) Pros and cons of JavaScript and AJAX.
Ans-JavaScript is a scripting language and mainly used for client side validation. We can validate the client side data before sending to the server. So by this we can improve the performance of the application.
Ajax is Synchronous JavaScript and XML which is used for the Asynchronous calls from the server. It uses internally the JavaScript for making the call and use XML for the Data Transfer. It basically uses the XmlHttpRequest for the asynchronous calls to the server and communicates with the XML data which is platform independent. So Ajax can be used with any technology.
6.) Difference between Server Controls and User controls?
Ans-User controls are used for the re-usability for the controls in the application. By using the user control, we can use the same control in the various pages. User controls can be created by combining more than one control. To use the user controls, first we need to register them in the web page where we want to use that control. A separate copy is need in each page where we want to use the user control. User controls can’t be included in to the toolbox.
Server controls are those controls which can be found in the toolbox and can be directly drag to the application like textbox, button etc. For the server control, only 1 copy of the control is needed irrespective of the number of web pages. If we want 10 text-boxes to be added in our web page, we need only 1 copy of the textbox in the toolbox and can be dragged 10 times.
7.) What is Performance Tuning? How do you implement it.
Ans-Performance Tuning is the process through which we can optimize the SQL Server objects like functions, triggers, stored procedure so that we can achieve high response time to the front end. In the performance tuning process we generally check for the below point and optimize the objects processing:
a. Through Query Execution plan, check for the processing time of the query execution.
b. Check the join conditions and break all the condition for executions of the queries individually
c. Check for the error prone process, conditions in the queries.
d. Check for the loops whether they are terminated if any error occurs
e. Check for the processes which are taking more time in execution and how to reduce the response time.
8.) What is the difference between ViewData, ViewBag and TempData?
Ans-In order to pass data from controller to view and in next subsequent request, ASP.NET MVC framework provides different options i.e., ViewData, ViewBag and TempData.
Both ViewBag and ViewData are used to communicate between controller and corresponding view. But this communication is only for server call, it becomes null if redirect occurs. So, in short, it’s a mechanism to maintain state between controller and corresponding view.
ViewData is a dictionary object while ViewBag is a dynamic property (a new C# 4.0 feature). ViewData being a dictionary object is accessible using strings as keys and also requires typecasting for complex types. On the other hand, ViewBag doesn’t have typecasting and null checks.
TempData is also a dictionary object that stays for the time of an HTTP Request. So, Tempdata can be used to maintain data between redirects, i.e., from one controller to the other controller.
9). What is a Partial class?
Ans-Instead of defining an entire class, you can split the definition into multiple classes by using partial class keyword. When the application compiled, c# compiler will group all the partial classes together and treat them as a single class. There are a couple of good reasons to use partial classes. Programmers can work on different parts of classes without needing to share same physical file.
Ex:
Public partial class employee
{
Public void somefunction()
{
}
}
Public partial class employee
{
Public void function ()
{
}
}
10). What is the difference between application exception and system exception?
Ans-The difference between application exception and system exception is that system exceptions are thrown by CLR and application exceptions are thrown by applications.
11). What is the difference between .tostring(), Convert.tostring()?
Ans-The basic difference between them is “Convert” function handles NULLS while
“.ToString()” does not it will throw a NULL reference exception error. So as a good coding practice using “convert” is always safe.
12.) What is a connection pool?
Ans-A connection pool is a ‘collection of connections’ which are shared between the clients requesting one. Once the connection is closed, it returns back to the pool. This allows the connections to be reused.
13.) What is smart navigation?
Ans-The cursor position is maintained when the page gets refreshed due to the server side validation and the page gets refreshed.
14.)What is “Common Type System” (CTS)?
Ans-CTS defines all of the basic types that can be used in the .NET Framework and the operations performed on those type. All this time we have been talking about language interoperability, and .NET Class Framework. None of this is possible without all the language sharing the same data types. What this means is that an int should mean the same in VB, VC++, C# and all other .NET compliant languages. This is achieved through introduction of Common Type System (CTS).
15.) How to configure HTTPS for a web application?
Ans-To configure the HTTPS (HTTP with Secure) for the web application, we need to have a client certificate. We can purchase the client certificate from the trusted providers and then we need to install that provider for our site. By implementing the HTTPS, all the data which is passing will be in encrypted format and will be more secure.
16.) What is MS-IL (Microsoft Intermediate Language) ?
Ans-When a program is complied in .Net, the source code will be converted into an intermediate language called Microsoft Intermediate Language (MS-IL).
17.) What is Garbage Collector ?
Ans-Garbage Collector is used in dot net Framework for memory management. While running an application, applications make a request for memory for its internal use. Framework allocates memory from the heap. Once the process is completed, allocated memory needs to be reclaimed for future use. The process of reclaiming unused memory is taken care by the Garbage Collector.
18.) Explain JSON Binding?
Ans-JavaScript Object Notation (JSON) binding support started from MVC3 onwards via the new JsonValueProviderFactory, which allows the action methods to accept and model-bind data in JSON format. This is useful in Ajax scenarios like client templates and data binding that need to post data back to the server.
19.) What is the difference between shadow and override?
Ans-In general when you extend a class you shadow fields with the same name in the base class and override virtual methods with the same name and parameter list in the base class. Overriding makes the base class method invisible. Shadowing a field only hides the field from view. You can still explicitly touch the hidden shadowed field if you wish. You cannot touch an invisible overridden method.
20.) What are the different types of action filters?
Ans-Authorization filters
Action filters
Result filters
Exception filters
21.) What are sections?
Ans-Layout pages, can define sections, which can then be overriden by specific views making use of the layout. Defining and overriding sections is optional.
22.) What are Bundling & Minification features in ASP.NET MVC 4?
Ans-Bundling & Minification reduces number of HTTP requests. Bundling & Minification combines individual files into single. Bundled file for CSS & scripts and then it reduce’s overall size by minifying the contents of the bundle.
23.) What are Action Filters in ASP.NET MVC?
If we need to apply some specific logic before or after action methods, we use action filters. We can apply these action filters to a controller or a specific controller action. Action filters are basically custom classes that provide a means for adding pre-action or post-action behavior to controller actions.
24.) List out the differences between WCF and Web API?
WCF
It is framework build for building or developing service oriented applications.
WCF can be consumed by clients which can understand XML.
WCF supports protocols like – HTTP, TCP, Named Pipes etc.
Web API
It is a framework which helps us to build/develop HTTP services
Web API is an open source platform.
It supports most of the MVC features which keep Web API over WCF.
25.) How we can restrict access to methods with specific HTTP verbs in Web API?
Ans-Attribute programming is used for this functionality. Web API will support to restrict access of calling methods with specific HTTP verbs. We can define HTTP verbs as attribute over method as shown below.
[HttpPost]
public void UpdateTestCustomer(Customer c)
{
TestCustomerRepository.AddCustomer(c);
}
26.) Explain the difference between classic web services (ASMX) and WCF?
Ans-Classic webservice known as ASMX were using SOAP protocol for sending and receiving the messages over network and over HTTP protocol whereas WCF allows the communication to happen over any transport protocol.
27.) Explain the types of contracts available in WCF?
Ans-Below are the list types of contracts available in WCF
Data Contracts
Service Contracts
Message Contracts
Fault Contracts
28.) What are Non Action methods in ASP.Net MVC?
Ans-In ASP.Net MVC all public methods have been treated as Actions. So if you are creating a method and if you do not want to use it as an action method then the method has to be decorated with “NonAction” attribute as shown below :
[NonAction]
public void TestMethod()
{
// Method logic
}
29.) What is Html.RenderPartial?
Ans-Result of the method : “RenderPartial” is directly written to the HTML response. This method does not return anything (void). This method also does not depend on action methods. RenderPartial() method calls “Write()” internally and we have to make sure that “RenderPartial” method is enclosed in the bracket. Below is the sample code snippet : @{Html.RenderPartial(“TestPartialView”); }
30.) How can we determine action invoked from HTTP GET or HTTP POST?
Ans-This can be done in following way : Use class : “HttpRequestBase” and use the
method : “HttpMethod” to determine the action request type.
DataBase Questions-
1.) Describe the three levels of data abstraction?
Ans-The are three levels of abstraction:
Physical level: The lowest level of abstraction describes how data are stored.
Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
View level: The highest level of abstraction describes only part of entire database.
2.) What is a view?
Ans-A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.
3.) What is an Entity?
Ans-It is a ‘thing’ in the real world with an independent existence.
4. What is Relational Calculus?
Ans-It is an applied predicate calculus specifically tailored for relational databases proposed by E.F. Codd. E.g. of languages based on it are DSL ALPHA, QUEL.
5.) What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
Ans-A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
6.) What is the difference between DELETE and TRUNCATE commands?
Ans-DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement.
TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.
7.) What is a stored procedure?
Ans-Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.
8. )What is data Integrity?
Ans-Data Integrity defines the accuracy and consistency of data stored in a database. It can also define integrity constraints to enforce business rules on the data when it is entered into the application or database.
9.) What is Auto Increment?
Ans-Autoincrement keyword allows the user to create a unique number to be generated when a new record is inserted into the table. AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.
Mostly this keyword can be used whenever PRIMARY KEY is used.
10.)What is the difference between Cluster and Non-Cluster Index?
Ans-Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.
A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.
11.) What is Datawarehouse?
Ans-Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data have a subset of data called Data Marts.
12.) What is Self-Join?
Ans-Self-join is set to be query used to compare to itself. This is used to compare values in a column with other values in the same column in the same table. ALIAS ES can be used for the same table comparison.
13.) What is Cross-Join?
Ans-Cross join defines as Cartesian product where number of rows in the first table multiplied by number of rows in the second table. If suppose, WHERE clause is used in cross join then the query will work like an INNER JOIN.
14.)What is user defined functions?
Ans-User defined functions are the functions written to use that logic whenever required. It is not necessary to write the same logic several times. Instead, function can be called or executed whenever needed.
15.) What are all types of user defined functions?
Ans-Three types of user defined functions are.
Scalar Functions.
Inline Table valued functions.
Multi statement valued functions.
Scalar returns unit, variant defined the return clause. Other two types return table as a return.
16.) What is collation?
Ans-Collation is defined as set of rules that determine how character data can be sorted and compared. This can be used to compare A and, other language characters and also depends on the width of the characters.
ASCII value can be used to compare these character data.
17.) Advantages and Disadvantages of Stored Procedure?
Ans-Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.
Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.
18.) What is CLAUSE?
Ans-SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records.
Example – Query that has WHERE condition
19. )What is the difference between an inner and outer join?
Ans-An inner join involves joining two tables where a common id/key exists in both. An outer join is the joining of two tables, but where there is no match in the second
(or first).
20.) Differentiate between a Local and a Global temporary table?
Ans-A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
Global temporary tables (created with a double “##”) are visible to all sessions.
Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.
21.) When is the UPDATE_STATISTICS command used?
Ans-When the processing of large data is done, this command is used.
Whenever large number of deletions, modification or copy takes place into the tables, the indexes need to be updated to take care of these changes. UPDATE_STATISTICS performs this job.
21.) Differentiate between a HAVING CLAUSE and a WHERE CLAUSE.
Ans-HAVING CLAUSE
– HAVING CLAUSE is used only with the SELECT statement.
– It is generally used in a GROUP BY clause in a query.
– If GROUP BY is not used, HAVING works like a WHERE clause.
WHERE Clause
– It is applied to each row before they become a part of the GROUP BY function in a query.
22.) What is Cursor?
Ans- Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor we need to perform some steps in the following order:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor
23. )What is sub-query?
Ans-Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.
24.) What is SQL Server Agent?
Ans-SQL Server agent plays an important role in the day-to-day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. 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.
25.) Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Ans-Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.
26.) What is CHECK Constraint?
Ans-A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
27.) What is NOT NULL Constraint?
Ans-A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
28.) What are Sparse Columns?
Ans-A sparse column is another tool used to reduce the amount of physical storage used in a database. They are the ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values.
29.) What does TOP Operator Do?
Ans-The TOP operator is used to specify the number of rows to be returned by a query. The TOP operator has new addition in SQL SERVER 2008 that it accepts variables as well as literal values and can be used with INSERT, UPDATE, and DELETES statements.
30.) What is Aggregate Functions?
Ans-Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY, for filtering query using aggregate values.
Following functions are aggregate functions.
AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP
31.)What is Row_Number()?
Ans-ROW_NUMBER() returns a column as an expression that contains the row’s number within the result set. This is only a number used in the context of the result set, if the result changes, the ROW_NUMBER() will change.
32.) What is the difference between UNION and UNION ALL?
Ans-UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.
UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table
33.)What are the advantages of using Stored Procedures?
Ans-Stored procedure can reduced network traffic and latency, boosting application performance.
Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
Stored procedures help promote code reuse.
Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
Stored procedures provide better security to your data.
34.) How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
Ans-One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
35.) How would apply date range filter?
Ans-You can use simple condition >= and
Sometimes date fields contain time and that is where the query can go wrong so it is recommended to use some date related functions to remove the time issue. In SQL Server common function to do that is datediff function.
You also have to be aware of different time zones and server time zone.
To increase query performance you may still want to use between however you should be aware of proper format you should use if not it might misbehave during filtering.