400,000 records is not a big number of records. I have runs with populate tables with with anything upto 50 million records.

There could be lots of reasons why some...

If I understand, you are getting the criteria from a list box from a screen.

My suggestion is:

create procedure [dbo].[query] @ProgramType char(1), @AccountName char(1), @ProgramStatus char(1)

as

select ProgramId ProgramType AccountName ProgramName ProgramBudget...

This might do the trick.

create function dbo.fn_format (@string as varchar(25))

returns varchar(25) as

begin

if @string > 1000000 set @string = left(@string,len(@string)-6) + '.' + substring(@string,len(@string)-5,1) + 'M'

else if @string > 1000 set...

Hedging my bets here but it depends:

1. SQL Server = Sequel Server

2. SQL statements = S Q L statments

Jeremy

Interesting timings - you learn something new everyday.

The index was probably not chosen because indexes are only useful when retrieving less than ~5% of the rows. The query is...

Alternatively, try a user defined function:

create function dbo.fn_RemoveTrailingZeros ( @string varchar(25) )

returns varchar(25) as

begin

declare @loop char(1)

set @loop = 'Y'

while @loop = 'Y' begin

if right(@string,1) not in ('0','.') begin

...

Is the query in the previous actually that efficient?

IMHO, for each row in the outer select statement it will evaluate the subselect statement - as there in no where clause...

Frank,

Thanks

Jeremy

Frank,

I've read your article and I am a bit confused.

I accept the performance overhead of dynamic SQL (not caching query plans) and the security aspects (giving specific permissions on tables...

If the array that is passed to the sp is comma delimted (e.g. col1, col2, col3) then you could use dynamic sql to create the statement:

declare @sqlString nvarchar(4000)

set @sqlstring =...

It means that the combination of the two columns must be unique.

Jeremy

It works fine on my server. It might be a setting problem - how SQL Server formats dates?

Jeremy

If I understand it, you want to put a 4 at the start of the value:

select '4' + <colname>

from <table>

HTH

Jeremy

You can use the UNION operator to concatentate the results from different tables:

Select Distinct AttributeID,

CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 1 THEN

dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS Target,

CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 2 THEN

dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS...

You can use case statements to denormalise data:

select distinct AttributeId,

case ColumnID when 1 then Data else 0 as col1

case ColumnID when 2 then Data...