SQL Server Geeks

Selamat tahun baru 2011...

Normalisasi dan denormalisasi sebuah plus dan minus

Normalisasi akan meningkatkan data integrity tetapi akan juga meningkatkan Query complexity dan sebaliknya Denormalisasi akan mengurangi data integrity dan juga mengurangi Query Compexity. Tujuan normalisasi adalah untuk membuat agar data yang ada tidak redundan dan memiliki data integrity yang kuat sehingga ketika kita melakukan relasi antara table akan dengan mudah kita menjaga dataintegrity dan mendapatkan datanya.

Normalisasi Table sendiri terbagi atas bentuk normal ke 1 sampai bentuk normal ke 4. lebih jelasnya baca tentang konsep RDBMS.

Bentuk Table yang tidak memenuhi bentuk normal (tidak dinormalisasi contohnya)

Table : DATAKARYAWAN (No, NAMAKARYAWAN, DEPARTEMEN, SEKSI, ALAMAT, KOTA)

Dalam menentukan normalisasi suatu database akan bergantung pada Functional Dipendency dari setiap Tuple (Field/Column) yang akan menentukan seberapa normal Satu Table.

Dari Gambaran diatas Table DATAKARYAWAN merupakan Table yang tidak dinormalisasi. Karena kita tidak melihat adanya Functional Dipendency Kalo kita lihat kemungkinan ketergantungan fungsinal (yang artinya tergantung pada) yang berlaku adalah :

No -> NAMAKARYAWAN,

DEPARTEMENDEPARTEMEN ->SEKSI

NAMAKARYAWAN -> ALAMAT

KOTA -> ALAMAT

Akibat kita melakukan design table yang tidak dinormalisasi seperti diatas kita kan mengalami kesulitan untuk membentuk suatu relasi antara table.

seperti pada contoh di bawah ini:

------------------------------------------------------------------------------------------------------ 

|No | NamaKaryawan | Departemen  | Seksi                   |  Alamat    | Kota     |

------------------------------------------------------------------------------------------------------ 

|1.  | A. Hanif              | IT                   | Application Dev  |Cideng      | Jakarta |

|2.  |Kusmawanti        |IT                    |Application Dev    |Cideng     |Jakarta   |

|3.  |Boboy                  |Sales               |Telesales             |Ancol        |Bekasi   |

------------------------------------------------------------------------------------------------------

Jika boboy kita hapus dari Table Kita maka Data departemen Sales juga akan terhapus,  berikut juga informasi Kota bekasi (Integritas data Terganggu). Padahal Departemen sales tetap ada meskipun tidak memiliki karyawan demikian juga dengan kota bekasi. Sehingga untuk kasus ini perlu di normalisasi menjadi table berikut.

Table: karyawan(NoKaryawan, Namakaryawan, KodeDepartemen, KodeSeksi, Alamat, KodeKota)

Table: Departemen(KodeDepartemen, NamaDepartement)

Table: Seksi(KodeDepartemen, KodeSeksi, NamaSeksi)

Table: Kota(KodeKota, NamaKota)

Setiap ketergantungan fungsional pada colum di suatu table pada implementasinya akan memungkinkan column tersebut dinominasilkan menjadi KEY baik itu Primary Key maupun secondary Key, dan dari sinilah konsep penentuan Key dan Index dimulai pada tahapan design. Yang pada akhirnya akan mementukan waktu akses yang diperlukan untuk mendapatkan suatu informasi dari table yang kita design. sini kita baru mulai menentukan desain database yang mempertimbangkan performance.

kapan denormalisasi dilakukan? Biasanya Untuk OLAP dan DataWarehouse Pendekatan designnya berbeda dengan OLTP sebagian besar table dibuat denormalized untuk lebih meningkatkan performace. Desaign yang dibuat menggunakan Star schema atau snowflake Schema.

http://sqlserver-indo.net/blogs/kiki/archive/2009/12/23/must-know-olap-v-s-oltp.aspx

SQL Server Indo Blog competition 2010

Hi All,

SQL Server 2008 R2 yang merupakan rilis terbaru SQL Server telah tersedia sejak beberapa waktu dengan banyak fitur baru yang sangat menarik. Untuk menyambut SQL Server 2008 R2, kami mengadakan sebuah kompetisi berhadiah sebuah MSDN Subscription Ultimate Not For Resale senilai $11,899 untuk 3 orang pemenang!

Caranya:

• Unduh trial SQL Server 2008 R2 atau SQL Server 2008 R2 Express
• Instal di PC Anda
• Tulis fitur-fitur barunya di blog SQLServer-Indo dengan Tag SQL Server 2008 R2
• Dan raih kesempatan untuk mendapatkan MSDN Subscription Ultimate!

Kompetisi ini terbuka untuk umum dan berlangsung sampai 30 September 2010. Pemenang yang beruntung akan diumumkan pada 1 Oktober 2010, berdasarkan banyaknya point yang diraih.

Apabila Anda belum memiliki blog di SQLServer-Indo, segera daftar dengan cara mengirim e-mail kepada Kiki Rizki Noviandi di kiki.rizki.noviandi@live.com

Sistem penghitungan: 1 blog bernilai 2 point, blog dengan tambahan sample code akan mendapatkan 2 point tambahan. Keputusan dewan juri tidak dapat diganggu gugat.

Untuk mengetahui lebih detail mengenai MSDN Subscription, dapat melihat disini.

Table Value Parameter : cara pengiriman parameter data berupa Table dalam SQL Server 2008

create proc ins_data (@t table (a int)) as

(

--- Create Logic Script di sini

)

 

TVP (table value parameter) memiliki kelebihan sebagai berikut :

• Memungkinkan membuat tipe data untuk parameter SP berupa tipe data Tabel
• Bisa menjadi parameter input pada SP dan Function di SQL Server
• Scope dari TVP adalah di dalam SP/Function Body sehingga tidak dikenali di luar SP/Function lainnya di luar scope tersebut
• Lebih cepat dalam pemrosesan data dalam ukuran besar
• Memiliki karakteristik seperti Bulk Copy/ BCP dalam server

Berikut cara mengimplementasikan TVP di SQL Server 2008

1. Create TVP di dalam database yang kita inginkan

USE AdventureWorks
GO
CREATE TYPE EmployeeTableType AS TABLE
(EmpID INT, EmpName nvarchar(100), EmpEmail nvarchar(100))

2. Create Store procedure / Function yang menggunakan TVP sebagai parameter

USE AdventureWorks
GO
CREATE PROCEDURE NewEmployee(@EmployeeDetails EmployeeTableType READONLY)
As
BEGIN
  INSERT INTO dbo.Employee
  SELECT * FROM @EmployeeDetails
END

3.Untuk memanggil TVP dilakukan dengan cara mendeclarasikan variable bertipe TVP

use AdventureWorks
Go
DECLARE @NewEmployees EmployeeTableType

4. Isi data ke variabel TVP

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')

5. Panggil SP dan passing parameter TVP

EXECUTE NewEmployee @NewEmployee

 

selamat mencoba (KN)

Mengenal operasi UPSERT dengan perintah Merge

Saya yakin kita semua pernah memerlukan syncronisasi antara 2 table di SQL, secara umum perintah yang dilakukankan adalah UPSERT (update or Insert), biasanya untuk melakukan ini di perlukan beberapa perintah dan kondisi yang menyebabkan perngaruh yang cukup signifikan dalam logika pemrograman dan tentu saja proses compilasi sintax di SQL server. seperti pada Script perintah di bawah ini  

CREATE TABLE MyTable(pk INT PRIMARY KEY, name VARCHAR(10), number INT);

CREATE PROCEDURE Legacy_Upsert_1
 @pk INT, @name VARCHAR(10), @number INT
AS
 UPDATE MyTable SET name = @name, number = @number WHERE pk = @pk
 -- Row is updated even if all the values are correct

 IF (@@ROWCOUNT = 0)
  INSERT MyTable VALUES(@pk, @name, @number)
  -- If row did not exist, a second statement execution is required

CREATE PROCEDURE Legacy_Upsert_2
 @pk INT, @name VARCHAR(10), @number INT
AS
 -- Updating only if any column needs to be changed requires an extra statement
 IF EXISTS (SELECT 1 FROM MyTable WHERE pk = @pk)
  UPDATE MyTable
   SET name = @name, number = @number
   WHERE pk = @pk AND (name <> @name OR number <> @number)
 ELSE
  INSERT MyTable VALUES(@pk, @name, @number)

kita bisa lihat berapa perintah TSQL perlu kita tuliskan di SQL server untuk menghasilkan data yang jika data sudah ada di MyTable maka akan di update dan jika data belum ada maka akan di insert ke MyTable.

Alasan utama kenapa kita akan sangat terbantu dengan intruksi merge ini diantaranya :

• Pada proses OLTP sangat membantu dalam proses UP-SERT (Update or Insert), Biasanya di gunakan dalam proses merging (penggabungan data) jika row data ada di table target maka akan di update jika belum ada maka akan di insert
• Pada proses melakukan syncronisasi data dari 2 table, Proses Insert/Update/Delete pada table target berdasarkan perbedaan data dari Table Source
• Melakukan Tracking history dari SCD (slowly Changing Dimension pada Data Warehouse), Update Jika data di target berbeda dengan source, insert jika data adalah data baru
• Tracking inventory, seperti pada proses inventory operasi Insert Stock baru,  update existing stock, Delete jika Amount = 0

Dengan menggunakan Merge Statement ini kita bisa menggabungkan perintah DML (insert/update/delete) dengan hanya menggunakan 1 statement yaitu MERGE, operasinya sendiri merupakan hasil Join antara Table Source dengan table Target.

Berikut Syntax perintah Merge:

[ 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 }
}

Berikut contoh penggunaan perintah Merge

CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty > 0));
CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY,  Delta INT CHECK (Delta <> 0));

INSERT Stock VALUES('MSFT', 10), ('BOEING', 5);
INSERT Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);

MERGE Stock S
 USING Trades T
 ON S.Stock = T.Stock
 WHEN MATCHED AND (Qty + Delta = 0) THEN
  DELETE -- delete stock if entirely sold
 WHEN MATCHED THEN
   -- delete takes precedence on update
  UPDATE SET Qty += Delta
 WHEN NOT MATCHED THEN
  INSERT VALUES (Stock, Delta);
 -- BOEING is deleted, GE inserted, MSFT updated

Dari perintah diatas kita bisa lihat operasi Merge pada table Stock dan Trade, dimana jika nilai trades menyebabkan nilai di stock habis maka data di stock di hapus, jika di stock ada maka data akan di update dengan menambahkan nilai Qty dengan nilai Delta sedangkan jika belum ada maka akan di insert data stock baru.

Klausa penting dalam perintah Merge

• WHEN MATCH, Jika row data ada di Table Source dan Table Target ini sama juga dengan operasi "Source Inner Join Target". Maka Valid Actionnya adalah UPDATE atau DELETE
• WHEN TARGET NOT MATCH, jika row data di table source tidak ada di table target ini sama juga dengan operasi "Source Left Outer Join Target". maka Valid Actionnya adalah INSERT
• WHEN SOURCE NOT MATCH, jika row data di table Target tidak ada di table source ini sama juga dengan operasi "Source Right Outer Join Target". maka Valid Actionnya adalah UPDATE atau DELETE

Selamat mencoba

how well is my system performing? - DBCC SQLPERF

DBCC SQLPERF is one of the most important undocumented SQL Server performance tuning pearls that allows you to answer the question: "how well is my system performing". DBCC SQLPERF provides different kinds of statistical data which is gathered by the system at runtime. This data can be used to analyze and evaluate your system and pinpoint possible bottlenecks.

This article tries to provide a complete set of documentation; describing how to use DBCC SQLPERF, what information is provided through the different options, which shape the data is returned in and how to use/interpret the data.

DBCC SQLPERF can be called in two ways. The first way it will return a result set of statistical data, the shape and content of result set depends on the key value provided. The second way resets the statistics values (to zero) so you can start measuring again from that point onwards, until the values get reset they are accumulated since the start of the server.

Returning the gathered statistics/data:
DBCC SQLPERF(<key>) [WITH { [NO_INFOMSGS] , [TABLERESULTS] } ]

Reset the data structures used to gather the statistics/data to zero:
DBCC SQLPERF(<key>, clear) [WITH { [NO_INFOMSGS] } ]

The WITH clause helps you control the output format, there are two WITH clauses that can be applied:

• NO_INFOMSGS: which will suppress the "DBCC execution completed. If DBCC printed error messages, contact your system administrator." message

• TABLERESULTS: which will cause the output of the DBCC command to be in the form of a result set, so you can re-use it latter. The DBCC SQLPERF options always return a result set, so this option is already implicitly set. I prefer to still make it explicit, which is why you see me use it in my examples.

DBCC SQLPERF can be called with different <key> values; each key value returns content and shape of data. The allowed key values are described in the table below.

Description of the key values:

Supported SQL Server versions:

DBCC SQLPERF is only support by SQL Server 7.0 and later versions, before 7.0 the information provided by DBCC SQLPERF is only available through a DBCC extension DLL created by Microsoft Product Support Services, which laid the foundation of DBCC SQLPERF in its current format. In SQL Server 2005, most information that you can only get through DBCC SQLPERF in 2000, can be retrieved via the new dynamic management views.

It is time to secure your DB Environment

Mengapa perlu?

SQL server adalah merupakan database server yang didalamnya kita menyimpan informasi data berupa database object seperti table, view, store procedure, function trigger dll. Dalam berbagai kasus pengamanan database server kurang terperhatikan dan cendrung sangat sekali minim dilakukan

Sebagai best practice dari system keamanan suatu system kita perlu mengetahui konsep dasar dari system keaman system yaitu defence in depth yang terdiri dari komponen berikut ini:

• Policies, Procedure & Awareness
• Physical Security
• Perimeter security
• Internal Network security
• Host/Server Security
• Application Security
• Data Security

Untuk mengetahui adakah update yang tertinggal dan belum di apply gunakan MBSA sehingga kita mengetahui patch atau service pack apa saja yang belum terinstall

Matikan semua service yang tidak diperlukan seperti:

• MSSQLServer
• SQLServerAgent
• MSSQLServerADHelper
• Microsoft Search
• Microsoft DTC

Database Security Category

Pengamanan database server merupakan syarat mutlak bagi aplikasi yang digunakan untuk mengolah data production. Gambar berikut menunjukkan category security yang perlu dilakukan terhadap database server.

1. Pengamanan Network Pengamanan netowork ini bertujuan untuk mengamankan jaringan/network dari kemungkinan akses oleh orang yang tidak berhak dan bermaksud mengacaukan keamanan database server kita. Pengaman ini dapat berupa:

Restrict SQL to TCP/IP
Meliputi pengontrolan siapa saja yang bisa mengakses database server dengan menggunakan IP Secure Policy. Dimana hanya computer dengan IP terdaftar saja yang dapat mengakses database server. Dalam kasus web application maka hanya web server saja yang bisa akses database server. Akses dari computer lain yang tidak terdaftar akan di tolak

Hardening TCP/IP Stack
Proses ini dilakukan untuk memastikan TCP/IP yang digunakan secure dari kemungkinan gangguan keamanan

Restrict Port
Melakukan blocking terhadap semua port kecuali SQL Server port dan port-port yang diperlukan untuk authentication. Selain itu IP Sec harus di configure untuk membatasi akses hanya port SQL saja yang bisa di akses yaitu Port 1433 dan 1434.

2. Pengamanan System operasi

   Pengaman ini dilakukan guna memastikan bahwa data yang ada di database server dari sisi system operasi akan tersimpan dengan aman tanpa adanya gangguan terhadap keamanan data. Hal yang perlu dilakukan di level database server adalah:

• Configure SQL Server Service agar dijalankan diatas user account dengan hak aksess (Permission) yang seminim mungkin. Ini ditujukan agar jangan sampai sql service dijalankan oleh account dengan role administrator. Sehingga memiliki hak akses yang sangat tinggi.
• Delete atau disable user account yang tidak digunakan, misalnya account guest atau local account yang tidak ada hubungannya dengan applikasi yang menggunakan database maka harus dihapus atau di disable dari sql server.
• Secure  Authetication traffic, dengan cara melakukan konfigurasi windows agar dapat menggunakan windows Integrated authentication (NTLM)
• Secure File and directories, perlu diperhatikan *** tentang keamanan dalam penyimpanan data berupa file data dan directory di database server dengan cara: 
• pastikan bahwa sqlserver terinstall di SQL Service installation directories. 
• Pastikan bahwa everyone group user tidak memiliki hak akses guna mengkases sql server file (mdf file) 
• Pastikan bahwa registry key telah terkonfigurasi dengan Access Control List yang tepat. 
• Remove semua sharing folder yang tidak butuhkan 
• Remove password yang ada di log file dengan menggunakan tools KillPwd.exe 
• Remove semua tools, utilities dan SDK bahkan development tools sepertti Visual studio dari database server.

3. Login dan User Role

   Ada beberapa hal yang perlu menjadi catatan penting sebagai best practice dalam mengamankan SQL Server sebagai database server adalah sebagai berikut:

• Gunakan strong password untuk sa (system administrator) agar tidak mudah di tebak. Strong password ini bisanya terdiri dari kombinasi huruf dan angka dengan carakter khusus seperti (!@#$%^&*()) dengan panjang tidak boleh kurang dari 5 character. 
• Remove Guest Account dari user di SQL server 
• Remove BUILTIN\Administrator user dari SQL Sever 
• Jangan melakukan grand permission terhadap public role

SQL Authentication Best Practice

Untuk mengamankan akses terhadap database server kita, maka kita perlu pastikan mekanisme authentication untuk database diserver production kita memenuhi syarat sebagai berikut:  

1. Set Windows Authentication menggunakan windows only dan bukan Mixmode, dengan penjelasan sebagai berikut:
   • bahwa credential dari windows only tidak dipassing melalui network
   • security dengan menggunakan windows only akan mudah di manage
   • dengan window authentication memungkinkan credential bisa di deligate
   • menghilangkan keperluan menyimpan user id dan password di client (seperti penulisan user id dan password untuk connection string).
    
2. Secure Database object, hal ini perlu dilakukan guna memastikan bahwa setiap object database (Table, view, Store procedure, Function dan trigger) hanya diakses oleh user yang berhak saja. Khususnya jika dalam satu database server terdapat lebih dari 1 database yang sama sama digunakan sebagai data production. Adapun best practice untuk melakukan secure database objects adalah sebagai berikut :

• Remove sample database dari database server
• Restrict access terhadap store procedure, dengan cara :
• membuat SQL Login kemudian
• map login to database user
• Add database user kedalam user define DB Role kemudian grant permission terhadap DB Role Tersebut.
• Restrict cmdExec access hanya untuk sysadmin role

3. Gunakan View dan Store procedure, ini pelu dilakukan karena
1. SQL Query mungkin mengandung informasi confidential sehingga perlu di hide seperti misalnya:

• Nama dari database component (Nama field, nama table) 
• Relasi antara table serta process logic 
• Account name serta password

2. Gunakan Store procedure seintensif mungkin karena selain menyembunyikan logic yang ada. SP membuat proses eksekusi lebih cepat dibandingkan query biasa.
3. Sebaiknya view lebih banyak digunakan daripada mengakses table secara langsung. Ini ada kaitannya juga dengan kemanan dalam hal menyembunyikan informasi dan relasi dari table yang ada.
4. Gunakan security best practice khususnya untuk aplikasi yang jalan di atas web application (secure code)

Demikian secara singkat bagaimana kita mengamankan database yang ada di SQL Server kita dengan memanfaatkan best practice untuk mengamankan database server khususnya untuk data production (kn-sqlgeeks).

Must Know : OLAP v.s OLTP

SQL Server 2008 - Date time data type

Permasalahan date time menjadi masalah serius dalam pengelolaan database, sering kali kita mengalami masalah yang semestinya tidak terjadi karena adanya anomali data akibat proses operasi tipe data date time ini. permasalahan mulai timbul ketika kita memiliki kebutuhan seperti :

• Memisahkan date dengan Time, mengingat secara standar tipe data selalu datetime
• Range tanggal yang lebih luas
• Bisa sesuai dengan kebutuhan Time Zone
• Persisi sampai nano second
• dsb

akhir nya di SQL 2008 dilakukan inprovement dengan menyediakan 4 tipe data datetime yang baru yaitu

1. Date

• Date Only
• Range dari 0001-01-01 sampai dengan 9999-01-01 menggunakan kalender gregorian

2. Time (n)

• Time Only
• optional persisi sampai 100 nanoseconds

3. DateTimeOffset(n)

• UTC Datetime dapat mengadaptasi kebutuhan TimeZone
• optional persisi sampai 100 nanoseconds

4. DateTime2

• Ukuran Date Range yang cukup besar
• optional persisi sampai 100 nanoseconds

Berikut contoh dan perbedaan data datetime yang di tunjukkan dalam Script TSQL di SQL Server 2008

CREATE TABLE t1 (c1 DATE, c2 TIME(3),
    c3 DATETIME2(7) NOT NULL DEFAULT GETDATE(),
    c4 DATETIMEOFFSET CHECK
 (c4<CAST(GETDATE() AS DATETIMEOFFSET(0)))
);

INSERT INTO t1 VALUES ('0001-01-01', '23:59:59',
    '0001-12-21 23:59:59.1234567',
    '0001-10-21 23:59:59.1234567 -07:00');

INSERT INTO t1 VALUES ('9999-12-31', '23:59:59',
    '9999-12-31 23:59:59.1234567',
    '1111-10-21 23:59:59.1234567 -07:00');

SELECT c4,
    DATEPART(TZOFFSET, c4),
    DATEPART(ISO_WEEK, c4),
    DATEPART(MICROSECOND, c4) FROM t1;

copy paste TSQL Script di atas untuk bisa melihat perbedaannya, jangan lupa jalankan di SQL Server 2008

Alhamdulilah, I got my MVP award for the 4th time. thank to microsoft and Indonesia IT Community

 Dear Kiki Noviandi, 

Wrap up, Essential SQL Server 2008 Community event

Dear SQLServer-Indo Member, 

Last monday (March, 16 2008), Our SQL Server community (SQLServer-indo) hosted  Essential SQL Server 2008 Community event. this event was attending by 45 attendee from SQL Server member and IT pro that very interested on SQL Server 2008 new technology update.

Thank to our SQL community leader :

Hendra Eka (SQL Server MVP),
Rachmat Haryanto,
David Pakpahan,
Deni Kusdeni and
Hendrik

for spending their time to prepare and present their session for this event, hopefully we can endorse other member to more active in sqlserver-indo community event.

I want also to say thank to Indonesia DPE team (narendra and welly) and also for SQLPass for sponsoring this event and give good opportunity for our community to get more benefit from joining IT community.

and for the last, thank to all sqlserver indo member for  supporting our SQL Indonesia Community. hopefully we can share and get more good benefit for joining SQLServer-indo community.

Here is some documentation from this event
http://cid-65cab324bf3c89cf.skydrive.live.com/browse.aspx/Public/sql%20community%20event?uc=12&isFromRichUpload=1

Kiki Rizki Noviandi, MVP SQL Server
SQLServer-Indo Community Leader

Official website : http://SQLServer-indo.org | mailing list : sqlserver-indo@yahoogroups.com | Facebook Group : SQLServer-Indo

[Invitation] Essential SQL Server 2008 Community Event

Dear Friends,

Live ID For Our Web Site (SQLServer-Indo.org)

10 Things You Shouldn't Do with SQL Server (Data Access Developer "Don'ts")

The Admin role in SQL Server is designed to allow administrative privileges to accounts that LEGITIMATELY need it. Rarely is this ever your application account. For example, with an ASP.NET application, you should never add the ASP.NET worker process (ASPNET or NETWORK SERVICE) to the Admin role to enable a trusted connection (integrated security). This is just asking for certain disaster. In this example, the ASP.NET worker process was never intended to run under an account with administrative privileges on a SQL Server database; the ASP.NET account is intended as a low privilege  account.

The ASP.NET worker process is installed when you install the .NET Framework. If you are running the .NET Framework on Windows XP or Windows 2000 the ASP.NET worker process runs under the MachineName\ASPNET account. On Windows Server 2003 the ASP.NET Worker process runs under the NT Authority\Network Service account. By including this account in the administrative role you are opening yourself up to SQL injection attacks, among other things.

Instead of granting a low privilege account administrative privileges to ensure that your application can do everything it needs to, take the time to determine the needs of your application. Make all possible attempts to have all of your data access managed in stored procedures. This will enable you to grant EXECUTE privileges for the ASP.NET account (or other low privilege account) on the individual stored procedures. This will not only ensure your application can do all of the things it needs to, but it will also help to tighten the overall security of your application and database.

Following is an example of TSQL code to grant the ASP.NET account access to your database, and give it execute permissions to a stored procedure.

-- Windows 2000 / XP
-- Replace "MachineName" with your machine name
EXEC sp_grantlogin [MachineName\ASPNET]  
EXEC sp_grantdbaccess [MachineName\ASPNET], [Alias]
GRANT EXECUTE ON [ProcedureName] TO [Alias]
GO

-- Windows Server 2003
EXEC sp_grantlogin [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_grantdbaccess [NT AUTHORITY\NETWORK SERVICE]
GRANT EXECUTE ON [ProcedureName] TO [NT AUTHORITY\NETWORK SERVICE]
GO

9. @@IDENTITY vs. SCOPE_IDENTITY

This particular issue isn't so much about doing something right or wrong, it is about understanding your options so you choose the right one. Both @@IDENTITY and SCOPE_IDENTITY() return the last identity value (primary key) that was entered by your active session, but in different scenarios they can each return different values. When I say "active session" I am referring to the current activity you are engaging in. For example, if you can a stored procedure, that is what I am referring to as your active session. Each call to t a stored procedure (or user defined function, etc) is a session, unless the a stored procedure is nested in the stored procedure you are calling. In the case of a nested stored procedure or user defined method, while they are separate methods, they are part of the current session, but not part of the current scope. Your scope is limited to the method (stored procedure or user defined function) that you explicitly invoked. This is where the difference between @@IDENTITY and SCOPE_IDENTITY() comes in.

@@IDENTITY will return the last identity value entered into a table in your current session (this is limited to your session only, so you won't get identities entered by other users). While @@IDENTITY is limited to the current session, it is not limited to the current scope. In other words, if you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it. Now this isn't bad, as long as you ensure that things are done in the correct order. Where this can get ugly is when there is an application revision and a new trigger gets added that gets fired from your stored procedure. Your code didn't anticipate this new trigger, so you could now be getting an incorrect value back.

SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

Follow is some sample script that you can run to see the different value you get back before a trigger is added, and after.

/*In a test database, create a new table named TY*/
USE SomeTestDatabase
CREATE TABLE TABLE_A ( TABLE_A_id int IDENTITY(100,5)PRIMARY KEY, ItemValue varchar(20) NULL)
/*INSERT records into TABLE_A*/
INSERT TABLE_A VALUES ('Widget')
INSERT TABLE_A VALUES ('Boat')
INSERT TABLE_A VALUES ('Car')
GO

/*Create a new table named TABLE_B*/
CREATE TABLE TABLE_B ( TABLE_B_id int IDENTITY(1,1)PRIMARY KEY, Username varchar(20) NOT NULL)
/*INSERT records into TABLE_B*/
INSERT TABLE_B VALUES ('Doug')
INSERT TABLE_B VALUES ('Erika')
INSERT TABLE_B VALUES ('Lola')
GO

/*INSERT a record into TABLE_B*/
INSERT TABLE_B
VALUES ('Kali')

/*SELECT the data to see what values were returned by @@IDENTITY and SCOPE_IDENTITY()*/
SELECT * FROM TABLE_A
SELECT * FROM TABLE_B
SELECT @@Identity AS [@@Identity], SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO

/*Create the trigger that inserts a row in table TABLE_A when a row is inserted in table TABLE_B*/
CREATE TRIGGER TABLE_B_trig
ON TABLE_B
FOR INSERT AS
BEGIN
INSERT TABLE_A VALUES ('Airplane')
END
GO

/*Now INSERT a record into TABLE_B, which will cause the trigger to be fired*/
INSERT TABLE_B
VALUES ('Donny')

/*SELECT the data to see what values were returned by @@IDENTITY and SCOPE_IDENTITY() - they will be different values. SCOPE_IDENTITY() will return the identity from TABLE_A (the identity that you explicitly created), while @@IDENTITY will return the identity from TABLE_B (the triggered item).*/
SELECT * FROM TABLE_A
SELECT * FROM TABLE_B
SELECT @@Identity AS [@@Identity], SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO

8. Fetch Semi-static Data on Each Request of a Resource

Ah, performance. Ultimately that is what we are talking about here. If, in your application, you have some semi-static data (data that doesn't change very frequently), and you are making calls to your data store on each request of the resource, you are missing a huge opportunity to increase the performance of your application. Data that is semi-static (even if it is only static for a short amount of time) can be cached in the application to reduce the overhead associated with making database calls frequently.

There are a couple options for caching in your application.

1.      Cache API: The Cache API is your application-level cache. This is where you can put ANY object and define rules around how long the object stays in the cache. The size of the cache (e.g., how many items you can put in it) is dictated by the amount of RAM on the machine running the application. The nice thing about the Cache API is you can put any object into the cache, and pull it out later to reuse it, even complex objects. You can define a sliding expiration time as the life span of the item in the cache (e.g., keep it in the cache as long it has been used in the past 5-minutes, but kill it after 5-minutes of idle time). You can alternately define an absolute expiration time (keep the item in cache for 1-hour and then remove it, regardless of how much it gets used or not). You can also define a file dependency (e.g., keep the item in cache until this file gets updated). This works great for caching XML data, and invalidating the cache if the XML file changes.

2.      Output Caching: For data you want to cache, where you don't need access to the raw data again, you can cache the output of the data. This is output caching; caching only the rendered HTML, not the actual object that you used to create the HTML. This is also very easy to implement, as seen in the second example below.

Using the Cache API:

DataTable productsTable;
// Insert code here to get a table of Product data

//The following code puts the object in cache
Cache.Add(
  "ProductsTable",             //Name
  productsTable,               //Object to cache
  null,                        //CacheDependency
  DateTime.Now.AddSeconds(60), //Absolute Expiration
  TimeSpan.Zero,               //Sliding Expiration
  CacheItemPriority.High,      //Priority
  null                         //onRemoveCallback
);

//The following code can be used to get the object from cache
if(Cache["ProductsTable"] != null)
  productsTable = (DataTable)Cache["ProductsTable"];   
Using Output Caching:

<%-- Sets the cache to 60-seconds --%>
<%@ OutputCache Duration="60" VaryByParam="None" %>

<%-- Sets the cache to 60-seconds and creates a separate cached version of the page based on the "City" parameter --%>
<%@ OutputCache Duration="60" VaryByParam="City" %>

<%-- Sets the cache to 60-seconds and creates a separate cached version of the page for each Accept-Language header --%>
<%@ OutputCache Duration="60" VaryByParam="None" VaryByHeader="Accept-Language" %>     
7. Include SQL Data Manipulation Language in Application Code

Embedding SQL code in your application code is simply asking for trouble. Not only could you be opening yourself to a SQL Injection attack, you are also creating code that is harder to maintain than it should be. With hard coded SQL in your application code, any time you want to make even a slight change to the SQL code, you have to recompile the entire application.

For example, the following SQL in your application is the code equivalent of a near death experience.

string sql = "SELECT * FROM Users WHERE username='" +
  Username.Text +
  "' AND password= '" +
  Encrypt(Password.Text) +
  "'";

SqlCommand command = new SqlCommand (sql, connection); 
For a full explanation on what can happen with this kind of code, read Stop SQL Injection Attacks Before They Stop You by Paul Litwin.

Of course, a better solution that concatenated string (if you MUST have SQL syntax in your code) is to use parameterized queries. Here you can see the same query using parameters (which will aid in preventing SQL injection attacks).

string sql = "SELECT * FROM Users WHERE username=@Username AND password= @Password";

SqlCommand command = new SqlCommand (sql, connection);
command.Parameters.Add("@Username", SqlDbType.VarChar).Value = UserName.Text;
command.Parameters.Add("@Password", SqlDbType.VarChar).Value = Encrypt(Password.Text);

SqlCommand command = new SqlCommand (sql, connection); 
An even better solution is to use stored procedures so that your queries are stored in the database, where they are compiled and optimized, and can be modified without having to recompile your application code.

SqlCommand command = new SqlCommand ("Users_GetUser", connection);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@Username", SqlDbType.VarChar).Value = UserName.Text;
command.Parameters.Add("@Password", SqlDbType.VarChar).Value = Encrypt(Password.Text);

SqlCommand command = new SqlCommand (sql, connection); 
The motto to live by is "embeddedSql == death;"

6. Abuse SELECT *

Strangely enough it seems that there is an all too common habit to do lazy data fetching. That is, many of us are still using "SELECT * FROM..." when we are writing our data access queries. This is bad news. Apparently many developers are still writing queries like this because at design time there are only a few fields, or the amount of data is small, or some other excuse. But what happens when the scope grows, and a new field is added. An Image field to hold a 1024x768 picture of a user for example. Now every call that uses "SELECT * FROM..." is pulling back this very large image (potentially multiple megabytes). This has HUGE performance implications.

This is just lazy. My theory is, at design time you will know all of the queries you need to do, and can write explicit stored procedures to return ONLY the data you NEED for those queries - no exceptions. You should never have to use "SELECT * FROM..." again.

5. Create Stored Procedures without Exception Handling

Every day you write code (I hope). And every day you likely write some exception handling in your code because you know that something could go wrong. The input may not be what you expect, data may not be returned from a query, etc. Strangely enough, not enough of us are writing exception handling in our stored procedures. Is it that strange to think that nothing will ever go wrong in our stored procedures? So you're telling me that you just handle the exception in your application code? Why not handle the exception as close to the source as possible? That is my #5 recommendation.

Following is an example of one option for exception handling in a stored procedure. There are lots of methodologies you could use, and this is only one. In this example we rely on an XML file in the application that cross references predefined error codes with human readable, user friendly error messages. The error codes were defined by our application architect.

CREATE PROCEDURE dbo.Users_Insert
  @Username VARCHAR (20)
AS
SET NOCOUNT ON
DECLARE @Err INT
SET @Err = 0 – Success
INSERT Users (Username) VALUES (@Username)
SET @Err = @@ERROR – This resets @@ERROR to 0
IF (@Err <> 0)
  BEGIN
    IF (@Err = 547) -- statement conflicted with constraint
      BEGIN
        SET @Err = 32 -– Our error code indicating 'Username already in use'
        GOTO abort
      END
    ELSE
      BEGIN
        SET @Err = 1 –- Our error code indicating 'Unspecified error'
      END 
    END
abort:
SET NOCOUNT OFF
RETURN @Err
GO     
 

When the error code is returned to the application you can interrogate its value. If it is "0" then there was no error. If the value is "32" you cross reference that with the ErrorCodes.xml file to return the string value:

 "There was an error creating your user account. The user name you selected was already in use. Please choose a new user name and try again."

If the error code is "1", then you cross reference that with the ErrorCodes.xml file to return the string value:

 "An unspecified error occured. Please try again. If the problem persists, please contact Customer Care."

At the very least you should always include some form of exception handling on INSERT, UPDATE and DELETE statements.

An important note: As soon as you interrogate the @@ERROR value it gets reset to "0". It is critical that you copy the value of @@ERROR to a local variable to ensure you can work with the value without accidentally resetting it.

4. Prefix Stored Procedures with "sp_"

I am often reminded of my early learning with SQL Server when I stumble across this little gem. You see, when I was first learning SQL Server, I looked at how things were named and saw a common theme, stored procedures were named with the Hungarian notation "sp_". Much to my dismay I later learned that "sp_" is the notation for "System Stored Procedure" (why they didn't use "ssp_" I'll never know). I say I am often reminded of this because I see it in code all over the place. I once stumbled into hundreds of these guys in an internal application created and used by one of the largest software companies in the world (name excluded to protect the guilty).

Allow me to quote SQL Server Books Online to provide clarity here:

System Stored Procedures

Many of your administrative activities in Microsoft® SQL Server™ 2000 are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

1.      The stored procedure in the master database.
2.      The stored procedure based on any qualifiers provided (database name or owner).
3.      The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important  If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

3. You Don't Protect the Database Connection String

The database connection string is probably the single most important secret information that your application uses. You MUST protect it at all costs. In the past people just like me (OK, it was me)  told you that it was OK to store your connection string in the web.config file. Well I am here to tell you I was wrong (hey, it can happen to any of us). In the early days of ASP.NET (circa 2000) we thought that this was true - that storing the connection string in the web.config was OK. The reality is that this is a big security risk - the web.config file is an XML file - human readable - which means if someone (external or internal) gets a hold of it, you're in big trouble. All secrets are revealed.

New Rule: Storing the connection string in the web.config file unencrypted is too risky - store it in the web.config file encrypted.

Of course this brings up the next question, "Where do I store my encryption key?" The answer, "You don't - let Windows do it for you using the Data Protection API (DPAPI)."

Windows 2000, XP and 2003 all include the Win32 DPAPI. This is an unmanaged API that you can use to do strong encryption of information, while letting Windows manage the key storage. Simple as anything. There is one important fact however - anything encrypted using DPAPI can only be decrypted on the same machine it was encrypted on. That means if I encrypt something on Machine_1, I cannot decrypt it on Machine_2. This means DPAPI is not a good solution for encrypting values you will store in the database, but it is good for encrypting connections strings and storing them in the web.config. You simply need to encrypt the raw connection string on each machine you deploy to, storing the machine specific encrypted value in the web.config file on that machine.

The .NET Framework v1.x doesn't include a managed wrapper for the Win32 DPAPI. You will need to write your own wrapper. Fortunately the Patterns & Practices group at Microsoft has helped out by providing a complete tutorial that includes cut-n-paste code (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/secmod/html/secmod21.asp).

2. Accept All Input

In the book, "Writing Secure Code, Second Edition" (MSPress), Michael Howard writes "All Input is Evil" (in fact, that is the name of Chapter 10). That's it...as simple as it sounds...all input is evil. Treat any input from a user - TextBox, QueryString, Cookie - as suspect until you have validated it to ensure it's legitimacy.

The .NET Framework ships with a number of tools to validate user input, either at the client or on the server (or both).

·       ASP.NET includes five (5) input validation controls: RequiredFieldValidator, RegularExpressionValidator, CompareValidator, RangeValidator, CustomValidator, plus the ValidationSummary control.

·       Windows Forms controls expose the Validating event to perform validation tasks.
·       The System.Text.RegularExpressions.RegEx class exposes a powerful regular expression engine.
·       HttpUtility.HtmlEncode can be used to HTML encode text before echoing it to the screen (prevents scripting attacks).

·       ASP.NET v1.1 (2003) includes a ValidateRequest attribute (@ Page directive or Web.config) that prevents some malicious script from being submitted.

1. Access the Database from the Application with the "sa" Account

Honestly I was stunned to see this at the top of the list. I thought we had all learned by now that the "sa" account is an administrative only account. Apparently note, so let me get on the soap box one last time:

·       NEVER use the "sa" account to access a database programmatically.
·       Use one or more limited-privilege accounts for programmatic data access (see #1).
o       SELECT-only account for data retrieval.
o       EXECUTE-only account for stored procedures.
·       Not using "sa" reduces an attacker's ability to execute harmful commands or system procedures.
·       If you really, really need to use the "sa" account, create a new account named "essay" and see if that help get past the need to use "sa".

Hopefully this Top 10 list has been helpful. Again, this isn't the end all be all of do's and don'ts with SQL Server, but it is a list that was compiled by a number of developers working in client locations, and seeing this stuff happen on a daily basis. Learn from it, love it, leave it.

Menghapus data duplikat didalam table

Kita bisa gunakan cara dengan menampung semua informasi yang duplicate ke temporary data dengan terlebih dahulu menjumlahkan value yang ada, lalu kemudian data yang duplikat di table kita delete. Setelah itu data dari table temporary tadi dimasukkan ke dalam table, sehingga sekarang di table tidak terdapat informasi yang duplikat lagi.

Perlu diingat ketika kita menghapus sebuah row maka kita akan kehilangan informasi yang mungkin kita butuhkan. Jika column yang ada hanya berupa 3 column dan informasinya termasuk key field saja maka tidak akan ada masalah. Yang menjadi masalah adalah ketika ada field lain yang berpengaruh seperti pada contoh yang saya buat adalah field value ketika kita menghapus data yang duplikat tetapi nilai field valuenya berbeda kita akan kehilangan informasi dari field value tersebut.

Karenanya hendaknya informasi penting selain key tidak menjadi hilang dikarenakan key duplikat. Pada contoh dibawah saya menjumlahkan terlebih dahulu field value untuk setiap data yang duplikat dan disimpan ke temptable, untuk seterusnya dimasukkan kembali ke Mytable dengan nilai field value yang sudah di SUM.

Berikut Contoh Scrip untuk menghilangkan duplicate data (silahkan di copy paste di Query analyser)

-------------------------- Begin Script -----------------------------

--Create Table MyTable

CREATE TABLE [dbo].[MyTable](

[FieldA] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[FieldB] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[FieldC] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Value] [int] NULL

) ON [PRIMARY]

--Insert Data to Table

INSERT INTO [dbo].[MyTable] ([FieldA],[FieldB],[FieldC],[Value])

VALUES ('A1','B1','C1',10)

INSERT INTO [dbo].[MyTable] ([FieldA],[FieldB],[FieldC],[Value])

VALUES ('A2','B2','C2',20)

INSERT INTO [dbo].[MyTable] ([FieldA],[FieldB],[FieldC],[Value])

VALUES ('A3','B3','C3',30)

INSERT INTO [dbo].[MyTable] ([FieldA],[FieldB],[FieldC],[Value])

VALUES ('A3','B3','C3',40)

INSERT INTO [dbo].[MyTable] ([FieldA],[FieldB],[FieldC],[Value])

VALUES ('A4','B4','C4',50)

INSERT INTO [dbo].[MyTable] ([FieldA],[FieldB],[FieldC],[Value])

VALUES ('A4','B4','C4',60)

-- Display All MyTable Data

Select * from [MyTable]

--Copy All Duplicate Data To #TempTable Table and Sum the Duplicate value

Select FieldA,Fieldb,FieldC,Sum(Value) as Value into #TempTable

from [MyTable]

where cast(FieldA as nvarchar(5)) +

cast(FieldB as nvarchar(5)) +

cast(FieldC as nvarchar(5))

in (

select

cast(FieldA as nvarchar(5)) +

cast(FieldB as nvarchar(5)) +

cast(FieldC as nvarchar(5))

from [MyTable]

group by FieldA, FieldB, FieldC

having count(

cast(FieldA as nvarchar(5)) +

cast(FieldB as nvarchar(5)) +

cast(FieldC as nvarchar(5)))>1)

Group by FieldA,Fieldb,FieldC

--Display All temptable data

select * from #TempTable

--Delete All Duplicate data in MyTable

Delete from [MyTable]

where cast(FieldA as nvarchar(5)) +

cast(FieldB as nvarchar(5)) +

cast(FieldC as nvarchar(5))

in (

select distinct

cast(FieldA as nvarchar(5)) +

cast(FieldB as nvarchar(5)) +

cast(FieldC as nvarchar(5))

from [MyTable]

group by FieldA, FieldB, FieldC

having count(cast(FieldA as nvarchar(5)) +

cast(FieldB as nvarchar(5)) +

cast(FieldC as nvarchar(5)))>1)

--Insert Data from Temptable to MyTable

insert into [MyTable] Select FieldA,Fieldb,FieldC,Value from #TempTable

--Display All MyTable Data

select * from [MyTable]

-- Drop All Table

drop table [MyTable]

drop table #TempTable

------------------------ End Script ----------------------------