Hello All,

     From Recent Search , Vinay find good  & quick solution for inserting record from one old table to new table, with specific columns

 

 INSERT INTO new_table (columns....)
SELECT columns....
FROM initial_table where [condition]
using this query we can insert some records form old table to new table with given condition.
thanks vinay for your solution.

Hi All,

   Recently @ time of checking records in Database table with case sensitive.

  We are firing query like

        select * from Tablename where strPassword = 'Vinay343@'

  at the same time if we fire query like

        select * from Tablename where strPassword = 'VINAY343@'

 we are getting same result so technically me & vinay think that it is not good to get result if user send case sensitive input

 after searching we found the solution that if we want to check exact match use query like this

  select * from Tablename where strPassword COLLATE Latin1_General_CS_AS='Vinayshah0!'

here We get exact match as per the input

Thanks Vinay for sharing this information

Cheers!!!!

Calculate Age from Sql function

In one of my interview , interviewer asked me how should  you calculate age from date of birth.

     As i m not too strong in SQL server , i have started from .Net code  for calculating Age from  DOB.
     but not success ed in that.  after searching in books & discussing with friends[Vinay Shah]
     come to conclusion that we can get Age  from Sql Function DATEDIFF.

   in SQL Server 2005 u can use Datediff function

   Sample Query = select datediff (year, '1988-05-17 00:00:00.000' , getDate())

  from above query you will get the Age in year  OUTPUT = 24
 instead of  1988-05-17 00:00:00.000   you can enter your DOB & get your Age.


  in Oracle 

   select ROUND((SysDate - DOB)/365) as Age from cust_table

  here you we will get age of the customers where DOB should have Date DataType

      Using Datediff function we can calculate not only age in years but   also following

              

Unit of time	Query	Result
NANOSECOND	SELECT DATEDIFF(NANOSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133')	4321300
MICROSECOND	SELECT DATEDIFF(MICROSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133')	4321
MILLISECOND	SELECT DATEDIFF(MILLISECOND,'2011-09-23 17:15:22.004','2011-09-23 17:15:22.548')	544
SECOND	SELECT DATEDIFF(SECOND,'2011-09-23 17:15:30','2011-09-23 17:16:23')	53
MINUTE	SELECT DATEDIFF(MINUTE,'2011-09-23 18:03:23','2011-09-23 17:15:30')	-48
HOUR	SELECT DATEDIFF(HH,'2011-09-23 18:03:23','2011-09-23 20:15:30')	2
WEEK	SELECT DATEDIFF(WK,'09/23/2011 15:00:00','12/11/2011 14:00:00')	12
DAY	SELECT DATEDIFF(DD,'09/23/2011 15:00:00','08/02/2011 14:00:00')	-52
DAYOFYEAR	SELECT DATEDIFF(DY,'01/01/2011 15:00:00','08/02/2011 14:00:00')	213
MONTH	SELECT DATEDIFF(MM,'11/02/2011 15:00:00','01/01/2011 14:00:00')	-10
QUARTER	SELECT DATEDIFF(QQ,'01/02/2011 15:00:00','08/01/2011 14:00:00')	2
YEAR	SELECT DATEDIFF(YY,'01/02/2011 15:00:00','01/01/2016 14:00:00')	5

If we want to Calculate Sum of 2 tables count we can use following  query

SELECT SUM(c)
FROM (
  SELECT COUNT(id) AS c FROM table1
  UNION ALL
  SELECT COUNT(id) FROM table2
  ) as b

Delete Duplicate Rows

/* Create Table with 7 entries - 3 are duplicate entries */

CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

The above table has total 7 records, out of which 3 are duplicate records. Once the duplicates are removed we will have only 4 records left.

/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO

The most interesting part of this is yet to come. We will use CTE that will re-generate the same table with additional column, which is row number. In our case, we have Col1 and Col2 and both the columns qualify as duplicate rows. It may be a different set of rows for each different query like this. Another point to note here is that once CTE is created DELETE statement can be run on it. We will put a condition here – when we receive more than one rows of record, we will remove the row which is not the first one. When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

It is apparent that after delete command has been run, we will have only 4 records, which is almost the same result which we would have got with DISTINCT, with this resultset. If we had more than 2 columns and we had to run unique on only two columns, our distinct might have not worked here . In this case, we would have to use above the mentioned method.

/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO

INDEXING IN SQL SERVER

Using the Right Indexes for Optimal Performance Query optimization is a complex game with its own rules.

Let’s look at three examples to discover when SQL Server Query Optimizer uses clustered indexes and non-clustered indexes to retrieve data and when to use the primary key (PK) to influence performance.

Example 1: Default Index Usage

Let’s look first at Query Optimizer’s default use of indexes.

Query 1’s query cost (relative to the batch) is much lower than Query 2’s query cost (relative to the batch).

In this example, no query hint is specified, so Query Optimizer can use any index it wants to use, which results in optimal performance.

Notice that even though ContactID (the primary key [PK] of the Contact table) is retrieved in Query 1, Query Optimizer does not use a primary key clustered index; instead, it uses a non-clustered index. In Query 2, on the other hand, Query Optimizer uses a clustered index on PK where all columns (*) are retrieved.

This may be surprising. It is a common belief that when PK columns are used and no other condition or joins are used, Query Optimizer will use a PK clustered index to return the results of a SELECT statement. However, this is not always true.

Example 2 : Forcing a Primary Key (PK) Clustered Index

Let’s see how performance changes when a primary key clustered index is used to retrieve data.

Query 1’s query cost (relative to the batch) is equals to Query 2’s query cost (also relative to the batch).

In this example, we are using a primary key clustered index to retrieve data. The same execution plan is created whether we retrieve only one column or all the columns.

Example 3: Forcing Non-Clustered Index

Now consider two queries in which a non-clustered index is used to retrieve data.

Query 1’s query cost (relative to the batch) is much lower than Query 2’s query cost (also relative to the batch).

In this example, we are using a non-clustered index to retrieve data. From the execution plan, it is very clear that retrieving only one column is much faster than retrieving all the columns from the Contact table. When all the columns from a table are selected, a PK clustered index is clearly the best option.

INTRODUCTION
TRIGGERS IN SQL SERVER
BACKGROUND
This article gives a brief introduction about Triggers in Sql Server 2000/2005.
What is a Trigger
A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.
Types Of Triggers
There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE. So, there are three types of triggers and hybrids that come from mixing and matching the events and timings that fire them.

Basically, triggers are classified into two main types:-

(i) After Triggers (For Triggers)
(ii) Instead Of Triggers
(i) After Triggers
These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further into three types as:

(a) AFTER INSERT Trigger.
(b) AFTER UPDATE Trigger.
(c) AFTER DELETE Trigger.

Let’s create After triggers. First of all, let’s create a table and insert some sample data. Then, on this table, I will be attaching several triggers.

Collapse

CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)

INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);


I will be creating an AFTER INSERT TRIGGER which will insert the rows inserted into the table into another audit table. The main purpose of this audit table is to record the changes in the main table. This can be thought of as a generic audit trigger.

Now, create the audit table as:-
Collapse

CREATE TABLE Employee_Test_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)

(a) AFTRE INSERT Trigger
This trigger is fired after an INSERT on the table. Let’s create the trigger as:-
Collapse

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]
FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';

insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'
GO

The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of FOR INSERT, AFTER INSERT can be used. Both of them mean the same.
In the trigger body, table named inserted has been used. This table is a logical table and contains the row that has been inserted. I have selected the fields from the logical inserted table from the row that has been inserted into different variables, and finally inserted those values into the Audit table.
To see the newly created trigger in action, lets insert a row into the main table as :
Collapse

insert into Employee_Test values('Chris',1500);


Now, a record has been inserted into the Employee_Test table. The AFTER INSERT trigger attached to this table has inserted the record into the Employee_Test_Audit as:-
Collapse

6 Chris 1500.00 Inserted Record -- After Insert Trigger. 2008-04-26 12:00:55.700

(b) AFTER UPDATE Trigger
This trigger is fired after an update on the table. Let’s create the trigger as:-
Collapse

CREATE TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]
FOR UPDATE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;

if update(Emp_Name)
set @audit_action='Updated Record -- After Update Trigger.';
if update(Emp_Sal)
set @audit_action='Updated Record -- After Update Trigger.';

insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER UPDATE Trigger fired.'
GO

The AFTER UPDATE Trigger is created in which the updated record is inserted into the audit table. There is no logical table updated like the logical table inserted. We can obtain the updated value of a field from the update(column_name) function. In our trigger, we have used, if update(Emp_Name) to check if the column Emp_Name has been updated. We have similarly checked the column Emp_Sal for an update.
Let’s update a record column and see what happens.
Collapse

update Employee_Test set Emp_Sal=1550 where Emp_ID=6

This inserts the row into the audit table as:-
Collapse

6 Chris 1550.00 Updated Record -- After Update Trigger. 2008-04-26 12:38:11.843

(c) AFTER DELETE Trigger
This trigger is fired after a delete on the table. Let’s create the trigger as:-
Collapse

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test]
AFTER DELETE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);

select @empid=d.Emp_ID from deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
set @audit_action='Deleted -- After Delete Trigger.';

insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER DELETE TRIGGER fired.'
GO

In this trigger, the deleted record’s data is picked from the logical deleted table and inserted into the audit table.
Let’s fire a delete on the main table.
A record has been inserted into the audit table as:-
Collapse

6 Chris 1550.00 Deleted -- After Delete Trigger. 2008-04-26 12:52:13.867

All the triggers can be enabled/disabled on the table using the statement
Collapse

ALTER TABLE Employee_Test {ENABLE|DISBALE} TRIGGER ALL

Specific Triggers can be enabled or disabled as :-
Collapse

ALTER TABLE Employee_Test DISABLE TRIGGER trgAfterDelete


This disables the After Delete Trigger named trgAfterDelete on the specified table.
(ii) Instead Of Triggers
These can be used as an interceptor for anything that anyonr tried to do on our table or view. If you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, and they will not actually get deleted (unless you issue another delete instruction from within the trigger)
INSTEAD OF TRIGGERS can be classified further into three types as:-

(a) INSTEAD OF INSERT Trigger.
(b) INSTEAD OF UPDATE Trigger.
(c) INSTEAD OF DELETE Trigger.

(a) Let’s create an Instead Of Delete Trigger as:-
Collapse

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]
INSTEAD OF DELETE
AS
declare @emp_id int;
declare @emp_name varchar(100);
declare @emp_sal int;

select @emp_id=d.Emp_ID from deleted d;
select @emp_name=d.Emp_Name from deleted d;
select @emp_sal=d.Emp_Sal from deleted d;

BEGIN
if(@emp_sal>1200)
begin
RAISERROR('Cannot delete where salary > 1200',16,1);
ROLLBACK;
end
else
begin
delete from Employee_Test where Emp_ID=@emp_id;
COMMIT;
insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
PRINT 'Record Deleted -- Instead Of Delete Trigger.'
end
END
GO

This trigger will prevent the deletion of records from the table where Emp_Sal > 1200. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed.
Now, let’s try to delete a record with the Emp_Sal >1200 as:-

Collapse

delete from Employee_Test where Emp_ID=4

This will print an error message as defined in the RAISE ERROR statement as:-

Collapse

Server: Msg 50000, Level 16, State 1, Procedure trgInsteadOfDelete, Line 15
Cannot delete where salary > 1200


And this record will not be deleted.
In a similar way, you can code Instead of Insert and Instead Of Update triggers on your tables.