Sql Server Interview Questions and Answers (1758) - Page 84

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

How do you debug the stored procedure?

Old is Gold
--------------
By using the Print() command of TSQL

Since Sql Server 2008
------------------------
From SSMS,we can start the debugger by either clicking the Debug button on the Query toolbar or by clicking Start Debugging on the Debug menu or pressing ALT+F5.

From Visual Studio 2010
---------------------------
Step 1: Open Server Explorer -> Server nodes-> SQL Server machine name -> SQL Servers node -> SQL Server instance -> Your database node -> stored procedures node.

Step 2: Right-click YOUR stored procedure and then click Step Into Stored Procedure.

Step 3: Press F11 to step through the stored procedure.

Suppose we have a table with the values WayPointsID Latitude Longitude PhoneNumber 1 -34.668516349643596 150.81978125 9886558702 2 -35.09660198927241 150.457232421875 9886558702 3 -34.713682925540354 149.74312109375 9886558702 4 -34.27452911659509 149.6167783203125 9886558702 5 -33.56343076390862 150.2869443359375 9886558702 What will be the output of the below? SELECT * FROM [dbo].[tblWayPoints] WHERE [WayPointsID] = CAST(RAND()*5 AS INT)

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of SELECT CAST(ROUND(123456789.987654321, 2, 1) AS DECIMAL(18, 2))

NOTE: This is objective type question, Please click question title for correct answer.

Consider the below query SELECT CAST(ROUND(InputNumber, N, 1) AS DECIMAL(18, N)) Where N=1,2,3,4,5,.... What will be the output of the query if InputNumber=876543.123456 and N=4

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of declare @Table1 table(id int) insert into @Table1 select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 declare @Table2 table(id int) insert into @Table2 select 1 union all select 2 union all select 5 union all select 8 select id from @Table1 except select id from @Table2

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of declare @str as varchar(100) set @str = 'Where are you now?' select REPLACE(@str,' ' ,'')

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of SELECT 'RNA''s'

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of DECLARE @str AS VARCHAR(500) ,@xml AS XML ,@delimiter AS VARCHAR(1) --Set the original string SET @str= 'Hello!!!This is text1 (text1).This is text2(text2).This is text3(text3) and text4(text4)' --Set the delimiter SET @delimiter ='@' -- Replace the ( and ) with '@-' and '-@' respectively SELECT @str =REPLACE(REPLACE(@str,'(','@-'),')','-@') -- Replace the '@' symbols with </X><X> SET @xml = CAST(('<X>'+REPLACE(@str,@delimiter ,'</X><X>')+'</X>') AS XML) --Query the XML using XQuery ;WITH CTE AS( SELECT N.value('.', 'varchar(500)') AS QueryResult FROM @xml.nodes('X') AS T(N) WHERE PATINDEX('%[-]%', N.value('.', 'varchar(100)')) = 0 AND LEN(N.value('.', 'varchar(500)')) > 0) SELECT ExtractedText = STUFF((SELECT ''

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of declare @str as varchar(100) declare @startposition int declare @endposition int declare @stopingcondition int declare @delimeter char(1) set @startposition = 0 set @stopingcondition = 10 set @delimeter ='' set @str = 'I would like to get word from a long sentence but not sure how to do this. I guess I should be looking for 15th space in the word but not sure how to do this could some please help?' -- original data ;with num_cte as ( select 1 as rn union all select rn +1 as rn from num_cte where rn <= len(@str) ) , get_all_delimited_char_pos_cte as ( select row_number()over(order by rn) cnt ,rn,chars from num_cte cross apply( select substring(@str,rn,1) AS chars) splittedchars where chars = @delimeter ) select @endposition = rn from get_all_delimited_char_pos_cte where cnt = @stopingcondition select SUBSTRING(@str,@startposition,@end

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of declare @tbl table(id int identity, refid int) insert into @tbl select 0 union all select 1 union all select 2 union all select 1 union all select 1 select t.id,t.refid,case when x.cnt is null then 0 else x.cnt end as total from @tbl t left join ( select refid,count(refid) as cnt from @tbl where refid <> 0 group by refid having (count(refid)>0)) x on t.id = x.refid

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of declare @t table(name varchar(50),subject varchar(50)) insert into @t select 'Rajlaxmi','Physics' union all select 'Rajlaxmi','Chemistry' union all select 'Rajlaxmi','Maths' union all select 'Niladri','Physics' union all select 'Niladri','Stats' union all select 'Niladri','Maths' union all select 'Arina','Physics' union all select 'Arina','Chemistry' union all select 'Arina','Maths' union all select 'RNA','Physics' union all select 'RNA','Chemistry' union all select 'RNA','Stata' select name from @t where subject ='Maths' or subject ='Physics' or subject ='Chemistry' group by name having (COUNT(name)>2)

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of SELECT REPLICATE ('DNF ', 5)

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of PRINT 'DNF ' GO 5

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of with cte as ( select 1 as rn,'DNF ' as data union all select rn+1, 'DNF ' as data from cte where rn<5) select data from cte option (maxrecursion 0)

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of declare @str varchar(max) set @str = 'D2A3D81C-F76E-44A4-9D47-83FBE4DDB76B' ;with cte as( select 1 as rn union all select rn+1 from cte where rn<LEN(@str)), cte2 as( select rn,chars from cte cross apply(select SUBSTRING(@str,rn,1) chars)X where chars like '%[0-9]%' ) select * from ( select cast(chars as varchar(max))from cte2 for xml path(''))X(numericData)

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of declare @str varchar(max) set @str = 'D2A3D81C-F76E-44A4-9D47-83FBE4DDB76B' ;with cte as( select 1 as rn union all select rn+1 from cte where rn<LEN(@str)), cte2 as( select rn,chars from cte cross apply(select SUBSTRING(@str,rn,1) chars)X where chars like '%[A-Za-z]%' ) select * from ( select cast(chars as varchar(max))from cte2 for xml path(''))X(numericData)

NOTE: This is objective type question, Please click question title for correct answer.

What will the below command do? EXEC xp_cmdshell 'RENAME D:\onlytest RenamedFolderonlytest'

NOTE: This is objective type question, Please click question title for correct answer.

What will be the output of Select Patindex('%[0-9]%',REVERSE(REVERSE(NEWID())))

NOTE: This is objective type question, Please click question title for correct answer.

What the below code is performing? USE msdb EXEC msdb..sp_send_dbmail @profile_name='Test Cient', @recipients='receipent1@yopmail.com;receipent2@yopmail.com', @subject='Daily File Report', @body= 'Please find enclosed the daily file report', @file_attachments='D:\FileToExport.txt'

The above code will send email using the sp_send_dbmail of MSDB which will reach to the recipients receipent1@yopmail.com;receipent2@yopmail.com with the subject as 'Daily File Report' , the body being 'Please find enclosed the daily file report' and a file attachment 'D:\FileToExport.txt'

Why it is said to avoid WHILE Loops in SQL Server?

In the case of While loops the rows are rows are processed one after another and hence if we want to submit any job(s) to the query engine, it process in a sequential manner.Where as if we can re-write the same query by using in a SET Based manner, we can submit a complete batch of job(s) to the query engine which on the other hand will be processed much faster way.

1
...
77
78
79
80
81
82
83
84
85
86
87
88

More SQL SERVER Exclusive Interview Questions & Answers here

Found this useful, bookmark this page to the blog or social networking websites.

GooglePlus

StumbleUpon

Delicious

Reddit

Email

Bookmark It

Interview Questions and Answers Categories

.NET Certifications

ASP.NET Web API

Data Structures & Algorithms

Design Pattern & Practices

DotNetFunda.Com

Entity Framework

Error and Solution

Function Points (FPA)

Interview Questions

Mobile Development

MSBI (SSIS, SSRS, SSAS)

News and Community

Product Reviews

Project Management

Regular Expressions

Web Services, Remoting