Welcome To My Humble Fortress Of Solitude

Penggunaan Cursor Yang Salah (Bagian 1)

Pendahuluan

Tulisan ini merupakan bagian dari beberapa tips tentang penggunaan cursor yang salah. Saya juga akan memberikan solusi bagaimana cara menulis ulang cursor sebagai simple SQL statement.

Kasus

Salah satu penggunaan cursor yang salah yang sering saya temui adalah menggunakan cursor untuk melakukan update terhadap suatu table menggunakan summary data dari table lainnya. Misalnya Anda punya table ProductEndOfDay dan table WarehouseTransaction. Table ProductEndOfDay merupakan table yang mencatat berapa quantity on hand untuk setiap product pada akhir transaksi setiap harinya. Sedangkan table WarehouseTransaction merupakan table yang mencatat setiap transaksi di warehouse. Anda ingin melakukan batch process untuk mencatat berapa posisi on hand setiap product setiap harinya.

Logika pengerjaannya adalah Anda akan mencatat berapa jumlah transaksi untuk setiap product di hari berjalan, dan kemudian akan menambahkan nilai tersebut dengan nilai on hand di hari kemarin, dan kemudian menyimpan hasilnya sebagai row baru di table ProductEndOfDay.

Misalkan kita akan menggunakan struktur table seperti dibawah ini:

CREATE TABLE ProductEndOfDay

(

    ProductEndOfDayId Int Identity Primary Key,

    ProductId Int,

    EndOfDayDate DateTime,

    QuantityOnHand Int

)

GO

CREATE TABLE WarehouseTransaction

(

    TransactionId Int Identity Primary Key,

    ProductId Int,

    TransactionDate DateTime,

    TransactionType Char(3),

    TransactionQuantity Int

)

TransactionType misalnya kita definsikan valuenya BUY untuk product yang diterima dan SLS untuk product yang dikeluarkan dari warehouse untuk penjualan. Nilai semua transaksi sesuai dengan jenis transaksinya, positif untuk BUY dan negatif untuk SLS.

Pada saat Anda melakukan update ke table ProductEndOfDay, Anda misalnya menggunakan cursor seperti code dibawah ini:

DECLARE @ProductId Int,

        @Quantity Int

DECLARE crEndOfDay CURSOR FOR

    SELECT    ProductId, SUM(TransactionQuantity)

    FROM    WarehouseTransaction

    WHERE    CONVERT(Varchar(10), TransactionDate, 112) = CONVERT(Varchar(10), GETDATE(), 112)

    GROUP BY ProductId

OPEN crEndOfDay

FETCH NEXT FROM crEndOfDay INTO @ProductId, @Quantity

WHILE @@FETCH_STATUS = 0

BEGIN

    INSERT INTO ProductEndOfDay(ProductId, EndOfDayDate, QuantityOnHand)

    SELECT    ProductId, DATEADD(dd, 1, GETDATE()) As EndOfDayDate, QuantityOnHand + @Quantity

    FROM    ProductEndOfDay

    WHERE    CONVERT(Varchar(10), EndOfDayDate, 112) = CONVERT(Varchar(10), DATEADD(dd, 1, GETDATE()), 112) AND

            ProductId = @ProductId

    FETCH NEXT FROM crEndOfDay INTO @ProductId, @Quantity

END

CLOSE crEndOfDay

DEALLOCATE crEndOfDay

Solusi

Sekarang mari kita tulis ulang code diatas tanpa menggunakan cursor. Inti dari proses diatas adalah mencari berapa total transaksi pada hari ini, kemudian mencari berapa QuantityOnHand di hari kemarin, dan menjumlahkan total transaksi hari ini dengan QuantityOnHand kemarin, kemudian disimpan ke table ProductEndOfDay

INSERT INTO ProductEndOfDay(ProductId, EndOfDayDate, QuantityOnHand)

SELECT    A.ProductId, B.EndOfDayDate, A.TodayQuantity + B.QuantityOnHand

FROM

        (

            SELECT    ProductId, SUM(TransactionQuantity) As TodayQuantity

            FROM    WarehouseTransaction

            WHERE    CONVERT(Varchar(10), TransactionDate, 112) = CONVERT(Varchar(10), GETDATE(), 112)

            GROUP BY ProductId

        ) As A JOIN

        (

            SELECT    ProductId, DATEADD(dd, 1, GETDATE()) As EndOfDayDate, QuantityOnHand

            FROM    ProductEndOfDay

            WHERE    CONVERT(Varchar(10), EndOfDayDate, 112) = CONVERT(Varchar(10), DATEADD(dd, 1, GETDATE()), 112)

        ) As B

ON        A.ProductId = B.ProductId

Kesimpulan

Setiap feature yang ada di database pasti bermanfaat jika digunakan dengan benar, termasuk cursor. Tidak ada yang salah dengan cursor, hanya kadang kala kita menggunakan cursor bukan buat hal-hal yang disarankan untuk dikerjakan menggunakan cursor.

For any comments, please put in this blog or you can email it directly to me at hendraep@yahoo.com.

Share this post: | | | |

T-SQL Trik (Query Data Tertentu Atau Semua Data)

Pernahkah Anda diharuskan membuat query untuk menampilkan data tertentu saja atau semua data? Berikut trik bagaimana menyelesaikan permasalahan tersebut dengan satu query saja J

Misalnya kita akan menampilkan data customer dari table Sales.Customer yang CustomerId-nya = 'Catherine'. Query berikut bisa menghasilkan data tersebut dengan benar

DECLARE     @Cust Varchar(50)

SET        @Cust = 'Catherine'

SELECT

        CustomerID,

        AccountNumber,

        FirstName,

        LastName

FROM        Sales.Customer As A JOIN Person.Person As B

ON        A.PersonID = B.BusinessEntityID

WHERE        FirstName = @Cust

GO

Selanjutnya ada permintaan agar query yang sama juga bisa menampilkan semua data apabila FirstName diisi 'All'. Trik yang digunakan disini sederhana. Tujuannya adalah untuk membuat expression yang ada pada WHERE clause bernilai True

DECLARE     @Cust Varchar(50)

SET        @Cust = 'All'

SELECT

        CustomerID,

        AccountNumber,

        FirstName,

        LastName

FROM        Sales.Customer As A JOIN Person.Person As B

ON        A.PersonID = B.BusinessEntityID

WHERE        FirstName = @Cust Or @Cust = 'All'

GO

For any comments, please put in this blog or you can email it directly to me at hendraep@yahoo.com.

Share this post: | | | |

All About Temporary Table

In SQL Server 2005, there are 3 types of temporary table: global temporary table, local temporary table, and table variable.

Global Temporary Table

1. Declare using ##

2. Visible to all users who had access to tempdb database

3. Lifetime until users that created a table is disconnected, and all other users finished query the temporary table

4. Stored in tempdb database

Use your SQL Server Management Studio to open a new query window. Then execute the following SQL statement  (for example in Management Studio had spid 55).

CREATE TABLE ##Temp1 ( Col1 Int, Col2 Varchar(50))
GO
INSERT INTO ##Temp1 VALUES (1, 'Row #1')

Open another query window and then execute the following query (spid 56)

SELECT * FROM ##Temp1

Watch your execution result, and verified that user with spid 56 can query a temporary table that created by user with spid 55.

Open another query window (spid 59) and execute the following query

SELECT * FROM ##Temp1

Verified that user with spid 59 also can query ##Temp1. Now execute the following query from user with spid 59

KILL 55
KILL 56

You had just terminated connection for user with spid 55 and 56. Now query the temporary table

SELECT * FROM ##Temp1

You will get the following error message:

Msg 208, Level 16, State 1, Line 1
Invalid object name '##Temp1'.

So after user 55 disconnected and no other users using ##Temp1, ##Temp1 automatically drop by SQL Server.

Local Temporary Table

1. Declare using #

2. Visible only to user who created it

3. Lifetime until user who created it disconnected

4. Stored in tempdb database

Go to query window with spid 55 and execute the following query. (Don't forget to reconnect first first) 

CREATE TABLE #Temp1 ( Col1 Int, Col2 Varchar(50))
GO
INSERT INTO #Temp1 VALUES (1, 'Row #1')

Go to query window with spid 56, and execute the following query

SELECT * FROM #Temp1

You will get the following error message:

Msg 208, Level 16, State 0, Line 1
Invalid object name '#Temp1'.

So, the local temporary table can only accessed by user with spid55. Now terminate spid 55's connection by execute the following query

KILL 55

Go to query window for user with spid 55 and run the following query

SELECT * FROM #Temp1

You will get the following error message.

Msg 208, Level 16, State 0, Line 1
Invalid object name '#Temp1'.

So after user with spid 55 disconnected, #Temp1 will be automatically drop by SQL Server.

Variable Table

1. Declare as variable

2. Visible only to a user who created it

3. Lifetime is one batch

4. Stored in memory

Go to query window with spid 55 and execute the following query

DECLARE @Temp1 TABLE ( Col1 Int, Col2 Varchar(50))
INSERT INTO @Temp1 VALUES (1, 'Row #1')
GO
SELECT * FROM @Temp1

Watch the error message:

(1 row(s) affected)
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@Temp1".

The error message telling that INSERT statement was success, but SELECT statement was failed. This is because the @Temp1 was access outside the batch that used to create the variable table.

My Opinion

In my opinion:

1. Never used global temporary table

2. Used local temporary table instead of variable table if you need a faster processing, but used variable table if you need a moderate hardware's resource

3. Watch out for new table data type in SQL Server 2008

Share this post: | | | |

Using User Defined Function As Check Constraint

Imagine you need to add some business rule that moderately complicated (I wonder wether moderately complicated is a correct word, :) ).

Suppose you have to check that each product that ordered by customer must have stock level after the order processed - at least as much as reorder level of that product .

CREATE TABLE Products

( ProductId Int, ProductName Varchar(255), ReorderLevel Int, CurrentStock Int )

GO

CREATE TABLE Orders

( OrderId Int, OrderDate DateTime, ProductId Int, Qty Int )

GO

INSERT INTO Products (ProductId, ProductName, ReorderLevel, CurrentStock)

SELECT ProductId, ProductName, ReorderLevel, CurrentStock

FROM AdventureWorks.Production.Products

Try to INSERT a row to table Orders

INSERT INTO Products (ProductId, ProductName, ReorderLevel, CurrentStock)

VALUES (1, 'Product #1', 50, 125)

Now, we will create a user defined function to check the stock level

CREATE FUNCTION fn_CheckStockLevel(@ProductId Int, @OrderQty Int)

RETURNS BIT

AS

BEGIN

DECLARE @Status Bit

SELECT @Status = CASE WHEN ReorderLevel >= CurrentStock -  @OrderQty Then 1 ELSE 0 END

FROM Products

WHERE ProductId = @ProductId

RETURN (@Status)

END

Now add a CHECK Constraint to Order Table

ALTER TABLE Orders

ADD CHECK (dbo.fn_CheckStockLevel(ProductId, Qty) = 1)

Finally, test the CHECK Constraint 

INSERT INTO Orders (OrderId, OrderDate, ProductId, Qty)

VALUES (1, GetDate(), 1, 100)

You got error message telling you that you cannot INSERT a row because violating a CHECK Constraint.

Share this post: | | | |

Constraint Vs Trigger

Have you ever forced to add a business rule to your tables? Well there are 2 ways to add business rule to a table in SQL Server 2005. Bear in mind to use a most effective way.

The first one is using a check constraint. You can add a check constraint to validate a simple rule, for example, make sure that a column at least have a 10-char length.

CREATE TABLE T1
( UserId Varchar(20) PRIMARY KEY,
  UserName Varchar(255) )

GO

ALTER TABLE T1
ADD CHECK (LEN(UserId) > 10 )

GO

INSERT INTO T1 (UserId, UserName)
VALUES ('ABCDE1234', 'User A')

You will get this message:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__T1__UserId__2D47B39A". The conflict occurred in database "AdventureWorks", table "dbo.T1", column 'UserId'.
The statement has been terminated.

The second one is using a trigger. I will recreate above rule using INSTEAD OF TRIGGER.

ALTER TABLE T1
DROP CONSTRAINT CK__T1__UserId__2D47B39A

GO

CREATE TRIGGER tr_1
ON T1
INSTEAD OF INSERT
AS
BEGIN

    DECLARE @Name VARCHAR(255)

    SELECT    @Name = UserName
    FROM    inserted

    IF LEN(@Name) > 10
        INSERT INTO T1 (UserId, UserName)
        SELECT * FROM inserted

END

GO

INSERT INTO T1 (UserId, UserName)
VALUES ('ABCDE1234', 'User A')

So keep in mind what kind of business rule would you apply to your table. Check constraint could be extended by using user defined function. I will explain about using user defined function in check constraint some other time.

Share this post: | | | |