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
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 ...
Zoredache's user avatar
  • 483
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 ...
Bhupendra Pandey'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
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
2 votes
2 answers
2k views

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT [closed]

I have a pretty large query in a view (let's call it a_sql), that is really fast unless I use ORDER BY in an outer SELECT with a small LIMIT: SELECT customs.id AS custom_id, customs.custom_name AS ...
Marco Marcianesi's user avatar
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 ...
ARX's user avatar
  • 1,399
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 ...
Richard Sayakanit's user avatar
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 ...
mamesaye's user avatar
  • 375
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,702
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 `...
Metafaniel's user avatar
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
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.6k
7 votes
2 answers
27k views

SQL SERVER - Linked Server and query performance

I have a performance problem related to SQL Server/linked servers/views. I hope you can help me understand what is the best way to do what I want =). I have a database K with 3 linked servers L1,L2,...
ced's user avatar
  • 93
4 votes
2 answers
53k views

How to generate list of dates --- ie every day for the past 2 years?

We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value. I'm wondering what's the simplest way to ...
user45867's user avatar
  • 1,651
4 votes
1 answer
30k views

How to pass a parameter into a function

At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date? Also is it good ...
alexsmn's user avatar
  • 353
3 votes
3 answers
8k views

Determining what algorithm MYSQL View is using

When creating a MySQL View, the algorithm is set to UNDEFINED. How do I see which algorithm MySQL is choosing for the query ? I tried using "EXPLAIN", but it is not obvious from the results what ...
Lawrence Cooke's user avatar
2 votes
2 answers
4k views

If view A is built atop other views and tables, does a user need permission to only view A, or everything down?

I'm talking about SQL Server in particular. I suppose a user tried it and got an error (you don't have select permissions on hidden underlying view B not explicitly mentioned in query). The main view ...
user45867's user avatar
  • 1,651
2 votes
2 answers
3k views

Generate SQL Table DDL from a View

How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql (1) can remove primary key/constraint, (2) ...
user avatar
2 votes
2 answers
3k views

MySQL Views Authorization

Questions: Is there a way, using SQL, to identify how many views are in the database per user? How can you authorize/deny users access to delete views created by another user?
neeraj's user avatar
  • 260
2 votes
1 answer
3k views

Why am I getting different results from a query vs. a view

The base problem I'm facing is that I need the latest record for each item. A little setup... MySQL 5.6.14. I need to create two views (because MySQL won't let me have a subquery in a view). My ...
markdotnet's user avatar
2 votes
2 answers
4k views

Creating a row for each value in a comma-separated string

I'm currently trying to create a View from a Table that does the following: Table A ╔═════════════╦═══════════════╗ ║ PART NUMBER ║ FILE NAME ║ ╠═════════════╬═══════════════╣ ║ PN0001-01 ║ ...
ErickM's user avatar
  • 73
2 votes
1 answer
755 views

Full outer joins, coalescing, indexes and views

I have two tables, let's call them TableA and TableB. TableA TableB ------ ------ ID ID Date Date Foo Bar Both tables ...
Shaul Behr's user avatar
  • 2,913
1 vote
1 answer
713 views

How to mimic a full outer join using mysql views?

I have all kinds of views in mysql with employee data. I have tried to use methods like this -https://stackoverflow.com/questions/2384298/why-does-mysql-report-a-syntax-error-on-full-outer-join but ...
LOSTinDB's user avatar
  • 551
1 vote
1 answer
383 views

Recursive Parent-Child Location Relationship Cache as View

Here is my data structure: CREATE TABLE Locations( LocID int IDENTITY(1,1) NOT NULL, LocationTypeID int NOT NULL, ParentID int NULL, LocNum varchar(50) NULL, LocName varchar(250) ...
David Allen's user avatar
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 ...
Rob Bednark's user avatar
  • 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 ...
David LeBauer's user avatar
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 ...
John's user avatar
  • 747
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
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. ...
vinny's user avatar
  • 555
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
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 ...
Yoseph's user avatar
  • 203
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,308
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
1 answer
3k views

Does sql server calculate views every time when someone query the views?

I wonder whether sql server calculate views every time when someone query the views. It is because everytime I query the views, it take such a long time. And when I tried to shorten the time by ...
lamwaiman1988's user avatar
8 votes
1 answer
230 views

Query is fast but becomes sluggish when created as a view

Execution Plan as Query SELECT VP.Branch , VP.ROUTE AS Route , VP.SAPCustomerID , S.CustomerID , S.ProductID , S.Date -- Group by Customer , CustomerQuantity = SUM(...
Shelby115's user avatar
  • 487
8 votes
2 answers
18k views

How to make a union view execute more efficiently?

I have a large table (tens to hundreds of millions of records) that we have split for performance reasons into active and archive tables, using a direct field mapping, and running an archive process ...
Shaul Behr's user avatar
  • 2,913
7 votes
1 answer
422 views

View designer strange join syntax

I have a data warehouse with a lot of views built in with the SSMS view designer. Viewing the syntax, the FROM clauses are unintelligible to me because the ON statements do not follow straight after ...
gregn's user avatar
  • 303
6 votes
2 answers
638 views

view corrupted when viewed using design feature

All, I had a very strange thing happen last Friday. We have a table that is updated on a daily basis using a job that fires a proc that utilizes a view. The columns were being transposed from the view ...
Paul_J's user avatar
  • 63
6 votes
1 answer
7k views

Are there issues with using SELECT * in a view definition?

I am creating a number of proxy views for views in a separate database. To avoid defining the view in two places, I want to use SELECT * to create the proxy view. I've searched for reasons not to do ...
Metaphor's user avatar
  • 890
6 votes
1 answer
13k views

Create a view that dynamically UNIONs new tables

I have a database named DDOS_WAF_REPORT that has multiple tables with the name format dbo.DDOS_WAF_ACCOUNT_###### (a 5 or 6 digit ID number). All the tables have the exact same structure (both column ...
Ariel Hayoun's user avatar
6 votes
1 answer
7k views

need suggestions to improve view performance

I am looking to improve the performance of a view in SQL Server 2008. This view exists in a reporting database that is widely used by not-very-technical-folk to basically denormalize all of these ...
JHFB's user avatar
  • 2,844
5 votes
2 answers
14k views

Problem with View When Restoring a MySQL Backup

I have a database (InnoDB) with some tables, views and routines... Periodically I do a backup using the command line: mysqldump -u user -ppassword --routines db_name > backup.sql But I have a ...
Marcx's user avatar
  • 151
5 votes
1 answer
2k views

Select Permissions Across Multiple Schemas Within the Same Database

I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, Excel and SSRS. The designated user will only have access to the view and no ...
Tim's user avatar
  • 53
5 votes
1 answer
2k views

Get list of functions used in a view

Say I have a Function like this: create function house_analysis(ingeo geometry) returns table(count_all numeric, count_important numeric) as $$ select count(*), count(*) filter (where h....
thomjah's user avatar
  • 115
5 votes
2 answers
7k views

Find and replace database name in views and stored procedures

I'm in a bit of a predicament. There are over 1,000 views and stored procedures in a handful of databases that are hard-coded to go after another database. In my example, the database names in SQL ...
Connor's user avatar
  • 353
5 votes
1 answer
6k views

Grant select to dbo view without select on non dbo tables

SQL Server 2008 R2 We have a login & user called "JoeBlogs", this user has it's own default schema of JoeBlogs - it has Public access only to the database. We then granted it SELECT on a dbo ...
HeavenCore's user avatar
4 votes
2 answers
12k views

How can I define the datatype of a view in MySQL?

I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side. Here's a simplified query for the sake ...
OmerGertel's user avatar
4 votes
1 answer
7k views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...
SeanKilleen's user avatar
4 votes
2 answers
5k views

Improving performance of nasty nested-view joins

I have a moderate-sized database spread out over a few tables, the rough architecture is: Input Data (Data ID, Session ID and a few fields of statistical importance) Input File (Data ID and a blob) ...
voretaq7's user avatar
  • 1,557
4 votes
2 answers
6k views

Postgres GROUP BY id works on table directly, but not on identical view

Lets say I have two tables. One of them references the other in a one-to-many relationship: CREATE TABLE t ( id SERIAL PRIMARY KEY , name text ); INSERT INTO t (name) VALUES ('one'), ('two'), ('...
Avery's user avatar
  • 143