Questions tagged [view]

A virtual table that represents the data in one or more tables in an alternative way.

Filter by
Sorted by
Tagged with
92 votes
4 answers
87k 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 ...
ARX's user avatar
  • 1,419
73 votes
7 answers
193k 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 ...;...
Rob Bednark's user avatar
  • 2,153
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 ...
David LeBauer's user avatar
63 votes
6 answers
89k 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 ...
jrara's user avatar
  • 5,333
48 votes
5 answers
45k 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 ...
Richard Sayakanit's user avatar
45 votes
3 answers
48k 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 ...
John's user avatar
  • 757
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 ...
Rachel's user avatar
  • 8,457
33 votes
7 answers
76k 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 ...
Zoredache's user avatar
  • 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 ...
Gravitate's user avatar
  • 421
31 votes
4 answers
71k 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 ...
Bhupendra Pandey's user avatar
29 votes
5 answers
31k 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?
garik's user avatar
  • 6,712
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 ...
Warren  P's user avatar
  • 1,087
23 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 ...
Pinke Helga's user avatar
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 ...
xorpower's user avatar
  • 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 ...
beeks's user avatar
  • 1,251
19 votes
1 answer
34k 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 ...
Yanick Rochon's user avatar
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 ...
Tomas Kubes's user avatar
17 votes
1 answer
20k 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 ...
mamesaye's user avatar
  • 375
16 votes
2 answers
98k 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 ...
velojason's user avatar
  • 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?
leeand00's user avatar
  • 1,702
16 votes
1 answer
573 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 ...
santios's user avatar
  • 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 ...
bazzilic's user avatar
  • 323
14 votes
2 answers
16k 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 ...
code base 5000's user avatar
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 ...
balu's user avatar
  • 365
14 votes
2 answers
40k 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. ...
vinny's user avatar
  • 555
14 votes
1 answer
37k 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 ...
user2400825's user avatar
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 ...
tom's user avatar
  • 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 ...
Ralf's user avatar
  • 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 ...
Manish Mulani's user avatar
13 votes
1 answer
16k 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 ...
Zerotoinfinity's user avatar
13 votes
2 answers
8k 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 ...
beldaz's user avatar
  • 1,690
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 ...
ArleyD's user avatar
  • 121
12 votes
3 answers
13k 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 ...
yellowblood's user avatar
11 votes
2 answers
10k 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 ...
Troy's user avatar
  • 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 ...
garik's user avatar
  • 6,712
11 votes
1 answer
506 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 ...
IronicMuffin's user avatar
11 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 ...
Nate's user avatar
  • 1,687
10 votes
1 answer
20k 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 (...
Stephan's user avatar
  • 1,463
10 votes
3 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.
Just a learner's user avatar
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 ...
SeanKilleen's user avatar
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 ...
Matt Fenwick's user avatar
  • 1,318
10 votes
2 answers
34k 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 `...
Metafaniel's user avatar
10 votes
1 answer
38k 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 ...
Chake's user avatar
  • 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 ...
GSerg's user avatar
  • 1,342
10 votes
1 answer
7k 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 ...
Yoseph's user avatar
  • 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 ...
FistOfFury's user avatar
9 votes
4 answers
71k 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 ...
favadi's user avatar
  • 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 ...
Nick Chammas's user avatar
  • 14.7k
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 & ...
paweloque's user avatar
  • 1,089
9 votes
3 answers
437 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 ...
crokusek's user avatar
  • 2,120

1
2 3 4 5
14