sql server by BNvM0fd

VIEWS: 8 PAGES: 81

									                                            SQL SERVER

        SQL Server is a Database Management System (DBMS) created by Microsoft. In early
days, File Management System is used for storing data permanently on secondary storage device
and retrieves it back whenever necessary. But because of some drawbacks in file management
system, a new data management system was developed, called as DBMS.


File Management System

In File Management System, data is organized in the form of Fields, Records and Files. A field is
meaningful collection of characters, a record is collection of related fields and a file is collection of
similar type of records. As an example you can observe the following table that contains student

details.



                                                                                 Fields

                     Sno            Sname                  Course
                 1            SAI                  C
                 2            KRISHNA              CPP
                 3            RAHUL                ORACLE
                 4            SARMA                DOT NET
                 5            PARDHU               JAVA


                                                                        Records
                                          File

Drawbacks Of File Management System
        The drawbacks of file management system that leads to the development of DBMS are as
follows.
    1. Data Redundancy and Inconsistency : In file management system data is duplicated,
        which is called as redundancy. Because of this duplication memory is wasted and it is
        inconsistent for the user if he/she has to make changes to the duplicated data. This is
        because changing the data at one place will not reflect in all places where it is duplicated
        and user has to make the change at every place where it is duplicated.
    2. Atomicity Problem : “Either all the changes in a transaction must occur nor neither occur”.
        This rule is called atomicity rule and this rule is violated by the file management system,




                                                       1
       which is called as atomicity problem. For example, if the transaction is to transefer1000/-
       from Account A to Account B, the two steps in the transaction will be as follows.
           a. Deduct 1000/- from Account A
           b. Add 1000/- to Account B
       If there is a system failure or power failure after deducting amount from Account A and

       before adding this amount to Account B, then amount is deducted from account A but is not
       added to account B, which violates atomicity rule. According to atomicity rule, If second step
       is cancelled then first step must also be cancelled other wise second step must also be
       performed.
   3. Concurrent Access Anomolies : The problems that occur when more than one user try to
       access the same data in the same file are called as concurrent access anomalies. For

       example within a bank there is a joint Account A, whose balance amount is 5000/-. Two
       customers of the joint account are withdrawing amount from the Account A, at the same
       time from two different branches of the bank. One is withdrawing 500/- and another is

       withdrawing 1000/-. As both are withdrawing at the same time, balance amount is read as
       5000 by both the computers. First computer will deduct 500/- and makes the remaining

       balance as 4500/-. Second computer will deduct 1000/- and will make the remaining
       balance as 4000/-. During the process of update only one can update data at a time. Hence
       finally balance will be shown either as 4500 or 4000 instead of the actual amount 3500/-.
   4. Security Problem : within file management system, if user got the write permission, he can
       insert new data, update existing data and he can also delete data from the file and it is not
       possible to restrict the user from updating or deleting data while allowing to insert new data.


Database Management System (DBMS)
       DBMS is based on databases. A database is defined as collection of inter-related data.

DBMS is defined as collection of databases and a set of programs to work with databases. SQL
Server is called as DBMS as it contains databases and a set of programs to work with databases.
DBMS overcomes all the drawbacks of file management system. After inventing DBMS, several
models are proposed stating various ways of representing data within the database and their

advantages, called as data models. These data models are classified into three categories based
on their purpose as follows.


   1. Physical Data Models
   2. Conceptual Data Models
   3. Logical Data Models




                                                   2
Physical Data Models : The data models that concentrate on how the data is physically stored
within the database are called as physical data models.


Conceptual Data Models : The data models that concentrate on how the data and relationship
between data can be represented diagrammatically are called as conceptual data models. One of
the most popular conceptual model is Entity-Relationship Model(E-R Model).
        E-R model uses the terminology Entity, Attribute and Relationship.          An entity is a real
world object stored within the database. If you store student information in the database, then
student is called an entity, if you store employee information within the database, then employee is
called an entity and so on. An attribute is a characteristic feature of the entity. For student entity sid,
sname, course, address all these are called as attributes. A relationship is an association between
entities. Relationship between entities are classified into one to one(1:1), one to many(1:M) and
many to many(M:N).
        Within E-R diagrams an entity is represented with a rectangle, an attribute with oval shape
and relationship with diamond shape. The E-R diagram for the entities Student and Course is as

follows. The relationship type between course and student is one to many. This is because one

student can join only one course and a course can be chosen by any number of students.


                Sname                                                Cname
SID                                                  CID
                                 Course                                               Duration




               Student           M                         1         Course
                                           Joins




Logical Data Models : The data models that concentrate on what data is stored in the database
and what type of relationship exist between data are called as logical data models. The following
lists of logical data models are available.
        1. Hierarchical Data Model
        2. Network Data Model
        3. Relational Data Model
        4. Object Data Model
        5. Object Relational Data Model




                                                      3
Hierarchical Data Model : in this data model data is organized hierarchically like in tree data
structure. Within hierarchical database model it is not possible to represent many to many
relationships. This drawback leads to the development of network data model. Student information
can be represented in hierarchical data model as follows.


                                             College




                      CID 1001           CID 1002                  CID 1003               Courses




          SID 10001              SID 10002              SID 10010             SID 10011             Students



Network Data Model : in this data model also data is represented same as in case of
hierarchical data model except that it can represent many to many relationship. Student information
is represented in network data model as follows.

                                         College




                  CID 1001              CID 1002               CID 1003                   Courses


                                                                                                      Students
                                                                                                        With
         SID 10001           SID 10002                 SID 10010          SID 10011                 Many To Many
                                                                                                    RelationShip
                                                                                                    With Course



       In both hierarchical and network data models, organization of data is very difficult as it is
organized like a tree data structure. Especially deleting a record will be very complex process as all
records that are based on deleted record must be rearranged. These drawbacks lead to the
development of relational data model.




                                                                    4
Relational Data Model : Relational data model was proposed by E.F.Codd in 1970. He
proposed the model theoretically and first practical implementation of relational model was
System-R and is developed by IBM Corporation. In relational data model data is organized in the
form of attributes, tuples and relations. Attribute, tuple and relation can be compared to a field,
record and file in file management system. These are technical terms given by E.F. Codd and now
a days these terms are replaced with column, row and table respectively.

                                                                                     Attributes
                                                                                         or
                          Sno           Sname                  Course                Columns
                      1           SAI                    C
                      2           KRISHNA                CPP
                      3           RAHUL                  ORACLE
                      4           SARMA                  DOT NET
                      5           PARDHU                 JAVA



                                        Relation or Table               Tuples or Rows

        As organization of data in relational data model is similar to file management system, it is
very easy to maintain when compared to hierarchical and network data models. Now a days most
of the DBMS are RDBMS. Examples are SQL Server, Oracle,DB2, MS Access etc.,


Object Data Model : This data model is based on object oriented concepts of programming. This
model provides OOP features within the database. This model also becomes popular as it has

advantages of OOP. Examples are Object Store, Cactus etc.,


Object Relational Data Model : this model combines the features of object data model with the
features of relational data model. Hence this model has both the advantages of relational data

model as well as object data model. Example is Oracle 8.0 onwards.




                                                     5
                                      Relational Data Model

        Relational data model is proposed by E.F.Codd in 1970. First practical implementation of
relational model is System-R developed by IBM Corporation. Later several DBMS are developed
based on relational model and are called as relational database management system (RDBMS).
SQL Server developed by Microsoft Corporation, Oracle developed by Oracle Corporation and
DB2 developed by IBM Corporation are some examples of RDBMS.
        In relational data model data is organized in the form of attributes, tuples and relations.
The attribute, tuple and relation can be compared to a field, record and file in file management
system. These are technical terms given by E.F. Codd and now a days these terms are replaced
with column, row and table respectively.



                                                                                      Attributes
                                                                                          or
                          Sno            Sname                 Course                 Columns
                      1            SAI                   C
                      2            KRISHNA               CPP
                                   RAHUL                 ORACLE
       NULL
                      4            SARMA                 DOT NET
                      5            PARDHU                JAVA



                                         Relation or Table               Tuples or Rows



        Relational data model provides various constraints to maintain data integrity within the
database. These constraints are called as integrity constraints. Integrity constraints supported by
Relational data model are as follows.


1. Not Null : if we insert a row into a table without providing value for a column, then the value in
that column is called NULL value. In the above student table third row is inserted without giving a
value for sno column. Hence this row contains NULL for sno column. If you want to restrict the user
from inserting a NULL value in to a column, then you can specify Not Null constraint on that
column.




                                                     6
2. Unique : when you don’t want to allow duplicate values in to a column, then you can specify
unique constraint on that column. For example, if you take student table, sno must be unique i.e.
sno given for one student must not be given to another student. To implement this condition on sno
column, you have to specify unique constraint on sno column.


3. Primary Key : when you specify primary key constraint on a column, then null values as well
as duplicate values are not allowed into that column. A table must contain an attribute as primary
key attribute to identify each row of the table uniquely.
        i) Candidate Key : An attribute of the table that will uniquely identify every tuple of the table
is called as candidate key. A table can contain more than one candidate key. Among all available
candidate keys, the candidate key used by the user to identify the tuples uniquely is called as

primary key. For example, if we take the Department table that contains Deptno, Dname and
Location Columns, both Deptno and Dname Columns are candidate keys. But as we use
Deptno to uniquely identify the rows of department table, Deptno is called as primary key.
        ii) Secondary Key : An attribute that is used to uniquely identify the rows of the table when
primary key fails is called as secondary key. For example, when we have to retrieve marks of a
student, we will provide student’s hall ticket no. Here hall ticket no is primary key. If user lost his
hall ticket no., then we can use student name and his father name combination to retrieve marks.
Hence combination of these two columns is called as secondary key.


4. Foreign Key : this constraint is used to create relationship between two tables. When you
specify foreign key constraint on a column, you must specify a primary key column as reference
to it. As a foreign key refers to a primary key, it is also called as referential integrity constraint. A
foreign key constraint will restrict the user from inserting a value that doesn’t exist in the referenced
primary key column.




                                                       7
                                              Normalization
        The process of minimizing data redundancy through a series of steps called normal forms is
called as normalization. Total normalization process includes 8 normal forms.
    1. First Normal Form (1NF)
    2. Second Normal Form (2NF)
    3. Third Normal Form (3NF)
    4. Boyce-Codd Normal Form (BCNF)
    5. Fourth Normal Form (4NF)
    6. Fifth Normal Form (5NF)
    7. Domain/Key Normal Form(DKNF)
    8. Sixth Normal Form(6NF)


First Normal Form (1NF) : A relation is said to be in first normal form if and only if a
primary key is defined for the relation and all non-key attributes are dependent on key
attributes. The attributes that are part of primary key are called as key attributes and all remaining
attributes are called as non-key attributes. As an example observe the following relation that
contains information regarding various projects and employees working in those projects.


 PID       Proj. Name         EmpID Ename                  Job          Charge/Hour Hours Worked
P101     College              1001        A          Sr.                500                3
         Maintenance                                 Programmer
P101     College              1007        K          DBA                750                2
         Maintenance
P101     College              1003        M          Jr.                300                5
         Maintenance                                 Programmer
P102     Hospital             1001        A          Sr.                500                5
         Maintenance                                 Programmer
P102     Hospital             1005        S          DBA                750                3
         Maintenance
P102     Hospital             1003        M          Jr.                300                5
         Maintenance                                 Programmer
P102     Hospital             1009        R          Jr.                300                4
         Maintenance                                 Programmer




                                                     8
        In order to convert the above table to 1NF, a primary key must be defined for the table and
all non-key attributes must be dependent on key attributes. In this table, it is not possible to define
primary key on a single column as every column can contain duplicate values. Hence we can
define primary key on the combination of PID and EMPID Columns. Now by giving the values of
PID and EMPID columns, we can retrieve the values of every other column in the table. Hence the
table is said to be in 1NF. Finally Table is represented as follows after defining a primary key on
PID and EMPID columns.


(PID,EMPID)           Pname, Ename, Job, Charge/Hour, Hours Worked


Second Normal Form (2NF) : A relation is said to be in 2NF if and only if the relation is in
first normal form and no partial dependencies exist in the relation. If primary key is defined on
more than one attribute and a non-key attribute is completely dependent on only one key attribute
then that dependency is called as partial dependency. The above table after conversion to 1NF,
contains PID and EMPID combination as primary key. But to get PNAME there is no need to
provide EMPID. Hence PNAME is partially dependent on key attributes. In the same way to get

ENAME, JOB, CHARGE/HOUR and HOURS WORKED, there is no need to provide PID. Hence

these attributes are also partially dependent on key attributes. The above relation is not in 2NF.
        To convert the above relation to 2NF, we have to divide the table as follows.


1. (PID)       Pname
2. (EMPID)         Ename, Job, Charge/Hour
3. (PID,EMPID)          Hours Worked


        Now all three relations are said to be in 2NF, as they doesn’t contain partial dependencies.


Third Normal Form (3NF) : A relation is said to be in 3NF, if and only if the relation is in
2NF and no transient dependencies exist in the relation. If a non-key attribute depends on
another non-key attribute, then that dependency is called as transient dependency. In the above
three tables that obtained by converting the table of 1NF to 2NF, second table contains
CHARGE/HOUR column, which was actually dependent on JOB but not on EMPID. To convert this
table, which was not in 3NF to 3NF, we have to divide the table as follows.


1. (PID)       Pname
2. (EMPID)         Ename, Job
3. (JOB)        Charge/Hour



                                                     9
3. (PID,EMPID)         Hours Worked


       Now all the four tables are in 3NF, as they don’t contain transient dependencies.


       As the normalization steps proceed to higher normal forms, no. of tables in the database will
increase. When no. of tables increases, the database performance will be reduced as we have to
combine the data from more than one table. This is the reason why database designers are
restricted to 3NF.


De-normalization : Converting the tables from their current normal form to their previous normal
form is called as de-normalization.




                                                 10
                           INTRODUCTION TO SQL SERVER

               SQL Server is a client-server Relational Database Management System developed
by Microsoft Corporation. A client-server RDBMS is an RDBMS that can be shared. SQL Server is
the RDBMS chosen as back end by most of the developers on Windows operating system. This is
because
   1. It is tightly integrated with Windows
   2. It is easy to use as it provides wizards to perform various tasks like object creation,
       database tuning, backup etc.,
   3. It scales from mobile laptop to symmetric multi-processor systems.
   4. It provides business intelligence concepts, which are until now available only in oracle and
       other expensive DBMS’s.
   5. It is a bundle of at least four products Relational Database Engine, Analysis Services,
       Reporting Services and Integration Services. Relational Database Engine is used to work
       with database and database objects, analysis service is used to work with business
       intelligence concepts like data warehousing and data mining. Reporting Services is used to
       create and deploy enterprise reports and Integration Services is used to integrate data from
       different types of data sources to SQL Server.


History Of SQL Server


       SQL Server was developed and implemented by Sybase Corporation. In 1988, Microsoft
licensed SQL Server for OS/2 operating system and began developing it for windows in early 1990.
At almost the same time, further development of SQL Server for OS/2 has been cancelled. In 1994,
Microsoft cancels the agreement with Sybase and starts developing SQL Server for windows.
Since then several versions of SQL Server are released and the new version in the market is SQL
Server 2005.


New Features in SQL Server 2005


   1. SQL Server 2000 provides Enterprise Manager to work with wizards and Query Analyzer
       to work with SQL queries. In SQL Server 2005, these two programs are combined into a
       single program called Management Studio.
   2. In today's connected world, data and the systems that manage that data must always be
       secure yet available to your users. With SQL Server 2005, users and information




                                                  11
   technology (IT) professionals across your organization will benefit from reduced application
   downtime, increased scalability and performance, and tight yet flexible security controls.
3. SQL Server 2005 includes key enhancements to enterprise data management in
   Manageability, Availability, Scalability and Security areas.
4. SQL Server 2005 makes it simpler and easier to deploy, manage, and optimize enterprise
   data and analytical applications.
5. SQL Server 2005 simplifies management by providing one integrated management console
   to monitor and manage the SQL Server relational database, as well as Integration Services,
   Analysis Services, Reporting Services, Notification Services, and SQL Server Mobile
   Edition across large numbers of distributed servers and databases.
6. Investments in high-availability technologies, additional backup and restore capabilities, and
   replication enhancements will enable enterprises to build and deploy highly available
   applications. Innovative high-availability features such as database mirroring; failover
   clustering, database snapshots, and enhanced online operations will minimize downtime

   and help to ensure that critical enterprise systems remain accessible.
7. Database mirroring allows continuous streaming of the transaction log from a source server
   to a single destination server. In the event of a failure of the primary system, applications
   can immediately reconnect to the database on the secondary server. The secondary
   instance detects failure of the primary server within seconds and accepts database
   connections immediately.
8. SQL Server 2005 introduces a dedicated administrator connection to access a running
   server even if the server is not responding or is otherwise unavailable. This allows you to
   execute diagnostic functions or Transact-SQL statements so you can troubleshoot problems
   on a server. The connection is activated by members of the sysadmin fixed server role and
   is only available through the SQLCMD command prompt tool either locally or from a remote
   computer.
9. Scalability advancements such as table partitioning, snapshot isolation, and 64-bit support
   will enable you to build and deploy your most demanding applications using SQL Server
   2005. The partitioning of large tables and indexes significantly enhances query performance
   against very large databases.
10. SQL Server 2005 makes significant enhancements to the security model of the database

   platform, with the intention of providing more precise and flexible control to enable tighter

   security of the data.




                                                12
Management Studio

       To work with SQL server 2005 databases and database objects, we have to use
management studio. Management Studio provides the following windows.


Registered Servers : this window displays all registered SQL Server servers on the current
system. By using this window, you can also register a new SQ Server. To register a new SQL

Server, right click on registered servers, then choose “register new server”, and then provide

information required to register a server. Short cut to open registered servers window is Ctrl + Alt +
G


Object Explorer : this window will display all the databases, database objects, users and logins
available in the selected server within registered servers window as a hierarchy. Whenever you
want to work with wizards provided by management studio, you can use object explorer. Short cut
to open object explorer window is F8


Summary : this window will display all the objects available within the selected category in object
explorer. You can work with the objects within summary window by double clicking on them or by
right clicking on them and then choosing the corresponding option. Short cut to open summary

window is F7


Query Editor : this window is used to write SQL queries and execute them. Whenever you want to
work with SQL Server using SQL commands, you can use query editor. Type the query within

query editor and to execute it, use the shortcut F5. When there are multiple statements within query
editor and you want to execute only one then select the statement you want to execute and then
press F5. To open query editor click on New Query button in the toolbar.


Connecting To Server

       Whenever you open management studio immediately “connect to server” dialog box will
displayed where you have to provide the following information to connect to a database server in
the network


Server Type : used to specify what type of server you want to connect to within SQL server.
Various options available are Relational Database Engine, Analysis Services, Reporting
Services and Integration Services.


                                                   13
Server Name : used to specify the name of the database server within the network to which you
want to connect to. If the database is within the local system, by default the local system name will
be given as server name.
Authentication Mode : SQL Server supports two types of authentication modes; Windows
Authentication and SQL Server Authentication.
       In windows authentication mode, the user name and password you specify to login to
windows operating system will be treat as login and password to login to SQL Server as well and in
this mode there is no need to provide additional login and password to login to SQL Server. In this
mode, SQL Server will not provide additional security for the data.
       In SQL Server authentication mode, in addition to user name and password you provide to
login to windows, you have to provide login and password to login to SQL Server. In this mode,
SQL Server will provide additional security for the data in addition to security provided by windows
Operating System.


Login And Password : if the mode is selected as SQL Server authentication mode, then valid
login and password must be specified to login to SQL Server.


       Whenever you try to register a new server using registered servers window, then also you
have to provide the same information to register the server on the client system.



TSQL (Transact SQL)
               SQL (Structured Query Language) is the common language for any RDBMS to work
with the database. In SQL Server the SQL used to work with SQL Server database is called as

TSQL. Actually SQL was developed by IBM corporation for their Relational project System – R as
part of it. But later it was developed as a separate language and was given a name SEQUEL
(Structured English Query Language). SEQUEL was renamed as SQL and was first commercially
released by ORACLE Corporation. Features of SQL are as follows.
    1. SQL is a non-procedural or fourth generation language (4GL).
    2. SQL is not case sensitive
    3. In SQL we can break a single statement into multiple lines without using any continuation
       character.
               Commands of SQL are classified as follows.
    1. Data Definition Language (DDL) : The commands of SQL that are used to create
       database objects, alter the structure of the database objects and delete database objects




                                                   14
         from database are collectively called as DDL. Examples include Create, Alter and Drop
         Commands.
    2. Data Manipulation Language (DML) : The commands of SQL that are used to insert data
         into the database, modify the data of the database and to delete data from the database are
         collectively called as DML. Examples include Insert, Update and Delete.
    3. Data Query Language (DQL) : The commands of SQL that are used to retrieve data from
         the database are collectively called as DQL and example is Select.
    4. Data Control Language (DCL) : The commands of SQL that are used to control the
         access to data stored in the database are collectively called as DCL and examples include
         Grant and Revoke.
    5. Transaction Control Language (TCL) : The commands of SQL that are used to control
         the transactions made against the database are collectively called as TCL and examples
         include Commit, Rollback and Savepoint.


Database
         The primary object to create when you want to work with SQL Server is Database. A single
SQL Server can maintain 32,767 databases. Within SQL Server, a database is maintained with the
help of operating system files. An SQL Server database uses two types of files, data file and Log
file.

       Data files have the internal data the engine needs, along with any user defined data you
         place in the database. The database engine has a default data file, and you may add
         additional data files for storage or organizational reasons. A primary data file will have the
         extension .mdf and additional data files will have the extension .ndf.
       Log files are used to store any modifications made to data, and is heavily used in the
         process of committing and rolling back changes in the database.     A log file will have the

         extension .ldf.

Creating A Database


Syntax 1 : Create Database DBName

         With this syntax SQL Server will create a database with specified name and will
automatically create a data file and a log file for the database with default specifications.


Example : Create Database MyDatabase

Syntax 2 :


                                                     15
Create Database DBName

On

(Name = Logical Name Of the Data File,

FileName = ‘Physical Path and Name Of the Data File’,


Size = initial memory to be allocated for the Data file,

MaxSize = maximum allowed size for the Data file,


Filegrowth = increment to the memory of the Data file

)

Log on


(Name = Logical Name Of the Log File,

FileName = ‘Physical Path and Name Of the Log File’,


Size = initial memory to be allocated for the Log file,

MaxSize = maximum allowed size for the Log file,

Filegrowth = increment to the memory of the Log file


)

         Here on option is used to specify information related to Data File and log on option is used
to specify the information related to Log File. Name option specifies the logical name of the file

used by the SQL Server, FileName option is used specify the physical path and name of the file,
Size option is used specify the initial memory to be allocated to the file. By default SQL Server will
take it in MB and to change it you can explicitly specify KB or any other measurement of memory.
MaxSize option specifies the maximum allowed size for the file. It can be specified as unlimited to
not to restrict the file growth. FileGrowth option is used to specify how much increment to be done
the file whenever allocated memory is completely utilized by the file.

Example :




                                                    16
Create Database MyDatabase

On


(Name = MyDatabase_Dat,

FileName = ‘D:\MyDatabase.Mdf’,


Size = 5,

MaxSize = 100,

FileGrowth = 5


)

Log on


(Name = MyDatabase_log,

FileName = ‘D:\MyDatabase.Ldf’,

Size = 5,


MaxSize = 20,

FileGrowth = 1


)

Syntax 3 : Create Database DBName as snapshot of Existing DBName

         This syntax is used to create a copy of an existing database as a new database and this
option is available only in SQL Server professional edition and is not available in Standard edition.


Altering The Structure Of The Database

         After creating the database you may have to create additional data files or log files to the
database or delete a data file or log file or make modifications to file specification etc., for this

purpose SQL server provides Alter Database command that has the following syntaxes.




                                                      17
1. Alter Database DBName Add File (Data File Specification)

       This syntax is used to add an additional data file to an existing database.


2. Alter Database DBName Add Log File (Log File Specification)

       This syntax is used to add an additional log file to an existing database.


3. Alter Database DBName Remove File Logical Name Of The File

       This syntax is used to delete a data file or log file from an existing database.


4. Alter Database DBName Modify Name = NewDBName


       This syntax is used to change the name of the database i.e. renaming the database.


5. Alter Database DBName Modify File (Name = LogicalName , Size=n , MaxSize=n ,
FileGrowth = n)


       This syntax is used to change the file specification for an existing file of the database.


Note : To get the information about an existing database use the system procedure
“SP_HELPDB”.      This will display information about the data files and log files created for
the database along with database information. Syntax will be as follows.
                                       SP_HELPDB ‘DBNAme’


Deleting A Database
       To delete a database from SQL server use Drop Database command. Syntax is as follows.
               Drop Database DBName


Tables Or Relations
       Whenever you want to store data into the SQL server database, first you have to create a
table within the database and to create a table you have to specify columns required for the table
along with the type of data you want to store in the table and constraints you want to check during
the insertion or updating of the data in the table.




                                                   18
Data Types In SQL Server


    Category           Data Type             Bytes                  Rannge
 Integer         Tiny int          1                  0 To 255
                 Small int         2                  -32768 To 32767
                 Int               4                  -2147483648 To 2147483647
                 Big int           8                  -263 To 263 – 1
 Float           Decimal(P,S)      5 to 17            Decimal(7,2) will allow a total of 7
                                                      digits where 2 digits will be after
                                                      the decimal point. (ex : 12345.67)
                 Float(P)          If P<25 4 Bytes
                                   If P>=25 8 Bytes
                 Small Money       4 Byte Decimal
                 Money             8 Byte Decimal
 Character       Char (n)                             Fixed Length Char Data and n can
                                                      be up to maximum 8000
                 Varchar(n)                           Variable Length Char Data And n
                                                      Can be up to maximum 8000
                 NChar(n)          a Unicode character Fixed Length Unicode char data
                                   will occupy 2 bytes in and n can be up to maximum
                                   memory             4000.
                 Nvarchar(n)                          Variable Length Unicode char
                                                      data and n can be up to maximum
                                                      4000.
                 Text(n)           n can be up to     Variable length char Data
                                   maximum 2GB
                 Ntext(n)          N can be up to     Variable length Unicode char data
                                   maximum 2 – 1 30




 Binary          Binary(n)         N can be up to     Fixed length binary data
                                   maximum 8000
                 VarBinary(n)      N can be up to     Variable Length Binary Data
                                   maximum 8000
                 Image(n)          N can be up to     Variable Length Binary data
                                   maximum 2 – 1 31




                 Bit               1                  True/False/NULL



                                              19
 Large Objects        Varchar(Max)         231 – 1                    Variable Length Char Data
                      NVarchar(Max)        2 –131
                                                                      Variable Length Unicode Char
                                                                      Data
                      VarBinary(Max)       2 –131
                                                                      Variable Length Binary Data
 DateTime             SmallDatetime        2                          Jan 1st 1900 to june 6th 2079
                      DateTime             4                          Jan 1st 1753 to Dec 31st 9999
 Miscellaneous        SQL_variant          Size varies                Can Store any Type of Data
                      Table                Size varies                Can Store a Table
                      XML                  Size Varies                Can store XML documents


Constraints In SQL Server
        A constraint is a condition given on a column that is checked during insertion and updating
data of the table to maintain data integrity within the database. Constraints in SQL Server are

classified into Declarative Integrity Constraints and Procedural Integrity Constraints.
        Procedural integrity constraints are the constraints that are not possible to specify during
the table creation and are specified using triggers. Declarative integrity constraints are the
constraints that can be specified during the table creation. Declarative integrity constraints
supported by SQL Server are as follows. Declarative integrity constraints can be specified either at
column level or table level. A constraint that is specified along with the column definition is called as
column level constraint and a constraint that is specified at the end of the table creation is called as
table level constraint. In general table level constraints are used when you have to specify the
constraint on more than one column. But you can specify a constraint that span on a single column
either at column level or table level.


1. Not Null : This constraint is used to restrict the user from inserting null values in to a column. It
is not possible to specify the not null constraint at table level.


Syntax : ColumnName DataType Not Null


2. Default : This constraint is used to specify a default value for a column. When user insert a row
into the table without providing a value for the column having default value then default value will
be inserted in to that column. Default can be specified only at column level.


Syntax : Columnname Datatype [constraint cname] default defautvalue




                                                      20
3. Unique : This constraint is used to restrict the user from inserting duplicate values into a column.
When we create a unique constraint on a column then SQL Server will automatically create an
index on that column. An index can be clustered or non-clustered. By default SQL Server will
create a non-clustered index on unique constraint.


Syntax :
Column Level
        ColumnName DataType [Constraint Cname] Unique [Clustered / NonClustered]
Table Level
        [Constraint Cname] Unique(Col1,Col2,…,Coln)


4. Primary Key : A column in the table that uniquely identifies every row of the table is called as
primary key. To uniquely identify every row of the table, the primary key column must not contain
either null values or duplicate values. When we specify primary key constraint on a column, then
user will be restricted from inserting null values as well as duplicate values. On primary key column
also SQL Server will create an index, which is by default a clustered index.


Syntax :
Column Level
        ColumnName DataType [Constraint Cname] Primary Key [Clustered / NonClustered]
Table Level
        [Constraint Cname] Primary Key(Col1,Col2,…,Coln)


5. Foreign Key : This constraint is used to create relationship between two tables. While creating
the foreign key constraint, we have to give a primary key or unique column as reference to foreign
key. Now the foreign key will restrict the user from inserting a value that doesn’t exist in the
referenced column.
        While specifying the foreign key you can specify “on update” and “on delete” options that
will specify the action to be taken on foreign key values when modification was done to primary key
column or a primary key row was deleted. This “on update” or “on delete” option will have four

options No Action, Cascade, Set Null, and Set Default.


        Default option for “on update” as well as “on delete” is no action. When these options are
set to no action, it is not possible to update or delete primary key value without first updating or
deleting the corresponding foreign key value.




                                                    21
        When these options are set to Cascade, then any modification made to primary key value
will be automatically made to foreign key value and if a primary key row was deleted then
automatically all its dependent foreign key rows will be deleted.
        When these options are set to Set Null, then any modification made to primary key value or
deletion of a primary key row will automatically set all its dependent foreign key values to null.
        When these options are set to Set Default, any modification made to primary key value or
deletion of a primary key row will automatically set all its dependent foreign key values to default. If
the default value is not specified for the foreign key column, then foreign key value will be set to
null.


Syntax :
Column Level
        ColumnName Datatype [Constraint Cname] References tablename(columnname)
Table Level
        [Constraint          Cname]           foreign          key(Col1,Col2,…,Coln)             References
Tablename(Col1,Col2,…Coln)


6. Check : This constraint is used to specify a custom condition on a column. When a check
constraint is specified at column level, it is not possible to refer to other columns of the table within
the condition of the check constraint. When you have to refer to other columns of the table within
the condition of the check constraint, then you have to specify the check constraint at table level.


Syntax :
Column Level
        ColumnName Datatype [Constraint Cname] check(condition)
Table Level
        [Constraint Cname] check(Condition)


Identity Column
Within a table when you want to automatically generate values for a column then you can create
that column as identity column by using identity option that has the following syntax.


                ColName DataType [Constraint] identity(seed,increment)




                                                     22
Creating A Table
       A table can be created using the create table command that has the following syntax.


Create table TableName
(Column1 Datatype [Constraints Information],
Column2 Datatype [Constraints Information],
.
.
.
Columnn Datatype [Constraints Information]
)


       The following example will create a table with the name student with SID, SNAME and
COURSE columns.


Create table Student
(SID Smallint constraint SID_PK primary key identity(1001,1),
SNAME varchar(20) not null,
COURSE varchar(25) not null
)
       The same table can be created by specifying the primary key at table level as follows.


Create table Student
(SID Smallint,
SNAME varchar(20) not null,
COURSE varchar(25) not null,
Constraint SID_PK primary key(SID)
)


       The following example will create a table called MARKS with a foreign key that refers to
STUDENT table.


Create table MARKS
(SID Smallint Constraint SID_FK references STUDENT(SID),
C Tinyint Constraint C_CHK Check(C<=100) default 0,
CPP Tinyint Constraint CPP_CHK Check(CPP<=100) default 0,
SQL Tinyint Constraint SQL_CHK Check(SQL<=100) default 0,


                                                  23
TOTAL Smallint,
AVEG Decimal(5,2),
GRADE varchar(30)
)


         The same table can be created by specifying constraints at table level as follows.



Create table MARKS
(SID Smallint,
C Tinyint default 0,
CPP Tinyint default 0,
SQL Tinyint default 0,
TOTAL Smallint,
AVEG Decimal(5,2),
GRADE varchar(30),
Constraint SID_FK Foreign Key(SID) references STUDENT(SID),
Constraint C_CHK Check(C<=100),
Constraint CPP_CHK Check(CPP<=100),
Constraint SQL_CHK Check(SQL<=100)
)


Note :
1. To get the list of tables available in the current database you are using, use the following select
statement.
         Select * from Sysobjects where type=’u’
Here ‘u’ represents the user defined tables. SQL Server also contains system tables. To get the list
of system tables, specify ‘s’


2. It is optional to give a name to a constraint when you create it. When didn’t specify a name for
the constraint then SQL Server will automatically give a name to the constraint. You can get the

details of all constraints available on a table by using the system procedure sp_helpconstraint.
         Sp_HelpConstraint ‘TableName’


3. To get a list of all available columns in a table, their data types and constraints information, use
the system procedure Sp_help that has the following syntax.
         Sp_help ‘tablename’


                                                     24
Altering the Table Structure
You can use Alter Table command to alter the structure of a table, like adding a new column,
changing data type of a column, add a constraint , deleting a column or constraint etc.,

Adding New Column


Alter Table TableName Add Col1 Datatype [Constraints],Col2 Datatype [Constraints],…
Changing Data Type of a Column


Alter Table TableName Alter Column Col1 Datatype [null/not null],Col2 DataType [null/not
null],….


This syntax can be used to change the data type of a column or add and remove not null constraint
on a column.


Adding Constraint to a Column


Alter Table TableName [with check/nocheck] Add [Constraint Constr.Name] Constraint


This Syntax can be used to add constraints like default, unique, primary key, check and foreign key


Adding Default Constraint


Alter Table TableName Add [Constraint Constr.Name] Default Def.value for ColName


Enable or Disable a Constraint


Alter Table TableName Check/NoCheck Constraint Constr.Name

Deleting a Column

Alter Table TableName Drop Column ColName

Deleting a Constraint


Alter Table TableName Drop Constraint Constr.Name


                                                 25
Renaming A Table


Sp_Rename ‘OldName’,’NewName’


Renaming A Column


Sp_Rename ‘TableName.OldName’,’NewName’


Data Manipulation Language (DML)
       The commands of SQL that are used to insert data into the database, modify the contents
of the database and to delete the data from the database are collectively called as DML. SQL

Server provides Insert command for inserting data into the database, Update command to make
modifications to the data and Delete command to delete data from the database.


Inserting Data Into A Table
       To insert data into a table SQL Server provides insert command and it has the following
syntaxes.


1. Insert into tablename values(val1,val2,val3,…,valn)
               When you are using this syntax, you must specify a value for every column of the

table. Even for a column which allows null values you must specify the value as null to insert null
value into that column. If you want to insert the default value for a column then use the keyword

default.


Ex 1 : To insert a row into student table, the insert statement will be written as follows.
       Insert into student values(1001,’SAI’,’SQL’)
Ex 2 : To insert a row into Marks table, the insert statement will be written as follows. In this
example I don’t want to give values for total, average and grade columns. Hence I will specify null
as the value for those columns.
       Insert into Marks Values(1001,55,77,66,null,null,null)


2. Insert into tablename(Col1,Col2,Col3,…,Coln) values(Val1,Val2,Val3,…,Valn)
       By using this syntax you can insert a row into a table without giving values for specified
columns. But the columns you exclude must not contain not null constraint. Within the column list
after the table name specify the names of columns for which you are providing values and then
give values for those columns within the values list.



                                                   26
Ex 1: to insert a row into marks table without giving values for total, average, and grade columns,
the insert statement will be written as follows.
         Insert into Marks(Sid,c,cpp,sql) values(1001,55,66,77)


3. Insert into tablename select statement
         This syntax is used when we have to insert rows from an existing table in to a new table,
provided no. of columns and their data types match in those two tables.
         Select * into Tablename From Tablename Syntax is used create a new table from existing
table.


Updating Data In The Table


         When you want to make modifications to the data available in the table, you have to use
update command. Update command has the following syntax.


Update tablename set column1=val1,column2=val2,….,column=valn [where condition]


         Specifying where clause is optional and when you didn’t specify the where clause then the
modification will be made to every row in the table.


Ex 1: To update the course of a student with sid 1002 to SQL, the update command will be as
follows.
         Update Student set course=’SQL’ where sid=1002


Ex 2: To update rows in marks table by calculating total, average columns, the update statement
will be as follows.
         Update Marks Set Total = C+Cpp+Sql, Average = (C+Cpp+Sql)/3


Ex 3: To update marks table by calculating grade, the update statement will be as follows. SQL
server provides case statement to check for a condition and then execute the statements within
update command and this update statement will use case to update grade based on average
marks of the student.
         Update Marks set Grade = case
                                       When Average>=90 then ‘DISTINCTION’
                                       When Average>=70 then ‘FIRST CLASS’
                                       When Average>=55 then ‘SECOND CLASS’


                                                   27
                                      When Average>=35 then ‘THIRD CLASS’
                                      Else ‘FAIL’
                                   End
Note : SQL Server supports calculated columns. Calculated columns are the columns for which
values are calculated automatically based on expressions given for those columns during the table
creation. For example to create marks table with total and average as calculated columns, the

create table statement will be as follows.
       Create table Marks
       (SID smallint constraint SID_Fk references Student(SID),
       C Tinyint,
       Cpp Tinyint,
       SQL Tinyint,
       Total as C+Cpp+SQL,
       Average as (C+Cpp+SQL)/3,
       Grade varchar(30)
       )


Deleting Data From The Table
       When you want to delete a row or rows from the table then you can use delete command.
Delete command has the following syntax.


       Delete [From] tableName [where Condition]


       Here also specifying where clause is optional and when you didn’t specify the where clause,
every row in the table will be deleted.


Ex 1: To delete a row from student table, whose SID is 1002, the delete statement will be as
follows.
       Delete Student Where SID=1002
Ex 2: To delete all rows from marks table, the delete statement will be as follows.
       Delete Marks


Truncate Command
       For deleting rows from a table you can also use truncate command. Differences between
delete and truncate commands are as follows.




                                                  28
                      Delete                                            Truncate
 You can delete only specified rows using It is not possible to delete specified rows from
 where clause                                       the table as this command doesn’t support
                                                    where clause and this command is used only
                                                    when you want to delete all rows from a table.
 Deletes rows one by one                            Deletes rows page by page. Hence deletion will
                                                    be fast.
 Deleted rows can be rollback.                      Deleted rows can’t be rollback.


Syntax : Trucate table TableName


Retreiving Data
          A statement that requests data from the database is called as a query. In SQL select
statement is used to retrieve data from the database and hence the select statement is called as a
query. Select statement has the following syntaxes.
1. Select * From TableName
          This syntax is used to retrieve every row from the table with information from every column
of the table. Here * represents “all columns”.
Examples:
          i. Display The Details of all employees in the company
                  Select * From emp
          ii. Display the details of all students
                  Select * from Student
2. Select Column1,column2,column3,… From TableName
          This syntax is used to retrieve the information from only specified columns of the table from
every row of the table.
Examples:
          i. Display Employee no,name,job and salary for every empoloyee
                  select empno,ename,job,sal from emp
          ii. Display sid and sname from student
                  select sid,sname from student
3. Select */ColumnList From TableName Where Condition
          This syntax is used retrieve only specified rows from table. The given condition will be
verified with every row and only the rows that satisfy the given condition will be displayed in the
output.
Examples:


                                                       29
        i. Display the details of employees working in department 30
                 Select * from emp where deptno=30
        ii. Display the details of employees working as manager
                 Select * from emp where job=’manager’
        iii. Find employees whose salary is more than 2000 and less than 3000
                 Select * from emp where Sal>2000 and sal<3000
4. Select distinct ColumnList From TableName [Where Condition]
        Distinct is used to eliminate duplicate values in the output of a select statement. Distinct can
be used only once in a select statement and it must be immediately after select key word. When
you specify a single column in the selection list after distinct then duplicates will be eliminated in

that column. When you specify multiple columns in the selection list after distinct then duplicates

will be eliminated in the combination of values of specified columns.
Examples :
        i. Find Department nos in which employees are working
                 Select Deptno from Emp
        This query will display Department nos 10,20 and 30 with duplicates. To display the result
without duplicates we have to write the query as
                 Select Distinct Deptno from emp
5. Select [Distinct] */ColumnList From TableName [Where Condition] Order By ColumnName
Asc/Desc
        Order By clause is used in select statement to arrange the rows in the out put in either
ascending or descending order of specified columns In the order by clause. In order by clause you
can specify more than one column and when you specify more than one column, first the rows are
arranged in specified order of first column in order by clause and then if more than one row has

same value in that column then those rows will be arranged in specified order of second column in
order by clause and so on.
Examples:
        i. Display all employees in the order of highest salary to lowest salary
                 Select * from emp order by sal desc
        ii. Display all employees in alphabetical order of their name
                 Select * from emp order by ename
6. Select ColumnName AliasName, ColumnName AliasName,… From TableName
        Within the select statement you can give alias names for the columns so that column names
will be changed in the output of the select statement. When alias name is more than one word then
it is compulsory to include it in double quotes. Generally alias names are used for the expressions
like “sal *12”
Examples:


                                                      30
       i. Display empno,name,job,salary and annual salary for every employee
               Select empno,ename,job,sal,sal*12 as annualsal from emp


Predicates
       SQL supports various key words and operators that can be used in where clause of select
statement and these key words and operators are called as predicates. Among the predicates
supported by SQL, Four predicates are as follows.
   1. Between … And : This predicate is used to verify whether the given value exists in given
       range of values. This predicate can be used with numeric type of data as well as date type
       of data.
       Examples:
               i. Find all employees whose salary is more than 2000 and less than 3000
                       Select * from emp where sal between 2000 and 3000
               ii. Find all employees who are joined in the company in the year 1982
                       Select * from emp where sal between ‘1/1/1982’ and ‘12/31/1982’
   2. In : This predicate is used to verify whether the given value exists in a given list of values.
       This can be used with numeric, character and date type of data.
       Examples:
               i. Find all employees whose salary is either 800 or 1250 or 1600
                       Select * from emp where sal in(800,1250,1600)
               ii. Find all employees who are working as either manager or clerk or analyst
                       Select * from emp where job in (‘manager’,’clerk’,’analyst’)

   3. Like : this predicate is used to verify for a specific pattern in character columns. This is
       used only with character type of data. With the like predicate you have to use two wild
       characters % and _ (Underscore).
       Examples:
               i. Find all employees whose name starts with ‘S’
                       Select * From emp Where Ename like ‘S%’
               ii. Find all employees whose name ends with ‘E’
                       Select * from emp where ename like ‘%E’
   4. Is Null : This predicate is used to verify for a null value in a given column.
       Examples:
               i. Find all employees who are not drawing commission
                       Select * from emp where comm Is null


Built In Functions



                                                      31
       As in other languages like C and C++, SQL also provides Built in functions. Built in functions
in SQL are classified into following categories.
       1. Scalar Functions
       2. Aggregate Functions
       3. Ranking Functions


Scalar Functions : The built in functions in SQL Server that work on a single value are called as
scalar functions. Scalar functions are again classified into following categories based on data type
on which they work.
       a) Numeric Functions
       b) String Functions
       c) Date Functions
       d) Conversion Functions
       e) System Functions


Numeric Functions : The scalar functions of SQL that work on numeric type of data are called
as numeric functions. Numeric functions in SQL are as follows.


    1. Abs(n) : Returns absolute value of the given n i.e. n value without any sign.
    2. Cos(n) : Returns cosine value of given n in radians.
    3. Ceiling(n) : Returns smallest integer greater than or equal to n.
    4. Floor(n) : Returns Largest integer less than or equal to n.
    5. Radians(n) : Converts the given n in degrees to radians.
    6. Degrees(n) : Converts the given n in radians to degrees.
    7. Exp(n) : Returns e raised to the power of n. e is exponential whose value is constant and is
       2.713
    8. Log(n) : Returns natural logarithm of n. i.e. base e logarithm.
    9. Log10(n) : Returns base 10 logarithm of n.
    10. Power(m,n) : Returns m raised to the power of n.
    11. Square(n) : Returns Square of given n.
    12. Sqrt(n) : Returns Square Root of given n.
    13. Sin(n) : Returns sine value of given n in radians.
    14. Tan(n) : Returns tangent value of given n in radians.
    15. Sign(n) : Return -1 if n is negative, 1 if n is positive and 0 if n is zero.
    16. Pi() : Return mathematical constant pi value i.e. 3.14




                                                   32
   17. Round(n,p,t) : Rounds the given in n to p decimal places. If t is specified as 1 then it
       truncates n to p decimal places instead of round the value.


String Functions : The built-in functions of SQL that work on string type of data are called as
string functions. String functions in SQL are as follows.
   1. ASCII(char) : Converts the specified character to the equivalent ascii code.
   2. Char(int) : Converts ascii code to the equivalent character.
   3. CharIndex(s1,s2) : returns the starting position where the partial string s1 first occurs in the
       string s2. Returns zero if s1 does not exist in s2.
   4. Lower(S1) : Converts all uppercase letters of the string s1 to lower case letters.
   5. Upper(s1) : Converts all lowercase letters of the string s1 to upper case letters.
   6. Ltrim(s1) : removes leading blank spaces in the string s1.
   7. Rtrim(S1) : Removes trailing blank spaces in the string s1.
   8. Reverse(S1) : Reverses the string S1.
   9. Right(S1,Len) : returns last Len characters from the string S1.
   10. Left(S1,Len) : returns first len characters from the string S1.
   11. Soundex(a) : Returns how the given string is pronounced in a four character soundex code.
   12. Stuff(s1,a,len,s2) : Replaces the partial string s1 with the partial string s2 starting at
       position a, replacing len no. of characters.
   13. Substring(S1,a,len) : creates a partial string from string s1 starting at the position a with a
       length of len.


Date Functions : The built-in functions of SQL that work on date type of data are called as date
functions and date functions in SQL server are as follows.
   1. GetDate() : Returns the current system date and time.
   2. DatePart(item,D) : returns the specified item of a date D as an integer.
   3. DateName(item,D) : returns the specified item of the date D as a string.
   4. DateDiff(item,D1,D2) : calculates the difference between the two dates        D1 and D2 and
       returns the result as an integer in units specified by the item.
   5. DateAdd(item,n,D) : Adds the number n no. of units specified by the item to the given date
       D and returns a date.


Conversion Functions : The Built-In functions of SQL Server that are used to convert one type
of data to another are called as conversion functions and conversion functions in SQL Server are
as follows.
   1. Cast(a as type[(length)]) : converts an expression a into the specified data type type.



                                                     33
   2. Convert(type[(length}],a) : Equivalent to cast, but the arguments are specified differently.


System Functions : The SQL Server system functions provide extensive information about
database objects.
   1. Object_ID(objname) : returns the identifier of the database object objname.
   2. Object_name(obj_id) : returns the name of the database object with id obj_id.
   3. Col_Name(Obj_Id,S_Id) : returns the name of the column belonging to the object obj_id
       with the identifier s_id.
   4. Col_Length(obj,Col) : returns the length of the column belonging to a database object obj.
   5. Datalength(s1) : calculates the length of the result of the expression s1.
   6. DB_Id(Db_name) : returns the id of the database db_name.
   7. DB_Name(Db_id) : returns the name of the database with id Db_id.
   8. User_Id(username) : returns the id of specified user name.
   9. user_name(uid) : returns the name of the user with id uid.
   10. Current_TimeStamp : returns the current date and time.


Aggregate Functions : The Built-In Functions of SQL Server that operate on a set of values are
called as aggregate functions. Aggregate functions are again classified into following categories.
   1. Convenient Aggregate Functions
   2. Statistical Aggregate Functions
   3. Super Aggregates


1. Convenient Aggregate Functions :


          o   Sum(Col) : Returns the sum of all values in the specified column.
          o   Avg(Col) : Returns average of all values in the given column.
          o   Max(Col) : Returns the highest value among all values in the given column.
          o   Min(Col) : Returns the smallest value among all values in the given column.
          o   Count(*)/Count_Big(*) : Returns the total no. of rows in the given table.
          o   Count(Col)/Count_Big(Col) : Returns the no. of non-null values in the given
              column.


2. Statistical Aggregate Functions :


          o   Var(Col) : Returns Variance of all values in the given column.
          o   Varp(Col) : Returns Variance of population of all values in the given column.



                                                 34
            o   Stdev(Col) : Returns standard deviation of all values in the given column.
            o   Stdevp(Col) : Returns Standard Deviation of population of all values in the given
                column.


Group By : Group By is used to group the rows of table based on values of a given column and
applies the aggregate function on each group separately.
Examples:
        i. calculate sum of salaries of employees department wise
                 Select Deptno,Sum(Sal) from emp group by Deptno
In the above query group by will divide the table in to groups based on deptno and then calculates
sum(sal) for each group separately, which will give the sum of salaries of employees department

wise.
        ii. Calculate sum of salaries of employees job wise
                 Select Job,Sum(sal) from emp group by job
Rule : A rule to be followed when working with group by is the columns that are selected in
the select list except aggregate functions must be in group by.


Having : Having is used to check for a condition that contains an aggregate function instead of
where clause. Because where clause can check a condition without any aggregate function but not
a condition with aggregate function. For example if you want to find the departments with three or
more employees then the query will be as follows.


            Select Deptno,Count(*) From Emp group by Deptno having Count(*)>=3
In the above query condition is Count(*)>=3. This contains an aggregate function. Hence we must
use having instead of where clause.
Rule : A Rule to follow when using having clause is a corresponding group by is must for a
having clause.


Super Aggregates : SQL Server provides Two super aggregates Cube and Rollup.


Roll Up : Roll up is used to calculate sub totals at the end of each group based on values of first
column in group by when you group the data on more than one column.
Cube : Cube is used to calculate sub totals at the end of each group based on values of second
column in group by along with the result of rollup when data is grouped on more than one column in
group by.




                                                    35
        Roll up and Cube can be used only with group by. For example if you want to calculate sub
totals at the end of each department when grouping data on both Deptno and Job, then the
following query is used.


    Select Deptno, Job, Sum(Sal) from emp group by Deptno,Job with roll up / with cube


COMPUTE Clause : The compute clause uses aggregate functions to calculate summary values
that appear as additional rows in the result of a query. The aggregate functions used with the
compute clause are referred to as row aggregate functions.
                        The compute clause has an optional BY portion. BY defines the grouping

form of the result. If BY is omitted, the row aggregate function is applied to all rows of a result

query. The ORDER BY clause is required if the COMPUTE clause with BY is used.
Example :
        Display Employee Details Along with Total salary department wise
                Select * from Emp order by Deptno Compute Sum(sal) by Deptno


OVER Clause : OVER Clause can be used to group the data like GROUP BY. The main difference
between the use of the GROUP BY clause and grouping using the OVER clause is that the OVER
clause displays each of the rows from a group separately, while the GROUP BY clause displays

only one row for each group.
Example :
        Display Employee Details Along with Total salary department wise
                Select *,Sum(Sal) over(partition by deptno) from emp


RANKING FUNCTIONS
        SQL Server 2005 defines several functions that are categorized as ranking functions, i.e.,
functions that return a ranking value for each row in a partition group. The system supports
following ranking functions.
1. RANK() : This function is used to return a number that specifies the rank of the row among al
rows. The RANK function uses logical aggregation. In other words, if two or more rows in a result
set have same value in the ordering column, they will have the same rank.
Example :
        Display employee details along with a rank based on salary
                Select empno,ename,job,sal,rank() over(order by sal desc) as Rank from emp


2. DENSE_RANK() : This function works same as Rank(), except that it will not skip a rank when
two or more rows got the same rank.


                                                     36
Example :
        Display employee details along with a rank based on salary
        Select empno,ename,job,sal,Dense_rank() over(order by sal desc) as Rank from emp


2. ROW_NUMBER() : It returns the sequential number of a row within a result set, starting at 1 for
the first row.


PARTITION BY : Ranking functions can be applied to partitions by using partition by clause within
the OVER() clause along with ORDER BY.
Example :
        Display Employee details along with a rank department wise
Select empno,ename,job,sal,Dense_rank() over(partition by Deptno order by Sal Desc) as Rank
                                                                                                from emp


TOP n Clause : This clause specifies the first n rows of the query result that are to be retrieved.
The TOP n clause can also be used with the additional PERCENT option. In that case, the first n
percent of the rows are retrieved from the result set. The additional option WITH TIES specifies that
additional rows will be retrieved from the query result if they have the same value in the ORDER
BY column as the last row that belongs to the displayed set.
Example :
        i. Find Top3 salaried employees
                 Select Top 3 empno,ename,job,sal from emp order by sal desc
        ii. find Top 10% salaried employees
                 Select Top 10 Percent Empno,ename,job,Sal from emp order by sal desc


SET OPERATORS
                 SQL Server supports the following three set operators that connect two or more
queries.
                 1. UNION
                 2. INTERSECT
                 3. EXCEPT


    1. UNION : The result of the UNION of the two sets is the set of all elements appearing in
        either or both of the sets by eliminating duplicate values. If UNION ALL is used, all resulting
        rows, including duplicates, are to be displayed.
        Example :



                                                    37
                Find jobs available in departmenents 10,20
                         Select Job From Emp Where Deptno=10
                         Union
                         Select Job From Emp Where Deptno=20


    2. INTERSECT : The intersection of two tables is the set of rows belonging to both tables.
        Example :
                Find jobs available Both in departmenents 10 and 20
                         Select Job From Emp Where Deptno=10
                         Intersect
                         Select Job From Emp Where Deptno=20


    3. EXCEPT : This finds the difference of two tables. The difference of two tables is the set of
        all rows, where the resulting rows belong to the first table but not to the second one.
        Example :
                Find jobs available in departmenent 10 And not in 20
                         Select Job From Emp Where Deptno=10
                         Except
                         Select Job From Emp Where Deptno=20


JOINING
        The process of combining data from more than one table using a single select statement is
called as joining. Within a relational database data will not be stored in a single table. Hence join is
needed whenever we have to combine data from more than one table. Joining in SQL Server is

classified as follows.


    1. EQUI JOIN : Joining more than one table by using a join condition that uses = operator is
        called as equi join.
        Example :
        Display Employee Details along with department details in which employee is
        working.
                Select * From Emp E join Dept D on E.Deptno=D.Deptno


    2. NATURAL JOIN : The equi join that displays the common column of the two tables only
        once is called as natural join.
        Example :



                                                     38
   Display Employee Details along with department details in which employee is
   working.
           Select E.*,Dname,Loc From Emp E join Dept D on E.Deptno=D.Deptno


3. OUTER JOIN : The join statement that displays the data that doesn’t satisfy the given join
   condition along with the data that satisfies the condition is called as outer join. Outer join is
   again three types.
       a. Left Outer Join : the outer join that displays rows from LHS table that does not
           contain a corresponding row in RHS table is called as Left Outer Join.
       b. Right Outer Join : the outer join that displays rows from RHS table that does not
           contain a corresponding row in LHS table is called as Right Outer Join.
       c. Full Outer Join : The outer join that displays rows from both the tables that doesn’t
           contain a corresponding row in the opposite table is called as full outer join.
   Example :
   Display Employee Details along with department details in which employee is
   working. Also display the details of departments in which there are no employees.
           Select * From Emp E right outer join Dept D on E.Deptno=D.Deptno


4. THETA JOIN : The join operation that uses a condition that contains an operator other than
   = is called as theta join.


   Example :
   Display Employee Details along with Grade of employee based on salary
           Select *,grade From Emp join salgrade on Sal between losal and hisal


5. CROSS JOIN : The join statement without any join condition is called as cross join. The
   result of this cross join is called as Cartesian product.
   Example :
   Display Employee Details by associating every employee with every department
           Select * From Emp E Cross join Dept D


6. SELF JOIN : The join statement that joins a table to it self is called as self join.
   Example :
   Display Employee Details along with his/Her manager name
           Select E.Empno,E.ename,E.Job,E.Sal,E.Mgr,M.Ename
           From Emp E join Emp M on
           E.Mgr=M.Empno


                                                39
Rule : A rule to be followed when writing a join statement is no. of join conditions must be one less
than no. of tables on which you perform join.


SUB QUERIES
       A statement that requests data from the database is called as a query. Select is used to

request data from the database and hence select statement is called as a query. A select
statement within another select statement is called as a sub query. Sub queries are classified as
follows.
   1. Single Row Sub Queries
   2. Multi Row Sub Queries
   3. Nested Sub Queries
   4. Co-related sub queries


   1. SINGLE ROW SUB QUERIES : The sub query that returns only one row is called as single
       row sub query.
       Example :
               i. Find The Employees Working In Sales Department.
                       Select * From Emp where Deptno=
                              (Select Deptno From Dept Where Dname=’Sales’)
               ii. Find The Employee Who is drawing highest salary.
                       Select * from emp where sal=
                              (Select max(sal) from emp)


   2. MULTI ROW SUB QUERIES : The Sub query that returns more than one row is called as
       multi row sub query. When a sub query returns more than one row then it is not possible
       use the operators like =,<,>,<=, and >=. To make this possible SQL Server provides two

       operators, Any and All. “Any” operators returns true when the given condition is true with
       at least one value returned by the sub query and “All” operator will return true only when
       the condition is true with every value returned by the sub query.
       Example :
               i. Find highest salaried employees department wise
                       Select * from emp where sal in
                              (Select max(sal) from emp group by deptno)
               ii. Find Employees not working in dept 30 and drawing salary more than any
               one employee in dept 30.


                                                  40
                       Select * from Emp where Deptno!=30 and sal> Any
                               (Select Distinct sal from emp where deptno=30)
               iii. Find Employees not working in dept 30 and drawing salary more than all
               employees in dept 30.
                       Select * from Emp where Deptno!=30 and sal> All
                               (Select Distinct sal from emp where deptno=30)


   3. NESTED SUB QUERIES : The sub query that contains another sub query within it is called
       as nested sub query.
       Example :
               Find Employees whose salary is more than average salary of sales Dept.
                       Select * from emp where sal >
                               (Select Avg(sal) from emp where deptno=
                                       (Select Deptno from Dept where Dname=’Sales’))


   4. CO-RELATED SUB QUERIES : The sub query that refers a column of the outer query
       within its condition is called as co-related sub query. SQL Server provides two operators
       that can be used only with co-related sub queries, Exists and NotExists. “Exists” returns
       true only when the co-related sub query returns at least one row and “NotExists” will return
       true only when the co-related sub query doesn’t return any rows.


       Example :
               i. Find Employees whose salary is more than average salary of the dept in
               which he is working.
                       Select * from Emp E where Sal >
                               (Select Avg(sal) from Emp S where E.Deptno=S.Deptno)
               ii. Find Employees To Whom At least one employee is Reporting.
                       Select * From emp M where exists
                               (Select * From Emp e where M.Empno=E.Mgr)
               iii. Find Departments in which there are no employees
                       Select * From Dept D where not exists
                               (Select * From emp Where D.Deptno=E.Deptno)


                                             INDEXES
       An index in SQL Server is similar to an index in a book. We can find a topic in a book very
fast with the help of an index. In the same way if an index is available in SQL Server, then data




                                                   41
retrieval will be very fast. Hence the main purpose of an index is to speed up the data retrieval. An
index is maintained by SQL server in the form of a B-Tree data structure. To create an index, the
following syntax is used.


    Create [Unique] [Clustered/NonClustered] index idxname on tablename(Col1,Col2,…)


       When we use Unique keyword         during the creation of index, then along with the index
unique constraint will also be created on that column.

       When an index is a clustered index, then the physical order of rows in table will be in the
same order of rows in index and when an index is an unclustered index, then the physical order of
rows in the table will not be in the order of rows in the index.

       When an index is created on a single column, then the index is called as single column

index and when the index is created on more than one column, then the index is called as a multi
column index or composite index.
Example :
       i. Create an index on deptno column of emp table.
               Create Index DnoIDX on Emp(Deptno Asc)
       ii. Create An Index On Deptno And Job Columns Of Emp Table.
               Create Index DnoJobIDX on Emp(Job Asc,Deptno Desc)


       SQL Server will use index only in the following situations
   1. The column on which index was created is referred in where clause.
   2. If the index is created on more than one column, all the columns on which index was
       created must be referred in where clause.


   Altering An Index
       After creating index you can alter the index using alter index command that has the
   following syntax.
   Syntax : Alter Index IdxName on TableName Rebuild/Reorganize/Disable
       Rebuild will rebuild the entire index.
       Reorganize will reorganize the leaf level nodes of Index
       Disable will disable the index and to enable the index use rebuild option.


   Deleting an Index
       To Delete an index use drop index command as follows.



                                                   42
                 Drop Index IdxName


                                                VIEWS

        A view is a logical object that doesn’t contain data directly. A view is not an independent
object and is created from a table or another view. The main purpose of a view is as follows.


1. To restrict the access to specific rows and or columns in the table.
2. To restrict the insertion and updation to only specified range of values.
3. To hide the complexity of a complex query like join or subquery.


        Once a view is created you can use it like a table and you can perform any DML operation
on that view as you perform on table. But any modification made to the view will be done in the

table from which the view was created and whenever you select data from the view, the select

statement written for the view will be executed and data is retrieved from the base table of the view.


Creating A View :


         Create view viewname(Col1,Col2,…) as select statement [with check option]


        Example :
                 Create a view that will provide access to only dept 10 rows from emp table
                        Create View Emp10 As Select * From emp Where Deptno=10


        If we create a view without with check option clause, then insertion and updation can be
done for the rows that doesn’t satisfy the condition specified in where clause of select statement of
the view and if a view is created with with check option clause then insertion and updation of rows
in the view is not possible if the row fails to satisfy the condition specified in where clause of the
select statement of the view.


Complex View


        A view is called as a complex view if it satisfies any of the following conditions.
    1. If the view is created from a select statement that combines the data from more than one
        table.
    2. If the select statement of the view contains distinct or group by.



                                                    43
    3. If the select statement of the view contains an expression like Sal*12 as a column.
    4. if the Select statement contains Aggregate functions.


    Performing DML operations is not possible on complex view. But user can select data from the
    complex view.
    Example :
       Create a view that will contain data of employees along with department details in
    which employee is working.
       Create View EmpDept as Select E.*,Dname,Loc from emp join dept
                Where e.deptno=d.deptno


Altering a View
       A view can be altered by using Alter view command when you want to change the definition
of the view.
Syntax : Alter View ViewName[(Col1,Col2,…)] As Select Statement [With Check Option]


Deleting A View
       Drop view viewname




                                  TRANSACTION PROCESSING


       A series of changes made to one or more tables of the database is called as a transaction.
When we make changes to the database by creating a transaction, any changes made to the
database will not be made permanent to the database and user will have the choice to cancel those
changes or make those changes permanent. For transaction processing SQL Server provides the
following commands.


1. Begin Transaction : This command will start a transaction.
2. Commit Transaction : This command will make changes in the current transaction permanent
and ends the transaction.
3. Rollback Transaction : This command will cancel the changes in the current transaction and
ends the transaction.
4. Save Transaction : This command will create a save point that will divide the transaction in to
more than one part so that user can commit one part while rollback the other part. When user



                                                    44
rollback the transaction to a save point, all the changes made to the database prior to the save
point will be committed while all the changes after the save point are rollback.


       During the transaction processing, SQL Server will use log files to record the changes and
uses the information in log file to commit or rollback the changes in current transaction. If a
transaction was started and there is system failure before the transaction was committed or
rollback, then all the changes in the current transaction will be automatically rollback. Whenever a
change was made to the database in a transaction, before image of the table and after image of the
table are written to the log file. In case transaction was committed, after image will be used to make
the changes in current transaction permanent to the database other wise before image will be used
to cancel the changes made to the database.




                                                   45
                              PROCEDURAL STATEMENTS

       SQL is a non-procedural language and hence it is very easy to work with SQL. But solving
some complex queries is not possible is not possible using SQL. Hence procedural features are

provided for SQL, which is called as PL/SQL.


Block : A set of statements written within Begin and End statements is called as a block.
       Begin
               -----
               -----
               -----
       End


Print : Print command is used to print any output to the out put window.
       Print ‘Out put To Display To The User’


Declaration : to declare variables within a block use Declare key word and a variable in SQL
Server must be prefixed with @
       Declare @Var1 Datatype, @Var2 Datatype,…


Setting Value : To set value for a variable, use set statement.
       Set @Var=Value


IF
       If condition
       Begin
               Statements
       End


       If Condition
       Begin
               Statements
       End
       Else if Condition
       Begin
               Statements



                                                 46
       End
       Else if condition
       Begin
               Statements
       End
       Else
       Begin
               Statements
       End


WHILE
       While Condition
       Begin
               Statements
       End


Examples :
i. This examples adds two numbers and prints the sum
       Begin
               Declare @A int,@B int, @S int
               Set @A=10
               Set @B=20
               Set @S=@A+@B
               Print ‘Sum is ‘ + cast(@S as varchar(5))
       End
ii. This example will determine whether an integer is even or odd
       Begin
               Declare @N int
               Set @N=5
               If @N%2=0
                      Print ‘Even Number’
               Else
                      Print ‘Odd Number’
       End




                                               47
iii. This Example will print integers from 1 to 10
        Begin
                Declare @N int
                Set @N=1
                While @N<=10
                Begin
                        Print @N
                        Set @N=@N+1
                End
        End


STORED PROCEDURES


        SQL Server supports the creation of user defined functions as well as procedures, which
are called as stored Subprograms. An user defined function is a stored subprogram that will return
a value back to the calling location. To create a function, the following syntax is used.


Create Function FunctionName(@Param1 Datatype, @Param2 Datatype, …) Returns
Datatype as
Begin
        Statements
        Return Value
End


        To execute an user defined function, you can use select statement as follows


                Select Dbo.Functionname(Arg1,Arg2,…)


Example :
        i. Create a Function that will calculate Sum of three integers
                Create Function Total(@A int,@B int, @C int) returns int as
                Begin
                        Return @A+@B+@C
                End
        ii. Create A Function That Will Generate New Deptno Automatically.




                                                  48
               Create Function NewDno() returns int as
               Begin
                       Declare @NDno int
                       Set @NDno=(Select isnull(max(deptno),0)+10 from dept)
                       Return @NDno
               End


        To Delete a function, use Drop Function Command as follows.


               Drop Function FunctionName


        A procedure is a stored subprogram that will not return a value back to the calling location
and syntax for creating a procedure is as follows.


Create Procedure ProcedureName(@param1 Datatype, @Param2 Datatype,…) as
Begin
        Statements
End


        To execute a procedure, use execute command as follows.


               Execute ProcName Arg1 , Arg2, Arg3, …


Example :
        i. Create A Procedure that will insert a row into Dept table.
               Create Procedure InsertDept(@Dno int,@Dn varchar(30),@L varchar(30)) as
               Begin
                       Insert into Dept values(@Dno,@Dn,@L)
               End
        ii. Create A Procedure To Update A Row Of Dept Table.
               Create Procedure UpdateDept(@Dno int, @Dn varchar(30),@l varchar(30)) as
               Begin
                       Update Dept Set Dname=@Dn,Loc=@L where Deptno=@Dno
               End
        iii. Create A Procedure To Delete A Row From Dept table.
               Create procedure DelDept(@Dno int) As
                       Delete Dept Where Deptno=@Dno


                                                 49
       To Delete a procedure, use Drop Procedure command as follows.


               Drop Procedure Procedurename


Default Arguments
       A procedure can be created with default arguments. A default argument is an argument that
is initialized within the procedure creation. When an argument is created as default argument, it is
optional to pass a value to that argument during function call and if you don’t pass a value then

default value will be used other wise the value you pass will overwrite the default value. Advantage
is a procedure can be called with variable number of arguments. Functions doesn’t support default
arguments.
Example :
       This example will create a procedure that can be called with 2 or 3 or 4 or 5 integers
to calculate Sum.
       Create procedure MySum(@A int,@B int, @C int=0,@D int=0,@E int=0) As
       Begin
               Print @A+@B+@C+@D+@E
       End
       Execute MySum 10,20
       Execute MySum 10,20,30
       Execute MySUm 10,20,30,40
       Execute mySum 10,20,30,40,50


Output Parameters
       In SQL Server Parameters of functions and procedures are classified into input parameters
and output parameters. By default every parameter is an input parameter. Any changes made to

input parameters within the function or procedure will not reflect in arguments passed to them. But
any changes made to output parameters will reflect in arguments.
Example : This example will create a procedure Swap that will swap two integers.
       Create procedure Swap(@X int output, @Y int output) As
       Begin
               Declate @T int
               Set @T=@X
               Set @X=@Y
               Set @Y=@T
       End



                                                    50
       To execute this procedure we have to create a block as follows.
       Begin
               Declare @A int,@B int
               Set @A=10
               Set @B=20
               Print ‘Before Swap A = ‘ + cast(@A as varchar(3)) + ‘ B = ‘ + cast(@B as varchar(3))
               Swap @A output,@B output
               Print ‘After Swap A = ‘ + cast(@A as varchar(3)) + ‘ B = ‘ + cast(@B as varchar(3))
       End


Cursors

       Cursors are used within a stored procedure when you want to access the rows of a table in
a sequence. Actually we can access only one row at a time within a stored procedure. But with the
help of cursors we can access the rows of a table one by one sequentially. To work with cursors we
have to follow the following steps.


   1. Cursor Declaration : first we have to declare the cursor. During the declaration a name
       was given to the cursor and a select statement is associated with the cursor.


               Declare Cursorname Cursor for select statement


   2. Opening Cursor : Before using the cursor, cursor must be opened. When we open the
       cursor, the select statement associated with the cursor will be executed and the returned
       rows will be stored in buffer and cursor will point to this buffer.


               Open CursorName


   3. Fetching Rows : Retrieving a row in the buffer pointed by cursor into local variables is
       called as fetching. As it is not possible to make changes to the data within the buffer, first
       they must be copied to the local variables. You can verify whether fetch was success or not
       by using the system variable @@FETCH_STATUS. If fetch was success this variable will
       contain “0” other wise a negative value.


       Fetch Next / Prior / First / Last / Absolute n / Relative n from Cursorname To Local
       variables list



                                                    51
   4. Closing Cursor : After the work with cursor was finished, cursor must be closed. When the
       cursor was closed, it is not possible to fetch the rows. But you can reopen the cursor to
       fetch rows.


               Close CursorName


   5. Deallocating Cursor : Finally you can deallocate cursor so that memory resources
       associated with the cursor will be released.
               Deallocate Cursorname

Example : This example will Read one by one record from Emp table and increments salary
based on job
       Create procedure Increment as
       Begin
               Declare @Eno int,@J varchar(30),@S decimal(7,2)
               Declare MyCur Cursor for select empno,job,sal from emp
               Open MyCur
               Fetch next from mycur into @Eno,@J,@S
               While @@Fetch_Status=0
               Begin
                       If @J=’President’
                              Set @S=@S+5000
                       Else if @J=’Manager’
                              Set @S=@S+3000
                       Else if @J=’Analyst’
                              Set @S=@S+2000
                       Else if @J=’Salesman’
                              Set @S=@S+1500
                       Else
                              Set @S=@S+700
                       Update Emp set Sal=@S where Empno=@Eno
                       Fetch Next From MyCUr into @Eno,@J,@S
               End
               Close Mycur
               Deallocate Mycur
       End


                                                  52
                                               TRIGGERS


        A trigger is like a stored procedure stored within the database. But unlike stored procedure,
the trigger will be executed automatically based on the DML event specified for the trigger during
trigger creation.
        While working with the triggers SQL server will automatically create two virtual tables
inserted and deleted. Inserted table will contain new values of the row and deleted table will
contain old values of the row.


        Constraints within SQL Server are classified into declarative integrity constraints and
procedural integrity constraints. Declarative integrity constraints are the constraints that can be
specified during table creation and procedural integrity constraints are the constraints that are not
possible to specify during table creation and for the specification of procedural integrity constraints
triggers are used. Purpose of trigger is
    1. To Create procedural integrity constraints
    2. To record Audit information of a table.
    3. To Make DML operation possible on complex view


    Trigger creation has the following syntax


        Create trigger trigger name
        On Table/View name
        For/After/Instead of
        Insert / Update / Delete
        As
        Begin
                Statements
        End


Examples :
1. This example will create a trigger that will restrict the user from performing DML
operation on Dept table on Sunday.
        Create trigger Sunday
        On Dept
        For insert,update,delete



                                                    53
       As begin
               Declare @Day varchar(30)
               Set @Day=(Select datename(dw,getdate()))
               If @Day=’sunday’
               Begin
                       Print ‘Transaction Are Not Allowed on Sunday’
                       Rollback Transaction
               End
       End


2. This example will create a trigger on dept table that will record audit information of dept
table in to another table called auditdept.
       Create trigger Audit on Dept for insert,update,delete as begin
               Declare @Odno int,@ODn varchar(30),@OL varchar(30)
               Declare @Dno int,@Dn varchar(30),@L varchar(30)
               Set @Odno=(Select Deptno from Deleted)
               Set @Odn =(select Dname from Deleted)
               Set @OL=(Select Loc From Deleted)
               Set @Dno=(Select Deptno From Inserted)
               Set @Dn=(Select Dname From Inserted)
               Set @L=(Select Loc From Inserted)
               Insert into AuditDept values(@ODno,@ODn,@OL,@Dno,@Dn,@L,Getdate())
       End


Instead of triggers
       When a view is created as complex view, DML operations will not be possible on that view.
Instead of triggers are used to make DMLoperations possible on a complex view.
Example: This example will create trigger on EMPDEPT view, which a complex view created
from emp and dept tables and will make insert possible on this view.
       Create Trigger InsteadInsert on Empdept instead of insert as
       begin
       Insert into emp select empno,ename,job,mgr,hiredate,sal,comm.,deptno from inserted.
       End




                                                 54
Deleting A Trigger


To Delete a trigger use drop trigger command as follows


             Drop trigger TriggerName


To get the list of trigger created on a table use the system procedure SP_HELPTRIGGER


             SP_HELPTRIGGER ‘TableName’


      To get the code written for either a trigger or function or procedure or view, use
SP_HELPTEXT system procedure.


             SP_HELPTEXT ‘procedurename’




                                            55
                           Microsoft SQL Server 2005 Overview
SQL Server Data Platform
   SQL Server is a comprehensive, integrated end-to-end data solution that empowers users across your
   organization by providing them with a secure, reliable, and productive platform for enterprise data and

   business intelligence (BI) applications.   SQL Server 2005 delivers powerful, familiar tools to Information
   Technology professionals as well as to information workers, reducing the complexity of creating, deploying,

   managing, and using enterprise data and analytical applications on platforms ranging from mobile devices to

   enterprise data systems.    Through a comprehensive feature set, interoperability with existing systems, and

   automation of routine tasks, SQL Server 2005 provides a complete data solution for enterprises of all sizes.
   Figure 1 shows the layout of the SQL Server 2005 data platform.




   Figure 1: The SQL Server 2005 Data Platform
   The SQL Server data platform includes the following tools:

      Relational database: Secure, reliable, scalable, highly available relational database engine with
        improved performance and support for structured and unstructured (XML) data.

      Replication Services: Data replication for distributed or mobile data processing applications, high
        systems availability, scalable concurrency with secondary data stores for enterprise reporting solutions,
        and integration with heterogeneous systems, including existing Oracle databases.

      Notification Services: Advanced notification capabilities for the development and deployment of
        scalable applications that can deliver personalized, timely information updates to a variety of connected
        and mobile devices.

      Integration Services: Extract, transform, and load capabilities for data warehousing and enterprise-wide
        data integration.
      Analysis Services: Online analytical processing (OLAP) capabilities for the rapid, sophisticated analysis
        of large and complex datasets using multidimensional storage.

      Reporting Services: A comprehensive solution for creating, managing, and delivering both traditional,
        paper-oriented reports and interactive, Web-based reports.




                                                                 56
     Management tools: SQL Server includes integrated management tools for advanced database
       management and tuning as well as tight integration with tools such as Microsoft Operations Manager
       (MOM) and Microsoft Systems Management Server (SMS). Standard data access protocols drastically

       reduce the time it takes to integrate data in SQL Server with existing systems. In addition, native Web

       service support is built into SQL Server to ensure interoperability with other applications and platforms.

     Development tools: SQL Server offers integrated development tools for the database engine, data
       extraction, transformation, and loading (ETL), data mining, OLAP, and reporting that are tightly integrated
       with Microsoft Visual Studio® to provide end-to-end application development capabilities. Every major

       subsystem in SQL Server ships with it’s own object model and set of APIs to extend the data system in
       any direction that is unique to your business.

  The SQL Server 2005 data platform provides organizations of all sizes with the following benefits:

     Leverage data assets: In addition to delivering a secure, reliable database for line-of-business and
       analytical applications, SQL Server 2005 enables customers to get more value from their data by
       including embedded functionality such as reporting, analysis, and data mining.

     Increase productivity:        Through comprehensive business intelligence capabilities and integration with
       familiar tools such as the Microsoft Office System, SQL Server 2005 provides information workers across
       your organization with critical, timely business information that is tailored to their specific needs.        The

       goal is to extend BI to all users within an organization and ultimately to allow users at all levels of the

       organization to make better business decisions based on one of their most valuable assets—their data.

     Reduce Information Technology complexity:             SQL Server 2005 simplifies the development,
       deployment, and management of line-of-business and analytical applications by providing a flexible
       development environment for developers and integrated, automated management tools for database

       administrators.

     Lower total cost of ownership (TCO): The integrated approach and focus on ease-of-use and
       deployment provides the industry’s lowest upfront, implementation, and maintenance costs for rapid
       return on your database investment.


What’s New in SQL Server 2005
Enterprise Data Management
  In today’s connected world, data and the systems that manage that data must always be secure yet available
  to your users. With SQL Server 2005, users and Information Technology professionals across your
  organization will benefit from reduced application downtime, increased scalability and performance, and tight
  yet flexible security controls. SQL Server 2005 also includes many new and improved capabilities to help
  make your Information Technology staff more productive. SQL Server 2005 includes key enhancements to
  enterprise data management in the following areas:

     Manageability

     Availability



                                                                  57
       Scalability
       Security

Manageability
    SQL Server 2005 makes it simpler and easier to deploy, manage, and optimize enterprise data and analytical
    applications. As an enterprise data management platform, it provides a single management console that

    enables data administrators anywhere in your organization to monitor, manage, and tune all of the databases

    and associated services across your enterprise.      It provides an extensible management infrastructure that
    can be easily programmed using SQL Management Objects (SMO), enabling users to customize and extend

    their management environment and Independent Software Vendors (ISVs) to build additional tools and

    functionality to further extend the capabilities that come out of the box.

    SQL Server Management Studio
    SQL Server 2005 simplifies management by providing one integrated management console to monitor and

    manage the SQL Server relational database, as well as Integration Services, Analysis Services, Reporting

    Services, Notification Services, and SQL Mobile across large numbers of distributed servers and databases.
    Database administrators can perform several tasks at the same time including: authoring and executing a

    query, viewing server objects, managing an object, monitoring system activity, and viewing online help. SQL
    Server Management Studio hosts a development environment for authoring, editing and managing scripts

    and stored procedures using Transact-SQL, Multidimensional Expressions (MDX), XMLA, and SQL Server

    Mobile Edition. Management Studio is readily integrated with source control. Management Studio also hosts

    tools for scheduling SQL Server Agent jobs and managing maintenance plans to automate daily maintenance

    and operation tasks. The integration of management and authoring in a single tool coupled with the ability to

    manage all types of servers provides enhanced productivity for database administrators.

    Proactive Performance Monitoring and Performance Tuning
    SQL Server 2005 exposes more than 70 new measures of internal database performance and resource

    usage from memory, locking, and scheduling to transactions and network and disk I/O. These Dynamic

    Management Views (DMVs) provide greater transparency and visibility into the database and a powerful
    infrastructure for proactive monitoring of database health and performance.

    SQL Management Objects
    SQL Management Objects (SMO) is a new set of programming objects that exposes all of the management

    functionality of the SQL Server database. In fact, Management Studio was built with SQL Management

    Objects. SMO is implemented as a Microsoft .NET Framework assembly. You can use SMO to automate
    common SQL Server administrative tasks, such as programmatically retrieving configuration settings,

    creating new databases, applying Transact-SQL scripts, creating SQL Server Agent jobs, and scheduling

    backups. The SMO object model is a more secure, reliable, and scalable replacement for Distributed

    Management Objects (DMO), which was included with earlier versions of SQL Server.

Availability
    Investments in high availability technologies, additional backup and restore capabilities, and replication

    enhancements will enable enterprises to build and deploy highly available applications. Innovative high



                                                                   58
availability features such as; database mirroring, failover clustering, database snapshots, and enhanced
online operations will minimize downtime and help to ensure that critical enterprise systems remain
accessible. We will review these enhancements in greater detail in this section.

Database Mirroring
Database mirroring allows continuous streaming of the transaction log from a source server to a single

destination server. In the event of a failure of the primary system, applications can immediately reconnect to
the database on the secondary server. The secondary instance detects failure of the primary server within

seconds and accepts database connections immediately. Database mirroring works on standard server
hardware and requires no special storage or controllers. Figure 2 shows the basic configuration of database

mirroring.




Figure 2: Basic Configuration of Database Mirroring
Failover Clustering
Failover clustering is a high availability solution that exploits Microsoft Windows® Clustering Services to

create fault-tolerant virtual servers that provide fast failover in the event of a database server failure. In SQL

Server 2005, support for failover clustering has been extended to SQL Server Analysis Services, Notification

Services, and SQL Server replication. The maximum number of cluster nodes has been increased to eight.

SQL Server failover clustering is now a complete fault-tolerant server solution.


 Availability Feature                                Database Mirroring                 Failover Clustering

 Automatic Failover                                  Yes                                Yes

 Transparent Client Redirection                      Yes, auto-redirect                 Yes, reconnect to same IP

 Impact on Overall Throughput                        No impact to minimal               No impact

 Zero Work Loss                                      Yes                                Yes

 Requires Certified Hardware                         No                                 Yes

 Provides Redundant Data                             Yes                                No


Database Snapshots


                                                                  59
    SQL Server 2005 introduces the ability for database administrators to create instant, read-only views of a
    database. The database snapshot provides a stable view without the time or storage overhead of creating a
    complete copy of the database. As the primary database diverges from the snapshot, the snapshot adds its

    own copy of pages as they are modified. Thus the snapshot may be used to quickly recover from an

    accidental change to a database by simply reapplying the original pages from the snapshot to the primary

    database.
    Fast Recovery
    SQL Server 2005 improves the availability of SQL Server databases with a new faster recovery option. Users

    can reconnect to a recovering database after the transaction log has been rolled forward. Earlier versions of

    SQL Server required users to wait until incomplete transactions had rolled back, even if the users did not
    need to access the affected parts of the database.

    Dedicated Administrator Connection
    SQL Server 2005 introduces a dedicated administrator connection to access a running server even if the

    server is not responding or is otherwise unavailable. This allows you to execute diagnostic functions or

    Transact-SQL statements in order to troubleshoot problems on a server. The connection is activated by
    members of the sysadmin fixed server role and is only available through the SQLCMD command prompt
    utility either locally, or from a remote machine.

    Online Operations (index operations and restore)
    The ability to create, rebuild, or drop an index online is an enhanced feature of SQL Server 2005 that
    augments the indexing capabilities of earlier versions of SQL Server. The online index option allows
    concurrent modifications (updates, deletes, and inserts) to the underlying table or clustered index data and
    any associated indexes during index data definition language (DDL) execution. With support for online index
    operations, you can add indexes without interfering with access to tables or other existing indexes.
    Additionally, the server workload allows index operations to take advantage of parallel processing

    SQL Server 2005 also introduces the ability to perform a restore operation while an instance of SQL Server is
    running. Online restoration capabilities improve the availability of SQL Server because only the data that is
    being restored is unavailable. The rest of the database remains online and available. Earlier versions of SQL
    Server require that you bring a database offline before you restore the database.

    Replication
    Replication is designed to increase data availability by distributing the data across multiple database servers.
    Availability is increased by allowing applications to scale out the SQL Server read workload across
    databases. SQL Server 2005 offers enhanced replication using a new peer-to-peer model that provides a
    new topology in which databases can be synchronized transactionally with any identical peer database.

Scalability
    Scalability advancements such as table partitioning, snapshot isolation, and 64-bit support will enable you to
    build and deploy your most demanding applications using SQL Server 2005. The partitioning of large tables
    and indexes significantly enhances query performance against very large databases.




                                                                   60
   Table and Index Partitioning
   Table and index partitioning eases the management of large databases by facilitating the management of the
   database in smaller, more manageable chunks. While the concept of partitioning data across tables,

   databases, and servers is not new to the world of databases, SQL Server 2005 provides a new capability for

   the partitioning of tables across filegroups in a database. Horizontal partitioning allows for the division of a

   table into smaller groupings based on a partitioning scheme. Table partitioning is designed for very large
   databases, from hundreds of gigabytes to terabytes and beyond.

   Snapshot Isolation
   Once data is copied, transformed, and archived to an analysis-oriented database it must be maintained

   and/or rebuilt periodically. Users certainly benefit from looking at a transactionally consistent version of the

   database; however, the version of the data that they are viewing is no longer current. It can take many hours

   to build and index the and that might not be what the user really needs. This is where snapshot isolation

   comes in. The snapshot isolation level allows users to access the last row that was committed, by using a

   transactionally consistent view of the database. This new isolation level provides the following benefits:

        Increased data availability for read-only applications.

        Nonblocking read operations allowed in an OLTP environment.
        Automatic mandatory conflict detection for write transactions.
        Simplified migration of applications from Oracle to SQL Server.
   Replication Monitor
   Replication Monitor is a tool that sets a new standard for ease of use in managing complex data replication
   operations with its intuitive user interface and wealth of data metrics.

   Support for 64-Bit System Itanium2 and x64
   Optimized for the Intel Itanium processor, SQL Server (64-bit) takes advantage of advanced memory

   addressing capabilities for essential resources such as buffer pools, caches, and sort heaps, reducing the

   need to perform multiple I/O operations to bring data in and out of memory from disk. Greater processing

   capacity without the penalties of I/O latency opens the door to new levels of application scalability.

   Windows Server™ 2003 x64 provides high performance for both 32-bit and 64-bit applications on the same
   system. The underlying architecture is based on 64-bit extensions to the industry-standard x86 instruction

   set, allowing today's 32-bit applications to run natively on x64 processors.      At the same time, new 64-bit

   applications are executed in 64-bit mode, which processes more data per clock cycle, allows greater access
   to memory, and speeds numeric calculations. The end result is a platform that leverages the existing wealth

   of 32-bit applications while also providing a smooth migration path to 64-bit computing.

Security
   SQL Server 2005 makes significant enhancements to the security model of the database platform, with the

   intention of providing more precise and flexible control to enable tighter security of the data. A considerable
   investment has been made in a number of features to provide a high level of security for your enterprise data
   including:



                                                                  61
   Enforcing policies for SQL Server login passwords in the authentication space.

   Providing for more granularity in terms of specifying permissions at various scopes in the authorization
     space.
   Allowing for the separation of owners and schemas in the security management space.

Authorization
A new security model in SQL Server 2005 allows administrators to manage permissions at a granular level
and at a designated scope, making management of permissions easier as well as ensuring that the principle
of least privileges is upheld. SQL Server 2005 allows you to specify a context under which statements in a
module execute. This feature also acts as an excellent mechanism for granular permission management.

Authentication
SQL Server 2005 clustering supports Kerberos authentication against a SQL Server 2005 virtual server.
Administrators can specify Microsoft Windows-style policies on standard logins so that a consistent policy is
applied across all accounts in the domain.

Native Encryption
SQL Server 2005 supports encryption capabilities within the database itself, fully integrated with a key
management infrastructure. By default, client/server communications are encrypted. To centralize security
assurance, server policy can be defined to reject unencrypted communications.

SQL and Trustworthy Computing

The Trustworthy Computing initiative outlines a framework that defines the steps necessary to
support secure computing as well as measures that help you deploy and maintain a secure

environment. These steps help to protect the confidentiality, integrity, and availability of data

and systems at every phase of the software life cycle—from design, to delivery, to
maintenance. To uphold the four tenets of the Trustworthy Computing initiative, Microsoft and

the SQL Server team have taken the following steps:

   Secure by design.        The SQL Server development team conducted multiple security audits and spent
     more than two months studying SQL Server components and the interaction between them. For each
     potential security threat, the team did a threat analysis to evaluate the issue and completed additional
     design and testing work to neutralize potential security issues. As a result of these design efforts, SQL

     Server 2005 includes many new server security features.

   Secure by default. Upon installation, SQL Server 2005 chooses the right set of configuration values for
     all setup options, ensuring that when a new system is installed, it will be in a secure state by default.

   Secure in deployment. Microsoft has created content to help organizations deploy SQL Server using
     the proper security credentials and to fully understand the steps and permissions required. SQL Server

     deployment tools provide the information necessary to understand the decisions you need to make

     during deployment. Security updates are easy to find and install—and if you choose the option, the

     updates install automatically. Tools are also available to help you assess and manage security risks

     across organizations.




                                                              62
Developer Productivity
   SQL Server 2005 includes many new technologies that bring significant increases in developer productivity.
   From .NET Framework support to tight integration with Visual Studio®, these features provide developers
   with the ability to more easily create secure, robust database applications at a lower cost. SQL Server 2005
   enables developers to leverage existing skills across a variety of development languages while providing an
   end-to-end development environment for the database. Native XML capabilities will also allow developers to
   build new classes of connected applications across any platform or device.

   Enhancements for developer productivity include:

      Expanded language support

      Improved development tools

      Extensibility
      Improved data access

      XML and Web services

      Application Framework

Expanded Language Support
   Because the common language runtime (CLR) is hosted in the database engine, developers can choose
   from a variety of familiar languages to develop database applications, including Transact-SQL, Microsoft
   Visual Basic® .NET, and Microsoft Visual C#® .NET. Additionally, CLR hosting will provide developers with
   increased flexibility through the use of user-defined types and functions. The CLR will also provide
   opportunities to use third-party code for rapid database application development.

   CLR/.NET Framework integration
   With the release of Microsoft SQL Server 2005, database programmers can now take full advantage of the
   Microsoft .NET Framework class library and modern programming languages to implement functionality
   within the server. Using common language runtime (CLR) integration, you can code your stored procedures,
   functions, and triggers in the .NET Framework language of your choice. Microsoft Visual Basic .NET and the
   C# programming language both offer object-oriented constructs, structured exception handling, arrays,
   namespaces, and classes. In addition, the .NET Framework provides thousands of classes and methods that
   have extensive built-in capabilities that you can easily use on the server-side. Many tasks that were awkward
   or difficult to perform in Transact-SQL can be better accomplished by using managed code; additionally, two
   new types of database objects—aggregates and user-defined types—are available. You can now better use
   the knowledge and skills that you have already acquired to write in-process code. In short, SQL Server 2005
   enables you to extend the database server to more easily perform appropriate computation and operations
   on the back end.




                                                           63
This integration between SQL Server and the CLR provides several major benefits:

   Enhanced programming model: Programming languages that are compatible with the .NET Framework
     are in many respects richer than Transact-SQL, offering constructs and capabilities that were previously
     not available to SQL developers.

   Enhanced safety and security: Managed code runs in a CLR environment, hosted by the database
     engine. This allows .NET Framework database objects to be safer and more secure than the extended
     stored procedures available in earlier versions of SQL Server.

   User-defined types and aggregates: Two new database objects that expand the storage and querying
     capabilities of SQL Server are enabled by hosting the CLR.

   Common development environment: Database development is integrated into the Microsoft Visual
     Studio 2005 development environment. You can use the same tools for developing and debugging

     database objects and scripts that you use to write middle-tier or client-tier .NET Framework components
     and services.

   Performance and scalability: Because managed code compiles to native code prior to execution, you
     can achieve significant performance increases in some scenarios.
By using languages such as Visual Basic .NET and C#, you can capitalize on CLR integration to write code
that has more complex logic and is more suited for computation tasks. In addition, Visual Basic .NET and C#
offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. You can easily
organize related code into classes and namespaces, which means that you can more easily organize and
maintain your code investments when you are working with large amounts of code. The ability to logically and
physically organize code into assemblies and namespaces is a huge benefit, that allows you to better find
and relate different pieces of code in a large database implementation.

Managed code is more efficient than Transact-SQL at processing numbers and managing complicated
execution logic, and provides extensive support for string handling, regular expressions, and so on. Also, with
the functionality that is available in the .NET Framework class library, you have full access to thousands of
pre-built classes and routines that you can access easily from any stored procedure, trigger, or user-defined
function. Everything from improved string-handling functions, math functions, date operations, access to
system resources, advanced encryption algorithms, file access, image processing, and XML data
manipulation is easily accessible from managed stored procedures, functions, triggers, and aggregates.

One of the major benefits of managed code is type safety. Before managed code is executed, the CLR
performs several checks through a process known as verification to ensure that the code is safe to run. For

example, the code is checked to ensure that memory is not read that has not been written to.




                                                               64
   Transact-SQL enhancements
   Transact-SQL has long been the basis for all programmability of SQL Server. SQL Server 2005 provides
   many new language capabilities for developing scalable database applications. These enhancements include

   error handling, new recursive query capabilities, and support for new SQL Server Database Engine

   capabilities. Transact-SQL enhancements in SQL Server 2005 increase your expressive powers in query

   writing, allowing you to improve the performance of your code and extend your error management
   capabilities. The continuous effort that is being put into enhancing Transact-SQL shows a firm belief in its
   significant role in SQL Server.

Improved Development Tools
   Developers will be able to use one development tool for Transact-SQL, XML, Multidimensional Expressions

   (MDX), and XML for Analysis (XML/A). Integration with the Visual Studio development environment will

   provide more efficient development and debugging of line-of-business and business intelligence (BI)
   applications.
   Business Intelligence Development Studio
   The Business Intelligence Development Studio is a common development environment for building BI

   solutions based on Visual Studio including a database engine, analysis services, and reporting services. Use
   the Business Intelligence Development Studio graphical user interface to design SQL Server Integration
   Services (SSIS) packages for data management applications. SSIS packages are designed, developed, and
   debugged in the Business Intelligence Development Studio by dragging tasks from the toolbox, setting their
   properties, and connecting tasks with precedence constraints. Figure 3 shows the interface in Visual Studio
   for the Business Intelligence Development Studio.




                                                               65
Figure 3: Business Intelligence Development Studio interface in Visual Studio
Visual Studio Integration
SQL Server 2005 and Visual Studio 2005 together provide deeper levels of integration between the database
and the application development environment than ever before. Developers now have the ability to create

CLR stored procedures, functions, user-defined types, and user-defined aggregates directly from within the
Visual Studio development environment. They can deploy these new database objects directly from Visual

Studio without having to switch tools. Visual Studio 2005 supports all of the new SQL Server data types, such

as native XML directly. You can also add your CLR database objects to the same source control system that
you use for all you Visual Studio projects, thus providing an even greater level of integration and security to
your development processes.

Cross-Tier and Cross-Language Debugging
SQL Server 2005 and Visual Studio 2005 together provide even deeper integration in the area of application

debugging. The combination allows you to seamlessly debug both CLR and Transact-SQL code using the

same Visual Studio debugging interface and it allows you to debug from CLR to Transact-SQL and back

again, regardless of the location of the code, whether it is on the developer machine or stored in the SQL
Server database.




                                                              66
Extensibility
    User-Defined Types and Aggregates
    User-defined types in SQL Server 2005 are not an object relational extensibility mechanism. They are a way
    to extend the scalar type system of the database. The scalar type system includes the columnar types that

    ship with SQL Server (types like int, nvarchar, uniqueidentifier, etc.). With user-defined types, you can

    define your own type that can be used for column definitions, for example. Create a user-defined type if your

    type really is an atomic value that is appropriate to be modeled as a column.

    Use user-defined types if you need to define your own scalar type. Example scenarios for such types include

    custom date/time data types in various calendars, and currency data types. With user-defined types, you can

    create a single object that exposes all the behaviors that are available on the type, and encapsulate, or hide,
    the underlying data that is stored by the type. Everyone that needs to access the data has to use the user

    defined type programmatic interface. If you can leverage existing functionality in the .NET Framework (such

    as the internationalization or calendar functionality), that is another really good reason to consider
    implementing your type as a user-defined type.

    There are a number of scenarios where you may need to perform aggregations over data. This includes

    performing statistical calculations, such as avg, stddev, etc. If the desired aggregation function is not directly
    supported as a built-in aggregate function, there are three ways to perform a custom aggregation in SQL

    Server 2005:

       Write the aggregation as a user-defined aggregate.

       Write the aggregate using a CLR stored procedure.

       Use a server-side cursor.
    SQL Management Objects(SMO)
    SQL Management Objects (SMO) is the management object model for SQL Server 2005. SMO represents

    significant design and architectural improvements for the SQL Server management object model. It is a

    simple to use but rich object model that is based on .NET Framework managed code. SMO is the primary
    tool for developing database management applications using .NET Framework. SMO is used by every dialog

    box in SQL Server Management Studio, and every administrative action that you can perform in SQL Server

    Management Studio you can also accomplish by using SMO.

    The new SMO object model and the Microsoft Windows Management Instrumentation (WMI) APIs replace

    SQL-DMO. Where possible, SMO incorporates similar objects as SQL-DMO for ease of use. You can still use

    SQL Server 2005 with SQL-DMO, but SQL-DMO will not be updated to manage features that are specific to

    SQL Server 2005.




                                                                      67
   Analysis Management Objects
   Analysis Management Objects (AMO) allows client applications to access the range of administrative
   commands and capabilities available to Analysis Services using an object library that can provide object-level

   validation capabilities, instead of having to manually generate DDL scripts for Analysis Services commands
   and the often-lengthy contents of the Analysis Services Scripting Language (ASSL) ObjectDefinition
   element. Applications using AMO can either connect and work directly with objects on an Analysis Services

   instance, or create such objects without an existing connection and persist the metadata for later deployment.
   AMO also “wraps” ASSL commands and elements.

Improved Data Access and Web Services
   In SQL Server 2005, you can develop XML Web services in the database tier, making SQL Server an HTTP

   listener. This provides a new type of data access capability for applications that are centralized around Web

   services. In SQL Server 2005 you can use HTTP to access SQL Server directly, without using a middle-tier

   listener such as Microsoft Internet Information Services (IIS). SQL Server exposes a Web service interface,

   to allow the execution of SQL statements and invocation of functions and procedures. Query results are

   returned in XML format and can take advantage of the Web services infrastructure of Visual Studio.

   ADO.NET 2.0/ADOMD.NET

   There is much that's new in the next version of ADO.NET. From new support for query change notifications,

   to Multiple Active Result Sets (MARS), ADO.NET evolves dataset access and manipulation to achieve

   greater scalability and flexibility.

Query notification
   SQL Server 2005 introduces notification support for SQL Server queries. You can use this support to send a
   command to SQL Server and to request that a notification be generated if executing the same command

   again produces different results from those obtained initially. You accomplish this by using a dependency

   object that detects when the underlying data is changed. Commands that are sent to the server through any
   of the client APIs such as ADO.NET, OLE DB, Open Database Connectivity (ODBC), Microsoft ActiveX®

   Data Objects (ADO), or SOAP may include a tag that requires a notification. For each statement that is

   executed as part of the request, the server creates a notification subscription that fires once for each

   statement that is included in the request. Notifications are delivered through a SQL Service Broker queue that

   applications can poll, and use activation services or blocking statements that return whenever the
   notifications are available. Query notifications are useful for enabling the caching of results in applications
   such as database-driven Web sites. Figure 4 shows the query notification process.




                                                                  68
        Figure 4: Query notification

MARS
   MARS provides the ability to have more than one pending request per connection, in particular to have more
   than one default result set open per connection. Default result sets are forward-only read-only result sets. For

   default result sets, the client drivers transparently retrieve the data in large chunks (Tabular Data Stream

   buffer sized chunks) so that application requests are satisfied without a roundtrip to the server (as in the case
   of server cursors). The application can use a simple row-at-a-time programming model without compromising

   performance. The multiple active result sets feature removes the current restriction in which an open default

   result set blocks the driver from sending requests to the server until the entire result set is consumed.


Transparent failover for data mirroring
   SQL Server 2005 supports a "hot spare" capability through database mirroring. If a SQL Server instance fails,
   the work can be shifted over to the backup server automatically. This requires an instance to witness the

   failover known as (not surprisingly) the witness instance. Hot spare scenarios require that existing client
   connections must "know" to fail over (establish a connection with the new server instance), as well. Client
   connections that produce an error on the next attempted access and must be manually "failed over" by client
   programming are suboptimal. SqlClient in ADO.NET 2.0 supports client failover without special programming
   of the application program.

XML Support
   Advancements such as native XML data type and XQuery help organizations to seamlessly connect internal
   and external systems.       SQL Server 2005 will support both relational and XML data natively, so enterprises

   can store, manage, and analyze data in the format that best suits their needs. Support for existing and
   emerging open standards such as Hypertext Transfer Protocol (HTTP), XML, Simple Object Access Protocol
   (SOAP), XQuery, and XML Schema definition language (XSD) will also facilitate communication across
   extended enterprise systems.




                                                                    69
XML Data Type
XML can model complex data; it is not limited to the scalar types that are supported by SQL Server. As such,
a string-based, built-in data type such as char or varchar does not suffice to make full and effective use of
the power and the numerous advantages of XML. For example, if XML is stored as a string, you can insert or

select an entire document, or even retrieve contiguous bytes from it, but you cannot query into the contents

of the document itself. By providing the XML data type, SQL Server 2005 allows you to query portions of an
XML document, validate that the document conforms to an XML schema, and even modify the contents of
the XML document in place. It also integrates traditional, relational data with data in unstructured or semi-
structured XML documents in ways that are not possible with SQL Server 2000. In SQL Server 2005, XML
data is stored as binary large objects (BLOBs) in an internal representation that allows efficient reparsing and

some compression.

A collection of XML schemas can be associated with a column of type XML. This provides validation for

constraints, inserts, and updates, and typing of values inside stored XML data, as well as optimizations for

storage and query processing. SQL Server 2005 also provides several DDL statements for managing
schemas on the server.

XQuery
The XML Query Language, or XQuery, is an intelligent and robust language that is optimized for querying all

types of XML data. With XQuery you can run queries against variables and columns of the XML data type
using the latter's associated methods. As with many of the XML standards, the World Wide Web Consortium

(W3C) oversees the development of XQuery. XQuery evolved from a query language called Quilt, which was

itself based on a variety of other query languages such as the XML Path Language (XPath) version 1.0, XQL,
and SQL. It also contains XPath 2.0 as a subset. Therefore, if you have experience using XPath 1.0, you can

capitalize on your skills and do not have to learn an entirely new query language. There are, however,

significant enhancements that go beyond XPath 1.0, such as typing, special functions, and support for better
iteration, sorting of results, and construction.

SQL Server 2005 ships with deep XQuery capabilities that allow for XML object manipulation in the data tier.

It supports a statically typed subset of the XQuery 1.0 Working Draft of November 15, 2003.
Web Services Support
In SQL Server 2005, you can develop XML Web services in the database tier, making SQL Server an HTTP

listener. This provides a new type of data access capability for applications that are centralized around Web

services. In SQL Server 2005, you can use HTTP to access SQL Server directly without using a middle-tier

listener such as Microsoft Internet Information Services (IIS). SQL Server exposes a Web service interface to

allow the execution of SQL statements and invocation of functions and procedures. Query results are
returned in XML format and can take advantage of the Web services infrastructure of Visual Studio.




                                                                70
   XML for Analysis Services (XML/A)
   XML for Analysis Services (XML/A) is the native, standards-based protocol for communicating with the
   Analysis Services server. New kinds of applications are enabled and easy to develop—applications that

   integrate analytics with operations in real time. With XML/A as the native protocol, Analysis Services clients

   can be configured to have a zero footprint, and each server is automatically a Web service. A light-footprint

   Win32 layer is available for backward compatibility with tools that work with Analysis Services 2000 on OLE
   DB for OLAP, ADOMD, and ADOMD.NET. Many users will continue to use the ADOMD.NET object model to
   build custom applications on Analysis Services.

Application Framework
   SQL Server 2005 introduces a new SQL Server application framework including: Service Broker, Notification

   Services, SQL Server Mobile, and SQL Server Express. Service Broker is a distributed application framework

   that provides reliable asynchronous messaging at the database to database level.

   Service Broker
   Over the last 10 years, the proliferation of e-commerce applications has created the need for increased

   workflow management across database applications. When an online customer places an order for a book,

   this order needs to commit transactions into the inventory, shipping, and credit card systems, and also needs

   to send an order confirmation using another Web application. Waiting for each of these processes to happen

   in order doesn't scale well. SQL Server 2005 provides a new scalable architecture for building asynchronous

   message routing. Figure 5 outlines the Service Broker architecture.




    Figure 5: Service Broker architecture




                                                                   71
The Service Broker technology allows internal or external processes to send and receive streams of reliable,
asynchronous messages by using extensions to normal Transact-SQL data manipulation language.
Messages are sent to a queue in the same database as the sender, to another database in the same

instance of SQL Server, or to another instance of SQL Server either on the same server or on a remote

server.
Notification Services
Microsoft SQL Server Notification Services is a platform for developing and deploying applications that

generate and send notifications to users. Notifications are personalized, timely messages that can be sent to
a wide variety of devices.
Notifications reflect the preferences of the subscriber. The subscriber enters a subscription to express an

interest in information. For example, "notify me when the stock price of Adventure Works reaches $70.00," or

"notify me when the strategy document my team is writing is updated."

A notification can be generated and sent to the user as soon as a triggering event occurs, or it can be

generated and sent on a predetermined schedule that the user specifies. The user's subscription specifies

when the notification should be generated and sent.

Notifications can be sent to a wide variety of devices. For example, a notification can be sent to a user's

mobile phone, personal digital assistant (PDA), Microsoft Windows Messenger, or e-mail account. Because

these devices often accompany the user, notifications are ideal for sending high-priority information.

SQL Server Mobile Edition
SQL Server 2000 shipped with SQL Server 2000 Windows CE Edition, which is now SQL Server Mobile

Edition version 3.0. There are a number of new key features in SQL Server Mobile Edition that relate to

developers:

   You can create a SQL Server Mobile Edition database on the desktop or on the device, directly from SQL
     Server Management Studio. You can also manipulate the schema of the SQL Server Mobile Edition
     database directly from Management Studio, regardless of whether the database resides on the mobile

     device or on the desktop. You can use SQL Server Management Studio to run queries that target a SQL

     Server Mobile Edition database on the device or on the desktop. You can also take advantage of new
     SQL Server Mobile Edition features that include an XML showplan rendered in a GUI format just like

     native SQL Server and the ability to use query hints to override the query optimizer in SQL Server Mobile

     Edition. For the first time, you can control the optimization plan on a device.

   You can now code against SQL Server Integration Services (SSIS) objects to exchange data.

   The new SqlCeResult set is derived from the SQLResult set that is in SQL Server 2005. This allows SQL

     Server Mobile Edition to have a true scrollable, updateable cursor. It also allows binding to data objects
     that are on devices.

   You can code an application to synchronize data while leaving the main application open, and you can

     have two separate applications access the same database on the device at the same time.




                                                               72
   You can get notifications that you can code into status bars that will give the status of a synchronization.
     Previously, there was no way to know how far synchronization status was, to notify users that a device
     had not stopped responding.

   You can maintain the small size of the database through a much more aggressive page reclamation
     policy.

   You can share parameterized query code with SQL Server syntax.

SQL Server Express
More than ever developers are leveraging relational databases to provide a rich end-user experience.
Protecting and managing information inside these applications is critical. Microsoft SQL Server Express helps
developers build robust and reliable applications by providing a free, easy to use, and robust database. Too
often database systems are overly complex for building simple applications. Microsoft Visual Studio 2005 and
SQL Server Express reduce this complexity by providing a simple but powerful development environment for
building data-driven applications. Developers can design schemas, add data, and query local databases, all
inside the Visual Studio 2005 environment. If developers need more advanced database features, then SQL
Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server. Figure 6 shows
the Query Editor interface in SQL Server Express Manager




Figure 6: The Query Editor in SQL Server Express Manager (XM)

A new GUI tool called SQL Server Express Manager (XM) is freely available as a separate Web download.
XM allows easy database management and query analysis capabilities, will have a small download size, and
will be freely redistributable. XM supports connections to SQL Server Express and other SQL Server 2005
editions, SQL Server 2000, and MSDE 2000. A simplified connection dialog box guides the user through the
selection of the instance and the authentication methods to be used. Both local and remote connections are
possible using XM. Object Explorer will enumerate and display the common objects used, such as the
instance, tables, stored process, etc., in a hierarchical manner and will help the user visualize access to the
database.


                                                          73
   All database management functionalities are available by invoking the right-click context menu from Object
   Explorer. Some of the database management options to be exposed include creating and modifying
   databases, tables, logins, and users. Many of these common database operations are available as task

   wizards that guide the user through the process, while many others are available as tabbed window

   documents. For instance, XM will provide a New/Edit Database document for creating new databases and

   editing existing databases.

   Many database users prefer to manage their servers using Transact-SQL, since this approach offers finer-
   grained control than using the graphical user interface. The Query Editor in XM will allow users to develop

   and execute Transact-SQL statements and scripts. The Query Editor will have rich features such as keyword

   color-coding and a results pane that returns results in a data grid. The error messages, if any, will also be

   shown in the results pane.


Business Intelligence
   SQL Server 2005 will further Microsoft’s leadership in the are of business intelligence (BI) through
   innovations in scalability, data integration, development tools, and rich analytics. SQL Server 2005 enables
   scalable business intelligence by putting critical, timely information in the hands of employees across your

   organization. From the CEO to the information worker, employees will be able to quickly and easily harness

   data to make better decisions faster. The comprehensive integration, analysis, and reporting capabilities of
   SQL Server 2005 enable companies to extend the value of their existing applications, regardless of the

   underlying platform.
   Business intelligence features include enhancements in the following areas:

      End-to-end integrated business intelligence platform

      Integration Services
      Analysis Services

      Reporting Services

      Integration with the Microsoft Office System

End-to-End Integrated Business Intelligence Platform
   SQL Server 2005 is a complete business intelligence platform that provides the features, tools, and

   functionality to build both classic and innovative kinds of analytical applications. The following provides an

   introduction to the tools that you will use to build an analytical application, and highlights new functionality

   that makes it easier than ever to build and manage complex BI systems.

   The SQL Server 2005 business intelligence toolset delivers end-to-end BI application integration:

      Design: Business Intelligence Development Studio is the first integrated development environment
        designed for the business intelligence developer. Built on Visual Studio 2005, the Business Intelligence
        Development Studio delivers a rich, integrated, professional development platform for BI system
        developers. Debugging, source control, and script and code development are available for all
        components of the BI platform.




                                                                  74
      Integrate: SQL Server Integration Services (SSIS) has been rewritten to perform complex data
        integration, transformation, and synthesis at high speed for very large data volumes. The Business
        Intelligence Development Studio makes building and debugging packages positively fun. Integration

        Services, Analysis Services, and Reporting Services work together to present a seamless view of data

        from heterogeneous sources.

      Analyze: Microsoft Data Mining has always been easy to use. Now it's even better with the addition of
        important new algorithms, including Association Rules, Time Series, Regression Trees, Sequence
        Clustering, Neural Network, and Naïve Bayes. SQL Server 2005 blurs the lines between relational and

        multidimensional databases. You can store data in the relational database, in the multidimensional
        database, or use the new Proactive Cache feature to get the best of both worlds. Important new

        analytical capabilities have been added to Analysis Services cubes as well: these include Key
        Performance Indicator (KPI) framework, MDX scripts, and other built-in advanced business analytics.

        The Reporting Services report delivery and management framework enables easy distribution of complex

        analytics to the widest possible audience.

      Report: Reporting Services extends the Microsoft business intelligence platform to reach the business
        user who needs to consume the analysis. Reporting Services is an enterprise managed reporting
        environment, embedded and managed via Web services. Reports can be personalized and delivered in a
        variety of formats, with a range of interactivity and printing options. Complex analyses can reach a broad

        audience through the distribution of reports as a data source for downstream business intelligence. New

        with SQL Server 2005 is the reporting tool, Report Builder.

      Manage: SQL Server Management Studio integrates the management of all SQL Server 2005
        components. Business intelligence practitioners will benefit from Microsoft's extension of the server
        abilities you expect from the relational engine—scalability, reliability, availability, programmability, and so
        on—to the full set of BI platform components.

Integration Services
   SQL Server 2005 includes a redesigned enterprise ETL platform, called SQL Server Integration Services
   (SSIS). SQL Server Integration Services enables organizations to more easily integrate and analyze data
   from multiple heterogeneous information sources. By analyzing data across a wide array of operational
   systems, organizations may gain a competitive edge through a holistic understanding of their business.

   Enterprise ETL Platform
   This new platform is the successor to the popular feature in SQL Server 2000, called Data Transformation
   Services (DTS). SSIS is completely new for SQL Server 2005. SSIS provides the breadth of features, and
   very high scale performance that is necessary to build enterprise-class ETL applications. SSIS is fully
   programmable, embeddable, and extensible—characteristics that make it an ideal ETL platform.

   Beyond Traditional ETL
   SQL Server 2005 supports nontraditional data (Web Services, XML) out-of-the box through:

      SSIS brings analytics to the data without persisting the data.

      Data Mining and text mining in the data flow.



                                                                   75
      Data Mining and analytics are brought to the data flow for data quality and data cleansing.

Analysis Services
   With SQL Server 2005, Analysis Services provides, for the first time, a unified and integrated view of all your
   business data as the foundation for all of your traditional reporting, OLAP analysis, and data mining.

   Unified Dimensional Model
   By combining the best aspects of traditional OLAP analysis and relational reporting, Analysis Services 2005

   provides a metadata model that covers both sets of needs. A set of cubes and dimensions defined in

   Analysis Services 2005 is referred to as a Unified Dimensional Model (UDM). The UDM is a central metadata

   repository defining business entities, business logic, calculations, and metrics that serves as the source for all
   reports, spreadsheets, OLAP browsers, KPIs, and analytical applications.

   Using the powerful new Data Source View feature, the UDM is mapped to a host of heterogeneous back-end

   data sources allowing a complete and integrated picture of the business regardless of the location of the

   data.
   With the UDM’s friendly descriptions of the business entities, navigation hierarchies, multiple perspectives,

   and even automatic translations to native languages, users will find it easy to explore the corporate business

   data.
   Data Mining
   Microsoft SQL Server 2005 Data Mining is the business intelligence technology that helps you build complex

   analytical models, and integrate those models with your business operations. Microsoft SQL Server 2005

   Analysis Services establishes new ground for data mining.          By creating an easy to use, extensible,
   accessible, and flexible platform, SQL Server 2005 Analysis Services data mining capabilities introduces data

   mining to organizations that previously would never have considered a data mining solution.

   Through an enterprise class architecture; a deep integration with the SQL Server family of business

   intelligence tools, and a rich set of tools, APIs and algorithms, SQL Server enables the creation of a new

   breed of intelligent applications that enhance productivity, increase profits and reduce costs by providing
   customized data-driven solutions to a broad range of business problems.

Reporting Services
   Reporting Services extends the Microsoft BI platform to reach the information worker who needs access to

   business data. Reporting Services is a server-based enterprise reporting environment, managed via Web

   services. Reports can be delivered in a variety of formats, with a range of interactivity and printing options.

   Complex analyses can reach a broad audience through the distribution of reports as a data source for
   downstream business intelligence.

   An integrated component of SQL Server 2005, Reporting Services provides:

      A high performance engine for processing and formatting reports.

      A complete set of tools for creating, managing, and viewing reports.

      An extensible architecture and open interfaces for embedding reports or integrating reporting solutions in
        diverse Information Technology environments.



                                                                    76
Relational and OLAP Reports
Reports built on relational data are useful but the ability to add additional analytic capabilities makes reporting
powerful.   Reporting Services allows you to easily build reports together or separately.          SQL Server 2005

supports both relational and OLAP data and provides a query editor for both including SQL Query Editor and

MDX Query Editor.
Report Builder
Report Builder, a new component of SQL Server 2005 Reporting Services, allows business users to create

their own reports using a user-friendly model of their data. Report Builder leverages the Reporting Services
platform to bring ad hoc reporting to all end users.        Users create and edit reports with the Report Builder

client application. The Report Builder user interface is built on top of familiar Microsoft Office paradigms such

as Excel and PowerPoint. Figure 7 shows a sample Report Builder report.




Figure 7: Design reports with Report Builder




                                                                 77
   Report Builder is a ClickOnce application deployed via the browser. Users start by selecting report layout
   templates containing predefined data sections such as tables, matrices and charts.          They drag and drop
   report items from the model to the design surface and set constraints to filter the report data.         The model

   contains all of the necessary information for the Report Builder to automatically generate the source query

   and retrieve the requested data. The Report Builder also allows users to:

      Add text and formatting to reports.

      Create new fields and calculations defined using the model.

      Preview, print, and publish reports.

      Export report data to formats such as Microsoft Excel.

Integration with the Microsoft Office System
   Reports that are served up by the Report Server in Reporting Services can run in the context of Microsoft

   SharePoint® Portal Server and Microsoft Office System applications such as Microsoft Word and Microsoft

   Excel. You can use SharePoint features to subscribe to reports, create new versions of reports, and distribute

   reports. You can also open reports in Word or Excel to view HTML versions of the reports.


Upgrading to SQL Server 2005
   Following are some tips for upgrading to SQL Server 2005.

      Upgrade to SQL Server 2005 from SQL Server 7.0 or SQL Server 2000.

      Run Upgrade Advisor before upgrading to determine if any product changes are expected to break
        existing applications.

      The Database Engine, Analysis Services, and Reporting Services can be upgraded by Setup.

      SQL Server Integration Services, the replacement for DTS, is installed side-by-side with DTS. You can
        run DTS packages using the DTS runtime components.

      SQL Server 2005 Notification Services is installed side-by-side with Notification Services 2.0. You must
        migrate instances of Notification Services to SQL Server 2005 when you upgrade the Database Engine.

      Use the Surface Area Configuration tool after upgrading to enable or disable SQL Server 2005 services,
        network protocols, and features.

Microsoft SQL Server Pricing and Licensing
   The family of SQL Server editions includes everything you need in one product for a comprehensive, out-of-
   the-box data storage, management, analysis and reporting platform with flexible licensing that allows you to
   choose the solution that best fits your unique needs.

   Designed to scale from the largest enterprise down to the smallest business, SQL Sever provides the same
   performance, security, reliability, and business value to all customers. SQL Server supports implementations
   ranging from multi-terabyte data warehouses to Pocket PC devices running SQL Server Windows CE Edition.

   Pricing & licensing




                                                                  78
Edition      Pricing         Benefit                    Size           Key Features

Express      Free            Fastest      way      to 1 CPU            Simple Management Tool
                             learn,    build     and 1-GB RAM          Simple Reporting
                             deploy simple data-                       Replication & SSB Client
                                                        4-GB DB size
                             driven applications.

Workgroup    $3,900      per Most affordable and 1-2 CPUs              Management Studio
             processor       easiest      to     use 3-GB RAM          Import/Export
             $739 (server database         solution                    Limited           Replication
             + 5 users)   for              smaller                     Publishing
                             departments         and
                                                                       Clustering
                             growing
                                                                       Back-up Log Shipping
                             businesses.

Standard     $6,000      per Complete            data 1-4 CPUs         Database Mirroring
             processor       management and Unlimited RAM              Basic ETL
             $2,799          analysis platform for                     Standard OLAP Server
             (server + 10 medium businesses                            with Analysis Services
             users)       and         larger
                                                                       Standard Reporting with
                             departments.
                                                                       Reporting Services

                                                                       Data Mining
                                                                       Full Replication & SSB
                                                                       Publishing
                                                                       Available in native 32- and
                                                                       64-bit editions
                                                                       Supports Itanium2 and x64

Enterprise   $25,000     per Fully      integrated Unlimited Scale     Advanced           database
             processor       data management & Partitioning            mirroring,        Complete
             $13,500         and           analysis                    online       &         parallel
             (server + 25 platform                for                  operations, and database
             users)          business-critical                         snapshot.
                             enterprise                                Advanced analysis tools
                             applications.                             including full OLAP & Data
                                                                       Mining.
                                                                       Advanced reporting with
                                                                       customized, high scale,
                                                                       and ad hoc reporting.
                                                                       Advanced         ETL      with




                                                        79
   Edition        Pricing          Benefit                 Size                Key Features
                                                                               complex data routing and
                                                                               transformation capabilities.

                                                                               Available in native 32 and
                                                                               64 bit editions.
                                                                               Supports Itanium2 and x64


  This table outlines the pricing and benefits for each edition of the Microsoft SQL Server 2005 product line.
      Note Bold indicates a feature that is new for Microsoft SQL Server 2005. Each higher edition includes
      the same functionality as the edition below it.      All prices are in US dollars and reflect pricing for
      purchases within the United States.

  For more information on pricing and licensing and the SQL Server 2005 product line please visit the
  following:
  http://www.microsoft.com/sql/howtobuy/default.asp

  http://www.microsoft.com/sql/howtobuy/understdbpricing.asp


Windows Server System Common Engineering Roadmap
  SQL Server is part of the Windows Server System—a comprehensive and integrated server infrastructure
  that simplifies the development, deployment, and operation of flexible business solutions.

  As a key part of the Windows Server System family, SQL Server 2005 includes a standard set of capabilities
  such as common patch management, Watson support, and tools such as the Microsoft Baseline Security
  Analyzer to deliver a consistent and predictable experience to Windows Server System customers.

  The goal of the Windows Server System Common Engineering Roadmap is to create a set of common
  services that will be implemented across all Windows Server System server products. This set of common
  services raises the bar for server infrastructure and helps ensure that products throughout Windows Server
  System are engineered for greater security, reliability, manageability, and flexibility. The Windows Server
  System Common Engineering Roadmap builds on the following initiatives to enable customers to meet
  today’s business challenges and the challenges of tomorrow.




                                                           80
To deliver on this vision of flexible Information Technology, Microsoft is currently focusing on three key
initiatives:

   .NET, Microsoft’s Web services strategy, connects information, people, systems, and devices through
     software. Adopting services-oriented architecture throughout the Microsoft platform provides businesses
     with the ability to quickly build, deploy, manage, and use connected, security-enhanced systems based

     on Web services. These systems enable faster, more agile business integration and deliver on the

     promise of information anytime, anywhere, on any device.

   Dynamic Systems Initiative (DSI) is focused on delivering systems that are designed with operations in
     mind and are built to monitor ongoing operations and adjust dynamically based on models that can
     change with the business. This initiative unifies hardware, software, and service vendors around a

     model-based management that enables customers to harness the power of industry-standard hardware

     and brings simplicity, automation, and flexibility to Information Technology operations. The goal is to
     reduce management and operations costs, improve reliability, and increase responsiveness throughout

     the entire Information Technology life cycle.

   Trustworthy Computing is a long-term, company-wide, and collaborative effort to create and deliver
     more secure, private, and reliable computing experiences for users, while reducing the demands on
     users and Information Technology administrators. The goal of Trustworthy Computing is to deliver the
     security, privacy, reliability, and business integrity that people expect from the computing industry.

In the Common Engineering Criteria for 2005, 16 different specifications have been defined and applied
throughout the Windows Server System. Beginning with 2005 versions, all Windows Server System products
will either comply with these criteria, or have specific reasons for any exemptions, with implementation plans
for future releases.




                                                              81

								
To top