create index on view fails, not schema bound

Posted on 2011-09-13
Last Modified: 2012-05-12
I'm trying to create an index on a view as:


but I get the error: "Cannot create index on view 'members' because the view is not schema bound."

I'm not sure what "view is not schema bound" means, but in any case, is there a way around this? Queries using this view take a really long time.
Question by:jmarkfoley
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
LVL 60

Expert Comment

by:Kevin Cross
ID: 36532182

- First check to see that the underlying tables are indexed properly.
- Also ensure that the view is not employing any JOIN or WHERE clause conditions that are inefficient, i.e., render your indexes useless.
- Check the EXECUTION PLAN on the query generating the view to help.

See this as a reference:
Note the WITH schemabinding and caveats

Author Comment

ID: 36532679
the view does have multiple joins.

I've checked your link. There are no null values in the column I want to index. I tried the "with schemabinding" and got the message:

Cannot schema bind view 'dbo.members' because name 'osh2011.dbo.tblPaEmpGenInfo' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Not sure what "two part" format is. The view references two tables in a different database. The table mentioned does reference itself (I suppose, not sure what that means) in  a left join. Am I screwed?

How do I check the execution plan? Sadly, I've never done that before.
LVL 60

Expert Comment

by:Kevin Cross
ID: 36532852
Two-part format would be schema_name.table_name or in your case dbo.tblPaEmpGenInfo. If you are querying across multiple databases and so need the three-part naming which includes database_name.schema_name.table_name then you cannot schema bind the view and as such cannot index it, unfortunately. If you post the query behind the view, maybe we can spot some way to optimize it so that hopefully you are not "screwed." *smile*

You can right-click and select "show actual execution plan or click graphical icon is probably easiest in SQL Management Studio.

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

ID: 36544308
Kevin - I'd like to check out that execution plan. We had an issue like this once before, and my predecessor did an execution plan and helped fix the problem. So, how exactly do I do that? The MS help tells you why/when you should run this, and what the results can tell you, but it doesn't actually tell you how to run it.

My included image show what I get when I right click on the view as you suggested. I see nothing related to "execution plan." The "SQL Profiler" tool gives me a blank screen with no wizard to tell me what to do next. Please advise. Thanks.
LVL 60

Expert Comment

by:Kevin Cross
ID: 36544433
My apologies for not being clear. Unfortunately, I am logged into a Linux system at the moment so using a different tool other than SQL Management Studio; therefore, my screen shots will not match up. But I will try to explain more clearly.

1. In the query window where the SQL code is that you are executing, right-click and you should see "Include Actual Execution Plan" ...
2. Query > Include Actual Execution Plan ...
3. On my SQL 2005 SSMS, there is a graphical icon 7 spots to right of the Execute button on toolbar ...
4. Ctrl+M should work.

If one of those doesn't get it, we can break out SET statements. :)

Author Comment

ID: 36545737
OK, found the icons. I have:

a) Display Estimated Execution Plan

That gives me the output:

Query 1: Query cost (relative to the batch): 50%
Query 2: Query cost (relative to the batch): 50%

Not sure what to do with that information. Nor does it tell me what Query 1 and Query 2 are. I have a union in this view, so perhaps these are each part of the union.

b) Analyze Query in Database Engine Tuning Advisor

Perhaps I'm a bit obtuse about these tools ... this one appears to give me the size and number of rows in my current database (not the database my view is looking at). In any case I don't really see any "advise".

c) Include Actual Execution Plan
d) Include Client Statistics

These two seem to turn the respective buttons on and off, but don't "do" anything that I can see. Perhaps something else needs to be run which uses these settings?

Now that I've found the buttons, perhaps you could help explain how I can use them.

More info: We are converting from a 3rd party accounting program modified to be a pension benefits program. The "old" system has two main member tables, one for active members and one for pension benefit recipients and their families. The old system creates a new database for each year. This view joins these two tables into one view. It is in a new database because that is where all the migrated tables, views, and procedures will end up and we'll stop using the by-year databases.

I'm going to check the two main tables and see if indexing can be improved.

ALSO ... you say you are using Linux and the screen shots are different. Intriguing. I am also using Linux with jdbc to a Windows SQL Server. Is that what you are doing? If so, do you have a Linux GUI for the SQL Server DB? Or, are you running SQL Server directly on Linux. Please let me know as I didn't know any of these possibilities existed. Thanks.

LVL 60

Expert Comment

by:Kevin Cross
ID: 36545921
Okay, to be clear:

If you post the query behind the view, maybe we can spot some way to optimize it so that hopefully you are not "screwed." *smile*

You can right-click and select "show actual execution plan or click graphical icon is probably easiest in SQL Management Studio.

That all went together. I wanted you to grab the SELECT statement that makes up the view. If you right-click the view, you can use Script View AS > CREATE TO > New Query Window ...

-- paste that query here in a code snippet
-- execute the query with execution plan turned on
-- right-click on the execution plan and save as XML
-- attach that XML file here
LVL 60

Expert Comment

by:Kevin Cross
ID: 36545930
"I'm going to check the two main tables and see if indexing can be improved." < That is a good idea. If indexing is fine on the tables, seeing the query behind the view may reveal issues in query that are preventing the indexes from being utilized correctly. When you get the graphical execution plan, it will often tell you if there are missing indices.

Author Comment

ID: 36546094
OK, I'll post the view first, then post the execution results later. It's a big query! I am attempting to merge 2 tables in this view. Some members exist in table a, but not in b and some exit in b, but not in a, hence the (select a union b ...) "main" table. The query is of the form:

select, a.col, b.col, ....
from (select id from a union id from b) x
left join a on =
left join b on =

/****** Object:  View [dbo].[members]    Script Date: 09/15/2011 16:49:50 ******/
-- 02-JUN-2011
ALTER view [dbo].[members] /*with schemabinding*/ as
select case when e.employeeid is not null then e.employeeId else a.employeeId end as memberId,
  left(case when e.employeeId is not null then e.socialSecurityNo else a.socialSecurityNo end,11) as socialSecurityNo,
  case when e.employeeId is not null then e.memberName else a.memberName end as memberName,
  case when e.employeeId is not null then e.firstName else a.firstName end as firstName,
  case when e.employeeId is not null then e.middleInit else a.middleInit end as middleInit,
  case when e.employeeId is not null then e.lastName else a.lastName end as lastName,
  case when e.employeeid is not null then e.employeeId else a.employeeId end as employeeId,
  case when e.employeeId is not null then e.laborClass else 'ACT' end as memberType,
  e.userDefinedField1 as retireeId,
  e.contactRelation as relationToRetiree,
  case when e.employeeId is not null then else end as sex,
  case when e.employeeId is not null then e.jobTitle else a.rank end as positionTitle,
  case when e.employeeId is not null then convert(int,e.userDefinedField2) else a.district end as district,,
  case when e.employeeId is not null then e.class else convert(varchar,a.class) end as class,
-- attribute settings
  case when e.employeeId is null or e.corporateOfficer is null then 0 else e.corporateOfficer end as estateSurvivor,
  case when a.disabled is not null and a.disabled = 1 then 1 else 0 end as activeDisabled,
  case when e.employeeId is not null then e.exemptFromOvertime when a.disabled = 2 then 1 end as onDutyDisability,
  --e.seasonalEmployee as optionBeneficiary,	-- indicator on DEP/BEN record, no longer needed
  case when e.employeeId is not null then e.boardMember else a.boardMember end as boardMember,
  case when e.employeeId is not null then e.birthDate else a.birthDate end as birthDate,
  (datediff(m,case when e.employeeId is not null then e.birthDate else a.birthDate end,getdate()) / 12) age,
  case when a.socialSecurityNo is not null then a.hireDate else e.adjustedHireDate end as hireDate,
  e.userDefinedDate6 as DROPwithdrawEligDate,
  e.userDefinedDate4 as DROPtermDate,
  a.retireDate as lastDayOfService,
  e.userDefinedDate3 as DeferRetirementDate,
  e.startDate as pensionStartDate,
  e.userDefinedDate1 as COLdate,
  e.userDefinedDate2 as ExtBenfitsEligDate,
  -- e.userDefinedDate5 as firstPensionPayment,
  case when e.employeeId is not null then e.addressLine1 else a.addressLine1 end as addressLine1,
  case when e.employeeId is not null then e.addressLine2 else a.addressLine2 end as addressLine2,
  case when e.employeeId is not null then e.residentCity else a.residentCity end as residentCity,
  case when e.employeeId is not null then e.residentState else a.residentState end as residentState,
  case when e.employeeId is not null then e.zipCode else a.zipCode end as zipCode,
  cast(null as varchar) as paytoAddressLine1, cast(null as varchar) as paytoAddressLine2, 
    cast(null as varchar) as paytoCity, cast(null as varchar) as paytoState, cast(null as varchar) as paytoZip,  
  cast(null as varchar) as mailAddressLine1, cast(null as varchar) as mailAddressLine2, cast(null as varchar) as mailCity, 
    cast(null as varchar) as mailState, cast(null as varchar) as mailZip,  
  case when e.employeeId is not null then e.phoneNumber else a.phoneNumber end as phoneNumber,
  e.workPhoneNo as workPhone,
  case when e.employeeId is not null then e.homeEmail else a.emailAddress end as emailAddress,
  case when e.employeeId is not null then e.workEmail else a.workEmail end as workEmail,
  e.emergrncyContact as emergencyContact,
  e.ContactHomePhone as emergencyContactPhone1,
  e.ContactWorkPhone as emergencyContactPhone2,
  e.salary as pensionAmount,
  convert(money,e.userdefinedfield3) as COLbase,
  o.employeeId as optionMemberId,
case when f.exemptions is not null and f.exemptions = 99 then 1 else 0 end as doNotWithholdFederal,
case when f.maritalStatus in ('S','M') then f.maritalStatus else null end as federalMaritalStatus,
case when f.exemptions is null or f.exemptions = 99 then 0 else f.exemptions end as federalExemptions,
case when f.extraWithholding is null then 0 else f.extraWithholding end as federalExtraWithholding,
case when f.fixedWithholding is null then 0 else f.fixedWithholding end as federalFixedWithholding,
case when s.exemptions is not null and s.exemptions = 99 then 1 else 0 end as doNotWithholdState,
case when s.maritalStat in ('S','M') then s.maritalStat else null end as stateMaritalStatus,
case when s.exemptions is null or s.exemptions = 99 then 0 else s.exemptions end as stateExemptions,
case when s.extraWithholding is null then 0 else s.extraWithholding end as stateExtraWithholding,
case when s.fixedWithholding is null then 0 else s.fixedWithholding end as stateFixedWithholding,
case when e.noLocalTax is null then 0 else e.noLocalTax end as doNotWithholdLocal,
--tempGroup2      0
from (select case when socialSecurityNo is null then '' else socialSecurityNo end + 
    case when laborClass in ('DEP','ALT','SUR') then employeeId else 'm' end as ssno 
  from osh2011.dbo.tblPaEmpGenInfo
  select socialSecurityNo + 'm' from osh2011.dbo.tblPaActiveMembers) x
left join osh2011.dbo.tblPaEmpGenInfo e on case when e.socialSecurityNo is null then '' else e.socialSecurityNo end + 
    case when e.laborClass in ('DEP','ALT','SUR') then e.employeeId else 'm' end = x.ssno
left join osh2011.dbo.tblPaActiveMembers a on a.socialSecurityNo + 'm' = x.ssno
left join osh2011.dbo.tblPaEmpFedWithhold f on f.employeeId = e.employeeId
left join osh2011.dbo.tblPaEmpStateWithhold s on s.employeeId = e.employeeId
left join (select userDefinedField1 retireeId, employeeId, optionElected,optionPercent,optionYears,optionAmount 
  from osh2011.dbo.tblPaEmpGenInfo where optionElected is not null and terminationDate is null) o on o.retireeId = e.employeeId

Open in new window

LVL 60

Expert Comment

by:Kevin Cross
ID: 36546145
As weird as this may sound, on my first glance I am wondering if a UNION as the outer query is not best here. I was originally thinking you were doing a FULL OUTER JOIN, but based on your CASE statements, it appears you are just pulling together two row sets to form a larger one and not really needing a JOIN of osh2011.dbo.tblPaEmpGenInfo and osh2011.dbo.tblPaActiveMembers ... but that is just an initial guess without full analysis of code in SELECT.
LVL 60

Accepted Solution

Kevin Cross earned 500 total points
ID: 36546214
And to clarify, for example:
a.socialSecurityNo + 'm' = x.ssno

This may not utilize the index on a.socialSecurityNo because it is having to concatenate it to 'm' first. In the case of osh2011.dbo.tblPaActiveMembers where all you did in UNION was "select socialSecurityNo + 'm' from osh2011.dbo.tblPaActiveMembers" it makes it seem like you could simply do a join of "a" to "x" directly where a.socialSecurityNo = x.socialSecurityNo thus making the columns sargable; however, then I looked and saw it was the same exact table; therefore, you are doing a full table scan in the derived table "x" to get a list of the socialSecurityNo values with 'm' appended to it, then turn around and self-join the table with a condition that nullifies the index and as a left outer join again with no where clause I could discern, so you have another table scan.

You could just simply do this:

select socialSecurityNo + 'm' as socialSecurityNo
     , {other columns you want}
from osh2011.dbo.tblPaActiveMembers

Maybe I am missing something, but that is just one point that will help if I am correct. Beyond that, the execution plan may reveal other areas to tune.

Author Comment

ID: 36554900
I'm going to look at that SSNO+'m" more carefully. I think I don't need that anymore. Anyway, I did manage to get the "show actual execution plan" working. I created a unique index on osh2011.dbo.tblPaActiveMembers.employeeId. That seemed to do the trick on this table. Like I said, I'll double check for the other efficiencies you suggested, but this view will soon be replaced by a real table when we've completed the migration, so perhaps it's "fast enough" for the moment.

Thanks, I think I'll consider this issue a wrap.

Before I close out the question, you wrote, "I am logged into a Linux system at the moment so using a different tool other than SQL Management Studio." Could you tell me what tool you are using? I'd love to use a Linux based tool!!!!
LVL 60

Expert Comment

by:Kevin Cross
ID: 36554993
RazorSQL. It is not free, but trying it out. I think TOAD has a package for SQL Server also. RazorSQL is the only one I have tried so far and it works for developing in SQL (haven't tried heavy administration through it). ;)

Author Closing Comment

ID: 36710017
Thanks for all the tips!

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question