September 23, 2010 at 12:18 am
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
September 23, 2010 at 7:50 am
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.September 23, 2010 at 9:44 pm
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?
September 29, 2010 at 6:13 am
vinothraj (9/23/2010)
SelectEmployeeNumber,
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.September 29, 2010 at 8:15 am
I've already used like that, but i need it across databases.
September 29, 2010 at 8:28 am
distributed partitioned views?
September 29, 2010 at 11:42 am
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.September 29, 2010 at 10:27 pm
thanks buddy. you are correct its across databases, where as i need schema binding in view not in table which is my original query.
September 30, 2010 at 6:27 am
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.December 6, 2010 at 3:02 pm
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
August 6, 2012 at 4:39 pm
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.
November 20, 2012 at 11:27 am
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.
February 18, 2015 at 9:38 am
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?
February 18, 2015 at 9:59 am
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply