A virtual table that represents the data in one or more tables in an alternative way.
2
votes
2answers
68 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
486 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
2answers
98 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
129 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
2answers
1k 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
164 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
342 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
91 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 ...
2
votes
2answers
93 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
236 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
189 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 ...
2
votes
1answer
85 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 ...
2
votes
2answers
117 views
Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent?
SQL Server has a an information_schema view, 'VIEW_COLUMN_USAGE', which you can use to determine the actual/base tables that the columns in a view reside in.
I'm looking for the equivalent in MySQL - ...
2
votes
2answers
1k 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
2answers
236 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 ...
2
votes
1answer
582 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 ...
2
votes
1answer
128 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 ...
2
votes
2answers
290 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?
1
vote
2answers
2k views
SQL query to return a column per date in date range?
Let me explain what I've got and what I'm trying to achieve.
Let's say I have Table A: BookingsPerPerson
Person_Id ArrivalDate DepartureDate
123456 2012-01-01 2012-01-04
213415 ...
1
vote
3answers
3k 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 ...
1
vote
3answers
67 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?
1
vote
1answer
73 views
Does the SQL Server Backup Database Command also backup views from a Database?
I have a backup job (SQL Server Agent job) which will iterate through all of our SQL Server databases in SSMS, invoke the BACKUP DATABASE command to create a .bak file on a different server. This ...
1
vote
1answer
112 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
117 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 ...
1
vote
1answer
337 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
2answers
604 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
vote
1answer
454 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.
...
1
vote
2answers
29 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 ...
1
vote
1answer
47 views
Is it a best practice to contrast table and dependent view metadata to target which views to refresh?
We have non-indexed views created on vendor tables. When they change table structure or column definitions we run sp_refreshview as needed. What is the viability of a script to contrast the table ...
1
vote
2answers
636 views
SQL Server 2008R2 - How to prevent a role from creating or altering views within a database?
I have a situation on SQL Server 2008 R2 where I am trying to prevent users of an AD group from being able to create new views, or alter any existing views, within a particular database. This AD group ...
1
vote
1answer
1k 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 ...
1
vote
1answer
44 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
2answers
227 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 ...
1
vote
1answer
47 views
Can't query one view from another?
Okay, I'm moving a database from SQL Server (which I already miss) to MySql 5.6. I'm having a problem creating a list of valid dates. In SQL Server I just used CTEs to build up numbers, then months, ...
1
vote
1answer
59 views
Question about Oracle's interpretation of “host cpu utilization” in v$sysmetric view
I am studying the v$sysmetric view from Oracle and trying to understand what is Oracle's interpretation of "Host CPU Utilization"
It says in the metric_unit as % busy/(idle + busy), but what is busy? ...
1
vote
1answer
50 views
What is the locking behavior for Updatable/Insertable Views?
Consider I have a View that is merely a simple join between 2 tables, selecting all columns.
The View is Updatable and Insertable.
What would be the locking behavior when executing an UPDATE or ...
1
vote
2answers
945 views
Why an object became invalid in oracle databse.
There are some invalid views and functions in my database.
I am not sure what changes caused these objects to became invalid since many people are working on the same database.
How can I see which ...
1
vote
1answer
125 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
522 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 ...
1
vote
1answer
2k 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 ...
1
vote
1answer
204 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 ...
1
vote
2answers
814 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 ...
1
vote
0answers
30 views
CouchDB views and references
Not sure if/how it is intended to be done but i am trying to match up all the active document in my database with the author of it:
function(doc) {
if (doc.data.active && doc.data.active ...
0
votes
2answers
1k 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 ...
0
votes
1answer
299 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
...
0
votes
1answer
91 views
Retrieving a fixed range of rows from Postgresql
I have a query:
select qid,ansid,ans from table1 where askerid='something'
This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the ...
0
votes
2answers
24 views
A query for a latest version of data
MySQL.
Let there is a table with fields startdate and kind.
Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
0
votes
1answer
65 views
View dropped, now shows as table “in use”, can't drop
I have a view on a MySQL/MyISAM setup, then the underlying table had a column dropped, and made that view invalid.
I tried dropping the view so I could recreate it, then MySQL said it was a table ...
0
votes
1answer
77 views
Create a VIEW that spans 3 tables
Sorry if the title is not clear - feel free to improve.
Here are 3 tables (not all data is relevant to the question):
CREATE TABLE `content` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
0
votes
2answers
116 views
MySQL Reusing calculation in SELECT clause in a VIEW
I have an SQL query that has a simple date difference between NOW() and the creation date (is in the table). I need to reuse this again within the SELECT clause in a VIEW. Is this possible at all?
...
