First the DDL

USE tempdb;

GO

CREATE TABLE dbo.TestConstraint

(

id INT NOT NULL

,ProductID int NOT NULL

,Datecreated DATETIME NULL

,DateExpired DATETIME NULL

);

GO

ALTER...

This query uses UNPIVOT in a CTE. A ROW_NUMBER function assigns a number for each header/value combination, which is then used to set the output column in the latter part...

mickyT (5/14/2014)

---

Luis Cazares (5/14/2014)

---

Until you find that Gordon Abraham and Abraham Gordon (or any other combination of...

I'll do it as well, don't hold your breath:Whistling:...

Paul White (5/14/2014)

---

Is everyone running Jeff's original/updated test rig from the article to get these results?

If so, you might not be testing what you think you are. There is a...

For fun:-P, it always looks simple until one starts to meddle with it

😎

with Names as

(

select 'Mike Davis' as FullName union all

...

emilacosta (5/14/2014)

---

Hi,

Thank you for your answer. 🙂

My main question is how to get the path of the image file from the table where it is stored and load the image...

OPENROWSET expects string literals, you can do this in dynamic sql.

😎

DECLARE @JPGPATH NVARCHAR(MAX) = N'''C:\temp\IMG00001.jpg''';

DECLARE @JPGBIN VARBINARY(MAX) = 0x00;

DECLARE @SQL_STR NVARCHAR(MAX) = REPLACE(N'SELECT * FROM OPENROWSET(BULK {{@JPGPATH}}, SINGLE_BLOB)...

Good question, thanks!

😎

Looks like the performance improvements have more to do with the platform (2014). I have been looking at results from the same (or identical) hardware (E6220, i5, 256GB SSD, 8Gb...

Brilliant:-)

Ran a short disco version of the test on 2014 Developers edition, also added a Lead version which came in second!

SplitterName ...

If the table exist, it could be in another schema. Change the code to (schema_name).[events] if you find it.

😎

I'll be there Friday/Saturday

look out for the one red shoe

😎

Few more numbers (500K rows), queries in the same order as before.

Take note of the high CPU cost of the LEAD function!

😎

Heap, no index,

[font="Courier New"]

(100000 row(s) affected)

Table 'TBL_POLICYHISTORY'. Scan...

This should be pretty close

😎

SELECT distinct CONVERT(DATETIME,arc_endTime,120) AS [Event Time],

arc_destinationHostName AS [Host Name],

arc_destinationUserName AS [User Name],

arc_sourceHostName AS [From Host],

arc_sourceAddress AS [From IP Address],

arc_deviceCustomString2 AS [Database],

arc_deviceCustomString1...