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

learn more… | top users | synonyms

1
vote
1answer
158 views

What is a good way to query data stored in multiple identical tables split by date/time

I have a 3rd party piece of software which records data samples from industrial equipment. This data logging software stores data in sample data tables with approximately one day's worth of samples ...
1
vote
1answer
1k views

PostgreSQL 9.1 - Query on VIEWS taking a lot of time

Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation: We have a partitioned Table "buz_scdr" over which we have built a VIEW ...
9
votes
1answer
772 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?
8
votes
3answers
2k 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 ...
3
votes
1answer
3k 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 ...
1
vote
1answer
371 views

while restoring mysql database, views changed into tables

I have a full database dump and i restored it. But in my new restored server all the views are appeared as MyISAM tables. In early times dumps were restored perfectly. can anyone help me to trigger ...
1
vote
1answer
122 views

Where do caching servers conceptually fit in my architecture?

Given the simple well-known architecture below: Where (conceptually) does the caching server(s) sit?
1
vote
1answer
580 views

Triggers vs Views in MySQL

We are in the process of denormalizing a large amount of data for performance reasons. Basically a Query that would normally JOIN over 10 tables is replaced with a Query that has no JOINs, yet all the ...
1
vote
1answer
3k 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
2answers
9k 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 ...
1
vote
2answers
1k 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) ...
5
votes
2answers
1k views

how does SQL Server know to lock view objects?

Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow. In SQL Server 2008 I have a view V over tables A and B that looks roughly like ...
9
votes
2answers
13k 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 ...
5
votes
1answer
2k 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
votes
1answer
256 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 ...
4
votes
2answers
875 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 ...
12
votes
3answers
2k 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 ...
2
votes
1answer
183 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 ...
-1
votes
1answer
2k 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 ...
4
votes
2answers
2k 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 ...
0
votes
1answer
425 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 ...
5
votes
2answers
455 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
vote
1answer
7k 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
461 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?
5
votes
1answer
3k 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. ...
14
votes
3answers
3k 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 ...
2
votes
3answers
566 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? ...
1
vote
1answer
143 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 ...
5
votes
1answer
1k 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 ...
1
vote
1answer
292 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 ...
2
votes
2answers
2k 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
314 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 ...
2
votes
1answer
226 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 ...
1
vote
1answer
45 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 ...
1
vote
1answer
156 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 ...
2
votes
2answers
3k 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 ...
2
votes
1answer
89 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 ...
1
vote
2answers
1k 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 ...
2
votes
2answers
390 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 ...
2
votes
2answers
391 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 ...
5
votes
1answer
302 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 ...
3
votes
2answers
2k 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 ...
3
votes
3answers
2k 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 ...
1
vote
2answers
668 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
2answers
2k 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 ...
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 ...
3
votes
2answers
3k views

How can I help SQL Server recognize my 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 ...
4
votes
2answers
1k 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 ...
12
votes
3answers
4k 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 ...
3
votes
2answers
1k 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 ...