Fungsi Standar
Definisi Fungsi
Fungsi adalah bagian dari perintah/statement yang merubah beberapa nilai masukan
menjadi sebuah nilai baru (keluaran/hasil).
Beberapa fungsi telah tersedia dan tinggal dipakai (fungsi standar) dan dapat pula dibuat sendiri sebagai fungsi baru (user defined function).
Semua fungsi ditulis nama yang diikuti kurung ()
Contoh
Fungsi power(a,b) untuk menghitung a pangkat b
Fungsi left(st,n) untuk mengambil n huruf depan/terkiri dari st
Contoh pemanggilan :
Select power(2,4) akan menampilkan 16
2 pangkat 4, yaitu 2 x 2 x 2 x 2 = 16
Update mhs set inisial=left(namadepan,1)+left(namabelakang,1)
Mengisi kolom inisial pada tabel mhs dengan 2 huruf yang diambil
dari 1 huruf namadepan dan 1 huruf namabelakang
select nama, ‘Hari Lahir ‘=datepart(dw,tgllahir) from mhs
Menampilkan nama dan hari lahir semua data mahasiswa
Kelompok Fungsi
Fungsi standar yang tersedia, dikelompokkan sebagai berikut
· Fungsi untuk Konfigurasi
· Fungsi untuk manipulasi kursor
· Fungsi untuk tanggal & jam
· Fungsi Matematika
· Fungsi Agregat
· Fungsi Metadata
· Fungsi untuk Security
· Fungsi untuk manipulasi string
· Fungsi untuk Sistem
· Fungsi untuk statistic
· Fungsi untuk teks & gambar
Pengelompokkan di atas dapat dilihat secara lebih jelas pada bagian Function.
Berikut ini ditabelkan kegunaan dari beberapa fungsi yang penting untuk diketahui
| No | Sintaks Pemanggilan | Arti/hasil |
| | FUNGSI STRING | |
| 1 | ASCII('C') | Nomor ASCII dari karakter ‘C’ |
| 2 | Char(65) | Karakter dari ASCII bernomor 65 |
| 3 | charindex('E','hello') | Posisi ‘E’ dalam kata ‘hello’ |
| 4 | left('HELLO',3) | 3 huruf terkiri dari kata ‘HELLO’ |
| 5 | ltrim(' Hello') | Membuang spasi di kiri |
| 6 | right('hello',3) | 3 huruf terkanan dari kata ‘HELLO’ |
| 7 | rtrim('Hello ') | Membuang spasi di kanan |
| 8 | len('Hello') | Panjang/jumlah huruf dari kata ‘HELLO’ |
| 9 | lower('HELLO') | Merubah ke huruf kecil |
| 10 | patindex('%BOX%','ACTION BOX') | Posisi kata ‘BOX’ dalam ‘ACTION BOX’ |
| 11 | reverse('HELLO') | Membalik susunan huruf/depan ke belakang dst |
| 12 | space(5) | Membentuk spasi sebanyak 5 |
| 13 | str(123.45,6,0) | Merubah ke string dengan 6 digit tanpa spasi |
| 14 | stuff('hello',2,2,'i') | Mengganti huruf kedua dengan huruf ‘i’ |
| 15 | substring('hello',2,2) | Mengambil 2 huruf mulai huruf ke 2 |
| 16 | upper('hello') | Merubah ke huruf besar |
| | | |
| | FUNGSI TANGGAL | |
| 1 | getdate() | Mengambil tanggal lengkap hari ini |
| 2 | datepart(dd,getdate()) | Mengambil bagian tanggal dari hari ini |
| 3 | datename(dw,'1980-06-11') | Menghasilkan nama hari dari 11 juni 1980 |
| 4 | dateadd(yy,2,getdate()) | Menambah 2 tahun dari hari ini |
| 5 | datediff(dd,'1980-06-11',getdate()) | Menghitung selisih hari (umur sejak 11 jun 80) |
| | | |
| | FUNGSI MATEMATIK | |
| 1 | abs(-25) | Mengambil nilai absolute (tanpa negatif) |
| 2 | sin(pi()/2) | Menghitung sinus sudut 90 derajat (pi=180) |
| 3 | exp(1) | Menghitung bilangan e pangkat 1 |
| 4 | degrees(pi()/2) | Mengkonversi dari pi/2 radian ke derajat |
| 5 | radians(180) | Mengkonversi 180 derajat ke radian |
| 6 | power(2,4) | Menghitung 2 pangkat 4 |
| 7 | floor(90.7) | Membulatkan ke bawah |
| 8 | sign(90) | Menghasilkan tanda bilangan (pos=1,neg=-1) |
| 9 | rand(90) | Menghasilkan bil acak dengan bil pembangkit 90 |
| 10 | round(1234.567,2) | Membulatkan ke 2 angka belakang koma |
| | | |
| | FUNGSI SYSTEM & METADATA | |
| 1 | host_id() | Menghasilkan ID dari host |
| 2 | host_name() | Menghasilkan nama dari host |
| 3 | suser_sname() | Menghasilkan system username yg sdg aktif |
| 4 | user_id() | Menghasilkan user ID yang sedang aktif |
| 5 | user_name() | Menghasilkan username yang sedang aktif |
| 6 | db_id() | Menghasilkan database ID yang sedang aktif |
| 7 | db_name() | Menghasilkan database name yang sedang aktif |
| 8 | object_id('Authors') | Menghasilkan ID dari object bernama ‘Authors’ |
| 9 | object_name('629577281') | Menghasilkan nama object yang ber ID=… |
Untuk dapat memahami penggunaan beberapa fungsi standar dapat dicoba beberapa contoh berikut :
select ' 1. ',ascii('ABC')
select ' 2. ',char(65)
select ' 3. ',charindex('E','hello')
select ' 4. ',left('HELLO',3)
select ' 5. ','A'+ltrim(' Hello')
select ' 6. ',right('hello',3)
select ' 7. ',rtrim('Hello ')+'A'
select ' 8. ',len('Hello')
select ' 9. ',lower('HELLO')
select '10. ',patindex('%BOX%','ACTION BOX')
select '11. ',reverse('HELLO')
select '12. ','A'+space(5)+'B'
select '13. ',str(123.45,6,0)
select '14. ',stuff('hello',2,2,'i')
select '15. ',substring('hello',2,2)
select '16. ',upper('hello')
select '17. ',getdate()
select '18. ',datepart(dd,getdate())
select '19. ',datepart(mm,getdate())
select '20. ',datepart(yy,getdate())
select '21. ',datepart(qq,getdate())
select '22. ',datepart(ww,getdate())
select '23. ',datepart(dy,getdate())
select '24. ',datepart(dw,getdate())
select '25. ',datename(mm,getdate())
select '26. ',datename(dw,getdate())
select '27. ',datename(dw,'1980-06-11')
select '28. ',dateadd(dd,2,getdate())
select '29. ',dateadd(mm,2,getdate())
select '30. ',dateadd(yy,2,getdate())
select '31. ',datediff(dd,'1980-06-11',getdate())
select '32. ',datediff(mm,'1980-06-11',getdate())
select '33. ',datediff(yy,'1980-06-11',getdate())
select '34. ',abs(-25)
select '35. ',sin(pi()/2)
select '36. ',exp(1)
select '37. ',degrees(pi()/2)
select '38. ',radians(180)
select '39. ',power(2,4)
select '40. ',floor(90.7)
select '41. ',sign(90)
select '42. ',rand(90)
select '43. ',round(1234.567,2)
select '44. ',host_id()
select '45. ',host_name()
select '46. ',suser_sname()
select '47. ',user_id()
select '48. ',user_name()
select '49. ',db_id()
select '50. ',db_name()
select '51. ',object_id('authors')
select '52. ',object_name('629577281')
Join
Definisi Join
Join adalah operasi untuk mengambil informasi dari 2 tabel atau lebih dalam 1 waktu. Dengan join baris data dari satu tabel dihubungkan dengan baris data pada tabel lain berdasarkan kolom tertentu.
Klasifikasi Join
· inner join
· outer join
· cross join
· equi join
· natural join
· self join
Dari beberapa macam join tersebut yang banyak digunakan adalah inner join dan outer join
Inner Join
Inner join adalah default dari join, digunakan mendapatkan data dari tabel lain berdasarkan kolom yang dihubungkan. Bila tidak ditemukan maka baris data tersebut dibatalkan.
Syntax :
Select … from tabelA [inner] join tabelB
on tabelA.namakolom operator tabelB.namakolom
Keterangan :
· Kata inner adalah opsional (boleh ditulis ataupun tidak)
· Klausa where, order by dll dapat disertakan
Contoh :
Menampilkan ID buku, Judul buku dan nama penerbitnya dari tabel Title & Publishers
Pada tabel Title tidak ada nama penerbit, tetapi ada kode penerbit/Pub_Id yang namanya dapat diperoleh dari tabel Publishers berdasarkan Pub_Id
Select t.Title_Id, t.Title, p.Pub_name from Titles t join Publishers p
on t.Pub_Id = p.Pub_Id
Outer Join
Outer join adalah join yang digunakan memasangkan data dari satu tabel dengan tabel lain berdasarkan kolom yang dihubungkan walaupun salah satu kolom penghubungnya tidak berpasangan.
Syntax :
Select … from tabelA [left|right] outer join tabelB
on tabelA.namakolom operator tabelB.namakolom
Keterangan :
· Kata outer harus ditulis dan didahului kata left atau right
· Kata left dipilih bila nama tabel disebelah kiri kata join yang menjadi acuan
· Kata right dipilih bila nama tabel disebelah kanan kata join yang menjadi acuan
· Klausa where, order by dll dapat disertakan
Contoh :
Menampilkan daftar penerbit & nama-nama pengarang yang berada di kota yang sama (semua penerbit ditampilkan walaupun tidak ada nama pengarang yang satu kota)
Select p.Pub_name, a.Au_Lname, a.Au_Fname from Publishers p
left outer join Authors a on p.City = a.City
dalam syntax lain :
Select p.Pub_name, a.Au_Lname, a.Au_Fname from Publishers p, Authors a
where p.City *= a.City
Membuat Fungsi Sendiri
Ada beberapa jenis fungsi yang dapat kita buat sendiri (User Defined Function), yaitu :
1. Inline Table – valued Function
2. Multi-statement– valued Function
3. Scalar– valued Function
Untuk membuat, klik pada bagian Function sbb :
Subquery
Definisi Subquery
Subquery adalah query yang menjadi bagian / ditulis dalam statement lain. Statement bisa berupa SELECT, INSERT, UPDATE ataupun DELETE. Satu atau lebih subquery yang berada dalam query yang lain disebut nested query.
Syntax untuk SELECT statement yang mengandung subquery:
Select … from namatabel
where namakolom operator [ALL|ANY] (select … from namatabel where …)
atau
Select … from namatabel
where [NOT] EXISTS (select … from namatabel where …)
Keterangan :
· Subquery ditulis dalam tanda kurung (…)
· Subquery tidak boleh menggunakan ORDER BY atau COMPUTE BY
· Bila subquery dengan hasil nilai tunggal digunakan operator: =, >, <, <=, >=, !=
· Bila subquery dengan hasil nilai tidak tunggal maka ALL|ANY harus disertakan (seperti: >ALL, >ANY, =ANY, <>ANY, <>ALL dll)
· =ANY dapat diganti dengan IN (sama dengan salah satu)
· <>ANY dapat diganti dengan NOT IN (tidak sama dengan salah satupun)
· [NOT] EXISTS untuk pengecekan, bila benar maka statement dikerjakan, semisal dg
If exists (select … from namatabel where …) Select … from namatabel
Contoh :
Menampilkan ID buku, Judul buku yang harganya lebih mahal dari buku yang berjudul ”Sushi, Anyone?” (ID=”TC7777”)
Select Title_Id, Title from Titles
where price > (select price from Titles where Title_Id=’TC7777’)
LAMPIRAN SQL Server 2005 Books Online (November 2008)
CREATE FUNCTION (Transact-SQL)
Updated: 14 April 2006
Creates a user-defined function. This is a saved Transact-SQL or common language runtime (CLR) routine that returns a value. User-defined functions cannot be used to perform actions that modify the database state. User-defined functions, like system functions, can be invoked from a query. Scalar functions can be executed by using an EXECUTE statement like stored procedures.
Syntax
Scalar FunctionsCREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ])RETURNS return_data_type [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END[ ; ] Inline Table-valued FunctionsCREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ])RETURNS TABLE [ WITH <function_option> [ ,...n ] ] [ AS ] RETURN [ ( ] select_stmt [ ) ][ ; ] Multistatement Table-valued FunctionsCREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ])RETURNS @return_variable TABLE < table_type_definition > [ WITH <function_option> [ ,...n ] ] [ AS ] BEGIN function_body RETURN END[ ; ] CLR FunctionsCREATE FUNCTION [ schema_name. ] function_name ( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ])RETURNS { return_data_type | TABLE <clr_table_type_definition> } [ WITH <clr_function_option> [ ,...n ] ] [ AS ] EXTERNAL NAME <method_specifier>[ ; ] Method Specifier<method_specifier>::= assembly_name.class_name.method_name Function Options<function_option>::= { [ ENCRYPTION ] | [ SCHEMABINDING ] | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] | [ EXECUTE_AS_Clause ]} <clr_function_option>::=} [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] | [ EXECUTE_AS_Clause ]} Table Type Definitions<table_type_definition>:: = ( { <column_definition> <column_constraint> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ]) <clr_table_type_definition>::= ( { column_name data_type } [ ,...n ] ) <column_definition>::={ { column_name data_type } [ [ DEFAULT constant_expression ] [ COLLATE collation_name ] | [ ROWGUIDCOL ] ] | [ IDENTITY [ (seed , increment ) ] ] [ <column_constraint> [ ...n ] ] }<column_constraint>::= { [ NULL | NOT NULL ] { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor | WITH ( < index_option > [ , ...n ] ) [ ON { filegroup | "default" } ] | [ CHECK ( logical_expression ) ] [ ,...n ]} <computed_column_definition>::=column_name AS computed_column_expression <table_constraint>::={ { PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ ,...n ] ) [ WITH FILLFACTOR = fillfactor | WITH ( <index_option> [ , ...n ] ) | [ CHECK ( logical_expression ) ] [ ,...n ]} <index_option>::={ PAD_INDEX = { ON | OFF } | FILLFACTOR = fillfactor | IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF } | ALLOW_ROW_LOCKS = { ON | OFF } | ALLOW_PAGE_LOCKS ={ ON | OFF } } Arguments
schema_name
Is the name of the schema to which the user-defined function belongs.
function_name
Is the name of the user-defined function. Function names must comply with the rules for identifiers and must be unique within the database and to its schema.
| Note: |
| Parentheses are required after the function name even if a parameter is not specified. |
@ parameter_name
Is a parameter in the user-defined function. One or more parameters can be declared.
A function can have a maximum of 1,024 parameters. The value of each declared parameter must be supplied by the user when the function is executed, unless a default for the parameter is defined.
Specify a parameter name by using an at sign (@) as the first character. The parameter name must comply with the rules for identifiers. Parameters are local to the function; the same parameter names can be used in other functions. Parameters can take the place only of constants; they cannot be used instead of table names, column names, or the names of other database objects.
| Note: |
| ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds. |
[ type_schema_name. ] parameter_data_type
Is the parameter data type, and optionally the schema to which it belongs. For Transact-SQL functions, all data types, including CLR user-defined types, are allowed except the timestamp data type. For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, and timestamp data types. The nonscalar types cursor and table cannot be specified as a parameter data type in either Transact-SQL or CLR functions.
If type_schema_name is not specified, the SQL Server 2005 Database Engine looks for the scalar_parameter_data_type in the following order:
· The schema that contains the names of SQL Server system data types.
· The default schema of the current user in the current database.
· The dbo schema in the current database.
[ = default ]
Is a default value for the parameter. If a default value is defined, the function can be executed without specifying a value for that parameter.
| Note: |
| Default parameter values can be specified for CLR functions except for varchar(max) and varbinary(max) data types. |
When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.
return_data_type
Is the return value of a scalar user-defined function. For Transact-SQL functions, all data types, including CLR user-defined types, are allowed except the timestamp data type. For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, and timestamp data types. The nonscalar types cursor and table cannot be specified as a return data type in either Transact-SQL or CLR functions.
function_body
Specifies that a series of Transact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function. function_body is used only in scalar functions and multistatement table-valued functions.
In scalar functions, function_body is a series of Transact-SQL statements that together evaluate to a scalar value.
In multistatement table-valued functions, function_body is a series of Transact-SQL statements that populate a TABLE return variable.
scalar_expression
Specifies the scalar value that the scalar function returns.
TABLE
Specifies that the return value of the table-valued function is a table. Only constants and @local_variables can be passed to table-valued functions.
In inline table-valued functions, the TABLE return value is defined through a single SELECT statement. Inline functions do not have associated return variables.
In multistatement table-valued functions, @return_variable is a TABLE variable, used to store and accumulate the rows that should be returned as the value of the function. @return_variable can be specified only for Transact-SQL functions and not for CLR functions.
select_stmt
Is the single SELECT statement that defines the return value of an inline table-valued function.
EXTERNAL NAME <method_specifier>, assembly_name.class_name.method_name
Specifies the method of an assembly to bind with the function. assembly_name must match an existing assembly in SQL Server in the current database with visibility on. class_name must be a valid SQL Server identifier and must exist as a class in the assembly. If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([ ]) or quotation marks (" "). method_name must be a valid SQL Server identifier and must exist as a static method in the specified class.
| Note: |
| By default, SQL Server cannot execute CLR code. You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL Server until you enable the clr enabled option. To enable this option, use sp_configure. |
<table_type_definition>, ( { <column_definition> <column_constraint> , | <computed_column_definition> } , [ <table_constraint> ] [ ,...n ], ) ,
Defines the table data type for a Transact-SQL function. The table declaration includes column definitions and column or table constraints. The table is always put in the primary filegroup.
< clr_table_type_definition > , ( { column_name data_type } [ ,...n ] ),
Defines the table data types for a CLR function. The table declaration includes only column names and data types. The table is always put in the primary filegroup.
<function_option>::= and <clr_function_option>::=
Specifies the function will have one or more of the following options.
ENCRYPTION
Indicates that the Database Engine will convert the original text of the CREATE FUNCTION statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server 2005. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration.
Using this option prevents the function from being published as part of SQL Server replication. This option cannot be specified for CLR functions.
SCHEMABINDING
Specifies that the function is bound to the database objects that it references. This condition will prevent changes to the function if other schema-bound objects are referencing it.
The binding of the function to the objects it references is removed only when one of the following actions occurs:
· The function is dropped.
· The function is modified by using the ALTER statement with the SCHEMABINDING option not specified.
A function can be schema bound only if the following conditions are true:
· The function is a Transact-SQL function.
· The user-defined functions and views referenced by the function are also schema-bound.
· The objects referenced by the function are referenced using a two-part name.
· The function and the objects it references belong to the same database.
· The user who executed the CREATE FUNCTION statement has REFERENCES permission on the database objects that the function references.
SCHEMABINDING cannot be specified for CLR functions or functions that reference alias data types.
RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
Specifies the OnNULLCall attribute of a scalar-valued function. If not specified, CALLED ON NULL INPUT is implied by default. This means that the function body executes even if NULL is passed as an argument.
If RETURNS NULL ON NULL INPUT is specified in a CLR function, it indicates that SQL Server can return NULL when any of the arguments it receives is NULL, without actually invoking the body of the function. If the method of a CLR function specified in <method_specifier> already has a custom attribute that indicates RETURNS NULL ON NULL INPUT, but the CREATE FUNCTION statement indicates CALLED ON NULL INPUT, the CREATE FUNCTION statement takes precedence. The OnNULLCall attribute cannot be specified for CLR table-valued functions.
EXECUTE AS Clause
Specifies the security context under which the user-defined function is executed. Therefore, you can control which user account SQL Server uses to validate permissions on any database objects that are referenced by the function.
| Note: |
| EXECUTE AS cannot be specified for inline user-defined functions. |
< column_definition >::=
Defines the table data type. The table declaration includes column definitions and constraints. For CLR functions, only column_name and data_type can be specified.
column_name
Is the name of a column in the table. Column names must comply with the rules for identifiers and must be unique in the table. column_name can consist of 1 through 128 characters.
data_type
Specifies the column data type. For Transact-SQL functions, all data types, including CLR user-defined types, are allowed except timestamp. For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, char, varchar, varchar(max), and timestamp.The nonscalar type cursor cannot be specified as a column data type in either Transact-SQL or CLR functions.
DEFAULT constant_expression
Specifies the value provided for the column when a value is not explicitly supplied during an insert. constant_expression is a constant, NULL, or a system function value. DEFAULT definitions can be applied to any column except those that have the IDENTITY property. DEFAULT cannot be specified for CLR table-valued functions.
COLLATE collation_name
Specifies the collation for the column. If not specified, the column is assigned the default collation of the database. Collation name can be either a Windows collation name or a SQL collation name. For a list of and more information about collations, see Windows Collation Name (Transact-SQL) and SQL Collation Name (Transact-SQL).
The COLLATE clause can be used to change the collations only of columns of the char, varchar, nchar, and nvarchar data types.
COLLATE cannot be specified for CLR table-valued functions.
ROWGUIDCOL
Indicates that the new column is a row globally unique identifier column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.
The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column. It also does not automatically generate values for new rows inserted into the table. To generate unique values for each column, use the NEWID function on INSERT statements. A default value can be specified; however, NEWID cannot be specified as the default.
IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table, SQL Server provides a unique, incremental value for the column. Identity columns are typically used together with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).
IDENTITY cannot be specified for CLR table-valued functions.
seed
Is the integer value to be assigned to the first row in the table.
increment
Is the integer value to add to the seed value for successive rows in the table.
< column_constraint >::= and < table_constraint>::=
Defines the constraint for a specified column or table. For CLR functions, the only constraint type allowed is NULL. Named constraints are not allowed.
NULL | NOT NULL
Determines whether null values are allowed in the column. NULL is not strictly a constraint but can be specified just like NOT NULL. NOT NULL cannot be specified for CLR table-valued functions.
PRIMARY KEY
Is a constraint that enforces entity integrity for a specified column through a unique index. In table-valued user-defined functions, the PRIMARY KEY constraint can be created on only one column per table. PRIMARY KEY cannot be specified for CLR table-valued functions.
UNIQUE
Is a constraint that provides entity integrity for a specified column or columns through a unique index. A table can have multiple UNIQUE constraints. UNIQUE cannot be specified for CLR table-valued functions.
CLUSTERED | NONCLUSTERED
Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints use CLUSTERED, and UNIQUE constraints use NONCLUSTERED.
CLUSTERED can be specified for only one constraint. If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY uses NONCLUSTERED.
CLUSTERED and NONCLUSTERED cannot be specified for CLR table-valued functions.
CHECK
Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns. CHECK constraints cannot be specified for CLR table-valued functions.
logical_expression
Is a logical expression that returns TRUE or FALSE.
<computed_column_definition>::=
Specifies a computed column. For more information about computed columns, see CREATE TABLE (Transact-SQL).
column_name
Is the name of the computed column.
computed_column_expression
Is an expression that defines the value of a computed column.
<index_option>::=
Specifies the index options for the PRIMARY KEY or UNIQUE index. For more information about index options, see CREATE INDEX (Transact-SQL).
PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.
FILLFACTOR = fillfactor
Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or change. fillfactor must be an integer value from 1 to 100. The default is 0.
IGNORE_DUP_KEY = { ON | OFF }
Specifies the error response to duplicate key values in a multiple-row insert transaction on a unique clustered or unique nonclustered index. The default is OFF.
STATISTICS_NORECOMPUTE = { ON | OFF }
Specifies whether distribution statistics are recomputed. The default is OFF.
ALLOW_ROW_LOCKS = { ON | OFF }
Specifies whether row locks are allowed. The default is ON.
ALLOW_PAGE_LOCKS = { ON | OFF }
Specifies whether page locks are allowed. The default is ON.
Remarks
User-defined functions are either scalar-valued or table-valued. Functions are scalar-valued if the RETURNS clause specified one of the scalar data types. Scalar-valued functions can be defined by using multiple Transact-SQL statements.
Functions are table-valued if the RETURNS clause specified TABLE. Depending on how the body of the function is defined, table-valued functions can be classified as inline or multi-statement functions. For more information, see Table-valued User-defined Functions.
The following statements are valid in a function:
- Assignment statements.
- Control-of-Flow statements except TRY...CATCH statements.
- DECLARE statements defining local data variables and local cursors.
- SELECT statements that contain select lists with expressions that assign values to local variables.
- Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.
- INSERT, UPDATE, and DELETE statements modifying local table variables.
- EXECUTE statements calling extended stored procedures.
- For more information, see Creating User-defined Functions (Database Engine).
Nesting User-defined Functions
User-defined functions can be nested; that is, one user-defined function can call another. The nesting level is incremented when the called function starts execution, and decremented when the called function finishes execution. User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function chain to fail.
| Note: |
| Any reference to managed code from a Transact-SQL user-defined function counts as one level against the 32-level nesting limit. Methods invoked from within managed code do not count against this limit. |
Function Properties
In earlier versions of SQL Server, functions are categorized only as deterministic or nondeterministic. In SQL Server 2005, functions have the following properties. The values of these properties determine whether functions can be used in computed columns that can be persisted or indexed.
| Property | Description | Notes |
| IsDeterministic | Function is deterministic or nondeterministic. | Local data access is allowed in deterministic functions. For example, functions that always return the same result any time they are called by using a specific set of input values and with the same state of the database would be labeled deterministic. |
| IsPrecise | Function is precise or imprecise. | Imprecise functions contain operations such as floating point operations. |
| IsSystemVerified | The precision and determinism properties of the function can be verified by SQL Server. | |
| SystemDataAccess | Function accesses system data (system catalogs or virtual system tables) in the local instance of SQL Server. | |
| UserDataAccess | Function accesses user data in the local instance of SQL Server. | Includes user-defined tables and temp tables, but not table-variables. |
The precision and determinism properties of Transact-SQL functions are determined automatically by SQL Server. For more information, see User-defined Function Design Guidelines. The data access and determinism properties of CLR functions can be specified by the user. For more information, see Overview of CLR Integration Custom Attributes.
Indexing Computed Columns That Invoke a User-defined Function
A computed column that invokes a user-defined function can be used in an index when the user-defined function has the following property values:
- IsDeterministic = true
- IsSystemVerified = true (unless the computed column is persisted)
- UserDataAccess = false
- SystemDataAccess = false
Calling Extended Stored Procedures from Functions
The extended stored procedure, when it is called from inside a function, cannot return result sets to the client. Any ODS APIs that return result sets to the client will return FAIL. The extended stored procedure could connect back to an instance of SQL Server; however, it should not try to join the same transaction as the function that invoked the extended stored procedure.
Similar to invocations from a batch or stored procedure, the extended stored procedure will be executed in the context of the Windows security account under which SQL Server is running. The owner of the stored procedure should consider this when giving EXECUTE permission on it to users.
Function Invocation
Scalar-valued functions can be invoked where scalar expressions are used. This includes computed columns and CHECK constraint definitions. Scalar-valued functions can also be executed by using the EXECUTE statement. Scalar-valued functions must be invoked by using at least the two-part name of the function. For more information about multipart names, see Transact-SQL Syntax Conventions (Transact-SQL). Table-valued functions can be invoked where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements. For more information, see Executing User-defined Functions (Database Engine).
Using Parameters and Return Values in CLR Functions
If parameters are specified in a CLR function, they should be SQL Server types as defined previously for scalar_parameter_data_type. For information about comparing SQL Server system data types to CLR integration data types or .NET Framework common language runtime data types, see SQL Server Data Types and Their .NET Framework Equivalents.
For SQL Server to reference the correct method when it is overloaded in a class, the method indicated in <method_specifier> must have the following characteristics:
- Receive the same number of parameters as specified in [ ,...n ].
- Receive all the parameters by value, not by reference.
- Use parameter types that are compatible with those specified in the SQL Server function.
If the return data type of the CLR function specifies a table type (RETURNS TABLE), the return data type of the method in <method_specifier> should be of type IEnumerator or IEnumerable, and it is assumed that the interface is implemented by the creator of the function. Unlike Transact-SQL functions, CLR functions cannot include PRIMARY KEY, UNIQUE, or CHECK constraints in <table_type_definition>. The data types of columns specified in <table_type_definition> must match the types of the corresponding columns of the result-set returned by the method in <method_specifier> at execution time. This type-checking is not performed at the time the function is created.
Disallowed SQL Statements
The following Service Broker statements cannot be included in the definition of a Transact-SQL user-defined function:
- BEGIN DIALOG CONVERSATION
- END CONVERSATION
- GET CONVERSATION GROUP
- MOVE CONVERSATION
- RECEIVE
- SEND
Viewing Information About Functions
To display the definition of Transact-SQL user-defined functions, use the sys.sql_modules catalog view in the database in which the function exists.
For example:
USE AdventureWorks;GOSELECT definition, type FROM sys.sql_modules AS mJOIN sys.objects AS o ON m.object_id = o.object_id AND type IN ('FN', 'IF', 'TF');GO| Note: |
| The definition of functions created by using the ENCRYPTION option cannot be viewed by using sys.sql_modules; however, other information about the encrypted functions is displayed. |
To display information about CLR user-defined functions, use the sys.assembly_modules catalog view in the database in which the function exists.
To display information about the parameters defined in user-defined functions, use the sys.parameters catalog view in the database in which the function exists.
Permissions
Requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. If the function specifies a user-defined type, requires EXECUTE permission on the type.
Examples
A. Using a scalar-valued user-defined function that calculates the ISO week
The following example creates the user-defined function
ISOweek. This function takes a date argument and calculates the ISO week number. For this function to calculate correctly, SET DATEFIRST 1 must be invoked before the function is called. The example also shows using the EXECUTE AS clause to specify the security context in which a stored procedure can be executed. In the example, the option
CALLER specifies that the procedure will be executed in the context of the user that calls it. The other options that you can specify are SELF, OWNER, and user_name.Here is the function call. Notice that
DATEFIRST is set to 1.USE AdventureWorks;GOIF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL DROP FUNCTION dbo.ISOweek;GOCREATE FUNCTION dbo.ISOweek (@DATE datetime)RETURNS intWITH EXECUTE AS CALLERASBEGIN DECLARE @ISOweek int; SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');--Special cases: Jan 1-3 may belong to the previous year IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;--Special case: Dec 29-31 may belong to the next year IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1; RETURN(@ISOweek);END;GOSET DATEFIRST 1;SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week'; Here is the result set.
ISO Week----------------52B. Creating an inline table-valued function
The following example returns an inline table-valued function. It returns three columns
ProductID, Name and the aggregate of year-to-date totals by store as YTD Total for each product sold to the store.USE AdventureWorks;GOIF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL DROP FUNCTION Sales.ufn_SalesByStore;GOCREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)RETURNS TABLEASRETURN ( SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total' FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID WHERE SH.CustomerID = @storeid GROUP BY P.ProductID, P.Name);GOTo invoke the function, run this query.
SELECT * FROM Sales.ufn_SalesByStore (602);C. Creating a multi-statement table-valued function
The following example creates the table-valued function
fn_FindReports(InEmpID). When supplied with a valid employee ID, the function returns a table that corresponds to all the employees that report to the employee either directly or indirectly. The function uses a recursive common table expression (CTE) to produce the hierarchical list of employees. For more information about recursive CTEs, see WITH common_table_expression (Transact-SQL).USE AdventureWorks;GOIF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL DROP FUNCTION dbo.ufn_FindReports;GOCREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)RETURNS @retFindReports TABLE ( EmployeeID int primary key NOT NULL, Name nvarchar(255) NOT NULL, Title nvarchar(50) NOT NULL, EmployeeLevel int NOT NULL, Sort nvarchar (255) NOT NULL)--Returns a result set that lists all the employees who report to the --specific employee directly or indirectly.*/ASBEGIN WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, 1, CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName) FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.EmployeeID = @InEmpID UNION ALL SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) + c.FirstName + ' ' + c.LastName), e.Title, e.EmployeeID, EmployeeLevel + 1, CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + LastName) FROM HumanResources.Employee as e JOIN Person.Contact AS c ON e.ContactID = c.ContactID JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID )-- copy the required columns to the result of the function INSERT @retFindReports SELECT EmployeeID, Name, Title, EmployeeLevel, Sort FROM DirectReports RETURNEND;GO-- Example invocationSELECT EmployeeID, Name, Title, EmployeeLevelFROM dbo.ufn_FindReports(109)ORDER BY Sort;GOD. Creating a CLR function
The following example assumes that the SQL Server Database Engine samples are installed in the default location of the local computer and the StringManipulate.csproj sample application is compiled. For more information, see Supplementary-Aware String Manipulation.
The example creates CLR function
len_s. Before the function is created, the assembly SurrogateStringFunction.dll is registered in the local database. DECLARE @SamplesPath nvarchar(1024);-- You may have to modify the value of the this variable if you have--installed the sample someplace other than the default location.SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') FROM master.sys.database_files WHERE name = 'master'; CREATE ASSEMBLY [SurrogateStringFunction]FROM @SamplesPath + 'StringManipulate\CS\StringManipulate\bin\debug\SurrogateStringFunction.dll'WITH PERMISSION_SET = EXTERNAL_ACCESS;GO CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))RETURNS bigintAS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];GO See Also
Reference
ALTER FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
EXECUTE (Transact-SQL)
EVENTDATA (Transact-SQL)
DROP FUNCTION (Transact-SQL)
OBJECTPROPERTYEX (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
EXECUTE (Transact-SQL)
EVENTDATA (Transact-SQL)
Other Resources
Help and Information
Change History
| Release | History |
| 14 April 2006 | New content: · Under "Function Invocation" the Remarks section, added scalar-valued functions must be invoked by using at least the two-part name of the function. |
| 5 December 2005 | Changed content: · Clarified the definition of the ENCRYPTION option. |
| User-defined SQL function 'Wrapping' a view may be faster thatn the view itself. | |
The following MyFunction is an example of a 'function-wrapper' which runs faster than the underlying view MyView:
CREATE FUNCTION MyFunction ()
RETURNS @Tbl TABLE
(
StudentID VARCHAR(255),
SAS_StudentInstancesID INT,
Label VARCHAR(255),
Value MONEY,
CMN_PersonsID INT
)
AS
BEGIN
INSERT @Tbl
(
StudentID ,
SAS_StudentInstancesID ,
Label ,
Value ,
CMN_PersonsID
)
SELECT
StudentID ,
SAS_StudentInstancesID ,
Label ,
Value ,
CMN_PersonsID
FROM MyView -- where MyView selects (with joins) the same columns from large table(s)RETURNENDUser Defined Functions
Written by Doug Carpenter on 12 October 2000
This article covers all the basics of User Defined Functions. It discusses how (and why) to create them and when to use them. It talks about scalar, inline table-valued and multi-statement table-valued functions. (This article has been updated through SQL Server 2005.)
With SQL Server 2000, Microsoft has introduced the concept of User-Defined Functions that allow you to define your own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.
What Kind of User-Defined Functions can I Create?
There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.
How do I create and use a Scalar User-Defined Function?
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.
CREATE FUNCTION whichContinent
(@Country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @Country
when 'Argentina' then 'South America'
when 'Belgium' then 'Europe'
when 'Brazil' then 'South America'
when 'Canada' then 'North America'
when 'Denmark' then 'Europe'
when 'Finland' then 'Europe'
when 'France' then 'Europe'
else 'Unknown'
end
return @return
end
Because this function returns a scalar value of a varchar(30) this function could be used anywhere a varchar(30) expression is allowed such as a computed column in a table, view, a T-SQL select list item. Below are some of the examples that I was able to use after creating the above function definition. Note that I had to reference the dbo in the function name.
print dbo.WhichContinent('USA')
select dbo.WhichContinent(Customers.Country), customers.*
from customers
create table test
(Country varchar(15),
Continent as (dbo.WhichContinent(Country)))
insert into test (country)
values ('USA')
select * from test
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Country Continent
--------------- ------------------------------
USA North America
Stored procedures have long given us the ability to pass parameters and get a value back, but the ability to use it in such a variety of different places where you cannot use a stored procedure make this a very powerful database object. Also notice the logic of my function is not exactly brain surgery. But it does encapsulate the business rules for the different continents in one location in my application. If you were to build this logic into T-SQL statements scattered throughout your application and you suddenly noticed that you forgot a country (like I missed Austria!) you would have to make the change in every T-SQL statement where you had used that logic. Now, with the SQL Server User-Defined Function, you can quickly maintain this logic in just one place.
How do I create and use an Inline Table-Value User-Defined Function?
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.
CREATE FUNCTION CustomersByContinent
(@Continent varchar(30))
RETURNS TABLE
AS
RETURN
SELECT dbo.WhichContinent(Customers.Country) as continent,
customers.*
FROM customers
WHERE dbo.WhichContinent(Customers.Country) = @Continent
GO
SELECT * from CustomersbyContinent('North America')
SELECT * from CustomersByContinent('South America')
SELECT * from customersbyContinent('Unknown')
Note that the example uses another function (WhichContinent) to select out the customers specified by the parameter of this function. After creating the user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets. Also note that I do not have to reference the dbo in my reference to this function. However, when using SQL Server built-in functions that return a table, you must now add the prefix :: to the name of the function.
Example from Books Online: Select * from ::fn_helpcollations()
How do I create and use a Multi-statement Table-Value User-Defined Function?
A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a T-SQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, I can use it in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.
CREATE FUNCTION dbo.customersbycountry ( @Country varchar(15) )
RETURNS
@CustomersbyCountryTab table (
[CustomerID] [nchar] (5), [CompanyName] [nvarchar] (40),
[ContactName] [nvarchar] (30), [ContactTitle] [nvarchar] (30),
[Address] [nvarchar] (60), [City] [nvarchar] (15),
[PostalCode] [nvarchar] (10), [Country] [nvarchar] (15),
[Phone] [nvarchar] (24), [Fax] [nvarchar] (24)
)
AS
BEGIN
INSERT INTO @CustomersByCountryTab
SELECT [CustomerID],
[CompanyName],
[ContactName],
[ContactTitle],
[Address],
[City],
[PostalCode],
[Country],
[Phone],
[Fax]
FROM [Northwind].[dbo].[Customers]
WHERE country = @Country
DECLARE @cnt INT
SELECT @cnt = COUNT(*) FROM @customersbyCountryTab
IF @cnt = 0
INSERT INTO @CustomersByCountryTab (
[CustomerID],
[CompanyName],
[ContactName],
[ContactTitle],
[Address],
[City],
[PostalCode],
[Country],
[Phone],
[Fax] )
VALUES ('','No Companies Found','','','','','','','','')
RETURN
END
GO
SELECT * FROM dbo.customersbycountry('USA')
SELECT * FROM dbo.customersbycountry('CANADA')
SELECT * FROM dbo.customersbycountry('ADF')
What are the benefits of User-Defined Functions?
The benefits to SQL Server User-Defined functions are numerous. First, we can use these functions in so many different places when compared to the SQL Server stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar User-Defined Function, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User-Defined function a very powerful tool.
Summary
So, if you have ever wanted to use the results of a stored procedure as part of a T-SQL command, use parameterized non-updateable views, or encapsulate complex logic into a single database object, the SQL Server 2000 User-Defined function is a new database object that you should examine to see if its right for your particular environment.
No comments:
Post a Comment