Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Remembering that it is good to "get back to basics" every now and then Expand / Collapse
Author
Message
Posted Tuesday, August 6, 2013 1:35 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:28 AM
Points: 116, Visits: 389
I have a new position where I finally have the long sought after Sr. prefix included. Only time will tell if I truly deserve the prefix, but I believe that I can do the job. One of the things that is different about this position by comparison to my most recent projects / positions is a more formalized deployment / implementation process (THANK GOD FOR THIS!!! ). Part of this process is a code review with several other developers that are at least as good as I am...and in many cases better.

I had my first code review today, and the only real issue anyone had with it (aside from learning naming conventions) involved a bit of code that I have used for years on end:
Select VariousFields
Into dbo.TestTable
From dbo.SourceTable(s)

----------
Set @RecordCount =
(Select Count(TestTable_PrimaryKey)
From dbo.TestTable)

The code goes on to split the logic path based on @RecordCount = 0 vs. @RecordCount > 0. This is a section of code that I could have said I had down as well as tying my own shoes (there is a Ted Talks reference there, and this entire post lines up with it).

The conversation went something like this:
Other developer: Why did you use this code here?
Me: Because I was needing to split the logic path.
Other developer: Yes, I see that. What I mean was why didn't you use:
Select VariousFields
Into dbo.TestTable
From dbo.SourceTable(s)

----------
Set @RecordCount = @@RowCount

Me: Because I had never heard of it, thought of it, or seen it before you just typed it.

This post is meant to remind not just me but everyone who reads this to look at those parts of our code that seem all but written in stone that we will use the same approach each time. Is there a better way to do what you're doing?

A small advantage in any area of life can sometimes be the difference between adequate results and all star results.


“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson
Post #1481527
Posted Tuesday, August 6, 2013 2:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 13,203, Visits: 10,064
Interesting story.
I hope they didn't give you the "and you're the senior guy?" look because you didn't memorize all possible TSQL functions.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1481550
Posted Tuesday, August 6, 2013 2:18 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:28 AM
Points: 116, Visits: 389
Thankfully no. The one guy who knew that step was the only one in the room who knew it. All of us in the review were either Sr. or team lead.

“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson
Post #1481552
Posted Tuesday, August 6, 2013 2:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 13,203, Visits: 10,064
jarid.lawson (8/6/2013)
Thankfully no. The one guy who knew that step was the only one in the room who knew it. All of us in the review were either Sr. or team lead.


You should try to follow the question of the day here at SSC (if you don't already). Sometimes there's a great question which introduces you to little gems of SQL Server.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1481553
Posted Tuesday, August 6, 2013 2:45 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:28 AM
Points: 116, Visits: 389
Koen Verbeeck (8/6/2013)
You should try to follow the question of the day here at SSC (if you don't already). Sometimes there's a great question which introduces you to little gems of SQL Server.


I've been on the site for years, and I've never been to that before. Thank you. We need a FaceBook style Like button for your post.

“No day in which you learn something is a complete loss.”
- David Eddings King of the Murgos


“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson
Post #1481566
Posted Tuesday, August 6, 2013 2:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 12,864, Visits: 31,712
no, you should say "Because @@rowcount is affected by any commands in the session, it's my practice to re-select from the table in case there was a trigger that might returna different @@rowcount i might not expect."

just blame force of habit to resolve rare edge cases like that.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1481570
Posted Tuesday, August 6, 2013 2:54 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:31 PM
Points: 1,911, Visits: 2,827
code goes on to split the logic path based on @RecordCount = 0 vs. @RecordCount > 0.



You shouldn't do a full count of the table for that either, btw; use EXISTS() instead:


IF EXISTS(SELECT TOP (1) 1 FROM dbo.tablename)
BEGIN
--actions to do when table has at least one row
END
ELSE
BEGIN
--actions to do when table has no row
END




SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1481571
Posted Tuesday, August 6, 2013 3:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:51 PM
Points: 13,028, Visits: 11,838
Lowell (8/6/2013)
no, you should say "Because @@rowcount is affected by any commands in the session, it's my practice to re-select from the table in case there was a trigger that might returna different @@rowcount i might not expect."

just blame force of habit to resolve rare edge cases like that.


I read this post and thought the same thing. So I decided I should test my theory. It seems that @@rowcount returns the correct number at least in this simple test.

create table RowCountTest
(
id int
)

go

create trigger RowCountTestTrigger on RowCountTest after insert as
select top 1000 *
into #Something
from Tally

go

insert RowCountTest
select 1

select @@ROWCOUNT

If this was a QOTD I would have answered that the value of @@rowcount would be 1000. Interesting. Maybe I will have to do a little more digging and submit as a qotd soon.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1481587
Posted Tuesday, August 6, 2013 3:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:28 PM
Points: 3,254, Visits: 7,007
Lowell (8/6/2013)
no, you should say "Because @@rowcount is affected by any commands in the session, it's my practice to re-select from the table in case there was a trigger that might returna different @@rowcount i might not expect."

just blame force of habit to resolve rare edge cases like that.

You're partially right about this and is a good thing to know. However, I don't know how a table that was just created by a SELECT...INTO would have any triggers.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1481594
Posted Tuesday, August 6, 2013 4:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:17 PM
Points: 1,770, Visits: 5,601
Sean Lange (8/6/2013)
Lowell (8/6/2013)
no, you should say "Because @@rowcount is affected by any commands in the session, it's my practice to re-select from the table in case there was a trigger that might returna different @@rowcount i might not expect."

just blame force of habit to resolve rare edge cases like that.


I read this post and thought the same thing. So I decided I should test my theory. It seems that @@rowcount returns the correct number at least in this simple test.

create table RowCountTest
(
id int
)

go

create trigger RowCountTestTrigger on RowCountTest after insert as
select top 1000 *
into #Something
from Tally

go

insert RowCountTest
select 1

select @@ROWCOUNT

If this was a QOTD I would have answered that the value of @@rowcount would be 1000. Interesting. Maybe I will have to do a little more digging and submit as a qotd soon.


An INSTEAD OF trigger can definitely result in @@rowcount being different to SELECT count(x) FROM ...


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1481603
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse