Remembering that it is good to "get back to basics" every now and then

Question

Post reply

Remembering that it is good to "get back to basics" every now and then

jarid.lawson

SSCrazy

Points: 2953
More actions
August 6, 2013 at 1:35 pm

#395252

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!!! :-D). 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.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

jarid.lawson SSCrazy Points: 2953 More actions · Answer 2

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. 😀

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

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.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

jarid.lawson SSCrazy Points: 2953 More actions · Answer 4

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

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 5

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101099 More actions · Answer 6

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

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 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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

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

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

jarid.lawson SSCrazy Points: 2953 More actions · Answer 10

Wow...to think there is so much surrounding such a simple step. Thank you all for the input...I will have to play with this even more to find all the moving parts.

The good news is my entire idea behind the original post worked. We have all picked up a few new ways and perspectives to do a basic step...well, most of us have. Thanks to all who have added the information I didn't know at the beginning.

[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein

"DOH!"
- Homer Simpson[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Luis Cazares (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.
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. 😛

😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

jarid.lawson (8/6/2013)
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!!! :-D). 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.

There's something else that you need to be aware of... although there are certain things that won't change the rowcount, you should get into the habit of never having any other code between the SELECT/INTO (or any other query) and the SET @Variable = @@ROWCOUNT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

Sean Lange (8/6/2013)
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.

Do that, it would make an interesting question.

(and one easy score for me, since I now know the answer. Moehahahahahaha :cool:)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

mister.magoo (8/6/2013)
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 ...

Same result changing this to an instead of trigger.

alter trigger RowCountTestTrigger on RowCountTest

instead of insert as

select top 1000 *

into #Something

from Tally

go

insert RowCountTest

select 1

select @@ROWCOUNT

_______________________________________________________________

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/