SQL Server vanlig tabelluttrykk

Det vanlige tabelluttrykket eller CTE er det navngitte resultatsettet, som ble introdusert i SQL Server 2005. Vanlig tabelluttrykk fungerer som en virtuell tabell med poster og kolonner som opprettes under utførelsen av en spørring med CTE og utgitt etter fullført spørring. Det kan refereres til i alle Select, Sett inn, oppdatering eller sletterklæring. Dette brukes også til å skape en visning.

CTE kan defineres av syntaksen nedenfor.

[Med […]]
Navn på CTE [(Navn på kolonner [,…])]
Som (spørring av CTE)
Velg * fra CTE

Eksempel:

Med CTE_NAME (kolonne1, kolonne2, kolonne3)
Som
(
Velg kolonne1, kolonne2, kolonne3
Fra tabell1
Hvor kolonne1> 500
)

I henhold til eksemplet, etter å ha definert CTE CTE_NAME, kan vi bruke CTE umiddelbart etter å ha definert den som en tabell. Nedenfor er et eksempel:

Velg fra CTE_NAME

Den vil returnere utgangen fra tre kolonner, kolonne1, kolonne2 og kolonne3.

Det kan brukes i innsatsen, slett, oppdater og slå sammen uttalelser også. Vi vil vise ett eksempel på hver nedenfor.

Flere CTE

Flere CTE kan brukes i en enkelt spørring.

Med CTE_NAME1 (kolonne1, kolonne2, kolonne3)
Som
(
Velg kolonne1, kolonne2, kolonne3
Fra tabell1
Hvor kolonne1> 100
)
SOM
(
Velg * fra CTE_NAME2
Hvor kolonne2> 200
)
Velg * fra CTE_NAME2

Spørringen over vil returnere postene fra tabell Tabell1 der kolonne1 er større enn 100 og kolonne2 er større enn 200.

Slett ved hjelp av CTE

CTE kan være veldig nyttig for å slette poster fra en tabell.

Med CTE_NAME (kolonne1, kolonne2, kolonne3)
Som
(
Velg kolonne1, kolonne2, kolonne3
Fra tabell1
Hvor kolonne1> 100
)
Slett fra CTE_NAME

Uttalelsen over vil slette postene fra basetabellen: Tabell Tabell1 der kolonnenes verdi er mer enn 100.

Dette er også den effektive måten å eliminere dupliserte oppføringer fra et bord. Nedenfor er eksemplet.

Med CTE_NAME (id, kolonne1, kolonne2, kolonne3, RN)
Som
(
Velg ID, Column1, Column2, Column3, Row_Number () Over (Partisjon etter ID -ordre etter ID) som RN
Fra tabell1
)
Slett fra CTE_NAME
Hvor CTE_NAME. RN> 1

Dette vil slette alle duplikatrappene fra tabellen Tabell1.

Sett inn ved hjelp av CTE

Vi kan sette inn et spesifikt datasett som er definert i en CTE i en annen tabell. Se på eksemplet nedenfor.

Med CTE_Insert (id, kolonne1, kolonne2, kolonne3)
Som
(
Velg ID, kolonne1, kolonne2, kolonne3
Fra tabell1
Hvor kolonne1> 200
)
/* for innsetting i en eksisterende tabelldest_table*/
Sett inn i dest_table (kolonne1, kolonne2, kolonne3)
Velg kolonne1, kolonne2, kolonne3 fra CTE_Insert
/ * For å opprette en ny tabell dest_table_new og sette inn dataene til CTE */
Velg kolonne1, kolonne2, kolonne3
Inn i dest_table_new

Uttalelsen over vil opprette tabellen med de tre kolonnene- kolonne1, kolonne2, kolonne3 og sette inn data i den.

Oppdatering ved hjelp av CTE

Begrepet oppdatering ved bruk av CTE er det samme som innsetting og sletting. La oss sjekke nedenfor eksemplet.

Med cte_update (id, kolonne1, kolonne2, kolonne3)
Som
(
Velg ID, kolonne1, kolonne2, kolonne3
Fra tabell1
Hvor kolonne1> 200
)
/* Oppdater basetabellen- Tabell1, for CTE for å øke verdien av kolonne1 med 100*/
Oppdater CTE_UPDATE
Sett kolonne1 = kolonne1+100
/*Oppdater en annen tabell - Dest_table, ved å bruke verdien av CTE*/
oppdatering a
Sett a.kolonne1 = b.Kolonne1
fra dest_table a
Bli med CTE_UPDATE b
på en.id = b.id

Slå sammen med CTE

Se eksemplet nedenfor for en bedre forståelse.

Med src_cte (id, kolonne1, kolonne2, kolonne3)
SOM
(
Velg ID, kolonne1, kolonne2, kolonne3 fra src_table
)
SLÅ SAMMEN
TGT_TBL som mål
Bruker SRC_CTE som kilde
På målet.ID = kilde.id)
Når matches da
Oppdateringssett mål.Kolonne1 = kilde.Kolonne1,
mål.Kolonne2 = kilde.Kolonne2,
mål.Kolonne3 = kilde.Kolonne3
Når du ikke blir matchet da
Sett inn (kolonne1, kolonne2, kolonne3) verdier (kilde.Kolonne1, kilde.Kolonne2, kilde.Kolonne3);

I spørringen over prøver vi å laste inn data trinnvis fra SRC_TABLE til TGT_TABLE.

Hvordan CTE-, Temp -tabell- og temp -variabel utsettes i SQL Server?

Fra de siste eksemplene blir vi kjent med bruken av CTE, og vi fikk en klar idé om hva som er CTE. Nå er forskjellen mellom CTE IS og Temp Table og Temp -variabel:

• • CTE trenger alltid minne, men TEMP -tabeller trenger en disk. Tabellvariabel bruker begge deler. Så vi skal ikke bruke CTE når det er mer volum av data.
  • Omfanget av tabellvariabelen er bare for partiet, og omfanget av temp -tabellen er for økten, og omfanget av CTE er bare for spørringen.

Konklusjon

CTE kan komme nyttig når du trenger å generere midlertidig resultatsett, og det kan nås i Select, Sett inn, oppdater, slette og fusjonere uttalelsen. Det kan være mye optimalisert med tanke på CPU og hukommelsesbruk.