Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
68
questions
33
votes
7
answers
78k
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
32
votes
4
answers
75k
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 ...
- 571
21
votes
4
answers
24k
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
14
votes
1
answer
22k
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
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 ...
97
votes
4
answers
92k
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,479
48
votes
5
answers
46k
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 ...
17
votes
1
answer
23k
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
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,752
10
votes
2
answers
3k
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,353
10
votes
2
answers
35k
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
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.7k
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,...
- 93
4
votes
1
answer
32k
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 ...
- 353
4
votes
2
answers
55k
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 ...
- 1,693
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 ...
- 183
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 ...
- 121
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?
- 260
2
votes
2
answers
5k
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 ...
- 1,693
2
votes
1
answer
793
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 ...
- 2,933
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 ║ ...
- 73
2
votes
2
answers
4k
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) ...
user162241
1
vote
1
answer
746
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 ...
- 551
1
vote
1
answer
480
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) ...
- 127
75
votes
7
answers
205k
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,203
66
votes
3
answers
93k
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,162
45
votes
3
answers
51k
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 ...
- 757
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
15
votes
2
answers
41k
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.
...
- 565
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
11
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 ...
- 213
10
votes
2
answers
13k
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,328
9
votes
1
answer
4k
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 ...
- 193
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,099
9
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 ...
- 2,933
8
votes
1
answer
241
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(...
- 487
7
votes
1
answer
438
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 ...
- 303
6
votes
2
answers
686
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 ...
- 63
6
votes
1
answer
8k
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 ...
- 906
6
votes
1
answer
14k
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 ...
- 85
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 ...
- 2,874
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 ...
- 53
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 ...
- 786
5
votes
2
answers
15k
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 ...
- 151
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....
- 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 ...
- 353
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'), ('...
- 143
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 ...
- 141
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 ...
- 427
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)
...
- 1,607