SQL SERVER 2008

Microsoft SQL Server is a Relational Database Management System (RDBMS) designed to run
on platforms ranging from laptops to large multiprocessor servers. SQL Server is commonly
used as the backend system for websites and corporate CRMs and can support thousands of
concurrent users.

SQL Server comes with a number of tools to help you with your database administration and
programming tasks.

SQL Server is much more robust and scalable than a desktop database management system such
as Microsoft Access. Anyone who has ever tried using Access as a backend to a website will
probably be familiar with the errors that were generated when too many users tried to access the
database!

Although SQL Server can also be run as a desktop database system, it is most commonly used as
a server database system.

Server Database Systems

Server based database systems are designed to run on a central server, so that multiple users can
access the same data simultaneously. The users normally access the database through an
application.

For example, a website could store all its content in a database. Whenever a visitor views an
article, they are retrieving data from the database. As you know, websites aren't normally limited
to just one user. So, at any given moment, a website could be serving up hundreds, or even
thousands of articles to its website visitors. At the same time, other users could be updating their
personal profile in the members' area, or subscribing to a newsletter, or anything else that
website users do.

Generally, it's the application that provides the functionality to these visitors. It is the database
that stores the data and makes it available. Having said that, SQL Server does include some
useful features that can assist the application in providing its functionality.

SQL Server 2008 Editions

If you are serious about installing (or upgrading) SQL Server, this page provides a quick
overview of your options.

SQL Server 2008 comes in many different editions. The edition you choose will depend on your
requirements. If you are looking for a free database management system, you will need to choose
one of the Express editions or the Compact edition. You could also try the Evaluation edition,
which allows you to trial SQL Server 2008 for 180 days.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
The Editions

Here are the different editions available for SQL Server 2008.

 Enterprise Edition
Data management and business intelligence platform providing enterprise class
scalability, high availability, and security for running business-critical applications
 Standard Edition
Data management and business intelligence platform providing ease of use and
manageability for running departmental applications
 Workgroup Edition
Data management and reporting platform providing secure, remote synchronization, and
management capabilities for running branch applications
 Developer Edition
May be installed and used by one user to design, develop, test, and demonstrate your
programs on as many systems as needed
 Web Edition
A low-TCO, scalable, and manageable database option for web hosters and end
customers looking to deploy publicly facing web applications and services
 Express Edition
A free edition of SQL Server ideal for learning and building desktop and small server
applications and for redistribution by ISVs
 Compact Edition
A free, SQL Server embedded database ideal for building stand-alone and occasionally
connected applications for mobile devices, desktops, and web clients
 Evaluation Edition
This edition may be installed for demonstration and evaluation purposes until an
expiration period of 180 days.

SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is the main administration console for SQL Server.

SSMS enables you to create database objects (such as databases, tables, views etc), view the data
within your database, you can configure user accounts, transfer data between databases, and
more.

Here's what SQL Server Management Studio looks like when you first open it up:

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

The left pane contains the Object Explorer. The Object Explorer provides navigation to
databases, server objects (such as triggers), log files, and more.

The right pane allows you to write queries against the database and view the results. In this
screenshot I have opened a blank query by clicking the "New Query" button. You can also bring
up other windows, such as the Properties window.

Note that I have minimized the size of the window for this screenshot. Once maximized, you
have much more room to play with.

You can use SQL Server Management Studio to create as many databases as you like. You can
also connect to as many databases on as many servers as you like.

Most of the tasks performed with SQL Server Management Studio are initiated either from the
top menu, or by right-clicking on an icon/object.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
SQL Server - Create a Database

System Databases
If you've only just installed SQL Server, you might notice that some databases have already been
created. These are system databases.

Database Type Description

System Stores system level information such as user accounts, configuration
master
database settings, and info on all other databases.
System
model This database is used as a template for all other databases that are created.
database
System Used by the SQL Server Agent for configuring alerts and scheduled jobs
msdb
database etc
System Holds all temporary tables, temporary stored procedures, and any other
tempdb
database temporary storage requirements generated by SQL Server.

We will now create another database for our own use.

Creating a New Database

1. Right click on the "Databases" icon and select "New Database...":

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
2. Name your database and click "OK":

Your New Database

You will now notice your new database appears under the "Databases" section of SQL Server
Management Studio.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Other Options

We have just created a database using the default options. When we created the database, a "Data
File" and a "Transaction Log" were created. They were created in the default location for our
server.

If we'd wanted to, we could have specified a different location for these files. We also could have
changed specifications on whether to allow the file to grow automatically (as it stores more and
more data), and if so, how that growth should be managed. We could have done that at step 2.
But all is not lost. We can still do it now that we've created the database. We can do it via the
Properties dialog box.

To view or change the database properties, simply right click on the database and select
"Properties":

The Properties dialog contains a large number of options for changing the configuration of your
database. For now, we can leave everything at its default setting.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

This lesson demonstrates how to create a table in a SQL Server database using SQL Server
Management Studio (SSMS).

SQL Server - Create a Table

1. Ensuring you have the right database expanded, right click on the "Tables" icon and
select "New Table...":

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
2. While you have this screen open, do the following:
a. Using the values in the screenshot, complete the details in the "Column Name"
column, the "Data Type" column, "Length" column, and "Allow Nulls" column.
b. Make the IndividualId column an "identity column", by setting "Is Identity" to
"Yes" (this option is under the "Identity Specification" section in the bottom
pane). Note that to set values in the bottom pane, you need to select the column
name in the top pane first). This column is going to be an auto-number column - it
will contain an incrementing number for each record that is created.
c. Set the "Default Value" of the DateCreated column to (getdate()). (This will
automatically insert the current date into that field for each new record).

What we are doing at this stage, is creating the column names, specifying the type of data
that can be entered into them, and setting default values.

3. Save the table by selecting File > Save Table_1:

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
4. When prompted, name your table:

SQL Server - Adding Data

Editing Table Rows

We can use the "Edit Top 200 Rows" option to add data to our table.

1. To use this option, right click on the table you wish to open, and select "Edit Top 200
Rows":

2. You can now start entering the data directly into your table.

Note that you don't need to enter data into the IndividualId and DateCreated columns.
This is because the they will be populated automatically (remember, we set IndividualId
to "Is Identity" and DateCreated to "GetDate()"))

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

SQL Server - SQL Scripts

Create a New Query

Before we generate our SQL script, we need somewhere to enter it into. This part is easy. Just
click the "New Query" button:

A blank, white sheet should now appear on the right pane.

Write/Run Your SQL Script

You are now ready to write SQL queries against your database. You can use this interface to
create database objects (such as databases, tables, views etc), insert data into a database table,
select data, update data, delete data.

To run an SQL query:

1. Type your query into the workspace on the right pane

2. Click "Execute" (you can also press F5)

The following screenshot shows an example of using a SQL 'select' statement to select data from
a database:

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

The above 'select' statement is an example of a SQL query. Apart from the occasional example,
SQL queries are outside the scope of this tutorial. If you'd like to learn more about writing SQL
queries, check out the SQL tutorial.

Database Administration Tasks

Most of the database administration tasks that can be performed in SSMS via the graphical user
interface can be performed programatically via SQL scripts. This tutorial concentrates on using
the graphical user interface, mainly because it's usually a lot easier for new users to get their head
around. Once you become more familiar with SQL Server, you may find yourself using SQL
scripts to perform many of the tasks that you started out doing via the graphical user interface.

Data Type:
The gateway converts Microsoft SQL Server data types to Oracle data types as follows:

Data Type Conversions

Microsoft SQL Server Oracle Comment

BINARY RAW -

BIT NUMBER(3) -

CHAR CHAR -

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Microsoft SQL Server Oracle Comment

DATETIME DATE Fractional parts of a second are truncated

DECIMAL NUMBER(p[,s]) -

FLOAT FLOAT(49) -

IMAGE LONG RAW -

INTEGER NUMBER(10) NUMBER range is -2,147,483,647 to 2,147,483,647

MONEY NUMBER(19,4) -

NCHAR NCHAR -

NTEXT LONG -

NVARCHAR NCHAR -

NUMERIC NUMBER(p[,s]) -

REAL FLOAT(23) -

SMALL DATETIME DATE The value for seconds is returned as 0

SMALL MONEY NUMBER(10,4) -

SMALLINT NUMBER(5) NUMBER range is -32,767 to 32,767

TEXT LONG -

TIMESTAMP RAW -

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Microsoft SQL Server Oracle Comment

TINYINT NUMBER(3) -

VARBINARY RAW -

VARCHAR VARCHAR2 -

SQL Server - Query Designer

About The Query Designer

The graphical query designer is a graphical user interface that allows you to build queries to run
against your SQL Server database. This can be particularly useful when building complex
queries that involves many tables, views etc.

The query designer can also be beneficial for those who are learning how to write SQL. This is
because you don't need to remember the SQL syntax in order to write queries against your
database - the query designer generates the SQL for you.

Building Your Queries

To build a query with the query gesigner:

1. Select Query > Design Query in Editor...:

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
2. Add the tables you want to run the query against. In this case, we only have one table to
choose from.

3. Select the column/s you want to display in your query:

4. Click "OK"

Once you've clicked OK, you will find the query has been added to your workspace. You can
then run it as you would any other query.

SQL Server – Queries

SQL is divided into two main categories; Data Manipulation Language (DML), and Data
Definition Language (DDL). An explanation follows.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Data Manipulation Language (DML)

DML enables you to work with the data that goes into the database. DML is used to insert,
select, update, and delete records in the database. Many of your SQL statements will begin with
one of the following commands:

 SELECT - Retrieves data from the database

 INSERT - Inserts new data into the database
 UPDATE - Updates existing data in the database
 DELETE - Deletes existing data from the database

Data Definition Language (DDL)

You may also occasionally need to create or drop a table or other datbase object. SQL enables
you to do this programatically using DDL.

Examples of DDL commands:

 CREATE DATABASE - Creates a new database

 ALTER DATABASE - Modifies the database
 DROP DATABASE - Drops (deletes) a database
 CREATE TABLE - Creates a new table
 ALTER TABLE - Modifies the table
 DROP TABLE - Drops (deletes) a table

SQL Select

The SELECT statement is probably the most commonly used in SQL. It simply retrieves data
from the database. We have already created table name is individual

IndividualId FirstName LastName UserName

1 Fred Flinstone freddo

2 Homer Simpson homey

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
3 Homer Brown notsofamous

4 Ozzy Ozzbourne sabbath

5 Homer Gain noplacelike

Let’s have a look at a simple SELECT statement:

SELECT * FROM Individual

This SQL SELECT statement is attempting to retrieve all columns from a table called Individual.

How do we know it is trying to select all columns? Because it is using an asterisk (*). This is a
quick way of selecting all columns - it's much easier than writing out the names of all columns
(especially if there are a lot of columns).

Of course, this SQL SELECT statement assumes that there is a table called Individual. If there
wasn't, an error would be generated.

Let’s have a look at the table the statement is trying to select data from:

Because our select statement asks to display all columns and all records, we would see the
following:

IndividualId FirstName LastName UserName

1 Fred Flinstone freddo

2 Homer Simpson homey

3 Homer Brown notsofamous

4 Ozzy Ozzbourne sabbath

5 Homer Gain noplacelike

Select from Multiple Tables

You can select from more than one table at a time. To do this, simply separate each table with a
comma. You should also qualify any references to columns by placing the table name in front,
separated by a dot.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
We have another table called Occupation, which contains the individual's occupation.

OccupationId IndividualId JobTitle

1 1 Engineer

2 2 Accountant

3 3 Cleaner

4 4 Attorney

5 5 Sales Executive

SQL statement

We will select from both the Individual table and the Occupation table. We will qualify any
column names by prefixing them with its table's name and a dot.

SELECT * FROM Individual, Occupation

WHERE [Link] = 'Homer'

Result
IndividualId FirstName LastName UserName OccupationId IndividualId JobTitle

1 Homer Simpson homey 2 2 Accountant

2 Homer Brown notsofamous 3 3 Cleaner

3 Homer Gain noplacelike 5 5 Sales Executive

Displaying Less Columns

If you don't need every column to be displayed you can single out just the columns you're
interested in. It's good programming practice to do this - the more columns your program has to
return, the more it will impact its performance.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
To only display those columns you're interested in, simply replace the asterisk (*) with a comma
separated list of the column names.

SQL where statement

SELECT IndividualId, LastName, UserName FROM Individual
WHERE FirstName = 'Homer'

Result
IndividualId LastName UserName

2 Simpson homey

3 Brown notsofamous

5 Gain noplacelike

Multiple Conditions

You can filter records based on more than one condition using operators. Two common operators
are the AND and OR operators.

AND Operator

The AND operator filters the query to only those records that satisfy both the first condition and
the second condition.

SELECT * FROM Individual

WHERE FirstName = 'Homer'
AND LastName = 'Brown'

Result
IndividualId FirstName LastName UserName

3 Homer Brown notsofamous

OR Operator

The OR operator filters the query to only those records that satisfy either one or the other
condition.

SELECT * FROM Individual

WHERE FirstName = 'Homer'
OR LastName = 'Ozzbourne'

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Result
IndividualId FirstName LastName UserName

2 Homer Simpson homey

3 Homer Brown notsofamous

5 Homer Gain noplacelike

4 Ozzy Ozzbourne sabbath

SQL order by statement

SELECT * FROM Individual
ORDER BY LastName

Result
IndividualId FirstName LastName UserName

3 Homer Brown notsofamous

1 Fred Flinstone freddo

5 Homer Gain noplacelike

4 Ozzy Ozzbourne sabbath

2 Homer Simpson homey

Descending Order

By default, ORDER BY sorts the column in ascending order - that is, from lowest values to
highest values. You could also explicitly state this using the ASC keyword, but it's not necessary.

If you want highest values to appear first, you can use the DESC keyword.

SQL statement
SELECT * FROM Individual
ORDER BY LastName DESC

Result
IndividualId FirstName LastName UserName

2 Homer Simpson homey

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

4 Ozzy Ozzbourne sabbath

5 Homer Gain noplacelike

1 Fred Flinstone freddo

3 Homer Brown notsofamous

Sorting By Multiple Columns

You can sort by multiple columns by stating each column in the ORDER BY clause, separating
each column name with a comma. SQL will first order the results by the first column, then the
second, and so on for as many columns that are included in the ORDER BY clause.

SQL statement
SELECT * FROM Individual
ORDER BY FirstName, LastName

Result
IndividualId FirstName LastName UserName

1 Fred Flinstone freddo

3 Homer Brown notsofamous

5 Homer Gain noplacelike

2 Homer Simpson homey

4 Ozzy Ozzbourne sabbath

SQL TOP statement

SELECT TOP 3 * FROM Individual

Result
IndividualId FirstName LastName UserName

1 Fred Flinstone freddo

2 Homer Simpson homey

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
3 Homer Brown notsofamous

Specifying a Percentage

You have the option of specifying a percentage of the result set instead of an absolute value. You
do this with the PERCENT keyword.

SQL statement
SELECT TOP 40 PERCENT * FROM Individual

Result
IndividualId FirstName LastName UserName

1 Fred Flinstone freddo

2 Homer Simpson homey

SQL TOP and the ORDER BY clause

If you are using the TOP clause along with the ORDER BY clause, the TOP clause is applied to
the ordered result set.

Therefore, if we add an ORDER BY to the above query, we end up with something like this:

SQL statement
SELECT TOP 40 PERCENT * FROM Individual
ORDER BY LastName DESC

Result
IndividualId FirstName LastName UserName

2 Homer Simpson homey

4 Ozzy Ozzbourne sabbath

SQL DISTINCT statement

SELECT DISTINCT(FirstName) FROM Individual

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Result

Using the DISTINCT keyword, all customers with a name of "Homer" are counted as one.

FirstName

Fred

Homer

Ozzy

SQL IN statement
SELECT * FROM Individual
WHERE LastName IN ('Simpson','Ozzbourne','Flinstone')

Result
IndividualId FirstName LastName UserName

1 Fred Flinstone freddo

2 Homer Simpson homey

4 Ozzy Ozzbourne sabbath

You might have noticed that this returns the same result as the following SQL statement:

SELECT * FROM Individual WHERE LastName = 'Simpson' OR LastName = 'Ozzbourne'

OR LastName = 'Flinstone'

SQL IN and Subqueries

Now, where the SQL IN operator becomes really useful is when you need to compare a value
against the result of another query.

For example, lets say we have another table called "Publisher". This table contains users who are
allowed to contribute to the website via an administration console. All users in the Publisher
table are also in the Individual table, but not all users in the Individual table are in the Publisher
table.

Our task is to return a list of usernames from all publishers who have an access level of
"Contributor".

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
SQL statement
SELECT UserName FROM Individual WHERE IndividualId IN
(SELECT IndividualId
FROM Publisher
WHERE AccessLevel = 'Contributor')

Result
UserName

homey

notsofamous

sabbath

SQL Group By:

SELECT UserName FROM Individual Group By UserName

Result
UserName

homey

notsofamous

sabbath

SQL Alias

In SQL, an alias is a name that you give a table. This can make it easier to work with table names
- especially when they are long. You could name the alias anything, but usually you'd make it
short.

You may be thinking "a table already has a name, why give it another one?". Well, there are
some good reasons for creating an alias. The main reasons are:

 Queries can sometimes get very long. Aliases can make your query easier to read.
 You may find yourself referencing the same table name over and over again - this will
occur if you're working with multiple tables and you need to refer to columns from those
tables. It can be annoying to have to write the whole name all the time - especially if it's a
long one.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Alias Syntax

SELECT * FROM table_name AS alias_name

Arithmetic Operators

+ Plus

- Minus

* Multiply

/ Divide

Comparison Operators

= Equal

> Greater than

< Less than

>= Greater than equal to

<= Less than equal to

<> Not Equal

IS NOT NULL, IS NULL

Pattern Matching Group Functions

LIKE AVG, MAX, MIN

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

NOT LIKE COUNT, SUM

String Functions Other Functions

+ (expression1 + expression2) ABS

ASCII CEILING

CHAR COS

CHARINDEX EXP

DATALENGTH FLOOR

LOWER LOG

LTRIM LOG10

RTRIM %, SIN,SQRT,TAN,COS

SUBSTRING IS NOT NULL, IS NULL

UPPER ROUND, POWER

Date Time

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Function SQL Server
Date addition DATEADD
Date subtraction DATEDIFF
Last day of month N/A
Time zone conversion N/A
First weekday after date N/A
Convert date to string DATENAME
Convert date to number DATEPART
Convert string to date CAST
Get current date and time GETDATE()

SQL Join

The SQL JOIN refers to using the JOIN keyword in a SQL statement in order to query data from
two tables.

When you perform a SQL join, you specify one column from each table to join on. These two
columns contain data that is shared across both tables.

You can use multiple joins in the same SQL statement to query data from as many tables as you
like.

Join Types

Depending on your requirements, you can do an "inner" join or an "outer" join. These are
different in a subtle way

 INNER JOIN: This will only return rows when there is at least one row in both tables that
match the join condition.
 LEFT OUTER JOIN (or LEFT JOIN): This will return rows that have data in the left
table (left of the JOIN keyword), even if there's no matching rows in the right table.
 RIGHT OUTER JOIN (or RIGHT JOIN): This will return rows that have data in the right
table (right of the JOIN keyword), even if there's no matching rows in the left table.
 FULL OUTER JOIN (or FULL JOIN): This will return all rows, as long as there's
matching data in one of the tables.

Join Syntax

Inner Join:
SELECT * FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Left Join:
SELECT * FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Right Join:
SELECT * FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Full Join:
SELECT * FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name

Example Inner Join Statement

SELECT * FROM Individual

INNER JOIN Publisher
ON [Link] = [Link]

Note: We could use table aliases instead of the full table name. This will keep our statement
shorter. For example:

SELECT * FROM Individual AS Ind

INNER JOIN Publisher AS Pub
ON [Link] = [Link]

Source Tables

Left Table

Id FirstName LastName UserName

1 Fred Flinstone freddo

2 Homer Simpson homey

3 Homer Brown notsofamous

4 Ozzy Ozzbourne sabbath

5 Homer Gain noplacelike

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Right Table

IndividualId AccessLevel

1 Administrator

2 Contributor

3 Contributor

4 Contributor

10 Administrator

SQL Inner Join statement

SELECT * FROM Individual INNER JOIN Publisher ON [Link] =
[Link] WHERE [Link] = '2'

Result
IndividualId FirstName LastName UserName IndividualId AccessLevel

2 Homer Simpson homey 2 Contributor

Left Outer Join

Use this when you only want to return rows that have matching data in the left table, even if
there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind LEFT JOIN Publisher AS Pub
ON [Link] = [Link]

Result
IndividualId FirstName LastName UserName IndividualId AccessLevel

1 Fred Flinstone freddo 1 Administrator

2 Homer Simpson homey 2 Contributor

3 Homer Brown notsofamous 3 Contributor

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
4 Ozzy Osbourne sabbath 4 Contributor

5 Homer Gain noplacelike NULL NULL

Right Outer Join

Use this when you only want to return rows that have matching data in the right table, even if
there's no matching rows in the left table.

Example SQL statement

SELECT * FROM Individual AS Ind RIGHT JOIN Publisher AS Pub ON
[Link] = [Link]

Result
IndividualId FirstName LastName UserName IndividualId AccessLevel

1 Fred Flinstone freddo 1 Administrator

2 Homer Simpson homey 2 Contributor

3 Homer Brown notsofamous 3 Contributor

4 Ozzy Osbourne sabbath 4 Contributor

NULL NULL NULL NULL 10 Administrator

Full Outer Join

Use this when you want to all rows, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind FULL JOIN Publisher AS Pub
ON [Link] = [Link]

Result
IndividualId FirstName LastName UserName IndividualId AccessLevel

1 Fred Flinstone freddo 1 Administrator

2 Homer Simpson homey 2 Contributor

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

3 Homer Brown notsofamous 3 Contributor

4 Ozzy Osbourne sabbath 4 Contributor

5 Homer Gain noplacelike NULL NULL

NULL NULL NULL NULL 10 Administrator

SQL Insert

SQL statement
INSERT INTO Individual VALUES ('6', 'Benny', 'Hill', 'hillbenny')

Result

Now if we do a SELECT on the Individual table, we can see the new record added to the bottom
of the result set.

IndividualId FirstName LastName UserName

1 Fred Flinstone freddo

2 Homer Simpson homey

3 Homer Brown notsofamous

4 Ozzy Ozzbourne sabbath

5 Homer Gain noplacelike

6 Benny Hill hillbenny

SQL Update

SQL statement
UPDATE Individual SET UserName = 'funnyman' WHERE IndividualId = '6'

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Result

Now if we select this record, we can see the updated value.

IndividualId FirstName LastName UserName

6 Benny Hill funnyman

Updating Multiple Fields

To update multiple fields, separate each field assignment with a comma.

SQL statement
UPDATE Individual
SET UserName = 'getserious', FirstName = 'Onetree'
WHERE IndividualId = '6'

Result
IndividualId FirstName LastName UserName

6 Onetree Hill getserious

SQL Delete

SQL statement
DELETE FROM Individual WHERE IndividualId = '6'

Result

Now if we select all records from the table, we see that record 6 has been deleted.

IndividualId FirstName LastName UserName

1 Fred Flinstone freddo

2 Homer Simpson homey

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
3 Homer Brown notsofamous

4 Ozzy Ozzbourne sabbath

5 Homer Gain noplacelike

SQL Functions

SQL has a number of functions to assist you in your database programming.

Functions are a self contained script/program built for a specific purpose. Generally, the value
returned by a function will depend on the context in which it is being used. Often, a SQL
function will be used within a query and this is what provides it with it's context.

Transact-SQL provides 3 different types of functions:

Rowset These return an object that can be used in place of a table reference in a SQL
Functions statement
Aggregate Perform a calculation on a set of values and return a single value. Aggregate
Functions functions can be used in the following:
 The select list of a SELECT statement
 A COMPUTE or COMPUTE BY clause

A HAVING clause

Scalar Functions These return a single value from a single value. Scalar functions are
categorized as follows:
 Configuration Functions
 Cursor Functions

 Date and Time Functions

 Mathematical Functions
 Metadata Functions
 Security Functions
 String Functions
 System Functions

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
 System Statistical Functions

 Text and Image Functions

On top of these functions, different database vendors have their own built-in functions for their
products. Also, most products enable programmers to program their own User Defined
Functions. For information on proprietary functions, you should consult the vendor's
documentation.

SQL Count

COUNT(*)

If we only want to see how many records are in a table (but not actually view those records), we
could use COUNT(*). COUNT(*) returns everything - including null values and duplicates.

SQL statement
SELECT COUNT(*) FROM Individual

Result
6

COUNT(column name)

If we want to see how many non-null values are in a given column, we use COUNT(column
name) where column name is the name of the column we want to test.

SQL statement
SELECT COUNT(LastName) FROM Individual

Result
5

Combining COUNT & DISTINCT

If we only want to see how many unique names are in the table, we could nest the DISTINCT
inside a COUNT function.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
SQL statement
SELECT COUNT(DISTINCT(FirstName)) FROM Individual

Result
4

SQL Server - Programming

A Simple PL/SQL Block:
Each PL/SQL program consists of SQL and PL/SQL
statements which from a PL/SQL block.

A PL/SQL Block consists of three sections:

 The Declaration section (optional).

 The Execution section (mandatory).

 The Exception (or Error) Handling section (optional).

Declaration Section:
The Declaration section of a PL/SQL Block starts with the
reserved keyword DECLARE. This section is optional and is
used to declare any placeholders like variables, constants,
records and cursors, which are used to manipulate data in
the execution section. Placeholders may be any of
Variables, Constants and Records, which stores data
temporarily. Cursors are also declared in this section.

Execution Section:
The Execution section of a PL/SQL Block starts with the
reserved keyword BEGIN and ends with END. This is a
mandatory section and is the section where the program
logic is written to perform any task. The programmatic
constructs like loops, conditional statement and SQL
statements form the part of execution section.

Exception Section:
The Exception section of a PL/SQL Block starts with the

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
reserved keyword EXCEPTION. This section is optional. Any
errors in the program can be handled in this section, so
that the PL/SQL Blocks terminates gracefully. If the PL/SQL
Block contains exceptions that cannot be handled, the
Block terminates abruptly with errors.

Every statement in the above three sections must end with

a semicolon ; . PL/SQL blocks can be nested within other
PL/SQL blocks. Comments can be used to document code.

This is how a sample PL/SQL Block looks.

DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;

SQL Server - Views

In SQL Server, a view is a pre-written query that is stored on the database. A view consists of a
SELECT statement, and when you run the view, you see the results of it like you would when
opening a table. Some people like to think of a view as a virtual table. This is because a view can
pull together data from multiple tables, as well as aggregate data, and present it as though it is a
single table.

Benefits of Views

A view can be useful when there are multiple users with different levels of access, who all need
to see portions of the data in the database (but not necessarily all of the data). Views can do the
following:

 Restrict access to specific rows in a table

 Restrict access to specific columns in a table
 Join columns from multiple tables and present them as though they are part of a single
table
 Present aggregate information (such as the results of the COUNT function)

Accessing Views
Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Any view that you create ends up being located under the "Views" folder of your database.

The following screenshot shows a number of views that are located within the
"AdventureWorks2008" database:

Creating a View

You create a view by using the CREATE VIEW statement, followed by the SELECT statement.

CREATE VIEW ViewName AS

SELECT ...

Example:

CREATE VIEW "Alphabetical list of products" AS

SELECT Products.*, [Link]
FROM Categories INNER JOIN Products ON [Link] =
[Link]
WHERE ((([Link])=0))

Modifing a View

You can modify an existing view by using using ALTER instead or CREATE.

Example:

ALTER VIEW "Alphabetical list of products" AS

SELECT Products.*, [Link]
FROM Categories INNER JOIN Products ON [Link] =
[Link]
Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
WHERE ((([Link])=0))

You can also right click on the view and select "Design".

Running a View

You run a view by using a SELECT statement.

SELECT TOP 1000 *

FROM [AdventureWorks2008].[Sales].[vIndividualCustomer]

You can also right-click on the view and select "Select Top 1000 Rows".

Running the above view results in this:

As you can see, it looks just like you've selected rows from a table. The difference is that, each
column could potentially be coming from a different table.

Stored procedures
Stored procedures are a powerful part of SQL Server. They can assist programmers and
administrators greatly in working with the database configuration and its data.

A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the

database (under the "Stored Procedures" node). Programmers and administrators can execute
stored procedures either from the SQL Server Management Studio or from within an application
as required.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Transact-SQL, which is based on SQL (Structured Query Language), is the programming
language used to interface between applications and their databases. Transact-SQL is a relatively
easy language to learn and I highly recommend becoming familiar with it.

Benefits of Stored Procedures

Here are some key benefits in using stored procedures:

Benefit Explanation of benefit

Modular  You can write a stored procedure once, then call it from multiple places in
programming your application.
 Stored procedures provide faster code execution and reduce network
traffic.
 Faster execution: Stored procedures are parsed and optimized as soon as
they are created and the stored procedure is stored in memory. This means
that it will execute a lot faster than sending many lines of SQL code from
your application to the SQL Server. Doing that requires SQL Server to
compile and optimze your SQL code every time it runs.
Performance
 Reduced network traffic: If you send many lines of SQL code over the
network to your SQL Server, this will impact on network performance.
This is especially true if you have hundreds of lines of SQL code and/or
you have lots of activity on your application. Running the code on the SQL
Server (as a stored procedure) eliminates the need to send this code over
the network. The only network traffic will be the parameters supplied and
the results of any query.
 Users can execute a stored procedure without needing to execute any of
the statements directly. Therefore, a stored procedure can provide
Security advanced database functionality for users who wouldn't normally have
access to these tasks, but this functionality is made available in a tightly
controlled way.

Creating a Stored Procedure

You create stored procedures in the SQL Server Management Studio using the CREATE
PROCEDURE statement, followed by the code that makes up the stored procedure.

CREATE PROCEDURE StoredProcedureName AS

...

The following code creates a stored procedure called "MyStoredProcedure":

CREATE PROCEDURE MyStoredProcedure AS

SET ROWCOUNT 10
SELECT [Link] AS TenMostExpensiveProducts, [Link]

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
FROM Products
ORDER BY [Link] DESC

Once you run this code in the SQL Server Management Studio, the stored procedure is created
and appears under the "Stored Procedures" node.

Modifying a Stored Procedure

If you need to modify an existing stored procedure, you simply replace the CREATE with
ALTER.

ALTER PROCEDURE MyStoredProcedure AS

...

Running a Stored Procedure

You can run a stored procedure by using EXECUTE or EXEC. For example, to run the above
stored procedure, type the following:

EXEC MyStoredProcedure

If the stored procedure has spaces in its name, enclose it between double quotes:

EXEC "My Stored Procedure"

If your stored procedure accepts any parameters, they are placed after the procedure name:

EXEC MyStoredProcedure @ParameterName="MyParameter"

So, here's an example:

EXEC SalesByCategory @CategoryName ="Beverages"

Using the GUI

You can also use the graphical user interface to initiate the execution of a stored procedure.

To initiate a stored procedure this way:

1. Navigate to the stored procedure

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
2. Right click on the stored procedure and select "Execute Stored Procedure...":

3. A dialog will appear. Enter your chosen parameter values etc:

4. Click "OK"

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
5. SQL Server will now generate the SQL code and execute the stored procedure:

Parameters

A parameter is a value that your stored procedure uses in order to perform it's task. When you
write a stored procedure, you can specify the parameters that need to be supplied from the user.
For example, if you write a stored procedure to select the address details about an individual,
your stored procedure needs to know which individual to select. In this case, the user can provide
an IndividualId or UserId to tell the stored procedure which individual to look up.

System Stored Procedures

SQL Server includes a large number of system stored procedures to assist in database
administration tasks. Many of the tasks you can perform via SQL Server Management Studio can
be done via a system stored procedure. For example, some of the things you can do with system
stored procedures include:

 configure security accounts

 set up linked servers
 create a database maintenance plan
 create full text search catalogs
 configure replication
Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
 set up scheduled jobs
 and much more.

Indexes
Every organization has its database and each and every day with the increase in the data volume
these organizations has to deal with the problems relating to data retrieval and accessing of data.
There is need of system which will results into increase in the data access speed. An index (in
simple words it like index of any book eg. While searching a word in Book we use index back of
book to find the occurance of that word and its relevant page numbers), which makes it easier for
us to retrieval and presentation of the data. An Index is a system which provides faster access to
rows and for enforcing constraints.

If we don't create any indexes then the SQL engine searches every row in table (also called as
table scan). As the table data grows to thousand, millons of rows and further then searching
without indexing becomes much slower and becomes expensive.

eg. Following query retrieves Customer information where country is USA from Customers table
of the Northwind database.
SELECT CustomerID,ContactName,CompanyName,City
FROM Customers
WHERE Country ='USA'

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

As there is no Index on this table, database engine performs table scan and reads every row to
check if Country is "USA". The query result is shown below. Database engine scans 91 rows and
find 13 rows.

Indexes supports to the database Engine. Proper indexing always results in considerable increase
in performance and savings in time of an application and vice-versa. When SQL Server process a
query then it uses Indexes to find the data. Indexes can created on one or more columns as well
as on XML columns also. We can create Index by selecting one or more columns of a table being
searched. Index creates model related with the table/view and constraints created using one or
more columns. It is more likely a Balanced Tree. This helps the SQL Server to find out rows
with the keys specified.

Indexes may be either Clustered or Non-Clustered.

Clustered Index

Every table can have one and only Clustered Index because index is built on unique key columns
and the key values in data rows is unique. It stores the data rows in table based on its key values.
Table having clustered index also called as clustered table.

Non-Clustered Index
Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
It has structure different from the data rows. Key value of non clustered index is used for
pointing data rows containing key values. This value is known as row locator. Type of storage of
data pages determines the structure of this row Locator. Row locator becomes pointer if these
data pages stored as a heap. As well as row locator becomes a clustered index key if data page is
stored in clustered table.

Both of these may be unique. Wherever we make changes to the data table, managing of indexes
is done automatically.

SQL Server allows us to add non-key column at the leaf node of the non clustered index by
passing current index key limit and to execute fully covered index query.

Automatic index is created wherever we create primary key, unique key constraints to table.

The Query Optimizer

Query Optimizer indexes to reduce operations of disk input-output and using of system resources
when we fire query on data. Data manipulation Query statements (like SELECT, DELETE OR
UPDATE) need indexes for maximization of the performance. When Query fires the most
efficient method for retrieval of the data is evaluated among available methods. It uses table
scans or index scans.

Table scans uses many Input-output operations, it also uses large number of resources as all rows
from the table are scanned.

Index scan used for searching index key columns to find storage location.
The index containing fewer columns results in to faster query execution and vice-versa.

Creating an Index

 Connect to Northwind database from Object Explorer, right click on the Customers table
to create an index and click on modify.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

 Click on Index/Keys from Table Desinger Menu on top or right click on any column
and click on Index/Keys.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
 Click on Add from Indexes/Keys dialog box.

 From Selected Primary/Unique Key or Index list, select the new index and set
properties for the index in the grid on right hand side.

 Now just specify other settings if any for the index and click Close.
 When we save the table, the index is created in the database.

We also create this index by using query. This command mentions the name of index (Country)
the table name (Customers), and the column to index (Country).

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
CREATE INDEX Country ON Customers (Country)

Creating Unique Index

SQL Server permits us to create Unique Indexes on columns which are unique to identify. (like
employee’s Reference ID, Email-id etc.) We use set of columns to create unique index.
 Right-click on the Customers and click Modify in Object Explorer.
 Now, click on Indexes/Keys from Table Designer menu and click on Click Add.

 The Selected Primary/Unique Key or Index list displays the automatically generated
name of the new index.
 In the grid, click on Type, from the drop-down list, Choose Index.
 Under Column name,we can choose columns we want to index and click on OK.
Maximum we can setup 16 columns. For optimum performance, it is recommended that
we use one or two columns per index. For every column we values of these columns are
arranged in ascending or descending order.
 In the grid, click Is Unique and select select Yes.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

 Null is treated as duplicate values. So, it is not possible to create unique index on one
column if it contains null in more than one row. Likewise index cannot be created on
multiple columns if those columns contains null in same row.
 Now select Ignore duplicate keys option. If it is required to ignore new or updated data
that will lead to creation of duplicate key in the index (with the INSERT or UPDATE
statement).
 When we save the table, the index is created in the database.

We also create this index by using query. This command mentions the name of index
(ContactName) the table name (Customers), and the column to index
(CompanyName,ContactName).
CREATE UNIQUE INDEX ContactName ON Customers (CompanyName,ContactName)

Creating Clustered Index

table can have only one clustered index. In Clustered index logical order and physical order of
the index key values is identical of rows in the table.

 In the Object Explorer click on the Northwind database, right click on the Customres to
create an index and click on modify.
 Now we have Table Designer for the table.
 From the Table Designer menu, click Indexes/Keys and from Indexes/Keys dialog box,
click Add.
 Now from Selected Primary/Unique Key or Index list, Select the new index
Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
 In the grid, select Create as Clustered, and choose Yes from the drop-down list to the
right of the property.

 When we save the table, the index is created in the database.

We also create this index by using query. This command mentions the name of index
(PK_Customers) the table name (Customers), and the column to index (CustomerID).
CREATE CLUSTERED INDEX PK_Customers on Customers(CustomerID)

Creating Full Text Search

For text based columns, full text search is always required to be performed under several times.
In such situations full text index is used. A regular index is required to be prepared before
creating full text index as the later relies on the former. Regular index is created on single
column having not null. It is recommended to create regular index on column having small
values. For several occasions, SQL Server management Studio is also used to create catalog.

 In the object explorer click on the Northwind database, right click on the customers to
create an index and click on modify.
 Now, we have Table Designer for the customers table and then Click Fulltext Index
from the Table Designer menu.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

 Dialog box for full-text index opens. (Sometimes database is not enabled for full text
indexing. In such situations add button disabled. To enable it check properties for
database by right clicking on database. And check the full text indexing check box)
 Now we have to right click on storage>New Full-Text catalog to create a catalog. Enter
some required information in dialog box.

 Now from Table Designer menu, open the Full Text Index property dialog and then
click on Add.
 Now select new index from selected full-text index list and assign properties for index in
the grid.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

 When we save Table the index is automatically saved in database, and this index is
available for modifications.

Changing index properties

 Connect to the SQL-Server 2005, In the object explorer click on the Northwind database.
 Click Indexes/Keys from the table designer menu.
 Now select index from the selected primary/unique key or index list. And Change the
properties.
 When we save Table the index is automatically saved in database.

Renaming an Index

 Right-click the table with the index you want to rename and click Modify, in Object
Explorer.
 Click Indexes/Keys from the Table Designer menu.
 Now from the Selected Primary/Unique Key or Index list, select the index.
 Click Name and type a new name into the text box in the grid.
 When we save Table the index is automatically saved in database.

We can also rename indexes with the sp_rename stored procedure. The sp_rename
procedure takes, at a minimum, the current name of the object and the new name for the

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
object. While renaming indexes, the current name must include the name of the table, a
dot separator, and the name of the index, as shown below:

EXEC sp_rename '[Link]', 'Countries'

Deleting an Index

 Right-click the table with indexes you want to delete and click Modify In Object
Explorer.
 Click Indexes/Keys from the Table Designer menu.
 Select the index you want to delete from the Indexes/Keys dialog box and Click on
Delete.
 When we save Table the index is deleted from the database.

We can follow same procedure for deleting a Full text index. From the Table Designer select
Full text index and then select the index name and click on delete.

It is very sensible to remove index from database if it is not much of worth. eg. For instance, if
we know the queries are no longer searching for records on a speicific column, we can remove
the index. Unneeded indexes only take up storage space and diminish SQL command is shown
below.
DROP Index Customers. Country

Specifying Fill Factor

Fill Factor Fill factor is used by SQL Server to specify how full each page index. The fill factor
is the percentage of allotted free space to an index. We can specify the amount of space to be
filled. It is very important as the improper selection my slow down the performance.

 Right-click the table with an index for which we want to specify fill factor and click
Modify in Object Explorer
 Click Indexes/Keys, from the Table Designer menu.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
 From Selected Primary/Unique Key or Index list, select the index.
 Type a number from 0 to 100, in the Fill Factor box. Value 100 denotes that index will
fully filled up and storage space requirement will be minimum, this is recommended in
situations where there are minimum changes of change in data. data fill factor. If there is
regular modification and addition to the data, then set this value to minimum. Storage
space is proportionate to the value set.

Creating XML Index

There somewhat different way to create XML indexes, we cannot create XML using Index/Keys
dialog box. We create XML index from xml data type columns those are based on primary XML
index. When we delete the primary XML index, then all the XML index will be deleted.

 In Object Explorer, right-click the customers table to create an XML index and click
Modify.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

 Select the xml column for the index for the table opened in Table Designer.
 From the Table Designer menu, click XML Index,
 Click on add, in the XML Indexes dialog box

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Deleting XML Indexes

 Right-click the customers table with the XML index you want to delete and click Modify
in Object Explorer.
 click on XML Index, from the Table Designer menu.
 From selected XML Index column, Click the index you want to delete. And then Click
on Delete.

Viewing Existing Indexes

We can view list of all indexes on a table in the dialog box we used to create an index. Just Click
on the Selected index drop down control and scroll through the available indexes.

We can use a stored procedure named sp_helpindex. This stored procedure gives all of the
indexes for a table with its all relevant attributes. The only input parameter to the procedure is
the name of the table, as shown below.
EXEC sp_helpindex Customers

How Index works

The columns specified in the CREATE INDEX COMMAND taken by the database engine and
sorts the values in Balanced Tree(B-Tree) data structure. B-Tree structure supports faster search
with minimum dist reads, and allows the database engine to find quick start and end point for the
stated query.

The database takes the columns specified in a CREATE INDEX command and sorts the values
into a special data structure known as a B-tree. A B-tree structure supports fast searches with a
minimum amount of disk reads, allowing the database engine to quickly find the starting and
stopping points for the query we are using.

Conceptually, every index entry has the index key. Each entry also includes a references to the
table rows which share that particular value and from which we can retrieve the required
information.

It is much similar to the back of a book helps us to find keywords quickly, so the database is able
to quickly narrow the number of records it must examine to a minimum by using the sorted list
of Key values stored in the index. Thus we avoid a table scan to fetch the query results.
Following some of the scenarios where indexes offer a benefit. Advantages of Indexing

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

Searching For Records

The most important use for an index is in finding a record or set of records matching a WHERE
clause. Indexes can help queries with speicfic range. as well as queries looking for a specific
value. E.g the following queries can all benefit from an index on UnitPrice:
DELETE FROM Customers WHERE Country = "USA"
UPDATE Customers SET Region = "Pacific" WHERE Country = "USA"
SELECT * FROM Customers WHERE Country="USA" or "Brasil"

Indexes work well when searching for a record in DELETE and UPDATE commands as they do
for SELECT statements.

Sorting Records

When we require sorted results, the database tries to find an index and avoids sorting the results
while execution of the query. We control sorting of a dataset by specifying a field, or fields, in an
ORDER BY clause, with the sort order as ascending (ASC) or descending(DESC). E.g. Query
below returns all customers sorted by Country:
SELECT * FROM Customers ORDER BY Country ASC

When there is no indexes, the database will scan the Customers table and then sort the rows to
process the query. However, the index we created on Country (Country) before will provide the
database with a already sorted list of Countries. The database can simply scan the index from the
first record to the last record and retrieve the rows in sorted order. The same index works same
with the following query, It simply scans the index in reverse.
SELECT * FROM Customers ORDER BY Country DESC

Grouping Records

We can use a GROUP BY clause to group records and aggregate values, e.g. for counting the
number of customers in a country. To process a query with a GROUP BY clause, the database
will quite ofen sort the results on the columns included in the GROUP BY. The following query
counts the number of customers from every country with the same UnitPrice value.
SELECT Count(*) FROM Products GROUP BY UnitPrice

Index Drawbacks

There are few drawbacks of indexes. While indexes provide a substantial performance benefit to
searches, there is also a downside to indexing.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
Indexes and Disk Space

Indexes are stored on the disk, and the amount of space required will depend on the size of the
table, and the number and types of columns used in the index. Disk space is generally cheap
enough to trade for application performance, particularly when a database serves a large number
of users. To see the space required for a table, use the sp_spaceused system stored procedure in a
query window. EXEC sp_spaceused Customers

Result
name rows reserved data
index_size unused
--------- ----------- ------------------ ------------------
------------------
Customers 91 200 KB 24 KB 176 KB
0 KB

From the above output, the table data uses 24 kb, while the table indexes use about 18 times as
much, or 176 kilobytes. The ratio of index size to table size can vary greatly, depending on the
columns, data types, and number of indexes on a table.

Indexes and Data Modification

If the data is change or modified on regular intervals then database engine requires to update all
the indexes, thus too many indexes will slows down the performance. Thus Database used for
transaction processing should use fewer indexes to allow for higher throughput on insert and
updates. While in DSS (Decision Support System) and datawarehousing where information is
static and queries is required largely for the reporting purposes than the modification purposes
then heavy indexing is required to optimze the performance.

Another downside to using an index is the performance implication on data modification

statements. Any time a query modifies the data in a table (INSERT, UPDATE, or DELETE), the
database needs to update all of the indexes where data has changed. As we discussed earlier,
indexing can help the database during data modification statements by allowing the database to
quickly locate the records to modify, however, we now caveat the discussion with the
understanding that providing too many indexes to update can actually hurt the performance of
data modifications. This leads to a delicate balancing act when tuning the database for
performance.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

SQL Server - User Logins

To Create a New User Login

1. Using SQL Server Management Studio, expand the "Security" option and right click on
"Logins"
2. Click on "New Login"

3. Complete the login properties in the "General" tab by providing a name for the login,
choosing the Authentication method (providing a password if you choose "SQL Server
authentication"), and selecting the database to use as a default. If you don't choose a
language, it will use the default for the current installation of SQL Server.

If you get an error that reads "The MUST_CHANGE option is not supported by this
version of Microsoft Windows", simply uncheck the "User must change password at next
login" option. The error occurs because your operating system doesn't support this option.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

4. Click the "Server Roles" tab if you need to apply any server-wide security privileges.

5. Click the "User Mapping" tab to specify which databases this user account is allowed to
access. By default, the login will be assigned to the "Public" role, which provides the
login with basic access. If the login needs more access in one or more databases, it can be
assigned to another role with greater privileges.

Note that these roles are "Database Roles" and are different to the server roles in the
previous tab. Server roles are for administering the SQL Server. Database roles are
created within each database and specify what the login can do within that database.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

SQL Server - Server Roles

When creating a new user login in SQL Server, you get the option of assigning the login one or
more server roles.

Server roles (not to be confused with database roles) are available for various database
administration tasks. Not everyone should be assigned to a server role. In fact, only advanced
users such as database administrators should be assigned a server role.

Accessing the Server Roles

To access the server roles in SQL Server Management Studio, expand the Security folder:

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

You view the properties of a server role by right clicking on it. You can then add users to the
server role by clicking Add. In the screenshot below, Homer has been added to the securityadmin
role.

Explanation of Server Roles

Here's an explanation of the server roles defined in SQL Server 2008 during setup:

Server Role Description

sysadmin Can perform any task in SQL Server.
serveradmin Can set server-wide configuration options, can shut down the server.
Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
setupadmin Can manage linked servers and startup procedures.
securityadmin Can manage logins and database permissions, read logs, change passwords.
processadmin Can manage processes running in SQL Server.
dbcreator Can create, alter, and drop databases.
diskadmin Can manage disk files.
bulkadmin Can execute BULK INSERT statements.
Every SQL Server user account belongs to this server role. When a server
principal has not been granted or denied specific permissions on a securable
public object, the user inherits the permissions granted to public on that object. Only
assign public permissions on an object when you want the object to be available to
all users.

As you can see, some of these roles allow very specific tasks to be performed. If you don't have
many technical users, it's likely that you'll only use one or two of these roles (including
sysadmin).

SQL Server - Database Schemas

Ever since SQL Server 2005 was released, each object in a database has belonged to a database
schema. SQL Server 2008 has continued with database schemas, and an explanation follows.

What is a Database Schema?

A database schema is a way to logically group objects such as tables, views, stored procedures
etc. Think of a schema as a container of objects.

You can assign a user login permissions to a single schema so that the user can only access the
objects they are authorized to access.

Schemas can be created and altered in a database, and users can be granted access to a schema. A
schema can be owned by any user, and schema ownership is transferable.

Creating a Database Schema

To create a database schema in SQL Server 2008:

1. Navigate to Security > Schemas

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
2. Right click on Schemas and select New Schema.... Like this:

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
3. Complete the details in the General tab for the new schema. In this example, the schema
name is "person" and the schema owner is "Homer".

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
4. Add users to the schema as required and set their permissions:

5. Add any extended properties (via the Extended Properties tab)

6. Click OK.

Add a Table to the New Schema

Now that we have a new schema, we can add objects such as tables, views, and stored
procedures to it. For example, we could transfer the table that we created in the earlier lesson to
the new schema.

When we created that table (called "Individual"), it was created in the default database schema
("dbo"). We know this because it appears in our object browser as "[Link]".

To transfer the "Individual" table to the person "schema":

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
1. In Object Explorer, right click on the table name and select "Design":

2. From Design view, press F4 to display the Properties window.

3. From the Properties window, change the schema to the desired schema:

4. Close Design View by right clicking the tab and selecting "Close":

5. Click "OK" when prompted to save

Your table has now been transferred to the "person" schema.

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008

Confirm your Change

To confirm the change:

1. Refresh the Object Browser view:

2. You will now see that Object Browser displays the new schema for the table
([Link]):

SQL Server - Linked Servers

If you've been navigating around SQL Server Management Studio (SSMS), you may have come
across the Linked Servers option.

The Linked Servers option allows you to connect to another instance of SQL Server running on a
different machine, perhaps remotely in a different city/country. This can be useful if you need to
perform distributed queries (query a remote database). Setting up a linked server is quite straight
forward in SSMS, all you need is details of the remote server, and the database that you need to
query.

Creating a Linked Server

To create a linked server:

1. Navigate to Server Objects > Linked Servers

Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
2. Right click on Linked Servers and select New Linked Server.... Like this:

3. Complete the details for the linked server. In this example, Data source refers to the
name of the SQL Server machine ("Barts_database_server"), Catalogue refers to the
name of the database ("Barts_database"). You can also configure options in the other two
tabs depending on your requirements.

Distributed Queries
Once you have configured your linked server, you will be able to run queries etc against it. When
you run a query against a linked server, it is referred to as a distributed query.

When you execute a distributed query against a linked server, you must include a fully qualified,
four-part table name for each data source to query. This four-part name should be in the form
linked_server_name.[Link].object_name.

Here's an example:

Barts_database_server.Barts_database.[Link]

This example is based on the linked server example above. It assumes that the remote database
has a schema called "Person" and a table called "Enemy".
Genius Academy Bhrugesh Thakkar

34
 SQL SERVER 2008
SQL Server Integration Services (SSIS)
SQL Server Integration Services (SSIS) allows you to integrate smoothly with other applications
and data sources.

You can use Integration Services to copy or download files, send e-mail messages in response to
events, update data warehouses, clean and mine data, and manage SQL Server objects and data.

In SQL Server 2005 and higher, Data Transformation Services (DTS) is no longer installed with
SQL Server. All DTS related tasks are now performed with SSIS.

Where Has DTS Gone?

If you have been working with earlier versions of SQL Server (SQL Server 2000 and earlier),
you are probably familiar with DTS. DTS allowed you to transfer data between disparate sources
into one or more destinations.

Well, starting from SQL Server 2005, DTS has been deprecated. DTS is no longer included with
your SQL Server installation.

If you need DTS-type functionality in SQL Server 2008, you will need to use SQL Server
Integration Services.

Backwards Compatibility

For backwards compatibility, Microsoft has provided installation files that provide run-time
support for DTS packages. This will be useful if you have already created DTS packages in an
earlier version of SQL Server. For more information see Support for Data Transformation
Services (DTS) in SQL Server 2008 on the MSDN website.

SQL Server Express & SSIS

SQL Server Integration Services is not available in the Express or Workgroup editions of SQL
Server. If you need to use Integration Services, you need at least SQL Server Standard edition.
And, if you need to use advanced features (such as data mining, text mining and cleansing or
fuzzy transforms), you will need SQL Server Enterprise edition.

Genius Academy Bhrugesh Thakkar

34