SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jarid.lawson
jarid.lawson
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 436
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.

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

"DOH!"
- Homer Simpson

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88384 Visits: 41128
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. :-P


:-D

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88384 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27823 Visits: 13268
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 :coolSmile


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

MCSE Business Intelligence - Microsoft Data Platform MVP
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26506 Visits: 17557
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.

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)
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4184 Visits: 7865
Sean Lange (8/7/2013)

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.



Yes, you get @@rowcount = 1.

The OP's original situation was that they wanted to know how many rows in the table.

In your test case the table contains zero rows (using the instead of trigger).
...but @@rowcount returns 1.

My point here is that if you need to know how many rows are in the table, you had better count them, not rely on a function that could be telling you something other than the obvious. :-P


drop table RowCountTest;
go
create table RowCountTest
(
id int
)

go

create trigger RowCountTestTrigger on RowCountTest
instead of insert as
select top 1000 *
into #Something
from syscolumns

go

insert RowCountTest
select 1

select @@ROWCOUNT
select count(*)
from RowCountTest



MM


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




  • 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

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search