ukulele-7
Goto Top

MSSQL WITH in View

Moin,

WITH ist ein undankbares Schlüsselwort, vor allem für Google face-smile Mir sind folgende Möglichkeiten bekannt:
CREATE VIEW ...
WITH SCHEMABINDING
AS
SELECT ....
und
CREATE VIEW ...
AS
WITH t AS (
SELECT ....
Kann ich eine View mit SCHEMABINDING erstellen und dennoch WITH in der Syntax verwenden?
CREATE VIEW ...
WITH SCHEMABINDING
AS
WITH t AS (
SELECT ....
Das wirft einen Syntaxfehler. Er stört sich jetzt nicht mal am Alias "t" und ich wüsste jetzt auch nicht warum das nicht gehen sollte, einzig die Syntax gibt das scheinbar nicht her. In diesem Fall mache ich kein CTE im WITH, kann meinen Select also auch anders aufbauen aber vielleicht kennt hier jemand einen Weg?

Content-Key: 7259172032

Url: https://administrator.de/contentid/7259172032

Printed on: February 21, 2024 at 04:02 o'clock

Member: Crusher79
Crusher79 Feb 12, 2024 at 09:40:42 (UTC)
Goto Top
https://stackoverflow.com/questions/1413516/can-you-create-nested-with-c ...

Nested WITH Komma separiert? Wie wäre es mit der Richtung?

WITH x AS 
(
    SELECT * FROM MyTable
), 
y AS 
(
    SELECT * FROM x
)
SELECT * FROM y

Leider fällt mir zu SCHEMABINDING nichts gescheites ein.
Member: HansDampf06
HansDampf06 Feb 12, 2024 at 11:41:43 (UTC)
Goto Top
Zitat von @ukulele-7:
Das wirft einen Syntaxfehler. Er stört sich jetzt nicht mal am Alias "t" und ich wüsste jetzt auch nicht warum das nicht gehen sollte, einzig die Syntax gibt das scheinbar nicht her. In diesem Fall mache ich kein CTE im WITH, kann meinen Select also auch anders aufbauen aber vielleicht kennt hier jemand einen Weg?

Zitat von @Crusher79:
Leider fällt mir zu SCHEMABINDING nichts gescheites ein.

Dass ein Syntaxfehler geworfen wird und dass dazu nichts Gescheites einfällt, liegt aus meiner Sicht in der Natur der Sache der Schemabindung und ist demgemäß zwingend. Denn bei dieser Art Abfragen handelt es sich - ich will es etwas vereinfacht ausdrücken - um materialisierte / deterministische Abfragen. Das und nur das sind auch diejenigen Abfragen, die indiziert werden können. Immerhin wird die Abfrage an die zugrundeliegende(n) Tabelle(n) gebunden. Die Tabelle kann aufgrund der Bindung auch nicht mehr geändert werden, ohne zuvor die Abfrage zu löschen. Diese Bindung führt dann vor allem zu einer extrem starken Syntaxstrenge - alles muss in der (einzigen) SELECT-Definition enthalten sein (Der Erinnerung nach sind nicht einmal die Stellvertreter-Abkürzungen (mit AS) erlaubt, sondern "FQDN" beginnend mit Datenbankname und dbo). Sogar das Einbinden von Unterabfragen ist unter diesen Vorgaben nur bedingt möglich.

In meiner Anfangszeit beim Umstieg von MS Access auf SQL Server hatte ich hinsichtlich der indizierten Views die illusorische Vorstellung, ich könnte wegen der Indizierung spürbare Leistungsverbesserungen erreichen. Wegen der Syntaxstrenge und dem Determinationszwang war es irgendwie eine Qual. Zudem war in den meisten der relevanten Fälle eine Deterministik der Abfrage ausgeschlossen und konnte auch nicht durch geschickte Veränderung der Abfragedefinition erreicht werden. Bei näherer Beschäftigung mit dieser Art Abfrage leuchtet das alles aber auch restlos ein.

Letztlich bin ich dann auf Computed Columns, Table-Functions, Proceduren etc. umgeschwenkt und dort konnte ich diejenigen Leistungspotentiale heben, die ich heben wollte und auch erhoffte.
Dort - bei den Anforderungen der Schemabindung - liegt auch der Grund, warum ich seither so gut wie gar nicht den CTE-Ansatz mit WITH genutzt habe. Hinzu kommt aber auch, dass die Fälle, in denen das WITH-Statement zur Vermeidung der mehrfachen Wiederholung der Syntax derselben Unterabfrage in einem SELECT-Konstrukt hilfreich sein könnte, äußerst selten ist. Ein weiterer Grund ist die leichtere (partielle) Prüfbarkeit eines komplexen SELECT-Konstruks (von innen nach außen) im SQL Studio, wenn auf das WITH-Statement verzichtet wird. Freilich steht dem der Nachteil gegenüber, dass ein mehrfacher Aufruf derselben Unterabfrage bei Änderungen indentisch gehalten werden muss.

Viele Grüße
HansDampf06
Member: ukulele-7
ukulele-7 Feb 12, 2024 at 12:08:46 (UTC)
Goto Top
Also ich nutze jetzt die selbe Abfrage nur anders zusammen gesetzt, vereinfacht so geht es:
CREATE VIEW v
WITH SCHEMABINDING
AS
SELECT t.s
FROM (

SELECT s
FROM dbo.tabelle

) t
oder eben
CREATE VIEW v
--WITH SCHEMABINDING
AS
WITH t AS (
SELECT s
FROM dbo.tabelle
)
SELECT s
FROM t
Ich sehe keinen Grund warum das nicht auch in der 2ten Variante schemagebunden laufen könnte...

Ich brauche die Schemabindung übrigens für RLS, da komme ich in dem Fall nicht drum herum. Ansonsten würde ich sie auch weg lassen.
Member: HansDampf06
HansDampf06 Feb 12, 2024 at 12:42:49 (UTC)
Goto Top
Vielleicht noch ein Erfahrungstipp: Von MS Access kommend war ich es gewöhnt. Häufiger verwendete Abfragen als View abzulegen und diese View dann an den benötigten Stellen aufzurufen. Bei dem Wechsel auf SQL Server habe ich das selbstredend zunächst beibehalten. Bei komplexen SELECT-Konstrukten war ich aber erstaunt, dass die Abfrageleistung erkennbar bei der Verwendung von solchen (ausgelagerten Unter-)Views einbrach, während die View für sich genommen durchaus schnell war. Der Grund liegt darin, dass der Analyzer / Optimizer solche Views nur bedingt bis gar nicht optimiert in das aktuelle Konstrukt einbinden kann. Erst die direkte Erweiterung des Konstrukts um die ausgelagerte (Unter)View brachte die erwartete Leistung.

Ich sehe keinen Grund warum das nicht auch in der 2ten Variante schemagebunden laufen könnte...
Es ist bei diesem Konstrukt keine aus sich heraus definierte Abfrage. Das WIHT-Statement lagert nunmal eine Unterabfrage bei der Definiton aus dem eigentlichen SELECT-Konstrukt aus. Nur noch der Stellvertreter ist in der (eigentlichen) Abfragedefinition enthalten. Das ist eben die Syntaxstrenge bei schemagebundenen Views.

Ich brauche die Schemabindung übrigens für RLS, da komme ich in dem Fall nicht drum herum. Ansonsten würde ich sie auch weg lassen.
Wenn Du die View unter DATENBANKNAME.dbo.Abfragename speicherst, ist sie im weitesten Sinne ans (Datenbank)Schema gebunden. Das WITH-SCHEMABINDING-Statement meint darüber hinausgehend die Materialisierung / Determinisierung der Abfrage in Bezug auf die verwendeten Tabellen und (materialisierten / deterministischen) Unterabfragen.
Prüfe doch noch einmal ganz genau, ob Du für Deine konkrete Abfrage tatsächlich diese Materialisierung / Determinisierung benötigst. Aus meiner Erfahrung heraus bin ich da jedenfalls eher skeptisch.

Sofern Du eine Indexierung für die View verwenden möchtest, bedenke zugleich, dass jeder Schreibvorgang in einer zugrundeliegenden Tabelle sofort auch eine Anpassung und den damit verbundenen Schreibaufwand für die View-Indexierung auslöst. Das kann Datenoperationen in der Tabelle erhebllich verlangsamen. Für mich war das ein weiterer wesentlicher Grund, letztlich von schemagebundenen Views die Finger zu lassen.

Im Übrigen: Wenn in SQL Studio Änderungen an irgendeinem Datenbankobjekt vorgenommen werden, von dem andere Datenbankobjekte abhängen, dann meckert SQL Studio, und zwar auch dann, wenn keine explizite Schemabindung definiert ist. Ich gehe davon aus, dass SQL Server selbst diese Abhängigkeiten erkennt/prüft. Wenn also die Schemabindung dem Veränderungsschutz dienen soll, könntest Du prüfen, ob dieser Veränderungsschutz auch ohne Schemabindung besteht.

Viele Grüße
HansDampf06
Member: MadMax
Solution MadMax Feb 12, 2024 at 14:25:14 (UTC)
Goto Top
Moin ukulele,

was bekommst Du denn für einen Fehler? Wenn ich Dein Skrip inkl. schemabinding ausführe, funktioniert das:
create table tabelle (s int)
go
CREATE VIEW v
WITH SCHEMABINDING
AS
WITH t AS (
SELECT s
FROM dbo.tabelle
)
SELECT s
FROM t
go

Ich habe hier den SQL Server 2017.

An den beiden with dürfte es also nicht liegen, da muß noch was anderes sein.

Gruß, Mad Max
Member: HansDampf06
HansDampf06 Feb 12, 2024 at 15:20:06 (UTC)
Goto Top
Zitat von @MadMax:
Ich habe hier den SQL Server 2017.
An den beiden with dürfte es also nicht liegen, da muß noch was anderes sein.

Hier ist es SQL Server für Linux 2019 in der aktuell(st)en Version.

Ich habe mir die hier vorhandene Probier-/Test-View gegriffen - sie hat das aktive WITH-SCHEMABINDING-Statement. Das damit vor langer Zeit getestete SELECT-Konstrukt verbindet drei Tabellen über ein INNER JOIN und gibt von zwei der Tabellen ausgewählte Spalten zurück.
Das CREATE-Konstrukt dieser View habe ich für eine zweite Test-View geklont und dabei die dritte Tabelle in ein WITH-Statement ausgelangert. Wie bei @MadMax sind die Ausführung des CREATE-Konstrukts als auch der View selbst erfolgreich.
Im zweiten Schritt habe ich die zweite Tabelle als zweites Alias in das WITH-Statement ausgelagert. Wieder alles erfolgreich.
Schließlich sind im dritten Schritt alle Tabellen ausgelagert. Wie zuvor alles erfolgreich.

Welche genaue Fehlermeldung bekommst Du denn? Hast Du irgendwo ein Sternchen als Platzhalter im SELECT-Konstrukt? Wird auf eine andere View zugegriffen, die selbst nicht schemagebunden ist?

Viele Grüße
HansDampf06
Member: ukulele-7
ukulele-7 Feb 14, 2024 at 07:38:52 (UTC)
Goto Top
Zitat von @MadMax:

create table tabelle (s int)
go
CREATE VIEW v
WITH SCHEMABINDING
AS
WITH t AS (
SELECT s
FROM dbo.tabelle
)
SELECT s
FROM t
go

Ich habe hier den SQL Server 2017.

An den beiden with dürfte es also nicht liegen, da muß noch was anderes sein.
Du hast tatsächlich Recht. Habe gestern Abend nochmal alles umgebaut und es geht jetzt auch, keine Ahnung wo mein Fehler lag.