SQL Server - Create Computed Column using User Defined Functions (UDFs)

User defined functions (UDFs) are small programs that you can write to perform an operation. Adding functions to the Transact SQL language has solved many code reuse issues and provided greater flexibility when programming SQL queries.

According to SQL Server Books Online, User defined functions (UDFs) in SQL Server 2000 can accept anywhere from 0 to 1024 parameters.

User defined functions (UDFs) are either scalar-valued or table-valued. Functions are scalar-valued if the RETURNS clause specified one of the scalar data types. Functions are table-valued if the RETURNS clause specified TABLE.

There are number of reasons to use user defined functions (UDFs), but this time I will share about the uses of scalar function to create a computed column. Scalar functions can be used to compute column values in table definitions. Arguments to computed column functions must be table columns, constants, or built-in functions. This example shows a table that uses a Volume function to compute the volume of a box:


-- Create function statement
CREATE FUNCTION BoxVolume
(
@BoxHeight decimal(4,1),
@BoxLength decimal(4,1),
@BoxWidth decimal(4,1)
)
RETURNS decimal(12,3)
AS
BEGIN
RETURN ( @BoxLength * @BoxWidth * @BoxHeight )
END
GO

-- Create table statement
CREATE TABLE [dbo].[BoxTable]
(
[BoxPartNmbr] [int] NOT NULL ,
[BoxColor] [varchar] (20) NULL,
[BoxHeight] [decimal](4, 1) NULL ,
[BoxLength] [decimal](4, 1) NULL ,
[BoxWidth] [decimal](4, 1) NULL ,
[BoxVol] AS ([dbo].[BoxVolume]([BoxHeight], [BoxLength], [BoxWidth]))
) ON [PRIMARY]
GO

-- Insert some data
INSERT INTO BoxTable(BoxPartNmbr,BoxColor,BoxHeight,BoxLength,BoxWidth)
VALUES (1,'RED',2,2,2)
GO
INSERT INTO BoxTable(BoxPartNmbr,BoxColor,BoxHeight,BoxLength,BoxWidth)
VALUES (2,'GREEN',3,3,3)
GO
INSERT INTO BoxTable(BoxPartNmbr,BoxColor,BoxHeight,BoxLength,BoxWidth)
VALUES (3,'BLUE',4,4,4)
GO

You must remember that a computed columns might be excluded from being indexed. An index can be created on the computed column if the user defined function is always returns the same value given the same input (deterministic).

With UDFs, you can more easily accommodate the unique requirements of a custom application. They increase functionality while often reducing your development effort.

How to Connect to SQL Server 2000 with PHP Using php_mssql.dll

PHP is powerful tool for build up your web applications because PHP is an easy to learn. SQL server is a Microsoft’s robust database product, which can handle terabytes of your data.

In web database application, usually some web programmers use PHP to connect to MySQL database. But in the other side, there are many web programmers looking for information how to connecting PHP to SQL Server 2000.

Well, this time I’ll share about how to connecting PHP to SQL Server using php_mssql.dll.

I use Windows Vista 32 bit for the OS, SQL Server 2000, PHP version 5.2.3 and AppServ version 2.5.9 for Windows for Web Server.

The php_mssql.dll file is already exists in extension or ext directory of your PHP installation folder. Before you can use the php_mssql.dll, you must make a modification in php.ini file, which is placed in C:\Windows directory, or If you’re using php before version 5, the php.ini file is in php installation folder.

• Open the php.ini file using Notepad or WordPad.
• In Notepad window, press Ctrl + F to find this word: “extension=php_mssql.dll”.
• Delete the semicolon mark (;).

• Save the php.ini file.
• Restart the web server using "net stop apache" and then "net start apache" from your computer DOS promp. Or maybe you need to restart your computer.

Now, let’s test the php_mssql.dll, it is already run perfectly or not. You can check by using phpinfo() function. Type this address in your browser: http://localhost/phpinfo.php



In the PHP configuration information, you should see something like this:

Now, your Apache web server is ready to make a connection between PHP and SQL Server 2000 using php_mssql.dll.

Create connect.php file. This file is example how to connect PHP to SQL Server 2000.

$host = "rafa-comp"; //set the host with server name or ip address
$user = "sa"; //set the user id
$password =""; //set the user password (blank password is not recomended)
$database = "Northwind"; //set the database to use
if (mssql_connect($host, $user, $password)) {
echo 'Connection to SQL Server success, ';
if (mssql_select_db($database)) {
echo "$database database is selected"; }
else {
echo 'database not found';}
}
else {
echo 'Sorry my bro, connection to SQL Server failed';
}


Save the connect.php file in your web root directory. Run it through your browser. Your browser return should look like this:

OK, I hope this onformation is usefull. Have a nice day :-)