Fungsi vs. Stored Procedure di SQL Server

Fungsi dihitung nilai-nilai yang permanen dan tidak dapat melakukan perubahan lingkungan untuk SQL Server (yaitu tidak ada INSERT atau UPDATE laporan diperbolehkan).

Fungsi dapat digunakan inline dalam pernyataan SQL jika ia mengembalikan nilai skalar, atau dapat bergabung atas jika itu mengembalikan hasil yang ditetapkan.

Sebuah titik yang patut dicatat dari komentar, yang merangkum jawaban. Terima kasih untuk @Sean K Anderson:

Fungsi tindak komputer-ilmiah definisi dalam bahwa mereka HARUS mengembalikan nilai dan tidak dapat mengubah data yang mereka terima sebagai parameter (argumen). Fungsi-fungsi yang tidak diperbolehkan untuk mengubah apa pun, harus memiliki setidaknya satu parameter, dan mereka harus mengembalikan nilai. Disimpan procs tidak harus memiliki parameter, dapat mengubah objek database, dan tidak harus mengembalikan nilai.

Perbedaan antara SP dan UDF tercantum di bawah ini:

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

Fungsi dan prosedur yang tersimpan berfungsi memisahkan tujuan. Meskipun itu's bukan yang terbaik analogi, fungsi dapat dilihat secara harfiah sebagai fungsi lainnya anda'd gunakan dalam bahasa pemrograman apapun, tapi disimpan procs lebih seperti program individu atau batch script.

Fungsi biasanya memiliki output dan input opsional. Output yang kemudian dapat digunakan sebagai input untuk fungsi lain (SQL Server built-in seperti DATEDIFF, LEN, dll) atau sebagai predikat untuk Query SQL - misalnya, PILIH a, b, dbo.MyFunction(c) DARI tabel atau PILIH a, b, c DARI tabel di MANA a = dbo.MyFunc(c).

Disimpan procs digunakan untuk mengikat query SQL bersama-sama dalam suatu transaksi, dan antarmuka dengan dunia luar. Kerangka kerja seperti ADO.NET, dll. bisa't memanggil fungsi secara langsung, tetapi mereka dapat memanggil stored proc ini diatas.

Fungsi-fungsi yang tidak memiliki bahaya tersembunyi meskipun mereka dapat disalahgunakan dan menyebabkan agak menjijikkan masalah kinerja: pertimbangkan query ini:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Di mana MyFunction dinyatakan sebagai:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

Apa yang terjadi di sini adalah bahwa fungsi MyFunction disebut untuk setiap baris dalam tabel MyTable. Jika MyTable memiliki 1000 baris, maka yang's 1000 ad-hoc query terhadap database. Demikian pula, jika fungsi ini dipanggil saat yang ditentukan dalam kolom spec, maka fungsi akan dipanggil untuk setiap baris yang dikembalikan oleh PILIH.

Jadi anda perlu berhati-hati menulis fungsi. Jika anda MEMILIH dari meja di sebuah fungsi, anda perlu bertanya pada diri sendiri apakah itu dapat lebih baik dilakukan dengan BERGABUNG dalam induk disimpan proc atau beberapa lainnya SQL membangun (seperti KASUS ini ... KETIKA ... ELSE ... END).

Perbedaan antara disimpan prosedur dan user-defined functions:

• Prosedur yang tersimpan tidak dapat digunakan dalam pernyataan Select.
• Prosedur yang tersimpan dukungan Tangguhan Resolusi Nama.
• Disimpan prosedur yang umumnya digunakan untuk melakukan logika bisnis.
• Prosedur yang tersimpan dapat kembali setiap datatype.
• Prosedur yang tersimpan dapat menerima jumlah yang lebih besar dari parameter input dari user defined functions. Disimpan prosedur dapat memiliki hingga 21,000 parameter input.
• Disimpan prosedur yang dapat mengeksekusi SQL Dinamis.
• Prosedur yang tersimpan dukungan penanganan kesalahan.
• Non-deterministik fungsi yang dapat digunakan dalam prosedur yang tersimpan.

• User-defined functions dapat digunakan dalam pernyataan Select.
• User-defined functions tidak mendukung Tangguhan Resolusi Nama.
• User-defined functions umumnya digunakan untuk perhitungan.
• User-defined functions harus mengembalikan nilai.
• User-defined functions tidak bisa kembali Gambar.
• User-defined functions menerima jumlah yang lebih kecil dari parameter input dari prosedur yang tersimpan. UDFs dapat memiliki hingga 1,023 parameter input.
• Tabel sementara tidak dapat digunakan dalam user-defined functions.
• User-defined functions tidak dapat mengeksekusi SQL Dinamis.
• User-defined functions tidak mendukung penanganan kesalahan. RAISEERROR ATAU @@ERROR tidak diperbolehkan di UDFs.
• Non-deterministik fungsi yang tidak dapat digunakan dalam UDFs. Misalnya, GETDATE() tidak dapat digunakan dalam UDFs.

Menulis fungsi yang ditetapkan pengguna ketika anda ingin menghitung dan mengembalikan nilai untuk digunakan di lain pernyataan SQL; menulis prosedur yang disimpan ketika anda inginkan, bukan untuk kelompok a mungkin-kompleks set pernyataan SQL. Ini adalah dua cukup kasus penggunaan yang berbeda, setelah semua!

Perbedaan Mendasar

Fungsi harus mengembalikan suatu nilai tetapi dalam Prosedur yang Tersimpan itu adalah opsional( Prosedur dapat kembali nol atau n nilai-nilai).

Fungsi hanya dapat memiliki parameter input untuk itu sedangkan Prosedur dapat memiliki input/output parameter .

Fungsi mengambil satu masukan parameter ini adalah wajib, tetapi Prosedur yang Tersimpan dapat mengambil o untuk n masukan parameter..

Fungsi dapat dipanggil dari Prosedur, sedangkan Prosedur tidak dapat dipanggil dari Fungsi.

Muka Perbedaan

Prosedur ini memungkinkan PILIH serta DML(INSERT/UPDATE/DELETE) pernyataan di dalamnya sedangkan Fungsi memungkinkan hanya MEMILIH pernyataan di dalamnya.

Prosedur tidak bisa dimanfaatkan dalam sebuah pernyataan SELECT sedangkan Fungsi dapat tertanam dalam sebuah pernyataan SELECT.

Prosedur yang tersimpan tidak dapat digunakan dalam pernyataan SQL di mana saja di MANA/MEMILIKI/PILIH bagian sedangkan Fungsi dapat.

Fungsi yang mengembalikan tabel dapat diperlakukan sebagai satu rowset. Hal ini dapat digunakan dalam Bergabung dengan tabel lain.

Fungsi Inline dapat meskipun sebagai pandangan yang mengambil parameter dan dapat digunakan dalam Bergabung dan lain Rowset operasi.

Pengecualian dapat ditangani dengan try-catch blok dalam Prosedur sedangkan try-catch blok tidak dapat digunakan dalam sebuah Fungsi.

Kita bisa pergi untuk Manajemen Transaksi dalam Prosedur sedangkan kita bisa't pergi dalam Fungsi.

sumber

User Defined Function adalah alat penting yang tersedia untuk sql server programmer. Anda dapat menggunakannya inline dalam sebuah pernyataan SQL seperti begitu

SELECT a, lookupValue(b), c FROM customers 

di mana lookupValue akan UDF. Fungsi semacam ini adalah tidak mungkin bila menggunakan prosedur yang disimpan. Pada saat yang sama anda tidak dapat melakukan hal-hal tertentu di dalam UDF. Hal dasar yang perlu diingat di sini adalah bahwa UDF's:

• tidak dapat membuat perubahan permanen
• tidak bisa mengubah data

stored procedure dapat melakukan hal-hal tersebut.

Bagi saya inline penggunaan UDF adalah yang paling penting penggunaan UDF.

Disimpan Prosedur digunakan sebagai script. Mereka menjalankan serangkaian perintah untuk anda dan anda dapat menjadwalkan mereka untuk berjalan pada waktu-waktu tertentu.

Fungsi yang digunakan sebagai metode. Anda lulus sesuatu dan mengembalikan hasilnya. Harus kecil dan cepat - apa itu on the fly.

Stored procedure:

• Seperti miniatur program di SQL Server.
• Dapat yang sederhana seperti memilih pernyataan, atau serumit yang panjang naskah yang menambah, menghapus, update, dan/atau membaca data dari beberapa tabel-tabel dalam sebuah database.
• (Bisa melaksanakan loop dan kursor, yang memungkinkan anda untuk bekerja dengan kecil hasil atau baris demi baris operasi pada data.)
• Harus disebut menggunakan EXEC atau MENGEKSEKUSI pernyataan.
• Kembali tabel variabel, tetapi kita bisa't menggunakan KELUAR parameter.
• Mendukung transaksi.

Fungsi:

• Tidak dapat digunakan untuk memperbarui, menghapus, atau menambahkan data ke database.
• Hanya mengembalikan satu nilai atau nilai tabel.
• Hanya dapat digunakan untuk memilih catatan. Namun, hal itu dapat disebut sangat mudah dari dalam standar SQL, seperti:

PILIH dbo.functionname('Parameter1')

atau

PILIH Nama, dbo.Functionname('Parameter1') FROM sysObjects

• Sederhana untuk dapat digunakan kembali pilih operasi, fungsi dapat menyederhanakan kode. Hanya berhati-hatilah menggunakan BERGABUNG klausul dalam fungsi anda. Jika anda fungsi memiliki BERGABUNG klausul dan yang anda sebut itu dari yang lain pilih pernyataan yang hasilnya banyak, bahwa pemanggilan fungsi ini akan BERGABUNG tabel tersebut bersama-sama untuk setiap baris yang dikembalikan dalam hasil set. Jadi meskipun mereka dapat membantu dalam menyederhanakan beberapa logika, mereka juga dapat menjadi hambatan kinerja jika mereka're tidak digunakan dengan benar.
• Mengembalikan nilai menggunakan KELUAR parameter.
• Tidak mendukung transaksi.

Fungsi SQL Server, seperti kursor, dimaksudkan untuk digunakan sebagai senjata terakhir! Mereka memiliki masalah kinerja dan oleh karena itu menggunakan table-valued function harus dihindari sebisa mungkin. Berbicara tentang kinerja adalah berbicara tentang sebuah tabel dengan lebih dari 1.000.000 catatan yang di-host pada server kelas menengah hardware; jika tidak, anda don't perlu khawatir tentang performa yang disebabkan oleh fungsi.

1. Jangan gunakan fungsi ini untuk mengembalikan hasil-set ke eksternal kode (seperti ADO.Net)
2. Gunakan views/disimpan procs kombinasi sebanyak mungkin. anda dapat memulihkan dari masa tumbuh-masalah kinerja dengan menggunakan saran DTA (Database Tuning Penasihat) akan memberikan anda (seperti indexed views dan statistik) - kadang-kadang!

untuk referensi lebih lanjut lihat: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

Untuk memutuskan kapan harus menggunakan apa poin-poin berikut ini mungkin bisa membantu-

1. Disimpan prosedur dapat't kembali tabel variabel dimana fungsi yang dapat melakukan itu.

2. Anda dapat menggunakan prosedur yang tersimpan untuk mengubah server parameter lingkungan di mana dengan menggunakan fungsi-fungsi yang dapat anda't.

cheers

Mulai dengan fungsi yang mengembalikan nilai tunggal. Hal yang baik adalah anda dapat menempatkan sering digunakan kode menjadi satu fungsi dan mengembalikan mereka sebagai kolom dalam menetapkan hasil.

Kemudian, anda dapat menggunakan fungsi untuk parameterized daftar kota-kota. dbo.GetCitiesIn("NY") Yang mengembalikan tabel yang dapat digunakan sebagai bergabung.

It's cara mengatur kode. Mengetahui ketika ada sesuatu yang dapat digunakan kembali dan ketika itu adalah buang-buang waktu adalah sesuatu yang hanya diperoleh melalui trial and error dan pengalaman.

Juga, fungsi ini adalah ide yang baik dalam SQL Server. Mereka lebih cepat dan bisa sangat kuat. Inline dan langsung memilih. Hati-hati untuk tidak berlebihan.

• Itu adalah wajib untuk Fungsi mengembalikan nilai, sedangkan hal ini tidak untuk disimpan prosedur.
• Pilih pernyataan yang hanya diterima di UDF sementara pernyataan DML tidak diperlukan.
• Disimpan prosedur menerima pernyataan serta pernyataan DML.
• UDF hanya memungkinkan input dan output.
• Disimpan prosedur yang memungkinkan untuk kedua input dan output.
• Menangkap blok tidak dapat digunakan dalam UDF tetapi dapat digunakan dalam prosedur yang tersimpan.
• Tidak ada transaksi yang diperbolehkan dalam fungsi UDF tetapi disimpan prosedur mereka diizinkan.
• Hanya tabel variabel-variabel yang dapat digunakan dalam UDF dan tidak tabel sementara.
• Disimpan prosedur yang memungkinkan untuk kedua tabel variabel dan tabel sementara.
• UDF tidak memungkinkan disimpan prosedur yang dipanggil dari fungsi sementara disimpan prosedur yang memungkinkan pemanggilan fungsi.
• Type data yang digunakan dalam bergabung klausul sementara disimpan prosedur tidak dapat digunakan dalam bergabung dengan klausa.
• Prosedur yang tersimpan akan selalu memungkinkan untuk kembali ke nol. UDF, sebaliknya, memiliki nilai-nilai yang harus datang kembali ke titik yang telah ditentukan.

Berikut ini's alasan praktis untuk memilih fungsi dari prosedur yang tersimpan. Jika anda memiliki disimpan prosedur yang membutuhkan hasil lain prosedur yang tersimpan, anda harus menggunakan insert-exec pernyataan. Ini berarti bahwa anda harus membuat tabel temp dan menggunakan exec pernyataan untuk memasukkan hasil dari prosedur yang tersimpan ke dalam tabel temp. It's berantakan. Satu masalah dengan ini adalah bahwa insert-eksekutif tidak dapat bersarang.

Jika anda're terjebak dengan prosedur yang tersimpan panggilan yang lain disimpan prosedur, anda mungkin mengalami hal ini. Jika bersarang prosedur tersimpan yang hanya mengembalikan dataset, dapat diganti dengan table-valued function dan anda'll tidak lagi mendapatkan kesalahan ini.

(ini adalah satu lagi alasan kita harus menjaga logika bisnis dari database)

User Defined Function.

1. Fungsi harus mengembalikan nilai.
2. Akan memungkinkan hanya Memilih pernyataan, tidak akan memungkinkan kita untuk menggunakan pernyataan DML.
3. Itu hanya akan mengizinkan parameter input, doesn't dukungan output parameter.
4. Ini tidak akan memungkinkan kita untuk menggunakan try-catch blok.
5. Transaksi yang tidak diperbolehkan di dalam fungsi.
6. Kita dapat menggunakan tabel variabel, hal ini tidak akan memungkinkan menggunakan tabel sementara.
7. Disimpan Prosedur dapat't akan dipanggil dari fungsi.
8. Fungsi dapat dipanggil dari pernyataan select.
9. UDF dapat digunakan dalam bergabung dengan klausa sebagai akibat ditetapkan.

Stored Procedure

1. Stored Procedure dapat atau tidak mengembalikan nilai.
2. Dapat pilih pernyataan serta pernyataan DML seperti insert, update, delete dan sebagainya
3. Hal ini dapat memiliki parameter input dan output.
4. Untuk penanganan eksepsi kita dapat menggunakan try catch blok.
5. Dapat menggunakan transaksi dalam Prosedur yang Tersimpan.
6. Dapat menggunakan kedua tabel variabel serta tabel sementara di dalamnya.
7. Disimpan Prosedur dapat memanggil fungsi-fungsi.
8. Prosedur dapat't akan dipanggil dari Select/Mana/Memiliki dan sebagainya pernyataan. Melaksanakan/Exec pernyataan dapat digunakan untuk memanggil/menjalankan Prosedur yang Tersimpan.
9. Prosedur dapat't yang akan digunakan dalam klausul Bergabung

• Fungsi dapat digunakan dalam memilih pernyataan mana karena prosedur tidak.

• Disimpan prosedur mengambil kedua parameter input dan output tetapi Fungsi hanya membutuhkan input parameter.

• Fungsi-fungsi yang tidak mengembalikan nilai-nilai dari jenis teks, ntext, gambar & cap waktu dimana prosedur dapat.

• Fungsi dapat digunakan sebagai pengguna didefinisikan tipe data dalam membuat tabel, tapi prosedur tidak.

***Misalnya:-buat tabel <namatabel>(nama varchar(10),gaji getsal(nama))

Di sini getsal adalah user defined function yang mengembalikan gaji tipe, ketika tabel dibuat tidak ada penyimpanan yang dialokasikan untuk gaji jenis, dan getsal fungsi ini juga tidak dieksekusi, Tapi ketika kita mengambil nilai-nilai dari tabel ini, getsal fungsi bisa dijalankan dan kembali Jenis dikembalikan sebagai hasil yang ditetapkan.

Saya menyadari ini adalah pertanyaan kuno, tapi aku don't melihat satu aspek penting yang disebutkan dalam jawaban: inlining ke query rencana.

Fungsi dapat...

1. Skalar:

MEMBUAT FUNGSI ... KEMBALI scalar_type SEBAGAI BEGIN ... END

2. Multi-pernyataan dihargai meja:

MEMBUAT FUNGSI ... KEMBALI @r TABEL(...) SEBAGAI BEGIN ... END

3. Inline table-valued:

MEMBUAT FUNGSI ... KEMBALI TABEL SEBAGAI KEMBALI PILIH ...

Jenis ketiga (inline table-valued) diperlakukan oleh query optimizer pada dasarnya sebagai (parametrized) pandangan, yang berarti bahwa referensi fungsi dari query yang mirip dengan copy-paste fungsi's SQL tubuh (tanpa benar-benar copy-paste), yang mengarah ke manfaat sebagai berikut:

• Query perencana dapat mengoptimalkan fungsi inline's eksekusi seperti itu akan lain sub-query (misalnya menghilangkan yang tidak terpakai kolom, mendorong predikat bawah, pick berbeda BERGABUNG strategi dll.).
• Menggabungkan beberapa fungsi inline doesn't membutuhkan mewujudkan hasil dari yang pertama satu sebelum makan berikutnya.

Hal di atas dapat menyebabkan berpotensi signifikan kinerja tabungan, terutama ketika menggabungkan beberapa tingkat fungsi.

CATATAN: Terlihat seperti SQL Server 2019 akan memperkenalkan beberapa bentuk skalar fungsi inlining juga.

Di SQL Server, fungsi dan prosedur yang tersimpan adalah dua jenis yang berbeda dari badan.

Fungsi: Dalam SQL Server database, fungsi-fungsi yang digunakan untuk melakukan beberapa tindakan dan aksi kembali hasil segera. Fungsi dua jenis:

1. Sistem didefinisikan

2. Pengguna didefinisikan

Disimpan Prosedur: Di SQL Server disimpan prosedur yang disimpan di server dan dapat kembali nol, satu dan beberapa nilai-nilai. Disimpan Prosedur dua jenis:

1. Sistem Prosedur Yang Tersimpan
2. Pengguna Didefinisikan Prosedur