Hot answers tagged

41 votes
Accepted

Does SQL Server Only Perform Calculations In A SELECT List Once?

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(...
Randi Vertongen's user avatar
32 votes

Is there a generic term for tables and views?

Postgres (and also relational algebra) calls them relations - tabular things of rows and columns that you can query from. This includes tables, views, materialised views, temporary tables, or even ...
Bergi's user avatar
  • 504
29 votes

Are views harmful for performance in PostgreSQL?

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. ...
Erwin Brandstetter's user avatar
21 votes

PostgreSQL - Create view with autoincremental column

You can use row_number, and the easiest way is to just add a row_number() OVER () field into the view. You need the PARTITION BY and using the "true" expression is the most performant way (...
Daniel's user avatar
  • 368
21 votes

ALTER VIEW drops Index from View

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: ...
Dan Guzman's user avatar
  • 28.1k
21 votes
Accepted

Is there a generic term for tables and views?

A "thing with columns and rows" is a relation. This is the word Codd used in his seminal paper, starting the RDBM revolution. A "thing that can change but retain its identity" - is ...
fdreger's user avatar
  • 691
18 votes

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

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 ...
ypercubeᵀᴹ's user avatar
18 votes

How to check/know the highest run queries

As someone said in the comments, pg_stat_statements is the way to get the statistics. Put this into your postgresql.conf: shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = ...
Matt's user avatar
  • 281
18 votes

SQL Server - convert complicated view to table - the best way

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 ...
spaghettidba's user avatar
  • 11.2k
17 votes

Are views harmful for performance in PostgreSQL?

EDIT: With apologies, I need to retract my assertion that the accepted answer is not always correct - it states that the view is always identical to the same thing written as a subquery. I think ...
enjayaitch's user avatar
15 votes

How to list all views in SQL in PostgreSQL?

If you only need this interactively while in psql, you can also use \dv to show views, or \dm for materialized views. Or use with +, like \dm+ for example to show some additional information (mostly ...
Tim's user avatar
  • 323
14 votes

How to get mysql view definitions?

SHOW CREATE VIEW viewName returns the SQL definition with a CREATE VIEW statement. SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'DATABASENAME' AND ...
Pankaj Kumar's user avatar
14 votes

What happens to a query that is performed on a view?

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 ...
Joe Obbish's user avatar
  • 32.1k
13 votes

How do I switch off SCHEMABINDING for a view without recreating it?

After looking around for hours, I created 2 stored proc for this. Hope this helps someone CREATE PROCEDURE ViewRemoveSchemaBinding @ViewName VARCHAR(MAX) AS BEGIN DECLARE @...
boblemar's user avatar
  • 231
12 votes

Replace a materialized view in Postgres

For my situation, I prefer to limit the drops by using a view layer: Create a copy of the materialized view suffixed with "_new" and also use "WITH NO DATA" for performance, make sure any indexes are ...
RuiDC's user avatar
  • 223
12 votes

Access view based on table in another database without account in that other database

This is easy to accomplish in a very secure way using Module Signing. This will be similar to the following two answers of mine, also here on DBA.StackExchange, that give examples of doing just this: ...
Solomon Rutzky's user avatar
12 votes
Accepted

Query is fast but becomes sluggish when created as a view

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 (...
Paul White's user avatar
  • 83.7k
11 votes
Accepted

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT

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, ...
filiprem's user avatar
  • 6,519
11 votes

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT

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 ...
Erwin Brandstetter's user avatar
11 votes
Accepted

"If not exists" using OBJECT_ID() doesn't work for views and triggers. why?

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 ...
Scott Hodgin - Retired's user avatar
11 votes
Accepted

ALTER VIEW drops Index from View

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 ...
David Browne - Microsoft's user avatar
10 votes
Accepted

Datetime fields return as integers in view

This has happened because the definition of the table has changed since the view was last modified. Maybe someone altered one or more columns, or the table was dropped and re-created with columns in a ...
Aaron Bertrand's user avatar
10 votes
Accepted

How to get cross apply to operate row by row on a view?

It is not possible to fully guarantee evaluating the view per row of the outer query, without using something that introduces a new T-SQL execution scope, for example a non-inline (multi-statement, ...
Paul White's user avatar
  • 83.7k
9 votes
Accepted

Updating a view on multiple joined tables

General view updatability The key part of the CREATE VIEW (Transact-SQL) documentation is: Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to ...
Paul White's user avatar
  • 83.7k
9 votes
Accepted

Why does SQL try to convert rows that are excluded by my where clause?

The view is not being used, since it is being "optimized away" by the query optimizer. You can see this by looking at the query plan: You could create an indexed materialized view, then use the ...
Hannah Vernon's user avatar
  • 69.9k
9 votes
Accepted

Why can you only drop a SQL Server view in the current database?

The documentation is skimpy on this, merely stating (and providing a syntax diagram) that only current database is relevant. Removes one or more views from the current database. DROP VIEW [ IF ...
Aaron Bertrand's user avatar
9 votes

How to get SEEK accessing converted ID via view

The issue is that your query through the view is the same as doing SELECT * FROM SomeTable WHERE CAST(SomeTable.ID as NVARCHAR(10)) = N'1' Pretty much any CAST of a column in a predicate will render ...
Martin Smith's user avatar
  • 84.4k
9 votes
Accepted

SQL Server Indexed View and TOP

Outer Apply You're using OUTER APPLY, but with a where clause that would reject NULL values. It's converted to an inner join without the TOP (1): SELECT U.Id, A.Reputation, A.DisplayName ...
8 votes
Accepted

Does schema binding views affect performance?

With schemabinding can improve performance in some cases. I couldn't recall all the details, but found an excellent blog post that goes through this in detail: With user defined functions that ...
sheldonhull's user avatar

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