A Range Query Optimization

Hi,
Need help in optimizing a query in SQL Server.
Following is the problem statement.
There are two tables;
1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
this is not the primary key. The table can have billions of records;
in test environment, we are having 3,000,000 records
2nd table (t2) has two columns a from_Range and to_Range, both
char(8). this table has lesser number of records, in thousands.
Clustered index is on the primary key.
However there is no relation whatsoever between the KEY and the
from/to range.
We need to find matching records where Key is found between the from
and to range :
select t1.id, t2.id from t1, t2
where t1.KEY between t2.from_range and t2.to_range
( The ids form part of primary keys in both tables. )
The plan shows a loop, with t1 using clustered index of KEY and t2
using clustered index of the primary key.
This query is taking around 14 seconds on SQL server 2000 in win2kpro
with P4 and 512 MB RAM.
Is there any way this can be reducd to a subsecond performance ? This
query forms the core of most of the processing, and any reduction here
will have recursive effect all over.
Thanks in advance,
roy.
aroy (anindya.roy@.rave-tech.com) writes:
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
> However there is no relation whatsoever between the KEY and the
> from/to range.
> We need to find matching records where Key is found between the from
> and to range :
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range
In general, it it best to post the CREATE TABLE and CREATE INDEX statements
for the table, as the narrative easily can be misunderstood.
But it sounds like a problem that Mischa Sandberg ran into recently,
and you can review that thread starting on
http://groups.google.com/groups?hl=s...396%40edtnps84
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||On 29 Jul 2004 00:43:27 -0700, aroy wrote:
(snip)
See my reply in comp.databases.ms-sqlserver.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Funny enough, but this is just what I've been working on,
for lookup of about 50M rows in a table of 10M ranges.
(discussion in another thread, in m.p.s.programming).
(As an aside, "KEY" is a lousy column name; you'll be hearing from J.C.)
Given:
T1(id INT, "Key" CHAR(8), ...)
T2(id INT, from_Range CHAR(8), to_Range CHAR(8))
Gert-Jan Strik came up with the single-query answer
(paraphrased from the query framed for my problem)
SELECT T1.id, T2.id
FROM T1
JOIN T2
ON T2.from_Range= (
SELECT MAX(from_Range) FROM T2
WHERE T1.Key BETWEEN T2 .from_Range AND T2 .to_Range
)
"aroy" <anindya.roy@.rave-tech.com> wrote in message
news:be87bcc0.0407282343.1bbda35f@.posting.google.c om...
> Hi,
> Need help in optimizing a query in SQL Server.
> Following is the problem statement.
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
> However there is no relation whatsoever between the KEY and the
> from/to range.
> We need to find matching records where Key is found between the from
> and to range :
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range
> ( The ids form part of primary keys in both tables. )
> The plan shows a loop, with t1 using clustered index of KEY and t2
> using clustered index of the primary key.
> This query is taking around 14 seconds on SQL server 2000 in win2kpro
> with P4 and 512 MB RAM.
> Is there any way this can be reducd to a subsecond performance ? This
> query forms the core of most of the processing, and any reduction here
> will have recursive effect all over.
> Thanks in advance,
> roy.

A Range Query Optimization

Hi,
Need help in optimizing a query in SQL Server.
Following is the problem statement.
There are two tables;
1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
this is not the primary key. The table can have billions of records;
in test environment, we are having 3,000,000 records
2nd table (t2) has two columns a from_Range and to_Range, both
char(8). this table has lesser number of records, in thousands.
Clustered index is on the primary key.
However there is no relation whatsoever between the KEY and the
from/to range.
We need to find matching records where Key is found between the from
and to range :
select t1.id, t2.id from t1, t2
where t1.KEY between t2.from_range and t2.to_range
( The ids form part of primary keys in both tables. )
The plan shows a loop, with t1 using clustered index of KEY and t2
using clustered index of the primary key.
This query is taking around 14 seconds on SQL server 2000 in win2kpro
with P4 and 512 MB RAM.
Is there any way this can be reducd to a subsecond performance ? This
query forms the core of most of the processing, and any reduction here
will have recursive effect all over.
Thanks in advance,
roy.aroy (anindya.roy@.rave-tech.com) writes:
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
> However there is no relation whatsoever between the KEY and the
> from/to range.
> We need to find matching records where Key is found between the from
> and to range :
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range
In general, it it best to post the CREATE TABLE and CREATE INDEX statements
for the table, as the narrative easily can be misunderstood.
But it sounds like a problem that Mischa Sandberg ran into recently,
and you can review that thread starting on
dtnps84" target="_blank">http://groups.google.com/groups?hl=...
dtnps84
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 29 Jul 2004 00:43:27 -0700, aroy wrote:
(snip)
See my reply in comp.databases.ms-sqlserver.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Funny enough, but this is just what I've been working on,
for lookup of about 50M rows in a table of 10M ranges.
(discussion in another thread, in m.p.s.programming).
(As an aside, "KEY" is a lousy column name; you'll be hearing from J.C.)
Given:
T1(id INT, "Key" CHAR(8), ...)
T2(id INT, from_Range CHAR(8), to_Range CHAR(8))
Gert-Jan Strik came up with the single-query answer
(paraphrased from the query framed for my problem)
SELECT T1.id, T2.id
FROM T1
JOIN T2
ON T2.from_Range= (
SELECT MAX(from_Range) FROM T2
WHERE T1.Key BETWEEN T2 .from_Range AND T2 .to_Range
)
"aroy" <anindya.roy@.rave-tech.com> wrote in message
news:be87bcc0.0407282343.1bbda35f@.posting.google.com...
> Hi,
> Need help in optimizing a query in SQL Server.
> Following is the problem statement.
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
> However there is no relation whatsoever between the KEY and the
> from/to range.
> We need to find matching records where Key is found between the from
> and to range :
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range
> ( The ids form part of primary keys in both tables. )
> The plan shows a loop, with t1 using clustered index of KEY and t2
> using clustered index of the primary key.
> This query is taking around 14 seconds on SQL server 2000 in win2kpro
> with P4 and 512 MB RAM.
> Is there any way this can be reducd to a subsecond performance ? This
> query forms the core of most of the processing, and any reduction here
> will have recursive effect all over.
> Thanks in advance,
> roy.

A Range Query Optimization

Hi,
Need help in optimizing a query in SQL Server.
Following is the problem statement.
There are two tables;
1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
this is not the primary key. The table can have billions of records;
in test environment, we are having 3,000,000 records
2nd table (t2) has two columns a from_Range and to_Range, both
char(8). this table has lesser number of records, in thousands.
Clustered index is on the primary key.
However there is no relation whatsoever between the KEY and the
from/to range.
We need to find matching records where Key is found between the from
and to range :
select t1.id, t2.id from t1, t2
where t1.KEY between t2.from_range and t2.to_range
( The ids form part of primary keys in both tables. )
The plan shows a loop, with t1 using clustered index of KEY and t2
using clustered index of the primary key.
This query is taking around 14 seconds on SQL server 2000 in win2kpro
with P4 and 512 MB RAM.
Is there any way this can be reducd to a subsecond performance ? This
query forms the core of most of the processing, and any reduction here
will have recursive effect all over.
Thanks in advance,
roy.aroy (anindya.roy@.rave-tech.com) writes:
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
> However there is no relation whatsoever between the KEY and the
> from/to range.
> We need to find matching records where Key is found between the from
> and to range :
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range
In general, it it best to post the CREATE TABLE and CREATE INDEX statements
for the table, as the narrative easily can be misunderstood.
But it sounds like a problem that Mischa Sandberg ran into recently,
and you can review that thread starting on
http://groups.google.com/groups?hl=sv&lr=&ie=UTF-8&selm=ZeKIc.27357%24Rf.25396%40edtnps84
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||On 29 Jul 2004 00:43:27 -0700, aroy wrote:
(snip)
See my reply in comp.databases.ms-sqlserver.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Funny enough, but this is just what I've been working on,
for lookup of about 50M rows in a table of 10M ranges.
(discussion in another thread, in m.p.s.programming).
(As an aside, "KEY" is a lousy column name; you'll be hearing from J.C.)
Given:
T1(id INT, "Key" CHAR(8), ...)
T2(id INT, from_Range CHAR(8), to_Range CHAR(8))
Gert-Jan Strik came up with the single-query answer
(paraphrased from the query framed for my problem)
SELECT T1.id, T2.id
FROM T1
JOIN T2
ON T2.from_Range= (
SELECT MAX(from_Range) FROM T2
WHERE T1.Key BETWEEN T2 .from_Range AND T2 .to_Range
)
"aroy" <anindya.roy@.rave-tech.com> wrote in message
news:be87bcc0.0407282343.1bbda35f@.posting.google.com...
> Hi,
> Need help in optimizing a query in SQL Server.
> Following is the problem statement.
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
> However there is no relation whatsoever between the KEY and the
> from/to range.
> We need to find matching records where Key is found between the from
> and to range :
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range
> ( The ids form part of primary keys in both tables. )
> The plan shows a loop, with t1 using clustered index of KEY and t2
> using clustered index of the primary key.
> This query is taking around 14 seconds on SQL server 2000 in win2kpro
> with P4 and 512 MB RAM.
> Is there any way this can be reducd to a subsecond performance ? This
> query forms the core of most of the processing, and any reduction here
> will have recursive effect all over.
> Thanks in advance,
> roy.

A Range Query Optimization

Hi,

Need help in optimizing a query in SQL Server.
Following is the problem statement.

There are two tables;
1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
this is not the primary key. The table can have billions of records;
in test environment, we are having 3,000,000 records

2nd table (t2) has two columns a from_Range and to_Range, both
char(8). this table has lesser number of records, in thousands.
Clustered index is on the primary key.

However there is no relation whatsoever between the KEY and the
from/to range.

We need to find matching records where Key is found between the from
and to range :

select t1.id, t2.id from t1, t2
where t1.KEY between t2.from_range and t2.to_range

( The ids form part of primary keys in both tables. )

The plan shows a loop, with t1 using clustered index of KEY and t2
using clustered index of the primary key.

This query is taking around 14 seconds on SQL server 2000 in win2kpro
with P4 and 512 MB RAM.

Is there any way this can be reducd to a subsecond performance ? This
query forms the core of most of the processing, and any reduction here
will have recursive effect all over.

Thanks in advance,
roy.aroy (anindya.roy@.rave-tech.com) writes:
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
> However there is no relation whatsoever between the KEY and the
> from/to range.
> We need to find matching records where Key is found between the from
> and to range :
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range

In general, it it best to post the CREATE TABLE and CREATE INDEX statements
for the table, as the narrative easily can be misunderstood.

But it sounds like a problem that Mischa Sandberg ran into recently,
and you can review that thread starting on
http://groups.google.com/groups?hl=...5396%40edtnps84

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 29 Jul 2004 00:43:27 -0700, aroy wrote:

>Hi,
>Need help in optimizing a query in SQL Server.
>Following is the problem statement.
>There are two tables;
>1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
>this is not the primary key. The table can have billions of records;
>in test environment, we are having 3,000,000 records
>2nd table (t2) has two columns a from_Range and to_Range, both
>char(8). this table has lesser number of records, in thousands.
>Clustered index is on the primary key.
>However there is no relation whatsoever between the KEY and the
>from/to range.
>We need to find matching records where Key is found between the from
>and to range :
>select t1.id, t2.id from t1, t2
>where t1.KEY between t2.from_range and t2.to_range
>( The ids form part of primary keys in both tables. )
>The plan shows a loop, with t1 using clustered index of KEY and t2
>using clustered index of the primary key.
>This query is taking around 14 seconds on SQL server 2000 in win2kpro
>with P4 and 512 MB RAM.
>Is there any way this can be reducd to a subsecond performance ? This
>query forms the core of most of the processing, and any reduction here
>will have recursive effect all over.
>Thanks in advance,
>roy.

Hi Roy,

The URL Erland gives has lots of potentially useful info that might help
you. But you might also try if it helps to define an index on (from_range,
to_range) in the second table (t2). Try it with both a clustered and a
non-clustered index - I expect a performance gain in both cases, but it's
hard to predict which will be the fastest.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Funny enough, but this is just what I've been working on,
for lookup of about 50M rows in a table of 10M ranges.
(discussion in another thread, in m.p.s.programming).

(As an aside, "KEY" is a lousy column name; you'll be hearing from J.C.)

Given:
T1(id INT, "Key" CHAR(8), ...)
T2(id INT, from_Range CHAR(8), to_Range CHAR(8))

Gert-Jan Strik came up with the single-query answer
(paraphrased from the query framed for my problem)

SELECT T1.id, T2.id
FROM T1
JOIN T2
ON T2.from_Range= (
SELECT MAX(from_Range) FROM T2
WHERE T1.Key BETWEEN T2 .from_Range AND T2 .to_Range
)

"aroy" <anindya.roy@.rave-tech.com> wrote in message
news:be87bcc0.0407282343.1bbda35f@.posting.google.c om...
> Hi,
> Need help in optimizing a query in SQL Server.
> Following is the problem statement.
> There are two tables;
> 1st table (t1) has a KEY ( char(8) ) column, with a clustered index.
> this is not the primary key. The table can have billions of records;
> in test environment, we are having 3,000,000 records
> 2nd table (t2) has two columns a from_Range and to_Range, both
> char(8). this table has lesser number of records, in thousands.
> Clustered index is on the primary key.
> However there is no relation whatsoever between the KEY and the
> from/to range.
> We need to find matching records where Key is found between the from
> and to range :
> select t1.id, t2.id from t1, t2
> where t1.KEY between t2.from_range and t2.to_range
> ( The ids form part of primary keys in both tables. )
> The plan shows a loop, with t1 using clustered index of KEY and t2
> using clustered index of the primary key.
> This query is taking around 14 seconds on SQL server 2000 in win2kpro
> with P4 and 512 MB RAM.
> Is there any way this can be reducd to a subsecond performance ? This
> query forms the core of most of the processing, and any reduction here
> will have recursive effect all over.
> Thanks in advance,
> roy.

A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
TIA,
ChrisR
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR
|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go

A new @@Error question.

Here is what I really need to accomplish:
CREATE TABLE [dbo].[Table1] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2] (
[c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare @.Error1 int
declare @.Error2 int
begin transaction
drop table Table1
select @.Error1 = @.@.Error
exec sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0
commit transaction
else
rollback transaction
Im not understanding why Table1 still gets dropped? It seems to me like
@.@.Error2 should not be 0, therefore a Rollback should occur? Why is @.@.Error2
not 0?
--
TIA,
ChrisR> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
@.@.ERROR returns the error number of the last Transact-SQL statement
executed. You need to also check the proc return code to determine whether
or not the rename succeeded:
declare @.Error1 int
declare @.Error2 int
declare @.ReturnCode int
begin transaction
drop table Table1x
select @.Error1 = @.@.Error
exec @.ReturnCode = sp_rename 'Table3','Table1'
select @.Error2 = @.@.Error
if @.Error1 = 0
if @.Error2 = 0 AND @.ReturnCode = 0
commit transaction
else
rollback transaction
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1A3F5C0A-9EC9-492D-9205-04031D36BFAA@.microsoft.com...
> Here is what I really need to accomplish:
> CREATE TABLE [dbo].[Table1] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table2] (
> [c1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare @.Error1 int
> declare @.Error2 int
> begin transaction
> drop table Table1
> select @.Error1 = @.@.Error
> exec sp_rename 'Table3','Table1'
> select @.Error2 = @.@.Error
> if @.Error1 = 0
> if @.Error2 = 0
> commit transaction
> else
> rollback transaction
> Im not understanding why Table1 still gets dropped? It seems to me like
> @.@.Error2 should not be 0, therefore a Rollback should occur? Why is
> @.@.Error2
> not 0?
> --
> TIA,
> ChrisR|||Look at the procedure code. Then look at the documentation regarding the
raiserror function. Then look at the documentation for @.@.error. The
following demonstrates why this happens. As Dan suggested, you also need
to refer to the documentation of the procedure to know how to use it.
set nocount on
go
create procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror with following statement', @.sev, 20)
return 1
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
alter procedure xx_test @.sev int
as
RAISERROR ('Testing raiserror, no following statement', @.sev, 20)
go
exec xx_test -1
select @.@.error
exec xx_test 10
select @.@.error
exec xx_test 11
select @.@.error
go
drop procedure xx_test
go