Overview of SQL Server

1
Overview of SQL Server

• Alka Arora

2
SQL Server Introduction

• Microsoft SQL Server is an application used to
  create computer databases for the Microsoft
  Windows family of server operating systems.
• Microsoft SQL Server provides an environment
  used to generate databases that can be accessed
  from workstations, the Internet, or other media
  such as a personal digital assistant (PDA).

3
Accessibility

• MS SQL Server is accessed by applications using
  the Structured query language (SQL).
• SQL is the language used to access and program
  relational databases.
• SQL Server is used to interact with a database.
  Recently, it has been widely used in Web
  applications involving a database back-end.
• It allows you to enter, edit, delete, and
  retrieve lots of data very quickly.

4
Features of SQL Server

• Internet Integration.
• It has the scalability, availability, and
  security features required to operate as the data
  storage component of the largest Web sites.
• Scalability and Availability
• The same database engine can be used across
  platforms ranging from laptop computers running
  Microsoft Windows 98 through large,
  multiprocessor servers running Microsoft Windows
  2000 Data Center Edition.
• SQL Server 2000 Enterprise Edition supports
  features such as indexed views, and large memory
  support that allow it to scale to the performance
  levels required by the largest Web sites.

5
Other Features of SQL Server

• Ease of installation, deployment, and use
• It includes a set of administrative and
  development tools that improve upon the process
  of installing, deploying, managing, and using SQL
  Server across several sites.
• These features allows to rapidly deliver SQL
  Server applications that customers can implement
  with a minimum of installation and administrative
  overhead.
• Data warehousing
• SQL Server 2000 includes tools for extracting and
  analyzing summary data for online analytical
  processing.

6
Data management features of MS SQL Server

• MS SQL Server consists of a number of programs
  that provide for managing data stored in
  databases
• The Enterprise Manager (EM) allows the user to
  manage their data objects (tables, views).
• The Query Analyzer (QA) assists users in creating
  and managing queries against their databases.
• There is a facility to import/export data to/from
  databases.
• There is a facility to take database backup and
  restore that backup.

7
Connecting to MS-SQL Server databases

• Connection to a database is accomplished in one
  of three ways
• via a console application like EM, QA, or
  import/export
• via a standalone application that you write
• via a web-based application that you write

8
Connecting using a console application

• To connect to an MS-SQL database this way, start
  an application like EM
• Go to the Windows Start menu,
• then Programs -gt Microsoft SQL Server -gt
  Enterprise Manager,
• Then identify your database.

9
Connecting using a standalone application

• To connect this way
• Use Windows Authentication.
• Create a user DSN (Data Source Name) in advance
  and use it in your application.

10
Connecting using a web application

• Use SQL Sever Authentication.
• SQL Server Authentication requires that you
  provide the SQL Server name and the password
  associated with this user.
• The user name and password are necessary in this
  case, as authentication to the database is NOT
  handled by Windows.

11
Graphical Tools

• Microsoft Management Console (MMC)
• SQL Server Enterprise Manager

12
SQL Server Query Analyzer

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

13
Service Manager

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

14
Data Transformation Services (DTS)

• DTS provides import and export capabilities to
  SQL Server.
• DTS can import data from another SQL Server, from
  Microsoft Access, text files, Microsoft Excel,
  Oracle, FoxPro etc.
• DTS imports and exports data.

15
Backup and Restore Service

• The backup and restore component provides an
  important safeguard for protecting critical data
  stored in SQL Server databases.
• With proper planning, you can recover from many
  failures, including
• Media failure
• User errors
• Permanent loss of a server.
• Additionally, backing up and restoring databases
  is useful for other purposes, such as copying a
  database from one server to another.
• By backing up a database from one computer and
  restoring the database to another, a copy of a
  database can be made quickly and easily.

16
Replication Services

• They are used to keep data in synchronization
  between SQL Server databases and other systems
  such as Oracle, Microsoft Access, handheld
  devices, and more. You can use replication to
  send data to multiple systems as data changes, on
  a scheduled basis.

17
(XML) capability

• SQL Server 2000 provides basic XML capabilities.
• Documents can enable data transfer between
  heterogeneous programs or data sources.

18
Stored Procedures

• Its a procedure that is designed and stored on
  the SQL Server.
• They can be called any number of times in your
  program.
• They are used when a certain operation requires a
  large amount of Transact-SQL code or is performed
  repetitively.
• Stored procedures can be faster than batches of
  Transact-SQL code.
• They allow faster execution.

19
Indexes

• An index is a tool that SQL Server uses to access
  data quickly.
• The analogy that is most often used to describe a
  database index is that of a book.
• In the case of the book's index, you can find the
  page numbers where a word is located.
• In the case of SQL Server, the server's Query
  Optimization engine uses an index to find where
  data is located physically on a hard drive.

20
Create New Database

• To 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 Database
• Give name to database

21
Add 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

22
Data Types

• Integers
• bigint
• Integer (whole number) data from -263
  (-9,223,372,036,854,775,808) through 263-1
  (9,223,372,036,854,775,807).
• int
• Integer (whole number) data from -231
  (-2,147,483,648) through 231 - 1
  (2,147,483,647).
• smallint
• Integer data from -215 (-32,768) through 215 -
  1 (32,767).
• tinyint
• Integer data from 0 through 255.
• bit
• Integer data with either a 1 or 0 value.

23
Data Types

• Character Strings
• char (n)
• Fixed-length non-Unicode character data with
  length of n bytes. n must be a value from 1
  through 8,000. Storage size is n bytes.
• varchar(n)
• Variable-length non-Unicode character data with
  length of n bytes. n must be a value from 1
  through 8,000. Storage size is the actual length
  in bytes of the data entered, not n bytes. The
  data entered can be 0 characters in length.
• text
• Variable-length non-Unicode data with a maximum
  length of 231 - 1 (2,147,483,647) characters.
• Unicode Character Strings
• nchar, nvarchar

24
Data Types

• Binary Strings
• binary
• Fixed-length binary data with a maximum length of
  8,000 bytes
• varbinary
• Variable-length binary data with a maximum length
  of 8,000 bytes.
• Image
• Variable-length binary data with a maximum length
  of 231 - 1 (2,147,483,647) bytes

25
Data Types

• datetime and smalldatetime
• datetime
• Date and time data from January 1, 1753, through
  December 31, 9999, with an accuracy of
  three-hundredths of a second, or 3.33
  milliseconds.
• smalldatetime
• Date and time data from January 1, 1900, through
  June 6, 2079, with an accuracy of one minute

26

• THANKS