SET commands - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SET commands

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-02-08 : 09:12:15

Hello,

I finally got access to Northwind/pubs.

I would like to know when and why do we use the following set commands. The SQL Server BOL does not say why and when to use these commands.

Thanks in advance!!!
sqlnovice123

Option Default Setting
Set nocount OFF
Set rowcount 0
Set ansi_nulls ON
Set quoted_identifier ON

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 09:30:15

Look them up in BOL.

Just a quick one,

Set nocount off - anytime you want to know how many records were affected by the next statement.
set rowcount 0 - when you want all rows returned after you previously limited rows in this way (suggested that you use top instead).

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 09:32:20

From BOL about ansi_nulls:

The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ value and that are not NULL.

Personally, i'd leave it on as i'd rather use IS NULL or IS NOT NULL..

Kristen
Test

22859 Posts

Posted - 2006-02-09 : 04:00:29

We put

SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON

as the first statement in [pretty much] all our SProcs

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources