29.12.08

SQL Server error

Dear Viewers,

  Frequently, most of our sql server developers face this issue,

"The Product level is insufficient for Component in sql server"

Even, I too faced this kind of issue in my development work. I have got the sole reason for this error in sql server.

Lets analyse,

  The problem is due to the installables. Most of the companies wont take up the entire version of SQL Server. Considering, mostly we will get this error in SSIS/SSRS and other sql server supporting services.

   Companies will try to buy the installables which hold only SQL Server client components and they dont have the entire package like SSIS, SSRS.

  If you face this kind of problem, Goto - >Programs ->Microsoft SQLServer2005 ->Configuration tools - >SQL Server Configuration Manager.

  Just check for those services running in your machine.




Else, goto run->services.msc and check for the corresponding services in your machine. If the services is available then, try to restart the services. It may solve your problem.


Related article is available at,
http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions_27.html

Thanks and Regards,
Venkatesan Prabu .J

1.12.08

DBReindex in sql server

DBReindex:
DBReindex is used to reindex the entire sql server database. It will flush out the indexes and create new index in the leaf level. Thats why its considered as very effective in improving the performance of our database. The syntax is,

dbreindex(tablename,indexname, fillfactor)
In the older version of sql server, there is an another option called "sorted_data_reorg" which is mainly used to enhance the speed of index creation. But, its dropped in the future versions of sql server.
Ex:
dbcc dbreindex(tblname,'',0,sorted_data_reorg)
After executing the above command, some dba's used to execute sp_recompile option.
sp_recompile tblname
But, practically speaking we dont want this statement after dbreindex command. Because, if the index or statistics got affected. Obviously sp_recompile option will get fired.

Thanks and Regards,
Venkatesan Prabu .J

Rows to columns in sql server

Its my third article on rows to columns coversion in sql server 2005. Now, its pretty different i have given a very different way to make this possible. Lot of our developers were finding to acheive this functionality in sql server 2000. Below is a simple approach to achieve it.

declare @Attendees table
( FunctionID integer,
DetailID integer,
ParticipantID integer,
StatusID integer
)
insert into @Attendees
select 1, 1, 123, 6 union all
select 1, 2, 124, 6 union all
select 1, 2, 125, 7
select * from @Attendees

declare @Status table
( StatusId integer,
Description varchar(10)
)
insert into @status
select 6, 'Invited' union all
select 7, 'Accepted'
select * from @status


select a.FunctionID,a.DetailID,--b.Description
case
when b.Description='Invited' then 1 else 0
end as invited,
case
when b.Description='Accepted' then 1 else 0
end as Accepted
from @Attendees a inner join
@status b on a.statusID=b.statusID


select a.FunctionID,a.DetailID,--b.Description
sum(case
when b.Description='Invited' then 1 else 0
end) as invited,
sum(case
when b.Description='Accepted' then 1 else 0
end) as Accepted
from @Attendees a inner join
@status b on a.statusID=b.statusID
group by a.FunctionID,a.detailid



Thanks and Regards,
Venkatesan Prabu .J

Rows to columns in sql server

As usual, a frequent article in sql server.

 I am trying to covert the rows to columns in sql server.

Problem description: Am having a table with list of participants and number of programs.
I need to give a binary value based on the participants for a specific program. Lets see how to arrive this.

Declared and inserting data into the table @ Attendees 

declare @Attendees table
( FunctionID integer,
DetailID integer,
ParticipantID integer,
StatusID integer
)
insert into @Attendees
select 1, 1, 123, 6 union all
select 1, 2, 124, 6 union all
select 1, 2, 125, 7
select * from @Attendees

Declared and inserting data into the table @ Status

declare @Status table
( StatusId integer,
Description varchar(10)
)
insert into @status
select 6, 'Invited' union all
select 7, 'Accepted'
select * from @status

Getting the output with duplicates.

select a.FunctionID,a.DetailID,--b.Description
case
when b.Description='Invited' then 1 else 0
end as invited,
case
when b.Description='Accepted' then 1 else 0
end as Accepted
from @Attendees a inner join
@status b on a.statusID=b.statusID

Getting the exact required output.

select a.FunctionID,a.DetailID,--b.Description
sum(case
when b.Description='Invited' then 1 else 0
end) as invited,
sum(case
when b.Description='Accepted' then 1 else 0
end) as Accepted
from @Attendees a inner join
@status b on a.statusID=b.statusID
group by a.FunctionID,a.detailid




Thanks and Regards,
Venkatesan Prabu .J

Use database inside stored procedure

I am thinking about an important funcitonality or property in sql server.
Is it possible to give the database name dynamically in sql server?
OOps, i think we can achieve this using dynamic sql.
I have made a try on this. Lets see,
use master
GO
declare @sql nvarchar(max)
select @sql = 'use'
select @sql = @sql + ' ' +
'Northwind'
select * from dbo.Employees -- Northwind table
exec sp_executesql @sql
--------------------------------------------------------
Its not working. I have tried to analyse this problem, its because once the query gets executed its coming out of that particular scope and enter into the public scope.

The work around for this problem is, we need to

use tempdb
go
declare @sql nvarchar(1000)
declare @dbname varchar(40)
select @dbname = 'Northwind'
select db_name() --tempdb
select @sql = 'use'
select @sql = @sql + ' '+@dbname +' select * from dbo.Employees
select db_name()'
exec sp_executesql @sql --northwind
select db_name() --tempdb

Thanks and Regards,
Venkatesan Prabu .J

22.10.08

Len Vs Datalength in sql server

I have seen a very common question like, Whats the difference between len and datalength function in SQL Server? (Important Interview question too... )

I have created a table name 'VENKATTABLE'

CREATE TABLE VENKATTABLE(NAME VARCHAR(100))
INSERT INTO VENKATTABLE VALUES('VENKAT ')
SELECT * FROM VENKATTABLE

Now, I've tried to use these statements,

SELECT LEN(NAME) FROM VENKATTABLE -- OUTPUT =>6
SELECT DATALENGTH(NAME) FROM VENKATTABLE -- OUTPUT =>9

The difference is length will trim the data and it wont consider the spaces available. Whereas, Datalength will fetch the exact data available and it will consider the spaces too.

In this case, I've tried with Ltrim and Rtrim to achieve the same functionality of len function.

SELECT DATALENGTH(LTRIM(RTRIM(NAME))) FROM VENKATTABLE -- OUTPUT =>6

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Tamil Joke - If we got chance to speak truth in interview

வேலைக்கான நேர்காணலில் ... உண்மையைச் சொல்ல முடிந்தால் ....

நீங்கள் ஏன் எங்கள் நிறுவனத்தில் பணிபுரிய விரும்புகிறீர்கள் ..?

எந்தப் புண்ணாக்குக் கம்பெனியிலாவது வேலை செஞ்சாதான் பொழப்ப ஓட்ட முடியும்.. எந்த நாய் வேலை குடுக்குதோ அங்க வேலை செய்ய வேண்டியதுதான் .. அதைத் தவிர உன் கம்பேனி மேல பெருசா ஒண்ணும் மதிப்பு மரியாதையெல்லாம் இல்லே ..!

உங்களுக்கு ஏன் இந்த வேலையைத் தரவேண்டும் ..?
உன் கம்பெனி வேலையை யாராவது ஒருத்தன் செஞ்சுதானே ஆகணும்... என்கிட்டதான் கொடுத்துப் பாரேன்.

உங்களுடைய தனித்திறமை என்ன ..?

வேலைக்கு சேர்ந்ததும் , கடலை போட வழியிருக்கான்னு பார்ப்பேன்.. இங்கேருந்து என்னென்ன சுடலாம்ன்னு நோட்டம் உடுவேன் .. உன் கம்பெனியில் வேலைக்கு சேர்ந்ததைச் சொல்லி ஊர் பூரா கடன் வாங்குவேன்.. அப்புறம் வேற கம்பெனிக்கு தாவ முயற்சி பண்ணுவேன் .. இதைத் தவிர உன் கம்பெனிக்கு சேவை செஞ்சு முன்னுக்குக் கொண்டு வரணும்ங்கிற மூட நம்பிக்கையெல்லாம் கிடையாது .

உங்கள் மிகப்பெரிய பலம்..?
இதைவிட பெரிய சம்பளத்தில் வேலை கிடைச்சா அப்படியே உட்டுட்டு அங்கே ஓடிருவேன் .. மனசாட்சி, நன்றியுணர்வு இதுக்கெல்லாம் முட்டாள்தனமா ,, இடமே கொடுக்காம கடுமையா நடந்துக்குவேன் ..

பலவீனம்..?

ஹி.. ஹி... பெண்கள்..!

இதற்கு முன் வேலை பார்த்த நிறுவனத்தில் உங்கள் சாதனைகள் என்ன..?அப்படி ஏதும் இருந்தா நான் ஏன் வேலை தேடி இங்கே வருகிறேன் .. அந்த சாதனைகளை பெருசா பில்டப் பண்ணி அங்கேயே வேணும்ங்கிற அளவுக்கு சம்பளத்தைக் கறந்துருக்க மாட்டேனா ..?

நீங்கள் சந்தித்த மிகப்பெரும் சவால் என்ன ..?

அதை எப்படி வெற்றி கொண்டீர்கள் ..?ஆண்டவன் அருள்தான் காரணம்.. இதுவரைக்கும் எந்த நிர்வாகியும் மூணாவது மாசச் சம்பளத்தைக் கொடுக்கறதுக்கு முன்னே நான் ஒரு வெத்துவேட்டுன்னு கண்டுபிடிச்சதே இல்லே .

ஏன் இதற்கு முன் பார்த்த வேலையை விட்டு விட்டீர்கள் ..?
நீங்கள் ஏன் இந்த வேலைக்கு நேர்காணல் நடத்த வேண்டிய அவசியம் வந்ததோ.. அதே காரணத்துக்காகத்தான்..!

இந்த பதவியில் நீங்கள் எதிர்பார்க்கும் அம்சங்கள் என்ன..?

நல்ல சம்பளம் , 0 % வேலை, பக்கத்து சீட்டுல கண்ணுக்கு குளிர்ச்சியா ஒரு பெண் , நாட்டாமை பண்ண எனக்குக் கீழே ஒரு கூட்டம். அது போதும் .
Endrum
Venkat,

17.10.08

Social article - Software Companies firing employees

Often, we are hearing a frequent new about software down due to financial break down in US.
I got some information on recent happenings in software field.

HP Layoffs

HP 24000 Lay-offs - After acquiring EDS, HP is planning to reduce their work force and they planned to take out 24000+ employees from their company. It includes both higher level and lower level people.

Satyam 4500 Job cuts :
Due to lehman brothers closure, Satyam computers has just started giving pink slips to its employees, could potentially downsize its workforce by a whopping 4,500 employees. Recently, satyam head have informed their employees not to be panic and advising his people to work hard.

Wipro technologies started giving pink slips:
Wipro technologies has put about 4-5% of its workforce, about 2,400-3,000 employees, under the scanner for non-performance.
This is the cycle and everyone has to sustain this problem. If you see the past 10 years record of IT industry it got changed in 2-3 times majorly in 2003. Satyam and Wipro are service based companies if their client is going to the depth of loss then why they bare the loss it is obvious that they will fired the employees.
The recent IT Lay offs is mainly due US Market slowdowns. And as everyone of us knows that the market is never stable. Who knows that though it is down now, it may grow at a remarkable height.
This is once again the firing season for IT. Satyam Computers has just started giving pink slips to its employees and around 4,500 of its employees are scheduled to get the boot.

wipro firing
Wipro Technologies has put about 4-5% of its workforce or about 2,400-3,000 employees, under the scanner for non-performance.
tcs firing
In February, TCS had given pink slips to 500 non-performers.

IBM firing
IBM India also asked about 700 employees to leave, citing non-performance.

patani firing
In July, Patni computer systems fired 400 employees.
If the going is tough for these bigwigs, what about those in the lower rung? Mid tier companies are also doling out pink slips in all generosity! Hexaware has reportedly booted out 500 people and this does not include the 130 employees to be laid off due to the shut down of its office in Gurgaon. Ramco Systems laid off 142 employees.
Not true news on infy firing.


Reference:

http://www.alleyinsider.com/2008/9/hp-firing-24-600-employees-after-buying-eds-hpq-

http://hubpages.com/hub/Indian-IT-Companies-Infy-Wipro-Satyam-Cognizant-Accenture-IBM-Firing-News

http://topinews.com/story/title/wipro-firing-infyosys-firing-cts-firing--hp-firing--news

http://www.indyapulse.com/Blogs/View.asp?ArticleId=688&CatId=111

Kindly share your comments regarding this post.
Regards,
Venkatesan Prabu .J

11.10.08

Social article - Increase in price - In india

Increase in rates:
  • Everywhere rates were increasing due to some of our people. They are ready to pay huge money for lemon juice too. I have seen some hotels were selling lemon juice at Rs. 20. Are we think about the money which we are spending is worth. (50 paise to 1 Rs Lemon + 1 Rs sugar +1 Rs water +1 Rs Electricity). Why dont we prepare a lemon juice in our home. Are we become too lethargic.
  • Giving huge tips to servers in the hotel. They are expecting the same from the normal people and they are not ready to serve the normal people. Dont you think, we are taking our society to the wrong side. We will raise flags against bribes. But, every one of us were doing the same indirectly.
  • For comfortness, we used to book and travel in A/C buses. Monday morning, I have to go to job and I should not feel tired. Sunday nights, all ticket rates got increased and normal people cant travel in ordinary deluxe bus too. We are ready to buy tickets in black and indirectly, most of us spoiled our society. You can ask me, Why venkat is pointing only software engineers. Its because, we are now becoming educated fools... for getting comfortness. Nowadays, Black tickets for movies were increasing. Even theatre people stopped to give tickets in counter. Inturn, they are arranging poeple and selling their tickets for good rates.
  • Our people wish to see films only in Mayajaal, INOX and other big theatres. Atleast, we should think about wasting our percious money. Some of us will go rarely to watch good films. Some of our people will prefer only those hi-fi theatres. Because they need comfortness to sit for 3 hours. Please please think whether we are spending our money for worthful things.

Planning to buy a house in India :-( Check this URL,

http://venkattechnicalblog.blogspot.com/2008/10/social-article-buy-property-in-india.html


How we are going to stop this? Provide me more inputs on this topics?

Venkatesan Prabu .J

Social Article- Buy a Property in India

Hi Friends,

Its pretty new topic / its not a technical topic and no way related to my blog topics. But, to create an awareness among our people (particularly software engineers). I scheduled my time to analyse the facts and express the same in my own words.


People who are involved / affected:

  • Mostly software engineers (70%) , people in very good position (Government job)(10%), business people (10%) and other people (10%).
  • One day, I wish to buy some flowers for my wife and went to a lady(she is selling flowers in the road). I asked flowers for Rs.5. She told about the increase in flower rates and I will be getting very less flowers for Rs.5. I replied her back. "No, i need it for Rs. 5. Its more than enough for me." Immediately, she enquired about my profession, and I told, "I am a software engineer" Immediately, she replied me back "Boss, You are earning lots and lots of money. But, you are not ready to buy flowers" She gave some flowers and forced me to give Rs.10. Its not a huge amount for me, but once, if someone comes to know about our profession, i wondered to see the rates reaching peaks. Are we cursed like this?
  • No one knows the pain and hard work behind our earnings. To Complete an engineering couse is not an easy job. Clearing all the subjects (arrears too)..and after getting a degree with first class distinction. I can remember those days (Carrying resume and requesting others for job - we need to get employee referral - First round , Second round, Third round of interviews). Many people will loose their opportunity in the final round. Its hard time for us to get a good job in software industry.
  • After getting job, we need to survive in this ever changing IT field, daily we need to update our knowledge to survive in this world, need to face lot of junk politics, lots and lots of restriction in the name of cost cutting, no job surety, no proper healthy food (as we are modernised with fast foods and hifi junk foods), no proper timeline to work, cant adhere to particular technology, no proper growth, no proper recognition. Atlast, we become more flexible as a non technology cum non human being (Entire brain loaded with junk politics).
  • No one thinks that, we (software engineers) dont have good money in hand to buy a single toilet room too. Its because with the help of banks we are buying houses. Do we have time to think, if my software job is gone . What I will do? How to repay the loans? We may think that, am having house right. I will sell the house and repay my loans. Then, Whats the reason for buying a house and stretching ourselves under pressure and huge headaches. What happen, If the rates went down. Now, In US market we can get a house for Rs.82,000. But, no one is there to buy that house. Do you think, it wont happen in india?

Increase in rents:

  • Our software engineers will be seeing huge money in the initial stage - Fresher (an average of Rs.20,000 per month). Immediately, they will target to buy a good bike and ready to shift their residence near to their office. Mostly, they(Bachelor) will stay in sharing basis and they wont think about the rent (10,000/5). Obviously Rs.2000 is not a huge rent for them. This makes house owners to think more. Nowadays, house owners were targeting inviting software engineers to their houses.
  • Once after marriage, software engineers will come to know about paying huge rents from their pockets. It becomes a huge headache for them after marriage. So, they are trying to marry a working women to survive. But, they dont find time to rethink about the reason behind. Its because of them, the rents got increased.

Reason for buying a house:

  • Increase in rents.
  • To avoid conflicts and pressure from the house owners to maintaining the
  • Sense of satisfaction - We used to think that, if we have our own house. Then, we will get a satisfaction like, I've got settled in my life.
  • Greediness, to get more income from the houses.
  • To avoid lot of conflicts from neighbours.
  • Very good investment for rich people.
  • NRI were trying to invest their dollars in India.

Reason for reducing land values in india :

  • Due to increase in interest rates. If we calculate the interest rates, How much am getting money and How much am repaying? You will surely get heart attack. Suppose am getting 20 Lakhs as loan amount from a bank. I need to pay around 50 to 60 Lakhs (Includes interest). Is it really worth? Instead, you can earnt / save / invest money and you can buy the same house with out any debts right . Why dont we think in that angle? Instead, unnecessarily we are paying more money to builders and banks.
  • Employees fired from top software companies. Due to cost cutting, downsizing company size, merging of companies. Employees who earned lakhs become jobless on the next day.
  • Due to increase in land rates, software engineers cant afford huge amount and they stopped buying houses.
  • A small awareness started among our people about this land value increase.
  • Everyone around us understand our mentality and playing based on this. We are ready to buy a house of worth 30 to 40 lakhs. Targeting this budget, builders started their plan of building houses 1200 SQ Ft to 2400 SQ Ft. Even, it may worth 25 lakhs, it will become 30 lakhs for software engineers. If we ask the reason, we will get a reply "Boss, You are earning too much money" Are we earning black money? or earning money with out any hard work"

My suggestions in buying a property in 2008:

  • Please dont buy property now, because due to financial break down and problems in software industry. The land value will surely reduce further. Stay for a while, rethink twice thrice before buying a property.
  • Next year, the rates will decrease further....... Lot of changes will happen soon.

Some URLs for your reference :

Chennai property bubble bursts, prices dip : http://www.ndtv.com/convergence/ndtv/story.aspx?id=NEWEN20080067248

Chennai properties will be reducing soon:

http://www.articlesbase.com/real-estate-articles/soaring-chennai-property-prices-boon-or-curse-493732.html

Buy a flat and get BMW free: (People should think about this offer. They will dream about the gift offered and forgot to think about the property price. The business people will increase the property rates like (Actual rate+BMW car rate) and they will sell their property easily.

http://indiarealestatemonitor.com/property-news/buy-flat-get-bmw-free/

Property market gone down due to US breakdown:

http://indiarealestatemonitor.com/property-news/indian-realty-wakes-up-to-the-us-meltdown/

Land value is now Rs. 15,000 per square feet against Rs. 40,000 for a similar property in 2007

Analysts predicts that, the land value will go further down to Rs.10,000 in next 6 months.

http://indiarealestatemonitor.com/property-news/affordable-housing-is-the-new-choice/

Realtors were slowly analysing the market and they are not ready to step ahead:

http://indiarealestatemonitor.com/property-news/realtors-advance-tax-payment-falls-down/

http://indiarealestatemonitor.com/property-news/realty-firms-consider-joint-development-for-construction-projects/

Feel free to post your comments and kindly share this URL to your friends and create an awareness among our people.

My article on increase in rates in India:

http://venkattechnicalblog.blogspot.com/2008/10/social-article-increase-in-price-in.html

Venkatesan Prabu .J

10.10.08

Update table with inner join with other table in sql server

I have see lots of people were querying about inner join used on update statement. Based on two tables, I need to do an update statement. Is it possible? OOPS, its possible. Let see the same through step by step.
I have created a table named employee,
create table employee(id int, name varchar(100))

Inserted some records in the table.
insert into employee values(1,'Venkat')
insert into employee values(2,'Suba')
insert into employee values(3,'Lakshmi')
insert into employee values(4,'Arun')
insert into employee values(5,'Karthi')
select * from employee

OOPs forgot to alter table employee
alter column id int not null
alter table employee add constraint prime primary key (id)
create table employeedetails(empid int, city varchar(200),designation varchar(10))
alter table employeedetails
alter column empid int not null
Am adding foreign key between these two tables,
alter table employeedetails add constraint foreignkeyforeign key (empid) references employee(id)

insert into employeedetails values(1,'Chennai','Engineer')
insert into employeedetails values(2,'Chennai','Engineer')
insert into employeedetails values(3,'Bangalore','Engineer')
insert into employeedetails values(4,'Newyork','Engineer')
select * from employeedetails

Lets see how to update the table using inner join with other table,
update a
set city ='Sydney' from employeedetails a inner join employee b on a.empid=b.id where b.[name]='Venkat'

Thanks and Regards,
Venkatesan Prabu .J

8.10.08

SQL Script to list down the strings with delimiter

I have see lot of queries asking for down the strings from comma seperated string or semicolon seperated string. Below is a superb function to acheive the same.


CREATE FUNCTION dbo.fnSplit(
@INPUTSTRING VARCHAR(8000) -- Actual string with comma delimeted

, @Delimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@Delimiter,@INPUTSTRING,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@INPUTSTRING,1,CHARINDEX(@Delimiter,@INPUTSTRING,0)-1))),
@INPUTSTRING=RTRIM(LTRIM(SUBSTRING(@INPUTSTRING,CHARINDEX(@Delimiter,@INPUTSTRING,0)+LEN(@Delimiter),LEN(@INPUTSTRING))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@INPUTSTRING) > 0
INSERT INTO @List SELECT @INPUTSTRING -- Put the last item in
RETURN
END
GO

The above code with create a function which will split the string based on its input parameter.

Lets execute the below query,

select * from dbo.fnSplit('1,22,333,444,,5555,666', ',')

The above query will fetch the output,

item

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

1

22

333

444

5555

666

Happy Learning!!!

Regards,

Venkatesan Prabu .J

28.9.08

T-SQL Interview Questions

Before reading this article. .........................
This post is a continuation of my previous post

7. Fetch the employee based on EmpQualification
select a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID



To avoid duplicates we need to used the distinct keyword
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID
INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(5,'Santhi','Jayakantham','Dharmapuri',1000,'PM')


To list down all the rows from the left table with matching columns in the right table. This can be achieved using Left outer join.
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
left outer join EmployeeDetails b on a.EmpID=b.EmpID

Appropriate right tables column will be appended with Null value.
What about getting the all the right table row with corresponding matching column from the left table.

select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
right outer join EmployeeDetails b on a.EmpID=b.EmpID

Getting the matching data from two tables. Suppose I need to list the employees who is having qualification = 'BE'
select distinct a.EmpID,a.EmpFirstName,a.Salary,a.Designation, b.EmpQualification from Employee a
inner join EmployeeDetails b on a.EmpID=b.EmpID where b.EmpQualification='BE'

8. List down all the managers and their employee ID
select b.ManagerID,a.EmpFirstName as ManagerName from employee a
inner join employee b on a.EmpID=b.ManagerID

List down the employees who is having managers,
select b.EmpID,b.EmpFirstName,b.ManagerID,a.EmpFirstName from employee a
inner join employee b on a.EmpID=b.ManagerID

List down all the employees with managers details(If exists)
select b.EmpID,b.EmpFirstName,b.ManagerID,a.EmpFirstName from employee a
right outer join employee b on a.EmpID=b.ManagerID

Happy Learning!!!
Regards,
Venkatesan Prabu .J

27.9.08

SQL server interview questions

Let see some basic T-SQL. On reading this article, you can straight forward attend an interview or you can take a SQL Server task.

CREATE TABLE EMPLOYEE(EmpID INT PRIMARY KEY , EmpFirstName VARCHAR(100),EmpLastName VARCHAR(100), City VARCHAR(10),ManagerID INT, Salary int ,Designation varchar(10))

CREATE TABLE EMPLOYEEDETAILS(EmpID INT , EmpQualification VARCHAR(100),EmpExtn INT)

1. Adding Constraint between two tables, to update a foreign key relation ship. You need to alter the child table.

ALTER TABLE EMPLOYEEDETAILS
ADD CONSTRAINT emp_for FOREIGN KEY (EmpID)
REFERENCES EMPLOYEE(EmpID)

2. Trying to insert into the child table.
INSERT INTO EMPLOYEEDETAILS VALUES(1,'BE',200)

You will get an error "The INSERT statement conflicted with the FOREIGN KEY constraint"
Its because, you need to populate data in the master table and corresponding column should be populated in the child table.

3. Trying to insert into the master table.

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(1,'Venkat','Prabu','Dharmapuri',1000,'PM')

Output:
(1 row(s) affected)

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary) VALUES(1,'Venkat','Prabu','Dharmapuri',1000)

On executing the above statement, we will get
Violation of PRIMARY KEY constraint 'PK__EMPLOYEE__3C69FB99'. Cannot insert duplicate key in object 'dbo.EMPLOYEE'.
Its because we are trying to insert duplicate key into the primary key column.
Let make a try with other insert statements,

INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,ManagerID,Designation) VALUES(2,'Arun','Prabu','Dharmapuri',500,1,'Dev')
INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,Designation) VALUES(3,'Karthi','Prabu','Salem',2000,'Lead')
INSERT INTO EMPLOYEE(EmpID, EmpFirstName,EmpLastName,City,Salary,ManagerID,Designation) VALUES(4,'Lakshmi','Prabu','Chennai',100,3,'Dev')

select * from Employee
Lets make a try to insert a row in the child table.
INSERT INTO EMPLOYEEDETAILS VALUES(1,'BE',200)
INSERT INTO EMPLOYEEDETAILS VALUES(2,'MBA',100)
INSERT INTO EMPLOYEEDETAILS VALUES(3,'BA',10)
INSERT INTO EMPLOYEEDETAILS VALUES(4,'BE',50)
INSERT INTO EMPLOYEEDETAILS VALUES(4,'BE',500)
--delete from Employeedetails where EmpID=1
select * from Employeedetails

4. Employee earning maxiumum salary

select max(salary) from EMPLOYEE

I need the max salary on each designation
select max(salary),designation from Employee Group by designation

I need max salary based on employee city
select max(salary),city from Employee Group by city

I need to check the max salary of the employees who is having salary >=1000 based on city.
select max(salary),city from Employee where Salary >=1000
Group by city

Total salary given to employees by the company
select sum(salary) from Employee

Total salary given to employees grouped by designation
select sum(salary),designation from Employee Group by designation

Total employees available in the company
select count(*) from employee

5. Employee Search

I need an employee with First name as "Venkat"
select EmpID,EmpFirstName from employee where EmpFirstName='Venkat'

I need an employee with Firstname start with "V"
select EmpID,EmpFirstName from employee where EmpFirstName like 'V%'



I need an employee with Firstname having second letter as "e"
select EmpID,EmpFirstName from employee where EmpFirstName like '_e%'

I need an employee with firstname ended with the letter "t"
select EmpID,EmpFirstName from employee where EmpFirstName like '%t'

I need an employee whose city is "Chennai"
select EmpID,EmpFirstName from employee where city ='Chennai'

6. List down the employees having more than one extension number
select empid from employeedetails group by empid having count( EmpExtn)>1
Happy Learning!!
Regards,
Venkatesan Prabu .J

SQL Interview Questions

Local and Global Cursor :
While declaring the cursors, we can specify a cursor as Local or Global cursor.

Local Cursor will exists for the particular object and it cannot be reference by an external object. By default, cursors are Local. If we need to make it as global and can be accessed by all the objects. We need to specify it as global.
If the cursor is declared as Global, it can be accessed by all the objects in the existing connection.

Subquery:
Subquery is an important concept in T-SQL. The outer query will provide the result based on the result given by the subquery.

Select id from table1 where id = (select max(id) from table1) -Red color is the subquery.

The outer table's output "id" depends on the subquery "id" value.
Here equal to (=) is used so that the subquery will retrieve only one id. If the subquery gives more than one value we have to use "IN" operator.
Select id from table1 where id in (select id from table1)
generally, the outer query is waiting for the subquery to finish its execution. So, it will hit the performance in a huge application with complex joins. So, we should avoid subqueries.

Correlated subquery:

These queries are little bit interesting one. The inner query depends on the outer queries input and there will be a mutal co-ordination occurs between these inner and outer query.

select name from venkat1 v

where id in (select id from venkat1 v1 where v.id=v1.id)
In the above query, the inner query depends on the outer queries output.

Happy Learning!!!

Regards,

Venkatesan Prabu .J

SQL Interview questions

Below are some of the maximum capacity for SQL Server 2005: (Frequently asked interview questions in sql server)
Table Level:
Maximum Number of tables : No restrictions (Until your harddisk size you can create tables)
Maximum bytes per row in the table : 8060 bytes
Maximum tables in a select statment - 256
Maxiumu references per table - 253
Maximum rows in the table - Unlimited (Depends on the storage)

Maximum columns per base table : 1024
Maximum columns per insert statment : 1024
Maximum columns per Select statment : 1024 * 4 = 4096
Bytes per Group by or Order by = 8000

Index:
Maximum clustered index per table - 1
Maximum Non-clustered index per table - 249
Maximum Columns in an index (composite index) - 16

Keys:
Maximum Columns in a Primary key - 16
Maximum Columns in a Foreign key - 16

Objects:
Nested Stored procedure levels- 32
Nested triggers levels- 32
Parameters per stored procedure - 2100
Parameters per User defined functions - 2100
Identifier length - 16

Database Level Details:
Number of instances for a server - 50
Files per database - 32767
Filegroups per database - 32767
Databases per instance of SQL Server - 32767

More SQL interview questions,
http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions_27.html

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions_11.html

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-question-part-2.html

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions.html

Happy learning!!!
Regards,
Venkatesan Prabu .J

SQL Server Interview Questions

RDBMS Vs DBMS:
Database management system is considered as the system to store the data for easy retieval and the data were relatively stored as a group. Whereas, Relational database management system will include a relationship between tables (using constraints). The relationship will be based on their logical relations. Considering,
There are two table "employee" which will hold employee information like, employee id, employee dob, where he is working. Another table "employee details" which may hold his personal information like blood group, identification etc..,
For these two tables, there should be a key which will form a relation ship between these two tables. Its nothing but RDBMS. (Eg: SQL Server, Oracle)

SQL Server Vs MS access:
I used to see this question acess vs sql server,
MS access is a RDBMS and can be considered as client level database. It comes along with Microsoft office package. The database will be formed based on filesystem concept, so it wont support multiuser environment. Its preferred for very small databases. It developed on Jet engine.
SQL Server is a RDBMS and works as a Server level database. It will work for a large group of Users with 24/7 availability and its preferred for high end system with huge data and huge support. Its developed on sql server engine.
Normalization:
Normalization is a very important consideration in designing the databases. Normalization includes various steps like,
1. First normal form
2. Second normal form
3. third normal form
4. Backus naur form
5. Extended Backus naur form
6. Fifth normal form
7. Sixth normal form
The above normal forms are used to avoid redundancy of data. To achieve these, we have to segregate the columns to different tables. So that, duplication should not be there, partial dependency should be avoided etc..,

Denormalization:
As the name indicates, it's opposite to normalisation. Introducing redundancy into the database is referred to as denormalization. The database becomes too complex and too many joins may occur to fetch the data. In that case, we used to prefer this denomalization concept. Narrow idea - Joining the divided or segregated tables.

MSDE vs SQL Server :
MSDE is the downgrade version of SQL Server, the same code with initial release of SQL Server was named as MSDE (Microsoft Desktop Enginer).

Extent Vs Page:
Pages are 8 KB size smallest unit to store the data. Inturn, 8 Pages will form the extent.

Delete VS Truncate:
Delete will delete row by row in the table. The syntax is,
delete from tablename
We can give where conditions for deleting the data.
Each delete will be logged in the log file.
Its a DML statement

Trucate will deallocate the reference in the pages instead of deleting the data.
Its DDL statement. The syntax is,
Truncate table tablename
We can provide truncate with tables having foreign key relationships. (Because it wont do any cascade delete on the table).
It wont log any information in the log file.
Delete or truncate the data at a stretch and wont do any row by row manipulation.

More SQL server Interview questions at,

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions_11.html

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-question-part-2.html

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions.html

Happy Learning!!!
Regards,
Venkatesan Prabu .J

22.9.08

Order by in view + sql server

I faced a small problem in using order by in views, Let me discuss the exact problem and hack to resolve this problem.


create table venkattable1(id int, val varchar(100))
insert into venkattable1(1,'Venkat')
insert into venkattable1(2,'Venkat')
insert into venkattable1(3,'Venkat')

create view venkatview
as
select * from venkattable1 order by id

I got the below error,
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

It shows that, we can't use order by clause on the view, I've tried with Top clause in the above statement,


create view venkatview
as
select top 10 * from venkattable1 order by id

It's working fine. Please check the bleow URL for more details related to this problem.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3908414&SiteID=1

17.9.08

Problem in loading excel data to SQL Server

While uploading the data from excel sometimes, data wont get populated into sql. Even, we have followed proper steps to upload the data, the data wont get populated. Why??

Reason:
Its because, sometimes the data in the column may be character or Alpha numeric and SSIS will design its package by considering the column as Integer. In this case, if there is any varchar value,

I have explained excels and SSIS basic characteristics in my another post,

http://venkattechnicalblog.blogspot.com/2008/09/excel-column-is-greater-than-255-in.html

The values wont get inserted into the table. In that case, There are two options to resolve this problem,

1. Option1 : Its tricky one, just copy the varchar related row to the top (may be as 1 row).Now try to create your package using import/export wizard.

2. Option 2: Append a Single quotes(') to the front of all the data and after migration, update the column using an update query like,

update tablename set column1=substring(column1,2,leng(column1))

Happy Learning!!!
Regards,
Venkatesan Prabu .J

excel column is greater than 255 in ssis

We used to face some problems like, while migrating data from excel to sql server error occurs due to lengthy data.

SSIS Excel Data Source: Error with output column “Comments” (5691) on output “Excel Source Output” (5596). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”

Reason for this error:
1. The error is due to the lengthy data in the excel.
2. SSIS has an inbuilt logic to scan the data in the spread sheet. It will scan the first 8 rows and based on that it will written a logic to build the table's logic for the package. If your lengthy data in not in the 8 row then your ssis wont respond it.

Considering am having a column named "Name"
First 8 rows is of length <255
9th row is of length > 255.
While executing the package you will get the above error, its because your input value will be truncated and SSIS wont allow for that. In this case,
Two types of logics can be followed,

Option1 : Its tricky one, just copy the 9th row to the top (may be as 1 row).Now try to create your package using import/export wizard. Your ssis will create column with width nvarchar(Max) which will accept upto 2 GB.

Option2: Change the Preparation SQL task query, change the data type as varchar(Max) and modify the excel source ->advanced editor and change the output columns type and length and external column type and length.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Excel to SQL Server using SSIS

Sometimes, we need to need to fetch the data from excel spread sheet to sql server database. It can be easily achieved using import/export wizard or we can achieve by using SSIS tasks.
Let see how to utilise both options to achieve this great task in a simple manner. Open a new SSIS project in Business Intelligence studio.
Goto Project ->SSIS Import and Export Wizard

Clicking the option, we will get the Import/Export wizard. Browse your excel file and select your file.

Now, clicking next you will get the destination. you can specify your server and database name.

Clicking next, you will get the next screen.

You will be given two options,
either you can copy the entire table or you can write your customized queries to fetch the data from excel sheet. On clicking next you can view the spread sheets available. Here you need to select the spread sheet, from which you need to fetch the data.

On clicking "Preview" button you can view the spread sheet data. Click next button. You will get a successful message indicating your package is created. Now you need to execute the package.

You can view the above tasks in your monitor. Preparation SQL task will create a table and Data flow task is used to transfer the data from Excel source to SQL Server Destination.


Right click the package and execute the tasks.

Happy Learning!!!

Regards,

Venkatesan Prabu .J

Convert Varchar to Datetime in sql server

Am having date, month and year as three columns and I need to append those data and convert into a date time. Let see how to achieve the same,
First,
We need to append those columns using Convert function in SQL Server.

I have tried to execute the below query,
select convert(datetime,
convert(varchar(10),convert(varchar(10),[month])+'/'+convert(varchar(10),[day])+'/'+convert(varchar(10),[year])))
from dbo.VenkatTable

I got this error,
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Reason: Its because of the date format. I am trying to insert the date value into the month but, it wont accept beyond 12. Am i right :-)

I have changed the query. Now, its working fine.

select convert(datetime,convert(varchar(10),convert(varchar(10),[day])+'/'+convert(varchar(10),[month])+'/'+convert(varchar(10),[year])))from dbo.VenkatTable
Happy Learning!!!
Regards,

Venkatesan Prabu .J

15.9.08

SQL Server error

While trying to write my data in to an external file (Like logging the sql output into an external text file). I found this error,

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
Reason:
Its because, by default sql server will disable xp_cmdshell property and we need to enable it.
Goto Surface Area Configuration manager and enableit.

Click apply, now you can achieve your tasks.

You can achieve it using T-SQL too.

Clearly you'll need sa privileges on your SQL instance to run this:
sp_configure 'show advanced options',

RECONFIGURE

GO

sp_configure 'xp_cmdshell',

GO

RECONFIGURE

GO

Regards,

Venkatesan Prabu .J

List out all tables and row count in sql server

How to list down all the tables and its row count in SQL Server:

SQL Server will hold all the tables and its row count in a special table named sysindexes.
Below is the query to fetch all the information,

select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows from sysindexes a inner join sysobjects b on a.id = b.id

To list out the information for a particular database, you can use the below query.

select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows
from sysindexes a inner join sysobjects b on a.id = b.id
inner join INFORMATION_SCHEMA.TABLES c on c.[TABLE_NAME]=convert(varchar(30),object_name(a.id))
where c.Table_catalog='Database Name'

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Ltrim / Rtrim in SQL Server

Trim function is a very important function in database part.
Sometimes, our front end application will populate some spaces with out actual data.
While validating the data ( "X" is not equal to " X") we will get improper data. To avoid this, SQL Server provides an option of trimming those white spaces.

For trimming the white space before the data, LTRIM function is used.
For trimming the white space after the data, RTRIM function is used.
For trimming both sides, we will use both function like LTRIM(RTRIM(ColumnName)

DECLARE @VenkatTable TABLE (id int, val varchar(10))
INSERT INTO @VenkatTable VALUES(1,' Venkat')
INSERT INTO @VenkatTable VALUES(1,' Ve nkat')
INSERT INTO @VenkatTable VALUES(1,' Venkat ')
INSERT INTO @VenkatTable VALUES(1,'Venkat ')
SELECT * FROM @VenkatTable
SELECT LTRIM(RTRIM(val)) as val FROM @VenkatTable



Happy Learning!!!
Regards,
Venkatesan Prabu .J

Server restart data in SQL Server

An interesting topic in SQL Server,

Is it possible to find the last restarted datatime of a server?

When my server is started is the question?

OOPS... where this data is stored. Frankly speaking even I dont know. But, I found a work around to achieve the same.

SQL Server is holding a database named 'tempdb' which gets flushed off during server down or server stop and created once sql server is restarted. So, we can fetch the relative data from tempdb's created date.

Below is the query to fetch this information,


SELECT create_date FROM sys.databases WHERE [name]='tempdb'

Happy Learning!!!

Regards,

Venkatesan Prabu .J

11.9.08

SQL Server interview questions

Some more SQL Server interview questions:
What are the authentication modes available in SQL Server?

Three types of authentication available,
1. Windows authentication - SQL Server authenticates based on the login windows user.
2. SQL Server authentication - A specific user leve authentication will be available to login into SQL Server.
3. Mixed mode authentication - It's a combination of windows and SQL Server authentication.


What are indexes, types and uses:
Indexes are nothing but a short cuts or high level idea about the table. SQL Server will have a high level idea about the data located. Its used to retrieve the data faster.
Two types of indexes available,
1. Clustered Indexes. - Only one index is available and it will physically sort the table.
This will hold the data in the leaf level and if a primary key is created. It will automatically create a clustered index. Clustered index is too heavy and took more memory.
2. Non- clustered indexes - 249 index can be created for a table.
This will hold the reference in the leaf level. Comparatively non clustered index will occupy less memory.

Rebuild index Vs Reorganize index:
Its a very important question and we need to know it for freshing up our knowledge.
  1. Rebuilding index is nothing but, scapping all the indexes and create fresh index so that the pages will be compressed and all the datas will be formatted (Storage wise) Where as reorganizing the index will do a high level compaction and it wont delete the existing index.
  2. We should not rebuild the index duing production time and it can be done during system downtime where as reorganising can be done during run time.

What do you mean by acid property?

Atomicity : The value should be atomic.

Consistency : The data in the database should be consistent. It indicates, if we are maintaining data in two different places. Those data should resemble. If it differs, then it will create confusions. Am I right?

Isolation : Transaction should be isolated and it should not affect the other transactions. If the transactions occured on the same data. Appropriate locks should be posted before starting the transactions.

Durability: The data should be stable.

Regards,

Venkatesan Prabu .J

7.9.08

ORDER BY CASE error in SQL Server

Considering am having two tables,

create table Employees(empid int, empname varchar(10),desig varchar(10),salary int, mgrid int)
insert into employees values(1,'aa','pm',10000,1)
insert into employees values(2,'bb','pm',10000,1)
insert into employees values(3,'cc','pl',500,2)
SELECT * FROM employees

create table employeedetails (empid int, City varchar(10), Telephone int)
insert into employeedetails values(1,'sydney',10)
insert into employeedetails values(1,'sydney1',10)
SELECT * FROM employeedetails

Now I need to fetch the data from the table in the order based on the id value.
If the ID has the value as 1 then the order should be based on the first column "ID" else, the order should be based on the second column "empName" Lets see, how to achieve the same,

select empid,empname from employees
union all
select empid,'a' as empname from employeedetails
order by
case
when empid=1 then 0
else 1
end
You will get an error,
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
In this case, we should do a work around to achieve the result, Let me try with derived tables concept,
select * from
(select empid,empname from employees
union all
select empid,'a' as empname from employeedetails )
t
order by
case
when t.empid=0 then 0
else 1
end

Now, we will get the exact output.
Happy Learning!!!
Regards,
Venkatesan Prabu .J

TSQL Interview Questions - Part2

T-SQL Interview Questions: (SQL Server Interview questions)
Considering am having two tables,


Considering am having two tables,
create table Employees(empid int, empname varchar(10),desig varchar(10),salary int, mgrid int)
insert into employees values(1,'aa','pm',10000,1)
insert into employees values(2,'bb','pm',10000,1)
insert into employees values(3,'cc','pl',500,2)
SELECT * FROM employees
SELECT * FROM employees
create table employeedetails (empid int, City varchar(10), Telephone int)
insert into employeedetails values(1,'sydney',10)
insert into employeedetails values(1,'sydney1',10)
SELECT * FROM employeedetails

The table structure should resemble as below,


Employees (empid, empname, Designation, salary, mgrid )
EmployeeDetails (empid, City, Telephone)

5. Group the employees based on the designation. Need the number of employees in each designation
SELECT Designation,COUNT(Designation) FROM employees GROUP BY Designation

6. Retrieve the number of employees having more than one phone number
SELECT distinct * FROM employeedetails A
INNER JOIN
(select empid,telephone,count(*) as cnt from employeedetails group by empid,telephone)t
ON A.empid = t.empid
WHERE t.cnt>1
7. Select the details of 3 max salaried employees from employee table.
SELECT TOP 3 empid, salary
FROM employee
ORDER BY salary DESC

8. Update 5 percent salary increment for all employees

UPDATE employees SET Salary = (Salary *105)/100
9. Display all managers from the table. (manager id is same as emp id)

select * from employees where empid IN (SELECT DISTINCT mgrid FROM employees)

10. Listing all the employees with Managers available
select a.empid,a.empname,b.mgrid,b.empname from employees a
inner join employees b
on a. mgrid =b.empid

Regards,
Venkatesan Prabu .J

T-SQL Interview Questions - Part 1

T-SQL Interview Questions: (SQL Interview questions)
Considering am having two tables,


create table Employees(empid int, empname varchar(10),desig varchar(10),salary int, mgrid int)
insert into employees values(1,'aa','pm',10,1)
insert into employees values(2,'aa','pm',10,1)
SELECT * FROM employees
create table employeedetails (empid int, City varchar(10), Telephone int)
insert into employeedetails values(1,'sydney',10)
insert into employeedetails values(1,'sydney1',10)
SELECT * FROM employeedetails

The structure of the table resembles,


Employees (empid, empname, Designation, salary, mgrid )
EmployeeDetails (empid, City, Telephone)

1. Select all employee with Salary>2000
Select empid, empname, Designation, salary, mgrid FROM employees
WHERE Salary >2000


2. Select all employee with Designation "Project Manager"
Select empid, empname, Designation, salary, mgrid FROM employees
where Designation='Project Manager'


3. Select all employee who doen'thave Telephone
SELECT DISTINCT A.empid, A.empname, A.Designation, A.salary, A.mgrid, B.city, B.Telephone FROM
employees A INNER JOIN EmployeeDetails B
ON A.empid=B.empid WHERE B.Telephone IS NOT NULL AND B.Telephone <> ''
(OR)
SELECT empid, empname, Designation, salary, mgrid
FROM employee
WHERE (empid IN
(SELECT empid
FROM EmployeeDetails where Telephone IS NOT NULL AND Telephone <> ''
GROUP BY empid


4. Select all employee belong to the city 'Sydney'
SELECT DISTINCT A.empid, A.empname, A.Designation, A.salary, A.mgrid, B.city, B.Telephone FROM
employees A INNER JOIN EmployeeDetails B
ON A.empid=B.empid WHERE B.City='Sydney'

Regards,
Venkatesan Prabu .J

Stored procedure output into table in sql server

Stored procedure output into the table:
I have seen lots of lots people are querying about, projecting stored procedure output
into a table.
I will take an extended procedure to explain this concept,


exec xp_fixeddrives
The above stored procedure is used to fetch the amount of space available in our hard disk.
My scenario is used to fetch the output of this SP and populate the same in my table.
We used to try the below query to achieve the same,


SELECT * INTO temp_table
exec xp_fixeddrives

OOps, am getting an error,
An object or column name is missing or empty.
For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names.
Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.


Whats the reason???. Its because, as per the syntax we have to specify the table and we can't specify the stored procedure or execute stored procedure in this context.
Lets see some work around for this problem,


In this case, we need to create a temp table or table and afterwards we have to do an insert statement.


CREATE TABLE temp_table (drive varchar(15),MBfree varchar(500))
INSERT INTO temp_table exec xp_fixeddrives
SELECT * FROM temp_table


Happy Learning!!!
Regards,
Venkatesan Prabu .J

Select * Vs Select column Name in SQL Server

In general, we used to put a select statement like,

Select * from TableName
Its one of the bad coding standard to retrieve the data from a particular table.
In turn, we have to specify the column name for the select statement.
The format should be,

Select ColName1, ColName2 etc.., from TableName
It will surely increase the performance of your query. Because, we are filtering the wanted column in the table + we specifying the exact column in the table.

Regards,
Venkatesan Prabu .J

Page Vs Extent in sql server

Extent Vs Page?
Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.
The size of the page is 8KB.
Eight consecutive pages will form an extent 8 * 8KB = 64KB. Thus I/O level operation will be happening at pages level.
The pages will hold a template information at the start of each page (header of the page). They are,
1. page number,
2. page type,
3. the amount of free space on the page,
4. the allocation unit ID of the object that owns the page.

Extents will be classifed into two types,

1. Uniform extents
2. Mixed extents

Uniform Extents:
It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.
Mixed Extents:
Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Property of SQL Server :
Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.
Herecomes, our fragmentation and reindexing concepts.
Regards,
Venkatesan Prabu .J

6.9.08

Copy table structure in SQL Server

Copy only structure with out data from SQL Server table:
Am creating a table name Table1,

CREATE TABLE Table1(id int primary key,nam varchar(10))
I am creating the second table Table2.
CREATE TABLE Table2(id1 int primary key, nam varchar(10))
Now, am trying to link these two tables using a column named id in first table with id in the second table.

ALTER TABLE Table2
ADD FOREIGN KEY (ID1) REFERENCES
Table1(ID)
Way to copy the structure of the table:
Using below query, we can copy the structure of the table without any data.

SELECT * INTO NewTable FROM Table1 WHERE 1=2
SQL Server will first create the table and insert the data. Since, our where clause fails due to the condition,
only structure will be created without any data.

SELECT count(*) FROM NewTable
The output will be 0
Happy Learning!!!
Regards,
Venkatesan Prabu .J

Copy table data in SQL Server

Copy table in sql server:
In our database world, we used to face this situation frequently. You will be given a table and requested
to copy those data into another table. Let's see how to achieve this scenario,
Am creating a table name Table1,

CREATE TABLE Table1(id int primary key,nam varchar(10))
I am creating the second table Table2.

CREATE TABLE Table2(id1 int primary key, nam varchar(10))
Now, am trying to link these two tables using a column named id in first table with id in the second table.

ALTER TABLE Table2
ADD FOREIGN KEY (ID1) REFERENCES
Table1(ID)
First way of copying source table data:
In this case, a new table can be created similar to the structure of the source table
and after wards, an insert statement can be fired

CREATE TABLE NewTable1(id int primary key,nam varchar(10))
INSERT INTO NewTable1(id,nam) SELECT id,nam FROM Table1

Second way to copy data from a table:
In this case, a table of similar structure will be created and data will be inserted into the table.
The new table structure resembles the old table structure.

SELECT * INTO NewTable2 FROM Table1
Oops!!! In the second case, I am doubting about the constraints. Whether the constraints is copied in the new table. No, its not the case.

Only the structure and the data will get copied to the new table and we need to create our constraints on the new table.
Lets check it by executing the below query,
SP_HELP Table1-- [sp_help will provide the details about the system object]


Let's check with the new table created.

SP_HELP NewTable2

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Foreign key in SQL Server

Am creating a table name Table1,

CREATE TABLE Table1(id int primary key,nam varchar(10))
I am creating the second table Table2.

CREATE TABLE Table2(id1 int primary key, nam varchar(10))
Now, am trying to link these two tables using a column named id in first table with id in the second table.

ALTER TABLE Table2
ADD FOREIGN KEY (ID1) REFERENCES
Table1(ID)
Foreign key: It forms a relation ship between the Master table and Child table.
Here Table1 is the master table where as, Table2 is the child table.
It inturns define a basic rule, "Master table should have a record before inserting into child table".
Let's try to insert some data in the child table without inserting appropriate records in the master table.

INSERT INTO Table2 VALUES(2,'VP')
We will get an error indicating, foreign key constraint wont allow for this.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Table2__id1__2898D86D".
The conflict occurred in database "master", table "dbo.Table1", column 'id'.

Let me try to insert some value in master table.

INSERT INTO Table1 VALUES(2,'VP')
Output is,
(1 row(s) affected)
Let's make a try with Table2.

INSERT INTO Table2 VALUES(2,'VP')
Output is,
(1 row(s) affected)

Hope its clear!!!
Happy Learning!!!
Regards,
Venkatesan Prabu .J

5.9.08

SQL Server Interview question - Part 2

What is normalization?
Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.


What is a stored procedure:

Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.


What is the difference between UNION ALL Statement and UNION ?

The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Example for Stored Procedure?

They are three kinds of stored procedures,
1.System stored procedure – Start with sp_
2. User defined stored procedure – SP created by the user.
3. Extended stored procedure – SP used to invoke a process in the external systems.
Example for system stored procedure
sp_helpdb - Database and its properties
sp_who2 – Gives details about the current user connected to your system.
sp_renamedb – Enable you to rename your database


What is a trigger?
Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.


What is a view?

If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.

What is an Index?

When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.


What are the types of indexes available with SQL Server?
There are basically two types of indexes that we use with the SQL Server
Clustered - 1. It will format the entire table, inturn physically sort the table.
2. Only one clustered index can be created for a table.
3. Data will be located in the leaf level.
4. By default, primary key will create clustered index on the table.
Non-Clustered Index – 1. It wont touch the structure of the table.
2. It forms an index table as reference to the exact data.
3. A reference to the data will be located in the leaf level.
4. For a table, we can create 249 non clustered index.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Server Interview questions

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.
We should avoid permitting null values because
Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.


What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.

The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

What is DTS in SQL Server ?
Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.
What is the difference between SQL and Pl/Sql ?
Straight forward. SQL is a single statement to finish up our work.
Considering, I need some data from a particular table.
“Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Programming Logic / SQL.

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.
We should avoid permitting null values because
Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.
Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

SQL Injection

SQL Injection:
Internet Hackers introduces the concept called SQL injection. It'snothing but injecting some malicious code to hack your system or to break with website. Since, they are using the concepts of sql server to break the security of the server. It's referred to as SQL Injection.
Considering am creating a table which contains username and password and based on the username and password. The user will be allowed to enter into the system.
CREATE TABLE VenkatSQLInjection(id INT,USERNAME VARCHAR(100),password VARCHAR(10))
INSERT INTO VenkatSQLInjection VALUES(1,'Venkat','Venkat')
INSERT INTO VenkatSQLInjection VALUES(2,'SUBA','SUBA')
SELECT * FROM VenkatSQLInjection

Below is teh procedure to validate the user authentication,
CREATE PROCEDURE SQLInjectionProc
(
@USR VARCHAR(10),
@PWD VARCHAR(10),
@VAL INT OUTPUT
)
AS
BEGIN
SELECT @VAL =COUNT(*) FROM VenkatSQLInjection WHERE USERNAME = @USR AND password = @PWD
RETURN @VAL
END


IF the user knows the username by using cookies. They will try to hack the system with out password. One possibility is, Considering you will be having a GUI which ask for User name and password. If the user give the existing username. For example, 'SUBA''--' and password as 'ABC'
In SQL Server, the operation resembles,

SELECT COUNT(*) FROM VenkatSQLInjection WHERE USERNAME = 'SUBA'-- AND password = 'ABC'

Obviously, the system will recognize the person as valid user and Its the basic foudation for hacking.
Happy Learning!!!
Regards,
Venkatesan Prabu .J

Views in sql server


Views in sql server:
View is a very good concept in sql server. View is nothing but a reference for a table. It inturn refer the table itself.
Its like a layer placed on the table. So that, we will be providing an extra layer over the tables. Instead of a using complex join, we
can views which will hold the entire logic and hope it's supporting encapsulation logic :-).
Creating a sample table:

CREATE TABLE venkat1(id int, name varchar(100))

INSERT INTO venkat VALUES(1,'aa;bb;cc;dd;')

INSERT INTO venkat VALUES(2,'EE;FF;GG;HH')

SELECT * FROM venkat

Creating view on the table:


CREATE VIEW VENKATVIEW AS

SELECT * FROM venkat

Selecting data from the view:


SELECT * FROM VENKATVIEW
DML operations on the view:
(Insert statement on views)


INSERT INTO VENKATVIEW VALUES(2,'VE')

SELECT * FROM VENKAT

(Delete statement on views)


DELETE FROM VENKATVIEW WHERE ID=1
(Update statement on views)


UPDATE VENKATVIEW SET NAME='AA' WHERE ID=2

Happy Learning!!!

Regards,

Venkatesan Prabu .J

My T-SQL Gallery @code.msdn.microsoft


Created my own T-SQL Gallery in Microsoft site. Do visit the same and share your feedback,

http://code.msdn.microsoft.com/VenkatSQLSample/Thread/List.aspx

Thanks and Regards,
Venkatesan Prabu .J

SQL Server Interview questions - Part 1

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.


The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables


What is DTS in SQL Server ?
Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.


What is the difference between SQL and Pl/Sql ?

Straight forward. SQL is a single statement to finish up our work.Considering, I need some data from a particular table. “Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Procedural Logic / SQL.

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

Select Statement in SQL Server

Select Statement in SQL Server

String Functions in sql server

String Functions in sql server
Substring/Len/replace/Ltrim/Rtrim

SQL Server Interview Question - Part 2

What is normalization?

Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.


What is a stored procedure:
Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.


What is the difference between UNION ALL Statement and UNION ?
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Example for Stored Procedure?
They are three kinds of stored procedures,1.System stored procedure – Start with sp_2. User defined stored procedure – SP created by the user.3. Extended stored procedure – SP used to invoke a process in the external systems.Example for system stored proceduresp_helpdb - Database and its propertiessp_who2 – Gives details about the current user connected to your system. sp_renamedb – Enable you to rename your database


What is a trigger?

Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.


What is a view?
If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.


What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.


What are the types of indexes available with SQL Server?

There are basically two types of indexes that we use with the SQL ServerClustered -

1. It will format the entire table, inturn physically sort the table.

2. Only one clustered index can be created for a table.

3. Data will be located in the leaf level.

4. By default, primary key will create clustered index on the table.

Non-Clustered Index

1. It wont touch the structure of the table.

2. It forms an index table as reference to the exact data.

3. A reference to the data will be located in the leaf level.

4. For a table, we can create 249 non clustered index.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Interview question

Extent Vs Page?

Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.The size of the page is 8KB.

Eight consecutive pages will form an extent 8 * 8KB = 64KB.

Thus I/O level operation will be happening at pages level.The pages will hold a template information at the start of each page (header of the page).

They are,

1. page number,

2. page type,

3. the amount of free space on the page,

4. the allocation unit ID of the object that owns the page.

Extents will be classifed into two types,

1. Uniform extents

2. Mixed extents

Uniform Extents:It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.Mixed

Extents:Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Property of SQL Server :Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.

Herecomes, our fragmentation and reindexing concepts.

Best Joke - Enjoy it

Best Joke - Enjoy it