Some Important, Interesting and useful Options and set Operators..

There would be times, we need to format the output in a specific way and ignore or disable the default output format/parameters. Here some very interesting Items.

1.      To Disable the Column/Row headings of the Query Result:

use -b option with the isql.

2.      To Disable the Row count of the Query Result:

set nocount on.

suppress the lines reporting the number of rows affected to generate output with no extra messages using the set nocount on option.

3.      To see the Query Plan with out executing the Stored procedure.

set fmtonly {on | off}

It captures plans in stored procedures without actually executing them.

4.      How to disable return status when executing stored procedure or a T-SQL Query?

set proc_return_status on|off

controls sending of a return status TDS token back to the client. set proc_return_status off suppresses sending the return status token to the client, and isql client does not display the (return status = 0) message. The default for this parameter is on.

5.      How to avoid Arithmetic Overflow errors causes the process termination:

set arithabort [arith_overflow | numeric_truncation] {on | off}

This option determines how Adaptive Server behaves when an arithmetic error occurs. The two arithabort options  arith_overflow and numeric_truncation, handle different types of arithmetic errors. You can set each option independently or set both options with a single set arithabort on or set arithabort off statement.

·        arithabort arith_overflow – specifies the Adaptive Server behavior following a divide-by-zero error, range overflow during an explicit or implicit datatype converson, or a domain error. This type of error is serious. The default setting,arithabort arith_overflow on, rolls back the entire transaction in which the error occurs. If the error occurs in a batch that does not contain a transaction, arithabort arith_overflow on does not roll back earlier commands in the batch; however, Adaptive Server does not execute any statements in the batch that follow the error-generating statement.

If you set arithabort arith_overflow off, Adaptive Server aborts the statement that causes the error, but continues to process other statements in the transaction or batch.

·        arithabort numeric_truncation – specifies the Adaptive Server behavior following a loss of scale by an exact numeric type during an implicit datatype conversion. (When an explicit conversion results in a loss of scale, the results are truncated without warning.) The default setting, arithabort numeric_truncation on, aborts the statement that causes the error, but Adaptive Server continues to process other statements in the transaction or batch. If you setarithabort numeric_truncation off, Adaptive Server truncates the query results and continues processing.

6.      To Handle divide-by-zero errors:

set arithignore [arith_overflow] {on | off}

This option determines whether Adaptive Server displays a message after a divide-by-zero error or a loss of precision. By default, thearithignore option is set to off. This causes Adaptive Server to display a warning message after any query that results in numeric overflow. To have Adaptive Server ignore overflow errors, use set arithignore on. You can omit the optionalarith_overflow keyword without any effect.

7.      How to insert/update identity values of a table:

set identity_insert TEST on

go

insert TEST (syb_identity)

values (21)

go

set identity_insert TEST off

go

set identity_update TEST on

go

update TEST set c2 = 10 where c1 =1

select * from TEST

c1              c2

--------     -------

1                 10

set identity_update TEST off

8.      How to update systabstats w/o affecting sysstatistics:

update table statistics

update table statistics updates statistics that are stored in systabstats table, such as rowcount, cluster ratios, and so on. update table statistics does not affect column statistics stored in sysstatistics.

9.      Differences among update all statistics,  update index statistics and update statistics:

update all statistics	update index statistics	update statistics
update all statistics updates all the statistics information for a given table. update all statistics regenerates and update the table statistics stored in systabstats for each data and index partition of the table.	update index statistics, when used with a table name and an index name, updates statistics for all columns in the specified index. If update index statistics is used with just a table name, it updates statistics for all columns in all indexes of the table. Specifying the name of an unindexed column or the nonleading column of an index generates statistics for that column without creating an index.	update statistics, when used with a table name and an index name, updates statistics for the leading column of an index. If update statistics is used with just a table name, it updates statistics for the leading columns of all indexes on the table. When you create a nonclustered index on a table that contains data, update statistics is automatically run for the new index. When you create a clustered index on a table that contains data, update statistics is automatically run for all indexes.