Ques: 1. What is SQL server agent?
Ans: The SQL Server
agent plays a vital role in day to day tasks of SQL server administrator (DBA).
Server agent's purpose is to implement the tasks easily with the scheduler
engine which allows our jobs to run at scheduled date and time.
Ques: 2. What is a Trigger?
Ans: Triggers are
used to execute a batch of SQL code when insert or update or delete commands
are executed against a table. Triggers are automatically triggered or executed
when the data is modified. It can be executed automatically on insert, delete
and update operations.
Ques: 3. What is the use of SET NOCOUNT ON/OFF
statement?
Ans: By default, NOCOUNT is set to OFF and it returns
number of records got affected whenever the command is getting executed. If the
user doesn't want to display the number of records affected, it can be
explicitly set to ON- (SET NOCOUNT ON).
Ques: 4. What is SQL injection?
Ans: SQL injection
is an attack by malicious users in which malicious code can be inserted into
strings that can be passed to an instance of SQL server for parsing and
execution. All statements have to checked for vulnerabilities as it executes
all syntactically valid queries that it receives.
Ques: 5. What will be the maximum number of index
per table?
Ans: For SQL Server
2008 100 Index can be used as maximum number per table. 1 Clustered Index and
999 Non-clustered indexes per table can be used in SQL Server.
1000 Index can be
used as maximum number per table.
1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.
Ques: 6. What is Filtered Index?
Ans: Filtered Index
is used to filter some portion of rows in a table to improve query performance,
index maintenance and reduces index storage costs. When the index is created
with WHERE clause, then it is called Filtered Index
Ques: 7. List the different index configurations
possible for a table?
Ans: A table can
have one of the following index configurations:
- No indexes
- A clustered index
- A clustered index and many non-clustered indexes
- A non-clustered index
- Many non-clustered indexes
Ques: 8. What is sub query and its properties?
Ans: A sub-query is
a query which can be nested inside a main query like Select, Update, Insert or
Delete statements. This can be used when expression is allowed. Properties of
sub query can be defined as
- A sub query should not have order by clause.
- A sub query should be placed in the right hand side of the comparison operator of the main query.
- A sub query should be enclosed in parenthesis because it needs to be executed first before the main query.
- More than one sub query can be included.
Ques: 9. What is Mirroring?
Ans: Mirroring is a
high availability solution. It is designed to maintain a hot standby server
which is consistent with the primary server in terms of a transaction.
Transaction Log records are sent directly from the principal server to a
secondary server which keeps a secondary server up to date with the principal
server.
Ques: 10. What is an execution plan?
Ans: An execution
plan is a graphical or textual way of showing how the SQL server breaks down a
query to get the required result. It helps a user to determine why queries are
taking more time to execute and based on the investigation user can update
their queries for the maximum result.
In 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 a query execution plan in a
separate window when a query is run again.
Ques: 11. What is a performance monitor?
Ans: Windows
performance monitor is a tool to capture metrics for the entire server. We can
use this tool for capturing events of the SQL server also.
Some useful counters are – Disks, Memory, Processors, Network, etc.
Some useful counters are – Disks, Memory, Processors, Network, etc.
Ques: 12. What is the difference between a Local
and a Global temporary table?
Ans: If defined
inside a compound statement a local temporary table exists only for the
duration of that statement but a global temporary table exists permanently in
the database but its rows disappear when the connection is closed.
Ques: 13. What is the SQL Profiler?
Ans: SQL Profiler
provides a graphical representation of events in an instance of SQL Server for
monitoring and investment purpose. We can capture and save the data for further
analysis. We can put filters as well to captures the specific data we
want.
Ques: 14. What are the properties of the Relational
tables?
Ans: 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.
Ques: 15. What is View?
Ans: A view is a
virtual table that contains data from one or more tables. Views restrict data
access of the table by selecting only required values and make complex queries
easy.
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
Ques: 16. Why is replication required on the SQL
Server?
Ans: Replication is
the mechanism that is used to synchronize the data among the multiple servers
with the help of a replica set.
This is mainly used
to increase the capacity of the reading and to provide an option to its users
to select among various servers to perform the read/write operations.
Ques: 17. What part
does database design have to play in the performance of a SQL Server-based
application?
Ans.
It plays a very major part. When building a new system, or adding to an
existing system, it is crucial that the design is correct. Ensuring that the
correct data is captured and is placed in the appropriate tables, that the
right relationships exist between the tables and that data redundancy is
eliminated is an ultimate goal when considering performance. Planning a design
should be an iterative process, and constantly reviewed as an application is
developed. It is rare, although it should be the point that everyone tries to
achieve, when the initial design and system goals are not altered, no matter
how slightly. Therefore, a designer has to be on top of this and ensure that
the design of the database remains efficient.
Ques: 18. What command is used to create a database
in the SQL Server and how?
Ans: CREATEDATABASE
Command is used to create any database in the SQL Server. Following is the way
to use this command:
CREATEDATABASE Name
of the Database
Example: If the name
of a database is “employee” then create command to create this database that
can be written as CREATEDATABASE employee.
Ques: 19. What is an extended stored procedure? Can
you instantiate a COM object
by using T-SQL?
Ans:
An extended stored procedure is a function within a DLL (written in a
programming language like C, C++ using Open Data Services (ODS) API) that can
be called from T-SQL, just the way we call normal stored procedures using the
EXEC statement.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from
T-SQL by using sp_OACreate stored procedure.
Ques: 20. When should SQL Server-based cursors be
used, and not be used?
Ans: SQL Server cursors are perfect when you
want to work one record at a time, rather than taking all the data from a table
as a single bulk. However, they should be used with care as they can affect
performance, especially when the volume of data increases.
From a beginner’s
viewpoint, I really do feel that cursors should be avoided every time because
if they are badly written, or deal with too much data, they really will impact
a system’s performance. There will be times when it is not possible to avoid
cursors, and I doubt if many systems exist without them. If you do find you
need to use them, try to reduce the number of records to process by using a
temporary table first, and then building the cursor from this. The lower the
number of records to process, the faster the cursor will finish. Always try to
think “out of the envelope”.
No comments:
Post a comment