SQL Server 2000

Databases

A database is a storage place for data. For most types of data files, a database does not present information directly to a user; rather, the user runs an application that accesses data from the database and presents it to the user in an understandable format.

Relational Databases

Although there are different ways to organize data in a database, a relational database is one of the most effective systems. A relational database system uses mathematical set theory to effectively organize data. In a relational database, data is collected into tables (called relations in relational database theory).

Table: A two-dimensional object consisting of rows and columns that is used to store data in a relational database. Each table stores information about one of the types of objects modeled by the database. For example, an education database might have one table for teachers, a second for students, and a third for classes.

Functions

A function encapsulates frequently performed logic in a subroutine made up of one or more Transact-SQL statements. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic.

SQL Server2000 supports two types of functions:

Built-in functions. These functions operate as defined in Transact-SQL and cannot be modified. The functions can be referenced only in Transact-SQL statements.

User-defined functions. These functions enable you to define your own Transact-SQL functions by using the CREATE FUNCTION statement.

Built-In Functions

The Transact-SQL programming language contains three types of built-in functions: rowset, aggregate, and scalar.

Data Types

A data type is an attribute defining the type of data that an object can contain. Columns, parameters, variables, functions that return data values and stored procedures that have a return code all have data types. Transact-SQL includes a number of base data types, such as varchar, text, and int.

Types of User-Defined Functions

The Transact-SQL programming language supports two types of user-defined functions: scalar and table.

Scalar Functions.

Scalar functions return a single data value of the type defined in a RETURNS clause. You can use all scalar data types, including bigint and sql_variant. The timestamp data type, user-defined data type, and non-scalar types (such as table or cursor) are not supported. The body of the function, defined in a BEGIN...END block, contains the series of Transact-SQL statements that return the value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Table Functions

Table functions return a table. There are two types of table functions: inline and multi-statement. For an inline table function, there is no function body; instead, the table is the result set of a single SELECT statement. For a multi-statement table function, the function body, defined in a BEGIN...END block, contains the Transact-SQL statements that build and insert rows into the table that will be returned.

Inner join: The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed.

Left join: The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed.

Right join: The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed.

Outer join: it may be desirable to include rows from one table even if it does not have matching rows in the other table. This is done by the use of an OUTER JOIN. OUTER JOINs are used when we want to keep all the rows from the one table, such as Course, or all the rows from the other, regardless of whether they have matching rows in the other table.

Indexers:

It is the job of the query optimizer to determine which indexes will be the most useful in processing a specific query. Although indexes may speed up queries in large tables, indexes will slow update operations (insert, delete, update), because every update causes a rebuild of the index. We begin by introducing the "simple" CREATE INDEX statement.

Stored procedure: A stored procedure is a group of Transact-SQL statements that is compiled one time and that can then be executed many times. This functionality increases performance when the stored procedure is executed because the Transact-SQL statements do not have to be recompiled.

CREATE PROCEDURE sp_GetInventory
@location varchar (10)
AS
SELECT Product, Quantity
FROM Inventory
WHERE Warehouse = @location

EXECUTE sp_GetInventory 'FL'

Triggers: A trigger is a special type of stored procedure that a user does not call directly. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.

Why triggers? – to extend data integrity and to implement complex business logic.

Two classes of triggers:

1) Instead of – this bypass the triggering action and run in their place.

2) After

– default trigger class

– After trigger fire as a supplement to the triggering actions.

– Move then one allowed on a table.

Syntax:

Create Trigger trigger_name

ON table_name or view_name

FOR trigger_class and trigger_types

AS T-SQL statement

Constraints

Constraints enable us to define the way that SQL Server automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using triggers, rules, or defaults.

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database. The following categories of the data integrity exist:

· Entity Integrity

· Domain Integrity

· Referential integrity

· User-Defined Integrity

· Entity Integrity ensures that there are no duplicate rows in a table.

Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.
Referential integrity ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital).
User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.

Each of these categories of the data integrity can be enforced by the appropriate constraints. Microsoft SQL Server supports the following constraints:

· PRIMARY KEY

· UNIQUE

· FOREIGN KEY

· CHECK

· NOT NULL

Rules

Rules are a backward-compatibility feature that performs some of the same functions as CHECK constraints. CHECK constraints are the preferred, standard way to restrict the values in a column. CHECK constraints are also more concise than rules; there can be only one rule applied to a column, but multiple CHECK constraints can be applied. CHECK constraints are specified as part of the CREATE TABLE statement, while rules are created as separate objects and are then bound to the column.

User Defined functions:

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.

CREATE FUNCTION whichContinent
(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end

return @return
end

Index: INDEX is used to create a new index on some column in an existing table. The following is the general syntax for the CREATE INDEX statement:

CREATE INDEX index_name

ON Table_name (column [ASC | DESC])

There are clustered and nonclustered indexes.
 A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Views:

A database object that can be referenced the same way as a table in SQL statements. Views are defined by using a SELECT statement and are analogous to an object that contains the result set of this statement.

Data type:

An attribute that specifies what type of information can be stored in a column, parameter, or variable. SQL Server provides system-supplied data types; you can also create user-defined data types.

Union:

The UNION command is used to select related information from two tables, much like the JOIN command.

Drop Index

You can delete an existing index in a table with the DROP INDEX statement.

Connection Pooling:

Connection pooling is the ability of re-use your connection to the Database. This means if you enable Connection pooling in the connection object, actually you enable the re-use of the connection to more than one user.

The connection pooling is enabled by default in the connection object. If you disable the connection pooling, this means the connection object which you create will not be re-used to any other user than who create that object.

SqlConnection testConnection = new SqlConnection(@"Data Source=(local)\SQLEXPRESS;Initial Catalog=DEMO;Integrated Security=SSPI;");

long startTicks = DateTime.Now.Ticks;

for (int i = 1; i <= 100; i++)

{

testConnection.Open();

testConnection.Close();

}

long endTicks = DateTime.Now.Ticks;

Console.WriteLine("Time taken : " + (endTicks - startTicks) + " ticks.");

testConnection.Dispose();

Object Pooling:

Object pools (otherwise known as resource pools) are used to manage the object caching. A client with access to a Object pool can avoid creating a new Objects by simply asking the pool for one that has already been instantiated instead. Generally the pool will be a growing pool, i.e. the pool itself will create new objects if the pool is empty, or we can have a pool, which restricts the number of objects created.

It is desirable to keep all Reusable objects that are not currently in use in the same object pool so that they can be managed by one coherent policy. To achieve this, the Reusable Pool class is designed to be a singleton class.

Object Pooling is a service that is defined as "A performance optimization based on using collections of pre-allocated resources, such as objects or database connections." It is also mentioned that pooling results in more efficient resource allocation.

SQL: Standard computer language for accessing and manipulating database systems.

DML (Data Manipulation Language): select, update, delete & insert into.

DDL (Data Definition Language): Create table, alter table, drop table, create index, drop index/
IN: used when exact value we want to return for at least are of the column.

The SqlCommand class provides three main methods:

·ExecuteNonQuery Executes the command and returns the number of rows affected by the statement.

· ExecuteScalar Executes the command and returns a single value.

· ExecuteReader Builds a SqlDataReader that can be used to read a set of records.