But a temp table / table variable won't begin to match the performance of a true array.

Lynn Pettis (7/23/2015)

WayneS (7/23/2015)

Good luck with adding that...

Btw, the WHERE clause can be improved to allow an index seek, if applicable:

WHERE LEFT(name, 5) = 'APTMP'

should be:

WHERE name LIKE 'APTMP%'

gshouse (7/22/2015)

If I wanted to get real nit-picky I'd even ask why int is lower case for one parameter-type and INT is upper case for the other. But that...

Often a good start is to look at current input and display screens, as well as reports, but just to identity needed data elements, not to copy the format/layout.

Agree with using REPLICATE instead, and get rid of any extraneous local variables, so fully coded would look like this:

CREATE FUNCTION [dbo].[NumberToString] (

@value int,

...

The most important thing is to treat it first as a data project. The database part comes later!

That is, start with logical data modeling. And stick with that...

Do not cluster this table by identity. Cluster it instead on EmailDate, for example.

I'd strongly consider using nvarchar(max) rather than nvarchar(3000) to allow longer messages if needed.

Status should not...

Hmm, no reason to insert a large number of rows and then do a distinct. Do either of the following, depending on if you need a count for each...

Without further info, the truly best clustering index is a complete guess, but based on what is known so far, I'd say try:

Cluster the table on ( StartDate, EndDate )

Is the key column really that long?

Why not just use a separate row for each interval, 5, 15 and 60 minutes?:

settlement_key, interval, value

I'd prefer a single UPDATE statement:

UPDATE raa

SET tt_TotalPay = aan.TotalPay,

tt_CountOf100s = aan.TotalPay / 100,

tt_CountOf50s = aan.TotalPay % 100 / 50,

...

SELECT Capacity

FROM (

SELECT '1234' AS Capacity UNION ALL

SELECT '-987' UNION ALL

SELECT NULL UNION ALL

SELECT...

Put double quotes around the column in the SELECT list:

SELECT ..., '"' + column_with_leading_zeros + '"' AS column_name, ...

You're welcome!

Btw, note that for "SELECT TOP (1) *", the * is not a problem, because SQL can determine that it needs to get only the columns that are actually...