Invalid Column Name

When I execute the following command under SQL Server 2000, "ALT_1_UNIT" is
correctly interpreted as the column name ALT_1_UNIT:
set quoted_identifier on
select "ALT_1_UNIT"
from IM_ITEM
However, I would like to be able to dynamically set the column name like
this:
set quoted_identifier on
declare @.UNIT varchar(1)
set @.UNIT = '1'
select "ALT_" + @.UNIT + "_UNIT"
from IM_ITEM
When I execute this command, I get the errors
Server: Msg 207, Level 16, State 3, Line 4
Invalid column name 'ALT_'.
Server: Msg 207, Level 16, State 1, Line 4
Invalid column name '_UNIT'.
From this, it appears that I cannot concatenate a column identifier and a
string.
I then tried replacing the single quotes in
set @.UNIT = '1'
with double quotes as in
set @.UNIT = "1"
That produced an additional error:
Server: Msg 207, Level 16, State 3, Line 3
Invalid column name '2'.
Server: Msg 207, Level 16, State 1, Line 4
Invalid column name 'ALT_'.
Server: Msg 207, Level 16, State 1, Line 4
Invalid column name '_UNIT'.
From this, it appears that I cannot store a column identifier in a variable.
How can I accomplish what I want to do?Paul,
I'm not sure what exactly you are trying to accomplish, however for what you
are attempting this should work fine in a stored procedure for sql 2k
CREATE PROCEDURE sp_testthis
(
@.Unit varchar(1)
)
AS
select "ALT_" + @.Unit + "_UNIT" as mystring
from
IM_ITEM
GO
mystring is what you will be selecting in code...for example (c#):
// ****************************************
**************
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=myserver;Initial
Catalog='mycatalogue';Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("sp_testthis", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1= new SqlParameter("@.Unit", SqlDbType.VarChar,
1);
param1.Value = TextBox1.Text;
cmd.Parameters.Add(param1);
string mytest = String.Empty;
conn.Open();
SqlDataReader result =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
result.Read();
mytest = (string)result["mystring"];
result.Close();
conn.Close();
cmd.Dispose();
Label1.Text = mytest.ToString();
}
// ****************************************
**************
HTH
"Paul D." <pablodados@.hotmail.com> wrote in message
news:XuAXf.71026$9I5.5222@.tornado.ohiordc.rr.com...
> When I execute the following command under SQL Server 2000, "ALT_1_UNIT"
> is
> correctly interpreted as the column name ALT_1_UNIT:
> set quoted_identifier on
> select "ALT_1_UNIT"
> from IM_ITEM
> However, I would like to be able to dynamically set the column name like
> this:
> set quoted_identifier on
> declare @.UNIT varchar(1)
> set @.UNIT = '1'
> select "ALT_" + @.UNIT + "_UNIT"
> from IM_ITEM
> When I execute this command, I get the errors
> Server: Msg 207, Level 16, State 3, Line 4
> Invalid column name 'ALT_'.
> Server: Msg 207, Level 16, State 1, Line 4
> Invalid column name '_UNIT'.
> From this, it appears that I cannot concatenate a column identifier and a
> string.
> I then tried replacing the single quotes in
> set @.UNIT = '1'
> with double quotes as in
> set @.UNIT = "1"
> That produced an additional error:
> Server: Msg 207, Level 16, State 3, Line 3
> Invalid column name '2'.
> Server: Msg 207, Level 16, State 1, Line 4
> Invalid column name 'ALT_'.
> Server: Msg 207, Level 16, State 1, Line 4
> Invalid column name '_UNIT'.
> From this, it appears that I cannot store a column identifier in a
> variable.
> How can I accomplish what I want to do?
>
>|||found a problem, this should work better
CREATE PROCEDURE sp_testthis
(
@.Unit varchar(1)
)
AS
declare @.mystring nvarchar(50)
select @.mystring = '
select ALT_' + @.Unit + '_UNIT
from
IM_ITEM'
execute(@.mystring)
GO
you will need to have select permission granted to your user on that table

> // ****************************************
**************
> protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=myserver;Initial
Catalog='mycatalogue';Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("sp_testthis", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1 = new SqlParameter("@.Unit", SqlDbType.VarChar,
1);
param1.Value = TextBox1.Text;
cmd.Parameters.Add(param1);
string mytest = String.Empty;
try
{
conn.Open();
SqlDataReader result =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
result.Read();
mytest = (string)result["Alt_" + TextBox1.Text + "_UNIT"];
result.Close();
conn.Close();
cmd.Dispose();
Label1.Text = mytest.ToString();
}
catch { Label1.Text = "error message"; }
}
> // ****************************************
**************
>
sorry, hope this works better|||That will work for me. Thanks!
Paul
"alice" <alice@.nospam.com> wrote in message
news:%234%233AtcVGHA.424@.TK2MSFTNGP12.phx.gbl...
> found a problem, this should work better
> CREATE PROCEDURE sp_testthis
> (
> @.Unit varchar(1)
> )
> AS
> declare @.mystring nvarchar(50)
> select @.mystring = '
> select ALT_' + @.Unit + '_UNIT
> from
> IM_ITEM'
> execute(@.mystring)
> GO
> you will need to have select permission granted to your user on that table
>
> {
> SqlConnection conn = new SqlConnection("server=myserver;Initial
> Catalog='mycatalogue';Integrated Security=SSPI");
> SqlCommand cmd = new SqlCommand("sp_testthis", conn);
> cmd.CommandType = CommandType.StoredProcedure;
> SqlParameter param1 = new SqlParameter("@.Unit", SqlDbType.VarChar,
> 1);
> param1.Value = TextBox1.Text;
> cmd.Parameters.Add(param1);
> string mytest = String.Empty;
> try
> {
> conn.Open();
> SqlDataReader result =
> cmd.ExecuteReader(CommandBehavior.CloseConnection);
> result.Read();
> mytest = (string)result["Alt_" + TextBox1.Text + "_UNIT"];
> result.Close();
> conn.Close();
> cmd.Dispose();
> Label1.Text = mytest.ToString();
> }
> catch { Label1.Text = "error message"; }
> }
> sorry, hope this works better
>|||Paul D. (pablodados@.hotmail.com) writes:
> When I execute the following command under SQL Server 2000, "ALT_1_UNIT"
> is correctly interpreted as the column name ALT_1_UNIT:
> set quoted_identifier on
> select "ALT_1_UNIT"
> from IM_ITEM
> However, I would like to be able to dynamically set the column name like
> this:
> set quoted_identifier on
> declare @.UNIT varchar(1)
> set @.UNIT = '1'
> select "ALT_" + @.UNIT + "_UNIT"
> from IM_ITEM
Sound like you have bad database design. You will need to use dynamic
SQL, which has a lot of consequences. I have an article on my web site
on dynamic SQL: http://www.sommarskog.se/dynamic_sql.html. Of particular
interest to you is http://www.sommarskog.se/dynamic_sql.html#Dyn_update.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9799657D8C3FYazorman@.127.0.0.1...
> Paul D. (pablodados@.hotmail.com) writes:
> Sound like you have bad database design. You will need to use dynamic
> SQL, which has a lot of consequences. I have an article on my web site
> on dynamic SQL: http://www.sommarskog.se/dynamic_sql.html. Of particular
> interest to you is http://www.sommarskog.se/dynamic_sql.html#Dyn_update.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Thanks. I see your point about the database design being bad. This IM_ITEM
table includes columns ALT_1_UNIT through ALT_5_UNIT and those could be put
into a separate table. Unfortunately, I have no control over the design of
this particular database.
I do also see that I could use a case statement to determine the name of the
column based on the value of @.UNIT. Is there an advantage to doing it that
way as opposed to forming the column name through concatenation?
Paul D.|||Paul D. (pablodados@.hotmail.com) writes:
> I do also see that I could use a case statement to determine the name of
> the column based on the value of @.UNIT. Is there an advantage to doing
> it that way as opposed to forming the column name through
> concatenation?
Instead of getting five plans in the cache, you get one. Furthermore,
if you use dynamic SQL, but you neglect to prefix the table name with
"dbo.", each user will get his own plan. These are issue that you
avoid if you use stored procedures with static SQL.
And of course, if the users do not have SELECT permissions on the table,
there is a very important difference.
Overall, it's better to use a solution with static SQL when this is
possible, as it reduces complexity and thus reduces maintenance costs.
There are some exceptions to this rule, the prime example is dynamic
search conditions. Had you had 550 such columns, I might have advocated
a solution with dynamic SQL, but for five columns, no. Use CASE instead.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

intra-query parallelism

Hi,
I am running sql 7 with sp3.
I got an error in running a sp.
The error message says, :Intra-query parallelism caused your server
command(process ID# 30) to deadlock. Rerun the query without intra-query
parallelism by using the query hint option (Maxdop 1).
Any ideas for this.
Thanks,Can you upgrade to Service Pack 4 and see if that remedies the issue?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am running sql 7 with sp3.
> I got an error in running a sp.
> The error message says, :Intra-query parallelism caused your server
> command(process ID# 30) to deadlock. Rerun the query without intra-query
> parallelism by using the query hint option (Maxdop 1).
> Any ideas for this.
> Thanks,
>|||I do have sp4 installed
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eXWhKH35EHA.1404@.TK2MSFTNGP11.phx.gbl...
> Can you upgrade to Service Pack 4 and see if that remedies the issue?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:OpMRxD35EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Hi,
> >
> > I am running sql 7 with sp3.
> >
> > I got an error in running a sp.
> > The error message says, :Intra-query parallelism caused your server
> > command(process ID# 30) to deadlock. Rerun the query without intra-query
> > parallelism by using the query hint option (Maxdop 1).
> >
> > Any ideas for this.
> >
> > Thanks,
> >
> >
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> I do have sp4 installed
Okay, your original post said SP3...
Did you try the MAXDOP hint? If it doesn't decrease performance
significantly, go with that. Otherwise, you're going to have to call PSS.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||What I did was increase the value for intra-query-Paralellism.
Is that ok instead of trying try the MAXDOP hint?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#H3rPc35EHA.4004@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23zvTGa35EHA.3756@.TK2MSFTNGP14.phx.gbl...
> > I do have sp4 installed
>
> Okay, your original post said SP3...
> Did you try the MAXDOP hint? If it doesn't decrease performance
> significantly, go with that. Otherwise, you're going to have to call PSS.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> What I did was increase the value for intra-query-Paralellism.
> Is that ok instead of trying try the MAXDOP hint?
Well, that will affect your entire server. If you're okay with that then I
guess it's not a problem, but it could have performance implications on
other queries that aren't having issues with parallelism. The MAXDOP hint
would only affect the one query.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks,
How do I do for The MAXDOP hint
>that would only affect the one query.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#DluLl35EHA.1632@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:uqE72i35EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > What I did was increase the value for intra-query-Paralellism.
> > Is that ok instead of trying try the MAXDOP hint?
>
> Well, that will affect your entire server. If you're okay with that then
I
> guess it's not a problem, but it could have performance implications on
> other queries that aren't having issues with parallelism. The MAXDOP hint
> would only affect the one query.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>|||"metoonyc" <metoonyc@.yahoo.com> wrote in message
news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
> Thanks,
> How do I do for The MAXDOP hint
> >that would only affect the one query.
SELECT ...
FROM ...
WHERE ...
OPTION (MAXDOP 1)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Thanks,
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:#zTbWH45EHA.3644@.tk2msftngp13.phx.gbl...
> "metoonyc" <metoonyc@.yahoo.com> wrote in message
> news:%23lNE$r35EHA.824@.TK2MSFTNGP11.phx.gbl...
> > Thanks,
> >
> > How do I do for The MAXDOP hint
> > >that would only affect the one query.
>
> SELECT ...
> FROM ...
> WHERE ...
> OPTION (MAXDOP 1)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>

Interpreting index statistics on SQL 2005

I ran the DBCC SHOW_STATISTICS command for all of my indexes; I was told that high density numbers are bad, low numbers good. I have some questions about my results, though; I'm not sure how to interpret them.

Of 48 indexes, 14 have a density of 0. Does this mean that the indexes are not selective enough? Does it mean they're garbage and I should toss them?

6 have a density of NULL. They are all primary keys. I suppose this just means that they're never used because these tables are rarely queried. Would this assumption be correct?

13 have a density of 1. I have no idea what this means.

The others have densities ranging from 0.01210491 to 0.5841165. I was told that the lower this number is, the more selective and thus more useful an index is. I think 0.5841165 is too high a number. Would this be correct?

Thanks in advance.First you need to ask good and bad for what. I can argue both ways equally well. Low density numbers are good for OLTP, and high density numbers are good for OLAP. Density is practically meaningless for compound keys, since they are almost always compound and the densisty only refers to the leftmost column.

Density is meant to convey one specific property of a table. I use it to get an initial feel for the table, but it doesn't mean much in the "big picture" to me... I really need more information than what the Density can convey by itself.

-PatP|||First you need to ask good and bad for what. I can argue both ways equally well. Low density numbers are good for OLTP, and high density numbers are good for OLAP. Density is practically meaningless for compound keys, since they are almost always compound and the densisty only refers to the leftmost column.

Density is meant to convey one specific property of a table. I use it to get an initial feel for the table, but it doesn't mean much in the "big picture" to me... I really need more information than what the Density can convey by itself.

-PatP

Ummm kaaaaaaay. Didn't understand hardly a word of that.

Basically I have a bunch of giant indexes chewing up space on my DB...one table's indexes take up 3 times as much space as the table's data does. Another tables indexes take up 20% more space than the table's data. I want to get rid of indexes that aren't being used or that are garbage; I thought I could use statistics to help determine that.

I know next to nothing about indexing.|||Ah, now we're getting somewhere!

First of all, I need to correct one typing error, I meant "natural keys, since they..." in the second paragraph above, I just didn't proofread it before I sent it.

Just FYI, the density has very little to do with how useful the index is to an application. There are lots of other things that factor in much more importantly than the density.

If you don't want to take the time to understand indexing, you really shouldn't try to make decisions about it... Kind of like if you don't know a distributor from a carburator, you probably shouldn't work on your motorcycle... Just ride it and enjoy! When it needs a tune up, call a professional.

If you want to learn about indexing, there are lots of web pages that explain it at varying levels of detail. I'm not sure what your needs are, so I can't really recommend one over another, but if you snoop around ten or fifteen minutes I'm sure you can find several that suit your needs.

If you don't want to learn about indexing, you've still got several choices. You can hire someone to come in and do the job for you. This is quick, efficient, and gets you about as good a solution as you're likely to get. You can post your index script here, and maybe someone will help you online. This isn't as good or as reliable, but its cheaper and might be faster too.

-PatP|||One thing you may try is the Index Tuning Wizard. It is not a perfect tool, but it may point out what indexes are not getting used. You will need to run a trace of the activity, and making sure you get enough activity is the big problem. If some of these indexes are used for the CEO's big report at the end of the quarter, and you drop that index, there could be a few uncomfortable explanations you will have to give. In short, have all the indexes scripted out, so you can add them back later on, if need be.|||Ah, now we're getting somewhere!

First of all, I need to correct one typing error, I meant "natural keys, since they..." in the second paragraph above, I just didn't proofread it before I sent it.

Just FYI, the density has very little to do with how useful the index is to an application. There are lots of other things that factor in much more importantly than the density.

If you don't want to take the time to understand indexing, you really shouldn't try to make decisions about it... Kind of like if you don't know a distributor from a carburator, you probably shouldn't work on your motorcycle... Just ride it and enjoy! When it needs a tune up, call a professional.

If you want to learn about indexing, there are lots of web pages that explain it at varying levels of detail. I'm not sure what your needs are, so I can't really recommend one over another, but if you snoop around ten or fifteen minutes I'm sure you can find several that suit your needs.

If you don't want to learn about indexing, you've still got several choices. You can hire someone to come in and do the job for you. This is quick, efficient, and gets you about as good a solution as you're likely to get. You can post your index script here, and maybe someone will help you online. This isn't as good or as reliable, but its cheaper and might be faster too.

-PatP

*sigh*

I never said I didn't want to learn. I said that I don't know very much about it and that I didn't understand what you had said earlier, which is not the same thing.

I have read many web pages dealing with indexing. I've found them to be as useful as a bucket of pee.

I'm sorry I asked. I don't feel that I did anything to deserve that barrage of condescending comments. I certainly won't be back asking any more questions or taking up any more of your precious time.|||One thing you may try is the Index Tuning Wizard. It is not a perfect tool, but it may point out what indexes are not getting used. You will need to run a trace of the activity, and making sure you get enough activity is the big problem. If some of these indexes are used for the CEO's big report at the end of the quarter, and you drop that index, there could be a few uncomfortable explanations you will have to give. In short, have all the indexes scripted out, so you can add them back later on, if need be.

We don't have access to that here; I did have the DBAs run it on a SQL Server 2000 database a short time ago and while they didn't let me see the actual output, they said that my indexes were "fine." Then they called me the next day griping about clustered index scans. Can't hit a moving target so why try? Seems nearly every time I ask, I get insulted and shouted down.|||Well, I think you need to chill just a bit...I don't think Pat was trying to be condesending...

If the DBA's are griping about scans...that is a totally different issue...|||Well, I think you need to chill just a bit...I don't think Pat was trying to be condesending...

If the DBA's are griping about scans...that is a totally different issue...

I'm trying to chill but it seems that indexing is a sacred subject because everywhere I've asked, I've basically been told to take a hike when I ask questions about that particular subject. The books I've read suck and I haven't found a decent website yet. I'm here asking for help and I'm getting fobbed off. My DBAs don't want to help me since they're about to be outsourced; you couldn't pay them to care about what's going on at the moment. Thing is, I still have a job and my database's performance is an issue. I'm trying to fix it. That's all I'm trying to do here.|||How about posting one of the queries that seem to be giving you a problem? Not all queries can be helped by indexes. In fact, some can be outright hurt by indexes, so even the best of experts have to guess more often than you might think.|||I've been watching Pat post for several years and he is just about the most even handed poster on here. I think you will probably find that he was trying to triangulate in on your experience and knowledge level to ensure his answer was appropriate and (it appears) simply missed the target a little.

And what is wrong with the UK anyway? Or have we covered that already before...?|||How about posting one of the queries that seem to be giving you a problem? Not all queries can be helped by indexes. In fact, some can be outright hurt by indexes, so even the best of experts have to guess more often than you might think.

Well...I think some of the queries are problematic because they're pulling down a ton of rows. The queries drive reports in an application I wrote and their where clauses are dynamic. I've tried to index the columns used in the where clauses but they just never seem to get used; I just get hit with a clustered index scan unless I keep narrowing the focus of the query; then I get the index seeks that the DBAs want to see.

For example, this query takes 24 seconds to run and returns 53,914 rows.

SELECT a.Pol_no
, a.OM_Available
, a.OM_Complete
, a.Agency
, a.Channel
, a.EDI_Flag
, a.NTU_Date
, a.PLCY_NAPI
, a.Plcy_Type_Cd
, PIE.Started AS 'Policy_Issue_Started'
, PR.Started AS 'PROPNTU_Started'
, Reg.Ended AS 'Registration_Ended'
, a.Team
, a.SumAssured
, a.RunDate
, a.MissingItems
, a.Distributor
, a.ParentCompany
, a.Site
, a.CI_Type
, a.ProcessOwner
, a.Status AS 'FullStatus'
, CriticalIllness = CASE WHEN a.CI_Type IS NULL THEN 'Life Only' ELSE 'Critical Illness' END
, QuartersOld = CAST(DATEDIFF(m, a.OM_Available, a.RunDate)/3 AS Int)
, DATEDIFF(d, a.OM_Available, a.RunDate) AS 'DaysElapsed'
FROM vwFESubset a
LEFT OUTER JOIN vwPIE PIE ON a.Pol_no = PIE.Pol_no
LEFT OUTER JOIN vwPROPNTU PR ON a.Pol_no = PR.Pol_no
LEFT OUTER JOIN vwReg Reg ON a.Pol_no = Reg.Pol_no
WHERE a.Status IN('Accepted','Registered','Pending')
AND a.OM_Complete IS NULL

We never run it on its own; we just query it for other reports. I get index scans in five different tables and index seeks in three, and sometimes multiple index scans or seeks for the same table. There are some tables with less than 12 rows in them which always have clustered index scans. I've been doing this less than a year so I'm not always sure how to interpret the query plan but our DBAs really hate seeing scans of any kind and they jump all over me whenever they see them.|||And what is wrong with the UK anyway? Or have we covered that already before...?

I don't like it.|||I am going to guess that the "vw" in the from clause means views. One of the first things to try here is to go through those views, and see if the views involve tables you do not need. If so, you can rewrite the query to go to the base tables, and you will avoid all the reads and joins against those extra tables.

As a percentage, how many rows have a.Status IN('Accepted','Registered','Pending')? I am going to guess it is a sizable percentage. If so, this query may not be eligible for indexes.

Is this query run for every report? If so, is the data aggregated or filtered further up the chain? If you can get that extra filtering back down to the database level, you may stand a better chance of making your DBAs less unpleasant.|||I am going to guess that the "vw" in the from clause means views. One of the first things to try here is to go through those views, and see if the views involve tables you do not need. If so, you can rewrite the query to go to the base tables, and you will avoid all the reads and joins against those extra tables.

As a percentage, how many rows have a.Status IN('Accepted','Registered','Pending')? I am going to guess it is a sizable percentage. If so, this query may not be eligible for indexes.

Is this query run for every report? If so, is the data aggregated or filtered further up the chain? If you can get that extra filtering back down to the database level, you may stand a better chance of making your DBAs less unpleasant.

The views are all necessary, and that table is indexed. The data is filtered sometimes, and the columns it is filtered on are indexed.

The Status IN value grabs 106800 rows out of 124727 possibles. I would think that wouldn't be eligible for indexing ever but I can't make my DBAs understand. They want me to further narrow the scope of the queries, which would make the reports pretty much useless.