Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
650
questions
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
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
3
votes
1
answer
328
views
Views - Indexes/Keys/Execution Plans
I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server.
There is a ...
- 31
0
votes
1
answer
12k
views
sql server - could not allocate space for object
I am trying to retrieve the first 1000 records of my view and am getting this error message:
Msg 1105, Level 17, State 2, Line 2
Could not allocate space for object 'dbo.SORT temporary run ...
- 245
4
votes
2
answers
9k
views
sql 2008, select from a view as a different user, like owner
In sql server 2008, the system views sys.database_role_members and sys.database_principals, only returns the users and group that the current account is allowed to view.
I need to check id a certain ...
- 327
2
votes
1
answer
1k
views
Using View filters for tenants in SQL-Server 2008
I'm currently creating a Multi-tenant data architecture in a SaaS BI-product.
The product are using shared database and shared schema, but with tenant view filters and tenant data encryption
(...
- 759
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
6
votes
1
answer
102k
views
Oracle SQL create view privileges
This is more of a theoritical question but I need help ASAP. Here it is:
With the appropriate use of SQL queries, grant as system
administrators the required access privileges to a database user ...
- 181
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
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
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
2
votes
3
answers
4k
views
View Design Best Practices
When designing a view, would it be best to retrieve
the ID fields where FK relationships exist between the joined tables
the desired description field obtained through the FK id relationship
both?
...
- 123
1
vote
1
answer
192
views
How should row-specific metadata be created handled for an outer join view?
I have two tables, a large (millions) table of Tickets for orders, and a small (hundreds of thousands) table for Invoices metadata on a subset of those tickets. The metadata is information about when ...
- 2,063
8
votes
1
answer
4k
views
why are people advised not to update through views
During College it was banged into our heads to not update tables through views, and again when I got into the workplace we where told do not update the database through views.
Where there/Are there ...
- 203
2
votes
1
answer
2k
views
View metadata refresh in MySQL
Like other RDBMS, view meta data is stored at view creation time.
From CREATE VIEW in the MySQL docs
The view definition is “frozen” at creation time, so changes to the underlying tables ...
- 69.7k
2
votes
2
answers
14k
views
Primary Key on a View
While answering this question
https://stackoverflow.com/questions/4668146/how-to-add-a-primary-key-on-a-oracle-view/8558346#8558346
A lot of questions popped in my mind.
1. Is it advisable to have ...
- 337
2
votes
1
answer
807
views
Virtual Column - Using it in view (Oracle)
I have made a virtual column in Oracle, using the following code.
CREATE OR REPLACE function email_address (ID_ varchar2)
return varchar2
deterministic
as
lname varchar2 (256);
snumber varchar2 ...
- 337
2
votes
1
answer
378
views
Why are views not copied over when using SQL Server Log Shipping?
I have 2 Sql Servers (2008) with Log Shipping setup between them. The receiving server is configured to keep the database in Read-Only mode. When creating a view on the primary server, this view is ...
- 143
1
vote
1
answer
51
views
building a view w/ fields that only contain a certain word
Trying to build a simple SQL query... I have a table filled with "Yes" and "No"
I want a view that only shows me records that contain "No"
SELECT PhysicalBuild.Name AS Name,
PhysicalBuild.Labels ...
- 515
1
vote
1
answer
230
views
Add info to mysql view around an IP address - 10.10.1.100 to https://10.10.1.100
I have built a database in MySQL to replace the use of MANY excel spreadsheets that document our servers, network, IP addresses, etc.
In that database I have created a view called DRAC (dell remote ...
- 515
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
3
votes
1
answer
139
views
Is it possible to create a database called C that is a view into all tables in database A + B?
Using SQL 2005 or 2008, is it possible to create a database called C that is a view into all tables in database A + B ? Databases A, B, and C are on the same local server.
I am wanting to be able ...
- 153
1
vote
2
answers
2k
views
Allow db_owner to add/view existing logins to database
On our development server (SQL 2008 R2) we want local dbo's to be able to add existing logins on the server to the database. The problem is they can't view them, they can only see themselves in the ...
- 111
8
votes
3
answers
4k
views
Creating a view on a non-existing view
I have a whole bunch of views that I need to create. Many of these views depend on other views.
If I create a view that depends on a view not yet created, I get an Invalid Object error.
Rather than ...
- 581
2
votes
2
answers
801
views
What reason is there to create a physical table for a report output?
I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view ...
- 2,063
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 ...
- 193
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
5
votes
4
answers
23k
views
How to create a view that refresh automatically
I was triying to create a View by the simplest way like this:
Use SoccerDB;
GO
CREATE VIEW ExampleDBaseII
AS
SELECT ID, Cast(Name AS Varchar) as Name,Cast(City AS Varchar) as City,
FROM Team
GO
How ...
- 281
1
vote
2
answers
3k
views
SQL Server 2000: Can't DROP VIEW?
I have SQL Server 2000. I want to clean it by dropping obsolete and unused objects. One of the views causes me an error. I try to give more information but I still have no clue what the cause is.
...
0
votes
2
answers
12k
views
Hide column if any value is null or zero via proc sql or view [closed]
Given either of the two following datasets
Kağıt Cinsi Sarı Yeşil Mavi
Kalın 4 0 5
İnce null 0 3
or
Kağıt Cinsi ...
- 1,078
4
votes
2
answers
3k
views
Set permissions on a view
I'm creating a view on a SQL Server 2005 database via SQL Server Management Studio 2008, the view reads from two tables. The view is accessed from a web application and I want users to be able to ...
- 2,063
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
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
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 ...
4
votes
2
answers
3k
views
sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups
These catalog views seem to show the exact same data. What is the point of having multiple system views that seem to show the same thing?
Here is how I proved it is the same data:
select
fil....
anon
3
votes
2
answers
950
views
Join condition on complex views
If i join two/more complex views (views that are made up by joining two/more tables); would it result in multiple iteration of reads on the base tables?
Would it not be more efficient just to have ...
- 145
3
votes
1
answer
933
views
Tablespace consumed by views
I was in an SQL class where the instructor said "DBAs don't like it when users create views, because they will clobber tablespace."
Obviously, I understand that any schema object will consume some ...
7
votes
3
answers
28k
views
When creating views, why do users need direct object permissions if they already have the same permissions via a role?
I'm having a problem with permissions in Oracle 10g. I'm hoping someone can help me make sense of this.
I have a schema with a table in it. I have granted select on that table to a role.
grant ...
- 173
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
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
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
5
votes
3
answers
4k
views
Optimizing query using view on EAV structure
An application is writing into a database that follows an EAV structure, similar to this:
CREATE TABLE item (
id INTEGER PRIMARY KEY,
description TEXT
);
CREATE TABLE item_attr (
item ...
- 1,525
3
votes
2
answers
556
views
Search on part of a column in a SQL Server cross-database view
I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views ...
- 133
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
8
votes
1
answer
5k
views
Do I need to enumerate the columns used in a materialized view when creating the MV log?
I'm currently creating materialized view logs on a whole bunch of tables in order to support materialized views with FAST REFRESH. The materialized views we're creating contain approx 30 columns from ...
- 451
3
votes
1
answer
2k
views
access all_mview or user_mviews from app code
I need to access the user_mviews or all_mviews table to get meta information about materialized view in my app. Which grants are necessary to read the user_mviews table?
- 1,089
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
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
8
votes
1
answer
6k
views
Oracle materialized view refresh schedule
Since I see that it is possible to specify an expression like sysdate + 1, here are my questions:
Is it possible to specify the exact time of the refresh for materialized views in oracle?
Is it ...
- 1,089
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