SQL Question Answer For Beginners - Part E

1. What is a database maintenance plan and why do you need one?
2. Why it is usually a good idea to maintain a script of your database?
3. How do you detach and attach a database?
4. What databases cannot be attached or detached?
5. What is the difference between a linked server and a remote server?

6. What data sources besides SQL Server can you link to SQL Server 2000?
7. How do you start a distributed transaction in SQL Server 2000?
8. What is the two-phase commit procedure?
9. What are the two main legitimate ways to gain access to SQL Server 2000
system information?
10. What is an INFORMATION_SCHEMA?

11. How do you execute a system stored procedure?
12. What are catalog system stored procedures?
13. What is an extended stored procedure in SQL Server 2000?
14. What is the purpose of SQL Server Agent?
15. Where does SQL Server Agent store and pick up information about tasks
scheduled for the system?

16. What is an Operator in SQL Server? ?
17. What is event forwarding?
18. What is one example of a MAPI-compliant server? 19. What is the difference between SQL Server Mail and SQL Server Mail
Agent?
20. What is the security context of SQL Server Mail Agent?

21. How do you send ad hoc e-mail messages?
22. What is the SQL Server Profiler and what can you do with it?
23. How do you view an estimated execution plan for a query?
24. How do you create statistics for a table? Who uses statistics?
25. What would you use as input data for the Index Tuning Wizard?
26. How can you optimize transaction-log performance?
27. What is DBCC?


1. A Database Maintenance Plan is a job scheduled under SQL Server Agent.
It consists of one or more steps such as backup, reclaiming unused space,
updating statistics, checking database integrity, and so on.
2. To document the database structure and possibly restore it in case of a
disaster.
3. By using the appropriate options from the right-click menu of the
Databases node or by using the system stored procedures sp_attach_db
and sp_detach_db.
4. System databases cannot be attached, detached, or copied.
5. Remote servers allow a client application connected to one SQL Server to
run a stored procedure on another without having to connect to it explic-itly.
Linked servers extend this functionality to heterogeneous data
sources, and also support Distributed transactions.

6. Any data source that has an OLE DB provider or ODBC driver for it.
7. Explicitly, by using the BEGIN DISTRIBUTED TRANSACTION keyword, or
implicitly, by using a distributed query or calling a remote stored proce-dure
within the local transaction.
8. The way to ensure the integrity of a distributed transaction process
across multiple servers.
9. By using the system catalog stored procedures or the
INFORMATION_SCHEMA tables.
10. An INFORMATION_SCHEMA is a view created for each user database that
provides access to the metadata for the database objects.

11. System stored procedures have global scope. To execute a system stored
procedure on a local server you do not need to specify the fully qualified
path or be in the context of any particular database.
12. System stored procedures that provide information about SQL Server
database objects.
13. A compiled dynamic link library that SQL Server can call to perform tasks
that are difficult or impossible to implement in Transact-SQL.
14. To define, schedule for execution, and execute various database tasks.
15. MSDB contains all the information about scheduled tasks.

16. An operator is a contact to which an alert will send notification via
e-mail or pager.
17. Configuring SQL Server to send events to a central server in a multi-server
environment to be processed there.
18. Most of the mail servers are MAPI compliant. The samples would be:
Microsoft Exchange, Sun Internet Mail Server, Mdaemon Mail Server and
dozens more. 19. You use SQL Server Mail to execute stored procedures remotely and return
results by e-mail; you use SQL Server Agent Mail to send e-mail and pager
notifications.
20. SQL Server Mail runs in the security context of MSSQLService.

21. Using extended stored procedures.
22. SQL Server Profiler is a graphical tool you can use to trace the perfor-mance
of SQL Server in your server and to help analyze the results to find
potential bottlenecks and problems.
23. From the Query Analyzer toolbar menu Query, select Display Estimated
Execution Plan.
24. Statistics information is created automatically whenever an index is cre-ated
for a table, but you can also create statistics using the CREATE STA-TISTICS
statement. Statistics are used by SQL Server Query Optimizer to
determine the best path of execution for a particular query.
25. Workload data created with SQL Server Profiler.

26. By setting a large initial size and big increments, so resources are not
wasted when you expand the log.
27. DBCC is a set of SQL Server commands for performing consistency checks
and various database-management tasks pertaining to the integrity of the
database.

No comments: