Wednesday, December 12, 2012

Microsoft technical palcement paper with answers
1 What do ?jobs? and ?alerts? mean in SQL Server ?Jobs: Using SQL Server Agent you can create and schedule jobs that
automate routine administrative tasks. Database administrators create
jobs to perform predictable administrative functions either according to
a schedule or in response of events and conditions. Jobs can be simple
operations containing only a single job step or can be extremely complex
operations containing many job steps. SQL Server Agent is responsible
for management and execution of all jobs. Agent must be running for jobs
to be executed. SQL server 2k supports jobs containing operating system
commands.Alerts: database administrators define alerts to provide event and
performance condition notification and to execute jobs in response to
SQL server events or performance conditions. E.g. when the log is 90%
full an alert can be configured to fire a job that executes a job to
back up and truncate the transaction log.
2.How many groups of roles are supported in SQL Server ? Explain themSQL Server uses roles. Two layers of access exist: access to the SQL
Server and access to a database object within the server. Each can be
configured separately. There are four database roles, namely: _ Public?
Essentially anyone who has enough rights to connect to the database; the
lowest role possible in terms of database permissions. _ db_owner?
Someone who has full rights to this database, including the right to
delete it altogether, create objects, and so on. _ db_data_reader?
Someone who is allowed to read the data without any modifications, and
who cannot create objects. _ db_datawriter? Someone who is allowed to
read and write data, but who cannot create objects. These roles are
contained in every database, including system databases. Every user will
belong to at least one of them
3 .What is T-SQL ?Transact-SQL is a language containing the commands that are used to
administer instances of SQL Server; to create and manage all objects in
an instance of SQL Server; and to insert, retrieve, modify, and delete
data in SQL Server tables. Transact-SQL is an extension of the language
defined in the SQL standards published by the International Organization
for Standardization (ISO) and the American National Standards Institute
(ANSI).A Transact-SQL statement is a set of code that performs some action on
database objects or on data in a database. SQL Server supports three
types of Transact-SQL statements: DDL, DCL, and DML.A DDL statement supports the definition or declaration of database
objects such as databases, tables, and views. Three DDL commands:
create, alter and drop.Data control language is used to control permissions on database objects. The DCL commands are grant and revoke.Data manipulation language is used to select, insert, update, and delete data in the objects defined with DDL
4 What is the lock types supported in SQL Server ?There could be thousands of concurrent users trying to read or modify
the database, sometimes exactly the same data. If not for locking, your
database would quickly lose its integrity. The following basic types of
locks are available with SQL Server: - Shared locks: Enable users to
read data but not to make modifications. - Update locks: Prevent
deadlocking (discussed later in this session). - Exclusive locks: Allow
no sharing; the resource under an exclusive lock is unavailable to any
other transaction or process. - Schema locks: Used when table-data
definition is about to change?for example, when a column is added to or
removed from the table. - Bulk update locks: A special type of lock used
during bulk-copy operations.
5 . What is OLAP ?Online Analytical Processing (OLAP) is by far the most complex and
advanced SQL Server components. Companies are using OLAP more and more
as they try to make sense of their tons of accumulated data. OLAP is
used in the mysterious field called ?dataAnalysis,? The standard database table represents a flat matrix; SQL
Server 2000 Analysis Services use the notion of cubes. The data and
corresponding objects are multidimensional, having more dimensions than
our four-dimensional space-time continuum; the number of dimensions is
limited only by your imagination and hardware capabilities. You must
install SQL Server Analytical Services. SQL Server 2000 Analysis
Services presents the data from these fact and dimension tables as
multidimensional cubes that can be analyzed for trends and other
information that is important for making informed business decisions.
6 . Which are the two authentication modes in SQL Server 2k ?There are two authentication modes in SQL Server 2k: - Windows
authentication: if user is already authenticated on the windows domain
as valid windows user, SQL Server 2k can be requested to trust
authentication by the operating system and allow the user assess to SQL
Server 2k based on these credentials. You call a connection using
windows authentication as a trusted connection.- SQL Server
authentication: if the user accessing either has not been authenticated
on the windows domain or wants to connect using a SQL Server 2k security
account the user can request that SQL Server 2k directly authenticate
the user based on submission of a username and password.
7.Which are the different services in SQL Server ? How do you manage them ?Replication Service: SQL Server 2000 replication enables sites to
maintain multiple copies of data on different computers, in order to
improve overall system performance, while ensuring that all the
different copies are kept synchronized.DTS: By using DTS, you can build data warehouses and data marts in SQL
Server by importing and transferring data from multiple heterogeneous
sources interactively or automatically on a regularly scheduled basis.Analysis Services: Analysis Services provides tools for analyzing the data stored in data warehouses and data marts.Metadata services: SQL Server Meta Data Services provides a way to
store and manage metadata about information systems and applications.
This technology serves as a hub for data and component definitions,
development and deployment models, reusable software components, and
data warehousing descriptions.Reporting services: used to generate reports from the data in the database
8 .What is the lock types supported in SQL Server ?There could be thousands of concurrent users trying to read or modify
the database, sometimes exactly the same data. If not for locking, your
database would quickly lose its integrity. The following basic types of
locks are available with SQL Server: - Shared locks: Enable users to
read data but not to make modifications. - Update locks: Prevent
deadlocking (discussed later in this session). - Exclusive locks: Allow
no sharing; the resource under an exclusive lock is unavailable to any
other transaction or process. - Schema locks: Used when table-data
definition is about to change?for example, when a column is added to or
removed from the table. - Bulk update locks: A special type of lock used
during bulk-copy operations.
9.How many groups of roles are supported in SQL Server ? Explain themSQL Server uses roles. Two layers of access exist: access to the SQL
Server and access to a database object within the server. Each can be
configured separately. There are four database roles, namely: _ Public?
Essentially anyone who has enough rights to connect to the database; the
lowest role possible in terms of database permissions. _ db_owner?
Someone who has full rights to this database, including the right to
delete it altogether, create objects, and so on. _ db_data_reader?
Someone who is allowed to read the data without any modifications, and
who cannot create objects. _ db_datawriter? Someone who is allowed to
read and write data, but who cannot create objects. These roles are
contained in every database, including system databases. Every user will
belong to at least one of them
10.Explain the Physical Structure of a DatabaseThe SQL data is stored in the database. The data is organized into
logical components that are visible to user however data is stored as
files on hard disk. Each SQL server has four system databases: master,
temp, msdb and model and multiple user databases. How many user
databases depend from organization to organization?The fundamental unit of data storage is page. The page size in SQL is
8KB. Every page contains a page header which is 96 bytes and stores
system information such as the type of the page, amount of free space on
the page, and object that owns the page.Extents are another unit which is used to allocate space to pages and indexes. A extent is 8 continuous pages or 64 KB.SQL server 2k has 3 types of data files: Primary data files which is
the starting point of the database and points to other files, secondary
data files comprise of data files other than primary data files and
thirdly the log files to recover database in the event of a disaster.