SQL Server 2005 : Menambahkan nilai default pada sebuah kolom

Sering kali karena diburu waktu, seorang admin database lupa memberikan nilai default pada sebuah kolom tabel.
Jika tabelnya sudah jadi, maka default bisa ditambahkan belakangan

Syntax SQL :

ALTER TABLE [NamaTabel]
ADD CONSTRAINT [Nama Default-Bebas]
DEFAULT [NilaiDefault] FOR [NamaField]

Contoh :

Jika saya memiliki tabel MK, dan saya ingin, agar field MKStatus memiliki nilai default ‘A’, kemudian aturan ini saya namakan DF_MKStatus (atau apapun yg anda inginkan) maka syntaxnya adalah sbb :

ALTER TABLE dbo.tMK
ADD CONSTRAINT DF_MKStatus
DEFAULT ‘A’ FOR MKStatus

Cara menambahkan primary key

Alter table dbo.tbmHrdPersonil
add primary key (pNIp)

Cara merubah kolom menjadi tidak boleh NULL

alter table dbo.tbmHrdPersonil
alter column pNIP varchar(18) NOT NULL

Perubahan struktur pada tabel besar MS SQL Server 2005

Manakala sebuah tabel berukuran besar (baik karena jumlah field yang banyak, maupun jumlah record yang jutaan), maka tabel tersebut akan sulit dirubah melalui SQL Server Management Studio.

Biasanya SQL Server Management Studio, akan mengalami time-out dan menghasilkan pesan error seperti ini :

SQLServer2005Error

‘xxx’ table
– Unable to delete relationship ‘FK_xxx_yyy’.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Could not drop constraint. See previous errors.

Hal ini akan lebih parah terjadi manakala banyak user yang sedang mengakses tabel tersebut, atau tabel tersebut berelasi dengan banyak tabel lain.

Maka solusinya adalah dengan merubah struktur tabel mempergunakan T-SQL seperti di bawah ini :

Untuk merubah field size dapat digunakan perintah sebagai berikut :

1. Merubah ukuran field (hanya angka yang dirubah)
ALTER TABLE tbtWisuda ALTER COLUMN tbtWisuda varchar (50)

2. Menambahkan default
alter table dbo.CustomerReport alter column rundate default getutcdate()

3. Menghapus constraint
alter table dbo.CustomerReport drop constraint df_CustomerReport_rundate

4. Menambahkan constraint
alter table dbo.CustomerReport add constraint df_CustomerReport_rundate default getutcdate() for rundate

5. Menambahkan foreign key
ALTER TABLE tbtTagihBankFlat ADD CONSTRAINT FK_TagihanFlatMataUang FOREIGN KEY (tbfMataUang) REFERENCES tbrMataUang(tbrMUKode)

6. Hapus foreign key
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders

6. Menambah contrain unique
ALTER TABLE

ADD CONSTRAINT UNIQUE()

7. Not Null
ALTER TABLE

ALTER COLUMN NOT NULL

8. Memberikan hak akses
GRANT { SELECT | EXECUTE } ON [ sys.]system_object TO principal

9. Menambah field
ALTER TABLE [Table Name] ADD [Field Name] [Data Type]

Melakukan detach pada Microsoft SQL Server 2005 dengan Management Studio

Pada MS SQL Server 2005 terdapat proses attach dan detach. Proses attach berarti menaikkan file database agar online, sedangkan detach adalah proses menurunkan file database agar offline.

Proses detach biasanya digunakan manakala file database akan dipindahkan atau file log-nya akan dihapus.

Masalahnya adalah : selama masih ada user yang mengakses, proses detach tak bisa dilakukan.

Detach_Menu

Nah solusinya bagaimana ?

Continue reading Melakukan detach pada Microsoft SQL Server 2005 dengan Management Studio

Menyelesaikan Error 468 (Collation Conflict) pada SQL Server 2005

Pernah mendapat pesan error (error message) seperti ini ketika bekerja dengan MS SQL Server ?

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Waduh…. gimana nih solusinya, padahal tipe data nya sama koq ….
Untuk menjawabnya, mari kita pelajari ilmu pendukungnya terlebih dahulu.

Apa itu collation ?
(Untuk membaca selengkapnya, click link READ FULL ARTICLE di samping kanan)
Continue reading Menyelesaikan Error 468 (Collation Conflict) pada SQL Server 2005

Mengetahui versi MS SQL Server

Untuk dapat melakukan mirroring database pada MS SQL Server, maka server principal (utama) dan server mirror harus menjalankan Edisi SQL Server yang sama, baik itu Standard maupun Enterprise.

The principal and mirror server instances must be running the same edition of SQL Server—either Standard or Enterprise

Pertanyaannya, bagaimana saya tahu versi, edisi, dan service pack yang digunakan ?

Pergunakan Query sebagai berikut :

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

Tinggalah pusing, menghadapi dua server yang berbeda….

SQL SERVER – DISABLE dan ENABLE user SA

Beberapa waktu yang lalu seorang rekan bertanya, “Bagaimana saya bisa mempreteli hak permission dari user SA ?”

Selidik punya selidik, rupanya rekan ini ingin menjadi user SA menjadi hanya memiliki hak akses guest. Mungkin sebagai tipuan untuk para hacker

Sayang merubah permission dari user SA tidak dimungkinkan. Walaupun demikian SA dapat di disable atau di enable menggunakan script berikut ini. Hanya pastikan anda logged menggunakan account windows authentication.

/* Disable SA Login */
ALTER LOGIN [sa] DISABLE
GO
/* Enable SA Login */
ALTER LOGIN [sa] ENABLE
GO

SQL SERVER – Transfer Logins dan Passwords Antar Instances SQL Server 2005

Setelah mendengar tim infrastruktur mau mindahin server database ke hardware dan sistem operasi yang lebih baik (Henky bilang mau migrasi ke 64 bit, hi hi hi), akhirnya saya mikirin juga cara mindahin login user dan password antar instances SQL Server yang berbeda.

Beruntung Microsoft punya dokumentasi yang baik untuk hal itu di sini.

Copy script yang ada pada windows

scrip ini akan menghasilkan sebuah file

Kemudian copy script yang tercipta dan jalankan pada SQL Server yang lain di mana, anda ingin memindahkan username dan password. Pastikan saja bahwa anda login dengan role sysadmin

Phew….. one more job is (almost) done.

Memberikan izin (permission) pada SQL Server 2005

Mari berkhayal sejenak, bahwa anda adalah seorang DBA pada sistem akademik UNPAD mempergunakan SQL Server 2000. Anda datang ke kantor di pagi hari, membuat minuman hangat dan mencek email. Ada dua permintaan dari rekan sejawat dalam inbox email anda. Ahmad Baehaqi dari bagian registrasi akademik membutuhkan akses read-only terhadap seluruh data akademik sehingga beliau bisa memprediksi volume mahasiswa untuk angkatan baru, dan Marin dari web-development punya masalah dengan aplikasi web akademik dan membutuhkan akses untuk menelusuri profiler untuk mengidentifikasi masalahnya. Terlihat suatu pekerjaan mudah bukan — benarkah ?

Dalam kasus pertama, data akademik tersebar dalam 98 tabel di antara 184 tabel pada database akademik. Anda punya peran yang telah didefinisikan untuk team akademik yang memberikan hak SELECT dan UPDATE, tapi Ahmad Baehaqi hanya membutuhkan akses read-only. Hal ini memberikan anda satu opsi : memberikan ahmad baehaqi akses SELECT kepada setiap tabel, satu demi satu. Halah…….

Bukankah sindrom melakukan seting mulai terasa ?

Sedangkan permintaan untuk mengakses penelusuran profiler tidak bisa diberikan. Dalam SQL Server 2000, akses untuk menjalankan penelusuran hanya diberikan kepada anggota dari System Administrator (sysadmin) yang merupakan peran tetap. Memberikan hak ini kepada marin akan memberikan juga seluruh akses kepada server – bukan ide yang baik bukan ? Hal ini memberikan anda satu-satunya pilihan : Bekerja sama dengan Marin dan melakukan penelusuran sendiri, menambah beban kepada jadwal anda yang sudah ketat.

Halah…. (untuk kedua kalinya)

Kedua skenario ini masih asing bagi DBA Sql server 2000, tapi jangan khawatir, solusi dari SQL Server 2005 tidak hanya memberikan kemajuan programatic yang beragam, tapi juga mendukung kemajuan beberapa security model secara bersamaan. Solusi untuk kedua masalah ini adalah : pemisahan user-schema dan izin server yang bersifat granular.

Pemisahan User Schema

SQL Server 2005 menambahkan dukungan terhadap schema seperti yang dijelaskan dalam ANSI SQL-92. Anggap schema sebagai wadah logis untuk database objek. Wadah ini menggantikan cara object-owner yang digunakan oleh banyak DBA pada versi SQL server yang lalu. Dalam SQL Server 2005, objek tidak lagi memiliki owner. Sebagai gantinya, objek merupakan anggota dari sebuah schema, dan diakses dengan nama skema.

Mari kita bandingkan. Dalam versi SQL server terdahulu, di mana sistem penamaan tiga bagian diterapkan :

database_name.owner_name.object_name

Dalam SQL Server 2005, sistem seperti ini yang digunakan.

database_name.schema_name.object_name

Dalam SQL Server 2005, alih-alih memberikan permission kepada objek secara langsung, DBA dapat memilih untuk memberikan permission kepada schema. Memberikan permission kepada sebuah schema mempropaganda permission terhadap setiap objek yang dikandung oleh schema tersebut dan setiap objek yang ditambahkan kepada schema tersebut di kemudian hari. Hal ini berarti bahwa DBA tidak perlu mengupdate permission untuk setiap user setiap kali sebuah objek baru ditambahkan.

Pemisahan antara user dan objek ini memiliki keuntungan sebagai berikut :
1. karena objek tidak lagi dimiliki oleh user, maka user dapat dihapus tanpa menghapus atau memetakan kembali objek yang dimiliki oleh mereka. Hal ini menghindarkan anda dari sakit kepala ketika seorang pegawai keluar, misalnya.

2. Objek dalam database yang sama dapat dikelompokkan secara logis berdasarkan fungsi bisnis. Sebagai contoh, anda mungkin dapat mengisi database client-management dengan informasi client-contact dan data penjualan. Tabel yang membuat masing-masing fungsi bersifat independen pada sebagian besar database. Dalam versi SQL Server sebelumnya, anda mungkin perlu membuat dua database. Hal tersebut mengelompokkan objek secara logis, sekaligus membuat relasi dengan constraint foreign key tidak mungkin, yang konsekwensinya adalah membuat pembuatan referential integrity menjadi sulit.

Schema dibuat menggunakan statemnt CREATE SCHEMA T-SQL. Sebagai contoh sebuah schema yang bernama sakademik dapat dibuat dengan perintah T-SQL sebagai berikut

CREATE SCHEMA sAkademik

Ketika sebuah schema berhasil dibuat, anda dapat membuat objek di dalamnya dengan menambahkan prefix pada sebuah objek dengan nama schema. Sebagai contoh, untuk membuat tabel dalam schema sAkademik yang bernama tMahaciwa, maka perintah T-SQL berikut dapat digunakan :

CREATE TABLE sAkademik.tMahaciwa
(
NPM INT NOT NULL PRIMARY KEY,
Nama Varchar(20) NOT NULL
)

Objek juga dapat ditransfer diantara schema menggunakan opsi TRANSFER atau statement ALTER SCHEMA. Objek tabel tMahaciwa dapat dipindahkan ke sebuah schema bernama sSkemaLain menggunakan perintah T-SQL sebagai berikut :

CREATE SCHEMA sSkemaLain
GO
ALTER SCHEMA sSkemaLain TRANSFER akademik.tMahaciwa
GO

Ketika sebuah schema telah dibuat dan diisi, maka user dapat diberikan permission untuk setiap objek di dalam schema sekaligus. Sebagai contoh, untuk memberikan akses SELECT kepada user ahmad baehaqi ke setiap objek dalam schema sAkademik maka perintah T-SQL berikut ini dapat digunakan :

GRANT SELECT ON sAkademik TO AhmadBaehaqi

Cara ini jelas lebih efisien dibanding dengan metode ekivalennya di versi SQL server yang lalu, yaitu memberikan permission untuk setiap objek satu demi satu.

Fitur Baru : Granular server permissions

Menggunakan fitur pemisahan user schema membuat pemberian permission kepada objek database secara masal menjadi mudah. Lalu bagaimana memberikan permission pada level server ?

SQL server datang bersama peran (role), yaitu kumpulan permission terhadap bermacam-macam sumber daya yang ada pada server.

Jika anda ingin memberikan permission pada sebuah login untuk membuat, merubah, atau menghapus database, maka anda dapat membuat login tersebut menjadi anggota dari server role “dbcreator”. Tapi jika anda ingin memberikan akses hanya untuk membuat database, maka anda tidak dapat melakukannya pada versi SQL Server sebelumnya. Pada SQL Server 2005, role dibagi lagi menjadi komponen yang lebih detail yang dapat diberikan dengan statement GRANT. Sebagai contoh, untuk memberikan login yang bernama AhmadBaehaqi, hak akses untuk membuat database pada server, maka perintah T-SQL ini dapat digunakan

GRANT CREATE ANY DATABASE TO AhmadBaehaqi

Terdapat banyak permission pada level server yang tersedia. Sejauh ini permission yang paling menarik adalah permission untuk menjalankan penelusuran (traces). Masih ingat kebutuhan marin untuk melakukan penelusuran pada server ? Dalam SQL Server 2005, hal ini dapat dilakukan dengan perintah T-SQL

GRANT ALTER TRACE TO John

Fitur ini saja sudah dapat memberikan senyuman pada kebanyakan DBA. Tidak perlu lagi menambahkan developers kepada role system administrator hanya agar mereka dapat melakukan penelusuran — dan tidak perlu lagi bekerja untuk mereka karena mereka tidak perlu ditambahkan ke role tersebut untuk memulai pekerjaannya.

Fungsi Baru : Melihat permission yang dimiliki

Semua metode dan permission yang baru untuk memberikan permission dapat membingungkan. User mungkin tidak mengerti permission apa yang mereka miliki atau tidak miliki. Untungnya microsoft memberikan fungsi baru untuk masalah ini : fn_my_permission(). Fungsi ini memberikan informasi permission yang dimiliki oleh user yang sedang login.

fn_my_permission()memiliki dua parameter : target name dan target type yang dideskripsikan oleh nama. Sebagai contoh, jika target adalah sebuah schema bernama sAkademik, nama target adalah SCHEMA, maka perintah T-SQL berikut ini dapat digunakan untuk menghasilkan sebuah tabel permission

SELECT *
FROM fn_my_permissions(‘Sales’, ‘SCHEMA’)

Untuk pelatihan lebih jauh dan meminta informasi permission pada tabel tMahaciwa, perintah T-SQL ini dapat digunakan :

SELECT *
FROM fn_my_permissions(‘MySalesData’, ‘TABLE’)

Secara virtual, setiap objek dapat dispesifikasikan sebagai target. Untuk meminta permission pada level server, pergunakan NULL untukkedua parameter sebagai berikut :

SELECT *
FROM fn_my_permissions(NULL, NULL)

Live the easy life

Diterjemahkan dan diadaptasi dari sini.

SQL Server – Tips Mencetak ER Diagram

Pernah putus asa ketika akan mencetak Entity Relationship Diagram di SQL Server 2005 ? Lho emang susah ?

Ya, kalau databasenya sudah besar, sebuah diagram pasti ketika di print berhalaman-halaman. Kadang sulit untuk memperkirakan di mana terjadi perpindahan halaman tersebut. Jadi sulit mendapatkan hasil print-out yang terlihat baik.

Tapi masa harus re-engineering (pakai visio misalnya ?) hanya untuk mengontrol perpindahan halaman ? Halah…. capek deeee….

Pakai ini aja deh. Rahasinya cukup melakukan right-click pada diagram yang terbuka, kemudian pilih VIEW PAGE BREAK