Statistics IO for

"Staging algorithm"

Table '#2180FB33'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#Sample_____________________________________________________________________________________________________________00000000001C'....

Here is a fast algorithm. Please notice that NO INDEXES AT ALL is present.

SET NOCOUNT ON

-- Create 100000 sample records

CREATE TABLE#Sample

(

Model CHAR(20) NOT NULL,

Metric1 INT,

Metric2 INT

)

INSERT#Sample

(

Model,

Metric1,

Metric2

)

SELECTLEFT(REPLACE(CAST(NEWID() AS CHAR(36)), '-', ''),...

Here is another approach for Top N algorithm

-- Prepare sample data

DECLARE @Sample TABLE (SalesType VARCHAR(6), SalesPrice INT)

INSERT@Sample

SELECT'cash', 5 UNION ALL

SELECT'cash', 3 UNION ALL

SELECT'cash', 2 UNION ALL

SELECT'cash', 1 UNION ALL

SELECT'credit', 9...

This will give you the result you seek.

set nocount on

declare @z table ( model varchar(20), metric1 int, metric2 int )

insert into @z values ('honda accord', 1540, 200 )

insert into @z...

Thank you for the feedback.

From the part of

-- Stage the CTE

and down, this approach averages

at 88 milliseconds for 100,000 records.

at 131 milliseconds for 1,000,000 records.

Fast enough? 😉

-- Create sample...

Try this

SELECTTOP 4

t1.A1,

t1.A2,

t1.A3

FROMTest AS t1

WHEREt1.A1 = (SELECT TOP 1 t2.A1 FROM Test AS t2 WHERE t2.A2 = t1.A2 ORDER BY NEWID())

ORDER BYNEWID()

Oh man.

Another cross-posting...

SELECTTOP 4

t1.A1,

t1.A2,

t1.A3

FROMTest AS t1

WHEREt1.A1 = (SELECT TOP 1 t2.A1 FROM Test AS t2 WHERE t2.A2 = t1.A2 ORDER BY NEWID())

ORDER BYNEWID()

Here is one way to skin this cat

SELECTTOP 4

r.A1,

r.A2,

r.A3

FROM(

SELECTA1,

A2,

A3,

ROW_NUMBER() OVER (PARTITION BY A2 ORDER BY NEWID()) AS RecID

FROMTest

) AS r

WHEREr.RecID = 1

ORDER BYNEWID()

Are you using one of the reserved characters out of place?

@ :

Great example but you can enhance this further

;WITH MyCTE (SalesOrderID, TotalRank, TotalDue)

AS (

SELECTSalesOrderID,

ROW_NUMBER() OVER (ORDER BY TotalDue DESC),

TotalDue

FROMSales.SalesOrderHeader

)

SELECTTOP 5

SalesOrderID,

TotalRank,

TotalDue

FROMMyCTE

WHERETotalRank <= 5

OR SalesOrderID = 879967268

ORDER BYTotalRank

I agree that the getdate() between single selects almost always are unique, but however you can't rely on that to 100%.

The getdate() function works very much like RAND(). It seeds...

Install Microsoft SQL Server 2005 or Microsoft SQL Server 2008 instead.

You do not need the EXEC thingy for each and one statement.

INSERTMyTable

SELECT'C2100',

'LG',

'TV'

UPDATEMyTable

SETCol1 = 'C2100',

Col2 = 'LG',

Col3 = 'TV'

WHERESomeOtherCol = SomeValue -- To only update the record(s) satisfying the filter.

stricknyn (2/1/2008)

---

You can set the default propert to (getdate()) which will timestamp this field as soon as a new record touches the table. That way you wont have to...