August 15, 2012 at 12:14 pm
CELKO (8/15/2012)
Probably to encourage quicker adoption of new technology. ;-)[/quote]
In part, but I write code in comments that can be used later. I usually have 2-3 versions of the query and some of them use features in the next release. (some of them are just different, so you can test them when you get an upgrade).
Right now, I have a grateful client who is busy un-commenting
"foo_date DATETIME NOT NULL
CHECK(<<foo_date is always 00:00:00>>). "
-- foo_date DATE NOT NULL. -- fix DML to match
It took them a weekend to set up a 2012 schema and move their data.
--[/quote]
Okay, but the solution you posted above is still a total fail as it is a straight SQL Server 2012 solution. Nothing in it will work in SQL Server 2008.
August 16, 2012 at 1:57 am
--== Now for a solution ==--
SELECT a.ID, a.DEVICE, a.VALUE
FROM (SELECT ID, DEVICE, VALUE,
ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos
FROM #yourSampleTable) a
LEFT OUTER JOIN (SELECT ID, DEVICE, VALUE,
ROW_NUMBER() OVER(PARTITION BY DEVICE ORDER BY ID) AS pos
FROM #yourSampleTable) b ON a.DEVICE = b.DEVICE AND a.pos = b.pos+1
WHERE a.VALUE-b.VALUE > 20;[/code]
Thank you for the posts. Enough information in this script to allow me to construct a solution adding a few joins (ps sorry about that lack of schema layout in original post). Tested on R&D successfully. Production environment will take time to return.
SELECT a.ID, a.DEVICEid, a.value, a.sentdate, a.customerid
FROM
(SELECT ID, LOC.DEVICEid, value, SENTDATE, DEVICEtbl.CUSTOMERID, ROW_NUMBER() OVER(PARTITION BY LOC.DEVICEid ORDER BY ID) AS pos FROM Loc with (NOLOCK)
INNER JOIN Devicetbl with (NOLOCK) ON Devicetbl.Deviceid = Loc.Deviceid
INNER JOIN Customer with (NOLOCK) ON Customer.CustomerID = Device.CustomerID
) a
LEFT OUTER JOIN
(SELECT ID, LOC.DEVICEid, value, sentdate, DEVICE.CUSTOMERID, ROW_NUMBER() OVER(PARTITION BY LOC.DEVICEid ORDER BY ID) AS pos FROM loc with (NOLOCK)
INNER JOIN Devicetbl with (NOLOCK)ON Devicetbl.Deviceid = Loc.Deviceid
INNER JOIN Customer with (NOLOCK) ON Customer.CustomerID = Device.CustomerID
) b ON a.DEVICEid = b.DEVICEid AND a.pos = b.pos+1
WHERE a.value-b.value> 20
and a.sentdate > 'x'
and a.customerid = x
August 16, 2012 at 2:48 am
scott_lotus (8/16/2012)
Thank you for the posts. Enough information in this script to allow me to construct a solution adding a few joins (ps sorry about that lack of schema layout in original post). Tested on R&D successfully. Production environment will take time to return.SELECT a.ID, a.DEVICEid, a.value, a.sentdate, a.customerid
FROM
(SELECT ID, LOC.DEVICEid, value, SENTDATE, DEVICEtbl.CUSTOMERID, ROW_NUMBER() OVER(PARTITION BY LOC.DEVICEid ORDER BY ID) AS pos FROM Loc with (NOLOCK)
INNER JOIN Devicetbl with (NOLOCK) ON Devicetbl.Deviceid = Loc.Deviceid
INNER JOIN Customer with (NOLOCK) ON Customer.CustomerID = Device.CustomerID
) a
LEFT OUTER JOIN
(SELECT ID, LOC.DEVICEid, value, sentdate, DEVICE.CUSTOMERID, ROW_NUMBER() OVER(PARTITION BY LOC.DEVICEid ORDER BY ID) AS pos FROM loc with (NOLOCK)
INNER JOIN Devicetbl with (NOLOCK)ON Devicetbl.Deviceid = Loc.Deviceid
INNER JOIN Customer with (NOLOCK) ON Customer.CustomerID = Device.CustomerID
) b ON a.DEVICEid = b.DEVICEid AND a.pos = b.pos+1
WHERE a.value-b.value> 20
and a.sentdate > 'x'
and a.customerid = x
Now, I don't know what the circumstances are for your choice of the NOLOCK hint. This may not be applicable to you and without more information there is no way for me to know.
However, in case you're not aware (and for future google searchers), please note that NOLOCK is not a good idea. I have some links for you to read through if you're interested : -
Allocation order scans with nolock
Consistency issues with nolock
Transient Corruption Errors in SQL Server error log caused by nolock
Dirty reads, read errors, reading rows twice and missing rows with nolock
August 16, 2012 at 3:16 am
Thanks for the NOLOCK info. Partially understood the side effects but the information you have posted is a great help. I understand READ_COMMITTED_SNAPSHOT is the way to go for solution with READ/WRITE blocking in preference over NOLOCK. 90% of the DB is made up of a single table with over billion rows. We are investigating a database sharing technique to scale out into multiple customer databases at which point I would hope to drop NOLOCK.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply