serg-52 (10/30/2014)

Alternatively use OPENXML

DECLARE @xmlDocument nvarchar(max)

SET @xmlDocument = N'<table>

<tr>

<td>cell1</td>

<td>cell2</td>

<td>cell3</td>

</tr>

<tr>

...

Tweaking the code a little bit more shaves off approximately 17 percent, here is an all in one code

😎

USE tempdb;

GO

SET NOCOUNT ON;

--===== Create the 100K row test table

...

Quick thought, the dynamic sql should be nvarchar so use NCHAR(39) where it's needed, it is on the other hand not needed for the parameters, use sp_executesql and the execution...

Thanks Alan and Jeff, very good job indeed!

This is really the SSC community at it's best and although I haven't had any time to look properly into this, Alan and...

A quick solution, should get you passed this hurdle

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @TXML XML = '<table>

<tr>

<td>cell1</td>

<td>cell2</td>

<td>cell3</td>

...

Quick suggestion, use a case statement

😎

USE tempdb;

GO

create table dbo.fun_test(

packtype nvarchar(50),

price money)

insert into dbo.fun_test

values ('Single','2'),('Monthly','25.00'),('Monthly','27.00'),('Monthly','23.50'),('Single','2'),('Deposit','1'),('Deposit','2')

select

PackType

,count(*) as Records

,sum(CASE ...

Little clean up in the logic, now it looks pretty good

😎

/********************************************************************

-- Stripping out any non-numerical characters

-- EE 2014-10-28 Inital coding

-- EE ...

Looks like this is coming from sqlmap (sqlmap.org), automated injection tool.

😎

SQL Server's implementation of the window functions does not allow for sharing window specification (over clause specs) between functions.

😎

Here is my attempt from the train journey to work this morning, looks like it will give the while loop a real run for the money.

😎

/********************************************************************

-- Stripping out any...

Nice problem, just what's needed to get the brain started in the morning:-D

😎

/* BASE_DATA brings together everything that is needed, adds

a group identifier for...

Some test results, looks like the computed column method is roughly 4 times faster than the next one

😎

Beginning execution loop

--============================================================================================================

-- Method 1 by sqlslacker

-- ...

Chipping in a little more, another method is a computed column and an index on that column, here is a script adapted to fit Jeff's test harness, append it at...

Quick thought, embed the case statement in a CTE and then do the aggregations on the output.

😎

Quick note, if the ROW_NUMBER function has a satisfying index to work on it is not expensive at all, consider the code below which only does one index scan and...