Hot answers tagged

77

The book is wrong. Selecting from a view is exactly as fast or slow as running the underlying SQL statement – you can easily check that using explain analyze. The Postgres optimizer (and the optimizer for many other modern DBMSes) will be able to push down predicates on the view into the actual view statement – provided this is a simple statement (again, ...


41

Most of the information you will need is going to be in the execution plan ( and the plan XML). Take this query: SELECT COUNT(val) As ColA, COUNT(val2) As ColB, COUNT(val) + COUNT(val2) As ColC FROM dbo.TableA; The execution plan (opened with sentryone plan explorer) shows what steps it went through: With the stream aggregate aggregating ...


27

You can query pg_catalog.pg_views for your desired information: select viewname from pg_catalog.pg_views; Refined query to get schema name also - just in case you have multiple views with the same name in different schemas - and left out those system views: select schemaname, viewname from pg_catalog.pg_views where schemaname NOT IN ('pg_catalog', '...


24

This happens often if the optimiser thinks it can do better with the base tables. If I create an indexed view, I tend to always use NOEXPAND to make it use it. I also compare the query statistics to ensure that it adds some benefit. That is, if I identify a useful indexed view I want the optimiser to always use it. Example: SET STATISTICS IO ON; SET ...


23

The [Ranking] field is showing as "Nullable" due to being a computed column. Yes, it is declared as NOT NULL, but as the MSDN page for Computed Columns states, the database engine can change that determination at query-time: The Database Engine automatically determines the nullability of computed columns based on the expressions used. The result of most ...


21

To give you an example of what @a_horse explained: Postgres implements the information schema, which consists of (sometimes complex) views providing information about DB objects in standardized form. This is convenient and reliable - and can be substantially more expensive than accessing the Postgres catalog tables directly. Very simple example, to get all ...


20

As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one. When you do so, I can only recommend two small things: Use DROP MATERIALIZED VIEW blabla CASCADE to get a ...


20

There is no setting to change this behavior nor has it changed in newer SQL Server versions. An excerpt from the current documentation remarks section for SQL Server 2019 and Azure SQL Database: ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view


19

First, stop using SELECT * in your views. I talk about this quite a bit here: Bad habits to kick : using SELECT * / omitting the column list Next, run sp_refreshview or sp_refreshsqlmodule against each view that references a table (or another view!) that you have changed, e.g.: EXEC sp_refreshview N'dbo.viewname'; If you want to generate a script that ...


18

It boils down to what UPDATE statement does. It's not entirely obvious but your statement is equivalent to this one: UPDATE upd SET Ticket = 'ARP.ExGE' , Method = 'smtp' , AcctOwner = 'r00417819' , DisplayName = '~AppLight HBSFax-Inactive' , Destination = 'r00417819@mail.ad.ge.com' ...


18

I think the main problem here is that you're trying to use a single view to feed many different reports, thus complicating the view definition over and over. Not all the reports will need all the columns, the calculations and the JOINs in that view, so, in many situations, you will be hitting tables and performing calculations for no reason. A more ...


15

PostgreSQL (true up to at least 9.4) doesn't currently support removing a column with CREATE OR REPLACE VIEW. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. There is no ...


15

A view is just a "saved query". The indexes on the base table are still used whenever you access the view. You don't need to use an indexed view, unless the view contains an expensive logic (aggregations or joins) that you don't want to perform each time you query the view. Please note that even when the view is "materialized", the optimizer is free to ...


14

Materialized Views Do Not Exist in MySQL. Flexviews has been recommended in the DBA StackExchange before Bill Karwin's post : Does MySQL have a version of Change Data Capture? Redguy's post : Need some support on MySQL Query Since you have it already, some due diligence and elbow grease on your part may be necessary to get going on using it (if you haven't ...


14

Yes, there are reasons to not use SELECT * in a view. The most important is that SQL Server caches the metadata of a view's output, and it doesn't magically update if underlying objects change. Here's a quick example to demonstrate: USE tempdb; GO -- simple table with two int columns CREATE TABLE dbo.x(a INT, b INT); GO INSERT dbo.x(a,b) VALUES(1,2); GO -- ...


14

My answer will focus almost exclusively on SQL Server just because I'm going to give a fairly detailed answer and I don't have the same level of expertise in other platforms. First it's important to realize that the query optimizer doesn't directly work against the SQL you write. It gets transformed into an internal format before optimization. What you have ...


13

This query: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE messages.timestamp BETWEEN timeA AND timeB ORDER BY recipient, timestamp DESC; says: For all messages between timeA and timeB, find the recipients and for every recipient, find one message (the latest in between timeA and timeB). ...


13

To guarantee that the Ranking computed column expression does not return NULL in any circumstances, you must wrap it in ISNULL with a suitable default value. For example: Ranking AS ISNULL(Id + RankingBonus, 0) PERSISTED NOT NULL The NOT NULL constraint ensures the persisted value is not null, in the context of the table- and session-level settings in ...


12

Partitioned views are a (very) old technique for partitioning data that are very rarely used today. Oracle added the ability to partition tables back in Oracle 8, which provides much more functionality than partitioned views, at which point partitioned views became obsolete. The only reason to consider using partitioned views would be if you can't afford a ...


12

I suggest an SQL function: CREATE OR REPLACE FUNCTION foo(_date date) RETURNS TABLE ( name text -- types have to match your actual types! , keyword_id int , project_id int , the_date date , today int , yesterday int , week int , month int) AS $func$ SELECT k.name, f.keyword_id, f.project_id, _date -- AS the_date -- col ...


11

To answer your question literally, yes, in MySQL, views do exist as occupied space on the disk. But of course they do: if the didn't, where would they exist? If you rebooted your server, how would the views persist? I imagine what you really meant was "do MySQL views occupy physical space in proportion to the number of rows they contain?", in which case the ...


11

You can use row_number, and the easiest way is to just add a row_number() OVER (PARTITION BY true) field into the view. You need the PARTITION BY and using the "true" expression is the most performant way (no need for sorting like in Fabrizio Mazzoni's answer).


11

This is just Standard SQL join syntax with the optional parentheses removed: SELECT * FROM tableC LEFT JOIN ( TableB RIGHT JOIN TableA ON TableA.ID = TableB.ID ) ON TableB.TypeID = TableC.TypeID If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using ...


11

Here is what I do in such cases, usually some of this helps: Look at the whole query and try to remove unneeded tables from it. Rethink outer JOINs (that is, LEFT/RIGHT JOIN) and if possible, eliminate them from view definition, replacing by inner JOINS. Try to increase planner constants so the server can put more effort into planning phase. You can do ...


11

Note: This answer addresses a couple of basic problems, but it's not the final solution. The question was still inconsistent after several requests for clarification, so I stopped processing. General difficulty The Problem is: predicates on some columns, ORDER BY on a different column. In your fast query, without ORDER BY, the first (arbitrary) 10 rows ...


11

The essential problem is that filtering on Route = N'0600' before computing the window functions (as in the query) is different from filtering on Route = N'0600' after computing the window functions (as in the view). This would give different (= incorrect) results for the window functions in general, so the optimizer does not do that. See Of Windowing ...


11

Referencing the documentation from CREATE VIEW under REMARKS: The CREATE VIEW must be the first statement in a query batch. Referencing the documentation from CREATE TRIGGER CREATE TRIGGER must be the first statement in the batch and can apply to only one table. For VIEWS and TRIGGERS, I think you'll have to check for the existence of the object ...


11

You can prevent altering an indexed view with a DDL trigger. But the implementation is a bit complex, because the DDL trigger runs after the view has been altered and the index dropped, but before the change has been committed, and you can't directly detect that the view previously had an index. So you have to get up to some shenanigans with extended ...


10

According to the MySQL Documentation on Views Views (including updatable views) are available in MySQL Server 5.6. Views are stored queries that when invoked produce a result set. A view acts as a virtual table. The first thing that must be realized about a view is that it produces a result set. The result set emerging from query invoked from the view is ...


10

A simple example: CREATE TABLE dbo.x(a INT, b NCHAR(4)); GO CREATE VIEW dbo.vx AS SELECT a, b FROM dbo.x; GO ALTER TABLE dbo.x ALTER COLUMN a TINYINT; ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4); GO SELECT a,b INTO #blat FROM dbo.vx; GO EXEC tempdb.dbo.sp_columns N'#blat'; GO DROP VIEW dbo.vx; DROP TABLE dbo.x, #blat; Partial output: COLUMN_NAME ...


Only top voted, non community-wiki answers of a minimum length are eligible