Login Login
MORE

WIDGETS

Widgets

Wanted articles
  • No information available
Who is online?
  • Nobody is logged in.
Article tools

MSSQL:Tips

From Aino Wiki

Jump to: navigation, search

Sito dedicato ai tips di SQL Server e per imparare: mssqltips

Contents

Dati

Operazioni sulle Stringhe

Conversioni

Da stringa a data, String to DateTime

Lista maggiormente esaustiva su: sqlusa 

SELECT convert(datetime, '23-10-2025', 105) -- dd-mm-yyyy
SELECT convert(datetime, '23/10/2020', 103) -- dd/mm/yyyy
SELECT convert(datetime, '10/25/2020', 101) -- mm/dd/yyyy
SELECT convert(datetime, '23/10/22', 3) -- dd/mm/yyyy
SELECT convert(datetime, '1/25/22', 1) -- mm/dd/yy
SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(datetime, '2019/05/23', 111) -- yyyy/mm/dd

oppure semplicemente: 

SELECT {ts '2014-10-11 23:59:59.000'}
Da stringa a numero
Usando CAST o CONVERT
SELECT CAST(VariabileChar AS INT) FROM Tabella
-- o
SELECT CONVERT(INT, VariabileChar) FROM Tabella
--o
SELECT 
   [Speed]
  ,(CAST([Speed] AS DECIMAL(10, 0)) * 10) AS SpeedOk_Decimal
  ,CAST(CAST((CAST([Speed] AS DECIMAL(10, 1)) * 10) AS integer) AS NVARCHAR(100)) AS SpeedOk_INT_NVARCHAR
FROM Tabella
Soluzioni ad errori

Caso in cui il numero è un esponenziale del tipo: '5.9999999999999998E-2' la seguente conversione non funzionerà e darà errore "Error converting data type varchar to numeric." 

--Il problema è quella 'E' con il '-2' !!!
SELECT CAST('5.9999999999999998E-2' AS DECIMAL(7,2))

Soluzione: 

--Il tipo FLOAT è predisposto ad interpretare i numeri esponenziali '*E-n' o '*En'
SELECT CAST('5.9999999999999998E-2' AS FLOAT)
Da varbinary a Stringa
DECLARE @varbinaryField varbinary(max);
SET @varbinaryField = 0x21232F297A57A5A743894A0E4A801FC3;
 
SELECT CONVERT(varchar(max),@varbinaryField,2)
--Producendo:            21232F297A57A5A743894A0E4A801FC3

Verifiche

Per verificare contemporaneamente che un campo non sia NULL e che non abbia stringa vuota 
SELECT *
FROM [Log] as l (nolock)
WHERE ISNULL([Username], '') <> ''

Ricerche

CHARINDEX

CHARINDEX(stringPattern, stringValue, startPos). E' in BASE 1 ovvero se il pattern corrisponde già sul primo carattere restituisce 1. 

DECLARE @WBS	AS Varchar(20) = 'A_12.33'
 
SELECT @WBS	AS StringSource
		, CHARINDEX('_', @WBS,0) AS FoundAtPosition
Sottostringa da pattern

Il seguente esempio estrae una parte di una stringa a partire dalla posizione di un carattere pattern 

DECLARE @WBS	AS Varchar(20) = 'A_12.33'
 
SELECT @WBS	AS StringSource
		, SUBSTRING(@WBS
			, CHARINDEX('_', @WBS) + 1
			, LEN(@WBS)
			) AS Desired_SubString

La lunghezza della sottostringa da estrarre può non essere definita esattamente ma che almeno sia maggiore dei caratteri effettivamente da estrarre.

Contare occorrenze in stringa

Contare le occorrenze di una sottostringa pattern in un altra. Segue soluzione per la ricerca di un carattere (da stackoverflow): 

Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))

Estrazioni

SUBSTRING

Si estraggono parti di un campo stringa. Questa è la sintassi:
SUBSTRING (expression, start, length )
expression
E' il campo ostringa su cui lavora. start
E' la posizione in base 1 su cui partire per l'estrazione. length
Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

DECLARE @testo AS varchar(max) = '123456789'
SELECT SUBSTRING(@testo, 4, LEN(@testo))

Estrarrà: 456789. LEN indica la lunghezza dell'intero testo, non è importante se dalla posizione 4 va oltre la lunghezza della stringa indicata!

RIGHT - LEFT

Restituisce la parte finale di una stringa di caratteri, di lunghezza pari al numero di caratteri specificato. Questa è la sintassi:
RIGHT ( character_expression , integer_expression )
character_expression
E' il campo stringa su cui lavora. integer_expression
È un numero intero positivo che specifica il numero di caratteri di character_expression verrà restituito. 

DECLARE @testo AS varchar(max) = '09876543210'
SELECT RIGHT(@testo, 5) -- restituisce: 43210

Se la stringa è minore di 5 caratteri restituirà fintanto che ce n'è!!!

REPLACE

Sostituzioni in una stringa mediante un pattern da cercare e sostituire con altra stringa. 

SELECT REPLACE('[Nome] è ritornato. [Nome] ha aperto la porta. [Nome] si è spaventato','[Nome]','Giuseppe'); 
--Restituirà:
--Giuseppe è ritornato. Giuseppe ha aperto la porta. Giuseppe si è spaventato

Elaborazioni

Dare una occhiata alla parte precedente "Estrazioni".

Padding left right

Padding LEFT 

Select Replace(Str(@MyInt, 4), ' ' , '0')

Padding RIGHT
Supponendo di volere una stringa sempre lunga 3 caratteri e le cifre significative siano a destra prese da altra variabile, vedi l'esempio seguente. 

DECLARE @field AS varchar(3) = '3'
SELECT RIGHT('000' + ISNULL(@field, ''), 3) -- Produce '003'
 
SELECT LEFT(ISNULL(@field,'') + '000', 3) -- Produce '300'

Pulizia ricorsiva caratteri non validi

DECLARE @target as varchar(10) = 'aa@bb!cc!!dd~ee@'
 
SELECT @target = REPLACE(@target, invalidChar, '-')
FROM (VALUES ('~'),(''''),('!'),('@'),('#')) AS T(invalidChar)
 
SELECT @target

Produrrà 

aa-bb-cc--

Resultset di una query in una stringa

Supponendo di voler inserire in una variabile testo il risultato di una query si potrà farlo valorizzando la variabile testo aggiungendo i valori dei campi per ciascun record selezionato, notare il '+=' !
segue esempio: 

DECLARE @ResultTxt	AS varchar(max) = ''
 
SELECT 
	@ResultTxt += CAST(X.NrDuplication as varchar) + ', "' + X.PrjectName + '"
'
FROM	(SELECT
			COUNT(*) AS NrDuplication
			, [FullName] AS PrjectName
		FROM [RS_Plan_ProjectVCVI_Test].[dbo].[Projects]	AS S
		WHERE 1=1
			AND S.[WBS] NOT LIKE '%.%'
		GROUP BY S.[FullName]) AS X
WHERE X.NrDuplication > 1
 
SELECT @ResultTxt

Naturalmente mi son fermato a due campi di ciascun record ma è importante indicare puntualmente ogni campo. Notare il ritorno a capo inserito per ciascun record selezionato.

Da StackOverflow, esecuzione dinamica: 

DECLARE @CommaString 	varchar(max) = '',
		@QrySQL 		nvarchar(max) = ''
 
SET @QrySQL = 'SELECT @Result += cast(number as varchar(10))+'',''
FROM master..spt_values
WHERE type = ''P'';
'
 
EXEC sp_executesql @QrySQL, N'@Result varchar(max) OUT', @CommaString OUT
 
SELECT @CommaString

Operazioni sulle Date

Operazioni

Somma

DATEADD(datepart,number,date)
SELECT DATEADD(day, -1, GETUTCDATE()) AS dataMenoUnGiorno, 
       GETUTCDATE() AS dataAttuale

Qualche enumerativo del primo parametro, DATEPART: year, month, week, day, hour, minute, second

Differenza

Si usa la DATEDIFF(), il primo parametro indica in quale misura convertire il risultato. Supponendo di volere la differenza in ore tra UTC e l'ora locale: 

SELECT 
     GETUTCDATE() UTC_Time, 
     GETDATE() AS Local_Time, 
     DATEDIFF(HOUR, GETUTCDATE(), GETDATE()) AS [hour of Difference]

Data senza orario

Data una data lunga con anche l'orario si può convertirla in stringa di 10 caratteri troncando così l'orario, successivamente si può convertire in data questa volta l'orario non è più preso in considerazione. 

SELECT
	   CONVERT(date, GETDATE(), 103) AS Data_CONVERSIONE_BANALE,           --2018-11-07
           CAST(CONVERT(date, GETDATE(), 103)  as datetime),                   --2018-11-07 00:00:00.000
	   CONVERT(date, CONVERT(varchar(10), GETDATE(), 103), 103) AS Data_Data_senzaOrario,		--2017-10-18
	   CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 103), 103) AS Data_Data_mezzaNotte,	--2017-10-18 00:00:00.000
	   CONVERT(varchar(10), GETDATE(), 103) AS DataTesto,					--18/10/2017
	   GETDATE() AS DataOriginaria								--2017-10-18 16:29:22.040

Conversioni

Da data a stringa

Usando CONVERT(VARCHAR, @MiaData, 120) O CONVERT(VARCHAR, @MiaData, 121) 

DECLARE @MiaData DATETIME
SET @MiaData = '2016-12-30 16:00:00.001'
--
-- Conversione a stringa
--
SELECT CONVERT(VARCHAR, @MiaData, 120)           --2016-12-30 16:00:00
SELECT LEFT(CONVERT(VARCHAR, @MiaData, 120), 10) --2016-12-30
SELECT CONVERT(VARCHAR, @MiaData, 121)           --2016-12-30 16:00:00.001
SELECT CONVERT(varchar, '2020-01-01', 106)       --dd MMM yyyy    es:  01 Jan 2020
 
SELECT CONVERT(VARCHAR, GETDATE(), 103)		-- 26/11/2022
SELECT CONVERT(VARCHAR, GETDATE(), 106)		-- 26 Nov 2022
SELECT CONVERT(VARCHAR, GETDATE(), 112)		-- 20221126
SELECT CONVERT(VARCHAR, GETDATE(), 108)		-- 16:00:00
Usando CAST
SELECT CAST(GETDATE() AS varchar(20));

Tabella mesi inglesi in formato stringa 

-- CONVERT(varchar, '2020-01-01', 106) AS strData_dd_MMM_yyy -- 01 Jan 2020
01 Jan 2020
01 Feb 2020
01 Mar 2020
01 Apr 2020
01 May 2020
01 Jun 2020
01 Jul 2020
01 Aug 2020
01 Sep 2020
01 Oct 2020
01 Nov 2020
01 Dec 2020

Ottenere solo l'orario

SELECT CONVERT(varchar, GETDATE(), 108);    --Restituisce es.:    15:35:27
SELECT CONVERT(varchar(5), GETDATE(), 108); --Restituisce es.:    15:35
SELECT CONVERT(varchar(2), GETDATE(), 108); --Restituisce es.:    15

Funzioni

Settimana dell'anno

Usando DATEPART e convertendo nel tipo "WK" 

SELECT DATEPART(WK, GETDATE())

ATTENZIONE se la lingua corrente del server è Inglese i giorni della settimana iniziano dalla domenica quindi calcolare la settimana dell'anno sulla domenica darà come numero la settimana successiva ovvero quella che si avrebbe lunedì! Si può cambiare il primo giorno della settimana come segue: 

SELECT @@DATEFIRST --Restituisce il numero del primo giorno della settimana
--se la lingua è inglese, @@DATEFIRST restituirà   7
 
SET DATEFIRST 1; --Per impostare il primo giorno a lunedì

ATTENZIONE SET DATEFIRST 1; non si può usare in una vista.
Una possibile soluzione per lasciare il server in Inglese col primo giorno nella domenica ma aggiustare le viste con il seguente accorgimento esempio: 

--Domenica 26 aprile è in realtà della settimana 17 e non 18 come esprime la colonna W1
--La soluzione è di togliere un giorno a qualsiasi data da processare
SELECT DATEPART(WEEK, '2020-04-26') AS W1,
       DATEPART(WEEK, DATEADD(DAY, -1, '2020-04-26')) AS W2

ATTENZIONE il comportamento standard di SQL Server è quella per cui i primi giorni dell'anno sono assegnati alla settimana 1 ma non corrisponde allo standard normalmente adottato. Normalmente, se il giovedì della della prima settimana rientra nel nuovo anno, l'intera settimana sarà la settimana 1 altrimenti sarà la settimana 53!
Per adottare la numerazione standard (ISO 8601 Calendar): 

SELECT DATEPART(ISOWK, Getdate()) AS Week_ISO_8601

Giorno della settimana

Impostando il primo giorno della settimana a lunedì, volendo avere il numero progressivo del giorno: 

SET DATEFIRST 1 -- Imposta a lunedì il 1 giorno della settimana
 
SELECT DATEPART(WEEKDAY, GETDATE()) --Visualizzerà il numero progressivo del giorno della settimana es 5=venerdì

Raggruppamento di dati

Le partizioni di dati. OVER, PARTITION, ROW_NUMBER()

Anche dette CTE (Common Table Expression), da microsoft technet.
E' possibile raggruppare i dati di una query per estrarre di ciascuna partizione solo i records che soddisfino una condizione.
La clausola OVER consente di aggregare informazioni senza usare GROUP BY.

Es. 1.1

Esempio semplice che consente di sommare i costi 'Cost' per ciascun ordine identificato da 'OrderNum'.
In altri termini supponendo che la tabella 'Orders' contenga più righe per uno stesso 'OrderNum' ogniuna delle quali ha il suo valore in 'Cost', il risultato è un elenco di costi, ciascun rigo contiene la somma dei costi per ordine.
SELECT SUM(Cost) OVER () AS Cost
     , OrderNum
FROM Orders
Continuando, supponendo di volere i costi 'Cost' per ciascun cliente (identificato da 'CustomerNo') sulla stessa tabella degli ordini 'Orders' dobbiamo considerare che tali costi riguardano più ordini e quindi dovremmo partizionare per gruppi di clienti e non per singoli ordini.
SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost

  , OrderNum
  , CustomerNo


FROM Orders

Es. 1.2

Altro esempio semplice, supponiamo che da una lista di utenti in cui v'è la data di compleanno, volessimo una colonna che indicasse la posizione in classifica iniziando dal più vecchio e finire al più giovane (il più vecchio avrà il numero 1).
SELECT
     [ID]
    ,[Nome]
    ,[Compleanno]
    ,ROW_NUMBER() over (ORDER BY Compleanno) AS Posizione
FROM	People
ORDER BY ID
Produrrà la seguente tabella:
ID Nome Compleanno Posizione di anzianità
1 Francesco 25/10/2005 3
2 Mario 13/02/1967 1
3 Giuseppe 20/12/1971 2
4 Vito 15/12/2005 4
Supponendo di voler visualizzare solo quelli dalla posizione 2 alla 3:
SELECT
     [ID]
    ,[Nome]
    ,[Compleanno]
    ,ROW_NUMBER() over (ORDER BY Compleanno) AS Posizione
FROM	Utenti
WHERE RowNumber BETWEEN 2 AND 3
ORDER BY ID

Es. 1.3

Dagli esmpi del sito di Microsoft Clausola OVER
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);
Producendo il seguente output 
SalesOrderID ProductID   OrderQty Total       Avg         Count       Min    Max
------------ ----------- -------- ----------- ----------- ----------- ------ ------
43659        776         1        26          2           12          1      6
43659        777         3        26          2           12          1      6
43659        778         1        26          2           12          1      6
43659        771         1        26          2           12          1      6
43659        772         1        26          2           12          1      6
43659        773         2        26          2           12          1      6
43659        774         1        26          2           12          1      6
43659        714         3        26          2           12          1      6
43659        716         1        26          2           12          1      6
43659        709         6        26          2           12          1      6
43659        712         2        26          2           12          1      6
43659        711         4        26          2           12          1      6
43664        772         1        14          1           8           1      4
43664        775         4        14          1           8           1      4
43664        714         1        14          1           8           1      4
43664        716         1        14          1           8           1      4
43664        777         2        14          1           8           1      4
43664        771         3        14          1           8           1      4
43664        773         1        14          1           8           1      4
43664        778         1        14          1           8           1      4

Es. 2.1

Ecco un esempio più complesso tratto da un caso reale.
In questo caso lo scopo è raggruppare i dati in partizioni comuni per determinati campi (port1, port2, shipCode) ed in ciascuna di queste partizioni vogliamo scegliere il primo record [ho una riserva sull'ordinamento usato all'interno della partizione perché non sempre il primo del gruppo è il record più recente].
Da notare l'uso di una tabella\variabile temporanea 'idealWithDateTimes ', l'uso della funzione BETWEEN per selezionare su un intervallo di date.
DECLARE @dateFrom	DATETIME = convert(datetime, '2014-09-12 17:30:00', 120) 
DECLARE @dateTo		DATETIME = dateadd(DAY, 4, @dateFrom);
 
WITH idealWithDateTimes AS
(
	SELECT ROW_NUMBER() OVER (PARTITION BY si.port1, si.port2, sa.shipCode 
                                  ORDER BY si.shipCode DESC, si.shipClassId DESC) AS Number,
		    si.id, 
		    si.companyCode, 
		    i.shipClassId, 
		    si.port1, si.port2,
		    sa.shipCode, 
		    si.geoPosition1, si.geoPosition2
	FROM		Segments	SA (NOLOCK)
	INNER JOIN	Segments	SI (NOLOCK) ON si.port1 = sa.port1 
                                                       AND si.port2 = sa.port2 
                                                       AND si.companyCode = sa.companyCode
	INNER JOIN	ShipNames	N  (NOLOCK) ON n.companyCode = sa.companyCode 
                                                       AND n.shipCode = sa.shipCode
	WHERE sa.[type] = 'Actual' 
			AND si.[type] = 'Ideal'
	-- Date range
	AND (sa.departureDateTimeLT1 BETWEEN @dateFrom AND @dateTo 
		OR sa.departureDateTimeLT2 BETWEEN @dateFrom AND @dateTo)
	-- Remove wrong Ideals (different ship/shipclass)
	AND (si.shipCode = sa.shipCode
			OR (si.shipCode IS NULL)
			OR (si.shipClassId = n.shipClassId)
		)
)
 
SELECT 
	i.id, i.companyCode, i.shipClassId, i.port1, i.port2, i.shipCode, 
	i.geoPosition1, i.geoPosition2
FROM idealWithDateTimes i
WHERE i.Number = 1 -- This select the first Ideal for each ambiguity case
	AND I.shipCode = 'AU'
ORDER BY companyCode, shipCode

Es. 2.2

Altro esempio più semplice, in realtà si demanda l'esecuzione di uno script SQL infilato in una variabile di testo. E' una query che agisce su Linked server ma riguardo l'uso della PARTITION BY, si vuole selezionare la prima riga del gruppo di informazioni ecco perchè si usa il trucco dell'ordinamento di partizione sul ID Articolo. 

EXECUTE(';WITH AO AS
(
   SELECT
		   ROW_NUMBER() OVER (PARTITION BY idarticolo ORDER BY idarticolo) AS Number,
 
	      idarticolo, codicearticolo, um, costoarticoloimpianto, qtaimballo
   FROM [{0}].itcr.ftGetArticoliOrdinabiliImpianto (''{1}'',''{2}'',''{2}'') 
)
SELECT 
	AO.idarticolo, AO.codicearticolo, AO.um, AO.costoarticoloimpianto, AO.qtaimballo
FROM AO
WHERE AO.Number = 1
	') AT [{3}]

Chiavi

Unique Identifier

Come generare il valore per un campo UniqueIdentifierm sarebbe il GuiId di C# (Chiavi di ricerca: gui id, guid, newguid). 

SELECT NEWID()
GO
-- Genererà un campo come il seguente
14F9C229-7D23-4C43-8407-2553DFA48B36

Per assegnare questo valore in una variabile di tipo dedicato: 

DECLARE @ID_Provolone uniqueidentifier
SET @ID_Provolone = NEWID()

Reset ID

Come eseguire un reset di un ID (campo definito IDENTITY), chiave primaria, di una tabella con autoincremento? Va col nome di ID Reseed.
Es. 

DBCC CHECKIDENT ('[Classes]', RESEED, 37)

Il prossimo item della tabella 'Classes' sarà inserito con ID 38!

Tabelle temporanee

Variabile tabella

Creare una variabile tabella con dati parziali pronti per fare una Join con altre tabelle 

DECLARE @tempProducts1 TABLE	
(	
    IDProduct INT,
    Testo NVARCHAR(MAX),
    ChiaveEsternaID INT
);
 
INSERT INTO @tempProducts1
SELECT 
   *
FROM Products
WHERE 1=1

Tabella Temporanea

Più efficiente rispetto ad una variabile tabella ma va fatta una DROP al termine dell'uso. 

	CREATE TABLE #POD 
		(	  COMPANY_CODE				NVARCHAR(3)
			, DIVISION_CODE				NVARCHAR(3)
			, SHOP_CODE					NVARCHAR(3)
			, ORDER_NUMBER				NVARCHAR(6)
			, ORDER_DATE				DATE
			, ORDER_LINENUMBER			SMALLINT
			, PURCHASE_ORDER_TYPE_CODE	NVARCHAR(3)
			, PRODUCTION_ORDER_NUMBER	NVARCHAR(6)
			, PRODUCTION_ORDER_DATE		DATE)
 
	INSERT INTO #POD
	SELECT 
			  POD.COMPANY_CODE				
			, POD.DIVISION_CODE				
			, POD.SHOP_CODE					
			, POD.ORDER_NUMBER				
			, POD.ORDER_DATE				
			, POD.ORDER_LINENUMBER			
			, POD.PURCHASE_ORDER_TYPE_CODE	
			, POD.PRODUCTION_ORDER_NUMBER	
			, POD.PRODUCTION_ORDER_DATE		
	FROM		[PO_B_PURCHASE_ORDER_DETAIL]	AS POD (NOLOCK)
	INNER JOIN	[PO_B_PURCHASE_ORDER_HEADER]	AS POH (NOLOCK)	ON POD.COMPANY_CODE		= POH.COMPANY_CODE
																AND POD.DIVISION_CODE	= POH.DIVISION_CODE
																AND POD.SHOP_CODE		= POH.SHOP_CODE
																AND POD.ORDER_NUMBER	= POH.ORDER_NUMBER
																AND POD.ORDER_DATE		= POH.ORDER_DATE
	INNER JOIN	[SY_GENERAL_STATUS]				AS GS  (NOLOCK) ON POH.COMPANY_CODE		= GS.COMPANY_CODE
																AND POH.DIVISION_CODE	= GS.DIVISION_CODE
																AND POH.ORDER_STATUS	= GS.STATUS_CODE
																AND GS.ENTITY_TYPE_CODE = 'PO'
	WHERE POD.COMPANY_CODE		= @COMPANY_CODE
		AND POD.DIVISION_CODE	= @DIVISION_CODE
		AND POD.SHOP_CODE		= @SHOP_CODE
		AND (POH.ORDER_STATUS	= @ORDER_STATUS 
				OR @ORDER_STATUS IS NULL)		
		--Only PO with Status still Active
		AND ((GS.[DT_END] IS NULL AND GETUTCDATE() >= GS.[DT_START]) 
				OR (GETUTCDATE() BETWEEN GS.[DT_START] AND GS.[DT_END])
			)	
--------------------	etc
DROP TABLE #POD

Il DROP potrebbe essere condizionale, così: 

DECLARE @TABLE_NAME AS  VARCHAR(50)  = ''
 
SET @TABLE_NAME = 'tempdb..#POD'
if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U')  
 
   and o.id = object_id(@TABLE_NAME)
)
	BEGIN
	Print @TABLE_NAME + ' exists, dropping'
	DROP TABLE #POD;
	END
ELSE
	BEGIN
	Print @TABLE_NAME + ' NOT exists'
	END

Più semplicemente così: 

	IF (OBJECT_ID('tempdb..#TableList') IS NOT NULL)
	BEGIN
		DROP TABLE #TableList
		Print '#TableList dropped.'
	END
	ELSE
		Print 'OK #TableList doesn''t exists.'

Global temporary table

Una tabella temporanea globale, la costruzione è identica a quella già vista ma occorre precedere il nome con 2 cancelletti: ##nomeTabella
Essenziale nei casi in cui la si crea dinamicamente e poi la si vuol usare in contesto differente. 

--Primo contesto
SET @cmd = ' SELECT * INTO ##temp3 FROM
            (
                select * from sometable
            ) x pivot
            (
                max(buildrate)
                for name in ('+ @columns +')
            ) as y '
EXECUTE(@cmd);

--Secondo contesto altrimenti con una semplice tabella temporanea, qui 
--NON SAREBBE PIU' DISPONIBILE
SELECT *
INTO #temp3
FROM ##temp3;

DROP TABLE ##temp3;

SELECT *
FROM JOIN #temp3 
LEFT ...;

Ritardare l'esecuzione

Da MSSQL server 2016 in su si può inserire una pausa (sleep) prima di riprendere l'esecuzione usando il comando WAITFOR (da Microsoft doc).
Blocca l'esecuzione di un batch, una stored procedure o una transazione fino all'ora specificata o fino a quando non è trascorso l'intervallo di tempo specificato oppure finché un'istruzione specificata non modifica o restituisce almeno una riga.
Blocca l'esecuzione per un massimo di 24 ore, ci sono due versioni, una con un valore di time out ed una con indicazione dell'ora di termine, segue la sintassi: 

WAITFOR   
{  
    DELAY 'time_to_pass'   
  | TIME 'time_to_execute'   
  | [ ( receive_statement ) | ( get_conversation_group_statement ) ]   
    [ , TIMEOUT timeout ]  
}

Esempio: 

DECLARE @LogDescription AS varchar(max);
 
SET @LogDescription = 'Inizio: ' + CONVERT(varchar, GETDATE(), 121);
RAISERROR(@LogDescription,0,1) WITH NOWAIT;
 
BEGIN  
	SET @LogDescription = 'inizio (dentro): ' + CONVERT(varchar, GETDATE(), 121);
	RAISERROR(@LogDescription,0,1) WITH NOWAIT;
 
	--!!!!!!!!!!!!!!!!!!!!!!
    WAITFOR DELAY '00:00:10';  --10 secondi di attesa
 
	SET @LogDescription = 'Fine (dentro): ' + CONVERT(varchar, GETDATE(), 121);
	RAISERROR(@LogDescription,0,1) WITH NOWAIT;
END;  
SET @LogDescription = 'Fine: ' + CONVERT(varchar, GETDATE(), 121);
RAISERROR(@LogDescription,0,1) WITH NOWAIT;

Che produce: 

Inizio: 2023-05-10 11:34:57.353
inizio (dentro): 2023-05-10 11:34:57.353
Fine (dentro): 2023-05-10 11:35:07.360
Fine: 2023-05-10 11:35:07.360

NOTARE

  • Durante l'esecuzione dell'istruzione WAITFOR la transazione è in esecuzione e pertanto non possono essere eseguite altre richieste nell'ambito della stessa transazione.
  • L'inclusione di WAITFOR rallenterà il completamento del processo SQL Server e può generare un messaggio di timeout nell'applicazione. Se necessario, regolare l'impostazione di timeout per la connessione a livello di applicazione.

Elaborazioni

Massimo o Minimo tra colonne diverse

Se si cercasse il minimo o massimo in una stessa colonna sarebbe banale ma su più colonne la seguente soluzione usa creare una tabella virtuale su ogni rigo-record definendo una colonna virtuale "VALUE(v)", la tabella virtuale è creata elencando puntualmente i vaori ovvero i campi\colonne del "record corrente" con "VALUES (campo1), ..., (campo2)". 

SELECT [ProjectID]
 
	,(SELECT MIN(v) 
	   FROM (VALUES ( [L0 approved]), ([G0 approved]), ([G1 approved])
					,([G2 approved]), ([G5 approved]), ([G6 approved])
					,([M1 approved]), ([M2 approved]), ([M3 approved])
			) AS VALUE(v)) AS [MinDate]
	,(SELECT MAX(v) 
	   FROM (VALUES ( [L0 approved]), ([G0 approved]), ([G1 approved])
					,([G2 approved]), ([G5 approved]), ([G6 approved])
					,([M1 approved]), ([M2 approved]), ([M3 approved])
			) AS VALUE(v)) AS [MaxDate]
 
      ,[L0 approved]
      ,[G0 approved],[G1 approved],[G2 approved],[G5 approved],[G6 approved] 
      ,[M1 approved],[M2 approved],[M3 approved]   
FROM [SRC_ProjectsDE_PPM].[dbo].[Data$]

Secondo valore Massimo

SELECT 
   MAX(colonna)
FROM tabella
WHERE colonna < (SELECT 
                 MAX(colonna)
                 FROM tabella)

Operazione Modulo (resto della divisione)

L'operatore che restituisce il valore intero di una divisione è %. 

dividend % divisor

Esempio: 

DECLARE @Dividend	AS int = 0,
		@Divisor	AS int = 3,
		@i			AS int = 1,
		@n			AS int = 20
 
Print '@Dividendo % @Divisore = @Resto'
WHILE (@i < @n)
BEGIN
	SET @Dividend = @i
	Print CAST(@Dividend AS varchar) + ' % ' + CAST(@Divisor AS varchar) + ' = ' + CAST((@Dividend % @Divisor) AS varchar)
	/*
	SELECT @Dividendo AS Dividendo 
		,  @Divisore AS Divisore
		, (@Dividendo % @Divisore) AS Resto
		*/
	SET @i = @i + 1
END

Operazioni sul File System

Ricerche

Directory

Per cercare la presenza di una Directory sul FileSystem: 

DECLARE @PathRootToCheck	VARCHAR(255) = 'D:\Documenti\DOC Files\Vodafone\_Attivita',
		@FolderToCheck		VARCHAR(255) = 'Migrazione TTU Premium PIN ver 2.0'		
DECLARE @ResultSet	TABLE (Directory varchar(200))
 
INSERT INTO @ResultSet
    EXEC master.dbo.xp_subdirs @PathRootToCheck
 
IF (EXISTS (SELECT 1
	    FROM @ResultSet 
	    WHERE Directory = @FolderToCheck))
	Print 'Found: 
	' + @PathRootToCheck + '\' + @FolderToCheck
ELSE
	Print 'NOT Found: 
	' + @PathRootToCheck + '\' + @FolderToCheck

Files

DECLARE @PathToCheck	VARCHAR(512) = 'D:\Documenti\DOC Files\Vodafone\_Attivita\Migrazione TTU Premium PIN ver 2.0',
		@FileName		VARCHAR(256) = 'Segrate-dati_CLI_PIN.txt',
		@PathFull		VARCHAR(512),
		@ResultFExists	INT,
		@BlnFExixts		BIT
 
SET @PathFull = @PathToCheck + '\' + @FileName
EXEC master.dbo.xp_fileexist @PathFull, @ResultFExists OUTPUT
SET @BlnFExixts = CAST(@ResultFExists as bit)
 
IF @BlnFExixts = 1
	Print 'Exixts
	' + @PathFull
ELSE
	Print 'NOT FOUND
	' + @PathFull

OPPURE allo scopo si crea una funzione: 

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;

Ed usarla così: 

dbo.fn_FileExists(filepath)

Resituisce una variabile di tipo BIT per cui se il file esiste sarà 1 altrimenti 0.

Elenco dei files

Uso xp cmdshell

DECLARE @PathRootToCheck	VARCHAR(255) = '"D:\Tmp\Nome con spazi"', --Notare il delimitatore "
	@DOSCmd		        VARCHAR(255) = ''
 
DECLARE    @FileList TABLE 
                     (ID       int IDENTITY, 
                      FileName varchar(100));
SET @DOSCmd = 'dir ' + @PathRootToCheck + ' /b' --Parametro /b non fa vedere altri dettagli come il sommario etc
INSERT INTO @FileList
EXEC xp_cmd shell @DOSCmd

Uso xp_dirtree

DECLARE @PathRootToCheck	VARCHAR(255) = 'D:\Tmp\Nome con spazi' --Senza "
EXEC xp_dirtree @PathRootToCheck, 1, 1 --Il primo 1 indica il livello il secondo se directory o anche files

Eseguire una console application

Link interno: Esecuzione comandi DOS
NOTA per problemi con la Wiki ho dovuto troncare la stored procedure che esegue il comando DOS inserendo uno spazio nel nome (xp_c mdshell) perché altrimenti l'interprete della Wiki lo assume come comando pericoloso di SQL injection.
Per eseguire un comando DOS: 

-- Per avere l'elenco del contenuto della cartella corrente. 
-- Risultato in griglia, una riga per ogni riga di testo in output
xp_c mdshell 'Dir'

Per eseguire una applicazione console con dei parametri occorrerà invocare direttamente l'applicazione del prompt comandi CMD (Microsoft Help) con opportuni parametri /S /C ed usare il doppi apici. Segue esempio "tradotto" in forma elaborabile di quel che avremmo lanciato normalmente come: 

      D:\test\App C#\ExcelAgentCTRL.exe "vSBC_Traffic KPIs_W8-W9.xlsx"

diventa: 

      CMD /S /C " "D:\test\App C#\ExcelAgentCTRL.exe" "vSBC_Traffic KPIs_W8-W9.xlsx" "


Significato delle due opzioni usate:
/S modifica l'interpretazione delle doppie virgolette quindi delle stringhe dopo il comando /C
/C fa eseguire il comando seguente e poi esce dall'ambiente di esecuzione
Quindi: 

DECLARE @result			AS int,
	@ExeConsoleApp		AS varchar(256) = 'ExcelAgentCTRL.exe',
	@PathApp		AS varchar(256) = 'D:\test\App C#',
	@StrParams		AS varchar(100) = '',
	@cmd			AS sysname = '', 
	@var			AS sysname = '';
 
SET @StrParams = '"vSBC_Traffic KPIs_W8-W9.xlsx"'
SET @cmd = 'CMD /S /C " "' + @PathApp + '\' + @ExeConsoleApp + '"'
 
IF ISNULL(@StrParams, '') <> ''
BEGIN
    SET @cmd = @cmd + ' ' + @StrParams
END
 
SET @cmd = @cmd + ' "'
Print @cmd
 
EXEC @result = xp_c mdshell @cmd; 
IF (@result = 0)  
	Print 'Success'  
ELSE  
	Print 'Failure';
Print 'End'

Impostare Path virtuale

Attenzione per far memorizzare codesto script ho dovuto inserire uno spazio vuoto nel nome della SP di sistema xp_c mdshell (rimuoverlo!). 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*=============================================
Author:			Giuseppe AINO
Create date:	25/10/2021
Description:	Poiché per molte procedure DEVE essere attivo il path verso il Gateway Server Power BI
				con questa procedura si verifica che sia attivo il Drive virtuale H: e quindi il virtual path:
					\\69.69.69.69\d$\Documents pizza /USER:rut\udente /PERSISTENT:yes
				se non lo fosse si attiva previa cancellazione delle precedenti impostazioni.
=============================================*/
CREATE PROCEDURE CheckAndSet_GatewayVirtualPath
AS
BEGIN
	SET NOCOUNT ON;
 
	DECLARE @OS_User				AS varchar(100) = 'udente',
		@OS_UserPW				AS varchar(100) = 'pizza',
		@CmdShellDOS			AS varchar(2000) = '',
		@VirtualDriveRoot		AS nvarchar(max) = 'H:',
		@VirtualPath			AS nvarchar(max) = '\\69.69.69.69\d$\Documents §password§ /USER:rut\§userName§ /PERSISTENT:yes',
		@ShellRowOutput			AS nvarchar(max),
		@ShellReturnCode		AS int
 
	DECLARE @ShellDOSOutput TABLE (ShellRowOutput nvarchar(max));
 
	SET @VirtualPath = REPLACE(@VirtualPath, '§userName§', @OS_User)
	SET @VirtualPath = REPLACE(@VirtualPath, '§password§', @OS_UserPW)
 
	SET @CmdShellDOS = 'Dir ' + @VirtualDriveRoot
 
	INSERT INTO @ShellDOSOutput
	EXEC @ShellReturnCode = master..XP_C MDSHELL @CmdShellDOS
 
	--	@ShellReturnCode=0	=>	OK
	--	@ShellReturnCode=1	=>	ko
 
	IF @ShellReturnCode <> 0
	BEGIN
		Print '@ShellReturnCode=' + CAST(@ShellReturnCode AS varchar)
		SELECT TOP 1 
			@ShellRowOutput = ShellRowOutput
		FROM @ShellDOSOutput
		WHERE ShellRowOutput IS NOT NULL
		Print 'Errore ricevuto: "' + @ShellRowOutput + '"'
 
		IF CHARINDEX('cannot find the path', @ShellRowOutput, 0) > 0
		BEGIN
 
			Print 'Si ripristinerà il path: "' + @VirtualPath + '"'
 
			SET @CmdShellDOS = 'net use ' + @VirtualDriveRoot + ' /delete'
			Print '1/2. ' + @CmdShellDOS
			EXEC XP_C MDSHELL @CmdShellDOS
 
			SET @CmdShellDOS = 'net use ' + @VirtualDriveRoot + ' ' + @VirtualPath
			Print '2/2. ' + @CmdShellDOS
			EXEC XP_C MDSHELL @CmdShellDOS
		END
	END
	ELSE
		Print 'OK è ATTIVO il path: "' + @VirtualPath + '"'
 
END
GO

Operazioni comuni, standard

Esecuzione query

ID di un inserimento

Normale

Dopo un inserimento come ottenere l'ID identificativo del record appena inserito. Ci sono diversi metodi uno è quello dell'ottenimentod dell' identity selezionando una variabile del tipo @@ la @@IDENTITY: 

INSERT INTO ApiRequestLogs 
    (ApiKey, 
    Username, 
    IPAddress, 
    [Action], 
    QueryString, 
    Report, 
    AuthResult, 
    ExecResult,
    LogDateUTC,
    Duration)
VALUES 
    (@apiKey, 
    @username, 
    @ipAddress, 
    @action, 
    @queryString, 
    @report, 
    @authResult, 
    @execResult,
    GETUTCDATE(),
    @duration);
SELECT @@IDENTITY;

In LinkedServer

Da StackOverflow 

DECLARE @UltimoID TABLE (ID int);
 
INSERT INTO @UltimoID
EXEC server.master..sp_executesql N'
  INSERT INTO database.schema.table (columns) VALUES (values);
  SELECT SCOPE_IDENTITY()';
 
SELECT * 
FROM @UltimoID;

Usando OPENQUERY 

SELECT *
FROM OPENQUERY(server, '
                        INSERT INTO database.schema.table (columns) VALUES (values);
                        SELECT SCOPE_IDENTITY() AS ID');

Ultimo ID di usato in una tabella

SELECT IDENT_CURRENT('NomeTabellaConID_Chiave')

Nr di records affetti

Dopo l'ultima istruzione\operazione per ottenere il numero di recors affetti si può usare la variabile @@ROWCOUNT (rows affected). Essa restituisce un intero, int. 

UPDATE miaTabella   
   SET mioCampo = N'Polpetta'
WHERE ID = 696969
IF @@ROWCOUNT = 0
   Print 'ATTENZIONE nessun aggiornamento effettuato';

Esecuzione dinamica

Link interno di approfondimento: Script Utili
Si usa la StoredProcedure di sistema SP_EXECUTESQL, nel caso in cui si debba costruire una query usando parametri variabili
Esempio semplice: 

DECLARE @SQLCommand		AS NVARCHAR(MAX),
	@TableName		AS NVARCHAR(50) = '[CLI_PIN]'
 
SET @SQLCommand = 'SELECT * 
FROM [MVNOTeleTu].dbo.' + @TableName
 
EXECUTE sp_executesql @SQLCommand

Passando parametri: 

DECLARE @SqlQuery	NVARCHAR(MAX),
	@NomeTabella	varchar(100) = '[MiaTabella]'
 
SET @SqlQuery = 'SELECT TOP (1) @Id = ([ID]) 
				 FROM ' + @NomeTabella + '
				 WHERE [ModulId] = @IdModul';
 
EXEC SP_EXECUTESQL @SqlQuery, N'@Id INT OUTPUT, @IdModul INT',
			  @IdModul = @IdModul, @Id = @Id OUTPUT

Print - Messages

Il seguente esempio scrive testo e numeri ed infine ha un ritorno a capo col testo da stampare accapo: 

   Declare @Nr AS Int = 123
   Print 'Testo: ' + CAST(ISNULL(@Nr, -1) AS VARCHAR(10)) + ' Records.' 
         + CHAR(10) + CHAR(13) + 'Nuova linea accapo'

Forzare i Print

Può capitare che in operazioni lunge, in loop anche su cursori i compandi di Print non vengano stampati, col segeunte comando si genera un "errore" che scatenerà il rilascio della cache con tutti i Print eventualmente sospesi. 

--Per visualizzare i precedenti Print eventualmente sospesi
RAISERROR( '...',0,1) WITH NOWAIT

Per saperne di più: sommarskog.se

Disabilitare i Print (conteggio esecuzione)

La seguente istruzione evita il conteggio e l'output del numero di righe affette dopo una esecuzione di una query, si può disabilitare ma dopo riabilitare. Tipica necessità nei loop di singoli inserimenti dove diventa fuorviante avere in output nei messaggi una serie di (1 row affected) 

SET NOCOUNT ON;   --Disabilita l'output ed il conteggio
--Loop con ad es. inserimenti
SET NOCOUNT OFF;   --Riabilita l'output del messaggio ed il conteggio

Inserire un ritardo tra 2 istruzioni

Ecco come inserire un delay o sleep time tra due struzioni ovvero mediante "WAITFOR DELAY": 

--Esempio inserendo 5 seconti
SELECT GETDATE() OraAttuale
WAITFOR DELAY '00:00:05' -- Ritardo di 5"
SELECT GETDATE() OraAttuale

La sintassi (doc) è: 

WAITFOR   
{  
    DELAY 'time_to_pass'   
  | TIME 'time_to_execute'   
  | [ ( receive_statement ) | ( get_conversation_group_statement ) ]   
    [ , TIMEOUT timeout ]  
}

Si intuisce che può essere usata anche come un orologio per continuare l'esecuzione ad un orario prefissato adottando TIME al posto di DELAY

ESECUZIONE parametrizzata: 

DECLARE @FormattedDelayValue	AS varchar(10) = '00:00:03'
 
SELECT GETDATE() CurrentTime;
 
WAITFOR DELAY @FormattedDelayValue
 
SELECT GETDATE() CurrentTime;

Creazione condizionale di una tabella, copia

Metodo canonico

Prima dell'inserimento\modifica in una tabella può esser necessario la verifica che essa esista realmente per evitare errori. Nel seguente si interroga una tabella indice di sistema (INFORMATION_SCHEMA.TABLE) ed eventualmente si crea la tabella incriminata. 

DECLARE @TABLE_NAME	AS	VARCHAR(50)  = 'PO_B_ORDER_TYPE_EXT_NZL'
IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = @TABLE_NAME))
	BEGIN
        Print 'Table ' + @TABLE_NAME + ' already created'
	END
ELSE
	BEGIN
        Print 'Table ' + @TABLE_NAME + ' will be created'	
-------------------------------------------------------------------------
	CREATE TABLE [dbo].[PO_B_ORDER_TYPE_EXT_NZL](
		[COMPANY_CODE] [nvarchar](3) NOT NULL,
		[DIVISION_CODE] [nvarchar](3) NOT NULL,
		[ORDER_TYPE_CODE] [nvarchar](3) NOT NULL,
		[ORDER_TYPE_DESCR] [nvarchar](255) NULL,
		[DT_START] [date] NULL,
		[DT_END] [date] NULL,
		[DT_INSERT] [datetime] NOT NULL,
		[USERINSERT] [nvarchar](50) NOT NULL,
		[DT_UPDATE] [datetime] NOT NULL,
		[USERUPDATE] [nvarchar](50) NOT NULL,
		[ROWGUID] [uniqueidentifier] NOT NULL,
	PRIMARY KEY CLUSTERED 
	(
		[COMPANY_CODE] ASC,
		[ORDER_TYPE_CODE] ASC,
		[DIVISION_CODE] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
-------------------------------------------------------------------------
	END

Metodo dinamico, copia in nuova tabella

La seguente istruzione sintetica può esser usata anche per copiare da una tabella in un'altra al volo.
Verifica l'esistenza della tabella 'Contacts1' e la crea usando una INSERT SELECT la cui condizione WHERE 1 = 2 impedisce il riversamento dei dati ma consente la creazione della struttura: 

USE AdventureWorks2008;
 
IF OBJECT_ID ('Contacts1', 'U') IS NOT NULL
DROP TABLE dbo.Contacts1;
 
SELECT *
INTO dbo.Contacts1
FROM AdventureWorks2008.Person.Person
WHERE 1 = 2;

ResultSet temporanei di query, Nolock vs ReadPast

Articolo: da techrepublic

Verifica esistenza tabelle

Tabella normale

Come verificare se esiste una tabella e di conseguenza crearla o meno procedendo. ( IF EXISTS )
IF (EXISTS (SELECT 1  -- 1 sta per il primo campo della tabella, modo efficiente ed effice
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_SCHEMA = 'dbo'          -- DB schema
                 AND  TABLE_NAME = 'MiaTabella'))--Tabella da verificare esistenza
BEGIN
    --Fai cose
END

Oppure: 

DECLARE @TableName As varchar(max) = 'MiaTabellaDaCercare'
 
IF EXISTS (SELECT * FROM sys.tables 
	   WHERE [name] = @TableName )
BEGIN
     SELECT 1
END
ELSE
BEGIN
     SELECT 0
END

Ma la ricerca vien fatta SOLO sul DB correntemente attivo altrimenti bisognerà far precedere la verifica da un USE mioDatabase;

Tabella temporanea #

CREATE TABLE #TableList (
				Id_Progr		INT IDENTITY
				)
---------------------
IF (OBJECT_ID('tempdb..#TableList') IS NOT NULL)
BEGIN
	DROP TABLE #TableList
	Print 'Table dropped.'
END
ELSE
	Print 'OK Table doesn''t exists.'

Chiamare una storedProcedure all'interno di un'altra

Usando semplicemente EXEC storedProcedureDaChiamare: 

CREATE PROCEDURE SP_Principale
	@id_in INT
AS
BEGIN
    DECLARE @id_out INT
    EXEC SP_Chiamata @id_in, @id_out OUTPUT
    SELECT @hid_out
END
GO
------------------------------
CREATE PROCEDURE SP_Chiamata
	@id INT, 
	@id_out INT OUTPUT
AS 
BEGIN
    IF @id = 1
        SET @id_out = 100
    ELSE
        SET @id_out = 200
END
GO

Esecuzione a tempo

Ecco come attendere del tempo prima di eseguire l'operazione\istruzione successiva (una specie di istruzione C# "Sleep").
Segue temporizzazione in un loop, utile nel momento in cui occorre attendere che si verifichi una condizione. 

DECLARE @DGT_TestDate		DateTime,
		@DGT_Message	varchar(255),
		@i		int = 0,
		@NrCall		int = 5
WHILE @i < 10
BEGIN
	SET @DGT_TestDate = GETDATE()
	Print CAST(@i AS varchar) + ' iterazione'
 
	SET @DGT_Message = CONVERT(varchar(12), GETDATE(), 8) + ': ' 
					+ CONVERT(varchar(11), @NrCall) 
					+ ' calls in progress, pausing...'
	RAISERROR (@DGT_Message,0,1) WITH NOWAIT
 
	WAITFOR DELAY '00:00:10' --10 secondi
 
	SET @i = @i + 1;
END

Risultato parziale nel TAB "Messages" 

0 iterazione
14:54:28: 5 calls in progress, pausing...
1 iterazione
14:54:38: 5 calls in progress, pausing...
2 iterazione
14:54:48: 5 calls in progress, pausing...
3 iterazione
14:54:58: 5 calls in progress, pausing...
4 iterazione
14:55:08: 5 calls in progress, pausing...
5 iterazione
14:55:18: 5 calls in progress, pausing...
6 iterazione
14:55:28: 5 calls in progress, pausing...
7 iterazione
14:55:38: 5 calls in progress, pausing...
8 iterazione
14:55:48: 5 calls in progress, pausing...
9 iterazione
14:55:58: 5 calls in progress, pausing...

Completion time: 2020-10-01T14:56:08.2235069+02:00

Gestione dell'Errore

Link interno Blocco TRY CATCH

RAISERROR e THROW

RaisError è stata introdotta da SQL Server 7 mentre la Throw a partire da SQL Server 12, Microsoft suggerisce di usare Throw (Fonte sqlhints.com) al posto della obsoleta raiserror
RAISERROR, es. uso semplificato NON BLOCCA L'ESECUZIONE! 

DECLARE @ROOM_CODE			AS nvarchar(10),
		@ErrorMsg			AS VARCHAR(MAX) = '',
		@ExtraErrorMsg		AS VARCHAR(MAX) = ''
 
IF @ROOM_CODE IS NULL
BEGIN	
	SET @ErrorMsg = 'Room code not available.' + @ExtraErrorMsg
	RAISERROR(@ErrorMsg, 
				16, --Severity	16
				1)	--State		1
	Print 'Genererà un errore che verrà intercettato dalla applicazione'
END
 
Print 'Codice eseguito comunque !!!'

RAISERROR, es. con uscita in caso di errore (si usa l'istruzione GOTO): 

DECLARE @ROOM_CODE			AS nvarchar(10),
		@ErrorMsg			AS VARCHAR(MAX) = '',
		@ExtraErrorMsg		AS VARCHAR(MAX) = ''
 
IF @ROOM_CODE IS NULL
BEGIN	
	SET @ErrorMsg = 'Room code not available.' + @ExtraErrorMsg
	RAISERROR(@ErrorMsg, 
				16, --Severity	16
				1)	--State		1
	GOTO sp_end
END
 
Print 'Esecuzione senza errori'
 
sp_end:
Print 'Fine'

THROW

  • Il primo parametro error_number è int e deve essere maggiore o uguale a 50000 e minore o uguale a 2147483647.
  • Il secondo è un tinyint compreso tra 0 e 255, indicato lo stato da associare al messaggio.
  • L'istruzione che precede l'istruzione THROW deve essere seguita dal carattere di terminazione punto e virgola (;) 
THROW 51000, 'The record does not exist.', 1;

Multiple CTE

CTE multipli, come da sql authority 

WITH CTE1 AS (SELECT 1 AS Col1),
     CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1)
 
SELECT 
    CTE1.Col1,CTE2.Col2
FROM       CTE1
CROSS JOIN CTE2
GO

Generare tabella con date in un intervallo

Il seguente esempio genera una tabella un calendario con un set di date progressive che va da una data ad un altra. 

DECLARE @StartDate		AS Date = '2022-08-08',
	@EndDate		AS Date = GetDate()
 
;WITH cte AS (SELECT 
			@StartDate AS myDate
		UNION ALL
	SELECT 
			DATEADD(day,1,myDate)
	FROM cte
	WHERE DATEADD(day,1,myDate) <=  @EndDate
)
------------- Verifica delrisultato:
SELECT myDate
FROM cte
OPTION (MAXRECURSION 0)

Consigli

Performance

  • Quando si usa un operatore LIKE conviene usare il Jolly a destra della stringa di testo da ricercare poiché così nonostante il Like si potrà sfruttare la presenza di eventuali Indici. es.: 
SELECT * 
FROM Tabella
WHERE CampoConIndice LIKE 'radice%'

Nozioni varie

Pagina interna: MSSQL:Nozioni varie

Variabili d'ambiente

Nome del HOST

--Nome della macchina che ospita il Server
SELECT host()

Nome del DB-SP corrente

Il seguente restituisce una stringa con il nome del DB corrente o StoredProcedure corrente, utile nelle query dinamiche costruite. 

--DB corrente
SELECT db_name()
--StoredProcedure corrente
SELECT OBJECT_NAME(@@PROCID)

Restart del servizio

Link diretto: Come Riavviare il servizio

Versione di SQL Server

Senza andare fisicamente sulla macchina del server centrale, per la versione del motore del DB si può fare la seguente query dall'IDE di SSMS (Sql Server Management Studio): 

SELECT @@version
 
/* Es di Output:
 
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)   Oct 20 2015 15:36:27   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (Intel X86)   Jul  3 2017 02:37:05   Copyright (c) Microsoft Corporation  Developer Edition on Windows NT 6.1 <X64> (Build 7601: ) (WOW64) 
Microsoft SQL Server 2017 (RTM-GDR) (KB4494351) - 14.0.2014.14 (X64)   Apr  5 2019 09:18:51   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17134: ) (Hypervisor) 
*/
 
SELECT SERVERPROPERTY('Edition')
 
/* Output:
Developer Edition (64-bit)
*/
 
SELECT SERVERPROPERTY('LicenseType')
     , SERVERPROPERTY('NumLicenses')
     , SERVERPROPERTY('productversion')
     , SERVERPROPERTY ('productlevel') 
     , SERVERPROPERTY ('edition')

Abilitare command Shell

Per il primo punto si può lanciare il seguente script: 

sp_configure 'show advanced options'
GO
/* 0 = Disabled , 1 = Enabled */
EXEC sp_configure 'show advanced option', '1'; 
 
RECONFIGURE WITH OVERRIDE
GO
 
-- Correggere sottovedi commento sotto (*) !!!!
sp_configure 'xp_cmds hell', 1
 
GO
RECONFIGURE WITH OVERRIDE
GO

(*) Per un errore nella pagina son stato costretto ad inserire uno SPAZIO non necessario tra xp_cmds e hell

Diritti da associare a SQLSERVERAGENT

Per una buona guida: stackexchange o come segue

Per assegnare i diritti necessari all'utente con cui SQL Server eseguirà il Job, visto che l'eseguibile è su una cartella del File System, occorrerà assegnargli i diritti di "esecuzione" ed eventualmente anche "scrittura".

L'utente in questione si chiama SQLSERVERAGENT (più precisamente: "NT Service\SQLSERVERAGENT"), sostituire il campo "From" col nome del PC SQL Server.

Permessi al fileSytem al SQLServerAgent.png

Conviene anche dare i diritti di esecuzione e modifica anche all'utente con cui è eseguito l'SQLSERVERAGENT, notare l'utente 'd09\s_sqlsrvagent' sull'ultima colonna.

Add grant SQLServerAgent 01.png

Estendere il server con CLR Assembly

(come da c-sharpcorner.com) Possiamo importare .NET DLL o gruppi di DLLs in SQL Server. Dopo che una DLL è stata importata si potrà accedere ai metodi della DLL da Stored Procedure, User-Defined function, o attraverso TSQL. Questa pratica è chiamata CLR Assembly.
E' possibile estendere funzioni e funzionalità del Server implementandole in DLL e exe, eseguibili, esterni al Server questo è possibile farlo mediante un comando
'CREATE ASSEMBLY'
che agisce sul DB master del server.

Esempio Estensione per codifica SHA-2

(SHA2) Prima di procedere è necessario render noto che con SQL Server 2017 si è introdotta una configurazione a livello di server chiamata "CLR strict security" che è abilitata per default. Questa opzione impone che tutti gli assembly devono essere accompagnati da un certificato (o chiave asimmetrica).
Quanto segue è un workaround che disabilita questa funzionalità. (Qui c'è un articolo che spiega eventualmente come essere conformi alla sicurezza github.com) 

Use master
EXEC sp_configure 'show advanced options', 1 
RECONFIGURE; 
EXEC sp_configure 'clr strict security', 0; 
RECONFIGURE;

Il seguente è un esempio realistico per consentire la codifica Hash ([1]). Si usa una libreria, la "TextFunctions.DLL" (pe l'SHA-2 SHA-2), questa a sua volta si appoggia alla DLL "SimMetrics.dll" quindi devono fisicamente esser presenti entrambe nella cartella del Server.
Creare il "link" alla libreria: 

Use master
CREATE ASSEMBLY TextFunctions  
FROM 'D:\Librerie\SHA_2\TextFunctions.dll'  
WITH PERMISSION_SET = SAFE;

Per verificare che che l'assembly sia stato collegato correttamente, lanciare la seguente query: 

SELECT 
  * 
FROM sys.assembly_files

Si ottiene un output così simile 

assembly_id name                                                               
----------- -------------------------------------------------------------------
1           microsoft.sqlserver.types.dll                                      
65536       D:\Documenti\Vodafone\Digitalk\DB_DLL_Extension\TextFunctions.dll  
65537       D:\Documenti\Vodafone\Digitalk\DB_DLL_Extension\simmetrics.dll     

(3 row(s) affected)

Creazione della StoredProcedure che serve a richiamare la funzione SHA-2 

USE [master]
GO
 
CREATE PROCEDURE [dbo].[xp_hash_sha]
@text [varbinary](8000),
@hash [varbinary](64) OUTPUT,
@bits [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [TextFunctions].[Libreria].[GetSHA2Hash] --è il namespace delle funzioni nella DLL
GO

NOTA
In Microsoft SQL Server la funzione di integrazione CLR (Common Language Runtime) è disattivata per default e deve essere abilitata in modo da usare oggetti implementati.
Per abilitare l'integrazione CLR, usa l'opzione clr enabled della stored procedure: 

sp_configure 
 
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

StoredProcedure che userà la libreria: 

CREATE  PROCEDURE [dbo].[xsp_hash_sha]
 
	@plain_text		VARCHAR(4000),
	@bits			INT,
	@hashed_text	VARCHAR(64) OUT
 
AS
BEGIN
	SET NOCOUNT ON
	SET ROWCOUNT 0
 
	DECLARE @plain_text_binary	VARBINARY(8000)
	DECLARE @hashed_binary		VARBINARY(64)
 
	SET @hashed_text = NULL
 
	-- Check inputs
	SET @bits		= COALESCE(@bits, 0)
 
	IF @bits NOT IN (256, 384, 512)
	BEGIN
		RAISERROR('@bits must be 256, 384 or 512', 11, 1)
		GOTO exit_point
	END
 
	IF @plain_text IS NULL
	BEGIN
		GOTO exit_point
	END
 
 
	-- Convert the plaintext to binary and hash it
	SET @plain_text_binary = CONVERT(VARBINARY(8000), @plain_text)
	EXEC master.dbo.xp_hash_sha @plain_text_binary, @hashed_binary OUTPUT, @bits
 
	-- Convert hashed binary to text and strip hex prefix
	IF @hashed_binary IS NOT NULL
	BEGIN
		DECLARE @temp_hashed_text VARCHAR(66)
 
		SET @temp_hashed_text = UPPER(master.dbo.fn_varbintohexstr(@hashed_binary))
 
		IF LEN(@temp_hashed_text) = 66 AND LEFT(@temp_hashed_text, 2) = '0x'
		BEGIN
			SET @temp_hashed_text = RIGHT(@temp_hashed_text, 64)
		END
 
		SET @hashed_text = @temp_hashed_text
	END
 
exit_point:
 
END

Per finire, ecco un esempio d'uso: 

DECLARE	@return_value int,
		@hashed_text varchar(64)
 
EXEC	@return_value = [dbo].[xsp_hash_sha]
		@plain_text = N'testo prova',
		@bits = 256,
		@hashed_text = @hashed_text OUTPUT
 
SELECT	@hashed_text as N'@hashed_text'

Output: 

@hashed_text
----------------------------------------------------------------
3000850D07813C60A20B5F4CB1C1ABBC55D6ADBEBC45D337492697F8F8A67E93

(1 row(s) affected)

Chiavi asimmetriche

Le asymmetric key sono ad esempio usate nella creazione dei CLR Assembly (link interno C# Assembly).
Per l'elenco di quelle disponibili installate eseguire la query: 

SELECT AK.name,
	AK.principal_id,
	AK.asymmetric_key_id,
	AK.pvt_key_encryption_type,
	AK.pvt_key_encryption_type_desc,
	AK.thumbprint,
	AK.algorithm,
	AK.algorithm_desc,
	AK.key_length,
	AK.sid,
	AK.string_sid,
	AK.public_key,
	AK.attested_by,
	AK.provider_type,
	AK.cryptographic_provider_guid,
	AK.cryptographic_provider_algid
FROM sys.asymmetric_keys AS AK;

Per cancellarne una: 

DROP ASYMMETRIC KEY CLRAssembly4SQLServer_Test1

Per crearla: 

CREATE ASYMMETRIC KEY CLRAssembly4SQLServer_Test1_Key
FROM EXECUTABLE FILE = 'D:\Tmp\CLRAssembly_Test\CLRAssembly4SQLServer_Test1.dll';

Jobs

Link interno: Nozioni_varie

Import and Export

Bulk data

Verifiche

Verifica esecuzioni

USE msdb ;  
GO  
 
EXEC dbo.sp_help_jobhistory   
    @job_name = N'FoxNewZealand.AutomaticBankDepositAndCashClosure_SIT' ;  
GO

Verifica dei Log History

SQL Server Jobs history log1.png

Soluzione problemi

Collation

La relazione tra due tabelle non funzione per il seguente errore:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. 

SELECT C.*
FROM		[MVNOTeleTu_Migrated_PremNr].[dbo].[T_D_CLI_PIN]	AS C
INNER JOIN	[VFT2Premiumsrv].[dbo].[CLI_PIN]			AS P ON C.CLI = P.CLI collate SQL_Latin1_General_CP1_CI_AS

Notare l'istruzione collate nella definizione della relazione

Chiudere le connessioni pendenti

Nel caso si stiano facendo attività, come il restore del DB, impedite dal fatto che ci sono connessioni attive, ecco come disconnettere le connessioni che risultano attive: 

USE master;
 
ALTER DATABASE YourDatabase 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
 
ALTER DATABASE YourDatabase 
SET MULTI_USER;

O più semplicemente: 

DECLARE @SQLQuery	AS NVARCHAR(max),
	@DBName		AS NVARCHAR(255) = '[RS_SRC_Avaya_AACC]'
 
SET @SQLQuery = '
USE master; 
ALTER DATABASE ' + @DBName + '
SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE  ' + @DBName + '
SET MULTI_USER;'
 
EXEC (@SQLQuery)

Permessi speciali

Operazioni sul File System

Per lavorare sul FileSystem si userà la 'xp_cmdshell': 

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' 
because this component is turned off as part of the security configuration for this server. 
A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. 
For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

ATTENZIONE al parametro della stored procedure 'xp_cmdshell', NON deve essere un varchar(max) ma varchar(2000) in quanto ha un baco e non riuscirebbe la conversione nel tipo atteso come parametro dalla SP.
Ecco come sbloccarlo, avviare management Studio come amministratore ed eseguire quanto segue, attenzione alla conseguenze di sicurezza e valutare soluzioni alternative: 

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Qui stackoverflow

Copia di un campo Timestamp

Ad es. copiando i dati da una tabella con campo timestamp ad un'altra, questo non è consentito.
Si può eludere il problema cambiando il tipo del campo che acccoglierà i valori timestamp, è possibile definirlo: varbinary(8)

Blocchi

Se per motivi imprecisati si creano dei lock su oggetti del database per cui risultino non più accessibili.

Sbloccare una tabella

La seguente elenca i processi\sessioni che interessano una tabella affetta da lentezza o blocco, (da StackOverflow): 

SELECT
    OBJECT_NAME(P.object_id) AS TableName,
    Resource_type,
    request_session_id
FROM    sys.dm_tran_locks L
JOIN    sys.partitions P ON L.resource_associated_entity_id = p.hobt_id
WHERE   OBJECT_NAME(P.object_id) = 'Mia_tabella_Ko'

Ottenuto l'ID del processo con più record, campo [request_session_id], si esegue la seguente query: 

--Supopsto l'ID del processo incriminato sia 60:
Kill 60

Mappa e Link

MS SQL | TSQL Linguaggio | Funzioni base | Script Utili | Script Utili2

C Sharp | Tips programmazione

Parole chiave: Index, ottimizzazione, ottimizzazioni, PAD

Retrieved from "http://wiki.aino.it/index.php?title=MSSQL:Tips&oldid=8658"
Author
Giuseppe AINO