Kemarin buka buka lagi catatan waktu ikut TPrep Project Untuk SQL Server 2008 di Redmond Tahun yang lalu, ini saya lakukan karena memang Hari Kamis kemarin (22/10/2009) berkesempatan untuk membawakan session microsoft di Acara Metro Data Solution Day di Hotel Shangrila Jakarta. Kebetulan materi yang akan di bawakan berkaitan dengan fitur - fitur SQL yang berhubungan dengan pengolahan data dalam ukuran besan sehingga judul besar dari session yang saya adalah SQL Data Warehouse Penta Byte Data Solutions.
Saya coba share sedikit apa yang saya presentasikan di blog saya ini, khususnya fitur-fitur yang berhungan dengan pengelolaan data yang memiliki ukuran sangat besar dengan memanfaatkan fitur fitur terbaru dari MS SQL 2008.
Change data capture adalah topik pertama yang saya sampaikan disini, dimana fitur baru di SQL Server 2008 ini memudahkan kita untuk melakukan capturing terhadap perubahan yang terjadi bada sebuah table dalam sebuah database. Dengan CDC ini memungkin semua operasi yang dilakukan pada sebuah table bisa kita tracking historynya dari operasi yang dilakukan, dari mulai data pertama kali di masukkan, perubahan terhadap data di setiap kolom hingga data terakhir yang ada pada sebuah table
untuk membuat CDC ini, kita bisa lakukan di SQL Server 2008 dengan terlebih dahulu mengaktifkan fitur CDC pada sebuah database dengan perintah berikut ini
EXEC
sys.sp_cdc_enable_db;
GO
Perintah ini akan mengaktifkan fitur CDC di database yang kita akan buatkan Change Data Capture-nya, ini bisa dilihat dari adanya file tambahan pada database kita di bagian system table seperti
.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
Dengan CDC ini memungkinkan kita untuk dapat melakukan pencatatan terhadap perubahan data yang terjadi tanpa harus membuat table auditlog sendiri maupun membuat mekanisme trigger untuk mencatat sejarah perubahan data baik oleh aplikasi maupun oleh DBA secara back end
Berikut contoh scipt untuk fitur change data capture di SQL Server 2008, silahkan mencoba
--set current database context
USE [AdventureWorks]
GO
--append new user column to the HumanResources.Employee Table
ALTER TABLE humanresources.employee
ADD [User] NVARCHAR(50)
GO
--disable any triggers
DISABLE Trigger ALL ON HumanResources.employee
GO
--enable CDC for AW
EXEC Sp_cdc_enable_db
GO
--enable CDC for HumanResources.Employee
EXEC Sp_cdc_enable_table
humanresources ,
employee ,
NULL ,
1 ,
dbo
GO
--create sample UDF to return change data
CREATE FUNCTION [dbo].[Udf_employee]
(@start_time DATETIME,
@end_time DATETIME)
RETURNS @Employee TABLE(employeeid INT,
nationalidnumber NVARCHAR(15),
contactid INT,
managerid INT,
title NVARCHAR(50),
birthdate DATETIME,
maritalstatus NCHAR(1),
gender NCHAR(1),
hiredate DATETIME,
salariedflag [FLAG],
vacationhours SMALLINT,
sickleavehours SMALLINT,
currentflag [FLAG],
rowguid UNIQUEIDENTIFIER,
[user] NVARCHAR(50),
cdc_operation VARCHAR(1))
AS
BEGIN
--declare local variables to hold LSNs
DECLARE @from_lsn BINARY(10),
@to_lsn BINARY(10)
--Map the time interval to a change data capture query range.
IF (@start_time IS NULL)
BEGIN
SELECT @from_lsn = sys.Fn_cdc_get_min_lsn('HumanResources_Employee')
END
ELSE
BEGIN
SELECT @from_lsn = sys.Fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)
END
IF (@end_time IS NULL)
BEGIN
SELECT @to_lsn = sys.Fn_cdc_get_max_lsn()
END
ELSE
BEGIN
SELECT @to_lsn = sys.Fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)
END
--if same then exit
IF (@from_lsn = sys.Fn_cdc_increment_lsn(@to_lsn))
BEGIN
RETURN
END
-- Query for change data
INSERT INTO @Employee
SELECT employeeid,
nationalidnumber,
contactid,
managerid,
title,
birthdate,
maritalstatus,
gender,
hiredate,
salariedflag,
vacationhours,
sickleavehours,
currentflag,
rowguid,
[user],
CASE __$operation
WHEN 1
THEN 'D'
WHEN 2
THEN 'I'
WHEN 4
THEN 'U'
ELSE NULL
END AS cdc_operation
FROM cdc.Fn_cdc_get_net_changes_humanresources_employee(@from_lsn,@to_lsn,'all')
RETURN
END
GO
--sample DML statement
UPDATE humanresources.employee
SET title = 'Marketing Director',
[User] = system_user
WHERE employeeid = 13
--get the net change of the sample DML including the User
SELECT *
FROM [dbo].[Udf_employee](NULL,NULL)
Meski sepertinya sudah banyak yang tahu bahwa produk terbaru dari microsoft yaitu SQL Server 2008 memiliki kehandalan dalam menangani data dalam skala enterprise, namun tidak ada salahnya kita sedikit perhatikan perubahan mendasar dalam hal Syntax TSQL yang terbaru di SQL Server 2008 ini.
Beberapa fitur yang sangat membantu developer dalam hal kemudahan melakukan scripting di database diantaranya adalah adanya type data baru dan perintah SQL baru yang banyak membantu database developer khususnya bagi mereka yang memiliki latar belakang dari programming
Berikut beberapa Enhancement dari TSQL di SQL Server 2008
A. TSQL "Delighters"
terdapat kemampuan untuk melakukan perintah berikut dalam script TSQL di SQL 2008
DECLARE @t int = 5; -- ini tidak bisa dilakukan di versi sebelumnya
INSERT dbo.myT
VALUES (‘WA’, @t), (‘FL’, @t+1); --ini tidak bisa dilakukan di versi sebelumnya
UPDATE dbo.myT
SET instances+=1; --ini tidak bisa dilakukan di versi sebelumnya
B. Table Value Parameter
Memungkinkan kita untuk mengirimkan para meter data berupa table, yang bisa mengurangi rountrip dalam proses pengiriman data dari aplikasi ke server, karena bisa dilakukan dalam 1 kali pemanggilan SP
create proc ins_data (@t table (a int)) as … -- Declarasi SP seperti ini tidak bisa dilakukan pada versi sebelumnya
Berikut contoh implementasi Table value paramenter
USE AdventureWorks
GO
-- Declarasikan Table Value type EmployeeTable
CREATE TYPE EmployeeTableType AS TABLE
(EmpID INT, EmpName nvarchar(100), EmpEmail nvarchar(100))
USE AdventureWorks
GO
-- Buat Procedure yang menggunakan Parameter dengan type EmployeeTableType
CREATE PROCEDURE NewEmployee(@EmployeeDetails EmployeeTableType READONLY)
As
BEGIN
INSERT INTO dbo.Employee
SELECT * FROM @EmployeeDetails
END
-- Simpan data di table value type
use AdventureWorks
Go
DECLARE @NewEmployees EmployeeTableType
INSERT INTO @NewEmployees
VALUES(1,'John McLean','JohnMcLean@contoso.com')
INSERT INTO @NewEmployees
VALUES(2,'Bob Smith','BobSmith@contoso.com')
INSERT INTO @NewEmployees
VALUES(3,'Ted Connery','TedConnery@contoso.com')
--- Panggil SP
EXECUTE NewEmployee @NewEmployees
C. MERGE Statement
Merge di gunakan untuk menangani proses UPSERT (UPDATE or INSERT) terhadap suatu table, perintah ini menyederhanakan perintah IF Exist yang selama ini di gunakan untuk melakukan syncronisasi data. skenario umumnya jika data sudah ada maka akan di update diantaranya
berikut Syntax MERGE Statement
[ WITH <common_table_expression> [ ,…n ] ]
MERGE
[ TOP (expression) [ PERCENT ] ]
[ INTO ] <target_table> [ [ AS ] table_alias ] [ WITH( <merge_hint> ) ]
USING <table_source>
ON <search_condition>
<merge_clause> [ …n ]
[ OUTPUT <dml_select_list> ]
[ OPTION ( <query_hint> [ ,…n ] ) ]
;
<merge_clause>:=
{
WHEN MATCHED [ AND <search_condition> ]
THEN { UPDATE SET <set_clause> | DELETE }
| WHEN [ TARGET ] NOT MATCHED [ AND <search_condition> ]
THEN INSERT [ (column_list) ]
{ VALUES (values_list) | DEFAULT VALUES }
| WHEN SOURCE NOT MATCHED [ AND <search_condition> ]
THEN { UPDATE SET <set_clause> | DELETE }
}
Contoh penggunaan MERGE untuk sinkronisasi data
CREATE TABLE Original (o_pk INT PRIMARY KEY, o_name VARCHAR(10), o_number INT);
CREATE TABLE Replica (r_pk INT PRIMARY KEY, r_name VARCHAR(10), r_number INT);
CREATE PROCEDURE usp_SyncReplica AS
MERGE Replica
USING Original
ON o_pk = r_pk
WHEN MATCHED
AND (o_name != r_name OR o_number != r_number) THEN
-- Row exists but data differs
UPDATE SET r_name = o_name, r_number = o_number
WHEN SOURCE NOT MATCHED THEN
-- Row exists in Replica but not Original
DELETE
WHEN NOT MATCHED THEN
-- Row exists in Original but not Replica INSERT VALUES (o_pk, o_name, o_number)
OUTPUT $action, inserted.r_pk, deleted.r_pk;
Selamat Mencoba.
(KN-MvpSQL)