Stack Overflow
Stack Overflow

Questions tagged [schemabinding]

19 questions
64
votes
10 answers

Downsides to "WITH SCHEMABINDING" in SQL Server?

I have a database with hundreds of awkwardly named tables in it (CG001T, GH066L, etc), and I have views on every one with its "friendly" name (the view "CUSTOMERS" is "SELECT * FROM GG120T", for example). I want to add "WITH SCHEMABINDING" to my…
SqlRyan
  • 33,116
  • 33
  • 114
  • 199
16
votes
2 answers

Cannot create index on view because the view is not schema bound error 1939

The third part of this assignment I'm working on says, "Create and index the existing Northwind view called “dbo.Orders Qry” based on the columns OrderID and EmployeeID." It is expected that I will get an error; however, my instructor only told us…
Sam
  • 161
  • 1
  • 1
  • 3
12
votes
2 answers

T-SQL - Check if view is SCHEMABINDING

I tried to google it, but didn't found an answer... Is it possible to check if view is created with SCHEMABINDING?
Alex Dn
  • 5,465
  • 7
  • 41
  • 79
2
votes
1 answer

How did a not null date become nullable in a view

I have a datetime field in a table called dbo.Traffic I am trying to aggregate traffic data by day. I am planing on creating a schemabound view and adding an index. CREATE VIEW [dbo].[vwTraffic] WITH SCHEMABINDING AS SELECT CONVERT(date,…
James A Mohler
  • 11,060
  • 15
  • 46
  • 72
1
vote
0 answers

How to drop and recreate indexed view without hardcoding the CREATE VIEW definitiotion

I have couple of indexed views (with schema binding and index) in my database. I want to alter some some columns used by the view, but I get: The object 'MyIndexedView' is dependent on the column 'MyColumn'. ALTER TABLE ALTER COLUMN MyColumn…
Liero
  • 25,216
  • 29
  • 151
  • 297
1
vote
1 answer

Using schema-bound SQL Server user-defined functions with alias types?

Consider the following T-SQL statements: create type mytype from decimal(8,3) go create function f (@x int) returns mytype with schemabinding as begin return @x * @x end When I try this using SQL Server 2017 (v14.xx), I get this error: Msg…
Kevin Martin
  • 116
  • 8
1
vote
1 answer

SQL Server : indexed views validating inserts in the base table incorrectly

I believe SQL Server indexed views are validating inserts in the base table incorrectly. In order to simulate, consider the following Create table: CREATE TABLE [dbo].[table_e] ( [id] [int] NOT NULL, [module] [varchar](50) NULL, [event]…
user158936
  • 11
  • 2
1
vote
0 answers

How to test for wrong table names and column names using scripts (SQL Server)

While developing and testing a new release I like to regularly run a test that checks that the references in some stored procedure still are valid and this without execution the SP’s (It would takes several hours to execute them.) (Creating SP:s…
UlfL
  • 59
  • 1
  • 3
1
vote
2 answers

Schema binding for a UDF using tables from other db

I have a UDF in SQL 2005 that I would need to schemabind as it is used in a view that I need an index on. This UDF gets information from a table that is located in a different db (same server) then the one where the UDF is located. Since it is…
MarkyMarc
  • 45
  • 6
0
votes
0 answers

what is indexview give me example?

explain the index view with a --tag-name- real-time scenario index view: when we create an index on a view that particular view is known as the index view. there are some conditions to create an index view: the view must be created with schema…
Mandeep
  • 1
  • 1
0
votes
0 answers

WITH SCHEMABINDING on Azure SQL Server Views

I am trying to speed up an Azure SQL Database View and I have read that I should start by using WITH SCHEMABINDING, however I always get the same error: Parse error at line: 1, column: 28: Incorrect syntax near 'SCHEMABINDING'. I get the same error…
Ivan
  • 1
  • 1
0
votes
1 answer

Cannot specify a sql CLR type in a Schema-bound object or a constraint expression - SQL - WITH SCHEMABINDING

I have a UDF function and I found some tips to get better performance: https://www.mssqltips.com/sqlservertip/5864/four-ways-to-improve-scalar-function-performance-in-sql-server/ I tried them all but I cannot get 'WITH SCHEMABINDING' to work. Error:…
saho
  • 63
  • 5
0
votes
1 answer

Read/Write SQL Server views in MS Access using SCHEMABINDING

I am having to redevelop an MS Access 2003/2010 ADP project, that uses SQL Server views as the RecordSource for all its forms, into an MS Access 2016 ACCDB. I have tried using pass through queries to get the data and this works fine for readonly…
Ronnie1001
  • 1
  • 1
0
votes
1 answer

How to disable view created with Schemabinding before update or delete operation on reference tables

I have a view that created with 'WITH SCHEMABINDING' and it has one unique clustered index, three different non-clustered indexes. The problem is that before this view created, update or delete operation (affecting more than 1000 rows) takes less…
yenicead
  • 15
  • 5
0
votes
0 answers

How to alter a view with SCHEMABINDING when other views are dependent on it

We have views created with SCHEMABINDING and they are used in other views that also have SCHEMABINDING I want to alter the base view to include newly added columns of the table but it is not allowing to alter it saying that other views are dependent…
Pawan Nogariya
  • 8,330
  • 12
  • 52
  • 105
1
2