MS SQL Server

1
MS SQL Server
2
Introduction

• MS SQL Server is a database server
• Product of Microsoft
• Enables user to write queries and other SQL
  statements and execute them
• Consists of several features. A few are
• Query Analyzer
• Profiler
• Service Manager
• Bulk Copy Program (BCP)

3
Profiler

• Monitoring tool
• Used for performance tuning
• Uses traces an event monitoring protocol
• Event may be a query or a transaction like logins
  etc

4
Service Manager

• Helps us to manage services
• More than one instance of SQL server can be
  installed in a machine
• First Instance is called as default instance
• Rest of the instances (16 max) are called as
  named instances
• Service manager helps in starting or stopping the
  instances individually

5
Instances

• Each instance is hidden from another instance
• Enhances security
• Every instance has its own set of Users, Admins,
  Databases, Collations
• Advantage of having multiple instance is
• Multi company support (Each company can have its
  own instance and create databases on the same
  server, independent on each other)
• Server consolidation (Can host up to 10 server
  applications on a single machine)

6
BCP

• Bulk Copy Program
• A powerful command line utility that enables us
  to transfer large number of records from a file
  to database
• Time taken for copying to and from database is
  very less
• Helps in back up and restoration

7
Query Analyzer

• Allows us to write queries and SQL statements
• Checks syntax of the SQL statement written
• Executes the statements
• Store and reload statements
• Save the results in file
• View reports (either as grid or as a text)

8
SQL Database Objects

• A SQL Server database has lot of objects like
• Tables
• Views
• Stored Procedures
• Functions
• Rules
• Defaults
• Cursors
• Triggers

9
System Databases

• By default SQL server has 4 databases
• Master System defined stored procedures, login
  details, configuration settings etc
• Model Template for creating a database
• Tempdb Stores temporary tables. This db is
  created when the server starts and dropped when
  the server shuts down
• Msdb Has tables that have details with respect
  to alerts, jobs. Deals with SQL Server Agent
  Service

10
Creating a database

• We need to use Master database for creating a
  database
• By default the size of a database is 1 MB
• A database consists of
• Master Data File (.mdf)
• Primary Log File (.ldf)

11
Database operations

• Changing a database
• Use ltdbnamegt
• Creating a database
• Create database ltdbnamegt
• Dropping a database
• Drop database ltdbnamegt

12
SQL Server Data types

• Integer Stores whole number
• Float Stores real numbers
• Text Stores characters
• Decimal Stores real numbers
• Money Stores monetary data. Supports 4 places
  after decimal
• Date Stores date and time
• Binary Stores images and other large objects
• Miscellaneous Different types special to SQL
  Server.
• (Refer to notes for more info)

13
Operators

• Arithmetic
• Assignment
• Comparison
• Logical
• String
• Unary
• Bitwise

14
Select Statements

• To execute a statement in MS SQL, Select the
  statement and Click on the Execute button in the
  query analyser or press F5
• This is used to retrive records from a table
• Eg. Select from table1
• This will fetch all rows and all columns from
  table1
• Eg. Select col1,col2 from table1
• This will fetch col1 and col2 from table1 for all
  rows
• Eg. Select from table1 where ltltcondngtgt
• This will fetch all rows from table1 that
  satisfies a condition
• Eg. Select col1,col2 from table1 where ltltcondngtgt
• This will fetch col1 and col2 of rows from table1
  that satisfies a condition

15
Select Options

• Aggregate functions
• Sum(col1) sum of data in the column col1
• Max(col1) data with maximum value in col1
• Min(col1) data with minimum value in col1
• Avg(col1) Average of data in col1
• Count(col1) Number of not null records in table
• Grouping Group by col1 Groups data by col1
• Ordering Order by col1 Orders the result in
  ascending order (default order) of col1
• Filtering Where ltltcondngtgt and Having ltltcondngtgt

16
Table management

• Create table tablename
• (
• col1 data type,
• col2 data type
• )
• - Creates a table with two columns
• Drop table tablename
• - Drops the table structure

17
Insert statements

• Inserting data to all columns
• Insert into tablename(col1,col2) values(v1,v2)
• Insert into tablename values(v1,v2)
• Inserting data to selected columns
• Insert into tablename(col1) values (v1)
• Insert into tablename(col2) values (v2)

18
Update statement

• Update table tablename
• Set colnamevalue
• - This updates all rows with colname set to
  value
• Update table tablename
• Set colnamevalue
• Where ltltconditiongtgt
• - This updates selected rows with colname as
  value only if the row satisfies the condition

19
Delete statements

• Delete from table1
• Deletes all rows in table1
• Delete from table1 where ltltconditiongtgt
• Deletes few rows from table1 if they satisfy the
  condition

20
Truncate statement

• Truncate table tablename
• Removes all rows in a table
• Resets the table.
• Truncate does the following, where as delete
  statement does not
• Releases the memory used
• Resets the identity value
• Does not invoke delete trigger

21
Alter statements

• Used to modify table structure
• Add new column
• Change data type of existing column
• Delete a column
• Add or remove constraints like foreign key,
  primary key

22
More table commands

• Viewing tables in a data base
• Exec sp_tables a
• This gives all tables in the current database
  that starts with a
• Viewing table strucure
• Exec sp_columns ltlttablenamegtgt
• Exec sp_columns student

23
Joins

• Cross Join
• Cartesian product. Simply merges two tables.
• Inner Join
• Cross join with a condition. Used to find
  matching records in the two tables
• Outer Join
• Used to find un matched rows in the two tables
• Self Join
• Joining a table with itself

24
Cross Join

• There are two tables A and B
• A has a column Id and data (1,2,3)
• B has a column Id and data (A,B)
• If I put
• Select A.Id, B.Id from A,B
• This generates output as
• A 1
• B 1
• C 1
• A 2
• B 2
• C 2

25
Self Join

• There is a table called Emp with the following
  structure
• empid ename mgrid
• 1 A null
• 2 B 1
• 3 C 1
• 4 D 2
• If I want to print all managers using self join,
  I should write quey as
• select e1.ename from
• emp e1,emp e2
• where e1.mgrid e2.empid

26
Inner Join

• I have 2 tables Student(sid,Name) and
  Marks(Sid,Subject,Score)
• If I want to print the marks of all students in
  the following format,
• Name Subject Score
• Select Name,Subject,Score from
• Student s join Marks m
• On s.sid m.sid

27
Outer Join

• Right outer Join
• Print all the records in the second table with
  null values for missing records in the first
  table
• Left outer Join
• Print all the records in the first table with
  null values for missing records in the second
  table
• Full outer Join
• Prints all records in both the table with null
  values for missing records in both the table

28
Left Outer Join

• I have a table Employee (Eid, Ename, Mid) and
• a table Machine (Mid,ManufacturerName)
• Employee
• Eid EName Mid
• 1 ABC 1
• 2 DEF 3
• Machine
• Mid ManufacturerName
• 1 Zenith
• 2 HP

29
Left Outer Join

• I want to print the employee name and machine
  name.
• If I write a query using inner join, then the
  second employee will
• not be displayed as the mid in his record is not
  avilable with the second
• table.
• So I go for left outer join. The query is as
  shown below
• Select Ename, ManufacturerName from Employee e
  left outer join
• Machine m on e.Mid m.Mid

30
Right outer Join

• Assume data in the tables like this
• Employee
• Eid EName Mid
• 1 ABC 1
• 2 DEF
• Machine
• Mid ManufacturerName
• 1 Zenith
• 2 HP

31
Right Outer Join

• If I want to find which machine is unallocated, I
  can use right outer join.
• The query is as follows
• Select Ename, ManufacturerName from Employee e
  right outer join
• Machine m on e.Mid m.Mid
• This yields a result
• ABC Zenith
• HP

32
Full Outer Join

• Assume data in the tables like this
• Employee
• Eid EName Mid
• 1 ABC 1
• 2 DEF
• 3 GHI 2
• Machine
• Mid ManufacturerName
• 1 Zenith
• 2 HP
• 3 Compaq

33
Full Outer Join

• If I want to find people who have been un
  allocated with a system and
• machines that are been un allocated, I can go for
  full outer join.
• Query is like this
• Select Ename, ManufacturerName from Employee e
  full outer join
• Machine m on e.Mid m.Mid
• This yields a result
• ABC Zenith
• DEF
• GHI HP
• Compaq

34
Views

• Views are logical tables
• They are pre compiled objects
• We can select few columns or rows from a table
  and put the data set in a view and can use view
  in the same way as we use tables

35
Views

• Create views
• Create view viewname as select stmt
• Create view view_emp as select empid,
• empname from employee
• Select from views
• Select from viewname
• Select empid,empname view_emp
• Drop views
• Drop view viewname
• Drop view view_emp

36
String Functions

• Substring(string,start,length) Will fetch
  characters starting at a specific index extending
  to length specified.
• Left(string,length) Fetches number of
  characters specified by length from left of the
  string
• Right(string,length) Fetches number of
  characters specified by length from right of the
  string
• Len(string) Returns the length of a string

37
String Functions

• Ltrim(string) Removes leading spaces in a
  string
• Rtrim(string) Removes trailing spaces in a
  string
• Lower(string) Converts the characters in a
  string to lower case
• Upper(string) Converts the characters in a
  string to upper case

38
Numeric Functions

• ABS(Number) Fetches the modulo value (Positive
  value) of a number
• CEILING(Number) Fetches the closest integer
  greater than the number
• FLOOR(Number) Fetches the closest integer
  smaller than the number
• EXP(Number) Fetches the exponent of a number

39
Numeric Functions

• POWER(x,y) Fetches x raised to the power of y
• LOG(Number) Fetches the natural logarithmic
  value of the number
• LOG10(Number) Fetches log to the base 10 of a
  number
• SQRT(Number) Fetches the square root of a number

40
Indexes

• Indexes make search and retrieve fast in a
  database
• This is for optimizing the select statement
• Types of index
• Unique
• Non unique
• Clustered
• Non clustered

41
Index

• Create index indexname on
• tablename(columnname)
• This creates a non clustered index on a table
• Create unique clustered index index_name on
• Student(sname)
• This creates a unique and clustered index on the
• Column Sname.

42
Sequences

• This creates an auto increment for a column
• If a table has a column with sequence or auto
  increment, the user need not insert data
  explicitly for the column
• Sequence is implemented using the concept of
  Identity

43
Identity

• Identity has
• A seed
• An increment
• Seed is the initial value
• Increment is the value by which we need to skip
  to fetch the nextvalue
• Identity(1,2) will generate sequence numbers
  1,3,5,7

44
Sample

• Create table table1
• (
• Id integer identity(1,1),
• Name varchar(10)
• )
• It is enough if we insert like this
• Insert into table1(name) values(Ram)
• Ram will automatically assigned value 1 for id