Clarification on the ROWCOUNT statement

Hi All,
I just wanted to get some clarification on the ROWCOUNT statement in T-SQL.
I'm presently using it to limit the number of rows returned in SELECT based
SPs.
However I'm just curious as the effect of this keyword on the global scope
of SQL operations.
Does setting ROWCOUNT to 25 in one stored procedure effect other SPs running
?
Or is it only specific to the SP which set it?
Also...if it does only effect the code in the specific SP which set the
ROWCOUNT why is it recommended/required to set it back to zero when you are
done?
I have tested this and is "seems" to have no effect on other code running,
but I just want to make sure before I create considerable headaches in my
application.
Thanks for any feedback.
John RossitterJohn,
You can just use the TOP clause with an ORDER BY clause to ommit the need of
setting ROWCOUNT.
From SQL BOL:
It is recommended that DELETE, INSERT, and UPDATE statements currently using
SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see
DELETE, INSERT, or UPDATE.
The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and
DELETE statements against remote tables and local and remote partitioned
views.
HTH
Jerry
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
> Hi All,
> I just wanted to get some clarification on the ROWCOUNT statement in
> T-SQL.
> I'm presently using it to limit the number of rows returned in SELECT
> based
> SPs.
> However I'm just curious as the effect of this keyword on the global scope
> of SQL operations.
> Does setting ROWCOUNT to 25 in one stored procedure effect other SPs
> running?
> Or is it only specific to the SP which set it?
> Also...if it does only effect the code in the specific SP which set the
> ROWCOUNT why is it recommended/required to set it back to zero when you
> are
> done?
> I have tested this and is "seems" to have no effect on other code running,
> but I just want to make sure before I create considerable headaches in my
> application.
> Thanks for any feedback.
> John Rossitter
>|||The ROWCOUNT setting does not bleed to other connections and it is also loca
l to inside the
procedure. I still always type a comment to reset it to 0 the very same mome
nt as I type the setting
to non-zero. Just in case someone else add some code to the procedure later.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
> Hi All,
> I just wanted to get some clarification on the ROWCOUNT statement in T-SQL
.
> I'm presently using it to limit the number of rows returned in SELECT base
d
> SPs.
> However I'm just curious as the effect of this keyword on the global scope
> of SQL operations.
> Does setting ROWCOUNT to 25 in one stored procedure effect other SPs runni
ng?
> Or is it only specific to the SP which set it?
> Also...if it does only effect the code in the specific SP which set the
> ROWCOUNT why is it recommended/required to set it back to zero when you ar
e
> done?
> I have tested this and is "seems" to have no effect on other code running,
> but I just want to make sure before I create considerable headaches in my
> application.
> Thanks for any feedback.
> John Rossitter
>|||Hi Jerry,
I can't use TOP because I need a dynamic number of rows selected.
Which is why I'm using ROWCOUNT directive instead.
To clarify what I have done, is in one of my tables set a MaxRows threshold.
Now when any of my SELECT based SPs execute the code looks something like
this:
====================================
DECLARE @.TOP int;
SET @.TOP = 1000
SELECT @.TOP = Max_Record_Count FROM Control_Table
SET ROWCOUNT @.TOP
SELECT ....
SET ROWCOUNT 0
=====================================
This way I can control the overall performance of all of my queries.
I just want to make sure that the ROWCOUNT setting does not have an impact
on any other code running in SQL.
For example lets say that the above SP was running, and then another started
in the middle of it's execution. Should I expect to see the ROWCOUNT clause
effect the 2nd query, or does it only effect the code which it's currently
executing.
I hope that helps clarify my question.
Thanks,
John Rossitter
"Jerry Spivey" wrote:

> John,
> You can just use the TOP clause with an ORDER BY clause to ommit the need
of
> setting ROWCOUNT.
> From SQL BOL:
> It is recommended that DELETE, INSERT, and UPDATE statements currently usi
ng
> SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see
> DELETE, INSERT, or UPDATE.
> The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and
> DELETE statements against remote tables and local and remote partitioned
> views.
> HTH
> Jerry
> "John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in messag
e
> news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
>
>|||Ok. I'm not aware of any issues. My testing confirms the same results - no
side affects. However that being said, I would recommend appending a SET
ROWCOUNT 0 to your code for completeness.
HTH
Jerry
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:9596F2C7-649D-4EDD-98B3-A7C321FD4038@.microsoft.com...
> Hi Jerry,
> I can't use TOP because I need a dynamic number of rows selected.
> Which is why I'm using ROWCOUNT directive instead.
> To clarify what I have done, is in one of my tables set a MaxRows
> threshold.
> Now when any of my SELECT based SPs execute the code looks something like
> this:
> ====================================
> DECLARE @.TOP int;
> SET @.TOP = 1000
> SELECT @.TOP = Max_Record_Count FROM Control_Table
> SET ROWCOUNT @.TOP
> SELECT ....
> SET ROWCOUNT 0
> =====================================
> This way I can control the overall performance of all of my queries.
> I just want to make sure that the ROWCOUNT setting does not have an impact
> on any other code running in SQL.
> For example lets say that the above SP was running, and then another
> started
> in the middle of it's execution. Should I expect to see the ROWCOUNT
> clause
> effect the 2nd query, or does it only effect the code which it's currently
> executing.
> I hope that helps clarify my question.
> Thanks,
> John Rossitter
>
> "Jerry Spivey" wrote:
>|||Hi
You can use top in dynamic queries like this
DECLARE @.TOP int;
DECLARE @.VAR varchar(200)
SET @.TOP = 1000
SELECT @.TOP = Max_Record_Count FROM Control_Table
select @.VAR = 'SELECT TOP' + CAST(@.TOP as varchar(10) + ' from your table
name...'
exec(@.VAR)
--
Regards
R.D
--Knowledge gets doubled when shared
"John Rossitter" wrote:
> Hi Jerry,
> I can't use TOP because I need a dynamic number of rows selected.
> Which is why I'm using ROWCOUNT directive instead.
> To clarify what I have done, is in one of my tables set a MaxRows threshol
d.
> Now when any of my SELECT based SPs execute the code looks something like
> this:
> ====================================
> DECLARE @.TOP int;
> SET @.TOP = 1000
> SELECT @.TOP = Max_Record_Count FROM Control_Table
> SET ROWCOUNT @.TOP
> SELECT ....
> SET ROWCOUNT 0
> =====================================
> This way I can control the overall performance of all of my queries.
> I just want to make sure that the ROWCOUNT setting does not have an impact
> on any other code running in SQL.
> For example lets say that the above SP was running, and then another start
ed
> in the middle of it's execution. Should I expect to see the ROWCOUNT claus
e
> effect the 2nd query, or does it only effect the code which it's currently
> executing.
> I hope that helps clarify my question.
> Thanks,
> John Rossitter
>
> "Jerry Spivey" wrote:
>

CHECKSUM & CHECKSUM_AGG in T-SQL

Hi,

I recently researched on the CHECKSUM & CHECKSUM_AGG functions in T-Sql and found them really useful. However, I was skeptical that there are chances of these functions returning the same values for non-identical inputs. I just got on to the forums and found more than one unhappy folks writing about their experience with these functions.

I am designing a large database (warehouse) and found these functions tempting to implement for the sake of

using CHECKSUM for

- indexing long character fields

- multiple colums of the same table that would involve in a join and use the new checksum field instead

using CHECKSUM_AGG for

- I bulkcopy flat file soruce data into a character field of a table and to ensure that I am not loading the same file multiple times, I plan to use CHECKSUM_AGG( CHECKSUM( [FlatFileRecord] ) ) and verify that no two loads have the same output.

Can some body suggest if I can trust these methods for my purpose?

Many thanks in advance!!

Thanks,

Harish

You can trust CHECKSUM to be very selective, but most likely you will get collisions from time to time. I would not use only CHECKSUM to "verify that no two loads have the same output", I would add a comprehensive check for rows with the same CHECKSUM.|||

Thanks Kuz for your response.. I found CHECKSUM_AGG very efficient in terms of performance. For a 6 million row table, the result of my expression CHECKSUM_AGG( CHECKSUM( [FlatFileRecord] ) ) executed in just 40 seconds. I intend to store the result in a log and then when I load the next file, the same expression will be evaluated on the new data and compared with the previous values in the log. However, its not yet clear to me if I can use the CHECKSUM for my purposes I listed earlier.

Thanks

|||

Well.. I found one of the comment few months back from Microsoft SQL Team.. here i gave as it is..

"Please don't use CHECKSUM or BINARY_CHECKSUM functions. They are not guaranteed to produce unique values for input. They are simple hash functions used to divide set of values into different ranges (for example to create compact indexes or partition the data). In fact, with the current implementation you can get duplicate checksum values quite easily and there are certain types of input values that will simply produce unexpected results (repeated values, NULLs etc). You could use hashbytes in SQL Server 2005 which can generate MD5 or MD4 hash for example which can avoid collisions but still no guarantee to produce unique value for each input."

Now you have decide which one you have to use...|||

Thanks Sekar... Here is my summary. The CHECKSUM & CHECKSUM_AGG are deterministic(same output always for the same input) but cannot guarantee unique output for each input. Please correct if I am wrong.

|||

Yes.. You got it perfectly..

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADAI believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADAI believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
>> Hi,
>> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
>> checkpoint if no actual processing has taken place since the last
>> transaction was checkpointed?
>> Thanks
>> Chris Wood
>> Alberta Department of Energy
>> CANADA|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
>> Hi,
>> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
>> checkpoint if no actual processing has taken place since the last
>> transaction was checkpointed?
>> Thanks
>> Chris Wood
>> Alberta Department of Energy
>> CANADA

Checkpoints

Hi,
In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
checkpoint if no actual processing has taken place since the last
transaction was checkpointed?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
I believe it does. You can turn on trace flag 3502 to do a test. -T3502
will print a message to errorlog whenever a checkpoint is run in SQL Server.
Yih-Yoon Lee
My blog http://www.mssql-tools.com/blog
E-mail: yihyoon.online@.gmail.com
/* remove .online to send me e-mail */
Chris Wood wrote:
> Hi,
> In SQL2000 will issuing a CHECKPOINT T-SQL statement actually issue a
> checkpoint if no actual processing has taken place since the last
> transaction was checkpointed?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>
|||Thanks Yih-Yoon.
The trace flag shows that checkpoints are written.
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:
|||Yih-Yoon,
What does the (9999999) number represent in the message produced by trace
flag 3502?
We see Ckpt dbid 6 started (80)
Ckpt dbid 6 phase 1 ended (80)
Ckpt 6 Complete
The number 80 comes out a lot of times with this flag set.
Thanks
Chris
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> wrote in message
news:eh1o17IBFHA.2584@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
>I believe it does. You can turn on trace flag 3502 to do a test. -T3502
>will print a message to errorlog whenever a checkpoint is run in SQL
>Server.
> Yih-Yoon Lee
> My blog http://www.mssql-tools.com/blog
> E-mail: yihyoon.online@.gmail.com
> /* remove .online to send me e-mail */
> Chris Wood wrote:

checking sql agent status

Is there any possible way to use T-sql checking sql agent status
thanx for your repliesHi
Assuming that you require to know that the SQLSERVERAGENT process is
running!You could try something like:
CREATE TABLE Running ( Service varchar(100) )
INSERT INTO Running ( Service ) EXEC master..xp_cmdshell 'NET START'
IF EXISTS ( SELECT 1 FROM Running WHERE Service LIKE '%SQLSERVERAGENT' )
PRINT 'Agent is running'
John
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:1EB89D23-BE37-4F3A-98D0-8DACB1874090@.microsoft.com...
> Is there any possible way to use T-sql checking sql agent status?
> thanx for your replies

checking sql agent status

Is there any possible way to use T-sql checking sql agent status?
thanx for your repliesHi
Assuming that you require to know that the SQLSERVERAGENT process is
running!You could try something like:
CREATE TABLE Running ( Service varchar(100) )
INSERT INTO Running ( Service ) EXEC master..xp_cmdshell 'NET START'
IF EXISTS ( SELECT 1 FROM Running WHERE Service LIKE '%SQLSERVERAGENT' )
PRINT 'Agent is running'
John
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:1EB89D23-BE37-4F3A-98D0-8DACB1874090@.microsoft.com...
> Is there any possible way to use T-sql checking sql agent status?
> thanx for your replies

checking sql agent status

Is there any possible way to use T-sql checking sql agent status?
thanx for your replies
Hi
Assuming that you require to know that the SQLSERVERAGENT process is
running!You could try something like:
CREATE TABLE Running ( Service varchar(100) )
INSERT INTO Running ( Service ) EXEC master..xp_cmdshell 'NET START'
IF EXISTS ( SELECT 1 FROM Running WHERE Service LIKE '%SQLSERVERAGENT' )
PRINT 'Agent is running'
John
"I.O" <anonymous@.discussions.microsoft.com> wrote in message
news:1EB89D23-BE37-4F3A-98D0-8DACB1874090@.microsoft.com...
> Is there any possible way to use T-sql checking sql agent status?
> thanx for your replies

Checking Jobs Status through Query

I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.

Thanks in Advance

Jacx

you can use opennrowset or openquery to

query other servers

job information are stored in msdb and you can invoke the

following to query job information

use msdb
select * from sysjobs
select * from sysjobhistory

|||

tually i wanted a code to find the urrent job status of a particular job which i am inerested in. Kindly help me with that.

Thanks

Jacx

|||

use msdb
select * from sysjobs sj join --<change the * to get only the columns you need
sysjobhistory sjh
on sj.job_id=sjh.job_id
where name like 'W%' <modify this for the job name

check the run status

check this link

http://msdn2.microsoft.com/en-gb/library/ms174997.aspx

Checking Job Status using T-SQL

Hi.

How can i check the job status using SQL Query?

The following query may help you...

Select
*
From
msdb..sysjobhistory as sysjobhistory
Join msdb..sysjobs as sysjobs on sysjobhistory.job_id=sysjobhistory.job_id
Where
Name='Your Schd. Package Name'
Order By
Run_Date Desc,
run_time Desc

|||Look up using [sp_help_job] in Books Online.|||

by the way....

What is meant by JOB STATUS ?

|||

Raja, I meant If the job (last execution/historical) executed successfully or not..From the above query..

KangKang, use RUN_Status column to find the status

1=Executed Successfuly

0=Failed with error

When Failed you can find the details from the Message Column

|||

I get too many recrod when i use this method

Select
DISTINCT run_status
From
msdb..sysjobhistory as sysjobhistory
Join msdb..sysjobs as sysjobs on sysjobhistory.job_id=sysjobhistory.job_id
Where
name = 'STR Balance'

I just want to get the current run_stauts and store it in a variable. I have refer to SP_Help_Job but i do not know how can i return the run_Status

|||

actually i just want to return the current status, i tried but it return the new and old execute status.

|||I tryto change the SP_HELP_JOB but its too complicated, Is there any other method so i can insert all things into temp table and select current_status from the temp table.|||

I guess I don't understand why you think sp_help_job is so complicated.

You give it a job name, and it gives you a status. Actually, very simple.

|||

YA actually is very easy.....I should try more before post...:).

|||

The problem with using sp_help_job in a batch is that you can't return the results to a table. This is because sp_help_job itself returns results to a table as part of its processing, and

An INSERT EXEC statement cannot be nested.

|||

I have modify the sp_help_job and the sp_get_composite_job_info to retrun only 1 value.

:)

Checking database size from T-SQL

Which is the best way to check the size of a database from Transact SQL ? (Selecting from Sysfiles gives a totally different result compared to right-clicking a database and selecting Properties. It's the result from the latter than I want to obtain through Transact sQL.)exec sp_spaceused|||might want to throw in the updateusage parameter.|||btw, if you ever want to know exactly what EM or SSMS is doing to get the results it's showing you, just turn on the profiler and start clicking around in EM/SSMS.

EDIT: Really, this will tell you what DMO/SMO is doing, since EM and SSMS use DMO and SMO under the covers.|||just turn on the profiler and start clicking around in EM/SSMS.
And I alway get very sad when I see just how much traffic just one click in EM generates... :eek:|||you think that's bad, try SSMS. SMO is a very chatty api.|||you think that's bad, try SSMS. SMO is a very chatty api.
I'm not sure I want to know ;) The only thing that can make it a little more easier to live with if the uncatchable "refresh"-problem in EM is history in SSMS:

Me: I just created the table you wanted
Developer: <click><click> I don't see it
Me: Did you refresh the table list? You were probably already connected before I created it.
Developer: <click><click><click> I did, but I still don't see it
Me: Did you do a refresh on the instance or on the table list, you must refresh the table list separately
Developer: <click><click><click> Did that but I still don't see it
Me: <sigh> just disconnect and reconnect...
Developer: <click><click><click> Ah, there it is!|||I was just about to try tracking what, in my case,
Mgmt Studio Express, is doing, but sp_spaceused
did the trick!
Thank you!|||fyi, you can also pass a table name to sp_spaceused to get the size of data/indexes in it.