July 27, 2015 at 12:35 pm
TSQL interview Question on @@ROWCOUNT
How can you find the number of rows affected by a SQL statement? or What is the use of @@ROWCount?
http://abhishekgaikwadtech.blogspot.in/
Solution :
@@Rowcount returns the number of rows affected by a query.
Example
When you run the below update statement ,@@Rowcount will give you the number of rows that were affected by this update statement.
update [Emp_Details] set ManagerID = 107 where ManagerID = 105
select @@ROWCOUNT
How to get the last record value in sql server without using MAX/TOP clause?
Solution :
Using the below query.
set rowcount 1
select * from employees order by id desc
2) Consider a Table name A which has below records
ID
---
5
5
5
5
5
Consider another table B which has below records
ID
--
5
5
5
5
5
5
5
5
How many rows will be returned by each of the below queries
a) select * from A inner join B on A.id = b.ID
b) select * from A left join B on A.id = b.ID
c) select * from A right join B on A.id = b.ID
Solution :
a) 40 rows
b) 40 rows
c) 40 rows
1) If a table is having only one identity column how can we insert records into this table?
Answer :
Consider table customer which is created with below statement in sql server
create table customer
( id int identity(1,1))
Now to insert records into this table we can do it in two ways:
a) INSERT INTO customer DEFAULT VALUES
b)
SET IDENTITY_INSERT dbo.customer ON
INSERT INTO customer (id)
values (1),(2),(3)
July 27, 2015 at 1:28 pm
Although "theoretically" this is right, one cannot endorse the use of @@ROWCOUNT in that way, too many gaps and exceptions.
😎
July 27, 2015 at 2:36 pm
This has been deprecated:
Using the below query.
set rowcount 1
select * from employees order by id desc
Use TOP instead.
July 27, 2015 at 2:39 pm
I can only surmise that this is your blog that you are linking to.
abhishek_300 (7/27/2015)
How to get the last record value in sql server without using MAX/TOP clause?Solution :
Using the below query.
set rowcount 1
select * from employees order by id desc
This is making a lot of assumptions here. First of all setting rowcount affects all queries in that connection. Unless you remember to set it back to 0 all subsequent queries will be affected. The bigger issue is that this is not necessarily true. This code is making an assumption that id is an identity column that is always incrementing. If the value for id is set by code or the value is a decrementing identity this code will not return the correct row.
Here is a working proof.
create table employees
(
id int identity(100, -1)
, FullName varchar(50)
)
insert employees
select 'I am the oldest.'
insert employees
select 'This is the ''newest'''
select * from employees order by id desc
1) If a table is having only one identity column how can we insert records into this table?
By definition you can't have a table with more than 1 identity column. I assume the intention is to state that the table has only 1 column and it is an identity. My argument would be that a table with only a single identity column is not a table and therefore this question is pointless.
While I applaud the effort I am afraid that the questions are not very robust.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 27, 2015 at 11:12 pm
Thanks Sean Lange for your reply.
1) Yes you are correct the rowcount should be set back to 0. So I have modified the query to below
SET ROWCOUNT 1
select * from employees order by id desc
SET ROWCOUNT 0
2) Also it won't work when you are setting the identity(100, -1) value to negative value.
3) For this question (If a table is having only one identity column how can we insert records into this table?)
I know this is pointless to have a table with only one identity column and there are no other columns.
However the interviewer trys to confuse the person with such question as he may have never tried it.
July 28, 2015 at 2:52 am
Eirikur Eiriksson (7/27/2015)
Although "theoretically" this is right, one cannot endorse the use of @@ROWCOUNT in that way, too many gaps and exceptions.😎
What gaps and exceptions, other than a trigger?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2015 at 2:55 am
abhishek_300 (7/27/2015)
How to get the last record value in sql server without using MAX/TOP clause?
Answer: Why are you enforcing silly and unrealistic restrictions on my code? Should I be expecting equally strange requirements in my actual projects preventing me from using the obvious and correct way of doing things?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2015 at 6:36 am
GilaMonster (7/28/2015)
Eirikur Eiriksson (7/27/2015)
Although "theoretically" this is right, one cannot endorse the use of @@ROWCOUNT in that way, too many gaps and exceptions.😎
What gaps and exceptions, other than a trigger?
Simple thing which are too easy to get wrong such as reset by variable declaration with value assignment, begin/commit tran etc..
😎
July 28, 2015 at 6:46 am
GilaMonster (7/28/2015)
abhishek_300 (7/27/2015)
How to get the last record value in sql server without using MAX/TOP clause?Answer: Why are you enforcing silly and unrealistic restrictions on my code? Should I be expecting equally strange requirements in my actual projects preventing me from using the obvious and correct way of doing things?
+1 for the answer :hehe:
July 28, 2015 at 7:19 am
Thanks Gila monster for your comments .However for interviews people do not ask simple question on SQL hence you are seeing unrealistic example.
July 28, 2015 at 7:37 am
abhishek_300 (7/28/2015)
Thanks Gila monster for your comments .However for interviews people do not ask simple question on SQL hence you are seeing unrealistic example.
There's no reason an interview question can't be complicated without arbitrarily asking to avoid using the proper syntax.
July 29, 2015 at 7:59 am
abhishek_300 (7/28/2015)
Thanks Gila monster for your comments .However for interviews people do not ask simple question on SQL hence you are seeing unrealistic example.
People don't (always) ask simple questions, but in order to be worthwhile, they should be realistic. This question would indicate to me that the rest of the interview was as likely to be influenced by the colour of my socks as the answers I gave to any of the questions.
July 29, 2015 at 8:12 am
abhishek_300 (7/28/2015)
Thanks Gila monster for your comments .However for interviews people do not ask simple question on SQL hence you are seeing unrealistic example.
And if they don't ask realistic questions, why continue the interview and try to work there?
My interviews always get realistic questions. I can better determine your skillset by using questions that are not unrealistic.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 29, 2015 at 9:21 am
abhishek_300 (7/28/2015)
Thanks Gila monster for your comments .However for interviews people do not ask simple question on SQL hence you are seeing unrealistic example.
I can ask some very non-simple questions without imposing unrealistic restrictions on what may be used in a query.
Eg:
Write a query which shows the third set of 10 rows from a table, with the ordering imposed by the column TransactionTime.
What other methods could you have used?
What are the advantages and disadvantages of each method?
That's going to tell me a lot more about the person's ability to code, think and evaluate their code than asking them to do things like join two tables without using a join clause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply