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...

You need to use CROSS APPLYs (CA) rather than an INNER JOIN (IJ). CA only, without the IJ, assumes you need to see only the low and high hematocrit...

Luis is correct.

But also quit using sysobjects and syscolumn views, as they are very obsolete and slow (and possibly even buggy).

Try this code instead:

SELECT @COLUMNS = @COLUMNS + c.name +...

This will be more complex than just what you've shown, if this is trying to strip prefixes/suffixes off a name, as it appears to be.

For example, the letters "JR" and...

Yeah, I can't think of a really clean way to do it now either, other than brute force:

SELECT table_name.*, derived2.row_num

FROM table_name

INNER JOIN (

SELECT colb,ROW_NUMBER() OVER(ORDER BY...