How to skip the records
-
Been there... now I learned the set approach
. Hopefully that's what will get you to 2k 
. -
I think I have an even faster solution than you, Remi:
Declare @Sample as int
Declare @OffSet as int
SET @Sample = 3
SET @OffSet = 1
create table #a(id int identity(1,1), name varchar(100))
insert into #a (name) select name from sysobjects order by name
select name from #a where id % @Sample = @Offset
drop table #a
This way you avoid the join...
-
Many thanks to all of you....
Regards
Rakesh
rakesh -
I always try to avoig temp table... but this one is definitly faster on bigger tables.
-
Well, you actually gave me the idea yourself, in the thread Please give me a replacement for a Cursor, when I asked if you could write a stored procedure that outputs integers 1 through n without using a loop
-
Ya I remember, different task but that one definitly always faster with select cross join (even if you have to drop a column at the end).
-
That is a really nice solution, (by both Jesper and Remi working together). I have always had a problem with the RAND function as the seed will produce the same results time after time... This is great.
I used to work in the environmental field and random selection became very important as well as stable selection over time or other dimensions.
Thanks folks.
I wasn't born stupid - I had to study.
-
If ya want to play outside of the sandbox
I wrote an Extended Stored Procedure (C/C++ coded DLL) that implements a global Char/VarChar variable store. With the help of a UDF I do the following:
-- Create the Test table just to put an ident column
-- on the rows to see how they are being selected.
If Object_Id('Test') is not Null Drop Table Test
Select Identity(Int,1,1)[Id],CompanyName into Test from Northwind.dbo.Customers
Exec master.dbo.mt_SetGlobalVars 'RowNum','1'
Select * from test where dbo.Ident('RowNum',[Id])%3=0
The above select statement where clause will get me every third row from the table...
The UDF:
Use Test
If Object_Id('dbo.Ident') is not Null Drop Function dbo.Ident
Go
Create Function dbo.Ident
(
@VarName VarChar(256),
@Dummy VarChar(256)
)
Returns Int
As Begin
Declare @rc Int,@Value VarChar(256),@i Int
Exec @rc=master.dbo.mt_GetGlobalVars @VarName,@Value Output
Select
@i=Cast(@Value as Int),
@Value=Cast(@i+1 as VarChar)
Exec @rc=master.dbo.mt_SetGlobalVars @VarName,@Value
Return @i
End
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Outside the sandbox is the real term here. It's nice, but overkill, but nice
. - Agreed, but in my case (huge tables) it is REALLY fast.
One person's sand is another person's sugar!
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Here's another plan : Add the identity field directly to the main table to avoid generating it on the fly... You just saved yourself from a full table insert. Now it's just a straight select (still a table scan but it's not avoidable in this case without further strategies in place).
-
OR
Create a numbers table with several mod values( the most common hope fully is not that many
) by each number, pin it and join the that with the identities in the target table. Then you will get seeks 
* Noel -
Adding the number column would be a good idea accept for those pesky DELETEs and INSERTs... The numbers would become fragmented and the selection scheme would not function as expected...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
That's an easy fix
.Declare @id
set @id = 0
Update dbo.table set @id = FakeIdent = @id + 1
Done
. -
Easy, but potentially resource and time intensive (for large tables)
Might as well migrate to a differnt database so you can use the internally maintained RowIds...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply