Questions tagged [view]
A virtual table that represents the data in one or more tables in an alternative way.
650
questions
4
votes
2
answers
285
views
Sargability of queries against a view with a case expression
I have a view that looks similar to this:
CREATE VIEW Shipment.Shipment AS
SELECT CASE
WHEN shipmentOld.SHIPMENT_ID = 0 OR shipmentOld.SHIPMENT_ID = -1 THEN NULL
...
- 2,484
2
votes
1
answer
13k
views
How to create a view from a join with multiple duplicate column names?
I have a few tables with this sort of format
myTable: id | Table1_id | Table2_id | Table3_id | value
Table1: id | text | ...
Table2: id | text | ...
Table3: id | text | ...
I want to create a ...
- 155
0
votes
1
answer
62
views
Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here?
Trigger reading from two view tables and write on a different table takes more than 12 seconds, what might be the issue here?
In my case, I am using a trigger to read from two view tables (Decisions2 ...
0
votes
1
answer
144
views
sql server partitioned view wrong execution plan on update a temporary table
i need your help because we have a performance issues. I would like to update a temporary table that contains a join to the partitioned view.
Schema:
create schema SCHEMA1
go
create schema SCHEMA2
...
- 9
0
votes
1
answer
738
views
Using views as a source for staging table? [closed]
I am in the process of trying to build a small datamart for my small business. I am planning on initially copying tables from the source DB to another DB for staging, where I can then do any data ...
- 13
2
votes
2
answers
8k
views
How to use FOR UPDATE with a view that is built using LEFT JOIN?
Assuming a simple table, e.g.
\d+ task
Column | Type | Modifiers | Storage |
-------------------+--------------------------+--...
- 1,145
4
votes
2
answers
236
views
Best approach for querying multiple tables [closed]
In SQL Server, I have a table, let's call it Source. The structure is
ID | SourceValue
-----------------
1 | 'value1'
2 | 'value1'
3 | 'value2'
and so on.
ID is a primary key and ...
- 143
2
votes
2
answers
3k
views
Does using a view over standard queries and table indexes improve performance on large databases?
I recently had a discussion with someone who said that using a view on a database with a large number of tables and (>1,000000) rows would allow for much better performance.
I don't see how this ...
- 131
1
vote
2
answers
394
views
Views created by SQL server Database Tuning advisor
Several years ago I ran Database Tuning Advisor on our database. Then the database wasn't very big so it was easy.
I implemented the suggestions, and it created several views with names like ...
- 113
2
votes
1
answer
2k
views
Efficiently return two aggregated arrays from a m:n table
I have a table for a many-to-many relationship over a users table to represent a follow relationship between users:
CREATE TABLE users (
id text PRIMARY KEY,
username text NOT NULL
);
CREATE ...
- 193
1
vote
1
answer
325
views
PostgreSQL drop view in case of unknown whether materialized or not
I'm trying to drop a view, of which I cannot assure whether it is a MATERIALIZED VIEW or a VIEW.
DROP VIEW IF EXISTS myview;
throws an error, if myview is a MATERIALIZED VIEW and vice versa. What I ...
- 307
1
vote
2
answers
167
views
View crossing multiple databases, but a query only using one is slow
Given a view like this example, crossing multiple databases in the same database cluster, with each database having a unique INSTANCE_NO.
Please note that each database can be split using the ...
1
vote
0
answers
66
views
Combining two event tables into one postgres view
I have two tables, events and event_logs.
The events table stores the theoretical data on a recurring event whereas the event_log stores specific information on a particular datetime.
Event
+-----...
- 111
2
votes
1
answer
230
views
Datetime fields return as integers in view
We ran into a curious case today where a view that's doing "select * from table" had a couple of datetime fields come back as integers. This was working previously up until very recently. These is ...
- 3,028
4
votes
2
answers
1k
views
select MAX() from MySQL view (2x INNER JOIN) is slow
I want to optimize my MySQL view v_booking. At the moment I am wondering about the following issue:
My queries
Query 1 is slow (47,48 sec):
SELECT MAX(Snapshot_Nummer) FROM v_booking;
Query 2 is ...
- 143
4
votes
2
answers
1k
views
Does cross-database permission chaining work for objects owned by sa/dbo?
I have two databases, both owned by sa. All objects in both databases are in schema dbo.
I have a user bob in dbSafe who needs to select from a view that joins tables from both dbSafe and ...
- 9,954
0
votes
1
answer
69
views
datetime column returning a zero when queried from a view
when I run the following query it returns me a zero, although the column is a datetime type.
SELECT IVSubmitted
FROM vw_queue_ready_for_agencyReview
WHERE agencyid = 1399
ORDER BY lastName ASC,...
- 15.8k
0
votes
1
answer
310
views
how to flatten child tables into parent
I have inherited a db, and I need to transform an existing parent-child relationship into all parents.
Let me first explain the existing data.The tables have a structure something like:
Thing ...
- 103
3
votes
1
answer
15k
views
Why create a Materialized View Log with Primary Key or RowID, etc?
I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED ...
- 149
1
vote
1
answer
4k
views
Oracle Query execution finishes first time but hangs up from second run
We have a very complex view that finishes execution only on first time, but hungs up when run from the same session second time.
complex view - view with nested views, lots of inner and outer joins, ...
- 299
0
votes
2
answers
71
views
what is the correct name for "rich" views? [closed]
I was reading about this the other day that with a good DB you can create a view of data, that is automatically updated and cached
say you have:
time user was online
this is measured with 2 ...
- 211
0
votes
1
answer
30
views
Retrieving What variables mean when retrieving view definitions?
I'm working on a small script to transfer views over from one database to another. I can get the view definition by using select view_definition from information_schema.views where table_name = '...
4
votes
2
answers
6k
views
Postgres GROUP BY id works on table directly, but not on identical view
Lets say I have two tables. One of them references the other in a one-to-many relationship:
CREATE TABLE t (
id SERIAL PRIMARY KEY
, name text
);
INSERT INTO t (name) VALUES ('one'), ('two'), ('...
- 143
-1
votes
2
answers
3k
views
Fetch data from view vs query?
Approach_1
Through SQL query
Select * from employee where id = 100
Approach_2
Here is the db view(employee_view)
Select * from employee
Then use the DB view in sql query from java
select * ...
- 399
1
vote
3
answers
7k
views
SQL Server: Improve CTE performance with case statements
I need your help, I need some guiadance to improve the performance for the following given view.
I have a view writen with the below code:
with timeframes as
(
select p.SEARCH_NUM,
case when p....
user141153
14
votes
1
answer
21k
views
Access view based on table in another database without account in that other database
I created view in database1 based on tables in database2. I gave SELECT permission to a user who has access only to database1. The user can't get this view to work because he doesn't have an account ...
- 141
2
votes
1
answer
3k
views
Define calculated column of BOOLEAN/TINYINT(1) type in a View? [duplicate]
I'm configuring a View which contains calculated columns that should be returning Boolean results. Here's an anonymised version of the view:
CREATE VIEW `ItemView` AS
SELECT Item.ID
,...
- 141
0
votes
1
answer
83
views
View and Synonym hirarchy oracle
The Dba of our company has created views for the tables for all the users.
We have 230 users, he has created 230 views for each tables!
I want to replace this by public synonym and grant select to the ...
0
votes
2
answers
52
views
could sql create view with fix group even without data
Let say that I have a table as below :
day | Qty
---------------
Sunday | 10
Monday | 5
Tuesday | 20
Sunday | 17
as I have another table that day value could only be 'Sunday' till '...
- 1
0
votes
1
answer
264
views
OLTP Indexed Views or SQL Calculation [closed]
At our financial company, we have different OLTP transactions for customers. We have a transaction table (debits, credits) and balance table.
We will only have 1000 transactions per month.
What is ...
user129291
0
votes
1
answer
186
views
How to allow an SQL-ID to access one database through a view in another?
I have 2 databases in MS-SQL Server 2012. The first database, ‘DatabaseA’, is accessed by multiple databases including my second database ‘DatabaseB’. I have a view called ‘vwDB_B’ in ‘DatabaseB’ that ...
- 13
2
votes
2
answers
3k
views
SQL Cross Database Views Security
Cross Database Views:
Hi,
We have two databases
Database A: Contains dbo.Customerid table,
Database B: Contains dbo.Customerdescription table,
Database Reporting: has a schema for consultant ...
user129291
0
votes
1
answer
598
views
Heterogeneous queries error with a View
I recently got my first taste of the error "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection." which I hadn't come across. Nearly every resource I ...
- 1,453
2
votes
1
answer
8k
views
MySQL having variable in view
I have a query that I want to save as a view because it will be used on a different system that we don't have access to. The query basically gets running balances on debit and credit columns from a ...
- 515
0
votes
2
answers
196
views
How to display data from table 1 separately, and combined with matching rows from table 2
I have two tables, let's call them t1 and t2. They're associated - column c1 in t2 matches the primary key column i1 in t1.
For my results, I want every row in t1 to appear without any t2 data; then, ...
- 15
1
vote
3
answers
422
views
How can I optimize a query where a IN clause is replaced with multiples UNION ALL?
I have a query like this:
select *
from slow_view
where id = 1
union all
....
union all
select *
from slow_view
where id = 1000
-- 50 ids, exec time: 10.615 sec,
This would run in a very ...
- 79
1
vote
1
answer
1k
views
How to convert SQL view creation script into a query, instead?
I have the following SQL view creation script. I would simply like to turn it into a query. I tried to replace the initial line with a SELECT statement, but this did not work. Can someone please show ...
- 177
0
votes
1
answer
118
views
Create view using tables from differing databases on a server
I need to create a view that pulls data from multiple databases on a server. I've done this by hard coding the database names (db1.schema.table), but I need it to be more dynamic. This view needs to ...
- 1
1
vote
1
answer
1k
views
Create view with column name aliases or hard change column names on a big database with a very low amount of changes?
Background:
I have to work with an big enterprise geodatabase on an SQL Server Express 2012 server with around 170 tables where many of these tables have 10+ columns. Some column names are repetitive, ...
- 45
4
votes
4
answers
25k
views
Views with parameters, is it possible?
I am working in a PHP application and some "complex" queries are starting to appear in the code. Because of the complexity I am not able to use any ORM and the only resource I have is a plain SQL and ...
- 1,710
5
votes
2
answers
300
views
How to produce an output of fields that are NULL (combined from multiple columns+tables)? [duplicate]
I manage an application that has multiple users entering data through a web front-end into an MSSQL database. Each individual 'record' can have around 100 database columns across multiple tables (...
- 51
2
votes
3
answers
2k
views
Query a view in a procedure in which the view name is a variable
My problem: I have a set of views from which I want to query dynamically.
I want to load the view names by SELECT * from sys.all_views into a cursor (let's call it view_cursor) and then do something ...
- 203
10
votes
1
answer
5k
views
SELECT COUNT(*) on a view is orders of magnitude slower than SELECT * on the same view
The View
CREATE VIEW [dbo].[vProductList]
WITH SCHEMABINDING
AS
SELECT
p.[Id]
,p.[Name]
,price.[Value] as CalculatedPrice
,orders.[Value] as OrdersWithThisProduct
FROM
products ...
- 1,677
7
votes
3
answers
9k
views
Can't open a view's design in SSMS
I get this error message (that you can see below), when I try to open a view's design. It's not happening with tables, only with views and only when I try to see the design, so I'm able to retrieve ...
- 205
4
votes
1
answer
722
views
Postgres cannot simulate table with a view: column must appear in the GROUP BY clause or be used in an aggregate function
I am trying to replace a table in our application by using a View. It works well for the most part, but I can't get past this error: "column must appear in the GROUP BY clause or be used in an ...
- 179
0
votes
1
answer
147
views
Best practice for sharing data over differenet mysql/mariadb databases
I have app that uses DB with 50+ tables. Now i'm in situation that i need to install another instance of app. Problem is that i would like to use some tables as "common" data, e.g. "brands" or "cities"...
- 103
8
votes
7
answers
3k
views
Why does SQL try to convert rows that are excluded by my where clause?
I've come across this multiple times, and I'm sure there is a good reason for it - but how do I avoid it?
I'm sure it has to do with the quirks around isnumeric. In English, I have a view that ...
- 663
2
votes
0
answers
141
views
Permissions on views/stored procedures and the referenced base tables in linked servers
I need help setting up security division between SELECTing and EXECUTEing from views and stored procedures and access to base tables referenced by those objects on linked servers on SQL Server 2012SP3....
-2
votes
3
answers
250
views
View slow ONLY if requested in batches
We have a view which includes a sub-query like this:
SELECT
id AS OrderNo,
timestamp AS OrderDate,
email,
(SELECT TOP (1) reasonId
FROM dbo.pr_cancel WITH (NOLOCK)
...
- 1
4
votes
1
answer
4k
views
Speed slow down on a UNION ALL view
This is my DB architecture:
a 3.000.000 records product (memory) table that must be shared by all databases
a x (say 1) records product table specific for each single database
a view in each ...
- 51