|
As we know that "Set NoCount ON;" increases the performance in SQL server queries by avoiding any extramessages being sent to client...
We are using this statement invarialbly in all procedures and I have seen it being part of SQL Server default procedure template...
What I couldnt figure out is if we need to use this with all the statements why is it OFF by default???
What are the cases where we shouldn't be using it???
~AHAGeek
|
|
|
|
|
A _sp that returns a result set and you want the count (often for queries and reporting purposes).
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
In SP if we want count..we can still use @@RowCount with this statement on???
~AHAGeek
|
|
|
|
|
Typically I would use @@ROWCOUNT in a stored procedure where there are several transactions that are going to modify records. You can then be certain that every update, delete, insert occurs as expected; esp. in stored procedures where it's all or nothing. If 1 table doesn't get modified as expected, you can then rollback.
IE: You have a SP (with NOCOUNT = ON) that has several table updates for updating general ledger entries for A/R and then write the changes into a history table. If one update fails (by checking @@ROWCOUNT), the entire transaction gets rolled.
Returning a rowcount (meaning NOCOUNT = OFF) is good for a very simple sps that return a result set and the row count can be displayed for information purposes and ensure the query didn't error off.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
I suggest the default is bc it is useful when coding a proc, it is only when you go to production that you want to turn it off.
It would be nice to be able to turn it off as a database/server option.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi, I searched the boards and found how to do this, but I have come up against a problem that I cannot seem to resolve.
This is the code to create the stored procedure: (It was create first but I have been playing with it)
ALTER PROCEDURE [dbo].[sproc_CreateMarriageTable]
-- Add the parameters for the stored procedure here
@Surname varchar(50)
AS
BEGIN
DECLARE @SQLStatement varchar(1500)
SET @SQLStatement = 'CREATE TABLE [' + @Surname + ']
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname +'_FName] DEFAULT (''''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_TName] DEFAULT (''''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_' + @Surname + '_MF] DEFAULT (''M''),
[Year] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_PerMar] DEFAULT (''''),
[BirthDate] [smalldatetime] NULL,
[Volume] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_Vol] DEFAULT (''''),
[PageNo] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_PNo] DEFAULT (''''),
[RegDistrictCode] [int] NOT NULL CONSTRAINT [DF_' + @Surname + '_RegDistCode] DEFAULT ((0)), CONSTRAINT [PK_' +@Surname + ']
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY])
ON [PRIMARY]'
EXEC @SQLStatement
END
This parses and compiles without any errors. Then I tried to execute the procedure:
USE [BMD2005]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[sproc_CreateMarriageTable]
@Surname = N'M_VAISEY'
SELECT 'Return Value' = @return_value
GO
It fails with the message:
Msg 203, Level 16, State 2, Procedure sproc_CreateMarriageTable, Line 29
The name 'CREATE TABLE [M_VAISEY]
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_FName] DEFAULT (''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_SName] DEFAULT (''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_TName] DEFAULT (''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_M_VAISEY_MF] DEFAULT ('M'),
[Year] [smallint] NULL CONSTRAINT [DF_M_VAISEY_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_M_VAISEY_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_M_VAISEY_PerMar] DEFAULT (''),
[BirthDate] [smalldatetime] ' is not a valid identifier.
The declaration of the statement variable is plenty long enough (The text is about 1200 characters) and the place where it stops is about 687 characters.
Is there a problem with NULL? Do I need to enclose that in something?
If anyone can see what the problem is I would be most grateful. 
David
UPDATE: I removed a couple of the filds from the end and got this message when I executed
Msg 203, Level 16, State 2, Procedure sproc_CreateMarriageTableTest, Line 25
The name 'CREATE TABLE [M_VAUGHAN]
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_FName] DEFAULT (''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_SName] DEFAULT (''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_M_VAUGHAN_TName] DEFAULT (''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_M_VAUGHAN_MF] DEFAULT ('M'),
[Year] [smallint] NULL CONSTRAINT [DF_M_VAUGHAN_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_M_VAUGHAN_Qtr] DEFAULT ((1)),
[BirthDate] [smalldatetime] NULL
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTI' is not a valid identifier.
It would seem there is some lenght of string problem. Any ideas?
modified on Friday, October 17, 2008 11:07 AM
|
|
|
|
|
DECLARE @SQLStatement varchar(1024)
DECLARE @SQLStatement2 varchar(1024)
SET @SQLStatement = 'CREATE TABLE [' + @Surname + ']
([AutoID] [int] IDENTITY(1,1) NOT NULL,[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname +'_FName] DEFAULT (''''),
[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_SName] DEFAULT (''''),
[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_TName] DEFAULT (''''),
[MF] [nvarchar](1) NOT NULL CONSTRAINT [DF_' + @Surname + '_MF] DEFAULT (''M''),
[Year] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Year] DEFAULT ((0)),
[Quarter] [smallint] NULL CONSTRAINT [DF_' + @Surname + '_Qtr] DEFAULT ((1)),
[PersonMarried] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname + '_PerMar] DEFAULT (''''), '
SET @SQLStatement2 = '[BirthDate] [smalldatetime] NULL,
[Volume] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_Vol] DEFAULT (''''),
[PageNo] [nvarchar](6) NULL CONSTRAINT [DF_' + @Surname + '_PNo] DEFAULT (''''),
[RegDistrictCode] [int] NOT NULL CONSTRAINT [DF_' + @Surname + '_RegDistCode] DEFAULT ((0)), CONSTRAINT [PK_' +@Surname + ']
PRIMARY KEY CLUSTERED ([AutoID] ASC)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY])
ON [PRIMARY]'
EXEC (@SQLStatement + @SQLStatement2)
|
|
|
|
|
Hi,
It looks like the length is the issue. I guess you might have some tab characters in the code for formatting perhaps, not sure. Anyway, I just fixed a similar problem in a Sybase IQ procedure and just removed all the white space.
So for example...
DECLARE @SQLStatement varchar(1500)
SET @SQLStatement = 'CREATE TABLE [' + @Surname
+ '] ([AutoID] [int] IDENTITY(1,1) NOT NULL,'
+ '[FirstName] [nvarchar](50) NULL CONSTRAINT [DF_'
+ @Surname
+ '_FName] DEFAULT (''''), '
+ '[SecondName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname
+ '_SName] DEFAULT (''''), '
+ '[ThirdName] [nvarchar](50) NULL CONSTRAINT [DF_' + @Surname
... etc
... etc
... etc
+ ' STATISTICS_NORECOMPUTE = OFF, '
+ ' IGNORE_DUP_KEY = OFF, '
+ ' ALLOW_ROW_LOCKS = ON, '
+ ' ALLOW_PAGE_LOCKS = ON)'
+ ' ON [PRIMARY]) '
+ ' ON [PRIMARY]'
EXEC @SQLStatement
I hope that helps.
Good luck  ,
Kevin
|
|
|
|
|
Took me a second or two to work out what you were saying. *david scratches head* Ah! The light dawned - concatenation
Yes of course the wrapping or rather formatting may be adding spurious characters.
Strange thing is that it worked when I split the line into two. Perhaps I just hit the right spot by luck rather than by judgement! 
I will give it a try.
Thanks
David
|
|
|
|
|
Worked a treat. Thanks again.
|
|
|
|
|
I'm missing the 'Open Table' right click menu item in SSMS 2008. I am running SQL Compact 3.5 sp1. Any ideas on how to get the menu option back.
It's getting extremely annoying having to view, modify, insert/delete by using queries.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
I was hoping someone had a fix for this. So annoying. Only with compact DBs.
Any suggestions, ideas, or 'constructive criticism' are always welcome.
|
|
|
|
|
Hi all.., How to find refresh percentage of a database through QUERY?
D.Willington
|
|
|
|
|
What do you mean exactly? Your question makes no sense.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
i have an application to refresh a database. I have to select database, backup file path and all.., it is working well.., while the database in refresh i have to display this much percentage has fininshed and so on.
so i want to get the completed percentage of refresh database.
D.Willington
|
|
|
|
|
Well, while your database is restoring you can't run queries so you'll just have to show them a progress bar or something that runs on a timer.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
can't run the query from master database?
D.Willington
|
|
|
|
|
You can run a query from the master database, but the database you are restoring is being restored, so there is nothing to query - it doesn't do it table by table etc.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ok.., Thank u very much
D.Willington
|
|
|
|
|
Hi,
I have a table (tblregfundprice) with the column name "Unit_Price, Fund_Code, and Date".
The table contains the following data.
Fund_Code.....Unit_Price.....Date
AF............0.1254.........10/17/2008
AF............0.5487.........10/16/2008
AF............0.2114.........10/15/2008
AF............0.8744.........10/14/2008
AF............0.1254.........10/13/2008
AF............0.5487.........10/12/2008
AF............0.2114.........10/11/2008
AF............0.8744.........10/10/2008
AF............0.1254.........10/09/2008
SC............0.5487.........10/14/2008
SC............0.2114.........10/13/2008
SC............0.8744.........10/12/2008
GH............0.1254.........10/11/2008
GH............0.5487.........10/10/2008
GH............0.2114.........10/09/2008
GH............0.8744.........10/08/2008
Now if you notice, the most recent date is 10/17/2008 and SC is having the most date of 10/14/2008. How can i do to create below result table for TOP 10 Fund_Code = "SC"?
Unit_Price.....Date
NULL..........10/17/2008
NULL..........10/16/2008
NULL..........10/15/2008
0.5487.......10/14/2008
0.2114.......10/13/2008
0.8744.......10/12/2008
0.1254.......10/11/2008
NULL..........10/10/2008
NULL..........10/09/2008
NULL..........10/08/2008
I need the NULL value to appear as i need it for my system to capture it... I hope there's a way for this.
Pls help....Many thx.
|
|
|
|
|
try this
SELECT *<br />
FROM (SELECT DISTINCT Date FROM TABLE) D<br />
LEFT JOIN (SELECT * FROM TABLE WHERE Fund = 'SC') SC ON SC.Date = D.Date
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am retreiving a data from a table by self join. I am getting all the clients of a doctor and and group them into pairs. I need to have a unique record for each pair that is pair clientAclientB is the same as pair clientBclientA.How can this be done please, since I am retreiving both records. The sql I am using is written be;low
Select Distinct c.id, c2.id
FROM Client c, Client c2
Where c.doc = c2.doc
And c.id != c2.id
Thanks for your time
|
|
|
|
|
A quick thought (not tested)
Select Distinct c.id, c2.id
FROM Client c, Client c2
Where c.doc = c2.doc
And c.id < c2.id --less than instead of not equal
Eslam Afifi
|
|
|
|
|
Can anyone tell me that how can I store images in database. I am using SQL 2005 and I want to store image in that. I know there is a image datatype but it is not working. Please remember I want to store the image not it's path.
|
|
|
|
|
I have already answered in C# forum
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
