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
  • 8
  • 6
LVL 59

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 59

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.

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the 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 59

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 59

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 59

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 59

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 59

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 59

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Not selecting duplicate data 6 54
Isolation level in SQL server 3 50
How to order Highest and lowest value rows alternatively in SQL Server ? 4 53
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

777 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