Saturday, August 15, 2015

Happy Independence Day - Jai Hind

Wishing you a very

Happy Independence Day


" We owe a lot to the Indians, who taught us how to count, without which no worthwhile scientific discovery could have been made. "Albert Einstein.



     Some facts about India 
  1. Indus Valley Civilization is the world’s oldest civilization.So, India is the world’s oldest, most advanced and continuous civilization.
  2. India has the world’s third largest active army, after China and USA but India has never invaded or attacked a country.
  3. Today, India is the world’s third largest economy.
  4. The Tirupati Balaji temple and the Kashi Vishwanath Temple both, receive more visitors than the Vatican City and Mecca combined.
  5. Every 12 years, a religious gathering called the Kumbh Mela occurs in India. It is the world’s largest gathering of people.
  6. Varanasi, also known as Benaras, was called "the Ancient City" when Lord Buddha visited it in 500 B.C., and is the oldest, continuously inhabited city in the world today.
  7. India has third largest Muslim population in the world and has more mosques (300,000 mosques) than any other nation in the world.
  8. Jews and Christians have lived continuously in India since 200 B.C. and 52 A.D. respectively
  9. Two major religions, Buddhism and Jainism, were established in India.
  10. Takshila is said to be the first every university in the world; it started around 700 BC.
  11. Indian Railways employs more than 1.3 million people. That’s more than the population of many nations.
  12. India is the largest democracy in the world. More than 54 crore people voted in the 2014 General Election – more people than the population of USA, UK, Australia and Japan combined.
  13. India has the largest English speaking population in the world.
  14. India’s space program is one of the top 5 space programs in the world and off course we invented water on the moon.
  15. Chess was invented in India.
  16. And discovered the number zero.
  17. And the value of pi. 
  18. And trigonometry, algebra, calculus.
  19. Ayurveda is the earliest school of medicine known to mankind. The Father of Medicine, Charaka, consolidated Ayurveda 2500 years ago.
  20. Cataract surgery and plastic surgery are invented in India
  21. Around the 17th century, India was one of the richest countries in the world.
  22. India is one of the only three countries that makes supercomputers (the US and Japan are the other two).
  23. India has the largest amount of vegetarians in the world. So much so that Pizza Hut had to open their first pure vegetarian restaurant in the country and KFC had to introduce a “vegetarian” menu for India.
  24. India gave the world Yoga, that has existed for more than 5,000 years.
  25. Martial Arts was first created in India.

Proud To Be An Indian


Friday, August 14, 2015

SQL Script to find all the triggers defined on a database or on a single table

Sometime we need to find all the triggers defined on the database. So in this case, we can use the below SQL Query:-

SELECT
       tbl.name as [Table Name]
     , trig.name as [Trigger Name]
     , trig.is_disabled 
FROM [sys].[triggers] as trig
INNER JOIN sys.tables as tbl
ON trig.parent_id = tbl.object_id 

In case, if we need to find out all the triggers defined on a particular table then we can use the below SQL script

SELECT
       tbl.name as [Table Name]
     , trig.name as [Trigger Name]
     , trig.is_disabled 
FROM [sys].[triggers] as trig
inner join sys.tables as tbl
on trig.parent_id = tbl.object_id 
where tbl.name='Tblname'

/*  where Tblname name is the name of the table*/


Related article

SQL Triggers - An Introduction

Keep learning and don't forget to gives feedback on the article. You can also send feedback to me on my mailid  askvivekjohari@gmail.com


Thursday, August 13, 2015

Interview question - Is Clustered index on column with duplicate values possible?

Through this article, we are going to discuss three important interview questions of SQL which are given below:-


 1) Can we create clustered index on a column containing duplicate values?

 2) Can we create a Primary Key on a table on which a clustered index is already defined?

3) If a clustered index is already defined on a table and we create a primary key on this table, then is there any index is automatically created on the column on which Primary key is defined? 

Many people say no for first question as they always consider clustered index and Primary key together and primary key can only be created on column with unique and not null values. Since primary key and clustered index combination is always consider as best combination like made in heaven so many people thought clustered index can only be created on the column on which primary key is created.

But this is not correct. We can create clustered index on the columns on which primary key is not defined or in simple words clustered index can be created on the non primary key column of a table. We know that when we defined primary key, a clustered index is created on the column / columns automatically by default. But if a clustered index is already defined for the table and we created the primary key latter then clustered index is not created on the primary key column as only one clustered index can be possible for a table which is in this case, already defined. So Primary key and clustered index are both different objects in SQL. Uniqueness is must in case of Primary key but it is not a must condition in case of Clustered Index.

For example,
Suppose we have a table called tbl_clustered whose structure is given below:-

create table tbl_clustered (Id int identity(1,1), Empname nvarchar(200 ))

Below is the data inserted  query to insert data in the table tbl_clustered  

Insert into tbl_clustered (Empname )Values ('Kumar'), ('Gupta'), ('Arora'),('Gupta'), ('Gupta') 

Now let see the data inserted into the table using the below query

select * from tbl_clustered 

Result:-

Id Empname
1 Kumar
2 Gupta
3 Arora
4 Gupta
5 Gupta

So till now, we have created a table and inserted some data in it. ID column is the identity column and Empname  is varchar type column and we inserted some duplicate values in the Empname column.

Some people thought when we create an Identity column, primary key is automatically created on it. This is wrong. We usually create primary key on Identity column as it contains unique and not null value but primary key is not created on it by default unless we create it manually.

Let see the table structure to make sure that no primary  key or clustered index is created on the table yet.

To see the table structure, we use the following command:-

sp_help tbl_clustered


Now lets create a clustered index on the table tbl_clustered.

create clustered index inx_clux_test on tbl_clustered( Empname )

Above command will create the clustered index successfully irrespective of the fact that Empname column contains the duplicate values. Let again check the table structure again

sp_help tbl_clustered


If you see the above picture, you can find the details of clustered index in the last row.

Now let create a Primary key on the table to show that if a clustered index is already created on a table and then we try to create a primary key then the primary key is created but without any clustered index.

let create the primary key using the below script

Alter table tbl_clustered add primary key ( id)

Let see the table details again to check is any clustered index is created on the primary key column when we defined the primary key in the table.



If we see the above picture, we got to know, if we create a primary key on table on which a clustered index is already defined, then a non clustered index is created automatically on the primary key column. So the table has two indexes
  1. Clustered Index defined manually by us on the table intially
  2. Non clustered index created automatically with the creation of primary key on the column on which Primary key is defined.

Summary:-Through this article, I tried to show that primary key and clustered index are the different objects. We can create a clustered index on the column which contains duplicate values but primary key can only be created on the column which contains unique and not null values. Also, if a clustered index is already defined on a table and we try to create a primary on the table then a non-clustered index is created on the column on which the primary key is defined(instead of clustered index). But we should try to design the database so that clustered index is created on the primary key as we mostly use primary key in Joins conditions. Also primary key is referred by the foreign keys. So clustered index on the primary key helps in increasing the database performance.

You can refers the following articles for better understanding of the concepts used in this article


For Primary Keys

For Indexes:-

Keep learning and don't forget to gives feedback on the article. You can also send feedback to me on my mailid  askvivekjohari@gmail.com

Monday, August 3, 2015

Question of the Month (July)

Suppose we have a table emp_plan  which contains 4 columns "Empid" (employeeid), Planid (Projectid), Startdate(Allocation Start date) and Enddate (Allocation Enddate). Its structure is given below:-


Create table emp_plan (empid nvarchar(200),planid nvarchar(20),startdate datetime, enddate datetime)

Also below is the script to enter sample data into the table:-

insert into emp_plan(empid,planid,startdate,enddate)
select '001','planA','2015-05-15','2015-05-30'
union all
select '001','planA','2015-05-31','2015-06-14'
union all
select '001','planA','2015-06-15','2015-06-30'
union all
select '001','planA','2015-07-10','2015-07-20'
union all
select '001','planA','2015-07-21','2015-07-30'
union all
select '001','planB','2015-06-14','2015-06-30'
union all
select '001','planB','2015-07-10','2015-07-20'
union all
select '001','planB','2015-07-21','2015-07-30'
union all
select '002','planA','2015-06-14','2015-06-30'
union all
select '002','planB','2015-07-10','2015-07-20'
union all
select '002','planB','2015-07-21','2015-07-30'

So the data contain in the table is given blow:-



Now if a employee has the same project(planid) with no gap in the next allocation segment( allocation segment means from allocation startdate to allocation enddate), then we need to join the continuous allocation segment. For example if you see the data in the table, in the first 3 rows, employee having empid 001 is allocated to the Same project (PlanA) and all allocation segments are continuous (from 2015-05-15 to 2015-06-30)  as shown below:-

2015-05-15 to 2015-05-30  (1st row)
2015-05-31 to 2015-06-14  (2nd row)
2015-06-15 to 2015-06-30  (3rd row)

But the 4th row contains allocation segment which is not not continuous with the above 3 allocation segments as there is a gap between the 3rd row enddate and 4th row startdate and again 4th row is in continuation with 5th row with same employeeid 001 and project id (Plan1) as shown below:-

2015-07-10  to 2015-07-20 (4th Row)

2015-07-21 to 2015-07-30 (5th Row)

So expect result set should be


Empid     Planid               Startdate                           Enddate
001          planA     2015-05-15 00:00:00.000     2015-06-30 00:00:00.000 

(1st to 3rd Row)
001          planA     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000        (4th to 5th Row)

So according to the above rule, expected result from the sample data inserted into the table (as shown in the above picture), should be same as given below:-

Expected Result

Empid     Planid               Startdate                           Enddate
001          planA     2015-05-15 00:00:00.000     2015-06-30 00:00:00.000
001          planA     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000
001          planB     2015-06-14 00:00:00.000     2015-06-30 00:00:00.000
001          planB     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000
002          planA     2015-06-14 00:00:00.000     2015-06-30 00:00:00.000
002          planB     2015-07-10 00:00:00.000     2015-07-30 00:00:00.000

So what should be the query/stored procedure to get the above result?