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(...
- 16.5k
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 ...
- 514
30
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. ...
- 182k
22
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 ...
- 691
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 (...
- 376
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:
...
- 28.5k
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 ...
- 11.3k
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 ...
- 171
17
votes
Accepted
Are views logically redundant?
Both views and inline TVFs are updatable, and can be used interchangeably almost everywhere.
Specifically for SQL Server, the following features are supported by views that are not available for ...
- 14.4k
15
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 ...
- 253
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 ...
- 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 ...
- 409
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 ...
- 32.6k
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:
...
- 69.5k
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 (...
- 90.2k
12
votes
View doesn't recognise a change to an underlying table when an existing column is dropped and replaced with one with the same name but as computed
I know a view refresh or alter view will correct this but the column names haven't changed.
Geezer
You also didn't tell the engine which columns you care about. You said select *.
It's been written ...
- 5,001
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 ...
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 ...
- 47.6k
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 ...
- 182k
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, ...
- 90.2k
10
votes
Accepted
View doesn't recognise a change to an underlying table when an existing column is dropped and replaced with one with the same name but as computed
When you drop and recreate the column the column is created at the end of the table so the results of the underlying SELECT query have their columns shifted.
SELECT T1.*, T2.*
FROM (
SELECT ...
- 86.5k
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 ...
- 70.5k
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 ...
- 182k
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 ...
- 86.5k
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
SQL Server: How do you prevent data being modified through an exposed view?
This is handled through the view's and table's security. Grant only SELECT permission to the view and the underlying tables.
Without write access, INSERTs, UPDATEs and DELETEs through the view will ...
- 5,986
8
votes
Postgres cannot simulate table with a view: column must appear in the GROUP BY clause or be used in an aggregate function
Postgres is smart enough to know in the TABLE that your group-by column is UNIQUE (PRIMARY KEY) ... grouping by a column that is UNIQUE is pointless, so postgres just gives you back what you ask for (...
- 3,428
8
votes
Accepted
Can't open a view's design in SSMS
Because you are using a newer version of SQL Server than SSMS, I recommend you install a SSMS of same version or later than the SQL Server. There is no guarantee that an older version of SSMS will ...
- 1,029
8
votes
Why is the result of a totally deterministic and all-encompassing "case" in select query in view nullable?
To answer the why part of your question, the CASE expression result is nullable because the CAST function result is nullable. This can be observed with:
EXEC sp_describe_first_result_set N'SELECT ...
- 28.5k
Only top scored, non community-wiki answers of a minimum length are eligible