Login Login
MORE

WIDGETS

Widgets

Wanted articles
Who is online?
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-2016', 105) -- dd-mm-yyyy
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
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)

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)

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.

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 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--

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
Usando CAST
SELECT CAST(GETDATE() AS varchar(20));

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

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 ...;

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
)

Manca una INSERT SELECT di popolamento

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

Ritardare l'esecuzione

Ecco come inserire una pausa (sleep) prima di riprendere l'esecuzione:

--Imposta una attesa di 5 minuti:
waitfor delay '00:05:00'

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 di una cartella:

DECLARE @PathRootToCheck	VARCHAR(255) = 'D:\Tmp',
	@DOSCmd		        VARCHAR(255) = '',
        @file TABLE (ID int IDENTITY, FileName varchar(100))

Operazioni comuni, standard

Ottenere l'ID di un inserimento

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;

Ottenere l'ultimo ID di usato in una tabella

SELECT IDENT_CURRENT('NomeTabellaConID_Chiave')

Ottenere il 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';

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;

Resulset 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

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.'

Esecuzione dinamica di una query

Link interno di approfondimento: Script Utili
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 @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
 
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Log, Print

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

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

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

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

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)
*/

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 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

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)

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)

Mappa e Link


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


C Sharp | Tips programmazione

Parole chiave: Index, ottimizzazione, ottimizzazioni, PAD

Author