MetaTips

Le blog de l'edition logiciel en ligne

Articles

Memento T-SQL - Part 1

Posted by Allouche Mathieu on May 8, 2012 at 5:50 AM

Type : Mémento - Niveau : 1 / 5
Tous les ans, je prend le temps de completer ce petit mémento. Voici un ensemble de requêtes sur le thème ‘utiliser les tables, les vues systèmes’. Bonne utilisation.





Type : Mémento
Niveau : 1 / 5
Durée de lecture : < 1 heure
Compatibilité / s'applique à :

  • SQL Serveur 2005
  • SQL Serveur 2008


Tables systèmes

select * from sys.tables
select * from sys.messages where language_id=1036 -- Liste des messages avec leur severités

Autre

select

* from [action] TABLESAMPLE(7 Percent) -- ou ROWS !Compatibilité 9.0 uniquement!
dbcc freeproccache -- Nettoyage du cache des proc
sp_recompile vTestView -- Demande une recompilation à la prochaine demande
select * from vTestView (with noexpand) -- Pour forcer de taper dans les index d'une vue

GESTION DU FULLTEXT

  1. Listes les langues installées

select * from sys.fulltext_languages

GESTION DES INDEXES

  1. Lister les indexes d'une table :

    sp_helpindex [nom_de_latable] -- Ecriture à la mode MSSQL 2000
    select * from sys.indexes where object_id= OBJECT_ID('ACTION') -- A partir de MSSQL 2005
  2. Creation

    CREATE
    UNIQUE NONCLUSTERED INDEX IX_Product_Supply_Chain

    ON Production.Product (ProductNumber, Color, ReorderPoint, SafetyStockLevel)
    INCLUDE (DaysToManufacture)
    WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, FILLFACTOR = 90, PAD_INDEX = ON)

  3. Affichage du niveau de fragmentation d'un index

    select * from sys.dm_db_index_physical_stats (DB_ID('nom_de_la_base_de_données'), OBJECT_ID('nom_de_la_table'), NULL, NULL, 'detailed');

  4. Recalcul d'un index

    Syntaxe MSSQL 2000

    dbcc updateusage ('BP_DEV_MLPV5_AGENCY_FG3P','ACTION','PK_CAMPAIGN_ACTIONS')

    Resultat :

    DBCC UPDATEUSAGE: counts updated for table 'ACTION' (index 'PK_CAMPAIGN_ACTIONS', partition 1):
    USED pages (In-row Data): changed from (191) to (163) pages.
    RSVD pages (In-row Data): changed from (247) to (214) pages.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Syntaxe MMSSQL 2005/2008

    alter index IX_ACTION on action REBUILD


  5. dsdsds

-- 3.

-- GESTION DES INDEXES XML

-- 1. Creation

create primary xml index IX_Primary_XML on humanResources.JobCandidate(XmlCard) with (fillfactor = 60, PAD_Index = ON)

create xml index IX_Secondary_XML on humanResources.JobCandidate(XmlCard) USING XML INDEX IX_XML_PRIMARY FOR PATH

-- GESTION DES TRIGGERS

EXEC sp_configure 'nested triggers', '0'

-- FAIRE DU DEBUG

-- Affiche les options avancées

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

-- Indique le temps d'attente maxi à 10 secondes

EXEC sp_configure 'blocked process threshold', 10

RECONFIGURE

-- Masque les options avancée

EXEC sp_configure 'show advanced options', 0

RECONFIGURE

-- GESTION DES BACKUPS

USE [master]

GO

ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT

GO

EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'BackupFull', @physicalname = N'C:\AW backups\BackupFull.bak'

GO

EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'BackupLog', @physicalname = N'C:\AW backups\BackupLog.bak'

GO

EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'BackupDiff', @physicalname = N'C:\AW backups\BackupDiff.bak'

GO

BACKUP DATABASE [AdventureWorks] TO [BackupFull] WITH NOFORMAT, INIT, NAME = N'AdventureWorks-Full Database', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

BACKUP LOG [AdventureWorks] TO [BackupLog] WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Transaction Log', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

BACKUP DATABASE [AdventureWorks] TO [BackupDiff] WITH DIFFERENTIAL , NOFORMAT, INIT, NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

BACKUP LOG [AdventureWorks] TO [BackupLog] WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Transaction Log Backup 2', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

-- GESTION DES RESTAURATIONS

BACKUP LOG [AdventureWorks] TO [BackupLog] WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Transaction Log Backup after fail', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

RESTORE DATABASE [AdventureWorks] FROM DISK = N'C:\AW backups\BackupFull.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

RESTORE DATABASE [AdventureWorks] FROM DISK = N'C:\AW backups\BackupDiff.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

RESTORE LOG [AdventureWorks] FROM DISK = N'C:\AW backups\BackupLog.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

RESTORE LOG [AdventureWorks] FROM DISK = N'C:\AW backups\BackupLog.bak' WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 10

GO

RESTORE LOG [AdventureWorks] FROM DISK = N'C:\AW backups\BackupLog.bak' WITH FILE = 3, NOUNLOAD, STATS = 10

GO

-- GESTION DES TRANSACTIONS et des CATCHs

-- 1. Gerer des transactions automatique à eviter

set IMPLICIT_TRANSACTIONS on

-- 2. Gestion complete

SET XACT_ABORT ON

Begin try

begin tran

...

commit tran

End try

Begin catch

-- print error_number(), error_message(), error_severity()...

if (XACT_State() = -1)

rollback tran

else

commit tran

End catch

-- 3. Gerer le niveau de lock, au niveau du context de connection. Le remettre apres ou faire exec sp_reset_connection

SET transaction isolation level (read uncommitted)

Use set lock_Timeout -- en millisecondes

select @@lock_timeout

-- GESTION DES CHAMPS XML

SET @cmd = @messagebody.value ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(100)')

SET @messagebody.value ('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(24)')

SET @spid = @messagebody.value ('(/EVENT_INSTANCE/SPID)[1]','nvarchar(6)')

-- GESTION DES TRIGGERS

ALTER ASSEMBLY [Sample_CLR_Project] ADD FILE FROM 0x496D70....50D0A0D0A AS N'Sample_UDT.vb'

Create trigger as external name

-- GESTION DE SERVICE BROKER

-- 1. Verification de l'activation du service

SELECT name, is_broker_enabled FROM sys.databases

-- 2. Activation du service

ALTER DATABASE BP_DEV_MLPV5_AGENCY_3WPP SET ENABLE_BROKER

-- 3. Declaration du type de message

CREATE MESSAGE TYPE [//Adventure-Works.com/Expenses/ExpenseClaim] VALIDATION = WELL_FORMED_XML

CREATE MESSAGE TYPE [//Adventure-Works.com/Expenses/ClaimResponse] VALIDATION = VALID_XML WITH SCHEMA COLLECTION awschemas

-- 4. Declaration du contrat

CREATE CONTRACT [//Adventure-Works.com/Expenses/ExpenseSubmission]

(

[//Adventure-Works.com/Expenses/ExpenseClaim] SENT BY INITIATOR,

[//Adventure-Works.com/Expenses/ClaimResponse] SENT BY TARGET

)

-- 5. Creation de la queue

CREATE QUEUE ExpenseQueueWithActivation WITH STATUS = OFF, ACTIVATION

(

STATUS = ON,

PROCEDURE_NAME = PSDeclancheur, -- Nom de la procedure à declancher

MAX_QUEUE_READERS = 5,

EXECUTE AS SELF

)

-- 6. Creation du service

CREATE SERVICE [//Adventure-Works.com/SubmitExpense] ON QUEUE ExpensesInitiator ( [//Adventure-Works.com/Expenses/ProcessExpense] )

CREATE SERVICE [//Adventure-Works.com/ProcessExpense] ON QUEUE ExpensesTarget ( [//Adventure-Works.com/Expenses/ProcessExpense] )

-- 7. Pousser un message

DECLARE @message xml

DECLARE @firstName nvarchar(50)

DECLARE @lastName nvarchar(50)

DECLARE @emailAddress nvarchar(50)

set @firstName = 'Mathieu'

set @lastName = 'Mathieu'

set @emailAddress = '[email protected]'

SET @message = NCHAR(0xFEFF)

+ ''

+ '' + @firstName + ' ' + @lastName + ''

+ '' + @emailAddress + ''

+ ''

DECLARE @dialogHandle UNIQUEIDENTIFIER

BEGIN DIALOG @dialogHandle

FROM SERVICE [//AW/Sales/CustomerService]

TO SERVICE '//AW/Email/EmailService'

ON CONTRACT [//AW/EMail/SendCustomerDetails];

SEND ON CONVERSATION @dialogHandle

MESSAGE TYPE [//AW/EMail/CustomerDetails] (@message)

END CONVERSATION @dialogHandle

-- 8. Lecture

DECLARE @conversation UNIQUEIDENTIFIER

DECLARE @msg NVARCHAR(MAX)

DECLARE @msgType NVARCHAR(256)

;RECEIVE TOP(1)

@conversation = conversation_handle,

@msgType = message_type_name,

@msg = message_body

FROM AdventureWorks.CustomerSvc.NewCustomerEmailQueue

IF (@@ROWCOUNT = 0) RETURN

IF (@msgType = '//AW/EMail/CustomerDetails')

BEGIN

-- Extract the email address

DECLARE @emailAddress nvarchar(30)

DECLARE @hDoc int

EXECUTE sp_xml_preparedocument @hdoc OUTPUT, @msg

Categories: SQL Serveur

Post a Comment

Oops!

Oops, you forgot something.

Oops!

The words you entered did not match the given text. Please try again.

Already a member? Sign In

0 Comments