DD/MM/YYYY mask in Asp.net TextBox

<asp:TextBox runat="server" ID="txtAppDOB" placeholder="mm/dd/yyyy" MaxLength="10" Width="120px" onkeyup="return fnDateMask(event,this);" TabIndex="8" onkeypress="return isNumber(event)"></asp:TextBox>

<asp:RequiredFieldValidator ID="reqDOBApp" runat="server" ControlToValidate="txtAppDOB" EnableViewState="True" ErrorMessage="Required" ValidationGroup="valApp" SetFocusOnError="True"></asp:RequiredFieldValidator>
                                           
  <asp:RegularExpressionValidator ID="regDOBApp" runat="server" ControlToValidate="txtAppDOB" CssClass="error-message" ErrorMessage="(MM/DD/YYYY)" ValidationExpression="(?=\d)^(?:(?!(?:10\D(?:0?[5-9]|1[0-4])\D(?:1582))|(?:0?9\D(?:0?[3-9]|1[0-3])\D(?:1752)))((?:0?[13578]|1[02])|(?:0?[469]|11)(?!\/31)(?!-31)(?!\.31)|(?:0?2(?=.?(?:(?:29.(?!000[04]|(?:(?:1[^0-6]|[2468][^048]|[3579][^26])00))(?:(?:(?:\d\d)(?:[02468][048]|[13579][26])(?!\x20BC))|(?:00(?:42|3[0369]|2[147]|1[258]|09)\x20BC))))))|(?:0?2(?=.(?:(?:\d\D)|(?:[01]\d)|(?:2[0-8])))))([-.\/])(0?[1-9]|[12]\d|3[01])\2(?!0000)((?=(?:00(?:4[0-5]|[0-3]?\d)\x20BC)|(?:\d{4}(?!\x20BC)))\d{4}(?:\x20BC)?)(?:$|(?=\x20\d)\x20))?((?:(?:0?[1-9]|1[012])(?::[0-5]\d){0,2}(?:\x20[aApP][mM]))|(?:[01]\d|2[0-3])(?::[0-5]\d){1,2})?$" ValidationGroup="valApp"></asp:RegularExpressionValidator>
                                         

Javascript :  fnDateMask

 function fnDateMask(event,control) {          
        var KeyID = event.keyCode;
        if (KeyID != 8)
        {  
            var s = new String(control.value);
            if (s.length == 2 || s.length == 5) {
                control.value = s + "/";
            }
        }
    }

function isNumber(evt) {
        var charCode = (evt.which) ? evt.which : event.keyCode;
        if (charCode > 31 && (charCode < 48 || charCode > 57))
            return false;
        else
            return true;
    } 

SQL Puzzle

/*
Update one column in Table1... But that value should not be updated in Table1..But it wil be updated to table2...
Try to create trigger for this process
*/

--Solution:

--As of now This block is empty.. Copy your answer here with your name:

/*Use Instead of Trigger for this Puzzle*/

DROP TABLE TEMP_RECORDS
DROP TABLE TEMP_RECORDS_2
CREATE TABLE TEMP_RECORDS
(
SNO INT IDENTITY(1,1),
E_NAME VARCHAR(100)
)

INSERT INTO TEMP_RECORDS
SELECT 'Vicky'


CREATE TABLE TEMP_RECORDS_2
(
SNO INT IDENTITY(1,1),
E_NAME VARCHAR(100)
)

INSERT INTO TEMP_RECORDS_2
SELECT 'Vicky'


ALTER TRIGGER INSTEAD_TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
INSTEAD OF UPDATE
AS
BEGIN

DECLARE @ename VARCHAR(100)
SELECT @ename = E_NAME FROM INSERTED /*Magic table used here*/

UPDATE TEMP_RECORDS_2
SET E_NAME = @ename

END

/*Update First Table Record*/
UPDATE TEMP_RECORDS
SET E_NAME = 'Moov'

/*Check Records*/
SELECT * FROM TEMP_RECORDS
SELECT * FROM TEMP_RECORDS_2

*******************************************************
CREATE TABLE Date_Ranges
(
EMP_NO INT,
STARTDATE DATETIME,
ENDDATE DATETIME
)

INSERT INTO Date_Ranges
SELECT 101,'01/01/2014','05/31/2014'

INSERT INTO Date_Ranges
SELECT 102,'02/01/2014','05/31/2014'

INSERT INTO Date_Ranges
SELECT 103,'01/01/2014','03/31/2014'

INSERT INTO Date_Ranges
SELECT 104,'12/01/2013','01/31/2014'

INSERT INTO Date_Ranges
SELECT 105,'12/01/2013','12/31/2013'

INSERT INTO Date_Ranges
SELECT 106,'06/01/2014','07/31/2014'

INSERT INTO Date_Ranges
SELECT 107,'02/01/2014','03/31/2014'

/*Sample Input*/
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '02/01/2014'
SET @EndDate = '05/01/2014'

/*Answer*/

*********************************************************************
CREATE TABLE Date_Ranges
(
EMP_NO INT,
STARTDATE DATETIME,
ENDDATE DATETIME
)

INSERT INTO Date_Ranges
SELECT 101,'01/01/2014','05/31/2014'

INSERT INTO Date_Ranges
SELECT 102,'02/01/2014','05/31/2014'

INSERT INTO Date_Ranges
SELECT 103,'01/01/2014','03/31/2014'

INSERT INTO Date_Ranges
SELECT 104,'12/01/2013','01/31/2014'

INSERT INTO Date_Ranges
SELECT 105,'12/01/2013','12/31/2013'

INSERT INTO Date_Ranges
SELECT 106,'06/01/2014','07/31/2014'

INSERT INTO Date_Ranges
SELECT 107,'02/01/2014','03/31/2014'

/*Sample Input*/
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '02/01/2014'
SET @EndDate = '05/01/2014'

/*Answer*/

SELECT * FROM DATE_RANGES

SELECT * FROM DATE_RANGES
WHERE STARTDATE <= @EndDate AND ENDDATE >= @StartDate

SELECT * FROM DATE_RANGES
WHERE NOT (STARTDATE <= @EndDate AND ENDDATE >= @StartDate)

*******************************************************
/*Method 1*/
DROP TABLE #TEMP_CLAIM

CREATE TABLE #TEMP_CLAIM
(
PATID INT,
CLAIMID INT
)

INSERT INTO #TEMP_CLAIM
SELECT 123,1 UNION SELECT 123,2 UNION SELECT 123,3

DROP TABLE #TEMP_LEDGER

CREATE TABLE #TEMP_LEDGER
(
PATIENTID INT,
LEDGERID INT,
AMOUNT MONEY,
PRI_CLAIM_ID INT,
SEC_CLAIM_ID INT,
TER_CLAIM_ID INT
)

SELECT * FROM #TEMP_CLAIM
SELECT * FROM #TEMP_LEDGER

INSERT INTO #TEMP_LEDGER
SELECT 123,100,10,1,2,3 UNION SELECT 123,101,20,0,2,0 UNION SELECT 123,102,30,1,2,0

--Answer :

SELECT PATID,CLAIMID,
SUM( CASE WHEN X.AMOUNT IS NOT NULL THEN X.AMOUNT WHEN Y.AMOUNT IS NOT NULL THEN Y.AMOUNT WHEN Z.AMOUNT IS NOT NULL THEN Z.AMOUNT ELSE 0 END)  AS AMOUNT FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER X ON PATID =  X.PATIENTID AND CLAIMID = X.PRI_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Y ON PATID =  Y.PATIENTID AND CLAIMID = Y.SEC_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Z ON PATID =  Z.PATIENTID AND CLAIMID = Z.TER_CLAIM_ID
where PATID is not null
GROUP BY PATID,CLAIMID

--Explanation:
/*Understanding FULL outer Join*/
--Step:1
SELECT * FROM #TEMP_LEDGER where PRI_CLAIM_ID<>0
SELECT * FROM #TEMP_CLAIM where CLAIMID = 1
SELECT * FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER X ON PATID =  X.PATIENTID AND CLAIMID = X.PRI_CLAIM_ID
/*Check the NULL values in both part of table*/

--Step:2
SELECT * FROM #TEMP_LEDGER where PRI_CLAIM_ID<>0
SELECT * FROM #TEMP_CLAIM where CLAIMID = 2
SELECT * FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER Y ON PATID =  Y.PATIENTID AND CLAIMID = Y.SEC_CLAIM_ID
/*Check the NULL values in both part of table*/

--Step:3
SELECT * FROM #TEMP_LEDGER where PRI_CLAIM_ID<>0
SELECT * FROM #TEMP_CLAIM where CLAIMID = 2
SELECT * FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER Z ON PATID =  Z.PATIENTID AND CLAIMID = Z.TER_CLAIM_ID
/*Check the NULL values in both part of table*/

--Step:4
/*Now Combine All the three Joins*/
SELECT * FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER X ON PATID =  X.PATIENTID AND CLAIMID = X.PRI_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Y ON PATID =  Y.PATIENTID AND CLAIMID = Y.SEC_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Z ON PATID =  Z.PATIENTID AND CLAIMID = Z.TER_CLAIM_ID


/*Now Exclude NULL value in Left table */
SELECT PATID, CLAIMID,X.AMOUNT, Y.AMOUNT, Z.AMOUNT  FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER X ON PATID =  X.PATIENTID AND CLAIMID = X.PRI_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Y ON PATID =  Y.PATIENTID AND CLAIMID = Y.SEC_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Z ON PATID =  Z.PATIENTID AND CLAIMID = Z.TER_CLAIM_ID
where PATID is not null

--Final Result!
/*Now  group the values*/
SELECT PATID,CLAIMID,
SUM( CASE WHEN X.AMOUNT IS NOT NULL THEN X.AMOUNT WHEN Y.AMOUNT IS NOT NULL THEN Y.AMOUNT WHEN Z.AMOUNT IS NOT NULL THEN Z.AMOUNT ELSE 0 END)  AS AMOUNT FROM #TEMP_CLAIM
FULL OUTER JOIN #TEMP_LEDGER X ON PATID =  X.PATIENTID AND CLAIMID = X.PRI_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Y ON PATID =  Y.PATIENTID AND CLAIMID = Y.SEC_CLAIM_ID
FULL OUTER JOIN #TEMP_LEDGER Z ON PATID =  Z.PATIENTID AND CLAIMID = Z.TER_CLAIM_ID
where PATID is not null
GROUP BY PATID,CLAIMID

/*
Note :
Full Outer Join Matches each rows in Left table to Right table and Right Table to Left Table.
If any of the Rows not matched, it reurns Null rows in next table.
So we can, filter the final result for required vaule with NOT NULL condition in where clause!
*/

/*Method 2*/

SELECT CLAIMID,ISNULL(X.AMOUNT,0) FROM #TEMP_CLAIM
LEFT OUTER JOIN (
SELECT PRI_CLAIM_ID,SUM(AMOUNT) AS AMOUNT FROM #TEMP_LEDGER WHERE PRI_CLAIM_ID <> 0
GROUP BY PRI_CLAIM_ID
UNION
SELECT SEC_CLAIM_ID,SUM(AMOUNT) FROM #TEMP_LEDGER WHERE SEC_CLAIM_ID <> 0
GROUP BY SEC_CLAIM_ID
UNION
SELECT TER_CLAIM_ID,SUM(AMOUNT) FROM #TEMP_LEDGER WHERE TER_CLAIM_ID <> 0
GROUP BY TER_CLAIM_ID
) X ON CLAIMID = PRI_CLAIM_ID

/*Please Check; Why Left outer join and ISNULL used here?*/

/*Method 3*/ /*Tricky and Fun one: Amazing use of Group By clause and aggregate functions!*/

/*For Latest claim Only - Giving to understand the use of Group by and aggregate!*/
SELECT PATIENTID,MAX(PRI_CLAIM_ID) AS PRI_CLAIM_ID ,SUM(AMOUNT * CASE WHEN PRI_CLAIM_ID =0 THEN 0 ELSE 1 END) AS PRIMARY_AMOUNT,
MAX(SEC_CLAIM_ID) AS  SEC_CLAIM_ID,SUM(AMOUNT * CASE WHEN SEC_CLAIM_ID =0 THEN 0 ELSE 1 END) AS SEC_AMOUNT,
MAX(TER_CLAIM_ID) AS TER_CLAIM_ID,SUM(AMOUNT * CASE WHEN TER_CLAIM_ID =0 THEN 0 ELSE 1 END) AS TER_AMOUNT
FROM #TEMP_LEDGER
GROUP BY PATIENTID

/*Method 4 - Priyanga and Divya did this! - Really a worth one compared to above all!*/
SELECT CLAIMID, SUM(AMOUNT) FROM #TEMP_CLAIM
INNER JOIN #TEMP_LEDGER ON PATIENTID=PATID
WHERE (CLAIMID=PRI_CLAIM_ID) OR (CLAIMID=SEC_CLAIM_ID) OR (CLAIMID=TER_CLAIM_ID)
GROUP BY CLAIMID

/*You can achieve too many methods to derive required results! But choose which one is unique and different and better performance! :-) */

 /* :-) Have a Nice day!!! */

***************************************************************

What is the differnce between CAST and VARCHAR?

Why these below two queries gives, two different results?

SELECT CAST( GETDATE() AS VARCHAR(112))

SELECT CONVERT(VARCHAR,GETDATE(),112)


One more information is there if u answer it..

Ans:

Cast and Varchar both are same  and converting one datatype to another one!... here in first statement 112 is size... in 2nd one 112 is style.... style always used with datetime only!


One more Info...select convert(varchar,'123456789123456789123456789123456789') what will be the result for this

So be cautious when u convert to varchar,,, default  size is 30.. you ll loss the data!

***********************************************************************

Write a program that prints the numbers from 1 to 100. But for multiples of three print “M-Three” 

instead of the number and for the multiples of five print “M-Five”. For numbers which are multiples 

of both three and five print “M-Three-Five”.

Result Set Example:

1

2

M-Three

4

M-Five 

M-Three 

7

8

M-Three 

M-Five 

11

M-Three 

13

14

M-Three-Five

....

...

..*********************************************************

DECLARE @var BIGINT

DECLARE @FactNo BIGINT

SET @var = 1

SET @FactNo = 12 /*Input*/

SELECT TOP (@FactNo) @var =  ROW_NUMBER() OVER (ORDER BY id) * @var FROM SYSOBJECTS 

SELECT @var

**********************************************************

What is NULL

--What is "NULL" in SQL?

DECLARE @X INT
SET @X = NULL

IF @X = NULL
PRINT 'NULL EQUAL TO NULL'

IF @X IS NULL
PRINT 'NULL IS NULL'

--NULL means unknown.

--Will you accept two unknown is equal? i.e, NULL = NULL

--But you can check any thing is unknown? i.e, NULL is NULL

--So dont use Logical operators(=,<>,>=,<=,>,<) with NULL value

IF EXISTS(SELECT NULL)
PRINT 'UNknown Value'
/*Check this: NULL is really a valid value. But its unknown! You can assign anything to NULL
*/
--by using
SELECT ISNULL(NULL,0)

--Also, when u use some aggregate functions (sum,avg) with NULL
SELECT SUM(NULL) /*No data type for NULL; so error coming!*/
SELECT SUM(NULL+1) /* NULL -- Any value plus unknown is Unknown*/
SELECT SUM(ISNULL(NULL,0)) /*So, Always use ISNULL in aggregate functions*/

/*Small Explanation about NULL datatype*/
SELECT NULL AS 'VAL' INTO NullValue
--VAL column created as Integer! (use Alt+F1) (But i said, No data type for NULL;)
--Yes, But default data type to create table with null column is integer. But we can change it!
DROP TABLE NullValue
SELECT cast(NULL as datetime) AS 'VAL' INTO NullValue
--Now Datetime!(use Alt+F1)

IF EXISTS(SELECT 1 WHERE 1<>1)
PRINT 'Nothing!'
/*Check this: this is nothing.. nothing will return in above query!

**So Be cautious to handling about NULL and Nothing!**

*/

--Add-on Note: i have not used FROM before to WHERE clause in above query!.. is it acceptable?

DECLARE @i int
set @i =0
SELECT 'Vicky' WHERE @i=1
set @i =1
SELECT 'Vicky' WHERE @i=1

--Here we can use select statement like IF statement! :-)

/***********/

View

/*Table Creations-Start*/
DROP TABLE TEMP_EMPLOYEE
DROP VIEW EMPLOYEE_SIMPLE_VIEW
DROP VIEW EMPLOYEE_COMPLEX_VIEW

CREATE TABLE TEMP_EMPLOYEE
(
Emp_No INT,
Emp_Name VARCHAR(100)
)

INSERT INTO TEMP_EMPLOYEE
SELECT 100,'MOOV'

INSERT INTO TEMP_EMPLOYEE
SELECT 101,'ANAND'

DROP TABLE TEMP_EMPLOYEE_SALARY

CREATE TABLE TEMP_EMPLOYEE_SALARY
(
Emp_No INT,
SALARY MONEY,
Salary_Month varchar(10)
)

INSERT INTO TEMP_EMPLOYEE_SALARY
SELECT 100,10000,'Jan'

INSERT INTO TEMP_EMPLOYEE_SALARY
SELECT 101,15000,'Jan'

SELECT * FROM TEMP_EMPLOYEE

SELECT * FROM TEMP_EMPLOYEE_SALARY

/*Table Creations-End*/

/*View Creations-Start*/

/*View are virtual table, whose content is defined by queries*/

/*View with one table*/
CREATE VIEW EMPLOYEE_SIMPLE_VIEW
AS
SELECT Emp_No , Emp_Name FROM TEMP_EMPLOYEE

SELECT * FROM EMPLOYEE_SIMPLE_VIEW

/*View with More than one table*/
CREATE VIEW EMPLOYEE_COMPLEX_VIEW
AS
SELECT X.Emp_No , Emp_Name,SALARY,Salary_Month FROM TEMP_EMPLOYEE X
INNER JOIN TEMP_EMPLOYEE_SALARY Y ON X.Emp_No = Y.Emp_No

SELECT * FROM EMPLOYEE_COMPLEX_VIEW

/*View Creations-End*/

/*Inserting Record into Simple View*/

insert into EMPLOYEE_SIMPLE_VIEW
select 103,'vicky'

/*Inserting Record into Complex View*/

select * from EMPLOYEE_COMPLEX_VIEW

/*This quey will throw error. Because, usage of multible tables in single view.. */
insert into EMPLOYEE_COMPLEX_VIEW
select 104,'KC'

/*Now Specify the columns to insert*/
insert into EMPLOYEE_COMPLEX_VIEW (Emp_No , Emp_Name)
select 104,'KC'


/*Delete Record from Simple View*/
DELETE FROM EMPLOYEE_SIMPLE_VIEW where Emp_Name = 'BS'

/*Delete Record from Complex View*/
DELETE FROM EMPLOYEE_COMPLEX_VIEW where Emp_Name = 'Vicky'
/*Usage of multible tables, we cant decide which table rows can be deleted!*/


/*Update Record in Simple View*/
update EMPLOYEE_SIMPLE_VIEW
set Emp_Name = 'moovee'
where Emp_No = 100

select * from EMPLOYEE_SIMPLE_VIEW

/*Update Record in Complex View*/
update EMPLOYEE_COMPLEX_VIEW
set Emp_Name = 'mooventhan'
where Emp_No = 100
/*It Updates*/

update EMPLOYEE_COMPLEX_VIEW
set salary = 45000
where Emp_No = 100
/*It Updates*/

/*Now Insert Feb Salary for Mooventhan*/

INSERT INTO TEMP_EMPLOYEE_SALARY
SELECT 100,10000,'Feb'

/*Now update salary*/
update EMPLOYEE_COMPLEX_VIEW
set salary = 100000
where Emp_No = 100

SELECT * FROM EMPLOYEE_COMPLEX_VIEW
/*It updates both Jan and Feb Salary. So need to check View query and conditions before update view.*/


/*So, View are not only for Reading Data... We can Insert records into view.. But there is some condition. Here our records inserted successfully but not always.
Consider, if some colums not selected in viewes are not null columns.. What will happen? It will throw error..
*/


/*You can Drop table , even when those tables are used in views*/

begin tran

drop table TEMP_EMPLOYEE

select * from EMPLOYEE_SIMPLE_VIEW

/*Now view is orphaned. We cant use this view no more*/

rollback

/*How can we avoid this...*/

/*Lets alter this view with schema binding option*/
ALTER VIEW EMPLOYEE_SIMPLE_VIEW
WITH SCHEMABINDING
AS
SELECT Emp_No , Emp_Name FROM TEMP_EMPLOYEE

/*It Throws error..  Because Table Names must be in two-part format and an object cannot reference itself*/

/*Now Try this dbo.TableNAme*/

ALTER VIEW EMPLOYEE_SIMPLE_VIEW
WITH SCHEMABINDING
AS
SELECT Emp_No , Emp_Name FROM dbo.TEMP_EMPLOYEE


/*Now Try to drop table , even when those tables are used in views*/

begin tran

drop table TEMP_EMPLOYEE
/*It Throws error. We cant delete it until this table removed from view!*/

rollback


sp_helptext EMPLOYEE_SIMPLE_VIEW

ALTER VIEW EMPLOYEE_SIMPLE_VIEW
WITH SCHEMABINDING, ENCRYPTION
AS
SELECT Emp_No , Emp_Name FROM dbo.TEMP_EMPLOYEE


sp_helptext EMPLOYEE_SIMPLE_VIEW
/*The text for object 'EMPLOYEE_SIMPLE_VIEW' is encrypted.
So now you cant view the query used in views
*/


/*
Avoides Complexity
Increases security
Viiew is slow when compared to Actual query. But, We can create Index on View when schema binding option is ON.
*/

/*
Advanced Topics in View,

Trigger on View,
Indexed View,
Partioned View,

*/

Triggers_Tit_Bits

Some tit-bits about Triggers and today session

*Turn off Trigger while bulk process.
 We can disable trigger instead of delete it permanantly. (Mohan know about this practically)

*DML triggers control data's, DDL Trigger Control Schema's and structure of db.

*We can use DDL and DML triggers for auditing purpose

*We can set multible trigger on same table. But it will create data discrepancy.
 So avoid to create multible triggers on same table.

*Triggers will affect @@identity property (We already discussed about this)

*Triggers not works when you Truncate table (Because truncate does not log transation)

*We can track bulk data changes using trigger. But in BAS it not working.
 Because in BAS, trigger not executed when more than one row changed.
 They Checked like this..

 IF (SELECT COUNT(*) FROM INSERTED) > 1
  RETURN

 Why they checked like this? (Just review the logic they used. you can find it out)

*We Can Create Trigger on Views..

Advanced Tit-Bits:

*We can set Trigger Order using sp_settriggerorder

*We can create Recursive Triggers (Trigger on Table1 updates Table2,
 Trigger on Table2 updates Table1, It again Repeats (upto 32 level)..
 (Research more on this)

*Check about Server Level Triggers (Its more risk for beginners)



Finally....Using Triggers, You can play in SQL... But , be cautious to create triggers.

Good Day.. Thank You!

Triggers

/*Trigger*/

Trigger is nothing but,its a process that will happen automatically if any database activities occurs..

Database Activties means... Any DDL  or DML or LogOn Activities on modifing data or schema (Not Selecting Records)

what is DDL What is DML

DDL - Data Definition Language -- So its about Table definition or schema....
CREATE,DROP,ALTER,TRUNCATE

DML - Data Manipulation Language -- So its about Table data modification...
INSERT,UPDATE,DELETE,SELECT

In this session, we can see about DML Triggers... (Trigger occurs when any data modification happened on table)


Ok. Now you all know the definition.. We can call trigger in two way, Before any data changes occur or after data changes occur...
--After Trigger
--Instead of Trigger

Now Examples....

/*First,After Trigger....*/

CREATE TABLE TEMP_RECORDS
(
SNO INT IDENTITY(1,1),
E_NAME VARCHAR(100)
)

INSERT INTO TEMP_RECORDS
SELECT 'Vicky'

Ok.. Lets create one trigger on this table....

We can create trigger for insert,update,delete seperately or compined

CREATE TRIGGER TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
FOR INSERT
AS
BEGIN
SELECT 'Hi... Records inserted successfully'
END

/*Insert Record now*/

INSERT INTO TEMP_RECORDS
SELECT 'Moov'

SELECT * FROM TEMP_RECORDS

/*
--So you can do any logic inside the trigger
--You can use, FOR or AFTER
*/

/*Lets try for the insert and update and delete*/

DROP TRIGGER TRIGGER_TEMP_RECORDS

CREATE TRIGGER TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
FOR INSERT,DELETE,UPDATE
AS
BEGIN

SELECT 'Hi... Trigger ''''TRIGGER_TEMP_RECORDS'''' fired'

IF UPDATE(E_NAME) /*Checking the update data modification-- E_NAME is the column name*/
SELECT 'Hi... Records Updated successfully'
END

INSERT INTO TEMP_RECORDS
SELECT 'anand'
/*Here Both statements are printed.. It means, Insert Statement modified the value of column E_NAME to new value*/

DELETE FROM TEMP_RECORDS where E_NAME = 'Moov'
/*Here One statement printed(Records Updated successfully).. It means, Delete Statement not update the value of column E_NAME*/

UPDATE TEMP_RECORDS
SET E_NAME = 'Vicky-TA'
WHERE E_NAME = 'Vicky'
/*Here Both statements are printed.. It means, update Statement modified the value of column E_NAME to new value*/

/*Now Insert New Column*/
ALTER TABLE TEMP_RECORDS
ADD AGE INT

/*Insert Records only in Age*/

INSERT INTO TEMP_RECORDS(AGE)
SELECT 10
select * from TEMP_RECORDS
/*Here Both statements are printed.. It means, Insert Statement modified the value of column E_NAME to null value*/

UPDATE TEMP_RECORDS
SET AGE = 10
WHERE E_NAME = 'Vicky'
/*Here One statement printed(Trigger ''TRIGGER_TEMP_RECORDS'' fired).. It means, update Statement on AGE column not considered in trigger.*/

/*This is the basics of Trigger... Here you learned we can do some logics while modifying data..*/

/*Next one Trigger with Magic Tables...*/

/*Magic tables are virtual tables, You can't select this table in real time, but u can select this in Run time of data modification.. Only in Trigger*/
/*
INSERTED,
DELETED
*/


ALTER TRIGGER TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
FOR INSERT,DELETE,UPDATE
AS
BEGIN

SELECT 'INSERTED',* FROM INSERTED
SELECT 'DELETED',* FROM DELETED
END


INSERT INTO TEMP_RECORDS
SELECT 'KC',20
/*
Here, No records deleted.. New values displayed in result set
*/

SELECT * FROM TEMP_RECORDS

DELETE FROM TEMP_RECORDS where E_NAME = 'anand'
/*
Here, No records Inserted.. Deleted values displayed in result set
*/

UPDATE TEMP_RECORDS
SET E_NAME = 'Vignesh'
WHERE E_NAME = 'Vicky-TA'

/*
Here, Inserted  and Deleted values displayed
*/

/*
So we can use Magic tables to identify old deleted values.. we can maintain it in another table
This is the logic we used in BAS Project
*/



/*Instead of Trigger*/
drop trigger TRIGGER_TEMP_RECORDS

drop trigger INSTEAD_TRIGGER_TEMP_RECORDS

CREATE TRIGGER INSTEAD_TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
INSTEAD OF INSERT,DELETE,UPDATE
AS
BEGIN

DECLARE @ename VARCHAR(100)
SELECT @ename = E_NAME FROM INSERTED /*Magic table used here*/

if @ename like '%vicky%'
begin
print 'You cant insert names like vicky'
rollback
end

END

/*Now try to insert record*/

truncate table TEMP_RECORDS

INSERT INTO TEMP_RECORDS
SELECT 'vicky',20

select * from TEMP_RECORDS
/*Errored out..Record not Inserted*/

INSERT INTO TEMP_RECORDS
SELECT 'kc',20
select * from TEMP_RECORDS
/*Record not inserted. You need to specify the insert statement inside trigger*/


ALTER TRIGGER INSTEAD_TRIGGER_TEMP_RECORDS ON TEMP_RECORDS
INSTEAD OF INSERT,DELETE,UPDATE
AS
BEGIN

DECLARE @ename VARCHAR(100)
SELECT @ename = E_NAME FROM INSERTED /*Magic table used here*/

IF @ename like '%vicky%'
BEGIN
PRINT 'You cant insert names like vicky'
ROLLBACK
END
ELSE
begin
INSERT INTO TEMP_RECORDS
SELECT E_NAME,AGE from INSERTED

end

END

/*Now Insert same record*/


INSERT INTO TEMP_RECORDS
SELECT 'kc',20
SELECT * FROM TEMP_RECORDS

/*So, Instead Of Trigger used to restrict the INSERT/DELETE/UPDATE...*/


/*Ok.. We have seen only about DML Trigger.. DDL Trigger should be learned by self.. Because.. it can be used against database developers.. If anything wrong in DDL or long on trigger.. you cant use databse.*/


/*Output Clause*/


CREATE TABLE TEMP_RECORDS_output
(
SNO INT IDENTITY(1,1),
E_NAME VARCHAR(100)
)

insert into TEMP_RECORDS_output
OUTPUT inserted.*
select 'vicky'

delete from TEMP_RECORDS_output
OUTPUT deleted.*
where E_NAME = 'vicky'


insert into TEMP_RECORDS_output
OUTPUT inserted.* into temp_log
select 'vicky'

create table temp_log
(
new_value varchar(100),
old_value varchar(100)
)


update TEMP_RECORDS_output
set E_NAME = 'vicky_ta'
OUTPUT inserted.E_NAME,deleted.E_NAME into temp_log
where E_NAME = 'vicky'

select * from temp_log

Top Clause

DROP TABLE TEMP_UPDATE

CREATE TABLE TEMP_UPDATE
(
SNO INT IDENTITY(1,1),
EMP_NAME VARCHAR(20),
SALARY MONEY
)

INSERT INTO TEMP_UPDATE
SELECT 'VICKY',0 UNION SELECT 'ANAND',0 UNION SELECT 'BS',0 UNION SELECT 'AS',0 UNION SELECT 'KC',0

SELECT  * FROM TEMP_UPDATE

/*Use Top Clause in Select Statement*/
SELECT TOP 2 * FROM TEMP_UPDATE

/*Use Top Clause in Update Statement*/
UPDATE TOP (2) TEMP_UPDATE
SET SALARY = 1000

SELECT  * FROM TEMP_UPDATE

/*Use Top Clause in Insert Statement*/
INSERT TOP (2) INTO TEMP_UPDATE (EMP_NAME,SALARY)
SELECT  EMP_NAME,SALARY FROM TEMP_UPDATE

SELECT  * FROM TEMP_UPDATE

/*Use Top Clause in Delete Statement*/

DELETE TOP (2) FROM TEMP_UPDATE

SELECT  * FROM TEMP_UPDATE


/*We can Use parameter in Top Clause*/

DECLARE @topvalue INT
SET @topvalue = 2
SELECT  TOP (@topvalue) *  FROM TEMP_UPDATE

/*We can use PERCENT to list particular % of table rows*/
SELECT  * FROM TEMP_UPDATE
--5 rows

SELECT  TOP (50) PERCENT * FROM TEMP_UPDATE
--3 rows
SELECT  TOP (20) PERCENT * FROM TEMP_UPDATE
--1 row
SELECT  TOP (25) PERCENT * FROM TEMP_UPDATE
--2 row


/*To Avoid incorrect result. always use ORDER BY clause with TOP operator*/

Temp Table

/*#,## and @ tables are stored int*/

DROP TABLE #TEMP_SINGLE_HASH

CREATE TABLE #TEMP_SINGLE_HASH
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into #TEMP_SINGLE_HASH
SELECT 'Calendar',100,50 UNION
SELECT 'Watch',1200,25 UNION
SELECT 'Shirt',1099,100 UNION
SELECT 'Laptop',45000,50 UNION
SELECT 'Phone',15420,75

select * from #TEMP_SINGLE_HASH

/*
This table stored in database disk storage; This scope only in this window

Try to run this (select * from #TEMP_SINGLE_HASH) in another window

you will get,

Msg 208, Level 16, State 0, Line 1
Invalid object name '#TEMP_SINGLE_HASH'.
*/

/*
But moreover, this table like normal physical table.. So u can add primary,uniquey key, and non-clustered index

So we can perform large data with this #table
*/

/***********************************************************************************************************************************/
DROP TABLE ##TEMP_SINGLE_HASH

CREATE TABLE ##TEMP_SINGLE_HASH
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into ##TEMP_SINGLE_HASH
SELECT 'Calendar',100,50 UNION
SELECT 'Watch',1200,25 UNION
SELECT 'Shirt',1099,100 UNION
SELECT 'Laptop',45000,50 UNION
SELECT 'Phone',15420,75


/*## is the Global temp table.. any user can use it.

Try to run this (select * from #TEMP_SINGLE_HASH) in another window
--Query will run

This scope lost until the session is closed

Close this window
Try to run this (select * from #TEMP_SINGLE_HASH) in another window'

Msg 208, Level 16, State 1, Line 1
Invalid object name '##TEMP_SINGLE_HASH'.

Like #table u can add primary,uniquey key, and non-clustered index

*/

/***********************************************************************************************************************************/


/*This is like variable; you need to run all this at same time*/

/*This table is variable; so its stored in memory*/

DECLARE @TEMP_SINGLE_HASH TABLE
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into @TEMP_SINGLE_HASH
SELECT 'Calendar',100,50 UNION
SELECT 'Watch',1200,25 UNION
SELECT 'Shirt',1099,100 UNION
SELECT 'Laptop',45000,50 UNION
SELECT 'Phone',15420,75


SELECT * FROM @TEMP_SINGLE_HASH

/**Check  this*/

DECLARE @TEMP_SINGLE_HASH TABLE
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into @TEMP_SINGLE_HASH
SELECT 'Calendar',100,50

begin tran
delete from @TEMP_SINGLE_HASH
rollback


SELECT * FROM @TEMP_SINGLE_HASH

/*Rollback not happen, transaction will not affect this.. because it stored in memory*/

DROP TABLE #TEMP_SINGLE_HASH

CREATE TABLE #TEMP_SINGLE_HASH
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into #TEMP_SINGLE_HASH
SELECT 'Calendar',100,50

begin tran
delete from #TEMP_SINGLE_HASH
rollback

select * from #TEMP_SINGLE_HASH


/*Rollback happen, transaction will affect this.. because it stored in disk storage*/

/*Like #table u can add primary,uniquey key, but u cant add non-clustered index*/
/*
So use # for larger process
and @ for smaller process
*/

Swapping Variable

declare @var1 int
declare @var2 int

set @var1 = 1
set @var2 = 2


SELECT @var1 =@var1 +@var2
select @var1,@var2
select @var2=@var1- @var2
select @var1,@var2
select @var1=@var1-@var2
select @var1,@var2

/*Table*/

create table #temp
(
Value1 varchar(10),
Value2 varchar(10)
)

insert into #temp
select '1','2'

update #temp
set Value1 = Value2,Value2=Value1


select * from #temp

SQL Injection

CREATE TABLE TEMP_TEST_DETAILS
(
SNO INT,
EMP_NAME VARCHAR(100)
)

INSERT INTO TEMP_TEST_DETAILS
SELECT 1,'TEST'

/*Ordinary Dynamic SQL Procedure*/
ALTER PROCEDURE GetEmpDetails
@EmpName VARCHAR(200)
AS
BEGIN

DECLARE @Query VARCHAR(2000)

SET @Query = 'SELECT * FROM TEMP_TEST_DETAILS WHERE EMP_NAME = ' + '''' + @EmpName + ''''

PRINT @Query
EXEC (@Query)

END

/*Getting Results from above SQL Procedure*/
EXEC GetEmpDetails 'TEST'

/*Some Trick used with single quotation mark*/
EXEC GetEmpDetails 'TEST'';  select ''1'

/*Finally Bomb!!!*/
EXEC GetEmpDetails 'TEST''; DROP TABLE TEMP_TEST_DETAILS; SELECT ''1'

SELECT * FROM TEMP_TEST_DETAILS

Over Clause

/*******************************/
DROP TABLE #TEMP

CREATE TABLE #TEMP
(
Emp_Name varchar(100),
Age int,
Department Varchar(100),
Salary_Month datetime,
Salary money
)

INSERT INTO #TEMP
SELECT 'Anand',25,'Software','01/01/2013',2000
UNION
SELECT 'Anand',25,'Software','02/01/2013',2000
UNION
SELECT 'Anand',25,'Software','03/01/2013',2000
UNION
SELECT 'Anand',25,'Software','04/01/2013',2000
UNION
SELECT 'Anand',25,'Software','05/01/2013',2000
UNION
SELECT 'Anand',25,'Software','06/01/2013',2000
UNION
SELECT 'Vicky',28,'Software','01/01/2013',2300
UNION
SELECT 'Vicky',28,'Software','03/01/2013',2300
UNION
SELECT 'Vicky',28,'Software','04/01/2013',2300
UNION
SELECT 'Vicky',28,'Software','05/01/2013',2300
UNION
SELECT 'Vicky',28,'Software','06/01/2013',2300
UNION
SELECT 'AS',26,'Software','01/01/2013',2500
UNION
SELECT 'AS',26,'Software','02/01/2013',2500
UNION
SELECT 'AS',26,'Software','03/01/2013',2500
UNION
SELECT 'AS',26,'Software','04/01/2013',2500
UNION
SELECT 'AS',26,'Software','06/01/2013',2500
UNION
SELECT 'BS',26,'Software','01/01/2013',3100
UNION
SELECT 'BS',26,'Software','02/01/2013',3100
UNION
SELECT 'BS',26,'Software','04/01/2013',3100
UNION
SELECT 'BS',26,'Software','05/01/2013',3100
UNION
SELECT 'BS',26,'Software','06/01/2013',3100
union
SELECT 'Naresh',26,'Billing','01/01/2013',2100
UNION
SELECT 'Naresh',26,'Billing','02/01/2013',2100
UNION
SELECT 'Naresh',26,'Billing','04/01/2013',2100
UNION
SELECT 'Naresh',26,'Billing','05/01/2013',2100
UNION
SELECT 'Naresh',26,'Billing','06/01/2013',2100
union
SELECT 'Prabhu',26,'Billing','01/01/2013',1800
UNION
SELECT 'Prabhu',26,'Billing','02/01/2013',1800
UNION
SELECT 'Prabhu',26,'Billing','04/01/2013',1800
UNION
SELECT 'Prabhu',26,'Billing','05/01/2013',1800
UNION
SELECT 'Prabhu',26,'Billing','06/01/2013',1800


/*******************************/

/*Points to Remember*/

/**Over Clause is used to partition or order the given set of rows**/

/**Over Clause Always used with Aggregate / ranking functions**/

/**What is aggregate and Ranking? Which function belongs to Partition or Order?**/

/*Order By is related to Ranking function*/

/*Partition is related to Aggregate function*/

/*Now Come to Over Clause.. You can use any of the aggregate/ranking function with Over Clause

Syntax:

<<Aggregate/Ranking function>> OVER (<<Partition By/Order By>> ColumnName)

We can do lot of magics using Over Clause.

Check Below,

*/
/***************************************************************************************************************/

/*1.First, we can generate serial number (Row number) in select list*/

/* For this use Row number function in over clause*/
SELECT ROW_NUMBER() OVER (ORDER BY EMP_NAME) as row_number,* FROM #TEMP
/*Here row number is listed based on emp_name ascending order*/

SELECT ROW_NUMBER() OVER (ORDER BY EMP_NAME desc)  as row_number,* FROM #TEMP
/*Here row number is listed based on emp_name descending order*/

/*Like this we can generate row number by multible order by combination*/

SELECT ROW_NUMBER() OVER (ORDER BY Salary desc,EMP_NAME desc) as row_number,* FROM #TEMP

/***************************************************************************************************************/

/*2. We can get Rank based on particular values*/

SELECT RANK() OVER (ORDER BY salary desc) as Rank,* FROM #TEMP

/*We can get rank even by Age :-) */

SELECT RANK() OVER (ORDER BY age desc) as Rank,* FROM #TEMP

/*3. We can get Dense Rank based on particular values*/

SELECT dense_RANK() OVER (ORDER BY salary desc) as Rank,* FROM #TEMP

/*Find out the difference of dense rank and rank from both the select list*/


/***************************************************************************************************************/

/*4. NTILE -- It will split the row set into specific number of groups based on specific column*/
SELECT NTILE(4) OVER (ORDER BY salary desc) as NTILE,* FROM #TEMP

/*Here, total 31 rows splitted into 4 groups, 8 + 8 + 8 + 7 and Numbered 1,2,3,4 based on salary column
use this feature right place in ur project
*/

/*Yes.. We finished Ranking function with over clause*/

/*Now Aggregate functions.. Sum, Min, Max,count, etc..*/

/*Remember, here you have to use, partition by in over clause*/

/*****************************************************************************/

/*6.max*/
SELECT DISTINCT COUNT(SALARY_MONTH) OVER (PARTITION BY DEPARTMENT) AS CNT,DEPARTMENT FROM #TEMP
/*Here we are getting, number of salries given by each department*/

SELECT distinct max(salary) OVER (PARTITION BY EMP_NAME),EMP_NAME,DEPARTMENT  FROM #TEMP
/*Here we are getting, maximum salry got by each employee*/

SELECT distinct min(salary) OVER (PARTITION BY DEPARTMENT),DEPARTMENT,EMP_NAME  FROM #TEMP

/*Here we are getting, minimum salry given by department to employee*/


/*

There is lot of real time usage is there in Over Clause. Try to find out this feature in reight place of your project.

All the best.. Thank You! :-)

**/

Identity

DROP TABLE test_identity
CREATE TABLE test_identity
(
sno int identity(1,1),
emp_name varchar(10)
)
go

DROP TABLE test_identity_1
CREATE TABLE test_identity_1
(
sno int identity(1,1),
emp_name varchar(10)
)
go

/*Check the identity value for these two inserts*/
INSERT INTO test_identity
select 'moov'

SELECT @@IDENTITY,SCOPE_IDENTITY()

INSERT INTO test_identity
SELECT 'kc'
SELECT @@IDENTITY,SCOPE_IDENTITY()

/*Creating Trigger*/
CREATE TRIGGER Trigger_Identity ON test_identity FOR INSERT
AS
BEGIN

   INSERT INTO test_identity_1
SELECT 'ANAND'

END
go

/*Now Check the Identity*/
INSERT INTO test_identity
select 'Vicky'
SELECT @@IDENTITY,SCOPE_IDENTITY()


/*Run this in seperate Window*/
INSERT INTO test_identity
select 'AS'

/*Run this here*/
SELECT @@IDENTITY,SCOPE_IDENTITY(),IDENT_CURRENT ('test_identity')


-- So,@@IDENTITY and SCOPE_IDENTITY() will give current session identity values.. But @@IDENTITY will be affected by Triggers used in table
-- So, Always use SCOPE_IDENTITY() to avoid errors
-- IDENT_CURRENT ('TableName') gives current identity value of specified table regardless of the session

/********** Thats All Today **********************/

Go Statement

/*Go - this command will send the current batch of query to no of times to sql instance*/
drop table #Test
create table #Test
(
sno int identity(1,1),
sd char(4)
)

go
insert into #Test select 'ss'
go 10

/*the above statement will execute 10 times*/

select * from #Test

/**************************/

/*please Check the differences of all below 3 set of statements: which one is printing more than 1 times!*/

print 'hi'
print 'hello'
go 5

/**/

print 'hi'
go 5
print 'hello'

/**/

go 5
print 'hi'
print 'hello'


/**********************/

declare @msg char(10)
set @msg= 'hi'

select @msg  as 'before go'
go
print @msg  as 'after go'

/*please Check the scope of the parameter declaration after sending GO commend*/

/**************/

CTE

DROP TABLE #TEMP_PRODUCTS

CREATE TABLE #TEMP_PRODUCTS
(
PRODUCT_ID INT IDENTITY(1,1),
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE MONEY,
PRODUCT_QUANTIY MONEY
)

insert into #TEMP_PRODUCTS
SELECT 'Calendar',100,50 UNION
SELECT 'Watch',1200,25 UNION
SELECT 'Shirt',1099,100 UNION
SELECT 'Laptop',45000,50 UNION
SELECT 'Phone',15420,75

/*Simple CTE Query*/
;with cte_products /*CTE Table Name*/
as
(
select * from #TEMP_PRODUCTS where PRODUCT_QUANTIY >30
)
select * from cte_products

/*Simple CTE Query with column names*/
;with cte_products (prod_desc,price,quantity) /*Column name*/
as
(
select PRODUCT_NAME,PRODUCT_PRICE,PRODUCT_QUANTIY from #TEMP_PRODUCTS where PRODUCT_QUANTIY >30
)
select prod_desc,price,quantity from cte_products where price >2000 /*Same Column Name Should be used*/


/*Note: You need to select the CTE table immediatly after defining (scope will lost after that line): Below will produce error*/

;with cte_products
as
(
select * from #TEMP_PRODUCTS where PRODUCT_QUANTIY >30
)
select 1
select * from cte_products
/*
Msg 422, Level 16, State 4, Line 8
Common table expression defined but not used.
**/

/*So the Query should be:*/

;with cte_products
as
(
select * from #TEMP_PRODUCTS where PRODUCT_QUANTIY >30
)
select * from cte_products
select 1

/*But you can use CTE tables multible times with joins and unions*/
;with cte_products
as
(
select * from #TEMP_PRODUCTS where PRODUCT_QUANTIY >30
)
select * from cte_products
union all
select * from cte_products

/*u can delete,update and insert operations in CTE table: record will be deleted in #TEMP_PRODUCTS*/

begin tran
;with cte_products
as
(
select * from #TEMP_PRODUCTS
)
delete from cte_products where product_id >= 3

select * from #TEMP_PRODUCTS
rollback

/****************************************************************/

/*Recursive CTE Query*/

/*using CTE tables with in CTE makes recursive*/

/*For Example, we need to split this into each row using comma seperator*/

--Consider this string 'MOOV,ANAND,VICKY,BS,AS,KC'

/*We can seperate the names by finding position of comma
For example, 1st comma position is 5
so,

select substring('MOOV,',1,5-1)

2nd comma position is 6
so,

select substring('ANAND,',5+1,12-1)

so we need postion like this format
start end
1 5
6 11
12 17

*/

DECLARE @String VARCHAR(1000)
SET @String = 'MOOV,ANAND,VICKY,BS,AS,KC'
SET @String = @String + ','

;with cte_recursive(Start,[End])
as
(

SELECT 1 as 'Start',CHARINDEX(',',@String,1) as 'End'
union all
SELECT [End]+1 as 'Start',CHARINDEX(',',@String,1+[end]) as 'End' from cte_recursive where [end] < len(@String) /*Using cte table inside cte expression*/
)
select start,[end],@String,SUBSTRING(@String,Start,[End]-Start) as Result from cte_recursive


/*
Important note: Recursive property will call the same CTE table again and again.. So use it in right place of ur project
Just study the above query well deeper.. We can use CTE recursive in many places. It will avoid while loops and complex querying;

CTE and derived tables are moreover same!.. Recursive is the top feature in CTE.. So, Look deeper in Recursive CTE...
*/

Better Understanding

/*APPLY*/

/*Today I want to tell the one very good new feature of SQL 2005 -- APPLY (CROSS APPLY/OUTER APPLY)*/


/*We can use APPLY operator with table valued function. Before going to APPLY. I ll brief types of Functions*/

/*Two Main types of functions*/
--1.System Function
--2.User defined function

--1.System Functions

/*These Functions already defined in SQL server for various operations*/

/*We can split system functions to two types*/
--1.Scalar Functions
--2.Aggregate Functions

--1.Scalar Functions

/*It works only for single value/variable and returns single value*/
/*Ex:*/

SELECT ABS(-10) /*It will return absolute value*/

DECLARE @Name VARCHAR(10)
SET @Name = 'moov'
select UPPER(@Name) /*It will convert Lower case to Upper Case*/

/*Some more Scalar functions are : LTRIM(),RTRIM(),ROUND(),CONVERT(),....*/

--2.Aggregate Functions
/*It will accept multible rows of values and return one result*/

if object_id('tempdb..#temp') is not null
DROP table #temp
GO
create table #temp
(
sno int,
e_name varchar(100),
dept  varchar(100)
)
go
insert into #temp
select 1,'vicky','sw' union select 2,'moov','bill' union select 3,'anand','acc' union select 5,'AS','sw'

select max(sno) from #temp /*It will return maximum value from 3 rows*/

SELECT COUNT(sno) from #temp /*It will return count of table*/

/*Some more Aggregate functions are : MIN(),AVG(),....*/

--2.User defined function
/*This functions are defined by SQL developers*/

/*We can split User defined functions to two types*/

--1.Scalar Functions
--2.Table valued Functions

--1.Scalar Functions
/*Again it accepts single values return single value*/


IF OBJECT_ID('dbo.FN_ADD_NAME') IS NOT NULL
DROP FUNCTION dbo.FN_ADD_NAME
GO

CREATE FUNCTION FN_ADD_NAME
(
@Name1 VARCHAR(100), /*Inputs*/
@Name2 VARCHAR(100)
)
RETURNS VARCHAR(500)
AS
BEGIN
RETURN (@Name1 + ' From ' + @Name2 + ' Team.')
END

/*We use the above function in select list of table*/
GO
SELECT sno,e_name,dept,dbo.FN_ADD_NAME(e_name,dept) as msg from #temp

/*Or we can use simple select list*/

SELECT dbo.FN_ADD_NAME('BS','Claims')

--2.Table valued Functions
/*It will return table values*/

IF OBJECT_ID('dbo.TEMP_SALARY') IS NOT NULL
DROP table dbo.TEMP_SALARY

go
CREATE TABLE TEMP_SALARY
(
SNO INT,
SALARY MONEY
)

INSERT INTO TEMP_SALARY
SELECT 1,'1000' UNION SELECT 2,'2000' UNION SELECT 3,'1500' UNION SELECT 4,'3000'


IF OBJECT_ID('dbo.FN_RETURN_SALARY') IS NOT NULL
DROP FUNCTION dbo.FN_RETURN_SALARY
go

CREATE FUNCTION FN_RETURN_SALARY
(
@sno INT, /*inputs*/
@AllValue INT
)
RETURNS @Salary TABLE
(
SNO INT,
SALARY MONEY
)
AS
BEGIN

INSERT INTO @Salary
SELECT * FROM TEMP_SALARY WHERE SNO =@sno OR 1=@AllValue /*Simple Trick to restrict output rows*/

RETURN
END

GO
/*We use the above function in simple select*/

SELECT * FROM FN_RETURN_SALARY(1,1) /*All values - 1  -- Return Sno 1*/

SELECT * FROM FN_RETURN_SALARY(4,2) /*Specific Value -- Return Sno 4*/

/*Now I want to get Salar with each name from these two tables using table function*/

SELECT * FROM #TEMP
SELECT * FROM TEMP_SALARY

/*You cant use inner join here. But you can use APPLY here- This is the new feature in SQL 2005*/

SELECT * FROM #TEMP
CROSS APPLY FN_RETURN_SALARY(SNO,2) /*2 means return specific value*/


SELECT * FROM #TEMP
OUTER APPLY FN_RETURN_SALARY(SNO,2) /*2 means return specific value*/

/*It will return NULL if function reurn nothing*/


/*So You can use APPLY operator with table valued function effectively. We can use this feature extensivly in our project*/

/*
Note:

/*BAS Team : You cant use this feature in our SQL 2005. Because this feature requires db compatability level should be 90. Our level is 80
So I created DB "KSCOPE" with db compatability 90 in dtdbtest server. You can use this feature in "KSCOPE" db. If you want to know about db compatability level
Please contact me.

/*If you want to see the error try to run this in dtdbtest..*/
*/

/*
DenRX Team : You can simply run this in your environment ;-)
*/

Thank You!
*/

trigger script

create table trig_parent(id int ,name varchar(20))
create table trig_child(id int ,name varchar(20))


create trigger trg_parent_id on trig_parent
for insert
as
begin

declare @id int,@name varchar(20)
select @id=id,@name=name from inserted
insert into trig_child values(@id,@name)


end

select  *from trig_parent
select * from trig_child

alter table trig_parent add sno int identity(1,1)

update trig_parent set name='vinoth'

insert into trig_parent values(4,'new')

create trigger trg_parent_upd on trig_parent
for update
as
begin

declare @id int,@name varchar(20)
select @id=id,@name=name from inserted
insert into trig_child values(@id,@name)



select @id=id,@name=name from deleted
insert into trig_child values(@id,@name)

end

select * from trig_parent where name='vinoths'

delete from trig_parent where sno in(select sno from (select ROW_NUMBER() over(partition by name order by name) sr_no,* from trig_parent)x
where sr_no<>1)

select sno from (select ROW_NUMBER() over(partition by name order by name) sr_no,* from trig_parent)x
where sr_no<>1

Filter Date of birth for same age and particular month

create table ##age (id int,dob datetime)
insert into ##age values(1,'1990-02-25 09:18:58.740')
insert into ##age values(2,'1990-07-01 09:18:58.740')
insert into ##age values(3,'1998-05-25 09:18:58.740')
insert into ##age values(4,'1990-08-25 09:18:58.740')

create table #temp (id int,dob datetime,age int,month int)

insert into #temp (id,dob,age,month)
select id,dob,DATEDIFF(yy, dob, GETDATE()) - CASE WHEN
(MONTH(dob) > MONTH(GETDATE())) OR (MONTH(dob) = MONTH(GETDATE()) AND DAY(dob) > DAY(GETDATE())) THEN 1 ELSE 0 END as age,month(dob) as [month] from ##age

select * from #temp
select * from #temp where age =26 and [month] in (1,2,3,4,5,6,7,8)

How to calculate accurate age from date of birth in sql

DECLARE @date datetime, @tmpdate datetime, @age int
SELECT @date = '02/25/91'
SELECT @tmpdate = @date
SELECT @age = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @age