SQL Server Integration Service Part 1 Posted by Unknown , Sunday, June 12, 2011 at 11:14 PM, in Labels: SSIS

Elemen yang membentuk Integration Service project::

1. Package: unit kerja yang didapatkan, dieksekusi, dan disimpan dalam msdb database atau XML File dengan eksensi .dstx
2. Control Flow : berisi task dan container
3. Data Flow :  Berisi sources, transformation, and destination
4. Variable : digunakan untuk melalukan operasi dinamis seperti update kolom dan mengontrol control flow berulang
5. Event Handler : menjalankan response untuk run time events untuk membantu monitoring dan merespon package

Lesson #8 Querying Metadata, XML, Full Text-Indexes Posted by Unknown , Thursday, June 9, 2011 at 4:04 AM, in Labels: Query

Metadata : informasi deskriptif tentang data atau database
Misalnya sebuah database customer information dimana  ada kolom untuk nama customer dan end user akan melihat nilai dari kolom tersebur disetiap row-nya adalah nama customer.

• Systems Catalog View: untuk meyederhanakan dan akses langsung ke metadata.
• Information Schema views :untuk menyediakan internal, system-table-independent view dari metadata, Information schema sesuai standar dan dapat digunakan untuk aplikasi
• Dynamic Management Views and Function : mekanisme untuk melihat SQL server bekerja. Menyediakan metode untuk memonitoring state dan kesehatan dari SQL Server
• Stored Procedure : cara untuk mendapatkan informasi metadata tanpa harus mengerti terlalu dalam tentang strukturnya

XML : open standar yang direkomendasikan w3c untuk menciptakan custom markup language

5 metode yang didukung oleh XML data type:

1. Query : menerima Xquery Statement meretrieve element dari XML data
2. Value : meretrieve value dari SQL Type dari sebuah XML Instance
3. Exist  : menentukan jika sebuah Xquery statement mengembalikan hasil
4. Modify : digunakan untuk menentukan XML data modification update statement
5. Nodes : membagi XML ke berbagai row

Lesson #7 Modifying Data in Table Posted by Unknown , Wednesday, June 8, 2011 at 10:02 PM, in Labels: Query

Terdapat 4 operasi saat ingin memodifikasi data dalam  table:

1. INSERT
2. DELETE
3. UPDATE
4. Overview Transaction

INSERT:

 Statement Insert dapat digunakan bersama-sama dengan SELECT (dengan menambahkan kata INTO setelah kata INSERT ), EXCUTE, dan TOP  (dengan menambahkan kata INTO setelah kata TOP dan sertai dengan statement SELECT)

INSERT dan OUTPUT clause
Penggunaan OUTPUT dalam sebuah INSERT statement  akan mengembalikan informasi dari setiap baris yang  berubah akibat INSERT statement

DELETE
Statement Delete dapat digunakan bersama WHERE clause, Subquery, TOP

TRUNCATE TABLE
Menghapus seluruh baris dari table harus melakukan penghapusan secara individual . Truncate Table tidak dapat digunakan pada table yang direferensikan oleh Foreign Key, berpartipasi dalam indexed view, dipublikasikan menggunakan transactional replication atau merge replication. Jika tidak dapat di hapus menggunakan truncate gunakanlah DELETE statement.

Truncate memiliki kelebihan dibandingkan DELETE yaitu:

1. Menggunakan transaction log lebih sedikit
2. Tak ada yang tersisa di table

DELETE and the OUTPUT clause

Penggunaan OUTPUT dalam sebuah DELETE statement akan menghapus baris dari sebuah table dan mengembalikan nilai yang di hapus ke sebuah result set.

UPDATE

Statement UPDATE terdiri dari 2 clause utama :

1. SET : termasuk didalamnya list kolom yang dipisahkan menggunakan tanda koma
2. FROM : mengidentifikasi table atau view yang mengisi value untu ekspresi pada SET clause

UPDATE and the OUTPUT clause

Penggunaan OUTPUT dalam sebuah UPDATE statement akan mengupdate baris dari sebuah table dan mengembalikan nilai yang di hapus ke sebuah result set.

TRANSACTION

adalah urutan operasi yang dtunjukan sebagai single logical unit of work. Terdiri:

1. Atomic : merupakan atomic unit of work
2. Consistency : harus meninggalkan semua dalam  consistent state
3. Isolation : harus terisolasi dari modifikasi yang dibuat dari concurrent transaction lainnya
4. Durability : Tahan setelah kegagalan sistem

Macam - macam Basic Transaction Statement :

1. BEGIN TRANSACTION
2. COMMIT TRANSACTION
3. ROLLBACK TRANSACTION

BEGIN TRANSACTION 

Menampilkan titik dimana data direferensikan oleh sebuah koneksi yang konsisten secara logikal dah fisikal> Jika terjadi error maka semua modifikasi data yang dibuat setelah BEGIN TRANSACTION dapat di rool back untuk mengembalikan data ke state consitency yang diketahui

COMMIT TRANSACTION

diterbitkan untuk membuat modifikasi menjadi permanent di database 

ROLLBACK TRANSACTION

menghapus seluruh modifikasi jika mengalami error saat modifikasi


TRANSACTION ISOLATION Level Control
Mengunci pengambilan data ketika data sedang di read, seberapa lama read locks ,bagaimana read operation mereferensikan rows acts

1. READ UNCOMMITED : Statement dapat membaca row yang telah dimodifikasi oleh transaksi lain namun belum di-COMMIT
2. READ COMMITED : Statement tidak dapat membaca data karena modifikasi belum di-COMMIT
3. REPEATABLE READ : Statetment tidak dapat membaca data karena belum di-COMMIT oleh transaction lain dan tidak ada transaction yang dapat memodifikasi data yang telah dibaca oleh current transaction sampai current transaction selesai
4. SNAPSHOT : Data dibaca oleh statement dalam sebuah transaksi yang akan menjadi trasactionally consistent version dari data yang telah ada  diawal transaction
5. SERIALIZABLE : mengunci semua table dalam seluruh select statement dalam sebuah transaction

Lesson #6 Working With SubQueries Posted by Unknown , at 9:21 PM, in Labels: Query

subquery vs join :

1. join menujukkan performa lebih cepat dari subquery dalam beberapa kasus namun eksistensi harus dicek
2. subquery lebih berguna untuk menjawab pertanyaan yang terlalu kompleks dijawab dengan join
3. subquery lebih cocok jika RAM kita tinggi karena terjadi adi memory
4. subquery lebih mudah di maintain daripada pada temporary table yang membutuhkan resources harddisk ketika dieksekusi
5. Gunakan temporary table untuk memecah sub query yang kompleks

contoh subquery vs join :

/* SELECT statement built using a subquery. */
SELECT Name
FROM Production.Product
WHERE ListPrice =
    (SELECT ListPrice
     FROM Production.Product
     WHERE Name = 'Chainring Bolts' )

/* SELECT statement built using a join that returns the same result
set. */
SELECT Prd1.Name
FROM Production.Product AS Prd1
     JOIN Production.Product AS Prd2
       ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.Name = 'Chainring Bolts';

contoh sub query dengan EXIST keyword :

SELECT Name
FROM Production.Product
WHERE EXISTS
    (SELECT *
     FROM Production.ProductSubcategory
     WHERE ProductSubcategoryID =
            Production.Product.ProductSubcategoryID
        AND Name = 'Wheels'); 

1. local temporary table : memiliki single number sign (#), muncul hanya pada koneksi yang sekarang digunakan user, dan terhapus ketika user diskoneksi
2. global temporary table :  memiliki double number sign (##), muncul pada seluruh user dan terhapus ketika diskoneksi

contoh create temporary table :

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);
INSERT INTO #MyTempTable VALUES (1);
SELECT * FROM #MyTempTable; 

CET : common expression table --> temporary result yang didapat dari select query dan dapat digunakan dalam select, insert, update, atau delete

keuntungan penggunaan CET adalah : query yang diturunkan dari table mudah dibaca dan menyediakan traversal hieraki recursive

contoh CET yang recursive :

USE AdventureWorks2008;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate,
ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID,
p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO 

contoh Store prosedure:



CREATE Procedure pGetPersonName
 @PersonID nvarchar(15)
AS
 SELECT BusinessEntityID,
   FirstName,
   MiddleName,
   LastName
  FROM dbo.vwPersonName
  WHERE BusinessEntityID = @PersonID
GO

Lesson #2: Summarizing Grouped Data Posted by Unknown , at 9:08 PM, in Labels: Query

1. Clause GROUP BY

digunakan untuk menggrupkan baris ke dalam result set. Digunakan untuk menjalankan fungsi agregat  agar dapat berkerja.

Contoh :

SELECT [kolom ID e.g : ID], SUM [kolom total] AS Subtotal FROM [table] GROUP BY [kolom ID e.g : ID] ORDER BY [kolom ID e.g : ID]

SELECT [kolom e.g : DATE] AS 'Year', SUM [kolom total e.g: TotalDue] AS 'Total Due Ammount' From [table]
GROUP BY  [kolom e.g : DATE]
HAVING  [kolom e.g : DATE]  >='2003'
ORDER BY  [kolom e.g : DATE]

ROLLUP : Membuat general summary dalam sebuah query. Menciptakan result set yang menunjukan agregat untuk hierarki value dalam kolom yang dipilih

Contoh :
SELECT a, b, c SUM () WHERE  () FROM [tabel] ROLLUP (a,b,c)


CUBE : Membuat general summary dalam sebuah query, Menciptakan result set  yang menunjukan hubungan agregat untuk seluruh kombinasi nilai dalam kolom yang dipilih.

Contoh :

SELECT a, b, c SUM () WHERE  () FROM [tabel] CUBE (a,b,c)

COMPUTE :  menciptakan total yang muncul sebagai tambahan summary collumn di akhir result sets. Fungsi tcompute menciptakan dua result sets untuk setiap grup. Result set pertama mengandung detail row dari select statement grup dan yang kedua adalah baris yang mengandung subtotal dari aggregat function yang dispesifikasi dalam compute clause.

Contoh :

SELECT a, b, c  FROM [tabel] ORDER BY a COMPUTE SUM(b), SUM(c)

COMPUTE BY : menciptakan keduanya baik detail (subtotal) dan summary (grand total) agar tercipta dengan sebuah select statement dan penngunaan compute by harus diikuti dengan ORDER BY

Contoh :

SELECT a, b, c  FROM [tabel] ORDER BY a COMPUTE SUM(b), SUM(c) ORDER BY  a

GROUP SETS : operator tambahan dalam GROUP BY clause. Operator ini akan menunjukan beberapa Group set dalam satu query 

Lesson #5 Joining Multiple Table Posted by Unknown , at 8:57 PM, in Labels: Query

INNER JOIN :
termasuk equal join atau natural join dan menggunakan operator perbandingan untuk mencocokan baris yang sama

(equal join : inner join yang mengembalikan baris yang sama atau setara dengan tabel yang di joinkan)

OUTER JOIN : termasuk didalamnya left, right, or full outer joins
Biasanya menampilkan sekurang-kurangnya satu table yang sebutkan selama memenuhi kondisei WHERE
dan HAVING.

Jika yang ditampilkan sebelah kanan disebut right outer join
Jika yang ditampilkan sebelah kiri disebut left outer join
jika keduaditampilkan disebut full outer join

CROSS JOIN : disebut juga cartesian product dapat digunakan untuk menjoin table apapun tanpa menggunakan
clause Where dapat menghasilkan hasil dalam jumlah besar. Ketika where di tambahkan maka akan berubah
menjadi inner join

SELF JOIN : join apapun yang mengarah ke tabel sendiri

NOT-EQUI-JOIN : jarang digunakan dan biasa digunakan untuk self join

Table translation : biasa digunakan sebagai penyederhanaan dan kustomisasi table oleh user.

UNION : mengkombinasikan hasil dari dua atau lebih query ke dalam 1 hasil tunggal yang termasuk di dalamnya
baris yang berasal dari seluruh query union.

EXCEPT : mengembalikan distinct values dari query disebelah kiri EXCEPT Operand yang dikembalikan dari query kanan.

INTERSECT : mengembalikan distinct values yang di kembalikan oleh kedua query baik dari sebelah kiri atau kanan dari INTERSECT Operand

Perbedaan EXCEPT dan INTERSECT hanya terdapat pada disebelah mana query ditempatkan

Lesson #1: Aggregate Function Posted by Unknown , Monday, June 6, 2011 at 1:13 AM, in Labels: Query

Fungsi agregat hanya dapat muncul di SELECT statement, COMPUTE/ COMPUTE BY dan HAVING Clause.

Contoh Fungsi agregat :

1. AVG
2. SUM
3. MIN
4. MAX
5. COUNT
6. GROUPING
7. VAR

Fungsi agregat biasanya tidak mempedulikan NULL namun pada waktu tertentu dapat mengsilkan result yang tak terduga, oleh karena itu gunakan fungsi ISNULL untuk menggantikan nilai NULL menjadi 0.

contoh:

SELECT AVG (ISNULL([kolom yang akan di-aveg], 0)) AS '[nama kolom]' FROM [table]

Perbedaan Count(kolom) dengan Count(*) adalah Count (kolom) akan menghitung seluruh records dengan mengacuhkan yang bernilai NULL sehingga lebih tepat digunakan Count(*) untuk dapat mendapat menghitung records dengan tepat

LATIHAN



Task 1: Create and execute a query to generate a report that lists the
average order quantity and sum of line total for each product with a
line total that exceeds $1000000.00, and with average quantity less
than 3
•  Create a query that returns the ProductID, average OrderQty and total of
LineTotal.
•  Use a GROUP BY to ensure the averages are computed for the correct column
•  Use a HAVING clause to restrict the query to run for cases where the total an
averages of LineTotal and OrderQty respectively are in the proper ranges.
•  Don’t be afraid to use functions in the HAVING clause.
•  Execute the query and review the results.

select ProductID, avg (OrderQty) as 'Order Qty', SUM(LineTotal)as 'Line Total'  from Sales.SalesOrderDetail
 group by CUBE (ProductID)
 having SUM (LineTotal) >= 1000000 and AVG (OrderQty) <= 3

Task 2: Create and execute a query to group the products and then
compute the sum of the quantity shelf-wise
•  Create a query that returns the ProductID Shelf and total Quantity for a group
of products.
•  Use a GROUP BY with the ROLLUP operator to calculate the columns in a
specific order.
•  Execute the query and review the results.

SELECT ProductID, Shelf, SUM(Quantity) AS 'QtySum'
FROM Production.ProductInventory
GROUP BY ROLLUP(ProductID, Shelf)

Task 3: Create and execute a query to generate a summary report
•  Create a query that summarizes sales information from the
SalesOrderDetail table using the CUBE operator.
•  Sum the number of orders.
•  Specify the CUBE operator with the correct columns from the table (orders are
related to products and the id of the SalesOrderDetail row.
•  Execute the query and review the results.

SELECT SalesOrderID, ProductID,
SUM(OrderQty) AS SumQuantity
FROM Sales.SalesOrderDetail
GROUP BY CUBE(SalesOrderID, ProductID)
ORDER BY SalesOrderID, ProductID 

Task 4: Distinguish the rows generated by the summary or
aggregations and actual table rows
•  Create a query that summarizes sales quotas and total YTD sales from the
SalesPerson table using the CUBE operator.
•  Use the GROUPING operator in tandem with the CUBE operator to indicate
the column not being aggregated.
•  Sum the SalesYTD column.
•  Execute the query and review the results.

SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD',
GROUPING(SalesQuota) AS 'Grouping'
FROM sales.SalesPerson
GROUP BY CUBE(SalesQuota) 

Lesson #4 Creating Crosstab Query Posted by Unknown , at 12:49 AM, in Labels: Query

PIVOT : merotasi tabel dengan mengubah unique value table dari 1 kolom  pada expression menjadi banyak kolom dan menunjukan agregasi. Pivot bekerja dengan mengambil nilai misalnya EmployeeID dan mentraslate mereka ke kolom biasa digunakan untuk memciptakan cross tab report.

 contoh :  SELECT * FROM [table] PIVOT (SUM ([sum kolom e.g :Qty]) FOR [kolom name e.g : Prod] IN ([header kolom baru1], [header kolom baru 2])) PVT

UNPIVOT : berlawanan dengan pivot dimana merotasikan kolom sari sebuah table-valued  expression menjadi kolom values

contoh :  contoh :  SELECT * FROM [table] UNPIVOT ([sum kolom e.g :Qty] FOR [kolom name e.g : Prod] IN ([header kolom lama1], [header kolom lama 2])) UnPVT

Lesson #3 :Ranked Group Data Posted by Unknown , Sunday, June 5, 2011 at 11:59 PM, in Labels: Query

Rangking adalah baris bernomor dari sekumpulan data dengan tipe rangking dan qualifier tertentu.

1. fungsi Rank :

Menambahkan rank berdasarkan nilai kuantitas di database. Misalnya list Top Ten customer berdasarkan jumlah pembelian

Contoh : SELECT [kolomName],  [kolomName] RANK () OVER (PARTITION BY [kolom pengelompokan] ORDER BY [kolom kategori]) AS [kolom rank] FROM [table] JOIN [kolom join table AB ] ON [kolom join table A ] = [kolom join table B ]

2. fungsi  DENSE RANK :
Menyediakan ranking terkategori tanpa ada gap apapun. Seperti RANK tetapi item yang sama dapat di tempat yang sama

Contoh : SELECT [kolomName],  [kolomName] DENSE_RANK () OVER (PARTITION BY [kolom pengelompokan] ORDER BY [kolom kategori]) AS [kolom rank] FROM [table] JOIN [kolom join table AB ] ON [kolom join table A ] = [kolom join table B ]

3. fungsi Row Number
Menyediakan kategori dan rank dalam 1 result set. Urutan dimana rank di assigned  ditentukan berdasarkan ORDER BY clause. Dapat digunakan dengan atau tanpa PARTITION. Jika menggunakan PARTITION, kolom yang digunakan adalah dimana ROW_SET kolom diaplikasikan. Misalnya  contact  pembeli dalam urutan custom.

Contoh : SELECT [kolomName],  [kolomName] ROW_NUMBER () OVER (PARTITION BY [kolom pengelompokan] ORDER BY [kolom kategori]) AS [kolom rank] FROM [table] JOIN [kolom join table AB ] ON [kolom join table A ] = [kolom join table B ]

4. fungsi NTILE
Menyediakan kemampuan mendistribusikan row pada partisi yang berurutan ke dalam grup yang ditentukan. Misalnya menentukan faktor yang digunakan untuk gruping atau pembagian tim sales berdasarkan sales figure. Biasa digunakan di datawarehousing. untuk setiap  row, NTILE akan mengembalikan nomor grup dimana row tersebut berada.

Contoh : SELECT [kolomName],  [kolomName] NTILE ([number of groups]) OVER (PARTITION BY [kolom pengelompokan] ORDER BY [kolom kategori]) AS [kolom rank] FROM [table] JOIN [kolom join table AB ] ON [kolom join table A ] = [kolom join table B ]