-- Table Valued Stored Procedure --Creating a table here
create table Candidate
(
id int,
Name varchar(20)
)

insert into candidate values (1,'Adarsh')
insert into candidate values (2,'Rohit')
insert into candidate values (3,'Vinod')
insert into candidate values (4,'Rahul')
select * from Candidate

create procedure ProcONE
as
select * from Candidate
exec ProcONE

--create the User defined table type
create Type MyType as
Table
(
id int,
Name varchar(20)
)

declare @MyTable MyType
insert into @MyTable values (1,'Adarsh'),(2,'Rohit'),(3,'Vinod')
--select * from @MyTable
insert into Candidate
select ID, name from  @MyTable

exec ProcOne

--Stored Procedures to auto Start
use master

--Stored Procedures to auto Start when a Sequel Server Starts
use master
create Procedure Adarsh as print 'Hello Adarsh'

exec sp_procoption Adarsh,'startup','on'

select * from master.INFORMATION_SCHEMA.ROUTINES where OBJECTPROPERTY(object_id(Routine_name),'ExecIsStartup')=1

--Trace flag dbcc traceon(4022)
dbcc tracestatus(-1)
exec sp_procoption Adarsh,'startup','on'

--Recompiling a Stored Procedure
exec sp_recompile 'Adarsh'
--master.dbo.sysCacheObjects store compiled objects
select * from master.dbo.sysCacheObjects where sql like 'exec%'

use Adarsh
create procedure Adarsh
as
select * from Candidate
select * from Candidate
print 'Hello'

exec Adarsh

--Encrypting and Decrypting

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[Adarsh]    Script Date: 09/20/2010 15:32:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create Procedure [dbo].[Adarsh] as print 'Hello Adarsh'
GO

EXEC sp_procoption N'[dbo].[Adarsh]', 'startup', '1'

GO

create Procedure [dbo].[Adarsh1] with encryption
as print 'Hello Adarsh'

sp_helptext Adarsh1
sp_helptext adarsh

--Can we execute a stored Procedure within queries...?
--Cant insert, update or delete any records in a function

select getdate()

--Can be called within a select statement only one way using linked service using open query

use Adarsh
alter procedure Adarsh
as
select * from Candidate
select * from Candidate
print 'Hello'

sp_helptext Adarsh
exec Adarsh

--3 UDFs
Scalar
Inline
Multi Statement Table Valued

alter function fnAdarshRahul
(
@employeename varchar(50)
)
returns int Begin Return (select id from Candidate where name = @employeename)
end

select * from dbo.fnAdarshRahul('Rahul')
select * from Candidate

--Inline Functions
create function fnTable
(
@EmployeeName varchar(50)
)
returns table as
return
select * from candidate where name = @EmployeeName
go

select * from fnTable('Adarsh')

--Multi Statement UDF
--Returns a table datatype

--Functions Cant be used to modify table info..DML,Insert , update and Delete
--Functions that return Non-Deterministic values arent allowed..

--CLR Integration
--Basics of SQLCLR
--Using CLR
--Compiled .net code---Managed Code
--Compiled .net code in files--DLL--Assemblies
--CLR allows creating Aggregate Functions and user defined types unlike T-SQL where in
--it allows only stored Procedures, function and triggers, which can also be creted in CLR
--CLR allows Encryption and Compression which isnt there in T-SQL
--To Perform complex procedural tasks
--To create user defined aggregates and data types

-- Enable CLR Integration in SQL
Reconfigure
sp_configure 'Show advanced options', 1
go
reconfigure
go
sp_configure 'clr enabled',1
reconfigure

--Code that deploys an assembly

use master
create database bindu
on primary
(
name='vinod',
filemane='C:vinodvinodbindu.mdf',
size=10mb,
maxsize=20mb
)
log on
(
name = 'bindu',
filename = 'C:vinodindu_log.ldf',
size = 5mb,
maxsize = 10mb
)

--Day5 13/08/2010 DB-Development
_________________________________________________________
TRIGGERS

1.) DDL Triggers:- (Database triggers) Fires on DDL statements like CREATE TABLE,CREATE VIEW,DROP TABLE, etc
2.) DML Triggers:-(Table triggers) Fires on DML statements like INSERT,DELETE and UPDATE......

Use of triggers:-
Data security like rolling back un-wanted operations
Automatic Calculations and Operations

syntax:
CREATE TRIGGER droptrig
on database
for drop_table as print 'You cannot DROP tables in this database, Remove Trigger first'
rollback

drop table employees
--Error: You cannot DROP tables in this database, Remove Trigger first
--Msg 3609, Level 16, State 2, Line 1
--The transaction ended in the trigger. The batch has been aborted.

create trigger updtrig
on employees
for UPDATE,delete
as
print 'You cant update from this table, Remove the trigger'
rollback

drop trigger updtrig

select * from employees
update employees set salary = 10034 where ecode = 101
--You cant update from this table, Remove the trigger
--Msg 3609, Level 16, State 1, Line 1
--The transaction ended in the trigger. The batch has been aborted.

Two special virtual tables exist inside trigger block are:-
-Deleted
-Inserted
*Their structure will be as per the table on which trigger is fired
*Deleted record are available inside "deleted" table
*New inserted records are available inside "Inserted" table
*In case of update,old record is deleted and kept inot deleted table and a new record is inserted which is kept in inserted table
*these special tables are visible only inside the trigger block

eg. Log the deleted record in your log table

create table log_table
(
ecode int,
ename varchar(20),
deptid int,
salary money,
time_of_oper datetime
)

select * from log_table

create trigger deltrig
on employees
for delete
as
declare @ec int,@en varchar (20),@did int, @sal money, @time datetime
select @ec = ecode,@en = ename,@did = deptid,@sal = salary,@time = getdate()
from deleted
Insert into Log_table values (@ec,@en,@did,@sal,@time)
print 'Record deleted and logged into log_table'

select * from employees
delete employees where ecode = 102

select * from Log_table
select * from dbo.join_view
select * from employees

select * from dapartment
--A view cant be updated if its from multiple tables and the update is on multiple table columns
create trigger join_view_trig
on join_view
instead of update
as
declare  @did int,@old_did int
select @new_did =dept_did from inserted
select @old_did = dept_did from deleted
update dapartment set deptid = @did where deptid = @old_did
update employees set deptid = @did where deptid = @old_did

Test: update join_view set emp_did = 206,dept_did = 206 where dept_did = 202

Assignment:
Q: Create two tables procuct and order with the following columns and put some records in the product table:
Product: Prodid,productname,qty,price
Order: orderid,orderqty,prodid

Define a trigger such that whenever the order is placed for any product, it should be checked in the product table
whether the ordered quantity is available in the product table. If not, the ordered transaction should be rejected
else it should be accepted in the order table and quantity should be updated in the product table as well

create table product
(
prodid int,
productname varchar(50),
qty int,
price money
)
select * from product

create table orderT
(
orderid int,
Orderqty int,
prodid int
)
drop table ordert

select * from ordert
select * from product
insert into product values (1,'Nokia X6',5,16000)
insert into product values (2,'Blackberry',2,13000)
insert into product values (3,'Sony Ericsson Xperia',1,18000)
insert into product values (4,'Samsung corby',4,8000)
insert into product values (5,'Micromax Q7',8,8000)
insert into product values (6,'LG Cookie',4,5000)

alter trigger assignment
on ordert
for insert
as
declare @Qty int,@prdid int
declare @Qty1 int
select @Qty = Orderqty,@prdid=prodid from inserted
select @Qty1 = qty from product where prodid = @prdid
if (@Qty<@Qty1)
 update product set qty= (@Qty1-@Qty) where prodid = @prdid
else begin
 rollback
 print 'Stock lesser than the quantity mentioned. Available Quantity is:'+convert(char(5),@Qty1)
 print 'Your Transaction has been rejected'
end
select * from ordert
select * from product
insert into ordert values (2,4,1)

alter table test_auto
(
rollno int IDENTITY ,
name varchar(10)
)

insert into test_auto(name) values('vinod')
insert into test_auto(name) values('Bindu')
insert into test_auto(name) values('vinu')
insert into test_auto(name) values(Rani')

SELECT * FROM test_auto
CREATE TABLE [dbo].[test_auto](
 [rollno] [int] IDENTITY(1,10) NOT NULL,
 [name] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

*Reccursive Triggers:- A trigger fires another trigger.
A very dangerous situation it is... May end up in loops of Triggers...
Direct Reccursion: When a trigger fires again the same trigger
Indirect Reccursion: When a triggergets fired again by another child trigger
 
This process is called nesting of trigger. 32 levels of nesting of triggers are allowed....
By default this is on. But can be disabled if needed
Statement to Enable and Disable

sp_configure 'nested triggers',0
_________________________________________________________
Dynamic SQL

use AdventureWorks

select * from sys.tables
select * from HumanResources.Employee

declare @SQL_Str nvarchar(max)
declare @mgrid varchar(20)
select @mgrid = '16,6,12'
select @SQL_Str = 'select * from HumanResources.Employee where managerID in ('+@mgrid+')'
select @SQL_Str
exec sp_executesql @SQL_STR

exec(@SQL_Str)

exec sp_dyn_sql '16,6'
===========================================================================================
create procedure sp_dyn_sql(@mgrid varchar(20))
as
declare @SQL_Str nvarchar(max)
select @SQL_Str = 'select * from HumanResources.Employee where managerID in ('+@mgrid+')'
select @SQL_Str
exec sp_executesql @SQL_STR
or
exec(@SQL_Str)
===================================================================
drop procedure sp_dyn_sql
select * from HumanResources.Employee

_________________________________________________________
INDEXES

Clustered: Sorts the data pages
Non-Clustered: Data pages aren sorted instead, index pages are created which actually store the information of data pages
Fill Factor: Related to data pages to prevent page splitting during indexing. This Indexing is faster. A table can have only one.
Pad Index: Related to Index Pages. This Indexing is slower. A Table can hav 249.

select * from HumanResources.Employee
use employeeDB

syntax:
create index idx_emp
on employees(ecode)
--Default: nonclustered index

create clustered index idx_emp_clust --also CREATE NONCLUSTERED INDEX can be used
on employees(ename)

alter index idx_emp_clust on employees Disable
Note: Indexes can only be created,dropped,enabled or disabled
drop index idx_emp_clust on employees

--On primary key column automatically clustered index is created
create index idx
on employees (ecode)
with (fillfactor = 5,
pad_index = on)

Note: Fillfactor and pad index parameters are used to optimize page splitting in case of frequent updations
Fragmentation: When spaces are created because of deletion of records
Defragmentation: Is used to reorganise the indexes

Rebuilding of indexes: It drops old indexes and creates new ones             --for a lot of fragmentation
Reorganising of indexes: It just rearranges the existing index without deleting it   --for fragmentation < 30%

How to we know %age of fragmentation of Indexes:
All Indexes details of Indexes are maintatined at:
select * from sys.sysindexes

Readymade Query for showing fragmentation:

select a.index_id,name,avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(DB_ID(N'EmployeeDB'),
OBJECT_ID(N'dbo.employees'),Null,Null,Null) As a Join sys.indexes as b on a.object_id = b.object_id and a.index_id = b.index_id

-- > 30%
Alter index idx on employees rebuild

-- < 30%
Alter index idx on employees reorganize
select * from employees
select * from employees where ename like 'a%'

_________________________________________________________
Full text search

select businessentityid,jobtitle from humanresources.employee where freetext(*,'Marketting Assistant')
select businessentityid,jobtitle from humanresources.employee where contains(Jobtitle,'Marketting OR Assistant')
select businessentityid,jobtitle from humanresources.employee where contains(Jobtitle,'Marketting AND Assistant')

create table letters
(
id int,
letters nvarchar(max)
)
use employeeDB
select letters from test_ftx where contains(letters,'system and prog')
select letters from test_ftx where freetext(*,'system and Gemini')

insert into test_ftx values (1,'An operating system (OS) is a set of system software (programs and data) running on a computer that manage the computer hardware and provide common services for execution of various application software.

For hardware functions such as input and output a')

select * from test_ftx

Q: use AdventureWorks
select * from Humanresources.JobCandidate
select * from Humanresources.JobCandidateHistory

create Trigger dJobCandidate
on Humanresources.JobCandidate
for delete
as
declare @CondID int,@Resum xml,@RejDate datetime,@CntID int
select @CondID = JobCandidateID, @Resum = Resume, @RejDate = getdate(), @CntID = null from deleted
insert into Humanresources.JobCandidateHistory(JobCandidateID,Resume,RejectedDate,ContactID) values (@CondID,@Resum,@RejDate,@CntID)

delete Humanresources.JobCandidate where JobCandidateID=2

select * from Humanresources.JobCandidateHistory

 It times back to 1980s, when Oracle had already had a very large base on database market. Microsoft, most of the people who are aware of, wanted to establish its presence in the RDBMS market to compete with Oracle. Hence the story goes on.......

 In 1987 Microsoft and Sybase started a partnership to build/sell a Database Management System, based on the then (yet to be released) Sybase DataServer product. Sybase would have the rights to the product on the Unix/Mini Platform and Microsoft would have the rights on the fledgling OS2, and any other OS developed by Microsoft.

 At the time the leader in the “PC” database systems (non mainframe/mini/UNIX) was a product from Ashton Tate called dBase. To help leverage the large dBase installed base and to give Ashton Tate a true Client Server offering, Microsoft and Ashton Tate with help from Sybase announced Ashton-Tate/Microsoft SQL Server (Sybase later renamed their product to Sybase SQL Server for UNIX and VMS), this was to be the name of the OS/2 port of Sybase DataServer.

 Version1 of Ashton-Tate/Microsoft SQL Server was shipped in 1989. Originally dBase IV was supposed to have provided a dev tool/front end for SQL Server (hence the original partnership) but the problems with dBase IV after its release later in the year led to this falling by the wayside. At this point the partnership between Microsoft and Ashton Tate was dissolved and the product became Microsoft SQL Server.

 Microsoft continued the evolution of SQL Server, adding support for Windows via SQL Server 1.1 in 1990, although almost all the work was still done by Sybase with Microsoft doing testing, project management and some minor development. Over the course of 1991 the Microsoft team was given read and then read/write access to the code to allow bug fixes to be made. The first real “partnership” release followed in 1992 and synched the Sybase 4.0 code into the OS/2 product, this was released as 4.2 and included significant code from MS as well as Sybase for the first time.

 Things started to heat up just after this release, Microsoft had been working on a 32 bit version of SQL Server and Sybase was working on what would become System 10. At this point OS/2 was becoming less viable as a platform and Microsoft already had beta versions of Windows NT that was 32 bit only, available. So the SQL Server team decided to build on the stable 4.2 code for its port to NT while Sybase continued with the new System 10 code base. Now “port” really is the wrong word to use here, NT offered lots of opportunities to make a better product by using features of the OS, the clincher was the ability to run on SMP systems by using the support provided by the OS, rather than having to write it into the database code, which is what the team did, leading to the code from the 4.2 for OS/2 and the SQL Server for NT, or SQL NT.

 By this time the value of the partnership between Microsoft and Sybase was beginning to reduce, as Sybase wanted to remain platform neutral, and Microsoft wanted to fully commit to the NT platform and only the NT platform. In addition, because of the restrictive nature of the Microsoft/Sybase agreement, Microsoft could not truly add new features without Sybase’s approval.  In 1994 the partnership was dissolved and Sybase was allowed to sell its product for the first time on OS/2 and Windows and Microsoft could take the code in any direction it wanted. Microsoft did indeed take the product in its own direction, building on the SQL Server for NT code base and releasing SQL Server 6.0 and 6.5 within 18 months, these releases involved rewriting existing code and adding new code. At this point in time SQL Server was a success in its own right with the code (and features) significantly different from the original Sybase code of the 4.2 for OS/2 version.

 During this window Microsoft decided to go “big” on the data management front, part of doing that meant building a stronger, bigger team and while the org had grown from the 1 corridor it occupied in the very early days, more needed to be done. Microsoft went out and hired some of the best and most experienced people in the database industry(Hal Berenson, Peter Spiro, David Campbell  and others from DEC, James Hamilton, Lubor Kollar and others from IBM, Bill Baker from Oracle, Pedro Celis, Pat Helland from Tandem) and paired them with the best in research(Jim Gray, Phil Bernstein and others) and some of the smartest new graduates from Database Masters and PhD programs from around the world, combing these folks with the original “soul” of the team(folks like Ron Soukup) and others from inside Microsoft allowed a great, focused team to be built and gel’d in a short space of time. Some of this team worked on 6.0/6.5 but a number of them came on line to work on what was called Sphinx, the code name for the next version of SQL Server, which was shipped as version 7.0.

 The goals for Sphinx were clear, set a new standard in ease of use for complete management of data, to do so meant building a new platform that could be extended in years to come, learning the lessons of the previous releases of SQL Server and other database platforms. To do this required a complete rewrite of the Database Engine, a new Query Processor, a new Storage Engine and a new set of Data Access APIs.

 While SQL Server had made its name as a relational database management system, with SQL Server 7.0 Microsoft wanted to provide a complete data solution, this meant adding support for OLAP via OLAP Services (the code was based on an acquisition of “Plato” from Panorama in Israel), ETL/Data Integration via DTS (the code was developed in house by the Starfighter/Tools team).

 Obviously the march of SQL Server did not stop with SQL Server 7.0, SQL Server 2000,  SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 followed......

 And this is the intelligence of Microsoft. Though the idea wasnt theirs' it has emerged a gaint share in the RDBMS market.