Vedremo in questo articolo qualche query di esempio: query per il confronto tra date, query che coinvolgono colonne con contenuto XML, conversioni di GUID direttamente sul Database ...
Query Delete By Date
Vediamo come fare una delete per eliminare tutti i record antecedenti ad una certa data fissa.
Questa query elimina i record nella tabella "MyTable" del database "MyDB" in cui la data di creazione è precedente alla data specificata, '20190201 00:00:00.000' . La query utilizza il comando DELETE per eliminare i record che soddisfano la condizione specificata nella clausola WHERE. La data viene convertita in un formato di data/ora utilizzando CAST e confrontata con la colonna "CreationDate" della tabella. GO alla fine della query indica il termine della transazione e invia l'istruzione al server:
USE [MyDB]
GO
DELETE FROM [dbo].[MyTable]
WHERE CreationDate < CAST('20190201 00:00:00.000' AS DATETIME)
GO
Query Update Date
Vediamo come fare una query di update per settare tutte le date di una tabella ad un valore fisso.
Questa query modifica i record nella tabella "MyTable" del database "MyDB", in particolare la colonna "UpdateDate" viene impostata su una data specifica '20180101 00:00:00.000' utilizzando il comando UPDATE. La data viene convertita in un formato di data/ora utilizzando CAST e impostata sulla colonna "UpdateDate" di tutti i record presenti nella tabella. Non ci sono condizioni specificate nella clausola WHERE, quindi l'istruzione modificherà tutti i record nella tabella. GO alla fine della query indica il termine della transazione e invia l'istruzione al server:
USE [MyDB]
GO
UPDATE [dbo].[MyTable]
SET [UpdateDate] = CAST('20180101 00:00:00.000' AS DATETIME)
GO
Query On XML Column
Vediamo questa semplice query:
select *
from MyTable
where xmlColumn like '%<myNode>2</myNode>%'
Se provate ad eseguire questa query sicuramente avrete il seguente errore:
Argument data type xml is invalid for argument 1 of like function
Questo perché se nella colonna xmlColumn ci sta del testo in formato XML la precedente sintassi genera un errore.
L'unico modo per eseguire una query di questo tipo è il seguente:
select *
from MyTable
where CAST(xmlColumn AS nvarchar(max)) LIKE N'%<myNode>8</myNode>%'
Ovvero la colonna deve essere prima convertita in testo e poi si esegue la query.
Se ci sono problemi nell'estrazione di contenuti XML troppo grandi inserire il seguente codice alla fine della query:
for xml path
Ad esempio:
select xmlColumn
from MyTable
where CAST(xmlColumn AS nvarchar(max)) LIKE N'%<myNode>8</myNode>%'
for xml path
Anche così facendo si sta usando l'istruzione like quindi la query non risulta molto efficiente.
Una tecnica alternativa è la seguente:
select try_cast(MyField as XML).value('declare namespace PD="http://www.deltavista.com/dspone/ordercheck-if/V001";xpath_specifico', 'nvarchar(max)') as MyFieldDescr
from table
where try_cast(MyField as XML).exist('
declare namespace PD="http://www.deltavista.com/dspone/ordercheck-if/V001";xpath_specifico') = 1
In questo caso si usa un xpath specifico per selezionare il nodo.
La query che hai fornito viene utilizzata per selezionare un campo chiamato MyFieldDescr da una tabella in cui il campo chiamato MyField viene convertito in XML e quindi verificato se contiene un elemento con uno specifico namespace utilizzando il metodo exist(). Il namespace è definito come PD=http://www.deltavista.com/dspone/ordercheck-if/V001
La funzione try_cast() viene utilizzata per convertire un valore in XML. La funzione restituisce NULL se la conversione non riesce.
Conversione in GUID direttamente con SQL
Vediamo questa semplice istruzione per convertire un numero in GUID direttamente in SQL:
cast(CONVERT(BINARY(16), (CONVERT(BINARY(16), numeroDaConvertire))) as uniqueidentifier)
Questa query utilizza una combinazione di funzioni di conversione e di cast per convertire un numero in un valore uniqueidentifier.
La funzione CONVERT viene utilizzata per convertire il numero in un valore BINARY(16) e successivamente CONVERT viene nuovamente utilizzato per convertire nuovamente il valore BINARY(16) in un valore uniqueidentifier utilizzando la funzione CAST.
In generale, questa query converte il numero in un valore univoco in formato binario e quindi lo riconverte in un valore univoco in formato uniqueidentifier.
Nota: questa query potrebbe avere problemi di compatibilità o di performance, dipende dal contesto in cui è utilizzata.
Query per conoscere le colonne con Always Encrypted sul DB
La seguente query estrae l'elenco delle tabelle e delle colonne che hanno abilitato l'Always Encrypted:
select
t.name as [Table],
c.name as [Column],
c.encryption_type_desc
from
sys.all_columns c inner join
sys.tables t on c.object_id = t.object_id
where
c.encryption_type is not null
order by
t.name,
c.name
Conversione SQL to GUID
select cast(CONVERT(BINARY(16), (CONVERT(BINARY(16), myFieldInteger))) as myFieldIntegerConvertedGUID),
myFieldInteger
from MyTable
where id='xxx'
Query per trovare le connesioni attive
La query per avere il numero di connessioni totali (1):
Questa query seleziona il numero di connessioni a un database SQL Server, conteggiando le righe della colonna "dbid" nella tabella "sys.sysprocesses" dove "dbid" è maggiore di zero. Il risultato viene etichettato come "TotalConnections".
La query restituisce le connessioni disponibili (2):
Questa query fa le seguenti operazioni in SQL Server:
Dichiarazione di una variabile di tabella temporanea chiamata "@SPWHO1", che contiene tre colonne: "DBName", "NoOfAvailableConnections" e "LoginName".
Inserimento di dati nella tabella temporanea "@SPWHO1" utilizzando una query SELECT-GROUP BY che seleziona il nome del database (da "dbid"), il numero di connessioni disponibili (conteggiando le righe di "dbid") e il nome utente di accesso (da "loginame"). I dati vengono raggruppati per "dbid" e "loginame".
Selezione di tutte le righe nella tabella temporanea "@SPWHO1" dove la colonna "DBName" è uguale al valore passato come parametro @dbName.
In sintesi, questa query seleziona informazioni sulle connessioni a un database SQL Server, filtrandole per nome del database specifico (@dbName). Il risultato viene restituito in una tabella temporanea con informazioni sul nome del database, sul numero di connessioni disponibili e sul nome utente.
La query restituisce le connessioni in running (3):
Questa query fa le seguenti operazioni in SQL Server:
Dichiarazione di una variabile di tabella temporanea chiamata "@SPWHO2", che contiene dodici colonne: "SPID", "Status", "Login", "HostName", "BlkBy", "DBName", "Command", "CPUTime", "DiskIO", "LastBatch", "ProgramName" e "Request".
Inserimento di dati nella tabella temporanea "@SPWHO2" eseguendo la stored procedure di sistema "sp_who2" con l'argomento "Active". Questa stored procedure restituisce informazioni sulle sessioni attive in un istanza di SQL Server.
Selezione di tutte le righe nella tabella temporanea "@SPWHO2" dove la colonna "DBName" è uguale al valore passato come parametro @dbName.
In sintesi, questa query esegue la stored procedure "sp_who2" per selezionare informazioni sulle sessioni attive in un'istanza di SQL Server e filtra i risultati per il nome del database specifico (@dbName). Il risultato viene restituito in una tabella temporanea con informazioni sulle sessioni, tra cui lo stato, il nome utente di accesso, il nome dell'host, il nome del database, il comando in esecuzione, il tempo di CPU, l'I/O su disco e altro.
Declare @dbName varchar(150) set @dbName = 'MyDatabase'
--(1) Total machine connections
SELECT COUNT(dbid) as TotalConnections FROM sys.sysprocesses WHERE dbid > 0
--(2) Available connections
DECLARE @SPWHO1 TABLE (DBName VARCHAR(1000) NULL, NoOfAvailableConnections VARCHAR(1000) NULL, LoginName VARCHAR(1000) NULL)INSERT INTO @SPWHO1 SELECT db_name(dbid), count(dbid), loginame FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame
SELECT * FROM @SPWHO1 WHERE DBName = @dbName
--(3) Running connections
DECLARE @SPWHO2 TABLE (SPID VARCHAR(1000), [Status] VARCHAR(1000) NULL, [Login] VARCHAR(1000) NULL, HostName VARCHAR(1000) NULL, BlkBy VARCHAR(1000) NULL, DBName VARCHAR(1000) NULL, Command VARCHAR(1000) NULL, CPUTime VARCHAR(1000) NULL, DiskIO VARCHAR(1000) NULL, LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL, SPID2 VARCHAR(1000) NULL, Request VARCHAR(1000) NULL) INSERT INTO @SPWHO2 EXEC sp_who2 'Active' SELECT * FROM @SPWHO2 WHERE DBName = @dbName
Buon lavoro!