Overview of SQL ServerOverview of SQL Server

Alka AroraAlka Arora

SQL Server: IntroductionSQL Server: Introduction

Microsoft SQL Server is an application Microsoft SQL Server is an application used to create computer databases for the used to create computer databases for the Microsoft Windows family of server Microsoft Windows family of server operating systems.operating systems.

Microsoft SQL Server provides an Microsoft SQL Server provides an environment used to generate databases environment used to generate databases that can be accessed from workstations, that can be accessed from workstations, the Internet, or other media such as a the Internet, or other media such as a personal digital assistant (PDA). personal digital assistant (PDA).

AccessibilityAccessibility

MS SQL Server is accessed by applications using the MS SQL Server is accessed by applications using the Structured query language (SQL). Structured query language (SQL). – SQL is the language used to access and program relational SQL is the language used to access and program relational

databases.databases.

SQL Server is used to interact with a database. SQL Server is used to interact with a database. Recently, it has been widely used in Web applications Recently, it has been widely used in Web applications involving a database back-end.involving a database back-end.

It allows you to enter, edit, delete, and retrieve lots of It allows you to enter, edit, delete, and retrieve lots of data very quickly. data very quickly.

Features of SQL ServerFeatures of SQL ServerInternet Integration.Internet Integration. – It has the scalability, availability, and security features required to It has the scalability, availability, and security features required to

operate as the data storage component of the largest Web sites. operate as the data storage component of the largest Web sites.

Scalability and AvailabilityScalability and Availability – The same database engine can be used across platforms ranging The same database engine can be used across platforms ranging

from laptop computers running Microsoft Windows® 98 through from laptop computers running Microsoft Windows® 98 through

large, multiprocessor servers running Microsoft Windows 2000 Data large, multiprocessor servers running Microsoft Windows 2000 Data

Center Edition. Center Edition.

– SQL Server 2000 Enterprise Edition supports features such as SQL Server 2000 Enterprise Edition supports features such as

indexed views, and large memory support that allow it to scale to the indexed views, and large memory support that allow it to scale to the

performance levels required by the largest Web sites.performance levels required by the largest Web sites.

Other Features of SQL ServerOther Features of SQL Server

Ease of installation, deployment, and use Ease of installation, deployment, and use

– It includes a set of administrative and development tools that improve It includes a set of administrative and development tools that improve

upon the process of installing, deploying, managing, and using SQL upon the process of installing, deploying, managing, and using SQL

Server across several sites. Server across several sites.

– These features allows to rapidly deliver SQL Server applications that These features allows to rapidly deliver SQL Server applications that

customers can implement with a minimum of installation and customers can implement with a minimum of installation and

administrative overhead. administrative overhead.

Data warehousing Data warehousing

– SQL Server 2000 includes tools for extracting and analyzing summary SQL Server 2000 includes tools for extracting and analyzing summary

data for online analytical processing. data for online analytical processing.

Data management features of Data management features of MS SQL ServerMS SQL Server

MS SQL Server consists of a number of MS SQL Server consists of a number of programs that provide for managing data stored programs that provide for managing data stored in databases: in databases: – The Enterprise Manager (EM) allows the user to The Enterprise Manager (EM) allows the user to

manage their data objects (tables, views). manage their data objects (tables, views). – The The Query Analyzer (QA)Query Analyzer (QA) assists users in creating assists users in creating

and managing queries against their databases. and managing queries against their databases. – There is a facility to There is a facility to import/export dataimport/export data to/from to/from

databases.databases.– There is a facility to take database There is a facility to take database backupbackup and and

restorerestore that backup. that backup.

Graphical ToolsGraphical Tools

Microsoft Management Console (MMC)Microsoft Management Console (MMC)

SQL Server Enterprise ManagerSQL Server Enterprise Manager

SQL Server Query AnalyzerSQL Server Query Analyzer

SQL Server Query Analyzer is a graphical SQL Server Query Analyzer is a graphical user interface for designing and testing user interface for designing and testing SQL statements, batches, and scripts SQL statements, batches, and scripts interactively. interactively.

Service ManagerService Manager

SQL Server Service Manager is used for SQL Server Service Manager is used for starting, stopping and pausing (MSSQL Server starting, stopping and pausing (MSSQL Server services).services).

Data Transformation Services Data Transformation Services (DTS)(DTS)

DTS provides import and export capabilities DTS provides import and export capabilities to SQL Server.to SQL Server.

DTS can import data from another SQL DTS can import data from another SQL Server, from Microsoft Access, text files, Server, from Microsoft Access, text files, Microsoft Excel, Oracle, FoxPro etc.Microsoft Excel, Oracle, FoxPro etc.

DTS imports and exports data. DTS imports and exports data.

Backup and Restore ServiceBackup and Restore Service

The backup and restore component provides an The backup and restore component provides an important safeguard for protecting critical data stored in important safeguard for protecting critical data stored in SQL Server databases. SQL Server databases. With proper planning, you can recover from many With proper planning, you can recover from many failures, including: failures, including: – Media failure Media failure – User errors User errors – Permanent loss of a server. Permanent loss of a server.

Additionally, backing up and restoring databases is Additionally, backing up and restoring databases is useful for other purposes, such as copying a database useful for other purposes, such as copying a database from one server to another.from one server to another.By backing up a database from one computer and By backing up a database from one computer and restoring the database to another, a copy of a database restoring the database to another, a copy of a database can be made quickly and easily. can be made quickly and easily.

(XML) capability(XML) capability

– SQL Server 2000 provides basic XML SQL Server 2000 provides basic XML capabilities.capabilities.

– Documents can enable data transfer Documents can enable data transfer between heterogeneous programs or data between heterogeneous programs or data sources. sources.

Stored ProceduresStored ProceduresIt’s a procedure that is designed and stored on the SQL It’s a procedure that is designed and stored on the SQL Server. Server.

They can be called any number of times in your They can be called any number of times in your program.program.

They are used when a certain operation requires a large They are used when a certain operation requires a large amount of Transact-SQL code or is performed amount of Transact-SQL code or is performed repetitively.repetitively.

Stored procedures can be faster than batches of Stored procedures can be faster than batches of Transact-SQL code. Transact-SQL code. They allow faster execution. They allow faster execution.

IndexesIndexes

An index is a tool that SQL Server uses to An index is a tool that SQL Server uses to access data quickly. access data quickly.

The analogy that is most often used to describe The analogy that is most often used to describe a database index is that of a book. a database index is that of a book. – In the case of the book's index, you can find the page In the case of the book's index, you can find the page

numbers where a word is located. numbers where a word is located. – In the case of SQL Server, the server's Query In the case of SQL Server, the server's Query

Optimization engine uses an index to find where data Optimization engine uses an index to find where data is located physically on a hard drive.is located physically on a hard drive.

Create New DatabaseCreate New DatabaseTo visually create a database, open Microsoft SQL Server Management

Studio. In the Object Explorer, expand the server name followed by the Databases

node. Right-click Databases and click New DatabaseGive name to database

Add a TableAdd a Table In the Object Explorer, expand the database and expand its Tables node. Right-click the Tables node and click New Table... Enter a name for each column and select its data type:

Data TypesData TypesIntegersIntegers• bigint

Integer (whole number) data from -2^63 (-Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).(9,223,372,036,854,775,807).

• intInteger (whole number) data from -2^31 (-2,147,483,648) through Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).2^31 - 1 (2,147,483,647).

• smallintInteger data from -2^15 (-32,768) through 2^15 - 1 (32,767).Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767).

• tinyintInteger data from 0 through 255.Integer data from 0 through 255.

• bitInteger data with either a 1 or 0 value.Integer data with either a 1 or 0 value.

Data TypesData TypesCharacter Strings Character Strings • char [(n)][(n)]– Fixed-length non-Unicode character data with length of Fixed-length non-Unicode character data with length of n n bytes. bytes. n n

must be a value from 1 through 8,000. Storage size is must be a value from 1 through 8,000. Storage size is n n bytes. bytes.

• varchar[(n)]– Variable-length non-Unicode character data with length of Variable-length non-Unicode character data with length of n n bytes.bytes. n n

must be a value from 1 through 8,000. Storage size is the actual must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not length in bytes of the data entered, not n n bytes. The data entered can bytes. The data entered can be 0 characters in length.be 0 characters in length.

• texttext– Variable-length non-Unicode data with a maximum length of 2^31 - 1 Variable-length non-Unicode data with a maximum length of 2^31 - 1

(2,147,483,647) characters.(2,147,483,647) characters.

Unicode Character StringsUnicode Character Stringsnchar, nvarcharnchar, nvarchar

Data TypesData Types

Binary StringsBinary Strings– binarybinary

Fixed-length binary data with a maximum length of Fixed-length binary data with a maximum length of 8,000 bytes8,000 bytes

– varbinaryvarbinaryVariable-length binary data with a maximum length Variable-length binary data with a maximum length of 8,000 bytes.of 8,000 bytes.

– ImageImageVariable-length binary data with a maximum length Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytesof 2^31 - 1 (2,147,483,647) bytes

Data TypesData Types

datetime and smalldatetime datetime and smalldatetime – datetime datetime

Date and time data from January 1, 1753, through Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.hundredths of a second, or 3.33 milliseconds.

– smalldatetime smalldatetime Date and time data from January 1, 1900, through Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute June 6, 2079, with an accuracy of one minute

THANKSTHANKS