shezi (3/16/2015)

---

Excellent.

One final Question. The Employee table also has status. Active or Terminated. I want...

Forgot Quote, see next post.

ORDER BY CASE WHEN pr.hours > 0 THEN 1 ELSE 2 END, Name

Yep; much safer to use "> 0" rather than checking for the specific bit value 🙂

Quirky update requires a clustered index that conforms to the computation being done.

I have the same concerns. SQL should be better at using the clustered index when it applies ... we went to the trouble to get the best clustered index...

I wasn't sure which table "valid_payroll" is in, so I guessed "Employee". If it's not, adjust the code accordingly. Btw, always use a table alias on every column...

Dan121 (3/16/2015)

---

Scott, my concern is a size of a row. As Grant said UNIQUEIDENTIFIER is 16 bytes. if I have 503 columns with datatype 'UNIQUEIDENTIFIER' and rows are empty, will...

Let me point out that using a char-based column for a date is a very bad idea. Not only does it take more space but it will get garbage...

Here's the code with the tally table built in as a derived table rather than a CTE.

Note that we need an actual date computation to compute the first desired Friday...

First, let's note that we need an actual date computation only for the first Friday. For the subsequent Fridays, we can simply add 7 more days.

Note: I...

Yes; indeed, that is the...

Edit: Bolded mods to original code.

BEGIN TRANSACTION

DECLARE @DEPTNBR BIGINT

SELECT TOP (1) @DEPTNBR = DEPTNBR

FROM DEPARTMENT_DETAILS WITH (UPDLOCK,READPAST)

WHERE STATUS = 1

UPDATE DEPARTMENT_DETAILS

SET STATUS = 0

WHERE DEPTNBR =...

CASE @Sort_Sel

WHEN 'Resource' THEN R.RESCODE

ELSE RIGHT(REPLICATE('0', 10) + CAST(D3.DATEID AS varchar(10)), 10)

END ASC

xsevensinzx (3/10/2015)

GilaMonster (3/10/2015)

---

Partitioning is not done for performance reasons. It's for data management, fast loads and fast deletes. It won't necessarily improve performance, it may, in fact, reduce performance. If...