create index on view fails, not schema bound

Hi.

- 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:
http://www.sqlservernation.com/home/tag/schemabinding
Note the WITH schemabinding and caveats

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.

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. http://msdn.microsoft.com/en-us/library/ms178071.aspx

Kevin

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

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

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.

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. http://msdn.microsoft.com/en-us/library/ms178071.aspx

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

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

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 x.id, a.col, b.col, ....
from (select id from a union id from b) x
left join a on a.id = x.id
left join b on b.id = x.id

USE [HPRS]
GO
/****** Object:  View [dbo].[members]    Script Date: 09/15/2011 16:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 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,
-- GENERAL
  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 e.sex else a.sex end as sex,
  case when e.employeeId is not null then e.jobTitle else a.rank end as positionTitle,
  a.jobCode,
  case when e.employeeId is not null then convert(int,e.userDefinedField2) else a.district end as district,
  a.post,
  a.unit,
  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,
  a.diedOnDuty,
  a.terminateReason,
  --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,
  e.freeDues,
  e.smoker,
  e.spouseOK,
  e.no1099,
  e.DROPforfeitOverride,
  e.newsLetterDelivery,
  e.checkstubDelivery,
--
-- KEY DATES
--
  a.MilitaryServiceStart,
  a.militaryServiceEnd,
  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,
  a.DROPdate,
  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,
  e.terminationDate,
  a.deceasedDate,
  e.exclusionStartDate,
--
-- CONTACT INFORMATION
--
  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.cellPhone,
  a.invalidMailingAddress,
  e.noPublishAddr,
  e.noPublishPhone,
  e.noPublishEmail,
  e.noGroupEmail,
  e.emergrncyContact as emergencyContact,
  e.ContactHomePhone as emergencyContactPhone1,
  e.ContactWorkPhone as emergencyContactPhone2,
--
-- WAGE AND PENSION
--
  a.taxedContrib,
  e.originalPension,
  e.salary as pensionAmount,
  e.payPeriodsPerYear,
  convert(money,e.userdefinedfield3) as COLbase,
  e.exclusionAmount,
  e.exclusionMonths,
  o.employeeId as optionMemberId,
  o.optionElected,
  o.optionPercent,
  o.optionYears,
  o.optionAmount,
  e.partialLumpSumAmount,
--
-- TAXES
--
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
a.comments
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
union 
  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

Select allOpen in new window

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.

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

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

Thanks for all the tips!