Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
651
questions
92
votes
4
answers
83k
views
Are views harmful for performance in PostgreSQL?
The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6):
The danger with using views is filtering a query against a view,
expecting to read a very ...
- 1,399
71
votes
6
answers
186k
views
How to list all views in SQL in PostgreSQL?
How do I list all views for a database using an SQL command in PostgreSQL?
I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g.,
SELECT ...
- 2,093
66
votes
3
answers
92k
views
When to use views in MySQL?
When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table?
One reason that I would prefer to use views is that the database schema ...
- 3,132
63
votes
6
answers
88k
views
Why not use a table instead of a materialized view?
I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some ...
- 5,333
47
votes
5
answers
44k
views
Is nested view a good database design?
I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
44
votes
3
answers
45k
views
Replace a materialized view in Postgres
I have a materialized view in Postgres 9.3 that I'd like to update with new columns. However, other materialized views also depend upon this view, and the error message indicates that dropping a view ...
- 747
34
votes
3
answers
36k
views
Are Views optimized when I add a WHERE clause to them?
Does it make a difference if you filter a View inside or outside the View?
For example, is there any difference between these two queries?
SELECT Id
FROM MyTable
WHERE SomeColumn = 1
Or
SELECT Id
...
- 8,437
33
votes
7
answers
75k
views
Modify DEFINER on Many Views
I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result.
Got error: 1449: The user ...
- 483
31
votes
4
answers
5k
views
Does SQL Server Only Perform Calculations In A SELECT List Once?
Take the following example:
SELECT <CalculationA> As ColA,
<CalculationB> As ColB,
<CalculationA> + <CalculationB> As ColC
FROM TableA
Would CalculationA and ...
- 421
31
votes
4
answers
69k
views
Best way to create a materialized view in MySQL
I am using MySQL 5.6. I am not being able to a create materialized view like I can in Oracle. I have seen one or two solutions like Flexview.
Can anybody tell me best way to create a materialized ...
- 561
29
votes
5
answers
30k
views
How do I switch off SCHEMABINDING for a view without recreating it?
How do I switch off SCHEMABINDING for a view without recreating it?
- 6,702
26
votes
1
answer
11k
views
Execution plan shows expensive CONVERT_IMPLICIT operation. Can I fix this with indexing or do I need to change the table?
I have a really important, really slow view which includes some really ugly conditions like this in its where clause. I am also aware that the joins are gross and slow joins on varchar(13) instead of ...
- 1,087
22
votes
6
answers
3k
views
Is there a generic term for tables and views?
I am looking for a generic term, e.g. for a database abstraction, that includes all tabular data structures like database tables, views, tabular query results aso.
As far as I understand, 'entity' is ...
- 331
21
votes
4
answers
23k
views
Determining how a schema change occurred?
Something bad happened yesterday.
A view that was created sometime back ago was modified by someone which eventually broke the reports. Unfortunately. somebody (knowingly or unknowingly) did this ...
- 577
20
votes
1
answer
1k
views
SQL Server - Handling localization of strings in nested non-deterministic view stacks
While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A ...
- 1,251
18
votes
2
answers
11k
views
Why is a NOT NULL computed column considered nullable in a view?
I have a table:
CREATE TABLE [dbo].[Realty](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RankingBonus] [int] NOT NULL,
[Ranking] AS ([Id]+[RankingBonus]) PERSISTED NOT NULL
....
)
And a ...
- 533
18
votes
1
answer
33k
views
PostgreSQL : drop column from view
I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last ...
- 1,571
17
votes
1
answer
19k
views
How to check/know the highest run queries
I would like to gather statistics on the highest run queries in my database.
For that I need to know how I can track queries so I can create views or materialized views to cache the results of the ...
- 375
16
votes
2
answers
96k
views
How to add a user with access to a single view?
I'm working with MSSQL Server Management Studio 2008 and I need to expose a view to a third party for their data reconciliation. I have created the appropriate view but I'm having trouble creating a ...
- 161
16
votes
1
answer
12k
views
How do you represent VIEWS in an ERD?
Are VIEWS (CREATE VIEW) represented in an ERD? If so, how do you represent them and specify what tables they came from?
- 1,702
16
votes
1
answer
561
views
In what order does PostgreSQL check object permissions?
Given a database role, user1, a function something() defined as a stored procedure and a view created like:
CREATE VIEW view1 AS select * from something()
And, given this permissions:
REVOKE ALL ON ...
- 163
14
votes
2
answers
51k
views
Transact SQL using WITH in CREATE VIEW
I want to create VIEW using WITH clauses, but really can't find any references on correct syntax.
I want smth like this
WITH TempTbl AS (SELECT ...)
CREATE VIEW SomeView
SELECT *
FROM TempTbl
And ...
- 323
14
votes
2
answers
15k
views
PostgreSQL - Create view with autoincremental column
I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N.
Is this possible to do without effecting the ...
- 285
14
votes
5
answers
9k
views
How do I query this 20 million record view faster?
For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...
- 365
14
votes
2
answers
39k
views
Will mysqldump command also backup all the views that are present in DB or there is a special command for it?
I am trying to backup my entire database along with the views. I am going to use mysqldump command. I was wondering if that will copy all the views or I will have to use separate command for it.
...
- 555
14
votes
1
answer
35k
views
How to get mysql view definitions?
Show create view viewName does not display correctly formatted text. How can I get the correctly-formatted SQL?
For example, I created a view like create view v1 as select * from mysql.uer, but it ...
- 141
14
votes
1
answer
21k
views
Access view based on table in another database without account in that other database
I created view in database1 based on tables in database2. I gave SELECT permission to a user who has access only to database1. The user can't get this view to work because he doesn't have an account ...
- 141
13
votes
2
answers
1k
views
ALTER VIEW drops Index from View
as the header line suggests: Using an ALTER VIEW statement on a VIEW that has an index on it will drop this (all?) indexes from the VIEW without warning. I would prefer the ALTER VIEW statement to ...
- 233
13
votes
1
answer
5k
views
Indexed view not being used - SQL Server [duplicate]
I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and ...
- 273
13
votes
1
answer
15k
views
Does view take advantage of table indexes
I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table
create view dbo.MyView
as
Select * from MyTable
Now, when I am using this ...
- 761
13
votes
2
answers
7k
views
Tracking current user through views and triggers in PostgreSQL
I have a PostgreSQL (9.4) database that limits access to records depending upon the current user, and tracks changes made by the user. This is achieved through views and triggers, and for the most ...
- 1,680
12
votes
5
answers
13k
views
Why does this UPDATE fail with a unique key constraint violation?
I'm an "accidental" DBA, relatively inexperienced and baffled by this problem.
Running MS SQL Server 2012. The problem is with this UPDATE statement:
UPDATE dbo.tAccts SET
Ticket ...
- 121
12
votes
3
answers
12k
views
What are the alternatives for an ORDER BY clause in a View?
This question just had to be in this site :)
ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view.
I know that there are ...
- 357
11
votes
2
answers
9k
views
Does a view need its own foreign key constraints?
Disclaimer: I'm a programmer, not a DBA, so bear with me...
I have a view that I use to just map 2 entities together. I have to do a join between a few different tables to get that:
CREATE OR ...
- 213
11
votes
1
answer
4k
views
noexpand hint for non-enterpise edition and performance
I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says:
Indexed views can be created in any
...
- 6,702
11
votes
1
answer
461
views
Why can you only drop a SQL Server view in the current database?
Just noticed this and was having a hard time coming up with a technical reason for it.
Tables can be dropped using three part names, but views are restricted to two part names.
What is the ...
- 663
10
votes
2
answers
10k
views
What's the difference between a v$ view and its dba_ equivalent?
What's the general difference between a v$ view and its dba_ equivalent? Take, for example, v$tablespace and dba_tablespaces.
- 2,602
10
votes
3
answers
8k
views
Oracle: How do I query a Hierarchical table?
Background
This is for the construction of some views we'll be using for reporting.
I have an table of locations, the key fields being "location" and "parent".
The structure that these two fields ...
- 427
10
votes
2
answers
12k
views
How do I get the execution plan for a view?
I have a schema with a number of views. I need to check the execution plans to make sure the appropriate indexes are in place and being used.
How do I do this?
I'd rather not have to copy and paste ...
- 1,308
10
votes
2
answers
33k
views
Grant permissions on views, deny select on tables
I have a MySQL user and I want it to view ONLY the views I want and not any other table in the database. I've granted this user permissions only on certain views as following:
GRANT SHOW VIEW ON `...
- 203
10
votes
1
answer
37k
views
Using a linked server with OPENQUERY in a database project
I have a SQL Server 2008 running a database I want to throw in TFS. Therefore I used a Visual Studio 2013 database project where I imported the DB. After fixing a bunch of errors I'm stuck with only ...
- 191
10
votes
2
answers
2k
views
Window functions cause awful execution plan when called from a view with external parametrized 'where' clause
I had this issue long time ago, I found a workaround which suited me and forgot about it.
But now there's that question on SO so I'm willing to bring this problem up.
There's a view that joins few ...
- 1,342
10
votes
1
answer
5k
views
SELECT COUNT(*) on a view is orders of magnitude slower than SELECT * on the same view
The View
CREATE VIEW [dbo].[vProductList]
WITH SCHEMABINDING
AS
SELECT
p.[Id]
,p.[Name]
,price.[Value] as CalculatedPrice
,orders.[Value] as OrdersWithThisProduct
FROM
products ...
- 1,677
10
votes
1
answer
6k
views
Postgresql Rename Table without Updating View Definitions
I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off?
So I rename the ...
- 203
9
votes
3
answers
5k
views
Performance of Inline-TVF vs. Views
I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together ...
- 235
9
votes
1
answer
19k
views
Why is it not possible to alter a view with a "CREATE OR REPLACE"?
The Postgresql documentation is clear on this point: you can not change the columns of an existing view.
The new query must generate the same columns that were generated by the existing view query (...
- 1,443
9
votes
4
answers
70k
views
Problem with mysqldump and view
When using mysqldump to backup MySQL, I got following error.
mysqldump --all-databases --routines >> all.sql
mysqldump: Couldn't execute 'show table status like 'hdkien'': SELECT command denied ...
- 227
9
votes
2
answers
5k
views
How can I help SQL Server recognize my indexed view column is NOT NULL-able?
I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes):
CREATE VIEW dbo.balances
WITH SCHEMABINDING
AS
SELECT
user_id
...
- 14.6k
9
votes
1
answer
1k
views
Materialized views in oracle
How are materialized views implemented in oracle? I've created a materialized view in oracle. What I now see is a quite normal table with a job. Are materialized views translated into tables & ...
- 1,089
9
votes
3
answers
433
views
How to improve estimate of 1 row in a View constrained by DateAdd() against an index
Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).
Given a table and index:
create table [User].[Session]
(
SessionId int identity(1, 1) not null primary key
CreatedUtc ...
- 2,066