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

learn more… | top users | synonyms

4
votes
1answer
998 views

Determining whether column in view can only contain unique values

I have a SQL Server 2008 database with several views. I'm looking for a way to definitively determine whether a particular view has a column that can only contain unique values (i.e. can be used as a ...
4
votes
1answer
86 views

Performance problems with TOP and user defined functions in views

I have problem with performance of queries on simple query on view. To keep things simple, assume my view is defined as follows: CREATE VIEW [mon].[ViewDevicesWithGroups] AS SELECT Id, Name, ...
4
votes
2answers
4k 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 ...
4
votes
2answers
2k views

SQL Server Linked Server to Progress is slow with an openquery view

We have a SQL Server database setup with a Linked Server setup connecting to a Progress OpenEdge database. We created a SQL Server view (for using with SSRS) of some of the OpenEdge tables using code ...
3
votes
3answers
11k 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 ...
3
votes
2answers
491 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 ...
3
votes
2answers
97 views

Use a view to join tables or a stored procedure for a better execution plan?

First let me state I am a C# developer, not a SQL Server DBA, so please excuse my ignorance on SQL Server database query execution plans. What I want to know is for a stored procedure which the ...
3
votes
3answers
348 views

Why set up static data in views vs. using tables in mysql?

I get an LDAP feed nightly. I get it as a text file and dump/create my LDAPALL table. There are roughly 75K employees times about 50 fields. I have the following too: LDAPIMPORTANT - view that ...
3
votes
1answer
135 views

Error when using views on a linked server

I have this query: SELECT DescTipoentrevistador FROM Alfabase.dbo.tblTipoEntrevistador WHERE CodTipoEntrevistador IN ( SELECT CASE WHEN EXISTS ...
3
votes
1answer
1k views

Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue?

I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express ...
3
votes
1answer
647 views

Mysql slow select from a view

I would appreciate an approach with this problem, I have a lottery system in mysql, this system receives a lot of transaction per second.. a lot!. I need to check the total amount for each the number ...
3
votes
1answer
110 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 ...
3
votes
1answer
9k 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 ...
3
votes
1answer
786 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 ...
3
votes
1answer
461 views

Can indexing views in SQL Server 2005+ speed up SELECT count(*) from a View?

My initial question was How does someone determine if it is a good idea to index a view?. Update: My underlying assumption was that indexing views speeds them up. This is apparently not sensible, ...
3
votes
2answers
2k 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 ...
3
votes
1answer
2k views

Best way to create materialized view in mysql

I am using mysql 5.6, I am not being able to create materialized view here like oracle. I got one or two solution like flexview and all... Can anybody tell me best way to create materialized view in ...
3
votes
2answers
9k 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 ...
3
votes
1answer
5k views

Oracle: How to create a not null column in a view

Given a table like: CREATE TABLE "MyTable" ( "MyColumn" NUMBER NOT NULL ); I want to create a view like: CREATE VIEW "MyView" AS SELECT CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn" FROM ...
3
votes
1answer
122 views

Slow MySQL query, not sure why

I'm working with an intranet application for property tax collection, and I've hit a snag. The following view runs much slower than I'd expect: create or replace view propertytaxessub0 as select ...
3
votes
1answer
7k 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 ...
3
votes
1answer
120 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 ...
3
votes
1answer
4k 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 ...
3
votes
0answers
38 views

Possible SQLite bug?

I am running into an issue with SQLite that I think might be a bug. When I run the PRAGMA table_info command on a view that uses an aggregate function, the data type always comes up empty. To ...
2
votes
2answers
245 views

Why the WHERE clause is not pushed down in the view's query?

With Postgres 9.4, I'm doing the following query quite often: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA ...
2
votes
1answer
48 views

How to use a view to span two tables that are identical

We have table A that receives data from our shop floor and we currently have it so that the data is moved from table A to table B after 5 years. I want to streamline this but need help creating a ...
2
votes
2answers
531 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
votes
3answers
1k views

do MYSQL views occupy physical space?

do MYSQL views occupy physical space? Or in other words, is a MYSQL database limited to a certain number of views?
2
votes
2answers
750 views

View column has an 8000 character limit?

In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 ...
2
votes
1answer
2k 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 ...
2
votes
2answers
1k 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
votes
1answer
1k views

Can a view affect ALTER TABLE commands?

I provide a database system to a number of customers. The database is installed locally on Oracle. We periodically upgrade the database structure (either adding new fields, renaming old fields, ...
2
votes
3answers
1k 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? ...
2
votes
2answers
82 views

View Disc Usage

Is this statement correct? Behind the scenes SQL Server actually stores the data in the view as a physical table which must be updated if any of the data behind the view changes I did some ...
2
votes
2answers
1k views

IN Clause causes Execution plan to change from Nested Loops to Hash Match

I'm tuning a query and have discovered some behaviour I'm not clear about. If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes. There only 7 rows returned in the ...
2
votes
1answer
68 views

Find substrings within between 2 string fragments

I am trying to populate a view in PostGIS in Postgres 9.0 and in this view I want it to contain a substring based on 2 string positions. See below for my code. CREATE OR REPLACE VIEW vw_actions AS ...
2
votes
1answer
85 views

PostgreSQL JSON data type used as NOSQL, But VIEW as Relational Data Structure

Could I create a VIEW from the JSON data type but have it as a relational structure? Example: SELECT * FROM json_test; Results [ { "name": "Roy", "Country": "USA", ...
2
votes
1answer
129 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 ...
2
votes
1answer
990 views

View with parameters in Sybase SQL Anywhere

A view with Sybase engine cannot take parameters. Is there a way to do this?
2
votes
1answer
3k views

In creating view use SQL Security definer or invoker?

CREATE VIEW syntax is CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name ...
2
votes
2answers
131 views

Migrating SQL Server 2000 to SQL Server 2008

I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause? Thanks.
2
votes
1answer
853 views

Speeding Up Querying Views

We've got a complicated stored procedure. The biggest pain point of the procedure is a join on a complicated view--it has unions and subqueries. I can't add indexes to it. So, I want to cheat. By ...
2
votes
1answer
24k 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 ...
2
votes
2answers
5k views

Primary Key on a View

While answering this question How to add a Primary Key on a Oracle view? A lot of questions popped in my mind. 1. Is it advisable to have primary key in a view. 2. Why anybody be intrested in ...
2
votes
1answer
289 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 ...
2
votes
1answer
687 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?
2
votes
1answer
92 views

With Clause vs View of the same name

In my database, there is a view named VAL_DATES_V which is used in many other views in the same schema. For example, the SQL code in the view SECTOR_V uses VAL_DATES_V. CREATE OR REPLACE VIEW ...
2
votes
1answer
895 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 ...
2
votes
2answers
145 views

(MySQL) How to change the results of a view according to the user that's viewing it?

I have a table, named servers, with a single column id. This table is accessed by various machines each with a different username for the database. I want to make it so each server does not see all ...
2
votes
1answer
448 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 ...