1. What is the full form of ADO?
The full form of ADO is ActiveX Data Object.
2. Explain ADO.NET in brief.
ADO.NET is a very important feature of .NET Framework, which is used to
work with data that is stored in structured data sources, such as databases and
XML files. The following are some of the important features of ADO.NET:
Contains a number of classes that provide you with various methods and
attributes to manage the communication between your application and data
source.
Enables you to access different data sources, such as Microsoft SQL Server,
and XML, as per your requirements.
Provides a rich set of features, such as connection and commands that can
be used to develop robust and highly efficient data services in .NET
applications.
Provides various data providers that are specific to databases produced by
various vendors. For example, ADO.NET has a separate provider to access data
from Oracle databases; whereas, another provider is used to access data from
SQL databases.
3. What are major difference between classic ADO and ADO.NET?
Following are some major differences between both
In ADO we have recordset and in ADO.NET we have dataset.
In recordset we can only have one table. If we want to accommodate more
than one tables. We need to do inner join and fill the recordset. Dataset can
have multiple tables.
All data persist in XML as compared to classic ADO where data persisted in
Binary format also.
4. What are the two fundamental objects in ADO.NET?
DataReader and DataSet are the two fundamental objects in ADO.NET.
5. What are the benefits of using of ADO.NET in .NET 4.0.
The following are the benefits of using ADO.NET in .NET 4.0 are as follows:
Language-Integrated Query (LINQ) - Adds native data-querying capabilities
to .NET languages by using a syntax similar to that of SQL. This means that
LINQ simplifies querying by eliminating the need to use a separate query
language. LINQ is an innovative technology that was introduced in .NET
Framework 3.5.
LINQ to DataSet - Allows you to implement LINQ queries for disconnected
data stored in a dataset. LINQ to DataSet enables you to query data that is
cached in a DataSet object. DataSet objects allow you to use a copy of the data
stored in the tables of a database, without actually getting connected to the
database.
LINQ to SQL - Allows you to create queries for data stored in SQL server
database in your .NET application. You can use the LINQ to SQL technology to
translate a query into a SQL query and then use it to retrieve or manipulate
data contained in tables of an SQL Server database. LINQ to SQL supports all
the key functions that you like to perform while working with SQL, that is, you
can insert, update, and delete information from a table.
SqlClient Support for SQL Server 2008 - Specifies that with the starting of
.NET Framework version 3.5 Service Pack (SP) 1, .NET Framework Data Provider
for SQL Server (System.Data.SqlClient namespace) includes all the new features
that make it fully compatible with SQL Server 2008 Database Engine.
ADO.NET Data Platform - Specifies that with the release of .NET Framework
3.5 Service Pack (SP) 1, an Entity Framework 3.5 was introduced that provides a
set of Entity Data Model (EDM) functions. These functions are supported by all
the data providers; thereby, reducing the amount of coding and maintenance in
your application. In .NET Framework 4.0, many new functions, such as string,
aggregate, mathematical, and date/time functions have been added.
6. Which namespaces are required to enable the use of databases in ASP.NET
pages?
The following namespaces are required to enable the use of databases in
ASP.NET pages:
The System.Data namespace.
The System.Data.OleDb namespace (to use any data provider, such as Access,
Oracle, or SQL)
The System.Data.SQLClient namespace (specifically to use SQL as the data
provider)
1. What is the full form of ADO?
The full form of ADO is ActiveX Data Object.
2. Explain ADO.NET in brief.
ADO.NET is a very important feature of .NET Framework, which is used to
work with data that is stored in structured data sources, such as databases and
XML files. The following are some of the important features of ADO.NET:
Contains a number of classes that provide you with various methods and
attributes to manage the communication between your application and data
source.
Enables you to access different data sources, such as Microsoft SQL Server,
and XML, as per your requirements.
Provides a rich set of features, such as connection and commands that can
be used to develop robust and highly efficient data services in .NET
applications.
Provides various data providers that are specific to databases produced by
various vendors. For example, ADO.NET has a separate provider to access data
from Oracle databases; whereas, another provider is used to access data from
SQL databases.
3. What are major difference between classic ADO and ADO.NET?
Following are some major differences between both
In ADO we have recordset and in ADO.NET we have dataset.
In recordset we can only have one table. If we want to accommodate more
than one tables. We need to do inner join and fill the recordset. Dataset can
have multiple tables.
All data persist in XML as compared to classic ADO where data persisted in
Binary format also.
4. What are the two fundamental objects in ADO.NET?
DataReader and DataSet are the two fundamental objects in ADO.NET.
5. What are the benefits of using of ADO.NET in .NET 4.0.
The following are the benefits of using ADO.NET in .NET 4.0 are as follows:
Language-Integrated Query (LINQ) - Adds native data-querying capabilities
to .NET languages by using a syntax similar to that of SQL. This means that
LINQ simplifies querying by eliminating the need to use a separate query
language. LINQ is an innovative technology that was introduced in .NET
Framework 3.5.
LINQ to DataSet - Allows you to implement LINQ queries for disconnected
data stored in a dataset. LINQ to DataSet enables you to query data that is
cached in a DataSet object. DataSet objects allow you to use a copy of the data
stored in the tables of a database, without actually getting connected to the
database.
LINQ to SQL - Allows you to create queries for data stored in SQL server
database in your .NET application. You can use the LINQ to SQL technology to
translate a query into a SQL query and then use it to retrieve or manipulate
data contained in tables of an SQL Server database. LINQ to SQL supports all
the key functions that you like to perform while working with SQL, that is, you
can insert, update, and delete information from a table.
SqlClient Support for SQL Server 2008 - Specifies that with the starting of
.NET Framework version 3.5 Service Pack (SP) 1, .NET Framework Data Provider
for SQL Server (System.Data.SqlClient namespace) includes all the new features
that make it fully compatible with SQL Server 2008 Database Engine.
ADO.NET Data Platform - Specifies that with the release of .NET Framework
3.5 Service Pack (SP) 1, an Entity Framework 3.5 was introduced that provides a
set of Entity Data Model (EDM) functions. These functions are supported by all
the data providers; thereby, reducing the amount of coding and maintenance in
your application. In .NET Framework 4.0, many new functions, such as string,
aggregate, mathematical, and date/time functions have been added.
6. Which namespaces are required to enable the use of databases in ASP.NET
pages?
The following namespaces are required to enable the use of databases in
ASP.NET pages:
The System.Data namespace.
The System.Data.OleDb namespace (to use any data provider, such as Access,
Oracle, or SQL)
The System.Data.SQLClient namespace (specifically to use SQL as the data
provider)
13. Name the two properties of the GridView control that have to be
specified to turn on sorting and paging.
The properties of the GridView control that need to be specified to turn on
sorting and paging are as follows:
The AllowSorting property of the Gridview control indicates whether sorting
is enabled or not. You should set the AllowSorting property to True to enable
sorting.
The AllowPaging property of the Gridview control indicates whether paging
is enabled or not. You should set the AllowPaging property to True to enable
paging.
14. Mention different types of data providers available in .NET Framework.
.NET Framework Data Provider for SQL Server - Provides access to Microsoft
SQL Server 7.0 or later version. It uses the System.Data.SqlClient namespace.
.NET Framework Data Provider for OLE DB - Provides access to databases
exposed by using OLE DB. It uses the System.Data.OleDb namespace.
.NET Framework Data Provider for ODBC - Provides access to databases
exposed by using ODBC. It uses the System.Data.Odbc namespace.
.NET Framework Data Provider for Oracle - Provides access to Oracle
database 8.1.7 or later versions. It uses the System.Data.OracleClient
namespace.
15. Which architecture does Datasets follow?
Datasets follow the disconnected data architecture.
16. What is the role of the DataSet object in ADO.NET?
One of the major component of ADO.NET is the DataSet object, which always
remains disconnected from the database and reduces the load on the database.
17. What is a DataReader object?
The DataReader object helps in retrieving the data from a database in a
forward-only, read-only mode. The base class for all the DataReader objects is
the DbDataReader class.
The DataReader object is returned as a result of calling the
ExecuteReader() method of the Command object. The DataReader object enables
faster retrieval of data from databases and enhances the performance of .NET
applications by providing rapid data access speed. However, it is less
preferred as compared to the DataAdapter object because the DataReader object
needs an Open connection till it completes reading all the rows of the
specified table.
An Open connection to read data from large tables consumes most of the
system resources. When multiple client applications simultaneously access a
database by using the DataReader object, the performance of data retrieval and
other related processes is substantially reduced. In such a case, the database
might refuse connections to other .NET applications until other clients free
the resources.
18. How can you identify whether or not any changes are made to the DataSet
object since it was last loaded?
The DataSet object provides the following two methods to track down the
changes:
The GetChanges() method - Returns the DataSet object, which is changed
since it was loaded or since the AcceptChanges() method was executed.
The HasChanges() method - Indicates if any changes occurred since the
DataSet object was loaded or after a call to the AcceptChanges() method was
made.
If you want to revert all changes since the DataSet object was loaded, use
the RejectChanges() method.
19. Which property is used to check whether a DataReader is closed or
opened?
The IsClosed property is used to check whether a DataReader is closed or
opened. This property returns a true value if a Data Reader is closed,
otherwise a false value is returned.
20. Name the method that needs to be invoked on the DataAdapter control to
fill the generated DataSet with data?
The Fill() method is used to fill the dataset with data.
21. What is the use of the Connection object?
The Connection object is used to connect your application to a specific
data source by providing the required authentication information in connection string.
The connection object is used according to the type of the data source. For
example, the OleDbConnection object is used with an OLE-DB provider and the
SqlConnection object is used with an MS SQL Server.
22. What is the use of the CommandBuilder class?
The CommandBuilder class is used to automatically update a database
according to the changes made in a DataSet.
This class automatically registers itself as an event listener to the
RowUpdating event. Whenever data inside a row changes, the object of the
CommandBuilder class automatically generates an SQL statement and uses the
SelectCommand property to commit the changes made in DataSet.
OLEDB provider in .NET Framework has the OleDbCommandBuiider class;
whereas, the SQL provider has the SqlCommandBuilder class.
23. Explain the architecture of ADO.NET in brief.
AD0.NET consists of two fundamental components:
The DataSet, which is disconnected from the data source and does not need
to know where the data that it holds is retrieved from.
The .net data provider, which allows you to connect your application to the
data source and execute the SQL commands against it.
The data provider contains the Connection, Command, DataReader, and
DataAdapter objects. The Connection object provides connectivity to the
database. The Command object provides access to database commands to retrieve
and manipulate data in a database. The DataReader object retrieves data from
the database in the readonly and forward-only mode. The DataAdapter object uses
Command objects to execute SQL commands. The DataAdapter object loads the
DataSet object with data and also updates changes that you have made to the
data in the DataSet object back to the database.
24. Describe the disconnected architecture of ADO.NET's data access model.
ADO.NET maintains a disconnected database access model, which means, the
application never remains connected constantly to the data source. Any changes
and operations done on the data are saved in a local copy (dataset) that acts
as a data source. Whenever, the connection to the server is re-established,
these changes are sent back to the server, in which these changes are saved in
the actual database or data source.
25. What are the usages of the Command object in ADO.NET?
The following are the usages of the Command object in AD0.NET:
The Command object in AD0.NET executes a command against the database and
retrieves a DataReader or DataSet object.
It also executes the INSERT, UPDATE, or DELETE command against the
database.
All the command objects are derived from the DbCommand class.
The command object is represented by two classes: SqlCommand and
OleDbCommand.
The Command object provides three methods to execute commands on the
database:
The ExecuteNonQuery() method executes the commands and does not return any
value.
The ExecuteScalar() method returns a single value from a database query.
The ExecuteReader() method returns a result set by using the DataReader
object.
26. What are the pre-requisites for connection pooling?
The prerequisites for connection pooling are as follows:
There must be multiple processes to share the same connection describing
the same parameters and security settings.
The connection string must be identical.
27. What is connection pooling?
Connection pooling refers to the task of grouping database connections in
cache to make them reusable because opening new connections every time to a
database is a time-consuming process. Therefore, connection pooling enables you
to reuse already existing and active database connections, whenever required,
and increasing the performance of your application.
You can enable or disable connection pooling in your application by setting
the pooling property to either true or false in connection string. By default,
it is enabled in an application.
28. What are the various methods provided by the DataSet object to generate
XML?
The various methods provided by the DataSet object to generate XML are:
ReadXml() - Reads XML document into a DataSet object.
GetXml() - Returns a string containing an XML document.
WriteXml() - Writes an XML data to disk.
29. Out of Windows authentication and SQL Server authentication, which
authentication technique is considered as a trusted authentication method?
The Windows authentication technique is considered as a trusted
authentication method because the username and password are checked with the
Windows credentials stored in the Active Directory.
The SQL Server Authentication technique is not trusted as all the values
are verified by SQL Server only.
30. How would you connect to a database by using .NET?
The connection class is used to connect a .NET application with a database.
31. Which adapter should you use, if you want to get the data from an
Access database?
OleDbDataAdapter is used to get the data from an Access database.
32. Which object is used to add a relationship between two DataTable
objects?
The DataRelation object is used to add relationship between two DataTable
objects.
33. What are different types of authentication techniques that are used in
connection strings to connect .NET applications with Microsoft SQL Server?
.NET applications can use two different techniques to authenticate and
connect with SQL Server. These techniques are as follows:
The Windows Authentication option
The SQL Server Authentication option
34. Explain the new features in ADO.NET Entity Framework 4.0.
ADO.NET Entity Framework 4.0 is introduced in .NET Framework 4.0 and
includes the following new features:
Persistence Ignorance - Facilitates you to define your own Plain Old CLR
Objects (POCO) which are independent of any specific persistence technology.
Deferred or Lazy Loading - Specifies that related entities can be loaded
automatically whenever required. You can enable lazy loading in your
application by setting the DeferredLoadingEnabled property to true.
Self-Tracking Entities - Refers to the entities that are able to track
their own changes. These changes can be passed across process boundaries and
saved to the database.
Model-First Development - Allows you to create your own EDM and then
generate relational model (database) from that EDM with matching tables and
relations.
Built-in Functions - Enables you to use built-in SQL Server functions
directly in your queries.
Model-Defined Functions - Enables you to use the functions that are defined
in conceptual schema definition language (CSDL).
35. What is the difference between the Clone() and Copy() methods of the
DataSet class?
The Clone() method copies only the structure of a DataSet. The copied
structure includes all the relation, constraint, and DataTable schemas used by
the DataSet. The Clone() method does not copy the data, which is stored in the
DataSet.
The Copy() method copies the structure as well as the data stored in the
DataSet.
36. What is the use of DataView?
User-defined view of a table is contained in a DataView. A complete table
or a small section of table depending on some criteria can be presented by an
object of the DataView class. You can use this class to sort and find data
within DataTable.
The DataView class has the following methods:
Find() - Finds a row in a DataView by using sort key value.
FindRows() - Uses the sort key value to match it with the columns of
DataRowView objects. It returns an array of all the corresponding objects of
DataRowView whose columns match with the sort key value.
AddNew() - Adds a new row to the DataView object.
Delete() - Deletes the specified row from the DataView object according to
the specified index.
37. What are the parameters that control most of connection pooling
behaviors?
The parameters that control most of connection pooling behaviors are as
follows:
Connect Timeout
Max Pool Size
Min Pool Size
Pooling
38. How can you add or remove rows from the DataTable object of DataSet?
The DataRowCollection class defines the collection of rows for the
DataTable object in a DataSet. The DataTable class provides the NewRow() method
to add a new DataRow to DataTable. The NewRow method creates a new row, which
implements the same schema as applied to the DataTable. The following are the
methods provided by the DataRowCollection object:
Add() - Adds a new row to DataRowCollection.
Remove()- Removes a DataRow object from DataRowCollection.
RemoveAt() - Removes a row whose location is specified by an index number.
39. Explain in brief DataAdapter class in ADO.NET.
The DataAdapter class retrieves data from the database, stores data in a
dataset, and reflects the changes made in the dataset to the database. The
DataAdapter class acts as an intermediary for all the communication between the
database and the DataSet object. The DataAdapter Class is used to fill a
DataTable or DataSet Object with data from the database using the Fill()
method. The DataAdapter class applies the changes made in dataset to the
database by calling the Update() method.
The DataAdapter class provides four properties that represent the database
command:
SelectCommand, InsertCommand, DeleteCommand, and UpdateCommand.
What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management
systems that maintain data records and indices in tables. Relationships may be
created and maintained across and among the data and tables. In a relational
database, relationships between data items are expressed by means of tables.
Interdependencies among these tables are expressed by data values rather than
by pointers. This allows a high degree of data independence. An RDBMS has the
capability to recombine the data items from different files, providing powerful
tools for data usage. (Read More Here)
What are the properties of the Relational tables?
Relational tables have six properties:
Values are atomic.
Column values are of the same kind.
Each row is unique.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name.
What is Normalization?
Database normalization is a data design and organization process applied to
data structures based on rules that help building relational databases. In
relational database design, the process of organizing data to minimize
redundancy is called normalization. Normalization usually involves dividing a
database into two or more tables and defining relationships between the tables.
The objective is to isolate data so that additions, deletions, and
modifications of a field can be made in just one table and then propagated
through the rest of the database via the defined relationships.
What are different normalization forms?
1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each
table a primary key. Each field contains at most one value from its attribute
domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a
separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to
a separate table. All attributes must be directly dependent on the primary key.
(Read More Here)
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes,
separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not
directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating
logically related many-to-many relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object
Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies is said to be in DKNF.
Remember, these normalization guidelines are cumulative. For a database to
be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.
What is De-normalization?
De-normalization is the process of attempting to optimize the performance
of a database by adding redundant data. It is sometimes necessary because
current DBMSs implement the relational model poorly. A true relational DBMS
would allow for a fully normalized database at the logical level, while
providing physical storage of data that is tuned for high performance.
De-normalization is a technique to move from higher to lower normal forms of
database modeling in order to speed up database access.
What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been
previously created and stored in the server database. Stored procedures accept
input parameters so that a single procedure can be used over the network by
several clients using different input data. And when the procedure is modified,
all clients automatically get the new version. Stored procedures reduce network
traffic and improve performance. Stored procedures can be used to help ensure
the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.
What is Trigger?
A trigger is a SQL procedure that initiates an action when an event
(INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the
DBMS. Triggers are used to maintain the referential integrity of data by
changing the data in a systematic fashion. A trigger cannot be called or
executed; DBMS automatically fires the trigger as a result of a data
modification to the associated table. Triggers can be viewed as similar to
stored procedures in that both consist of procedural logic that is stored at
the database level. Stored procedures, however, are not event-drive and are not
attached to a specific table as triggers are. Stored procedures are explicitly
executed by invoking a CALL to the procedure while triggers are implicitly
executed. In addition, triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic
within itself, so when the trigger is fired because of data modification it can
also cause another data modification, thereby firing another trigger. A trigger
that contains data modification logic within itself is called a nested trigger.
(Read More Here)
What is View?
A simple view can be thought of as a subset of a table. It can be used for
retrieving data, as well as updating or deleting rows. Rows updated or deleted
in the view are updated or deleted in the table the view was created with. It
should also be noted that as data in the original table changes, so does data
in the view, as views are the way to look at part of the original table. The
results of using a view are not permanently stored in the database. The data
accessed through a view is actually constructed using standard T-SQL select
command and can come from one to many different base tables or even other
views.
What is Index?
An index is a physical structure containing pointers to the data. Indices
are created in an existing table to locate rows more quickly and efficiently.
It is possible to create an index on one or more columns of a table, and each
index is given a name. The users cannot see the indexes; they are just used to
speed up queries. Effective indexes are one of the best ways to improve performance
in a database application. A table scan happens when there is no index
available to help a query. In a table scan SQL Server examines every row in the
table to satisfy the query results. Table scans are sometimes unavoidable, but
on large tables, scans have a terrific impact on performance.
What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL
Server to a Group and query both the SQL Server dbs using T-SQL Statements.
With a linked server, you can create very clean, easy to follow, SQL statements
that allow remote data to be retrieved, joined and combined with local data.
Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new
Linked Server. (Read More Here)
1. Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility
TCP/IP properties.
2. What are the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records
in the table are physically stored. Therefore table can have only one clustered
index. The leaf nodes of a clustered index contain the data pages.
A non clustered index is a special type of index in which the logical order
of the index does not match the physical stored order of the rows on disk. The
leaf node of a non clustered index does not consist of the data pages. Instead,
the leaf nodes contain index rows.
3. What are the different index configurations a table can have?
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes
4. What are different types of Collation Sensitivity?
Case sensitivity - A and a, B and b, etc.
Accent sensitivity
Kana Sensitivity - When Japanese kana characters Hiragana and Katakana are
treated differently, it is called Kana sensitive.
Width sensitivity - A single-byte character (half-width) and the same character
represented as a double-byte character (full-width) are treated differently
than it is width sensitive.
5. What is OLTP (Online Transaction Processing)?
In OLTP - online transaction processing systems relational database design
use the discipline of data modeling and generally follow the Codd rules of data
normalization in order to ensure absolute data integrity. Using these rules
complex information is broken down into its most simple structures (a table)
where all of the individual atomic level elements relate to each other and
satisfy the normalization rules.
6. What's the difference between a primary key and a unique key?
Both primary key and unique key enforces uniqueness of the column on which
they are defined. But by default primary key creates a clustered index on the
column, where are unique creates a nonclustered index by default. Another major
difference is that, primary key doesn't allow NULLs, but unique key allows one
NULL only.
7. What is difference between DELETE and TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we
provide with a WHERE clause. Truncate will actually remove all the rows from a
table and there will be no data in the table after we run the truncate command.
TRUNCATE:
TRUNCATE is faster and uses fewer system and transaction log resources than
DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the
table's data, and only the page deallocations are recorded in the transaction
log.
TRUNCATE removes all rows from a table, but the table structure, its
columns, constraints, indexes and so on, remains. The counter used by an
identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE cannot be rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table
DELETE:
DELETE removes rows one at a time and records an entry in the transaction
log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want
to remove table definition and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE can be rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.
Note: DELETE and TRUNCATE both can be rolled back when surrounded by
TRANSACTION if the current session is not closed. If TRUNCATE is written in
Query Editor surrounded by TRANSACTION and if session is closed, it can not be
rolled back but DELETE can be rolled back.
8. When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has
occurred. If a large amount of deletions any modification or Bulk Copy into the
tables has occurred, it has to update the indexes to take these changes into
account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
9. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
They specify a search condition for a group or an aggregate. But the
difference is that HAVING can be used only with the SELECT statement. HAVING is
typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves
like a WHERE clause. Having Clause is basically used only with the GROUP BY
function in a query whereas WHERE Clause is applied to each row before they are
part of the GROUP BY function in a query.
10. What are the properties and different Types of Sub-Queries?
Properties of Sub-Query
A sub-query must be enclosed in the parenthesis.
A sub-query must be put in the right hand of the comparison operator, and
A sub-query cannot contain an ORDER-BY clause.
A query can contain more than one sub-query.
Types of Sub-Query
Single-row sub-query, where the sub-query returns only one row.
Multiple-row sub-query, where the sub-query returns multiple rows,. and
Multiple column sub-query, where the sub-query returns multiple columns
11. What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to
monitor events in an instance of Microsoft SQL Server. You can capture and save
data about each event to a file or SQL Server table to analyze later. For
example, you can monitor a production environment to see which stored
procedures are hampering performances by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If
traces are becoming too large, you can filter them based on the information you
want, so that only a subset of the event data is collected. Monitoring too many
events adds overhead to the server and the monitoring process and can cause the
trace file or trace table to grow very large, especially when the monitoring
process takes place over a long period of time.
12. What are the authentication modes in SQL Server? How can it be changed?
Windows mode and Mixed Mode - SQL and Windows. To change authentication
mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL
Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server
program group. Select the server then from the Tools menu select SQL Server Configuration
Properties, and choose the Security page.
13. Which command using Query Analyzer will give you the version of SQL
server and operating system?
SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition').
14. What is SQL Server Agent?
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.
15. Can a stored procedure call itself or recursive stored procedure? How
much level SP nesting is possible?
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.
16. What is Log Shipping?
Log shipping is the process of automating the backup of database and
transaction log files on a production SQL server, and then restoring them onto a
standby server. Enterprise Editions only supports log shipping. In log shipping
the transactional log file from one server is automatically updated into the
backup database on the other server. If one server fails, the other server will
have the same db and can be used this as the Disaster Recovery plan. The key
feature of log shipping is that it will automatically backup transaction logs
throughout the day and automatically restore them on the standby server at
defined interval.
17. Name 3 ways to get an accurate count of the number of records in a
table?
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
18. What does it mean to have QUOTED_IDENTIFIER ON? What are the
implications of having it OFF?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double
quotation marks, and literals must be delimited by single quotation marks. When
SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all
Transact-SQL rules for identifiers.
19. What is the difference between a Local and a Global temporary table?
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.
A global temporary table remains in the database permanently, but the rows
exist only within a given connection. When connection is closed, the data in
the global temporary table disappears. However, the table definition remains
with the database for access when database is opened next time.
20. What is the STUFF function and how does it differ from the REPLACE
function?
STUFF function is used to overwrite existing characters. Using this syntax,
STUFF (string_expression, start, length, replacement_characters),
string_expression is the string that will have characters substituted, start is
the starting position, length is the number of characters in the string that
are substituted, and replacement_characters are the new characters interjected
into the string. REPLACE function to replace existing characters of all
occurrences. Using the syntax REPLACE (string_expression, search_string,
replacement_string), where every incidence of search_string found in the
string_expression will be replaced with replacement_string.
21. What is PRIMARY KEY?
A PRIMARY KEY constraint is a unique identifier for a row within a database
table. Every table should have a primary key constraint to uniquely identify
each row and only one primary key constraint can be created for each table. The
primary key constraints are used to enforce entity integrity.
22. What is UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of
columns, so no duplicate values are entered. The unique key constraints are
used to enforce entity integrity as the primary key constraints.
23. What is FOREIGN KEY?
A FOREIGN KEY constraint prevents any actions that would destroy links
between tables with the corresponding data values. A foreign key in one table
points to a primary key in another table. Foreign keys prevent actions that
would leave rows with foreign key values when there are no primary keys with
that value. The foreign key constraints are used to enforce referential
integrity.
24. What is CHECK Constraint?
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.
25. What is NOT NULL Constraint?
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.
26. How to get @@ERROR and @@ROWCOUNT at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0
as the value of @@Recordcount as it would have been reset. And if @@Recordcount
is checked before the error-checking statement then @@Error would get reset. To
get @@error and @@rowcount at the same time do both in same statement and store
them in local variable.
SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
27. What is a Scheduled Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes that run on regular or
predictable cycles. User can schedule administrative tasks, such as cube
processing, to run during times of slow business activity. User can also
determine the order in which tasks run by creating job steps within a SQL
Server Agent job. E.g. back up database, Update Stats of Tables. Job steps give
user control over flow of execution. If one job fails, user can configure SQL
Server Agent to continue to run the remaining tasks or to stop execution.
28. What are the advantages of using Stored Procedures?
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.
29. What is a table called, if it has neither Cluster nor Non-cluster
Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL)
refers it as Heap. A heap is a table that does not have a clustered index and,
therefore, the pages are not linked by pointers. The IAM pages are the only
structures that link the pages in a table together. Unindexed tables are good
for fast storing of data. Many times it is better to drop all indexes from
table and then do bulk of inserts and to restore those indexes after that.
30. Can SQL Servers linked to other servers like Oracle?
SQL Server can be linked to any server provided it has OLE-DB provider from
Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that
Microsoft provides to add it as linked server to SQL Server group.
31. What is BCP? When does it used?
BulkCopy is a tool used to copy huge amount of data from tables and views.
BCP does not copy the structures same as source to destination. BULK INSERT
command helps to import a data file into a database table or view in a
user-specified format.
32. How to implement one-to-one, one-to-many and many-to-many relationships
while designing tables?
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.
33. What is an execution plan? When would you use it? How would you view
the execution plan?
An execution plan is basically a road map that graphically or textually
shows the data retrieval methods chosen by the SQL Server query optimizer for a
stored procedure or ad- hoc query and is a very useful tool for a developer to
understand the performance characteristics of a query or stored procedure since
the plan is the one that SQL Server will place in its cache and use to execute
the stored procedure or query. From within Query Analyzer is an option called
"Show Execution Plan" (located on the Query drop-down menu). If this
option is turned on it will display query execution plan in separate window
when query is ran again.
No comments:
Post a Comment