Scripts

Recentemente, ho avuto l’occasione di realizzare un’estrazione dati in cui era richiesto di dividere le righe di una colonna di tipo VARCHAR per ottenere due colonne ordinate tra loro, una specie di partizione dinamica ordinata.

Scenario

Disponiamo di un database SQL Server in cui sono presenti le tabelle anagrafiche dbo.Magazzini e dbo.Vani utilizzate rispettivamente per memorizzare l’anagrafica dei magazzini aziendali ed la relativa suddivisione in vani.

Le tabelle dbo.Magazzini e dbo.Vani hanno la seguente struttura:

Può capitare, per diversi motivi, di avere la necessità di svuotare un database per riportarlo allo stato “iniziale”.

Per stato “iniziale” intendo il momento dell’installazione: nessuna riga (se non in eventuali tabelle che devono essere già popolate) e con i contatori delle colonne autoincrementanti a zero.

---

/*
Meccanismo di delete automatico
*/
set nocount on
print ‘*** delete’

declare @tName nvarchar(100)
declare @tSQL nvarchar(2000)
declare @nrRows bigint
declare @rowCount bigint

Abbiamo trattato il problema della frammentazione degli indici nei post Index Fragmentation e Index Fragmentation (Parte 2) definendo la stored procedure USP_ExecReorgRebuildIndex() in grado di eseguire la deframmentazione degli indici con avg_fragmentation_in_percent maggiore del 10% in un database SQL Server 2005.

La DMV sys.dm_db_index_physical_stats non è presente nella versione 2000 di SQL Server (di cui esistono numerose installazioni attive in produzione) da qui la non compatibilità della stored procedure USP_ExecReorgRebuildIndex() su SQL Server 2000.

La funzione PIVOT di SQL Server 2005 non permette di utilizzare una variabile per la definizione delle colonne da creare come risultato dell’operazione di pivoting.

E’ possibile superare questo limite utilizzando del codice SQL dinamico, creando al volo la query da eseguire ed mandandola in esecuzione con il comando sp_executesql.

use tempdb
go

set nocount on
go

/*

 Setup scenario di esempio

*/

if (object_id(‘dbo.tabColonne’) is not null) drop table dbo.[tabColonne];
if (object_id(‘dbo.tab1’) is not null) drop table dbo.[tab1];
go

La stored procedure USP_IMPORT_XML_INTO_SQL() definita di seguito permette l’importazione di dati da file XML a tabella SQL Server.

La sorgente dati è rappresentata da un file XML strutturato come il file di esempio TestTab.xml illustrato in figura 1.

Figura 1

La destinazione su cui importare i dati è rappresentata da una tabella SQL Server il cui nome deve essere specificato nel file XML dal tag DATA IDENTIFIER. Nell’esempio, la tabella di destinazione è TESTTAB <DATA IDENTIFIER="TESTTAB">.

Lo standard di codifica EAN/UCC richiede che ogni codice generato termini con una cifra compresa tra zero e nove chiamata cifra di controllo (check digit).

La seguente Scalar-valued Function UDF_GetCheckDigitUCCEAN() implementa l’algoritmo di calcolo della cifra di controllo e può essere utilizzata per i seguenti tipi:

• EAN/UCC-8
• EAN/UCC-12
• EAN/UCC-13
• EAN/UCC-14
• SSCC (EAN/UCC-128) 

-- Drop Scalar-valued Function DBO.UDF_GetCheckDigitUCCEAN
IF OBJECT_ID(‘UDF_GetCheckDigitUCCEAN’, ‘FN’) IS NOT NULL
  DROP FUNCTION DBO.UDF_GetCheckDigitUCCEAN
GO

-- Create Scalar-valued Function dbo.UDF_GetCheckDigitUCCEAN
CREATE FUNCTION dbo.UDF_GetCheckDigitUCCEAN
(@Codice AS VARCHAR(18))
RETURNS SMALLINT
AS BEGIN
  /*
    Algoritmo per il calcolo della cifra di controllo:

La stored procedure CS_SP_DMOExportToExcel rappresenta un esempio di utilizzo degli oggetti SQLDMO.SQLServer per esportare il risultato di una query su foglio Excel specificando il worksheet su cui trasferire i dati. Il worksheet specificato dal parametro @WorksheetIndex verrà “pulito” e ri-assegnato con i dati della query. Il file XLS esistente non verrà sovrascritto.

/* Stored Procedure CS_SP_DMOExportToExcel per esportazione dati su Excel */

IF (OBJECT_ID(‘CS_SP_DMOExportToExcel’, ‘P’) IS NOT NULL)

 DROP PROCEDURE DBO.[CS_SP_DMOExportToExcel]

Questo  piccolo script (per Sql2005) riporta, per ciascun database, le dimensione dei dati, del file di log, la percentuale di utilizzo del log ed il modello di recovery attivo.

select
  rtrim (pc1.instance_name) as database_name,
  pc3.cntr_value/1024.0 as database_size_mb,
  pc1.cntr_value/1024.0 as log_size_mb,
  cast (pc2.cntr_value*100.0/pc1.cntr_value as dec (5,2)) as log_space_used_perc,
  db.recovery_model_desc
from
  sys.databases as db
inner join
  sys.dm_os_performance_counters as pc1 on pc1.instance_name = db.name
inner join
  sys.dm_os_performance_counters as pc2 on pc1.instance_name = pc2.instance_name
inner join
  sys.dm_os_performance_counters as pc3 on pc1.instance_name = pc3.instance_name
where
  pc1.counter_name = ‘Log file(s) Size (KB)’
and
  pc2.counter_name = ‘Log file(s) Used Size (KB)’
and
  pc3.counter_name = ‘Data File(s) Size (KB)’
and
  pc1.cntr_value > 0
order by
  pc1.instance_name

Questo script permette di vedere quanta memoria è occupata dai vari oggetti di SQL Server:

select
    [name],
    used_memory_mb = sum(single_pages_kb + multi_pages_kb)/1024.0
from
    sys.dm_os_memory_clerks
group by
    [name]
order by
    2 desc

La memoria occupata dai piani di esecuzione per query ad-hoc è rappresentata dalla riga “SQL Plans”, mentre quella utilizzata per Stored Procedure e Trigger è indentificata dalla riga “Object Plans”

Nella definizione di una strategia di disaster recovery uno dei principali fattori da tenere in considerazione è avere ben chiare le modalità di ripristino dei dati ed avere coscienza non solo della corretta sequenza di ripristino ma anche dei tempi attesi è un aspetto di primaria importanza. Il più delle volte si perde del tempo prezioso nel ricercare, all’interno di un device di backup, la posizione dei vari backup completi, differenziali e del t-log al fine di ricostruire la corretta sequenza di ripristino. Personalmente mi sono costruito uno script che potete trovare qui: