SELEZIONE

Ci sono quattro tipi di SQL Server joins:

Join	Alias
INNER JOIN	JOIN
LEFT OUTER JOIN	LEFT JOIN
RIGHT OUTER JOIN	RIGHT JOIN
FULL OUTER JOIN	FULL JOIN

INNER JOIN

Quando si selezionano tutte le righe che rispettano l'uguaglianza della relazione tra due tabelle e non si considerano i valori NULL.

LEFT JOIN

Quando si selezionano tutte le righe che rispettano l'uguaglianza della relazione tra due tabelle e in aggiunta si selezionano i record che hanno un valore non NULL nel campo di relazione della prima tabella (di sinistra) e NULL nella seconda.

RIGHT JOIN

Quando si selezionano tutte le righe che rispettano l'uguaglianza della relazione tra due tabelle e in aggiunta si selezionano i record che hanno un valore non NULL nel campo di relazione della seconda tabella (di destra) e NULL nella prima.

CROSS JOIN

Indipendentemente da una relazione di uguaglianza su un campo si selezionano tutte le combinazioni di record tra le due tabelle creando un prodotto cartesiano.
Vedere CROSS APPLY. Esempio di come combinare le ore del giorno con una tipologia:

SELECT
    [hour],
    [DonationType]
FROM		@_TB_Hours
CROSS JOIN @_TB_DonationTypes
ORDER BY [hour], [DonationType]

Per provare usare il seguente script:

DECLARE @_TB_Hours TABLE	
(	
    [hour] char(2)
);
 
INSERT INTO @_TB_Hours
SELECT '00' UNION SELECT '01' UNION SELECT '02' UNION SELECT '03' UNION SELECT '04' UNION SELECT '05' UNION 
SELECT '06' UNION SELECT '07' UNION SELECT '08' UNION SELECT '09' UNION SELECT '10' UNION SELECT '11' UNION  
SELECT '01' UNION SELECT '12' UNION SELECT '13' UNION SELECT '14' UNION SELECT '15' UNION SELECT '16' UNION 
SELECT '17' UNION SELECT '18' UNION SELECT '19' UNION SELECT '20' UNION SELECT '21' UNION SELECT '22' UNION SELECT '23';
----------------------------------------------------------------------------------
DECLARE @_TB_DonationTypes TABLE	
(	
    [DonationType] varchar(10)
);
 
INSERT INTO @_TB_DonationTypes
SELECT 'don' UNION SELECT 'recur';
-----------------------------------------------------------------------------------
SELECT
    [hour],
    [DonationType]
FROM		@_TB_Hours
CROSS JOIN @_TB_DonationTypes
ORDER BY [hour], [DonationType]

Che produce in output:

Si possono aggiungere tutte le CROSS JOIN che si vuole per avere altre combinazioni

SELECT
	[AgentCode],
    [hour],
    [DonationType]
FROM		@_TB_AgentCodes
CROSS JOIN	@_TB_Hours
CROSS JOIN	@_TB_DonationTypes
ORDER BY [AgentCode], [hour], [DonationType]

Selezione con UNION

Serve ad unire due subset di dati omogenei, es per acquisire dati da due tabelle che hanno la stessa struttura.
Ci sono due UNION, una per cui il risultato finale non può avere duplicati (pensa allo stesso record che è contemporaneamente sulle due tabelle della union) ed un altra usando la clausola ALL che invece somma i duplicati.

SELECT ColonnaX FROM table1
UNION
SELECT ColonnaY FROM table2
ORDER BY ColonnaComune;

ATTENZIONE ClonnaX e ColonnaY DEVONO essere dello stesso tipo!

Esempio con la UNION ALL qiondi con i duplicati:

SELECT ColonnaX FROM table1
UNION ALL
SELECT ColonnaY FROM table2
ORDER BY ColonnaComune;

SELEZIONE AGGIORNAMENTO

Cancellazione

TRUNCATE TABLE

Rimuove tutte le righe da una tabella. E' simile a DELETE senza clausola WHERE, è tuttavia più rapida e utilizza un numero minore di risorse di sistema e del log delle transazioni.

DELETE - SELECT

Esempio realistico, dopo aver cancellato i dettagli ordine cancellerò le testate

DELETE PO_B_PURCHASE_ORDER_HEADER
FROM PO_B_PURCHASE_ORDER_HEADER AS POH
WHERE NOT EXISTS (SELECT *
				  FROM PO_B_PURCHASE_ORDER_DETAIL AS POD
				  WHERE POD.COMPANY_CODE = POH.COMPANY_CODE
					AND POD.DIVISION_CODE = POH.DIVISION_CODE
					AND POD.SHOP_CODE = POH.SHOP_CODE
					AND POD.ORDER_DATE = POH.ORDER_DATE
					AND POD.ORDER_NUMBER = POH.ORDER_NUMBER

INSERT - SELECT

INSERT INTO [AlertMailNotifications]
           ([AlertId]
           ,[MailRecipientId]
		   ,[SendingState]
           ,[CreateDateUTC])
SELECT TOP 1
	   A.[Id] AS AlertId
	   ,MR_AG.MailRecipientId
	   ,{0}
	   ,GETUTCDATE()
FROM		[Alerts]						AS A	
INNER JOIN	[AlerterThresholds]				AS AT		ON A.[ThresholdId] = AT.Id
INNER JOIN	[AlertGroups]					AS AG		ON AT.[AlertGroupId] = AG.Id
														AND AG.[Enabled] = 1
INNER JOIN	[MailRecipients_AlertGroups]	AS MR_AG	ON AG.[Id] = MR_AG.AlertGroupId
WHERE A.[TriggerDateUTC] BETWEEN @Date_TO 
								AND @Date_FROM
		AND A.[DispatchDateUTC] IS NULL
		AND A.[DispatchResult] = {1}

ALTRO

DECLARE @USER			AS NVARCHAR(50) = 'SPX_TEST_PINO',
		@UTCDATE		AS DATE = GETUTCDATE(),
		@COMPANY_CODE	AS VARCHAR(3) = '071',
		@DIVISION_CODE	AS VARCHAR(3) = '001'
 
PRINT 'Inserting missed "NEW APPOINTMENT" navigation control in UI_S_CONTROL'
INSERT INTO [dbo].[UI_S_CONTROL]
			([COMPANY_CODE]
			,[DIVISION_CODE]
			,[MODULE_ID]
			,[CONTROL_ID]
			,[CONTROLGROUP_CODE]
			,[TOPROFILE]
			,[DT_INSERT]
			,[USERINSERT]
			,[DT_UPDATE]
			,[USERUPDATE]
			,[ROWGUID])
SELECT 
	C.COMPANY_CODE
	,@DIVISION_CODE
	,'NAVIGATION'
	,'LEV2_MYAPPOINTMENT' 
	,'NAV' 
	,'Y' 
	,@UTCDATE 
	,@USER 
	,@UTCDATE 
	,@USER 
	,NEWID()
FROM		[dbo].[SY_COMPANY]		AS C
WHERE C.COUNTRY_CODE = 'NZ'
	-- Condition needed to exclude records altready inserted
	AND NOT EXISTS (SELECT CT.[ROWGUID]
					FROM [dbo].[UI_S_CONTROL] AS CT
					WHERE 1=1
						AND CT.COMPANY_CODE = C.COMPANY_CODE
						AND CT.DIVISION_CODE = @DIVISION_CODE					
						AND CT.MODULE_ID = 'NAVIGATION'
						AND CT.CONTROL_ID = 'LEV2_MYAPPOINTMENT'
						AND CT.CONTROLGROUP_CODE = 'NAV'

					)

ALTRO

DECLARE @HistoryRangeMinutes	AS INT = -240, -- 3 giorni (3x24x60)=4320
		@Date_FROM				AS DatetimeOffset(7) = GETUTCDATE(),
		@Date_TO				AS DatetimeOffset(7)
 
SET @Date_TO = DATEADD(MINUTE, @HistoryRangeMinutes, @Date_FROM) 
 
INSERT INTO [AlertMailNotifications]

          ([AlertId]
          ,[MailRecipientId]

		   ,[SendingState]

          ,[CreateDateUTC])

SELECT TOP 1
	   A.[Id] AS AlertId
	   ,MR_AG.MailRecipientId
	   ,{0}
	   ,GETUTCDATE()
FROM		[Alerts]						AS A	
INNER JOIN	[AlerterThresholds]				AS AT		ON A.[ThresholdId] = AT.Id
INNER JOIN	[AlertGroups]					AS AG		ON AT.[AlertGroupId] = AG.Id
														AND AG.[Enabled] = 1
INNER JOIN	[MailRecipients_AlertGroups]	AS MR_AG	ON AG.[Id] = MR_AG.AlertGroupId
WHERE A.[TriggerDateUTC] BETWEEN @Date_TO 
								AND @Date_FROM
		AND A.[DispatchDateUTC] IS NULL

		AND A.[DispatchResult] = {1}

COPY Creazione dinamica ed inserimento

Con la seguente si ha il risultato della INSERT, Select ma in più si crea dinamicamente la tabella di inserimento.
In pratica si fa la copia dei dati di una tabella copiandoli in un'altra:

SELECT 
   *   --Si può anche selezionare le colonne
INTO TabellaDaCreare
FROM TabellaDaCuiCopiare
WHERE 1=1 --Consente di fare una copia identica!
 
-- Se invece si vuole solo copiare la struttura:
SELECT *
INTO TabellaDaCreare
FROM TabellaDaCuiCopiare
WHERE 
1=2 --Così crea SOLO struttura'

UPDATE - SELECT

UPDATE
    Table
SET Table.col1 = other_table.col1,
    Table.col2 = other_table.col2
FROM        Table
INNER JOIN  other_table ON    Table.id = other_table.id

ES Con gli alias:

UPDATE CB
	SET CB.[severity] = T_CB.X50
FROM		[Fact_CorridorBreach]	AS CB 
INNER JOIN	@tmp_corridorBreach		AS T_CB	ON CB.[uid] = T_CB.[uid]

UPDATE if EXISTS or INSERT

L'aggiornamento o inserimento condizionale all'esistenza o meno di un record ha diversi approcci efficienti in base alle condizioni\ambito in cui ci si trova. Vedere qui stackoverflow

Opzione 1, ON DUPLICATE KEY

Buona soluzione per records multipli per inserts/updates. Tuttavia usa solo la verifica su una chiave primaria.

INSERT INTO table (id, user, date, points)
    VALUES (1, 1, '2017-03-03', 25),
           (2, 1, '2017-03-04', 25),
           (3, 2, '2017-03-03', 100),
           (4, 2, '2017-03-04', 150)
    ON DUPLICATE KEY UPDATE points = VALUES(points)

Opzione 2, ROWCOUNT

Per piccole quantità di dati:

UPDATE mia_tabella SET campo1='Peppino' WHERE id=1234
IF @@ROWCOUNT=0
   INSERT INTO mia_tabella(campo1) VALUES ('Peppino');

Opzione 3, MERGE

Bene per grandi query. Per leggere da una tabella e inserting/updating in un'altra di conseguenza.

MERGE dbo.Test WITH (SERIALIZABLE) AS T
USING (VALUES (3012, 'john')) AS U (id, name)
    ON U.id = T.id
WHEN MATCHED THEN 
    UPDATE SET T.name = U.name
WHEN NOT MATCHED THEN
    INSERT (id, name) 
    VALUES (U.id, U.name);

Raggrupamenti

GROUP BY

Ecco un esempio che verifica anche in intervallo di date mediante la BETWEEN

DECLARE @Dt_Day AS VARCHAR(2) = '16'
 
SELECT 
	MONTH(tms) as mese , DAY(tms) as giorno, COUNT(*) as nrecord
FROM [ESB].[scr].[samples] with (nolock)
WHERE tms BETWEEN '2016-11-' + @Dt_Day + ' 00:00:00.00' AND '2016-11-' + @Dt_Day + ' 23:59:59.999'
	--AND VALUE <> 0.000000
	AND modbusid < 90000	
GROUP BY MONTH(tms),DAY(tms)
ORDER BY MONTH(tms),DAY(tms)

Attenzione, indicando delle date stringa nella BETWEEN, avverrà una approssimazione per cui i primi dati di mezzanotte del 17/11/2016 verranno selezionati come appartenenti al giorno prima.

StoredProcedure, Views, Functions

StoredProcedure

Creazione:

ToDo

Viste

Creazione:

CREATE VIEW [dbo].[v_Projects]
AS
	SELECT 
		 P.[IDProject]
		--,PV.[IDVendor]
		,P.[IDOwner]
		,P.[FullName]
		,P.[Description]
		,(SELECT 
				MIN(A.DateFrom)
			FROM .[dbo].[Activities] AS A
			WHERE P.[IDProject] = A.[IDProject]
		) AS [DateFrom]
		,(SELECT 
				MAX(A.DateTo)
			FROM .[dbo].[Activities] AS A
			WHERE P.[IDProject] = A.[IDProject]
		) AS [DateTo]
		--,P.[ProgressPercentage]
		,(SELECT 
				(SUM(A.[ProgressPercentage]) 
				/ COUNT(*))
			FROM .[dbo].[Activities] AS A
			WHERE P.[IDProject] = A.[IDProject]
		) AS [ProgressPerc]
	FROM		[dbo].[Projects]		AS P

Funzioni

Funzioni scalari

Creazione:

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL  
    DROP FUNCTION ufnGetInventoryStock;  
GO  
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)  
RETURNS int   
AS   
-- Returns the stock level for the product.  
BEGIN  
    DECLARE @ret int;  
    SELECT @ret = SUM(p.Quantity)   
    FROM Production.ProductInventory p   
    WHERE p.ProductID = @ProductID   
        AND p.LocationID = '6';  
     IF (@ret IS NULL)   
        SET @ret = 0;  
    RETURN @ret;  
END;

Funzioni tabellari

Creazione:

/*
-- =============================================
-- Author:		Giuseppe AINO
-- Create date: 01/03/2019
-- Description:	Data una stringa CSV restituisce una variabile tabella con tante righe quante 
				sono le porzioni di stringa separate da virgola o altro carattere chiave
-- =============================================
 
Es:
DECLARE @txtSQL_View as VARCHAR(MAX) = '' --??
 
SELECT
	(CASE WHEN (CHARINDEX(' AS ', Value) > 0) THEN
		SUBSTRING(Value, 0, CHARINDEX(' AS ', Value) )
	ELSE
		Value
	END) AS Campo
FROM dbo.Split(@txtSQL_View, ',')
*/
ALTER FUNCTION [dbo].[Split](
		 @myString nvarchar (max),
		 @Delimiter nvarchar (10))	
	RETURNS @ValueTable TABLE ([Value] nvarchar(max))
BEGIN
	DECLARE	@NextString nvarchar(4000)
			, @Pos int
			, @NextPos int
			, @CommaCheck nvarchar(1)
	--Initialize
	SET @NextString = ''
	SET @CommaCheck = right(@myString,1) 
	--Check for trailing Comma, if not exists, INSERT
	--if (@CommaCheck <> @Delimiter )
	SET @myString = @myString + @Delimiter
 
	--Get position of first Comma
	SET @Pos = charindex(@Delimiter,@myString)
	SET @NextPos = 1
 
	--Loop while there is still a comma in the String of levels
	WHILE (@pos <>  0)  
	BEGIN
		SET @NextString = LTrim(RTrim(
							REPLACE(
								REPLACE(
									REPLACE(SUBSTRING(@myString,1,@Pos - 1),
										CHAR(9), ''),	-- Tabulazione
										CHAR(10), ''),	-- Inizio riga
										CHAR(13), '')	-- Ritorno a capo
								))
		INSERT INTO @ValueTable ([Value]) 
			Values 
		(@NextString)
		SET @myString = SUBSTRING(@myString, @pos +1, len(@myString))
 
		SET @NextPos = @Pos
		SET @pos  = charindex(@Delimiter,@myString)
	END
	RETURN
END

Query distribuite

OPENQUERY

Link interno Nozioni varie
Link interno Ottimizzazioni, LinkedServer vs Openquery

Attenzione la sintassi della query è la stessa del SQL del DB sorgente, quindi se ad es si è configurato un linked server ad un DB Oracle la sintassi da usare deve essere secondo PL-SQL e non T-SQL !!!

Mappa e Links

MS SQL | Funzioni base

Navigation

Help

Tools

WIDGETS

Widgets

Recent changes

Wanted articles

Who is online?

Article tools

MSSQL:Comandi di Query

From Aino Wiki

Contents

SELEZIONE

INNER JOIN

LEFT JOIN

RIGHT JOIN

CROSS JOIN

Selezione con UNION

SELEZIONE AGGIORNAMENTO

Cancellazione

DELETE - SELECT

INSERT - SELECT

COPY Creazione dinamica ed inserimento

UPDATE - SELECT

UPDATE if EXISTS or INSERT

Opzione 1, ON DUPLICATE KEY

Opzione 2, ROWCOUNT

Opzione 3, MERGE

Raggrupamenti

GROUP BY

StoredProcedure, Views, Functions

StoredProcedure

Viste

Funzioni

Funzioni scalari

Funzioni tabellari

Query distribuite

OPENQUERY

Mappa e Links