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

learn more… | top users | synonyms (1)

2
votes
1answer
44 views

Filtering UNION ALL result is much slower than filtering each subquery

(edit: see end for a simpler example) I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), ...
1
vote
2answers
19 views

How to grant permissions to users based on table information?

Say I have a table: +------------------------+ | Employees | +------+--------+--------+ | Dept | Name | Salary | +------+--------+--------+ | 1 | John | 10000 | | 1 | Mary | ...
0
votes
1answer
20 views

Replicate View different servers

Using MySQL, and related to this answer open in another thread (How to replicate MySQL table in different servers?), It arises me the idea of replicating only a View in different servers (through ...
6
votes
3answers
113 views

How to improve estimate of 1 row in a View constrained by DateAdd() against an index

Using Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64). Given a table and index: create table [User].[Session] ( SessionId int identity(1, 1) not null primary key CreatedUtc ...
2
votes
2answers
62 views

Why am I able to query for more than 1024 columns but I can not make a view of the same query?

My boss is requesting a view with greater than 1024 columns where in he will only select a few columns he wants when he queries the view so that the actual returned columns are less than 1024. Why am ...
1
vote
0answers
19 views

Efficient Provision of Multiple Versions of Data

We have a fairly unusual database use case: we have a number of large datasets used for research (the largest being a couple of billion rows). The datasets are used by potentially several hundred ...
0
votes
1answer
31 views

Is the same thing(internally) putting conditions in a query over a view that directly on the view?

I'm having trouble understanding performance with queries on views. Given this two scenarios, the queries internally will be the same? SCENARIO 1 View: CREATE VIEW TEST.SIMPLEVIEW AS SELECT ...
1
vote
1answer
53 views

Create a table based on a table depending on the values in the initial table column

I have a table like: +----+--------+----------------+ | id | name | address | +----+--------+----------------+ | 1 | Ivan | Moscow,Moscow | | 2 | Petr | Samara,Samara | | 3 | Grigor ...
1
vote
1answer
33 views

Creating a view with a Left Outer Join and Where Condition

Let's say I have a User Table and a User Record table which lists Data Sources associated with that User. I want to create a (Sql Server) View that lists all the contents of a User, and an optional ...
1
vote
1answer
41 views

special setup to create views in Oracle in default schema

When creating views in Oracle, do you need special setup for a given user if the view and table in which the view is reading from are in two different schemas? The user creating the view has dba ...
0
votes
1answer
86 views

Encapsulate table with view

I need to change the order of columns in my table (Oracle and SQL Server). Reason is that some of our software calls select * and insert without specifying columns. Refactoring will be too expensive ...
0
votes
1answer
43 views

Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record

Hey Microsoft SQL Gurus I need your help, please. This is my first post, and I have a table called projecthistory, it’s actually a view so I could take advantage of the Rank()function to get a unique ...
0
votes
1answer
21 views

Can there be situations where using (noexpand) on an indexed view is undesirable?

From the past experience using the schema bound and indexed view in a query with (noexpand) hint usually dramatically improves performance if the view is designed well. Apparently using the indexed ...
0
votes
2answers
34 views

Database view listing distinct days

I have a list of sensors of many machines, that stores on a second base (i.e., 86400 records each day for each machine). One of the many columns is the datetime of the value (that can also be stored ...
3
votes
1answer
31 views

Select Permissions Multiple Schema

I have an interesting one that I have not had the pleasure of solving before. I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, ...
1
vote
1answer
32 views

Is it possible to write a select * view that includes new columns added to table?

I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them. If I do the ...
1
vote
0answers
53 views

Get the name of the table column referenced by a (auto updatable) view column (PostgreSQL)

Given the folowing view definition create or replace view projects as select id, client_id as clientId from data.projects Is it possible, knowing the view name and the view column to get the name ...
0
votes
0answers
56 views

Can't create a view for other user's table despite having select privilege

I already executed the following commands: grant create view to scott; conn hr/hr; grant select on employees to scott; but: create or replace view company_phone_book as select employee_id emp_id, ...
0
votes
1answer
20 views

Hide column values for some rows in auto-updatable views while retaining the “write” capability for them in PostgreSQL

Suppose i have a view defined like this create or replace view clients as select id, name, case app_user_type() -- hide column content for everyone except the administrator when ...
7
votes
0answers
106 views

SQL Server - Handling localization of strings in nested non-deterministic view stacks

While profiling a database I came across a view that is referencing some non-deterministic functions that get accessed 1000-2500 times per minute for each connection in this application's pool. A ...
0
votes
1answer
33 views

Partitioned view with dedicated “routing” column

I have an application (Microsoft Dynamics NAV to be exact) that splits up data for different companies in tables with the exact same structure. I can not change that behavior, so I built VIEWs that ...
2
votes
2answers
20 views

Most restricting permissions for querying view with linked server

I've created view to query another server via linked server(SQL Server 2014). I need to grant permissions to developers to use this view. Granting only select is not enough and they got the ...
1
vote
2answers
33 views

Correct way to write this view? Total sales/purchases/profits by day

I'm currently trying to create a view that lists a sum of sales, purchases and profits done by each person per day. This data comes from 3 tables: transaction: idtransaction (pk) user_iduser (fk) ...
1
vote
1answer
56 views

Postgres CTE query for a single row not optimized

From my database I am trying to get a weighted sum of a user's stats, and I will only be querying the table one or two users at a time, so I wrote it as a view. Since it's a view, I pretend to ...
6
votes
3answers
229 views

Updating a view on multiple joined tables

Since MSDN does not say much, what happens exactly if I execute the following query? update claims set status='Awaiting Auth.' where status = 'Approved' Can I use the ClaimStatusName column of the ...
1
vote
1answer
29 views

DB2 - cannot invalidate a view

I work on something and I need to check some views. I use this code: SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME ...
1
vote
1answer
64 views

Making data fetching faster on VIEW

I have created a rest service which fetches data from Oracle database and send it back to the application from which the rest service is called . Below is the query to fetch data from VIEW ,this ...
0
votes
1answer
35 views

Cast as INT(11) instead of DECIMAL 11,0 in a view

I have a view with a column defined as: IFNULL(priority.id, 2) priority_id, ... where priority.id has type INT(11), however the resulting view column has type: DECIMAL(11,0) How do I get the ...
1
vote
1answer
30 views

Find insert/update/delete queries that reference a partitioned view

I have just given a SQL Server with performance problems. The database is very large and use distributed views with a very large primary clustered key. (24 bytes). I would like to change this primary ...
1
vote
1answer
48 views

Performance issue using view vs. direct statement

I have a table with structure similar to this: table values ( id1 int, id2 int, value real, value_at timestamp, constraint primary key (id1,id2,value_at) ) I need to get the "current value" for a ...
1
vote
2answers
347 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 ...
2
votes
1answer
28 views

Turn off auto qualification of table names when creating a view

Problem Definition I am trying to modified a shared development database, for a dev team to use for application development/testing. Most of the collective work (table names, views, etc) are stored ...
10
votes
5answers
672 views

Why does this UPDATE fail with a unique key constraint violation?

I'm an "accidental" DBA, relatively inexperienced and baffled by this problem. Running MS SQL Server 2012. The problem is with this UPDATE statement: UPDATE dbo.tAccts SET Ticket ...
1
vote
0answers
62 views

SQL query causes server gone away

I'm developing a database system which stores and caluclates engine performance data. I have been created a new view which selects data from several tables and calculates some values. Now I'm running ...
3
votes
1answer
135 views

Bad performance with select on view if check for 'is null'

I have two quite simple tables, and a view to join them on bigint fields: CREATE TABLE foo.ao ( id serial NOT NULL, ao_id bigint NOT NULL, ao_plz text, ao_community text, ao_street text, ...
6
votes
1answer
229 views

Running out of disk space running MySQL query

I have been trying to create a "gaps and islands" identification routine in MySQL. See the previous question regarding setup here and computational resources required here. I increased the RAM of the ...
0
votes
0answers
44 views

force SQLite view column type affinity

Using SQLite 3, I have some view columns which are defined with an expression, and so have no type affinity. Is it possible to define them in a way that forces them to have a certain type affinity? ...
0
votes
2answers
84 views

Create View For All Table Names Listed In Table

I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure. I have a table that holds table names, I need to ...
1
vote
2answers
58 views

Update a table after insertion to another table in PostgreSQL?

Let's say that my database has 2 tables (Person & Car): Person (personID, number_of_cars) Car (carID, owner) where the owner attribute in Car refers to the personID in the Person table. Now, ...
1
vote
1answer
222 views

Teradata view with “With Clause” syntax

I am trying to a view with "With" clause in Teradata but it is not allowing to create a view. Please suggest with any options. Given below is the query which I have tried: create view ...
0
votes
1answer
48 views

MySQL: Overlay modifications on original data while indexing on a field that itself has modifications

I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct ...
2
votes
0answers
115 views

Materialized View - vs. New Calculated table for performance [closed]

I have a problem - I'm trying to de-normalize data tables in a data warehouse in preparation for use in the Business Intelligence/ Presentation layer. By denormalize I mean create a series of joins. ...
2
votes
1answer
167 views

Encryption/Decryption with StoredProcedure/View

I'm working on a project that uses EntityFramework for programmers to access the data. We've recently found need to add fields that were determined that need to be encrypted. This will be the first ...
1
vote
0answers
86 views

Secure view on row level with custom security tables

Let's say I have a following table that lists all member of my application. CREATE TABLE dbo.Member ( MemberId BIGINT, LoginName NVARCHAR(64), SecurityRole NVARCHAR(64) ) I assume that ...
-2
votes
1answer
77 views

Create a view for different attributes from different tables

I have three tables: product(pid,name,category,maker-cid) purchase(buyer-ssn,seller-ssn,quantity,pid) person(ssn,name,phone number,city) How can I create a view to expose only the Buyer name, ...
0
votes
1answer
76 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 ...
0
votes
0answers
10 views

is there a way to see statistics on when and how often a view is being used in SQL Server? [duplicate]

I was recently handed a request to produce a report on all the views in a given database along with an indication of how frequently they are used, presumably so that the developers can remove views ...
0
votes
1answer
69 views

SQL database ODBC connection Multi-part identifier could not be bound

We're using PADS Professional xDX Designer (from MentorGraphics) with a SQL parts database connected through ODBC using views. All seems to be working just fine, except for our Inductor parts where ...
0
votes
0answers
18 views

MySQL View cannot be executed via shell command

I have a PHP script (CakePHP framework) that makes this call $data_reports = Cache::read('get_data_reports', 'short'); if (!$data_reports) { $data_reports = $this->paginate('DataReport'); ...
0
votes
0answers
34 views

I currently have a stored procedure that contains 5 joins, would it be more efficient to implement it as a view?

My current stored procedure is badly written and needs to be refactored. I've managed to find the time to do that. This procedure will be called every two minutes or so, and as more products are sold ...