Schema binding across multiple databases

  • Hi All

    We need to create a clustered index by implementing schema binding in the view. As you know it throws error. Could anyone possibly tell me to achieve in alternate way?

    Thanks in advance

    Vin

  • vinothraj (9/23/2010)


    We need to create a clustered index by implementing schema binding in the view. As you know it throws error.

    How could I know? ... post shows nothing.

    Would you mind in posting a test case showing the issue?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Thanks for the reply...

    For example i've two databases called "dbFirst" and "dbSecond".

    In dbSecond i've a table called "EmployeeTable".

    I'm going to create a view called "EmployeeView" in dbFirst database with

    Select

    EmployeeNumber,

    EmployeeName

    From

    dbSecond.dbo.EmployeeTable

    I want to create a index (either clustered or non-clustered) for this view as my view containing millions of records and i dont want to wait for the retrieval. If i want to create index for this view which is located in dbFirst referring dbSecond, i'm unable to do. Could you suggest me whether there is any alternate way to achieve this?

  • vinothraj (9/23/2010)


    Select

    EmployeeNumber,

    EmployeeName

    From

    dbSecond.dbo.EmployeeTable

    I want to create a index (either clustered or non-clustered) for this view as my view containing millions of records and i dont want to wait for the retrieval. If i want to create index for this view which is located in dbFirst referring dbSecond, i'm unable to do. Could you suggest me whether there is any alternate way to achieve this?

    First, a view is an abstraction not a physical object meaning a view is just a stored query therefore it can contain nothing.

    SQL Server engine takes advantage of the indexes pointing to the base table/s of the view.

    You can see it by yourself doing this simple test.

    1- Go to Northwind and create a view pointing to Pubs database like...

    USE Northwind

    go

    SET ANSI_NULLS,QUOTED_IDENTIFIER ON

    go

    CREATE VIEW dbo.MyEmployeeView

    AS

    SELECT *

    FROM pubs.dbo.employee

    WHERE emp_id = 'JYL26161F'

    2- Check the execution plan of query below...

    USE Northwind

    go

    select *

    from dbo.MyEmployeeView

    ... which should show something like...

    StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions

    select * from dbo.MyEmployeeView110[NULL][NULL]1[NULL]1[NULL][NULL][NULL]0.0126592[NULL][NULL]SELECT0[NULL]

    |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[employee]))131Bookmark LookupBookmark LookupBOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[employee])[employee].[hire_date], [employee].[pub_id], [employee].[job_lvl], [employee].[job_id], [employee].[lname], [employee].[minit], [employee].[fname], [employee].[emp_id]10.006251.1e-006490.0126592[employee].[hire_date], [employee].[pub_id], [employee].[job_lvl], [employee].[job_id], [employee].[lname], [employee].[minit], [employee].[fname], [employee].[emp_id][NULL]PLAN_ROW01

    |--Index Seek(OBJECT:([pubs].[dbo].[employee].[PK_emp_id]), SEEK:([employee].[emp_id]='JYL26161F') ORDERED FORWARD)143Index SeekIndex SeekOBJECT:([pubs].[dbo].[employee].[PK_emp_id]), SEEK:([employee].[emp_id]='JYL26161F') ORDERED FORWARD[Bmk1000]10.00632857.96e-005530.0064081[Bmk1000][NULL]PLAN_ROW01

    ... it does takes advantage of existing base table indexes, doesn't it?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • I've already used like that, but i need it across databases.

  • distributed partitioned views?

  • vinothraj (9/29/2010)


    I've already used like that, but i need it across databases.

    Please note that my example has query and view in database "A" while base tables of the view are in database "B".

    Would you mind in elaborating about "across databases"?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • thanks buddy. you are correct its across databases, where as i need schema binding in view not in table which is my original query.

  • vinothraj (9/23/2010)


    We need to create a clustered index by implementing schema binding in the view.

    Lets start over?

    Post is kind of confusing.

    1- Clustered indexes have their leaf level inside the target table therefore it is hard to understand how "clustered index" and "view" got into the same phrase.

    2- schemabinding option creates a schema - schema as in DDL - dependency in between the new view and the base tables. Again, not sure how "clustered index" made it to this phrase.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • While reading this post, I believe I have a similar question. There are tables that exist in dbA and dbB. I wish to create an fast view in dbC, as in the example below:

    USE [Onyx_support]

    GO

    IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vIncident_Audit_Log]'))

    DROP VIEW [dbo].[vIncident_Audit_Log]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE VIEW [dbo].[vIncident_Audit_Log] WITH SCHEMABINDING

    AS

    SELECT incident_audit_log_id, site_id, incident_type_did, incident_category_did, incident_product_code, incident_id, secondary_id, assigned_to, labor, time,

    primary_contact_id, primary_contact_type_enum, assigned_id, tracking_code_did, serial_number, desc1, desc2, source_did, status_did,

    resolution_did1, resolution_did2, resolution_did3, resolution_did4, total_time, total_labor, image, read_only_access, private_access, reminder_date,

    user1, user2, user3, user4, user5, user6, user7, user8, user9, user10, insert_by, insert_date, update_by, update_date, onyx_timestamp, delete_status,

    central_office, cs_source_description, cs_proposal_date, cs_projected_close_month, cs_contract_expire_date, cs_current_provider, cs_competitor,

    cs_competitor_description, cs_pots_ale, cs_t1_ale, cs_mrr, cs_nrr, cs_hi_cap_ale, cs_phone_system_type, cs_bts_nrr

    FROM onyx.dbo.incident_audit_log

    WHERE (insert_date > DATEADD(m, - 2, GETDATE()))

    when I try to run the above code, I get the error below:

    Cannot schema bind view 'dbo.vIncident_Audit_Log' because name 'onyx..incident_audit_log'

    is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

    Is there a way for me to create an indexable view on a column that is not indexed in the Onyx db? They are proprietary database tables that are running too slow in a query.

    TIA

    Andre

  • quote taken from http://www.sqlballs.com/2012/05/cross-database-views-and-schema-binding.html

    An Indexed View is essentially a Materialized View. All of the data in the view instead of existing as a select statement is persisted to the physical disk. The way the data is read for an index view is quicker because you are performing a seek or a scan against one object that is dependent upon its base table.

    Too much bad information on web. All starts with people not knowing what they are talking about.

  • Books online list the following requirements/limitations for indexed views:

    The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.

    The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.

    The view must not reference any other views, only base tables.

    All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

    The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.

    User-defined functions referenced in the view must have been created with the SCHEMABINDING option.

    Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.

    All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.

    Note that the limitation of referencing tables in a different database is specifically mentioned.

    My suggestion would be to create the indexed view in the database containing the tables. Then create a synonym in the database you originally wanted the view in and have it point to the new view in the other database.

    This is probably the only workaround to your issue.

    I do have a few questions for you about your plan to use an indexed view.

    First, you realize that an indexed view creates a copy of the entire result set and does not necessarily query the involved tables for each query?

    Is the data you wish to include in the indexed view highly transactional or fairly static? If highly transactional, you will probably see a performance hit rather than improvement due to the requirement for the system to update the result set with each data change.

  • I have a similar need.

    The view points at a table (a table that stores daily currency exchange rates) in a different database (the master database that stores all shared data).

    Replies in this thread are confusing.

    In short, can you create a view WITH SCHEMABINDING to a table in a different database?

  • you can create a view, but Not with schemabinding.

    Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.

    since a differnet database is referenced via a three part name, for example, and since there's no way to create true referential integrity accross multipel databases in SQL server, it's not possible with schemabinding.

    max.shrimps (2/18/2015)


    I have a similar need.

    The view points at a table (a table that stores daily currency exchange rates) in a different database (the master database that stores all shared data).

    Replies in this thread are confusing.

    In short, can you create a view WITH SCHEMABINDING to a table in a different database?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply