dwain.c (8/8/2012)

---

Actually, I perused the link you provided but couldn't think about how to use ROLLUP that way.

The performance test result is interesting.

Try mine with:

OPTION (MAXDOP 2)

And watch the CPU...

Performance test as promised: -

SET NOCOUNT ON;

SELECT TOP 1000000 Student_ID,

Sex = CASE WHEN sex = 1 THEN 'M' ELSE 'F' END,

RaceCode = CASE race WHEN 0 THEN 'Other' WHEN...

dwain.c (8/8/2012)

---

Thanks Lynn! Apparently if I could read, I would've come up with this version instead:

;WITH Unpivoted AS (

SELECT SchoolCode, RaceCode, Code, Value

...

Quick performance test: -

SET NOCOUNT ON;

IF object_id('tempdb..#sampleData') IS NOT NULL

BEGIN

DROP TABLE #sampleData;

END;

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS [Sno],

CAST((ABS(CHECKSUM(NEWID())) % 20000)...

My version: -

--Create sample data

SELECT [Sno], [Column]

INTO #sampleData

FROM (SELECT [Sno] = 1, [Column] = '01112300'

UNION ALL SELECT 2, '00100'

...

SomewhereSomehow (8/6/2012)

---

Cadavre,

Thx for the testing scripts! Especially pointing a bug with replace function (closed as by design).

I have quite similar results

========== SomewhereSomehow ===========================================

SQL Server Execution Times:

CPU...

Celko forgot to account for the REPLACE "bug".

Change...

franck.maton (8/6/2012)

---

I asked my boss if I could get rid of the milliseconds and it seems I can. So If I write the query like this:

SELECT count(1)

...

franck.maton (8/6/2012)

---

Thanks for fast reply 🙂

You are guessing well, the first query doesn't work, the second either by the way :/

The third one tells me that approximatively all my records...

I'm guessing that when you run this: -

SELECT CONVERT(datetime,CounterDateTime)

FROM [MSSQL_PerfmonCollector].[dbo].[CounterData];

You have the same error?

If so, you have some bad data in there, which is why you should always be using...

shahsn11 (8/3/2012)

---

I really appreciate you for your answer thanks. And the output was the same as i was thinking.

Since i am a newbie , i am having some problem while...

Is this an intellectual challenge? Because otherwise, use reverse().

I guess you could do something like this...

Very cool. Will have to dig into this and see whether or not it's something that is viable for use in my environment.

laurie-789651 (8/1/2012)

---

How about this:

IF OBJECT_ID('dbo.Sales1') IS NOT NULL

DROP TABLE dbo.Sales1;

CREATE TABLE dbo.sales1

(

Sales_saleID int,

Sales_customerID int,

Sales_status varchar(10)

);

INSERT INTO dbo.sales1 VALUES ( 1, 1, 'Pending' );

INSERT INTO dbo.sales1 VALUES ( 2, 1, 'Completed'...

Learn to alias your tables.

SELECT *

FROM #temp1

WHERE test1 IN (

SELECT test1 --refers to #temp1

FROM #temp2

);

SELECT *

FROM #temp1

WHERE test1 IN (

SELECT a.test1 --refers to #temp2 and will error

FROM #temp2 a

);

What you...