SQL 2005 Videos

Click on the text to follow the link to the video, just in case if you find that link is expired please get back to me.

1. Importing XML Data
2. How to Replicate Databases in SQL 2005 Servers
   
3. Detaching and Attaching a Database in SQL Server 2005 Express
4. SQL Server Bulk Import Tutorial
5. How to view a SQL 2005 Error Log
6. SQL 2005 Reporting Services drilldown
   
7. How to Schedule Backup and Maintenance Plan in SQL 2005 Server
8. SQL Server Backup Types Explained
9. How to Backup A SQL Database
10. SQL Server 2005 - Get a list of Databases and DB Files
    

A good video tutorial on overall scenario on buisness intelligence.

Business Intelligence Demonstration

Locks

Locks can be applied at different levels

1. Database level(db)
2. Table level(tab)
3. Page level(page)
4. Row level(row)

SQL Server 2005 support different types of locking modes

1) Shared Lock(s):
A shared lock is used when you only need to read the data - that is you wont be changing anything, meaning Select statement [Read only] - Any number of transactions can apply shared lock on the data at the same time. Shared locks prevents users from performing dirty reads.

2)Exclusive Locks(x)
Exclusive locks are not compatible with any other lock, they cannot be achieved if any other lock exists, nor will they allow a new lock of any form to be created on the resource while the exclusive lock is still active, this prevents two people from updating, deleting or whatever at the same time.
A transaction applied exclusive lock on the data can perform any DML operation until that particular transaction is completed, no other transaction can access the same data.

3)Update Lock(u)
Update locks are hybrids, something between shared and exclusive locks and update locks are compatible only with shared locks and intent locks.
Update locks indicate that you have a shared lock that's going to become exclusive lock after you have done your initial scan of the data to figure out what exactly needs to be updated. This acknowledges the fact that there are two distinct stages to an update:

• First, the stage where you are going to figure out what meets the WHERE clause criteria(whats going to be updated). This is the part of an update query that has an update lock.
• Second, the stage where, if you actually decide to perform the update, the lock is upgraded to an exclusive lock, otherwise the lock is converted to a shared lock.

4)Intent Locks(i):
A transaction applying lock at high level and other transaction applying at low level is called intent lock, intent locks can be combined with other locks also.
Intent locks come in three different flavors:

• Intent shared lock: A shared lock has or is going to be established at some lower point in the hierarchy. For example, a page is about to have a page level shared lock established on it. This type of lock applied only to tables and pages.
• Intent exclusive lock: This is the same as intent shared, but with an exclusive lock about to be placed on the lower level item.
• Shared with intent exclusive lock: A shared lock has or is about to be established lower down the object hierarchy, but the intent is to modify data, so it will become an intent exclusive at some point.

5)Schema Locks:This comes in two flavors

• Schema modification lock(Sch-M): A schema change is being made to the object. No queries or other CREATE,ALTER or DROP statements can be run against this object for the duration of the Sch-M Lock.
• Schema Stability Lock(Sch-S):This is very similar to shared lock; This locks sole purpose is to prevent a Sch-M since there are already locks for other queries(or CREATE,ALTER,DROP statments) active on the object. This is compatible with all other lock types.

6)BULK Update Locks:
A bulk update lock (BU) is really just a variant of a table lock with one little(but significant) difference. Bulk update locks will allow parallel loading of data - that is, the table is locked from any other "normal"(T-SQL Statements) activity, but multiple BULK INSERT or Bcp operations can be performed at the same time.

PS:In order to monitor the locks information we can use "sp_lock" function.

Third party links

Please check out the links below for further reference on SQL Server 2005, Click on the text to follow the link.

Microsoft SQL Server 2008, 2005, 2000 and 7.0 Builds

How to install SQL Server 2005 Reporting services on a windows vista based computer

An introduction to SQL server 2005 Integration Services

Microsoft Excel Pivot Tables

Microsoft Groups

Cubes and Schemas

http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx#1

--------------------------------------------------

Definitions

INDEXS

An index is an object that exists only within the framework of a particular table or view. An index works much like a book index. An index provides us ways of speeding the lookup of desired information, indexes are of two types

Clustered:
There is one clustered index per table. If an index is clustered, it means that the table on which the clustered index is based is physically sorted according to that index. For instance if you were indexing a book, the clustered index would be the page numbers, the information in the book is stored in the order of the page numbers.

Non Clustered:
There can be more than one Non-Clustered index in a table. This kind of index points to some other value that will let you find the data. So for instance this we can consider in terms of our book , the keyword index at the back of the book.

PS:Views which have indexes or indexed views, must have at least one clustered index before it can have any non clustered index.

----------------------------

Triggers

A trigger is an object that exists only within the framework of a table. Triggers are a pieces of logical code that are automatically executed when certain things such as inserts, updates or deletes happens to your table.

Triggers can be used for great variety of things, but mainly used for either copying data as it is entered or checking the update to make sure that it meets some kind of criteria.

Or

Triggers are a special kind of stored procedure which will be executed automatically based on the user events (DDL or DML).

----------------------------

Savepoint

Savepoint is a named point that allows you to rollback the transaction upto a particular statement. Savepoint does not commit the operations and a transaction can have one or more save points.

Ex:
Begin transaction
statement1
statement2
statement3 (The statements from 1 to 4 will not roll back)
statement4
save transaction p1
statement5 ((The statements from 5 to 6 will roll back))
statement6

Rollback transaction p1
commit transaction.

-----------------------

Deadlock and Blocking
When a transaction conflicts with other transaction, then its called as deadlock, its usually known as 1205 error number.

or

Deadlock are caused when one lock cant do what it needs to do in order to clear because a second lock is holding that resource and vice versa. when this happens someone has to win the battle, so SQL sever chooses a deadlock victim. The dead lock victims transaction is rolled back and is notified that this happened through the 1205 error. The other transaction can continue normally.

Blocking means a transaction is waiting for resource of other transaction, blocking may lead to a chain of transactions.

PS:Blocking or deadlocks degrades the system performance.

-----------------------------

Constraints
A constraint can be defined as an object that exists only within the confined of a table. constraints confine the data into our table to meet certain conditions. constraints, in a way compete with triggers as possible solutions to data integrity issues.

---------------------------------

Views

A view is a virtual table, a view for the most part is used just like a table except the fact that it does not contain any data of its own. Instead, a view is merely a preplanned mapping and representaion of the data stored in tables. The plan is stored in the database in the form of a query. This query calls for a data from some, but not necessarily all, columns to be retrived from one or more tables. The data retrived may or may not be have to meet special criteria in order to be shown as data in that view.

• Views that reference multiple tables generally perform much faster with an indexed view because the join between the tables is preconstructed.
• Aggregations performed in the view are pre calculated and stored as part of the index; again this means that the aggregation is performed one time and then can be read directly from the index information.
• Inserts and deletes have higher overhead because the index on the view has to be updated immediately; updates also have higher overhead if the key column of the index is affected by the update.

Or

A logical structure created based on 1 or more tables

• A view contains only one select statement [stored query].
• View does not contain any rows in it.
• View is also known as virtual table.

In order to create and use views we have two steps
Step 1: Creating a view
Create View viewname
[with encryption]
as select .............. statement.

Step 2: Performing DML Operations through view
select * from viewname
insert viewname(colnames)
values(list of values)

delete from viewname
where

update viewname
set col1=newval
set col2=newval
where

• while performing DML operations through view rows will be effected in or more base tables.
• Performing operations through view also verifies the constraints in the tables
• View may be updatable view or non-updatable view

Updatable View:
If successful DML operations are performed through a view, then its called as Updatable view

Non-Updatable View:
If select operations are performed successfull through a view is called as non-updatable view.

---------------------------------------

STORED PROCEDURE
Stored procedures are ordered series of transact sql statments bundled up into a single logical unit. They allow for variables and parameters as well as selection and looping constructs. sprocs offer several advantages over just sending individual statements to the server in the sense that they

• Are reffered to using short names, rather than a long string of text, therefore less network traffic is required in order to run the code within sproc.
• Are pre-optimized and pre compiled, saving a small amount of time each time the sproc is run.
• Encapsulate a process, usually for security reasons or just to hide the complexity of the database.
• Can be called from other sprocs, making them reusable in a somewhat limited sense.

or

Stored procedures are a set of precompiled transact SQL statements stored in a database server.

There are two types of stored procedures

System stored Procedure:

• These are pre-defined and present in 'master' database.
  
• System procedures are used to perform operations with system tables as well as to perform administrative tasks.
• Every system procedure starts with 'Sp_' ex: sp_helpdb, sp_helptext,sp_depends.

User stored procedure:

User stored procedures are used to perform DML operations with user tables.
In order to work with user stored procedures we have two steps

Step 1: Create Stored Procedure
Create Procedure procname
(

)
as begin
-----------------------
-----------------------
end

Step 2: Calling stored procedure or executing the stored procedure.
exec procname parameters.

-----------------------------------

User Defined Functions

User defined functions is a set of pre complied transact SQL statement stored in the database server.

• User defined function contains only select statments to read data from tables.
• User defined functions does not contain insert,delete and update statements to perform operations with permenant tables.
• User defined functions takes only input parameters.
• Return statement can be used to return scalar value or set of rows.

There are two steps to work with user defined functions

Step1: Create Function name
Create function functionname
(

)
returns return type (which means, any data type except text, image, cursor/timestamp)
as begin
--------------------------
--------------------------
return (scalar value (or) set of rows)
end

Step 2: Calling the user defined function or executing the user defined function.

Ownername.functionname(parameters)
default owner ->.dbo

Types of User defined functions, They are of 3 types

1)Scalar UDF
A user defined function which returns single scalar values is called as scalar UDF

ex:Create a user defined function which returns 'empname','job' of a given employee number

Step1:
Create function get emp details
(
@empno int
)
returns varchar(36) or varchar (max)
as begin
declare @ details varchar(36)
select @ details=empname+','+job from employees where empno=@empno return(@details)
end

Step2: This can be handled in two ways
Method 1:
Declare @result varchar(36)
set @ result=dbo.getempdetails(120)
print @result

Method 2:
select dbo.getempdetails(120)

2)Inline UDF
A user defined function which returns set of rows is called inline udf. Inline UDF can have only one statement that should return statement (No being and no end)

Ex: Create function which returns empno, empname, sal and dname of given deptno employees.

Step 1:
Create function getempbydno
(
@deptno int
)
returns table
as
return (select empno, empname, sal,dname from employees inner join dept on employees.deptno=dept.deptno and employees.deptno=@deptno)

Step 2:
While a function returns a set of rows it can be called same as a table.

select * from dbo.getempbydno(10) ->This statement returns the table having set of (n number ) of rows.


3)Multi-statement table UDF
A user defined function returns a set of rows with multiple statements is called as multistatement table UDF
Return type of multistatement table UDF is table variable.
Ex:
Create a function which returns empno, empname,sal and deptname of a given deptno "employees".

Step1:Creating a UDF
Create function getemployees
(
@deptno int
)
returns @t1 table
(
empno int,
empname varchar(20),
sal money,
dname varchar(15)
)
as begin
-------Copy rows into table variable------------
insert into @t1 (empno,empname,sal,dname)
select empno, empname,sal,dname from employees inner join dept on employees.deptno=dept.deptno and employees.deptno=@deptno
return
end

Step2: Calling UDF function
While calling user defined function which returns set of rows can be called same as table

select * from dbo.getemployees(10)->This will return table variable where we have set of rows.


Table variable
Table variables are used to prepare one dimension arrays (list of values) and prepare two dimenstion arrays(set of rows)

• Table variables can store list of values or set of rows temporarily.
• Table variable memory is allocated in RAM

ex:Prepare one dimension array to store collection of integers temporarily.

declare @arr table
(
col1 int
)
insert @arr values(10)
insert @arr values(20)
insert @arr values(30)
select * from @arr

ex: Prepare a table variable to store collection of rows temporarily
Declare @t1 table
(
col1 int primary key,
col2 char(10)
)
insert @t1 values(10,'abc')
insert @t2 values(20,'def')
select * from @t1

------------------------------

Properties of table variables

1. Table variables existed in the memory as long as script is executed.
2. Table variables cannot be defined with relationships.
   

----------------------------------

Temporary Tables

• Temporary tables are used to store set of rows temporarily.
• Temporary tables memory is allocated in tempdb database.

Properties of temporary tables

1. Temporary tables exist untill we close the connection.
2. Temporary tables can be defined with relationships also.
3. Temoprary tables can be dropped explicitly also by the following command. Ex: Drop table #emp or ##emp.
   

Two types of temporary tables

1)Local Temporary table
#Tablename-> Accessible from only current connection.

Ex
Create table #emp1
(
empno int primary key,
empname varchar(20)
)
insert #emp1 values(10,'lmno')
insert #emp1 values(20,'pqrs')
select * from #emp1

2)Global Temporary tables
##Tablename->Accessible from all connections.

Ex:
Create table ##emp2
(
empno int primary key,
empname varchar(20)
)
insert ##emp2 values(10,'yedj')
insert ##emp2 values(20,'nhtr')
select * from ##emp2

-------------------------------

Dropping a function
Drop function functionname
Ex:drop function getemployees

---------------------------------

Transaction and locks
Its a logical unit of work which contains one or more operations, every transaction follows ACID properties

ATOMICITY
It guarantees that if all the operations are completed and then the transaction is committed, if any of the operations fails then transaction is rolled back.
or
Transaction is all or nothing

Consistency
Its about providing a correct data to the transactions, it can be implemented with locking mechanism.
or
All constraints and other data integrity rules have been adhered to, and all related objects (data pages, index pages) have been updated completely.

Isolation
One transaction failure should not effect the other transactions in the system
or
Each transaction is completely isolated from any other transaction. The actions of one transaction cannot be interfered with by the actions of a separate transaction.

Durability
It Guarantees that result of every committed transaction should be permenantly saved in database tables.
or
After a transaction is completed, its effects are permanently in place in the system. The data is "safe", in the sense that things such as a power outage or other non-disk system failure will not lead to data that is only half written.

---------------------------------

Types of transactions

SQL Server supports two types of transactions
1)Implicit transactions:
One statement creates one transaction implicitly, implicit transactions are automatically committed.
To control the behaviour of implicit transactions, set implicit_transactions ON/OFF
->OFF - Implicit transactions automatically committed and we cannot rollback (or) undo->its by default.
->ON - Implicit transactions are not committed automatically and we can roll back.

Ex:
Set implicit _transactions ON
GO
Delete from employees
Now, in order to retrive the lost data the command is roll back.

**In order to set implicit transactions ON for every connection by default follow the steps as given below:

Open Object explorer->Connect to a Database engine->select server and then right click properties->select connections page->check the option implicit transaction->Click OK.

2)Explicit transactions
These transactions are used to execute group of operations inside a single transaction.
In order to prepare explicit transactions transact SQL provides TCL commands (Transaction control language)
TCL Commands:

1. Begin Transaction: It Creates transaction process.
2. Save Transaction:It creates save transaction.
3. Commit Transaction:Transaction operations result is permanently saved in actual database tables.
4. Rollback Transactions:Transaction operation results not saved in actual database tables (Undo).

Advantages of Explicit Transactions:

• Explicit transactions are used to execute group of operations in single transaction
• E transaction allows you to verify the the buisness conditons.

Stored Procedures

Define stored procedure.

Stored procedure is a set of SQL commands that have been complied and stored on the database sever. They can be used in the code as and when required since hey stored. They need not be complied over and over again. They can be invoked by CALL procedure (..) or EXECUTE procedure(..)

Define temporary and extended stored procedure.

Temporary Stored Procedure

• Temporary Stored Procedure is stored in TempDB database.
• It is volatile and is deleted once connection gets terminated or server is restarted.
• Temporary stored procedures are prefixed with pound sign #.
• One pound sign means that it is temporary within the session.
• Two pound signs ## means it is a global temporary procedure.
• Global temporary procedure can be called by any connection to the SQL server during its lifetime.

Extended Stored Procedure

It is basically created to expand features of stored procedure.
It uses external program and compiles as DLL. Mostly xp_ prefix is used as naming convention for extended stored procedure.

What are the purposes and advantages stored procedure?

Purposes and advantages of stored procedures:

• Manage, control and validate data
• It can also be used for access mechanisms
• Large queries can be avoided
• Reduces network traffic since they need not be recompiled
• Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code
• Permissions can be granted for stored procedures. Hence, increases security.

Determine when to use stored procedure to complete SQL Server tasks ?

• If a large piece of code needs to be performed repeatedly, stored procedures are ideal
• When hundreds of lines of SQL code need to be sent; it is better to use stored procedure through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
• When security is required.

Explain OPENQUERY function and OPENROWSET function?

PS: Not sure about the syntax.

OPENQUERY: - Used to execute the query passed on the linked server.

Syntax: OPENQUERY (Linked_server_identifier, query). It can also be refernced from a FROM clause of select query.

e.g. Select * from OPENQUERY (Oracleserver, ‘select fname, FROM Employee);

OPENROWSET: - Used to access tables in a linked server. It can be used one time for accessing remote data by using OLE DB. It includes all the information required to access the remote data.

Syntax:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

DATA TRANSFORMATION SERVICE - DTS

Describe in brief exporting and importing utility?

BCP Utility

The Bulk Copy is a command utility that transfer SQL data to or from a data file.
This Utility is mostly used to transfer huge data to SQL server from other database.
With this, data is first exported from source file to a data file and then imported from the data file to SQL server table.
It is also used to transfer data from SQL server table to a data file.
You can use ‘Bulk Insert’ statement to transfer data from data file to SQL server table।

---------------------------------------------------

DTS Packages

It is a set of tools that allows you to extract, transform, and consolidate data from disparate sources into single or multiple destinations. You can create custom data movement solution using DTS object model.

DTS packages can provide following services:

It can import or export data to or from text file or OLE DB data source.
It supports data transformation using DTS transformation which means that data can be operated using one or more functions before hitting the destination.
You can transfer database objects along with data using DTS package.
DTS package also notifies if package steps succeed or failed by sending mails to source and destination.

-------------------------------

Describe how DTS is used to extract, transform and consolidate data ?

Data Transformation Services is a set of tools available in SQL server that helps to extract, transform and consolidate data. This data can be from different sources into a single or multiple destinations depending on DTS connectivity. To perform such operations DTS offers a set of tools. Depending on the business needs, a DTS package is created. This package contains a list of tasks that define the work to be performed on, transformations to be done on the data objects.

Import or Export data: DTS can import data from a text file or an OLE DB data source into a SQL server or vice versa.

Transform data: DTS designer interface also allows to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. For parameterized queries and mapping purposes, Data driven query task can be used from the DTS designer.

Consolidate data: the DTS designer can also be used to transfer indexes, views, logins, triggers and user defined data. Scripts can also be generated for performing these tasks.

-----------------------

Define data, entity, domain and referential integrity.

Data Integrity Data Integrity validates the data before getting stored in the columns of the table. SQL Server supports four type of data integrity: Entity Integrity Entity Integrity can be enforced through indexes, UNIQUE constraints and PRIMARY KEY constraints.

Domain Integrity

Domain integrity validates data for a column of the table.
It can be enforced using:

Foreign key constraints,
Check constraints,
Default definitions
NOT NULL.

Referential Integrity

FOREIGN KEY and CHECK constraints are used to enforce Referential Integrity.

User-Defined Integrity

It enables you to create business logic which is not possible to develop using system constraints. You can use stored procedure, trigger and functions to create user-defined integrity.

SQL Server

1) Data Integrity: The concept of applying business rules on database tables is called as data integrity.

1. There are two types of integrity, Pre- Defined and user defined

a. The predefined integrity is of three types

b. It can be implemented with the help of constraints we have three subtypes under predefined data integrity

i. Entity integrity – entity means row and entity set means table, Primary key and unique.

ii. Domain Integrity- domain means list of possible values in a column, not null, Default and check.

iii. Reference integrity-maintaining relationships, foreign key.

2) Primary key – It is collection of one or more column used to identify a row or entity, it doesn’t allow duplicates and null values and it can be simple or composite. A table can have only one Primary key.

3) Unique Key –It can be defined on single column or combination of multiple columns, it is also used to identify a row, a table can have any number of unique constraints. Unique constraint column does not allow duplicates but it do accepts one null value.

4) Not Null-In order to restrict entering null values in a column which means one must assign a value to not null column. It can be combined with other constraints also.

5) Default-Which means if a value is not inserted in a particular column, default value is assigned automatically.

6)Check-This constraint is used to check the user defined condition.

7)Foreign Key-It is a column referencing values from other table primary key column, It is used to maintain the relationships among the tables. It allows duplicates and null values by default. A table can have 253 foreign keys, foreign key column name recommended to be same as primary key column but datatype should be same.

8) Relationships- The association or dependency among the tables is called relationships, There are three types of relationships.

(i) One to many relationships, Ex -Dept to Employees. A row in one table is associated with many rows in another table.

(ii)One to One relationship,Ex-Employee to phone, A row in one table is associated with only one row in a table.

(iii)Many to Many Relationship, Ex-Many rows in one table is associated with many row in another table, it will be established using a linking table and linking table contains multiple foreign keys and composite primary key.

9) Normalization: The process of applying normal forms sequentially on the database design can be called as normalization. There are 7 types of normal form.

a) First Normal Form – A table in first normal form should follow the rule – No Multi value columns in the table.

b) Second Normal Form – A table in second normal form, it should follow first normal form and every non key column should depend on complete primary key (No Partial dependency-a non key column depends on part of the primary key is called partial dependency)

c)Third Normal Form – It should follow second normal form, No transitive dependency meaning one non key column depends on other non key column in the same table can be called as transitive dependency.

d) BCNF –It should follow 3rd normal form, no overlap between candidate keys.

10) Advantages of Normalization-

1) Normalization eliminates the redundancy.

2) It eliminates the functional dependency problems like partial dependency, transitive dependency etc.

3) Faster index creation.

4) Normalization improves the performance while performing insertions, deletions and updating into the table.

11) Drawbacks of Normalization-It degrades the performance while retrieving data from multiple tables.

12) De normalization- The reverse process of normalization is called as de normalization, This is recommended as it increases performance while retrieving data from a large table. The drawbacks of de normalization is that it introduces data redundancy , functional dependency.

OLTP – Used for daily transactions – It’s a normalized DB tables-Production servers

OLAP-De Normalized tables –Querying, analysis and reporting purpose or business intelligence purpose.

13) Candidate key – It’s a collection of one or more columns used to identify a row, a table can have multiple candidate keys. While designing tables we should make note of the following – Identify candidate keys-Define primary keys-Define unique constraints. One of the candidate key is defined as primary key and other candidate keys defined with unique constraints.

-----

What is Normalization?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

The Normal Forms

First Normal Form or 1NF) through five (fifth normal form or 5NF). In practical applications, you'll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won't be discussed in this article.

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

• Eliminate duplicative columns from the same table.
• Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

• Meet all the requirements of the first normal form.
• Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
• Create relationships between these new tables and their predecessors through the use of foreign keys.
  

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

• Meet all the requirements of the second normal form.
• Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

• Meet all the requirements of the third normal form.
• A relation is in 4NF if it has no multi-valued dependencies.