Wednesday, April 25, 2012

SQL Server : User Defined Functions


The ability to create a user-defined function (UDF) is a new feature added to SQL Server 2000. Developers have been asking Microsoft to add this feature for several versions of the product, so let's take a quick look at how to create a few simple UDFs to see if they can help you in your programming endeavors.

Creating a Simple UDF

A user-defined function is a database object that encapsulates one or more Transact-SQL statements for reuse. This definition is similar to the one for stored procedures, but there are many important differences between user-defined functions and stored procedures—the most pronounced being what types of data they can return. Let's create one so you can see how easy they are to create and reference.

The following statement shows how to create a function that accepts two input parameters, sums them together and then returns the sum to the calling statement.

CREATE FUNCTION fx_SumTwoValues  ( @Val1 int, @Val2 int )  RETURNS int  AS  BEGIN    RETURN (@Val1+@Val2)  END

The structure of the CREATE FUNCTION statement is fairly straightforward. You provide an object name (fx_SumTwoValues), input parameters (@Val1 and @Val2), the type of data the function will return () and the statement(s) the function executes are located between the BEGIN…END block. The following SELECT statement calls the function. Note that the two-part name (owner.object_name) is required when calling this function.

SELECT dbo.fx_SumTwoValues(1,2) AS SumOfTwoValues    SumOfTwoValues  --------------  3

When the SELECT is executed, the input parameters 1 and 2 are added together and the sum 3 is returned. You can use any values that either are, or can be, implicitly converted to an int data type for the input parameters. Keep in mind, though, that only an int can be returned, so the following statement will not produce the desired results.

SELECT dbo.fx_SumTwoValues(1.98,2.78) AS SumOfTwoValues    SumOfTwoValues  --------------  3

The function returns a 3, which indicates the decimal portion of the parameters are truncated before the calculation occurs.

SQL Server's ability to implicitly convert data allows the following to execute successfully.

SELECT dbo.fx_SumTwoValues('7','7') AS SumOfTwoValues    SumOfTwoValues  --------------  14

When values that cannot be converted to an int are passed to the function, the following error message is generated.

SELECT dbo.fx_SumTwoValues('Y','7') AS SumOfTwoValues    Server:Msg 245,Level 16,State 1,Line 1  Syntax error converting the varchar value 'Y'to a column of data type int.

Three Types of User-Defined Functions

Now that you have seen how easy it is to create and implement a simple function, let's cover the three different types of user-defined functions and some of the nuances of how they are implemented.

Scalar Functions

A scalar function returns a single value of the data type referenced in the RETURNS clause of the CREATE FUNCTION statement. The returned data can be of any type except text, ntext, image, cursor, or timestamp.

The example we covered in the previous section is a scalar function. Although the previous example only contained one statement in the BEGIN…END block, a scalar function can contain an unlimited number of statements as long as only one value is returned. The following example uses a WHILE construct to demonstrate this.

CREATE FUNCTION fx_SumTwoValues2  ( @Val1 int, @Val2 int )  RETURNS int  AS  BEGIN   WHILE @Val1 <100    BEGIN     SET @Val1 =@Val1 +1    END    RETURN (@Val1+@Val2)  END  go    SELECT dbo.fx_SumTwoValues2(1,7) AS SumOfTwoValues    SumOfTwoValues  --------------  107

The @Val1 input parameter is set to 1 when the function is called, but the WHILE increments the parameter to 100 before the RETURN statement is executed. Note that the two-part name (owner.object_name) is used to call the function. Scalar functions require that their two-part names be used when they are called. As you will see in the next two sections, this is not the case with the other two types of functions.

Inline Table-Valued Functions

An inline table-valued function returns a variable of data type table whose value is derived from a single SELECT statement. Since the return value is derived from the SELECT statement, there is no BEGIN/END block needed in the CREATE FUNCTION statement. There is also no need to specify the table variable name (or column definitions for the table variable) because the structure of the returned value is generated from the columns that compose the SELECT statement. Because the results are a function of the columns referenced in the SELECT, no duplicate column names are allowed and all derived columns must have an associated alias.

The following uses the Customer table in the Northwind database to show how an inline table-valued function is implemented.

USE Northwind  go  CREATE FUNCTION fx_Customers_ByCity  ( @City nvarchar(15) )  RETURNS table  AS  RETURN (          SELECT CompanyName          FROM Customers          WHERE City =@City  		 )  go  SELECT * FROM fx_Customers_ByCity('London')    CompanyName  ----------------------------------------  Around the Horn  . . .   Seven Seas Imports

Multi-Statement Table-Valued Functions

The multi-statement table-valued function is slightly more complicated than the other two types of functions because it uses multiple statements to build the table that is returned to the calling statement. Unlike the inline table-valued function, a table variable must be explicitly declared and defined. The following example shows how to implement a multi-statement table-valued function that populates and returns a table variable.

USE Northwind  go  CREATE FUNCTION fx_OrdersByDateRangeAndCount  ( @OrderDateStart smalldatetime,     @OrderDateEnd smalldatetime,     @OrderCount smallint )  RETURNS @OrdersByDateRange TABLE    (  CustomerID nchar(5),       CompanyName nvarchar(40),       OrderCount smallint,       Ranking char(1) )  AS  BEGIN   --Statement 1   INSERT @OrdersByDateRange   SELECT a.CustomerID,          a.CompanyName,          COUNT(a.CustomerID)AS OrderCount,         'B'   FROM Customers a   JOIN Orders b ON a.CustomerID =b.CustomerID   WHERE OrderDate BETWEEN @OrderDateStart AND @OrderDateEnd   GROUP BY a.CustomerID,a.CompanyName   HAVING COUNT(a.CustomerID)>@OrderCount    --Statement 2  UPDATE @OrdersByDateRange  SET Ranking ='A'  WHERE CustomerID IN (SELECT TOP 5 WITH TIES CustomerID                       FROM (SELECT a.CustomerID,                                    COUNT(a.CustomerID)AS OrderTotal                             FROM Customers a                             JOIN Orders b ON a.CustomerID =b.CustomerID                             GROUP BY a.CustomerID) AS DerivedTable                             ORDER BY OrderTotal DESC)  RETURN  END

The main difference between this example and the one in the previous section is that we were required to specify the structure of the @OrdersByDateRange table variable used to hold the resultset and list @OrdersByDateRange in the RETURNS clause. As you can see from the input parameter list, the function accepts a start date, an end date and an order count value to filter the resultset.

The first statement (--Statement 1) uses the input parameters to populate the table variable with customers who meet the specified criteria. The second statement (-Statement 2) updates the rows in table variable to identify the top five overall order placers. The IN portion of the UPDATE may seem a little confusing at first glance, but all its doing is using a derived table to select the CustomerID values of the top five order producers. Derived tables are discussed in Chapter 4. You can use the following to find the companies who have submitted more than two orders between 1/1/96 and 1/1/97.

SELECT *  FROM fx_OrdersByDateRangeAndCount ('1/1/96','1/1/97',2)  ORDER By Ranking    CustomerID  CompanyName                      OrderCount   Ranking  ----------  ------------------------------   ----------   -------  ERNSH       Ernst Handel                      6           A  FOLKO       Folk och fä HB                    3           A  HUNGO       Hungry Owl All-Night Grocers      5           A  QUICK       QUICK-Stop                        6           A  SAVEA       Save-a-lot Markets                3           A  SEVES       Seven Seas Imports                3           B  SPLIR       Split Rail Beer &Ale              5           B  ...

The rows ranking values of 'A' indicate the top five order placers of all companies. The function allows you to perform two operations with one object. Retrieve the companies who have placed more than two orders between 1/1/96 and 1/1/97 and let me know if any of these companies are my top five order producers.

One of the advantages of using this type of function over a view is that the body of the function can contain multiple SQL statements to populate the table variable, whereas a view is composed of only one statement. The advantage of using multi-statement table-valued function versus a stored procedure is that the function can be referenced in the FROM clause of a SELECT statement while a stored procedure cannot. Had a stored procedure been used to return the same data, the resultset could only be accessed with the EXECUTE command.

A Real-World Example

Now that you have an idea of the different types of functions available in SQL Server 2000, let's wrap up this article with an example you might be able to use on one of your projects. The following statements create a function that determines the last day of the month (LDOM) for a given date parameter.

CREATE FUNCTION fx_LDOM  (  @Date varchar(20) )  RETURNS datetime  AS  BEGIN   --ensure valid date   IF ISDATE(@Date) = 1    BEGIN     --determine first day of month     SET @Date = DATEADD(day,-DAY(@Date)+1,@Date)     --determine last day of month     SET @Date = DATEADD(day,-1,DATEADD(month,1,@Date))    END   ELSE    SET @Date = '1/1/80'      RETURN @Date  END

The function's parameter (@Date) is defined as varchar(20), so error-checking code can be implemented. The ISDATE function makes sure the supplied value is a valid date. When an invalid date value is supplied, the function returns '1/1/80' to the calling statement. If you do not use this type of error-checking, the call to the function will fail when an invalid date is supplied.

When a valid date value is supplied, the DATEADD function is used to:

  1. Determine the first day of the month, and
  2. Determine the last day of the month.

If you have never used DATEADD before this may seem a little confusing, but a quick explanation should eliminate any that might exist. You use DATEADD to add or substract a date/time unit from a given date. The first parameter (in this case 'day') indicates the portion of the date that should be incremented. You can also specify year, quarter, month...millisecond. The second parameter is the number of units to add or substract. When subtracting, you simply make the value negative as shown in the example. The third parameter is the date value on which the calculation is performed.

The first day of the month is determined by calculating the number of elapsed days in the supplied parameter with the DAY function, adding 1 and then substracting it from the parameter. For an @Date value of 1/15/01, it simply subtracts 14 (15-1) days to get 1/1/01.

The last day of the month is determined by adding 1 month to the current month value and subtracting one day. So, 1/1/01 plus 1 month is equal to 2/1/01 and when you substract one day you get: 1/31/01.

The following shows how fx_LDOM is used in a SELECT statement to calculate the number of days remaining in a month.

CREATE TABLE fx_Testing (DateValue datetime)  go  INSERT fx_Testing VALUES ('1/1/01')  INSERT fx_Testing VALUES ('2/15/01')  INSERT fx_Testing VALUES ('2/15/02')  INSERT fx_Testing VALUES ('2/15/03')  INSERT fx_Testing VALUES ('2/15/04')    SELECT DateValue,         dbo.fx_LDOM(DateValue) AS LDOM,         DATEDIFF(day,DateValue,dbo.fx_LDOM(DateValue)) AS DaysLeftInMonth  FROM fx_Testing    DateValue                 LDOM                    DaysLeftInMonth  ------------------------  ----------------------- ---------------  2001-01-01 00:00:00.000   2001-01-31 00:00:00.000  30  2001-02-15 00:00:00.000   2001-02-28 00:00:00.000  13  2002-02-15 00:00:00.000   2002-02-28 00:00:00.000  13  2003-02-15 00:00:00.000   2003-02-28 00:00:00.000  13  2004-02-15 00:00:00.000   2004-02-29 00:00:00.000  14

The DATEDIFF function is used to determine the difference between two dates. In this case, the number of days between the value in the DateValue column and the last day of the month calculated by fx_LDOM.

Friday, April 13, 2012

Running Multiple Tomcat Instances

We first see the  tomcat directory structure. .

here each folder uses following purpose.

bin -  It contains all binary and script files for running tomcat.

lib - contains all shared libraries used for tomcat

conf - contains configuration information like which port tomcat can bind , etc...

logs - it contain all logging details

temp - this folder tomcat used for temporary files purpose

webapps - this folder is very important. here we put all application war files.

work - If application contain any jsp then jsp is translated and converted into servlet its stores here.

In when run the tomcat its uses 5 environment variables. They are 

in above list CATALINA_HOME and JAVA_HOME is mandatory environment variables. all others are optional and its can be calculated using CATALINA_HOME.

CATALINA_HOME - this environment variable should point to tomcat base folder, where tomcat binary are  installed/extracted. so based on CATALINA_HOME we can get bin and lib folder

CATALINA_BASE - If we not specified then CATALINA_HOME value is set. This variable pointed to configuration and webapps folder. Based on this variable server uses conf, logs, temp, webapps, work folders.

Usual ways to run the tomcat is only set CATALINA_HOME environment variable. and run the script file. this file automatically calculate and assign the values of other variables what we are not set. file set the environment variable and  then call file. this files is read CATALINA_BASE value and attach conf i.e $CATALINA_BASE/conf folder and get server.xml. this file is heart of tomcat. it contains all configuration information. like which tomcat uses as shoutdown port, connector post, host name, application folder ,.. for example usually tomcat uses 8080 is a connector port, so we can access http://localhost:8080/ 

if we set the $CATALINA_BASE explicitly then tomcat uses our variable to search and get the server.xml file from our target place, what we specified in CATALINA_BASE. 

this is trick to run multiple tomcat in single machine. we don't change CATALINA_HOME value. we need to change CATALINA_BASE value before start/shutdown the tomcat.

create one folder named "tomcat-instance1" anywhere, and copy conf, logs, temp, webapps, work folder from CATALINA_HOME folder and change conf/server.xml file in tomcat-instance1. we need to change 3 port shutdown port, connector port and ajp port.

shutdown port - this port is used for shutdown the tomcat. when we call the script they send signal to shutdown port. this port listen by tomcat java process. if signal is received the that process then its cleanup and exit by itself.

connector Port -This port is actual port to expose the application to outside client. 

ajp port - this port is used to apache httpd server  communicate to tomcat. this port used when we setup load balanced server.

see the sample server.xml file
.... ..

  1. <server port="8005" shutdown="SHUTDOWN">  
  2. .....  
  3.  <connector connectiontimeout="20000" port="8080" protocol="org.apache.coyote.http11.Http11NioProtocol" redirectport="8443">  
  5. <connector port="8009" protocol="AJP/1.3" redirectport="8443">  
  6. </connector></connector></server>  
so we change these three port to different number, because once this port is binded  then other process can't bind it again. so wee bind different port. so tomcat-instance1/conf/server.xml file i configured server port =8105, connector port = 8181, ajp port = 8109.
  1. <server port="8105" shutdown="SHUTDOWN">  
  2. .....  
  3.  <connector connectiontimeout="20000" port="8181" protocol="org.apache.coyote.http11.Http11NioProtocol" redirectport="8443">  
  4. <connector port="8109" protocol="AJP/1.3" redirectport="8443">  
  5. </connector></connector></server>  

now we can create two script file for startup and shutdown the tomcat-instance1.
export CATALINA_BASE= /home/ramki/tomcat-instance1
export CATALINA_BASE= /home/ramki/tomcat-instance1

here we explicitly set the CATALINA_BASE variable and point to new tomcat-instance1
the we go to CATALINA_HOME/bin folder because all binary for running tomcat is still present in CATALINA_HOME folder then startup/shutdown the script.

Based on above technique we can create many instance folder and change conf/server.xml file port values and run that instance with own newly created script files.


Thursday, April 12, 2012


The CHARINDEX and PATINDEX functions are used to search a character string for a character or set of characters. If the character string being searched contains the characters being searched for, then these functions return a non-zero integer value. This integer value is the starting location of where the character string being searched for is located within the string being searched. The PATINDEX function allows for using wildcard syntax in the pattern being searched for, where as the CHARINDEX function does not support wildcard character searches. Let's look at each one of these functions in a little more detail.

How to use the CHARINDEX Function

The CHARINDEX function returns the starting position of a character, or a string of characters within another character string. The CHARINDEX function is called using the following format:

CHARINDEX ( expression1 , expression2 [ , start_location ] )

Where expression1 is the string of characters to be found in expression2, and start_location is the position where the CHARINDEX function will start looking for expression1 in expression2.

The CHARINDEX function returns an integer. The integer value returned is the position where the characters being search for are located within the string being searched. If the CHARINDEX does not find the characters you are searching for then the function returns a zero integer value. Let say we execute the following CHARINDEX function call:

	CHARINDEX('SQL', 'Microsoft SQL Server')

This function call will return the starting location of the character string "SQL", in the string "Microsoft SQL Server". In this case the CHARINDEX function will return the number 11, which as you can see is the starting position of "S" in string "Microsoft SQL Server".

Now say we have the following CHARINDEX Command:

	CHARINDEX('7.0', 'Microsoft SQL Server 2000')

In this example the CHARINDEX function will return zero, since the character string "7.0" cannot be found in the string "Microsoft SQL Server 2000". Let go through a couple of examples of how you might be able to use the CHARINDEX function to solve some actual T-SQL problems.

For the first example say you would like to display only the last name of the ContactName column, for the first 5 records in the Northwind database Customer table. Here are the first 5 records.

	ContactName 	------------------------------  	Maria Anders 	Ana Trujillo 	Antonio Moreno 	Thomas Hardy 	Christina Berglund 

As you can see, the CustomerName contains both the first and last name of the customer, where first and last name are separated by a single space. I will use the CHARINDEX function to identify the position of the space between the two names. This way we can used the position of the space to parse the ContactName so we can display only the last name portion of the column. Here is some T-SQL code to display only the last name for the first 5 records in the Northwind Customer table.

	select top 5 substring(ContactName,                        charindex(' ',ContactName)+1 ,                        len(ContactName)) as [Last Name]        from Northwind.dbo.customers 

Here is the output from this command:

	Last Name 	------------------------------  	Anders 	Trujillo 	Moreno 	Hardy 	Berglund 

The CHARINDEX function found the space between the First and Last Name, so that the substring function could split the ContactName, thus only the Last Name was displayed. I added 1 to the integer value that CHARINDEX returned, so the Last Name displayed did not start with a space.

For the second example, say you want to count all the records from a table where a given column contains a particular character string. The CHARINDEX function could be used to satisfy your request. To count all of the Addresses in the Northwind.dbo.Customer table where the Address column contains either the word 'Road' or an abbreviation for road ('Rd'), your SELECT statement would look like this:

	select count(*) from Northwind.dbo.Customers  	  where CHARINDEX('Rd',Address) > 0 or CHARINDEX('Road',Address) 	> 1

How Does the PATINDEX Function Work?

The PATINDEX function returns the starting position of a character or string of characters within another string, or expression. As stated earlier the PATINDEX has additional functionality over CHARINDEX. PATINDEX supports wildcard characters in the search pattern string. This makes PATINDEX valuable for searching for varying string patterns. The PATINDEX command takes the following form:

PATINDEX ( '%pattern%' , expression )

Where "pattern" is the character string you are searching for and expression is the string in which you are searching. Commonly the expression is a column in a table. The "%" sign is needed on the front and back of the pattern, unless you are searching for the pattern at the beginning and/or ending of the expression.

Like the CHARINDEX function, the PATINDEX function returns the starting position of the pattern within the string being searched. If you have a PATINDEX function call like so:


Then the result of the PATINDEX function call is 2, which is the same as the CHARINDEX function. The %'s in the above command tells the PATINDEX command to find the position of the "BC" string where the string might have zero or more characters in front of, or after "BC". The % sign is a wildcard character.

If you want to determine if a string starts with a specific set of characters you would leave off the first % sign, and your PATINDEX call would look like this:


In this case the PATINDEX function returns a 1, indicating that the pattern 'AB' was found in the expression 'ABCD'.

Now with wildcard characters you can create a much more complicated pattern matching situation then the simple ones I have shown you so far. Say you want to determine if a character string contains the letters A and Z, as well as any numeric number. Then your PATINDEX function call might look like this.


Note that the pattern I am looking for in the above example uses a number of wildcard references. Review SQL Server Books Online for other wildcard syntax information. Let's go through a couple of examples in how we might use the PATINDEX command in conjunction with a SELECT statement.

Say you want to find all of the records that contain the words "Bread", or "bread" in the Description text column in the Northwind.dbo.Categories table, then your select statement would look like this:

	select Description from Northwind.dbo.Categories 	  where patindex('%[b,B]read%',description) > 0

Here I used wildcarding to look for either a lower or upper case "b". When I run this SELECT statement against my Northwind database I get the following Description columns displayed:

	Description 	-------------------------------------------------------- 	Desserts, candies, and sweet breads 	Breads, crackers, pasta, and cereal 

Here is another example where I used some additional wildcard references to find some records. This example excludes the Description that has an 'e' as the second letter from the result set in the above example.

	select Description from Northwind.dbo.Categories 	  where patindex('%[b,B]read%',description) > 0 	    and patindex('_[^e]%',description) = 1 

By adding an additional PATINDEX function call, to the where statement, that used the ^ wildcard symbol, I was able to exclude the "Dessert, candies, and sweet breads" description. The above example returned the following single description.

	Description 	-------------------------------------------------------- 	Breads, crackers, pasta, and cereal   


As you can see the CHARINDEX and the PATINDEX perform similar kinds of pattern searches within a character string. The PATINDEX function provides wildcard specifications, allowing it to be used for much different kinds of pattern matches, while the CHARINDEX function does not. Depending on what you need to do, these two functions are great in helping you search, manipulate and parse character strings in SQL Server.