SQL Server & Delphi

[OT] Porretta Soul Festival 2009

sgovoni — Thu, 02 Jul 2009 21:47:00 GMT

Vi piace la musica Soul ma non potete andare fino a Memphis per ascoltare le grandi icone della black music ?

Vi segnalo la 22 nd edizione del Porretta Soul Festival che si svolgerà nella cittadina Bolognese dal 23 al 26 Luglio 2009. In particolare il ricavato del concerto di apertura, nella serata inaugurale, sarà devoluto a favore della Fondazione Hospice.

Qui trovate i dettagli della serata inaugurale.

User Auditing

sgovoni — Wed, 01 Jul 2009 21:56:00 GMT

Abbiamo parlato dei meccanismi di auditing in questo articolo Meccanismi di Auditing e nella sessione dedicata durante l’ultimo workshop UGISS a Torino. Riprendo l’argomento per rispondere ad una domanda che alcuni di voi mi hanno sottoposto durante l'evento.

"Come è possibile fare user auditing anche quando la gestione delle login (e degli utenti) risiede nell’applicazione e non nel database ?"

Il caso tipico è rappresentato da un’applicazione WEB o Win32 che gestisce in autonomia i primi due livelli di sicurezza (autenticazione e autorizzazione) per l’accesso ai dati di un DB SQL Server. Al momento della connessione tutti gli utenti applicativi (autenticati e autorizzati) si presentano al database con la medesima login (ad esempio “ERPLogin”). In questa situazione, il database non conosce la *vera* identità dell’utente perché mascherata dalla login applicativa. Abbiamo tuttavia la necessità di conoscere chi fa che cosa e quando sul database applicativo.

La soluzione più pulita sarebbe quella di creare, sul DB, una login/utente per ogni utente applicativo assegnando correttamente ruoli e diritti a livello di database, in questo modo *vera* identità dell’utente non verrebbe mascherata dalla login applicativa. In alcune situazioni però non è possibile (almeno nel breve periodo) spostare la gestione delle login (e degli utenti) dall’applicazione al database. Pensiamo ad esempio ad un software gestionale che implementa, lato applicativo, l’intera gestione degli utenti e dei relativi permessi di accesso alle funzioni.

Una soluzione alternativa è rappresentata dall’implementazione di una tabella di associazione SIPD/utente applicativo. Subito dopo aver eseguito la connessione al database l’applicazione conoscerà sia lo SPID ovvero l’ID della sessione relativa al processo utente, che l’utente applicativo utilizzato per fare login. Queste due informazioni con l’aggiunta della data/ora di login potranno essere memorizzate (dall’applicazione stessa) nella tabella di associazione dbo.userforSPID. Di seguito i comandi T-SQL per il setup delle tabelle dbo.userforSPID e dbo.auditing. Nella tabella dbo.auditing, rispetto alla precedente versione, è stata aggiunta la colonna externaluser:

use [AdventureWorks];
go

-- Drop table dbo.userforSPID if exists
if (object_id('userforSPID') is not null)
drop table dbo.userforSPID;
go

-- Create table dbo.userforSPID
-- Memorizza l'utente esterno (user name) associato ad un
-- determinato SPID in un determinato momento
create table dbo.userforSPID
(id int identity(1, 1),
SPID int not null default @@SPID,
externaluser nvarchar(128) not null,
login_time datetime not null,
logout_time datetime default null
primary key(id)
)
go

-- Drop table dbo.auditing if exists
if (isnull(object_id('auditing'), 0) > 0)
drop table [dbo].[auditing]
go

-- Create table dbo.auditing if exists
create table [dbo].[auditing]
(id int identity (1, 1) not null,
rowid int null,
eventclass int null,
textdata nvarchar(max) null,
databaseid int null,
ntusername nvarchar(128) null,
ntdomainname nvarchar(128) null,
hostname nvarchar(128) null,
clientprocessid int null,
applicationname nvarchar(128) null,
loginname nvarchar(128) null,
spid int null,
duration bigint null,
starttime datetime null,
objectid int null,
objectname nvarchar(128) null,
databasename nvarchar(128) null,
dbusername nvarchar(128) null,
login_time datetime null,
oldvalue nvarchar(128) null,
newvalue nvarchar(128) null,
fieldname nvarchar(128) null,
externaluser nvarchar(128) null
)
on [primary]

Sarà necessario implementare anche la tabella dbo.lastimport per registrare la data/ora dell’ultima importazione, dal file fisico (traccia SQL) alla tabella dbo.auditing.

use [AdventureWorks];
go

-- Drop table dbo.lastimport if exists
if (object_id('lastimport') is not null)
drop table dbo.lastimport;
go

-- Create table dbo.lastimport
create table dbo.lastimport
(id int identity(1, 1),
lastexecution datetime not null
primary key(id)
)

Subito dopo aver eseguito la connessione, l’applicazione potrà registrare l’associazione SPID/utente applicativo semplicemente con:

-- Subito dopo la connessione al DB
insert into dbo.userforSPID
(
externaluser,
login_time
)
select
'ERPLogin',
(select login_time from sys.sysprocesses where SPID=@@SPID);

Dove “ERPLogin” rappresenta l’utente applicativo utilizzato per fare login; consultando la tabella di associazione si otterrà:

L’implementazione della traccia di auditing descritta nel precedente articolo riporta, per ogni evento, l’identificativo della sessione relativa al processo utente (SPID), tale ID avrà una corrispondenza nella tabella di associazione dbo.userforSPID.

Le attività registrate potranno essere associate all’utente applicativo attraverso lo SPID, la data/ora di login e la data/ora di esecuzione dell’evento registrato (attività tracciata), semplicemente con una join rivolta alla tabella di associazione allestita al momento della connessione.

Per importare le attività registrate, dal file fisico (traccia SQL) alla tabella dbo.auditing, si può utilizzare la seguente stored procedure schedulandone l’esecuzione attraverso un job:

use [AdventureWorks];
go

-- Drop procedure dbo.usp_trace_gettable_import_file if exists
if (object_id('usp_trace_gettable_import_file') is not null)
drop procedure [dbo].[usp_trace_gettable_import_file]
go

-- Create procedure dbo.usp_trace_gettable_import_file
create procedure dbo.usp_trace_gettable_import_file
as
begin

-- Aggiornamento dbo.lastimport
if (select count(id) from dbo.lastimport) = 0
    insert into dbo.lastimport
    (
      lastexecution
    )
    values
    (
      '19000101'
    );

-- Importazione dei dati raccolti da file (.trc) --> tabella dbo.auditing
Insert Into [dbo].[auditing]
(
    EventClass,
    TextData,
    DatabaseID,
    NTUserName,
    NTDomainName,
    HostName,
    ClientProcessID,
    ApplicationName,
    LoginName,
    Login_Time,
    SPID,
    Duration,
    StartTime,
    ObjectID,
    ObjectName,
    DatabaseName,
    DBUserName,
    externaluser
)
select
    T.EventClass,
    replace(cast(T.TextData as nvarchar(max)), char(13) + char(10), ''),
    T.DatabaseID,
    T.NTUserName,
    T.NTDomainName,
    T.HostName,
    T.ClientProcessID,
    T.ApplicationName,
    T.LoginName,
    (select
       s.login_time
     from
       sys.sysprocesses s
     where
       (s.spid=T.SPID)
    ) as Login_Time,
    T.SPID,
    T.Duration,
    T.StartTime,
    T.ObjectID,
    T.ObjectName,
    T.DatabaseName,
    T.DBUserName,
    uSPID.externaluser
from
    ::fn_trace_gettable('C:\Audit_Trace\audit_trace_file.trc', default) as T
left outer join
    dbo.userforSPID uSPID on (uSPID.SPID=T.SPID) and
                             (T.StartTime >= uSPID.login_time)
where
    (T.TextData is not null) and
    (T.StartTime >= (select lastexecution from dbo.lastimport));

if (@@error=0)
    update
      dbo.lastimport
    set
      lastexecution = getdate();

if (@@error=0)
    -- Cancellazione per SPID login_time e logout_time
    -- nella tabella dbo.userforSPID
    delete
      uSPID
    from
      dbo.userforSPID uSPID
    join
      dbo.auditing au on (au.SPID=uSPID.SPID) and
                         (au.starttime >= uSPID.login_time) and
                         (uSPID.logout_time is not null);
end

Subito dopo aver chiuso la connessione, l’applicazione potrà registrare la data/ora di logout nella tabella di l’associazione SPID/utente applicativo, alla prossima esecuzione della stored procedure dbo.usp_trace_gettable_import_file verranno importate le registrazioni mancanti e successivamente verranno eliminate le associazioni SPID/utente applicativo con data/ora login diversa da NULL.

Le registrazioni importate sono state arricchite della login applicativa utilizzata, siamo quindi in grado di conoscere quale utente applicativo che ha eseguito una determinata attività:

select
SPID,
externaluser,
starttime,
textdata,
objectname,
databaseid
from
dbo.auditing;

Indici doppi: un sovraccarico inutile #2

sgovoni — Thu, 18 Jun 2009 21:57:00 GMT

Grazie alla segnalazione di Andrea Benedetti, nel post Indici duplicati. Qualche considerazione, ho potuto correggere un bug nella stored procedure dbo.usp_drop_double_more_index:

Con l’occasione ho eliminato anche l’utilizzo dei cursori … la versione aggiornata è disponibile qui.

Speaker Dinner @ Giovanni restaurant

sgovoni — Tue, 16 Jun 2009 00:19:00 GMT

Una foto per immortalare questa magnifica cena pre-workshop, finalmente ci siamo potuti conoscere di persona.

Tra una portata e l'altra, tanto SQL... DDL Trigger... ma non solo...

Da sinistra a destra, Francesco Diaz, io, Davide Mauri, Luca Bovo, Marco Russo e Andrea Benedetti.

Bellissima serata!!

Torino 16 Giugno 2009: Workshop UGISS now!

sgovoni — Sun, 24 May 2009 23:14:00 GMT

Tra meno di un mese si terrà il primo Workshop UGISS nella città di Torino.

http://www.ugiss.org/Content/Event/Workshop-UGISS-Torino.aspx

C’è grande attesa per questo evento tecnico, a 360° sul mondo SQL Server, composto da ben 6 sessioni che reputo di grande interesse ed attualità: si parlerà di Modellazione, Business Intelligence, Virtualizzazione, Sicurezza e Gestione dei carichi di lavoro.

Ho l’onore di partecipare a questo workshop come speaker con la sessione:

Meccanismi di Auditing con SQL Server 2005 e 2008

Con SQL Server 2008 il supporto all'auditing è nativo grazie al comando "CREATE AUDIT", con SQL Server 2005 è necessario invece costruire una soluzione basata sull’utilizzo di SQL Trace. L'utilizzo delle tracce è fondamentale per mantenere alto il livello delle prestazioni, esse infatti, non sono intrusive come i Trigger ed inoltre rappresentano l'unico modo per poter effettuare l'audit anche del comando “TRUNCATE TABLE” che altrimenti non potrebbe mai essere intercettato in quanto non scatena Trigger. Si utilizzeranno i Trigger solo in quelle situazioni in cui è necessario conoscere il valore che aveva un attributo prima dell’aggiornamento ed il valore che ha assunto dopo l’aggiornamento.

La partecipazione all’evento è *gratuita*, sì avete letto bene … sarebbe un peccato non partecipare!! e se sarete pronti e fortunati potrete portare a casa un Best-Seller su SQL Server... da leggere sotto l'ombrellone .

Aggiornare una tabella pivot di Microsoft Excel #2

sgovoni — Sun, 17 May 2009 21:56:00 GMT

Oltre alle tabelle pivot, un foglio di lavoro Microsoft Excel può ospitare anche oggetti di tipo QueryTables.

E’ disponibile un aggiornamento della stored procedure dbo.USP_DMO_Excel_Pivot_RefreshTable con cui ora è possibile rinfrescare anche eventuali QueryTables presenti nei fogli di lavoro del file XLS indicato come parametro.

SQLCLR instead of OLE Automation (sp_OA* method)

sgovoni — Sat, 11 Apr 2009 17:32:00 GMT

Riprendo il tema dell'aggiornamento dei dati esposti su una tabella pivot di un foglio di lavoro Microsoft Excel.

Con il post Aggiornare una tabella pivot di Microsoft Excel abbiamo realizzato una possibile soluzione basata sull'utilizzo di OLE Automation Stored Procedures (sp_OA*).

Vediamo ora com'è possibile raggiungere lo stesso risultato utilizzando il Common Language Runtime (CLR) del .Net Framework... Read More...

[OT] 5x1000 Volte Grazie

sgovoni — Tue, 24 Mar 2009 01:14:00 GMT

Un brevissimo OT per segnalare la possibilità devolvere il cinque per mille delle imposte a favore di organizzazioni non-profit come l’Hospice MTC Seràgnoli oppure alla ricerca scientifica e sanitaria.

Segnalo inoltre che è stato creato il gruppo Sostenitori dell'Hospice MT. Chiantore Seràgnoli sul famosissimo LinkedIn.

Tutti coloro che vogliono essere aggiornati sulle iniziative a sostegno dell’Hospice non devono far altro che aggiungersi al gruppo:

http://www.linkedin.com/groups?gid=1856898

Indici doppi: un sovraccarico inutile

sgovoni — Mon, 26 Jan 2009 00:34:00 GMT

Immaginiamo di dover cercare una parola in un libro. L’azione più naturale (la prima che ci viene in mente) è quella di andare nell’indice analitico dove possiamo facilmente trovare le pagine nelle quali appare la parola che stiamo cercando, perché le parole in un indice sono ordinate alfabeticamente. Possiamo quindi leggere le pagine indicate dall’indice e trovare tutte le informazioni associate alla parola cercata.

Cosa succederebbe se il nostro libro non avesse indici ? Avremmo dovuto leggere l’intero libro, dall’inizio, pagina per pagina, per cercare tutte le occorrenze della parola che desideriamo trovare.

Anche SQL Server applica questa stessa logica quando ricerca per un valore di un attributo. Indicizzando l’attributo SQL Server potrà eseguire un’index seek, in alternativa l’engine sarà costretto a leggere tutte le pagine eseguendo l’operazione che viene chiamata table scan.

Potremmo quindi avere la tentazione di indicizzare tutti i possibili attributi di una tabella! In ogni caso SQL Server dovrà mantenere aggiornati tutti gli indici che abbiamo deciso di creare… più indici decideremo di creare più lenti saranno i comandi di update.

E se un attributo fosse doppiamente indicizzato ? In questo caso SQL Server sarà costretto ad aggiornare due indici, organizzati in strutture B-Tree letteralmente identiche, senza trarre alcun beneficio da una delle due.

La stored procedure dbo.usp_drop_double_more_index, illustrata nella figura seguente, permette di individuare ed eliminare i casi di doppia (tripla, ...) indicizzazione dei medesimi attributi.

Aggiornare una tabella pivot di Microsoft Excel

sgovoni — Fri, 09 Jan 2009 01:08:00 GMT

Le tabelle pivot sono un’apprezzabile caratteristica di Microsoft Excel (Learn to use Pivot Tables in Excel 2007) e vengono largamente utilizzate per aggregare i dati con l’obiettivo di presentarli attraverso nuova prospettiva, in pochi click.

Si potrebbe pensare ad una tabella pivot come ad una tabella utente, riepilogativa, di un foglio di lavoro originale. È possibile creare una tabella pivot definendo quali campi visualizzare e come i dati dovranno essere visualizzati.

In funzione dei campi selezionati, Excel aggrega e organizza i dati in modo da fornire un diverso punto di vista per l’analisi dei dati stessi. I dati da analizzare potrebbero essere esterni (origine dati esterna):

... e potrebbero risiedere all’interno di un database SQL Server interrogato con una query ad hoc che collega più tabelle, come ad esempio avviene in una tabella pivot implementata per l’analisi del portafoglio ordini o del fatturato trimestrale di un’azienda.

Ad esempio, i dati necessari all'analisi del fatturato provengono dal sistema informativo aziendale e vengono modificati frequentemente (si spera ), in queste situazioni, potrebbe essere richiesto un aggiornamento automatico, della tabella pivot, ogni X minuti oppure al verificarsi di un determinato evento senza dover necessariamente aprire il file Excel.

La stored procedure dbo.USP_DMO_Excel_Pivot_RefreshTable, attraverso l’utilizzo di OLE Automation Stored Procedures, permette di aggiornare i dati esposti su una tabella pivot di un determinato file Excel (senza l’apertura manuale del documento XLS).

Conoscere la data/ora dell'ultimo user UPDATE su una tabella

sgovoni — Sat, 20 Dec 2008 18:58:00 GMT

Come posso conoscere la data e l’ora dell’ultimo user UPDATE eseguito su una tabella ?

Una possibile soluzione a questo link:

http://www.ugiss.org/Content/Article/Conoscere-la-data-ora-dell-ultimo-user-UPDATE-su-una-tabella.aspx

Etichette di identificazione prodotti: estrazione ordinata su due colonne

sgovoni — Fri, 19 Dec 2008 21:24:00 GMT

Si sta avvicinando la fine dell’esercizio fiscale e la vostra azienda si appresta ad eseguire l’inventario fisico dei prodotti giacenti a magazzino.

Il responsabile del magazzino, in occasione dell’inventario fisico, desidera etichettare tutti i prodotti in modo da identificarli univocamente. Uno degli obbiettivi del prossimo anno è infatti l’implementazione della movimentazione merci con l’utilizzo dei codici a barre e l’etichettatura dei prodotti rappresenta un prerequisito fondamentale.

L’azienda dispone di una stampante di etichette adesive a trasferimento termico avente un supporto cartaceo a due colonne. Vi hanno chiesto l’estrazione dei dati che identificano in modo univoco un prodotto e la possibilità di stamparli, in modo ordinato, su etichette a due colonne.

L’anagrafica prodotti ha una struttura simile a quella disegnata nel database AdventureWorks con la tabella Production.Product.

Per produrre l’output richiesto si può utilizzare la stored procedure dbo.usp_split_column con la seguente chiamata:

EXEC dbo.USP_SPLIT_COLUMN

@TableName= 'Production.Product',

@AliasName= 'P',

@FieldPK= 'ProductID',

@FieldName= 'ProductNumber',

@FieldName1= 'Name',

@WhereCond= '',

@TableJoin= 'Production.ProductSubCategory',

@AliasJoin= 'C',

@FieldJoinLeft= 'ProductSubcategoryID',

@FieldJoinRight= 'ProductSubcategoryID',

@FieldJoinList= 'LTRIM(RTRIM(C.Name))'

[OT] Il week end che preferisco

sgovoni — Sat, 29 Nov 2008 22:01:00 GMT

Tra colleghi non è facile pianificare un week end fuori porta accontentando tutti tra mille impegni e imprevisti, ma questa volta ce l’abbiamo fatta!

Nonostante le previsioni meteo , siamo partiti per un week end in montagna, ospiti nell’appartamento di un carissimo amico ed ex-collega. La neve non ha tardato ad arrivare, già dalla sera di venerdì...

...e questa mattina ci siamo svegliati immersi in questo magnifico paesaggio:

Peccato non si possa fare più spesso

Indicizzazione dei vincoli FOREIGN KEY

sgovoni — Wed, 26 Nov 2008 22:46:00 GMT

Abbiamo utilizzato la stored procedure usp_create_index_on_foreign_key per determinare e creare gli indici mancanti per i vincoli FOREIGN KEY in essere su una determinata tabella o su un determinato schema.

Lo scenario illustrato di seguito permetterà di osservare il comportamento dell’ottimizzatore durante la fase di ricerca del piano di esecuzione migliore per risolvere una query che faccia uso di JOIN per l’estrazione dei dati di due tabelle relazionate tra loro attraverso una chiave primaria (Primary Key) ed una chiave esterna (Foreign Key).

Confronteremo i piani di esecuzione utilizzati ed il numero di accessi di I/O con e senza indici.

Scenario

La nostra azienda ha un database SQL Server 2005 che contiene le tabelle dbo.OrderHeader e dbo.OrderDetail utilizzate rispettivamente per memorizzare testate e righe degli ordini di vendita ricevuti dai propri clienti. Gli utenti utilizzano una query di estrazione dati applicando un filtro per numero ordine.

Utilizziamo il seguente script T-SQL per la generazione delle tabelle dbo.OrderHeader e dbo.OrderDetail:

-- Drop table OrderDetail
IF (OBJECT_ID('OrderDetail', 'U') IS NOT NULL)
DROP TABLE dbo.OrderDetail
GO

-- Create table OrderDetail
CREATE TABLE dbo.OrderDetail
(OrderDetailID INT IDENTITY(1, 1) NOT NULL,
OrderHeaderID INT NOT NULL,
ProductID INT NOT NULL,
RigNumber AS (OrderDetailID * 2) ,
UnitPrice MONEY DEFAULT 0 NOT NULL
PRIMARY KEY(OrderDetailID)
)

GO

-- Drop table Order
IF (OBJECT_ID('OrderHeader', 'U') IS NOT NULL)
DROP TABLE dbo.OrderHeader
GO

-- Create table OrderHeader
CREATE TABLE dbo.OrderHeader
(OrderID INT IDENTITY(1, 1) NOT NULL,
OrderDATE DATETIME DEFAULT GETDATE() NOT NULL,
OrderNUMBER AS (ISNULL(N'SO' + CONVERT([nvarchar](23), [OrderID], 0), N'*** ERROR ***')),
CustomerID INT DEFAULT 1 NOT NULL,
ShipName VARCHAR(20) DEFAULT 'Name',
ShipAddress VARCHAR(40) DEFAULT 'Address',
ShipVia VARCHAR(40) DEFAULT 'Via',
ShipCity VARCHAR(20) DEFAULT 'City',
ShipRegion VARCHAR(20) DEFAULT 'Region',
ShipPostalCode VARCHAR(20) DEFAULT 'Postal code',
ShipCountry VARCHAR(20) DEFAULT 'Country'
PRIMARY KEY(OrderID)
)

GO

-- ALTER TABLE ADD CONSTRAINT
ALTER TABLE
[dbo].[OrderDetail]
WITH CHECK ADD CONSTRAINT
[FK_OrderHeaderID]
FOREIGN KEY
([OrderHeaderID])
REFERENCES
[dbo].[OrderHeader] ([OrderID])

Inseriamo alcuni dati di prova:

DECLARE @i AS int,
        @scope_id AS int

SET @i = 0
SET @scope_id = 0

WHILE (@i < 20000)
BEGIN
SET NOCOUNT ON

INSERT INTO dbo.OrderHeader DEFAULT VALUES

SELECT @scope_id = SCOPE_IDENTITY()

-- 1
INSERT INTO dbo.OrderDetail
(OrderHeaderID,
   ProductID,
   UnitPrice)
SELECT
    @scope_id,
    (@i * 2)+1,
    ((@i * 2)+1)/2

-- 2
INSERT INTO dbo.OrderDetail
(OrderHeaderID,
   ProductID,
   UnitPrice)
SELECT
    @scope_id,
    (@i * 3)+1,
    ((@i * 3)+1)/3

-- 3
INSERT INTO dbo.OrderDetail
(OrderHeaderID,
   ProductID,
   UnitPrice)
SELECT
    @scope_id,
    (@i * 4)+1,
    ((@i * 4)+1)/4

SET @i = (@i + 1)

SET NOCOUNT OFF
END

Utilizziamo ad esempio la seguente query (Q1) per consultare alcune informazioni relative all’ordine numero 'SO9000':

select
OH.OrderDATE,
OH.OrderNUMBER,
OH.CustomerID,
OD.ProductID
from
dbo.OrderHeader OH
join
dbo.OrderDetail OD ON OD.OrderHeaderID = OH.OrderID
where
OH.OrderNUMBER = 'SO9000'

La Foreign Key FK_OrderHeaderID non è indicizzata, il piano di esecuzione utilizzato per soddisfare la query (Q1) è il seguente:

Si osservano i due Clustered Index Scan rispettivamente sulle tabelle dbo.OrderHeader e dbo.OrderDetail con le seguenti statistiche di I/O:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 50 ms.

(3 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrderDetail'. Scan count 1, logical reads 218, physical reads 0, read-ahead reads 224, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrderHeader'. Scan count 1, logical reads 211, physical reads 1, read-ahead reads 212, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 15 ms, elapsed time = 90 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms, elapsed time = 1 ms.

Utilizziamo ora la stored procedure usp_create_index_on_foreign_key per generare l’indice (NONCLUSTERED) mancante sul campo OrderHeaderID della tabella dbo.OrderDetail:

EXEC dbo.usp_create_index_on_foreign_key 1, N'dbo', N'OrderDetail', 0;

usp_create_index_on_foreign_key : Generazione indici...

Esecuzione di: CREATE NONCLUSTERED INDEX [IDX__OrderDetail_OrderHeaderID] ON dbo.OrderDetail(OrderHeaderID) completata correttamente.

usp_create_index_on_foreign_key : Generazione indici completata.

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

La Foreign Key FK_OrderHeaderID è ora indicizzata, eseguiamo di nuovo la query (Q1) e confrontiamo il nuovo piano di esecuzione e le statistiche di I/O:

Osserviamo ora che il Clustered Index Scan sulla tabella dbo.OrderDetail è stato sostituito con un Index Seek sull’indice IDX__OrderDetail_OrderHeaderID individuato e creato dalla stored procedure. Le statistiche di I/O denotano una sensibile diminuzione delle letture sulla tabella di dettaglio ordini i cui dati vengono acceduti attraverso l’indice:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 5 ms.

(3 row(s) affected)
Table 'OrderDetail'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'OrderHeader'. Scan count 1, logical reads 211, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 15 ms, elapsed time = 18 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
   CPU time = 0 ms, elapsed time = 1 ms.

Conclusioni

Creare un indice su una chiave esterna rappresenta spesso un'operazione utile in quanto:

Le modifiche apportate ai vincoli PRIMARY KEY vengono confrontate con i vincoli FOREIGN KEY nelle tabelle correlate
Le colonne chiave primaria vengono citate nei criteri di JOIN quando per riunire i dati di tabelle diverse si confrontano rispettivamente le colonne nel vincolo FOREIGN KEY con le colonne nel vincolo PRIMARY KEY

In questi casi la presenza di un indice consente all’engine di SQL Server di trovare rapidamente i dati correlati nella tabella che contiene la chiave esterna.

Non si tratta solo di dati...

sgovoni — Thu, 30 Oct 2008 23:32:00 GMT

...è una nuova forma di energia:

http://www.microsoft.com/servers/sql/2008/sqlserverenergy/it/it/default.aspx?WT.srch=1&WT.mc_id=ms_sql