Fetch last record in a table

HI ALL,
Suppose i did not use identity for generating sequence in a
table.Also there is no sequence no column or no primary key on a
column. Then how to find last record in a table. Suppose there are 10
rows. How can i fetch 10th rows record.
Define your SQL SELECT statement , and THEN use ORDER BY myCol DESC -
select TOP 1. That's assuming there are 10 records.
If there are more than 10 records , and you are using SQL 2005 , you could
do something like:
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY Col2 DESC)AS RowFROm
myTableWHERE Row = 10
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
|||On Jan 16, 2:50Xpm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched, last
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
>
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.
|||MC
SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
> First you need to define 10th. What does it mean exactly? Last fetched,
> last by some value, last..... Without primary key you basically dont have
> a consistent approach, so some kind of definition is definitely needed
> here.
>
> MC
>
> "mohit" <goenka.mohit@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
|||On Jan 16, 3:22Xpm, mohit <goenka.mo...@.gmail.com> wrote:
> HI ALL,
> X X X Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
In a set there is no such thing as last record.
|||On Jan 16, 3:22Xpm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
>
>
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong
|||On Jan 16, 3:28Xpm, SB <othell...@.yahoo.com> wrote:
> On Jan 16, 3:22Xpm, mohit <goenka.mo...@.gmail.com> wrote:
>
> In a set there is no such thing as last record.
Can you tell what set means. i am asking from table how to fetch last
record i.e last row from a table
|||On Jan 16, 4:45Xpm, mohit <goenka.mo...@.gmail.com> wrote:
> On Jan 16, 3:28Xpm, SB <othell...@.yahoo.com> wrote:
>
>
> Can you tell what set means. i am asking from table how to fetch last
> record i.e last row from a table
A table consists of a set of records. It is not a sequence so that you
have first or last record. A set is an unordered set of records.
|||> Sorry yaar but its not working. When we do order by col_name desc
> the rows gets shuffle due to which answer is coming wrong
How do you know it is not working if there is no data in the row to identify
the order of insertion?
An important relational database concept is that a table is an unordered set
of rows. Rows may be returned in an arbitrary sequence unless you specify
ORDER BY. If you want data returned in the sequence in which rows were
inserted, you'll need an incrementing column like inserted datetime or
identity for the ordering.
Hope this helps.
Dan Guzman
SQL Server MVP
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:d4d2d6c5-c6e0-4612-9f57-13accf2ff8b1@.i7g2000prf.googlegroups.com...
On Jan 16, 3:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
>
>
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong
|||"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
As others have said, w/o a primary key, there's no such thing as a "last
record" defined.
And in fact, without an ORDER BY you can never guarantee the order you'll
return the data in.
SELECT * from FOO may in fact return a different order of results at
different times.
This could be due to what's in the memory cache at the time, if the engine
decides to parallize the query across different CPUs, etc.
Now, MOST LIKELY for 10 rows, a simple "select * from FOO" will return the
data in the order it was inserted but that's absolutely no guarantee this is
true.
You may want to google the definition of a "SET" or "TABLE" within SQL.
They have no inherent order.
So sorry to say, you probably can't get the answer to the question you seek
(at least not the way it's posed.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
sql

Fetch last record in a table

HI ALL,
Suppose i did not use identity for generating sequence in a
table.Also there is no sequence no column or no primary key on a
column. Then how to find last record in a table. Suppose there are 10
rows. How can i fetch 10th rows record.First you need to define 10th. What does it mean exactly? Last fetched, last
by some value, last..... Without primary key you basically dont have a
consistent approach, so some kind of definition is definitely needed here.
MC
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.|||Define your SQL SELECT statement , and THEN use ORDER BY myCol DESC -
select TOP 1. That's assuming there are 10 records.
If there are more than 10 records , and you are using SQL 2005 , you could
do something like:
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY Col2 DESC)AS RowFROm
myTableWHERE Row = 10
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.|||On Jan 16, 2:50=A0pm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched, la=st
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.=
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
> > HI ALL,
> > =A0 =A0 =A0Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.|||MC
SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
> First you need to define 10th. What does it mean exactly? Last fetched,
> last by some value, last..... Without primary key you basically dont have
> a consistent approach, so some kind of definition is definitely needed
> here.
>
> MC
>
> "mohit" <goenka.mohit@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>> HI ALL,
>> Suppose i did not use identity for generating sequence in a
>> table.Also there is no sequence no column or no primary key on a
>> column. Then how to find last record in a table. Suppose there are 10
>> rows. How can i fetch 10th rows record.
>|||On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> HI ALL,
> =A0 =A0 =A0 Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
In a set there is no such thing as last record.|||On Jan 16, 3:22=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
> > First you need to define 10th. What does it mean exactly? Last fetched,
> > last by some value, last..... Without primary key you basically dont ha=ve
> > a consistent approach, so some kind of definition is definitely needed
> > here.
> > MC
> > "mohit" <goenka.mo...@.gmail.com> wrote in message
> >news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> >> HI ALL,
> >> =A0 =A0 =A0Suppose i did not use identity for generating sequence in a
> >> table.Also there is no sequence no column or no primary key on a
> >> column. Then how to find last record in a table. Suppose there are 10
> >> rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong|||On Jan 16, 3:28=A0pm, SB <othell...@.yahoo.com> wrote:
> On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> > HI ALL,
> > =A0 =A0 =A0 Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.
> In a set there is no such thing as last record.
Can you tell what set means. i am asking from table how to fetch last
record i.e last row from a table|||On Jan 16, 4:45=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> On Jan 16, 3:28=A0pm, SB <othell...@.yahoo.com> wrote:
> > On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> > > HI ALL,
> > > =A0 =A0 =A0 Suppose i did not use identity for generating sequence in =a
> > > table.Also there is no sequence no column or no primary key on a
> > > column. Then how to find last record in a table. Suppose there are 10
> > > rows. How can i fetch 10th rows record.
> > In a set there is no such thing as last record.
> Can you tell what set means. i am asking from table how to fetch last
> record i.e last row from a table
A table consists of a set of records. It is not a sequence so that you
have first or last record. A set is an unordered set of records.|||If the 'last' you mean last added then you need a column which will define
sequence of insertions. Something like 'DateAdded' or something like that.
Otherwise, you need to specify how do YOU know which record is 'last' when
you open sample data from table.
MC
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:a53c73e5-4685-4d12-8f94-5e8a1b94b5b8@.f10g2000hsf.googlegroups.com...
On Jan 16, 2:50 pm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched,
> last
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
> > HI ALL,
> > Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.|||> Sorry yaar but its not working. When we do order by col_name desc
> the rows gets shuffle due to which answer is coming wrong
How do you know it is not working if there is no data in the row to identify
the order of insertion?
An important relational database concept is that a table is an unordered set
of rows. Rows may be returned in an arbitrary sequence unless you specify
ORDER BY. If you want data returned in the sequence in which rows were
inserted, you'll need an incrementing column like inserted datetime or
identity for the ordering.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:d4d2d6c5-c6e0-4612-9f57-13accf2ff8b1@.i7g2000prf.googlegroups.com...
On Jan 16, 3:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
> > First you need to define 10th. What does it mean exactly? Last fetched,
> > last by some value, last..... Without primary key you basically dont
> > have
> > a consistent approach, so some kind of definition is definitely needed
> > here.
> > MC
> > "mohit" <goenka.mo...@.gmail.com> wrote in message
> >news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> >> HI ALL,
> >> Suppose i did not use identity for generating sequence in a
> >> table.Also there is no sequence no column or no primary key on a
> >> column. Then how to find last record in a table. Suppose there are 10
> >> rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong|||"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
As others have said, w/o a primary key, there's no such thing as a "last
record" defined.
And in fact, without an ORDER BY you can never guarantee the order you'll
return the data in.
SELECT * from FOO may in fact return a different order of results at
different times.
This could be due to what's in the memory cache at the time, if the engine
decides to parallize the query across different CPUs, etc.
Now, MOST LIKELY for 10 rows, a simple "select * from FOO" will return the
data in the order it was inserted but that's absolutely no guarantee this is
true.
You may want to google the definition of a "SET" or "TABLE" within SQL.
They have no inherent order.
So sorry to say, you probably can't get the answer to the question you seek
(at least not the way it's posed.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>>
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.
If you don't have a column that can tell you what "last" means, then you
could return ANY row and how would you know it wasn't the "last" row?
In SQL Server, a table is an unordered set of rows by definition. If you
say SELECT TOP 1 columns FROM table or SELECT TOP 10 columns FROM table and
you don't include an ORDER BY clause, the optimizer is free to return
whichever rows it wants... and it can change its mind when you run the query
again 5 minutes later, giving you a different set. So, if you want to
identify your "last" row, you will need to have a column that you can use to
tell SQL Server "use this column to determine last."
A

fastest way to insert range/sequence

I'd like to use a stored procedure to insert large amounts of records
into a table. My field A should be filled with a given range of
numbers. I do the following ... but I'm sure there is a better
(faster) way:

select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
while @.start <= 500000
begin
insert into tbl (A, B, C)
values (@.start, 'test1', test2')
set @.start = @.start +1
end

another question is, how to prevent that another user inserts the same
numbers into the field A?

Thanks a lot for any help!
ratu"ratu" <postit@.hispeed.ch> wrote in message
news:e6e93102.0407070830.67d763c5@.posting.google.c om...
> I'd like to use a stored procedure to insert large amounts of records
> into a table. My field A should be filled with a given range of
> numbers. I do the following ... but I'm sure there is a better
> (faster) way:
> select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
> while @.start <= 500000
> begin
> insert into tbl (A, B, C)
> values (@.start, 'test1', test2')
> set @.start = @.start +1
> end
> another question is, how to prevent that another user inserts the same
> numbers into the field A?
> Thanks a lot for any help!
> ratu

One possible solution is an auxiliary table of numbers, or a UDF as
described in this post:

http://groups.google.com/groups?q=i...sftngp13&rnum=1

You could then do something like this:

insert into tbl (A, B, C)
select n, 'test1', 'test2'
from dbo.fn_nums(@.start, 500000)

As for preventing duplicate entries, you can use a primary key or unique
constraint to prevent duplicates, depending on your data model. If
necessary, you can also use a check constraint to ensure that the table will
only accept a certain range of numbers.

Simon|||On 7 Jul 2004 09:30:03 -0700, ratu wrote:

> I'd like to use a stored procedure to insert large amounts of records
> into a table. My field A should be filled with a given range of
> numbers. I do the following ... but I'm sure there is a better
> (faster) way:
> select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
> while @.start <= 500000
> begin
> insert into tbl (A, B, C)
> values (@.start, 'test1', test2')
> set @.start = @.start +1
> end

Here's the DIGITS view-based version:

CREATE VIEW DIGITS (D) AS
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9;

INSERT INTO tbl (A,B,C)
SELECT D3.D * 1000 + D2.D * 100 + D1.D as [ValA],
'test1' AS [ValB], 'test2' AS [ValC])
FROM DIGITS AS [D1], DIGITS AS [D2], DIGITS AS [D3]
WHERE D3.D * 1000 + D2.D * 100 + D1.D between @.LowVal AND @.HiVal

> another question is, how to prevent that another user inserts the same
> numbers into the field A?

Create a unique index on field A. The other user will receive an error.|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:jaaqazxjdyer.1b96bwdbr1qod.dlg@.40tude.net...
> On 7 Jul 2004 09:30:03 -0700, ratu wrote:
> > I'd like to use a stored procedure to insert large amounts of records
> > into a table. My field A should be filled with a given range of
> > numbers. I do the following ... but I'm sure there is a better
> > (faster) way:
> > select @.start = max(A) from tbl where B = 'test1' and C = 'test2'
> > while @.start <= 500000
> > begin
> > insert into tbl (A, B, C)
> > values (@.start, 'test1', test2')
> > set @.start = @.start +1
> > end
> Here's the DIGITS view-based version:
> CREATE VIEW DIGITS (D) AS
> SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9;

If there's no need to eliminate duplicates in the union operation, as here,
use UNION ALL instead. This particular case is trivial but in other
cases the performance improvement can be noticeable.

> INSERT INTO tbl (A,B,C)
> SELECT D3.D * 1000 + D2.D * 100 + D1.D as [ValA],
> 'test1' AS [ValB], 'test2' AS [ValC])
> FROM DIGITS AS [D1], DIGITS AS [D2], DIGITS AS [D3]
> WHERE D3.D * 1000 + D2.D * 100 + D1.D between @.LowVal AND @.HiVal

You're missing the 10s place here.

Of course, one can fill a table with all integers possibly needed and query
for ranges. Alternatively, one can define a view to calculate the range by
applying constraints to each place value in turn, that is, the ones, tens, hundreds,
etc., as opposed to applying a constraint to each final candidate integer. The
former being more of a branch and bound approach while the latter is
generate and test.

CREATE VIEW Digits (d)
AS
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9

-- Return a range [@.lower, @.upper]
CREATE FUNCTION NonnegativeIntegerRange
(@.lower INT, @.upper INT)
RETURNS TABLE
AS
RETURN(
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d + 1000 * Thousands.d +
10000 * TenThousands.d AS n
FROM Digits AS TenThousands
INNER JOIN
Digits AS Thousands
ON TenThousands.d BETWEEN @.lower/10000 AND @.upper/10000 AND
(TenThousands.d <> @.upper/10000 OR
Thousands.d <= (@.upper%10000)/1000) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d >= (@.lower%10000)/1000)
INNER JOIN
Digits AS Hundreds
ON (TenThousands.d <> @.upper/10000 OR
Thousands.d <> (@.upper%10000)/1000 OR
Hundreds.d <= (@.upper%1000)/100) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d <> (@.lower%10000)/1000 OR
Hundreds.d >= (@.lower%1000)/100)
INNER JOIN
Digits AS Tens
ON (TenThousands.d <> @.upper/10000 OR
Thousands.d <> (@.upper%10000)/1000 OR
Hundreds.d <> (@.upper%1000)/100 OR
Tens.d <= (@.upper%100)/10) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d <> (@.lower%10000)/1000 OR
Hundreds.d <> (@.lower%1000)/100 OR
Tens.d >= (@.lower%100)/10)
INNER JOIN
Digits AS Ones
ON (TenThousands.d <> @.upper/10000 OR
Thousands.d <> (@.upper%10000)/1000 OR
Hundreds.d <> (@.upper%1000)/100 OR
Tens.d <> (@.upper%100)/10 OR
Ones.d <= @.upper%10) AND
(TenThousands.d <> @.lower/10000 OR
Thousands.d <> (@.lower%10000)/1000 OR
Hundreds.d <> (@.lower%1000)/100 OR
Tens.d <> (@.lower%100)/10 OR
Ones.d >= @.lower%10)
)

SELECT n
FROM NonnegativeIntegerRange(20000, 20009)
ORDER BY n

n
20000
20001
20002
20003
20004
20005
20006
20007
20008
20009

--
JAG|||On Fri, 09 Jul 2004 03:18:31 GMT, John Gilson wrote:

> If there's no need to eliminate duplicates in the union operation, as here,
> use UNION ALL instead. This particular case is trivial but in other
> cases the performance improvement can be noticeable.

Thanks. I noticed you using UNION ALL before, but never understood it,
because I never looked up the explanation of UNION ALL.

> You're missing the 10s place here.

D'oh!

> Of course, one can fill a table with all integers possibly needed and query
> for ranges. Alternatively, one can define a view to calculate the range by
> applying constraints to each place value in turn, that is, the ones, tens, hundreds,
> etc., as opposed to applying a constraint to each final candidate integer. The
> former being more of a branch and bound approach while the latter is
> generate and test.

Thanks for your clearly expressed improvement of my half-baked ideas. :)

I still have two very active SQL 6.5 servers, so I tend not to think of UDF
solutions.|||"Ross Presser" <rpresser@.imtek.com> wrote in message
news:1x4qrf3kzc0t0$.1wjptvh6fhq50.dlg@.40tude.net.. .
> On Fri, 09 Jul 2004 03:18:31 GMT, John Gilson wrote:
> > If there's no need to eliminate duplicates in the union operation, as here,
> > use UNION ALL instead. This particular case is trivial but in other
> > cases the performance improvement can be noticeable.
> Thanks. I noticed you using UNION ALL before, but never understood it,
> because I never looked up the explanation of UNION ALL.

It's worth knowing when you're taking the union of significant result sets.
It's like knowing when and when not to use DISTINCT. In this case
it obviously isn't an issue other than reinforcing good practice.

> > You're missing the 10s place here.
> D'oh!

It's even easier to do when you go into the millions and beyond.

> > Of course, one can fill a table with all integers possibly needed and query
> > for ranges. Alternatively, one can define a view to calculate the range by
> > applying constraints to each place value in turn, that is, the ones, tens, hundreds,
> > etc., as opposed to applying a constraint to each final candidate integer. The
> > former being more of a branch and bound approach while the latter is
> > generate and test.
> Thanks for your clearly expressed improvement of my half-baked ideas. :)

Your generate-and-test approach is an obvious and completely reasonable
solution. The branch-and-bound approach, run on SQL Server 2000, does
seem, in cursory testing, to be over twice as fast. Though it is more verbose
and perhaps less immediately clear.

> I still have two very active SQL 6.5 servers, so I tend not to think of UDF
> solutions.

The UDF here is just for named packaging. Could've simply provided the
SELECT using variables for the range bounds.

--
JAG

Failure Precedence on Nested Sequence Containers

I have a Sequence containser(named One) and 2 Sequence containers( named two and three) nested inside container One. In Container two and three I have execute SQL tasks to execute Stored Procedure. Then I have send email task linked to my sequence containter One on failure constraint.

Failure contrainst to send email is not working. I want each sequence containters(two and three) to execute SQL tasks and if one fails wait for other to execute and then failure task should execute.

Any help is appreciated.

Amar

Looks like your errors are not bubbling up to container one.

When your package fails, what's the status (color) of Container One? I other words, does it turn red or yellow or green?

And, what's the constraint between two and three? Looks like they run independent of each other.

|||

On package failure status of Container One is failed(red color).

Containers two and three run independent of each other.

Amar

|||

Amar Khaira wrote:

On package failure status of Container One is failed(red color).

Containers two and three run independent of each other.

I created a test package and it has the same behaviour. But I know how to fix this.

Set the FailParentOnFailure property to True on both the child containers and all the tasks inside them.

The problem is the failure is not bubbling up the parent.

Try it and let me know. I have a test package I can send if you need it.

|||

Thanks Ravi..Your solution also works.

I also have a work around..it is now working. I changed ForceExecutionResult to Completion for nested sequence containers(two and three) and also changing value of variable flag on completion or failure.

Then I am checking value of variable flag in Precedence for next step depending on successful completion or failure of nested sequence containers.

Amar S.

Failure Precedence Constraints stopped working in my package

I had a Send email task linked to my Sequence Containers in my package and it was working fine. Everytime the container fails it would send an email to myself.

At some point all Failure constraints stopped working. Failure constraints work if I add brand new tasks, but with the existing tasks, they don't work. The Task which fails, turns red and execution stops. Next failure task is not executed.

I am not sure what triggered it to stop working. I cannot get anything on the log

Any help is appreciated.

I've resolved this issue.

I've added an Onerror Event Handler and changed the failure constaints to Logical Or.

It is working now...

|||

This is not consistent at all...

Next day failure precedences are not working again. For workaround I created onerror event handlers for each container to send an email. This time I got lock errors on the errDescription variable.