SQL Reference

By Frederic JOFES and Co.

Catégorie : Script (Page 1 of 4)

Display rowid

— Script permettant d’afficher le rowid des lignes d’une table. On remarque que lorsque l’on supprime des lignes et qu’on en ajoute d’autres, le numéro utilise les plages vides, la création d’un index cluster est donc nécessaire pour ordonner les données. L’instruction select effectue un scan de la table si aucun index cluster n’est crée

CREATE TABLE T2(A varchar(50), X INT);
INSERT INTO T2 VALUES ('toto1', 1),('toto2', 2), ('toto3', 3), ('toto4', 4);
SELECT X, A, %%physloc%% AS [%%physloc%%],
sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM T2;
DELETE FROM T2 WHERE X = 3;
INSERT INTO T2 VALUES('toto3', 3), ('toto44', 44);
SELECT X, A, %%physloc%% AS [%%physloc%%],
sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM T2;
SELECT A
FROM T2
WHERE X = 44;
CREATE INDEX idx_T2_X ON T2 (X);
SELECT X
FROM T2
WHERE X = 44;

print unix date to readable date

Pour transformer une date Unix en date lisible :
SELECT FROM_UNIXTIME(1111885200)

 

Load Csv file into mysql database

  • Faire un export Csv avec MySQL Workbenck puis :
  • LOAD DATA LOCAL INFILE 'C:\\Data\\Eclipse\\insert_Index_Data.csv' INTO table index_data FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' STARTING BY ''

Rename an SQL Server instance

 Avant de renommer un serveur SQL, il est indispensable d’effectuer l’operation suivante sur le moteur de base de données :

1/ Instance par defaut

sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO

1 bis/ Instance nommée

sp_dropserver <'old_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO

Changer le nom du serveur (poste de travail/Propiétés système)

redemarrer le server

Control du nouveau nom

SELECT @@SERVERNAME AS ‘Server Name’

Sous dos :

hostname

Insert Filestream

Paramétrage du serveur :
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
Parametrage de la base :
CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'c:\data\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'c:\data\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'c:\data\archlog1.ldf')
GO
Parametrage de la table :
CREATE TABLE [dbo].[BlobTable](
	[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
	[Doc] [varbinary](max) FILESTREAM NULL
) ON [PRIMARY]
Insertion d’une valeur dans la table :
INSERT INTO dbo.BlobTable (Id,Doc)  
SELECT CAST('B14BC077-F1DF-457C-9F7E-7CB9E0BC1CF5' as uniqueidentifier),* FROM 
OPENROWSET(BULK N'C:\Data\Clients\Loreal\FRDGOBIPPDSQL1_ApplicationLog.evtx', SINGLE_BLOB) as I

Interet de Filestream :

  • Stocker des fichiers de plus de 2 Go
  • Déporter les fichiers dans un répertoire du système
  • Compresser les enregistrements pour en faire tenir plus par page.

« 

Création d’une table test pour générer des inserts

Creation de la table :
/****** Object:  Table [dbo].[TableTest]    Script Date: 08/16/2010 16:07:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TableTest](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DateInsert] [datetime2](7) NULL,
[Libelle] [nvarchar](1000) NULL,
CONSTRAINT [PK_TableTest] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
Création dela procedure de génération :
create procedure InserTest
@NbLignes varchar(50)
as
begin
DECLARE @n INT
SET @n = 1
while @n<= @NbLignes
begin
insert into TableTest values (getdate(),'Test insertion en base')
set @n = @n + 1
end
end

 

Generation des enregistrements (exemple pour 1000) :
insertest 1000
 Temps d’execution sur differentes plateforme pour 1 millions de lignes :

Windows XP  pro SP3, 2 CPUs, 2 Gz, 2 Go de RAM : 7mn 44 s

Volume occupé pour 1 millions de lignes :

Data : 68 Mo

Index :  0,2 Mo

 

 

Mesurer l’espace utilisé par la tempdb

Pour la session courante :

SELECT * FROM sys.dm_db_session_space_usage WHERE SESSION_ID = @@spid
GO
--<create or rebuild the index>
GO
select * from sys.dm_db_session_space_usage where session_id = @@spid
GO

Pour chaque taches :

sys.dm_db_task_space_usage

Analyse sur les répercutions des actions de réindexation sur la tempdb :
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/tempdb_capacity_planning_index.mspx

SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name

Replace count(*) by sp_rowcount

Cette procédure permet d’obtenir des meilleurs performances que le SELECT COUNT(*) en passant par l’index pour retourner le nombre d’enregistrement d’une table.

/****** Object: Stored Procedure dbo.sp_rowcount Script Date: 7/28/1999 3:15:53 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.sp_rowcount') and sysstat & 0xf = 4)
drop procedure dbo.sp_rowcount
GO
create procedure sp_rowcount
@tablename varchar(32) = null
as
/**************************************************************************************************
author: douglas a. bass
date: 07/29/1999
description: returns rowcounts for all tables in the current database,
or if @tablename is provided, for that table only.
caveat: this procedure makes use of sysindexes, so make sure all indexes are up-to-date
thanks to: jon a. kale
**************************************************************************************************/
declare @dbname varchar(32)
set nocount on
if @tablename is not null
begin
if exists (select * from sysobjects where id = object_id(@tablename) and sysstat & 0xf = 3)
begin
select o.name, i.rows
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.id = object_id(@tablename)
and i.indid < 2
order by o.name
end
else
begin
select @dbname = db_name()
raiserror ('Object "%s" does not exist in database "%s" or is not a user table.',
16, 1, @tablename, @dbname)
end
end
else
begin
select o.name, i.rows
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.type = 'u'
and i.indid < 2 order by o.name
end
--
GO

Memory check

Différentes petites requêtes pour l’analyse de la mémoire et des espaces occupés

EXEC sp_spaceused
SELECT fileid, name, filename, size, growth, status, maxsize
FROM dbo.sysfiles WHERE (status & 0x40) <>0
DBCC sqlperf(logspace)
DBCC showfilestats
DBCC MEMORYSTATUS
-- SQL 2000
select * from sys.sysperfinfo
-- http://support.microsoft.com/kb/271624
-- SQL 2005
select * from sys.dm_os_performance_counters
-- SQL 2008
select * from sys.dm_resource_governor_workload_groups

Quick check of server and database properties

Propriétés du serveur :

xp_msver

Listing des bases avec poids, propriétaire et statut :

sp_helpdb

Lire la suite

Page 1 of 4

Fièrement propulsé par Fred & Co.