SqlServer 2005 Date And Time Function

DATEPART Function

The DATEPART function allows retrieving any part of the date and time variable provided. This function is deterministic except when used with days of the week.

The DATEPART function takes two parameters: the part of the date that you want to retrieve and the date itself. The DATEPART function returns an integer representing any of the following parts of the supplied date: year, quarter, month, day of the year, day, week number, weekday number, hour, minute, second, or millisecond.

For example, suppose that you want to retrieve the week number of January  1,2012 and December 31, 2012 . You can use the following code:

SELECT DATEPART(WEEK, 'Dec 31 2012');
Output : 53
SELECT DATEPART(WEEK, 'Jan 1 2012');
Output : 1

Similarly, if you want to know which weekday a particular date falls on, you can use the WEEKDAY keyword with the DATEPART function:

SELECT DATEPART(WEEKDAY, 'Jan 8 2012' )
output:1

Here sun=1 mon=2 tue=3 wed=4 thu=5 fri=6 and sat=7

DATENAME Function

The DATENAME nondeterministic function returns the name of the portion of the date and time variable. Just like the DATEPART function, the DATENAME function accepts two parameters: the portion of the date that you want to retrieve and the date. The DATENAME function can be used to retrieve any of the following: name of the year, quarter, month, day of the year, day, week, weekday, hour, minute, second, or millisecond of the specified date. For instance, you can determine the weekday name as well as the month name of a given date as follows:

SELECT DATENAME(WEEKDAY, '1-26-2012' ) as day,DATENAME (MONTH, '1-26-2012' ) as month
output:
 day month 
 Thursday   January

DAY, MONTH, and YEAR Functions

DAY, MONTH and YEAR functions are deterministic. Each of these accepts a single date value as a parameter and returns respective portions of the date as an integer. The following example shows how various portions of the date and time value can be retrieved using these functions:

SELECT DAY('Jan 8 2012'),MONTH('Jan 8 2012'),YEAR('Jan 8 2012')
output: 
day month year
8	1	2012

DAY, MONTH and YEAR functions are functionally equivalent to executing DATEPART function with day, month or year as the first parameter, respectively.

GETDATE and GETUTCDATE Functions

GETDATE and GETUTCDATE functions both return the current date and time. However, GETUTCDATE returns the current Universal Time Coordinate (UTC) time, whereas GETDATE returns the date and time on the computer where SQL Server is running. The GETUTCDATE() function compares the time zone of SQL Server computer with the UTC time zone. Neither of these functions accepts parameters, and they are both non-deterministic. Here is an example:

SELECT   GETDATE() AS local_date,   GETUTCDATE() AS UTC_date
output: 
local_date UTC_date
2012-01-07 12:05:30.043	2012-01-07 06:35:30.043

DATEADD Functions

DATEADD function is deterministic; it adds a certain period of time to the existing date and time value. For instance, the following query determines the date 49 months from the current date:

SELECT DATEADD(day, 359, GETDATE())AS '359_Days_from_now';
SELECT DATEADD(WEEK, 51, GETDATE())AS '51_Weeks_from_now';
SELECT DATEADD(month, 11, GETDATE())AS '11_Months_from_now';
output:
359_Days_from_now
2012-12-31 12:12:47.107

51_Weeks_from_now
2012-12-29 12:12:47.107

11_Months_from_now
2012-12-07 12:12:47.107

DATEADD is also often used to determine which rows qualify for a particular report.For example, the following report retrieves details on past two years by using (-2) as the second DATEADD parameter:

SELECT   Date from kiran WHERE  date > =  DATEADD(YEAR, -2, GETDATE())
Output: 
Date
2012-01-31 00:00:00.000
2012-01-31 00:00:00.000

DATEDIFF Function

DATEDIFF function is deterministic; it accepts two DATETIME values and a date portion (minute, hour, day, month, etc) as parameters. DATEDIFF() determines the difference between the two date values passed, expressed in the date portion specified. Notice also that start date should come before the end date, if you’d like to see positive numbers in the result set.

DATEDIFF will work even if the end date is earlier than the start date – you will simply see the negative values in the output. Also keep in mind that DATEDIFF returns an INTEGER – it does not calculate fractions for you. This can cause unexpected results, like in the following query:

SELECT   DATEDIFF (YEAR, '1/1/2012', '1/1/2013') as yeardiff,
DATEDIFF (month, '1/1/2012', '12/31/2012') as monthdiff,
DATEDIFF (day, '1/1/2012', '12/31/2012') as datesdiff
output:
 yeardiff monthdiff datesdiff
 1	  11	     365

so if you care about accuracy then be sure to use the smallest fraction of the time possible when using DATEDIFF.

Enjoy The Code Keep Rocking….!

SQL SERVER – 2005 – Last Ran Query – Recently Ran Query

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Calling server side methods using JavaScript and JQuery in ASP.Net

This is asked many times on asp.net forums,

1. How to call Server Side methods or functions using JavaScript in ASP.Net

2. How to call Server Side methods Client Side in ASP.Net

Now the answer is using jQuery.

jQuery allows you to call Server Side ASP.net methods from client side without any PostBack. Actually it is an AJAX call to the server but it allows us to call the method or function defined server side.

 

Syntax

The figure below describes the syntax of the call.

 

HTML Markup

<div>

Your Name :

<asp:TextBox ID=”txtUserName” runat=”server”></asp:TextBox>

<input id=”btnGetTime” type=”button” value=”Show Current Time”

    onclick = ”ShowCurrentTime()” />

</div>

 

As you noticed above I have added a textbox when user can enter his name and a TML button that calls a JavaScript method to get the Current Time.

 

Client Side Methods

<script src=”scripts/jquery-1.3.2.min.js” type=”text/javascript”></script>

<script type = ”text/javascript”>

function ShowCurrentTime() {

    $.ajax({

        type: ”POST”,

        url: ”CS.aspx/GetCurrentTime”,

        data: ’{name: “‘ + $(“#<%=txtUserName.ClientID%>”)[0].value + ’” }’,

        contentType: ”application/json; charset=utf-8″,

        dataType: ”json”,

        success: OnSuccess,

        failure: function(response) {

            alert(response.d);

        }

    });

}

function OnSuccess(response) {

    alert(response.d);

}

</script>

 

Above the ShowCurrentTime method makes an AJAX call to the server and executes the GetCurrentTime method which accepts the username and returns a string value.

 

Server Side Methods

C#

[System.Web.Services.WebMethod]

public static string GetCurrentTime(string name)

{

    return ”Hello “ + name + Environment.NewLine + ”The Current Time is: “

        + DateTime.Now.ToString();

}

 

VB.Net

<System.Web.Services.WebMethod()> _

Public Shared Function GetCurrentTime(ByVal name As String) As String

   Return ”Hello “ & name & Environment.NewLine & ”The Current Time is: “ & _

            DateTime.Now.ToString()

End Function

 

The above method simply returns a greeting message to the user along with the current server time. An important thing to note is that the method is declared asstatic (C#) and Shared(VB.Net) and also it is declared as Web Method unless you do this you won’t be able to call the methods

 

The figure below displays the output displayed to the user when the button is clicked

SQL Tuning or SQL Optimization

Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best query is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.

SQL Tuning/SQL Optimization Techniques:

1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than ‘*’.

For Example: Write the query as

SELECT id, first_name, last_name, age, subject FROM student_details;

Instead of:

SELECT * FROM student_details;

2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes.
For Example: Write the query as

SELECT subject, count(subject)
FROM student_details
WHERE subject != 'Science'
AND subject != 'Maths'
GROUP BY subject;

Instead of:

SELECT subject, count(subject)
FROM student_details
GROUP BY subject
HAVING subject!= 'Vancouver' AND subject!= 'Toronto';

3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
For Example: Write the query as

SELECT name
FROM employee
WHERE (salary, age ) = (SELECT MAX (salary), MAX (age)
FROM employee_details)
AND dept = 'Electronics';


Instead of:

SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee_details)
AND age = (SELECT MAX(age) FROM employee_details)
AND emp_dept = 'Electronics';

4) Use operator EXISTS, IN and table joins appropriately in your query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.

For Example: Write the query as

Select * from product p
where EXISTS (select * from order_items o
where o.product_id = p.product_id)

Instead of:

Select * from product p
where product_id IN
(select product_id from order_items

5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.
For Example: Write the query as

SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:

SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

6) Try to use UNION ALL in place of UNION.
For Example: Write the query as

SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;

Instead of:

SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;

7) Be careful while using conditions in WHERE clause.
For Example: Write the query as

SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:

SELECT id, first_name, age FROM student_details WHERE age != 10;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE 'Chan%';


Instead of:

SELECT id, first_name, age
FROM student_details
WHERE SUBSTR(first_name,1,3) = 'Cha';


Write the query as

SELECT id, first_name, age
FROM student_details
WHERE first_name LIKE NVL ( :name, '%');


Instead of:

SELECT id, first_name, age
FROM student_details
WHERE first_name = NVL ( :name, first_name);


Write the query as

SELECT product_id, product_name
FROM product
WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)

Instead of:

SELECT product_id, product_name
FROM product
WHERE unit_price >= MAX(unit_price)
and unit_price <= MIN(unit_price)

Write the query as

SELECT id, name, salary
FROM employee
WHERE dept = 'Electronics'
AND location = 'Bangalore';

Instead of:

SELECT id, name, salary
FROM employee
WHERE dept || location= 'ElectronicsBangalore';

Use non-column expression on one side of the query because it will be processed earlier.

Write the query as

SELECT id, name, salary
FROM employee
WHERE salary < 25000;

Instead of:

SELECT id, name, salary
FROM employee
WHERE salary + 10000 < 35000;

Write the query as

SELECT id, first_name, age
FROM student_details
WHERE age > 10;

Instead of:

SELECT id, first_name, age
FROM student_details
WHERE age NOT = 10;

8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause.
For Example: Write the query as

SELECT id FROM employee
WHERE name LIKE 'Ramesh%'
and location = 'Bangalore';

Instead of:

SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee
WHERE name LIKE 'Ramesh%';

9) To store large binary objects, first place them in the file system and add the file path in the database.

10) To write queries which provide efficient performance follow the general SQL standard rules.

a) Use single case for all SQL verbs
b) Begin all SQL verbs on a new line
c) Separate all words with a single space
d) Right or left aligning verbs within the initial SQL verb

SQL Server 2005 Backup Types

Full, Transaction Log, Differential, Partial, Differential Partial, File and Filegroup, and Copy Only Database Backups

• Full Database Backup
• Transaction Log Backup
• Differential Database Backup
• Partial Database Backup
• Differential Partial Database Backup
• File and Filegroup Backups
• Copy Only Database Backup

Full Database Backup

A full database backup captures the entire database in one fell swoop. It can be used to restore a database to the time when the backup completed. A full database backup is often used during restore operations as a first step in recovering a database. Other backups are applied after the full backup is restored to bring the database closer in time to the actual moment of failure.

Data Loss:

A full database backup will not on its own provide full recovery up to the point of failure. A full database backup for SQL Server 2005 restores the database up to the time when the backup was completed. If you have more stringent data loss requirements – and most organizations will – you need to supplement your full database backups with other types of backups. Your best chance at preventing data loss is to use the full recovery model with transaction logs.

Size:

A full database backup will be the largest type of backup that there is.

Resources Required:

A full database backup requires a lot of system resources and can negatively impact system performance.

Transaction Log Backup

Only available under the full recovery model and the bulk logged recovery model. Transaction log backups allow for point in time recovery since each transaction is captured within the transaction log.

Data Loss:

The use of transaction log backups provides point in time recovery. As far as backups are concerned, the use of full backups combined with transaction logs can provide point in time recovery capabilities and minimize data loss.

Size:

Compared to other types of database backups, transaction log backups aren’t very large if they are scheduled regularly.

Resources Required:

Generally speaking, transaction log backups take up very little in the way of system resources.

Differential Database Backup

A differential database backup captures changes (and only the changes) made to the database since the last full database backup. This full database backup which differential backups reference is also called the differential base, since the differential backups are recording changes since the last full database backup was performed. The differential database backup records the changes to the database up to the time when the differential backup was completed.

Data Loss:

A differential backup provides additional protection from data loss than a full database backup, but does not on its own guarantee that some data won’t be lost. Your best chance at preventing data loss is to use the full recovery model with transaction logs.

Size:

The differential database backup will likely be a fraction of the size of the full database backup since only changes since the last full database backup are saved. However, if you have multiple differential database backups between each full database backup, each differential database backup will be larger than the one before it, since all differential database backups record ALL changes since the last full database backup.

Resources Required:

The overhead required to perform a differential backup is significantly less than what is required for a full database backup since only the changes since the last full database backup are being backed up.

Partial Database Backup

Partial backups were introduced for SQL Server 2005. Partial backups will backup the primary filegroup, any read/write file groups, and any read-only files that are specified during the backup. The partial backup is designed to not backup any data that is placed in a read-only file group. So, you can use a partial backup to backup your entire database – except for the read only data. Partial backups are meant to be used in a simple recovery model situation, although they can be used in a full recovery model situation as well.

Data Loss:

The partial backup provides bare minimum protection from data loss. The partial backup serves as a base for differential partial backups – which can be applied to the partial backup to bring the database closer to a point in time near the actual database failure. A full recovery model with transaction logs provides the best chance to restore the database up to the point of failure.

Size:

A key advantage of using a partial backup as opposed to a full database backup is the reduced file size. The read only filegroups only have to be saved once, therefore the partial backups will be smaller than what would be required for a full database backup.

Resources Required:

The strain on the database will likely be much less for a partial database backup than it is for a full database backup since specified read only filegroups won’t have to be backed up.

Differential Partial Database Backup

Differential partial backups use the most recent partial backup as their differential base. They work very similar to a differential backup, except (with some exceptions) they capture only changes made to read/write filegroups .

If you add, drop, or change the status of any of your filegroups, you’re best off taking a partial backup prior to resuming differential partial backups. Filegroup changes can have unintended consequences with differential partial backups.

Data Loss:

Differential Partial provide a reduced chance of data loss from a partial backup alone, since the differential partial backups are performed between partial backups.

Size:

A differential partial backup should be relatively small given that it won’t backup specified read only filegroups and uses the most recent partial backup as its base.

Resources Required:

The resources required for a differential partial backup will be relatively small compared to full database backups and partial backups.

File and Filegroup Backups

This primarily applies to very large databases (VLDB) with multiple filegroups that have strict availability requirements. In these instances, sometimes it takes too long to perform a full backup, or the size of a full backup is extremely large.

If you add, drop, or change the status of any of your filegroups, you’re best off taking a partial backup prior to resuming differential partial backups. Filegroup changes can have unintended consequences with differential partial backups.

Data Loss:

Data loss is minimized with file and filegroup backups through the proper use of transaction log backups. File and Filegroup backups can only be performed under a full recovery model or a bulk logged recovery model.

Size:

The size of individual file or filegroup backups will obviously be smaller than the size required for a full database backup – which is one of the key advantages of using file and filegroup backups in VLDB situations.

Resources Required:

While there is overhead involved in backing up any portion of a VLDB, the resources will likely be much less than the resources required for performing a full database backup on a very large database.

Copy Only Database Backup

Copy Only backups in SQL Server 2005 must be performed using T-SQL and are not logged in the backup and restore sequence for the database. In other words, you can’t apply differential backups to this database backup, it is an independent copy separate from the original database. The transaction log is unaffected (will not be truncated) during this type of backup. You might consider making a copy only backup for a special purpose – like database development or testing.

Data Loss:

Not applicable. A copy only database backup is used to make a copy of the database and isn’t part of a backup and recovery plan.

Size:

Not Applicable. Not part of the overall backup and recovery plan.

Resources Required:

Resources required for a Copy Only backup would be similar to a full database backup.

which is best Language to Develop Web Applications…?

SQL Functions

Types of Functions

There are several basic types and categories of functions in SQL99 and vendor implementations of SQL. The basic types of functions are:

Aggregate functions
Operate against a collection of values, but return a single, summarizing value.
Scalar functions
Operate against a single value, and return a single value based on the input value. Some scalar functions, CURRENT_TIME for example, do not require any arguments.

Aggregate Functions

Aggregate functions return a single value based upon a set of other values. If used among many other expressions in the item list of a SELECTstatement, the SELECT must have a GROUP BY clause. No GROUP BY clause is required if the aggregate function is the only value retrieved by theSELECT statement. The supported aggregate functions and their syntax are listed in .

Function	Usage
AVG(expression)	Computes the average value of a column by the expression
COUNT(expression)	Counts the rows defined by the expression
COUNT(*)	Counts all rows in the specified table or view
MIN(expression)	Finds the minimum value in a column by the expression
MAX(expression)	Finds the maximum value in a column by the expression
SUM(expression)	Computes the sum of column values by the expression

AVG and SUM

The AVG function computes the average of values in a column or an expression. SUM computes the sum. Both functions work with numeric values and ignore NULL values. They also can be used to compute the average or sum of all distinct values of a column or expression.

AVG and SUM are supported by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.

Example

The following query computes average year-to-date sales for each type of book:

SELECT type, AVG( ytd_sales ) AS “average_ytd_sales”
FROM titles
GROUP BY type;
This query returns the sum of year-to-date sales for each type of book:

SELECT type, SUM( ytd_sales )
FROM titles
GROUP BY type;
COUNT

The COUNT function has three variations. COUNT(*) counts all the rows in the target table whether they include nulls or not. COUNT(expression) computes the number of rows with non-NULL values in a specific column or expression. COUNT(DISTINCT expression) computes the number of distinct non-NULL values in a column or expression.

Examples

This query counts all rows in a table:

SELECT COUNT(*) FROM publishers;
The following query finds the number of different countries where publishers are located:

SELECT COUNT(DISTINCT country) “Count of Countries”
FROM publishers
MIN and MAX

MIN(expression) and MAX(expression) find the minimum and maximum value (string, datetime, or numeric) in a set of rows. DISTINCT or ALL may be used with these functions, but they do not affect the result.

MIN and MAX are supported by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.

MySQL also supports the functions LEAST( ) and GREATEST( ), providing the same capabilities.

Examples

The following query finds the best and worst sales for any title on record:

SELECT ‘MIN’ = MIN(ytd_sales), ‘MAX’ = MAX(ytd_sales)
FROM titles;
Aggregate functions are used often in the HAVING clause of queries with GROUP BY. The following query selects all categories (types) of books that have an average price for all books in the category higher than $15.00:

SELECT type ‘Category’, AVG( price ) ‘Average Price’
FROM titles
GROUP BY type
HAVING AVG(price) > 15

Scalar Functions

Scalar functions fall into the categories listed

Categories of Scalar Functions

Function Category	Explanation
Built-in	Performs operations on values or settings built into the database.Oracle uses the term “built-in” to describe all the specialty functions that are provided by Oracle, and thus “built into” their DBMS. This is a distinct and separate usage from the built-in functions described here.
Date & Time	Performs operations on datetime fields and returns values in datetime format.
Numeric	Performs operations on numeric values and returns numeric values.
String	Performs operations on character values (char , varchar, nchar, nvarchar, and CLOB) and returns a string or numeric value.

Note that CASE and CAST are both functions. However, they are detailed in Chapter 3 because of their complexity and frequent usage in SQL-data statements.

Built-in Scalar Functions

SQL99 built-in scalar functions identify the current user session, and also characteristics of the current user session, such as the current session privileges. Built-in scalar functions are almost always nondeterministic. The first three functions listed in  are built-in functions that fall into the date-and-time category of functions. Although the four vendors provide many additional functions beyond these SQL built-ins, the SQL standard declares only those listed in

SQL99 Built-in Scalar Functions

Function	Usage
CURRENT_DATE	Identifies the current date.
CURRENT_TIME	Identifies the current time.
CURRENT_TIMESTAMP	Identifies the current date and time.
CURRENT_USER	Identifies the currently active user within the database server.
SESSION_USER	Identifies the currently active Authorization ID, if it differs from the user.
SYSTEM_USER	Identifies the currently active user within the host operating system.

Microsoft SQL Server supports all the built-in scalar functions. Oracle does not support the built-in scalar functions shown above; however, it supportsUSER as a synonym of CURRENT_USER and SYSDATE as a synonym of CURRENT_TIMESTAMP. MySQL supports all the SQL99 built-in scalar functions, plus both of Oracle’s variants. PostgreSQL supports USER, as defined in SQL99, as a synonym for CURRENT_USER. In addition, MySQL supports NOW( ) and UNIX_TIMESTAMP( ) as synonyms of the function CURRENT_TIMESTAMP. PostgreSQL supports all the SQL99 built-in scalar functions except SESSION_USER.

Example

The following queries retrieve the values from built-in functions. Notice that the various vendors return dates in their native formats:

/* On MySQL */
SELECT CURRENT_TIMESTAMP;
-> '2001-12-15 23:50:26'
 
/* On Microsoft SQL Server */
SELECT CURRENT_TIMESTAMP
GO
-> 'Dec 15,2001 23:50:26'
 
/* On Oracle */
SELECT USER FROM dual;
-> dylan

Numeric Scalar Functions

The list of official SQL99 numeric functions is rather small. The various vendors provide quite a large supplement of mathematical and statistical functions. MySQL supports many of these commands in its SQL99 incarnations. The other database products offer the same capabilities of numeric scalar functions through their own internally defined functions, but they do not share the same name as those declared by the SQL standard. The supported numeric functions and syntax are listed in

SQL99 Numeric Functions

Function	Usage
BIT_LENGTH(expression)	Returns an integer value representing the number of bits in an expression.
CHAR_LENGTH(expression)	Returns an integer value representing the number of characters in an expression.
EXTRACT(datetime_expression datepart FROM expression)	Allows the datepart to be extracted (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,TIMEZONE_HOUR, or TIMEZONE_MINUTE ) from an expression.
OCTET_LENGTH(expression)	Returns an integer value representing the number of octets in an expression. This value is the same asBIT_LENGTH/8.
POSITION(starting_stringIN search_string)	Returns an integer value representing the starting position of a string within the search string.

BIT_LENGTH, CHAR_LENGTH, and OCTET_LENGTH

The closest any of the vendors get to the BIT_LENGTH function is Oracle. Oracle supports the LENGTHB function, which returns an integer value representing the number of bytes in an expression.

MySQL and PostgreSQL support CHAR_LENGTH and the SQL99 synonym CHARACTER_LENGTH( ). PostgreSQL also supports EXTRACT( ), OCTET_LENGTH( ), and POSITION( ) as per the SQL99 standard. The other two vendors each have a similar function that provides identical functionality. SQL Server provides the LEN function and Oracle provides the LENGTH function.

MySQL and PostgreSQL also fully support the OCTET_LENGTH function.

Example

The following example determines the length of a string and a value retrieved from a column:

/* On MySQL and PostgreSQL */
SELECT CHAR_LENGTH('hello');
SELECT OCTET_LENGTH(book_title) FROM titles;
 
/* On Microsoft SQL Server */
SELECT DATALENGTH(title) 
FROM titles
WHERE type = 'popular_comp'
GO
 
/* On Oracle */
SELECT LENGTH('HORATIO') "Length of characters"
FROM dual;
 
 

EXTRACT

The EXTRACT function is not supported by the database vendors, except for PostgreSQL and MySQL.

Each vendor supports a separate command to accomplish the same functionality. Oracle uses the TO_CHAR function to extract a portion of a date into a character string. SQL Server uses the CONVERT function to extract a portion of a date.

MySQL implementation is extended somewhat beyond the SQL99 standard. The SQL99 standard does not have a provision for returning multiple fields in the same call to EXTRACT( ) (e.g., “DAY_HOUR”). The MySQL extensions try to accomplish what the combination DATE_TRUNC( ) andDATE_PART( ) do in PostgreSQL. MySQL supports the dateparts listed in

MySQL Dateparts

Type value	Meaning	Expected format
SECOND	Seconds	SECONDS
MINUTE	Minutes	MINUTES
HOUR	Hours	HOURS
DAY	Days	DAYS
MONTH	Months	MONTHS
YEAR	Years	YEARS
MINUTE_SECOND	Minutes and seconds	“MINUTES:SECONDS”
HOUR_MINUTE	Hours and minutes	“HOURS:MINUTES”
DAY_HOUR	Days and hours	“DAYS HOURS”
YEAR_MONTH	Years and months	“YEARS-MONTHS”
HOUR_SECOND	Hours, minutes, seconds	“HOURS:MINUTES:SECONDS”
DAY_MINUTE	Days, hours, minutes	“DAYS HOURS:MINUTES”
DAY_SECOND	Days, hours, minutes, seconds	“DAYSHOURS:MINUTES:SECONDS”

Example

This example extracts dateparts from several datetime values:

/* On MySQL */
SELECT EXTRACT(YEAR FROM "2013-07-02");
-> 1999
SELECT EXTRACT(YEAR_MONTH FROM "2013-07-02 01:02:03");
-> 199907
SELECT EXTRACT(DAY_MINUTE FROM "2013-07-02 01:02:03");
-> 20102

POSITION

The POSITION function returns an integer that indicates the starting position of a string within the search string. MySQL and PostgreSQL support thePOSITION function with no variation from the SQL99 syntax. PostgreSQL has a synonymous function, TEXTPOS, while MySQL has the synonymous function, LOCATE.

Oracle’s equivalent function is called INSTR. Microsoft SQL Server has both CHARINDEX and PATINDEX. The CHARINDEX and PATINDEXare very similar, except that PATINDEX allows the use of wildcard characters in the search criteria. For example:

/* On MySQL */
SELECT LOCATE('bar', 'foobar');
-> 4
 
/* On MySQL and PostgreSQL */
SELECT POSITION('fu' IN 'snafhu');
-> 0
 
/* On Microsoft SQL Server */
SELECT CHARINDEX( 'de', 'abcdefg' )
GO
-> 4
SELECT PATINDEX( '%fg', 'abcdefg' )
GO
-> 6

String Functions

Basic string functions offer a number of capabilities and return a string value as a result set. Some string functions are dyadic, indicating that they operate on two strings at once. SQL99 supports the string functions listed in.

SQL String Functions

Function	Usage
CONCATENATE (expression || expression)	Appends two or more literal expressions, column values, or variables together into one string.
CONVERT	Converts a string to a different representation within the same character set.
LOWER	Converts a string to all lowercase characters.
SUBSTRING	Extracts a portion of a string.
TRANSLATE	Converts a string from one character set to another.
TRIM	Removes leading characters, trailing characters, or both from a character string.
UPPER	Converts a string to all uppercase characters.

CONCATENATE

SQL99 defines a concatenation operator ( || ), which joins two distinct strings into one string value. The CONCATENATE function appends two or more strings together, producing a single output string. PostgreSQL and Oracle support the double-pipe concatenation operator. Microsoft SQL Server uses the plus sign (+) concatenation operator.

MySQL supports a similar function, CONCAT( ). Refer to the “Concatenation Operators” section Chapter 3, SQL Statements Command Reference, for more information on concatenation within Oracle, PostgreSQL, and Microsoft SQL Server.

SQL99 Syntax

CONCATENATE('string1' || 'string2')

MySQL Syntax

CONCAT(str1, str2, [,...n])

If any of the concatenation values are null, the entire returned string is null. Also, if a numeric value is concatenated, it is implicitly converted to a character string:

SELECT CONCAT('My ', 'bologna ', 'has ', 'a ', 'first ', 'name...');
-> 'My bologna has a first name...'
SELECT CONCAT('My ', NULL, 'has ', 'first ', 'name...');
-> NULL

CONVERT and TRANSLATE

The CONVERT function alters the representation of a character string within its character set and collation. For example, CONVERT might be used to alter the number of bits per character.

TRANSLATE alters the character set of a string value from one base-character set to another. Thus, TRANSLATE might be used to translate a value from the English character set to a Kanji (Japanese) or Russian character set. The translation must already exist, either by default or having been created using the CREATE TRANSLATION command.

SQL99 Syntax

CONVERT (char_value target_char_set USING form_of_use source_char_name)
 
TRANSLATE(char_value target_char_set USING translation_name)

Among the database vendors, only Oracle supports CONVERT and TRANSLATE with the same meaning as SQL99. Oracle’s implementation ofTRANSLATE is very similar to SQL99, but not identical. In its implementation, Oracle accepts only two arguments and allows translating only between the database character set or the national language support character set.

MySQL’s implementation of the CONVERT function only translates numbers from one base to another. In contrast, Microsoft SQL Server’s implementation of CONVERT is a very rich utility that alters the base datatype of an expression, but is otherwise dissimilar to the SQL99 CONVERTfunction. PostgreSQL does not support CONVERT, and its implementation of TRANSLATE serves to morph any occurrence of a character string to any other character string.

MySQL Syntax and Variations

CONV(int, from_base, to_base)

MySQL does not support TRANSLATE. This implementation of CONVERT returns a string value representing the number as it is converted from thefrom_base value to the to_base value. If any of the numbers are NULL, then the function returns NULL. Following are some examples:

SELECT CONV("a",16,2);
-> '1010'
SELECT CONV("6E",18,8);
-> '172'
SELECT CONV(-17,10,-18);
-> '-H'

Microsoft SQL Server Syntax and Variations

CONVERT (data_type[(length) | (precision,scale)], expression[,style])

Microsoft SQL Server does not support TRANSLATE. Microsoft’s implementation of the CONVERT function does not follow the SQL99 specification. Instead, it is functionally equivalent to the CAST function. The style clause is used to define the format of a date conversion. Refer to the vendor documentation for more information. Following is an example:

SELECT title, CONVERT(char(7), ytd_sales)
FROM titles
ORDER BY title
GO
 

Vendor Extensions

The following section provides a full listing and description of each vendor-supported function. These functions are vendor-specific. Thus, a MySQL function, for example, is not guaranteed to be supported by any other vendor. MySQL functions are provided to give an idea of the capabilities available within the various products. Refer to the vendor’s documentation for exact syntax usage.

Microsoft SQL Server-Supported Functions

provides an alphabetical listing of Microsoft SQL Server-supported functions.

Microsoft SQL Server-Supported Functions

Function	Description
abs(numeric_expression)	Returns absolute value.
acos( float_expression)	Returns angle (in radians) whose cosine is the specified argument.
app_name( )	Returns application name for current session; set by application.
ascii(character_expression)	Converts character to a numeric ASCII code.
asin( float_expression)	Returns angle (in radians) whose sine is the specified argument.
atan( float_expression)	Returns angle (in radians) whose tangent is the specified argument.
atn2( float_expression, float_expressioin)	Returns angle (in radians) whose tangent is argument1/argument1.
avg([ All| Distinct] Expression)	Computes average of a column.
binary_checksum(* | expression [,...n])	Returns binary checksum for list of expressions or row of a table.
cast(Expression as Data Type)	Converts a valid SQL Server expression to the specified datatype.
ceiling(numeric_expression)	Returns smallest integer greater than or equal to the argument.
char(integer_expression)	Converts a numeric ASCII code to a character.
charindex(expression1, expression2 [, start_location])	Returns position of the first occurrence of a substring in a string.
checksum(* | expression [,...n])	Returns checksum value (computed over row values or expressions provided).
checksum_agg([ALL | Distinct] expression)	Returns checksum of the values in group.
coalesce(expression [,...n])	Returns the first non-NULL argument from a list of arguments.
col_length(`table’, `column’)	Returns column length in bytes.
col_name(table_id, column_id)	Returns column name, given table ID and column ID.
contains({column | }, `contains_search_condition’})	Searches columns on exact or “fuzzy” matches of contains_seach_criteria. It is an elaborate function used to perform full-text searches. Refer to the vendor documentation for more information.
containsable(table, column, contains_search_condition)	Returns a table with exact and “fuzzy” matches of contains_search_condition. It is an elaborate function used to perform full-text searches. Refer to the vendor documentation for more information.
convert(data_type [(length)], expression [, style])	Converts data from one datatype to another.
cos(float_expression)	Returns cosine.
cot(float_expression)	Returns cotangent.
count({[All | Distinct] expression]| *})	Counts rows.
count(*)	Computes the number of rows, including those with NULL values.
count( DISTINCT expression )	Calculates the number of distinct non-NULL values in a column or expression. Each group of rows with the same value of expression adds 1 to the result.
count( expression )	Returns the number of rows with non-NULL values in a certain column or expression.
count_big([All | Distinct] expression)	Same as count except returns big integer.
current_timestamp	Returns current date and time.
current_user	Returns username in the current database of the current session.
datalength(expression)	Returns number of bytes in a character or binary string.
databasepropertyex(database, property)	Returns database option or property.
dateadd(datepart, number, date)	Adds a number of dateparts (e.g., days) to a datetime value.
datediff(datepart, startdate, enddate)	Calculates difference between two datetime values expressed in certain dateparts.
datename(datepart, date)	Returns name of a datepart (e.g., month) of a datetime argument.
datepart(datepart, date)	Returns value of a datepart (e.g., hour) of a datetime argument.
day(date)	Returns an integer value representing the day of the date provided as a parameter.
db_id(`[database_name]‘)	Returns database ID and given name.
db_name(database_id)	Returns the database name.
degrees(numeric_expression)	Converts radians to degrees.
difference(character_expression, character_expression)	Compares how two arguments sound and returns a number from 0 to 4. Higher result indicates better phonetic match.
exp(float_expression)	Returns exponential value.
floor(numeric_expression)	Returns largest integer less than or equal to the argument.
file_id(`file_name’)	Returns the file ID for the logical filename.
file_name(file_id)	Returns the logical filename for file ID.
filegroup_id (`filegroup_name’)	Returns filegroup ID for the logical filegroup name.
filegroup_name (filegroup_id)	Returns the logical filegroup name for filegroup ID.
filegroupproperty (filegroup_name, property)	Returns filegroup property value for the specified property.
fileproperty (file, property)	Returns file property value for the specified property.
fulltextcatalog property(catalog_name, property)	Returns full-text catalog properties.
fulltextservice property(property)	Returns full-text service level properties.
formatmessage (msg_number, param_value [,... n ])	Constructs a message from an existing message in SYSMESSAGES table (similar to RAISERROR).
freetexttable(table { column |*}, `freetext_string’ [, top_n_by_rank])	Used for full-text search; returns a table with columns that match the meaning but don’t exactly match value offreetext_string.
getdate( )	Returns current date and time.
getansinull([`database'])	Returns default nullability setting for new columns.
getutcdate( )	Returns Universal Time Coordinate (UTC) date.
grouping(column_name)	Returns 1 when the row is added by CUBE or ROLLUP; otherwise, returns 0.
host_id( )	Returns workstation ID of a given process.
host_name( )	Returns process hostname.
ident_incr (`table_or_view’)	Returns identity-column increment value.
ident_seed (`table_or_view’)	Returns identity seed value.
ident_current (`table_name’)	Returns the last identity value generated for the specified table.
identity(data_type [, seed, increment]) As column_name	Used in SELECT INTO statement to insert an identity column into the destination table.
index_col(`table’, index_id, key_id)	Returns index column name, given table ID, index ID, and column sequential number in the index key.
indexproperty(table_id, index, property)	Returns index property (such as Fillfactor).
isdate(expression)	Validates if a character string can be converted to DATETIME.
is_member({`group’ | `role’})	Returns true or false (1 or 0) depending on whether user is a member of NT group or SQL Server role.
is_srvrolemember (`role’ [,'login'])	Returns true or false (1 or 0) depending on whether user is a member of specified server role.
isnull(check_expression, replacement_value)	Returns the first argument if it is not NULL; otherwise, returns the second argument.
isnumeric(expression)	Validates if a character string can be converted to NUMERIC.
left(character_expression, integer_expression)	Returns a portion of a character expression, starting at integer_expression from left.
len(string_expression)	Returns the number of characters in the expression.
log(float_expression)	Returns natural logarithm.
log10(float_expression)	Returns base-10 logarithm.
lower(character_expression)	Converts a string to lowercase.
ltrim(character_expression)	Trims leading-space characters.
max([All | Distinct] expression)	Finds maximum value in a column.
min([All | Distinct] expression)	Finds minimum value in a column.
month(date)	Returns month part of the date provided.
nchar(integer_expression)	Returns the unicode character with the given integer code.
newid( )	Creates a new unique identifier of type uniqueidentifier.
nullif(expression, expression)	Returns NULL if two specified expressions are equivalent.
object_id(`object’)	Returns object ID and given name.
object_name(object_id)	Returns object name and given ID.
objectproperty (id, property)	Returns properties of objects in the current database.

MySQL-Supported Functions

provides an alphabetical listing of MySQL-supported functions.

MySQL-Supported Functions

Function	Description
abs(X)	Returns the absolute value of X.
acos(X)	Returns the arc cosine of X, i.e., the value whose cosine is X; returns NULL if X is not in the range -1 to 1.
ascii(str)	Returns the ASCII code value of the leftmost character of the string str; returns 0 if str is the empty string; returns NULL if str is NULL.
asin(X)	Returns the arc sine of X, i.e., the value whose sine is X; returns NULL if X is not in the range -1 to 1.
atan(X)	Returns the arctangent of X, i.e., the value whose tangent is X.
atan2(X,Y)	Returns the arctangent of the two variables X and Y.
avg(expr)	Returns the average value of expr.
benchmark(count,expr)	Executes the expression expr count times. It may be used to time how fast MySQL processes the expression. The result value is always 0.
binary	Casts the string following it to a binary string.
bin(N)	Returns a string representation of the binary value of N, where N is a long (BIGINT ) number.
bit_count(N)	Returns the number of bits that are set in the argument N.
bit_and(expr)	Returns the bitwise AND of all bits in expr. The calculation is performed with 64-bit (BIGINT ) precision.
bit_or(expr)	Returns the bitwise OR of all bits in expr. The calculation is performed with 64-bit (BIGINT ) precision.
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result …] [ELSE result] ENDCASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END	The first version returns the result where value=compare-value. The second version returns the result for the first condition that is true.If there is no matching result value, then the result after ELSE is returned. If there is no ELSE part, NULL is returned.
ceiling(X)	Returns the smallest integer value not less than X.
char(N,…)	Interprets the arguments as integers and returns a string consisting of the characters given by the ASCII code values of those integers. NULL values are skipped.
coalesce(list)	Returns first non-NULL element in the list.
concat(str1,str2,…)	Returns the string that results from concatenating the arguments.
concat_ws(separator, str1, str2,…)	Stands for CONCAT With Separator and is a special form of CONCAT( ). The first argument is the separator for the rest of the arguments. The separator and the rest of the arguments can be a string. If the separator is NULL, the result is NULL. The function skips any NULLs and empty strings after the separator argument. The separator is added between the strings to be concatenated.
connection_id( )	Returns the connection ID (thread_id ) for the connection. Every connection has its own unique ID.
conv(N,from_base,to_base)	Converts numbers between different number bases; returns a string representation of the number N, converted from base from_base to base to_base ; returns NULL if any argument is NULL.
cos(X)	Returns the cosine of X, where X is given in radians.
cot(X)	Returns the cotangent of X.
count(DISTINCT expr,[expr...])	Returns a count of the number of different values.
count(expr)	Returns a count of the number of non-NULL values in the rows retrieved by a SELECT statement.
curdate( )current_date	Returns today’s date as a value in `YYYY-MM-DD’ or YYYYMMDD format, depending on whether the function is used in a string or numeric context.
curtime( )current_time	Returns the current time as a value in `HH:MM:SS’ or HHMMSS format, depending on whether the function is used in a string or numeric context.
database( )	Returns the current database name.
date_add(date,INTERVAL expr type)date_sub(date,INTERVAL expr type)adddate(date,INTERVAL expr type)subdate(date,INTERVAL expr type)	These functions perform date arithmetic. ADDDATE( ) and SUBDATE( ) are synonyms for DATE_ADD( )and DATE_ SUB( ). date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr may start with a – for negative intervals. type indicates how the expression should be interpreted.
date_ format (date, format)	Formats the date value according to the format string.
dayname(date)	Returns the name of the weekday for date.
dayofmonth(date)	Returns the day of the month for date, in the range 1 to 31.
dayofweek(date)	Returns the weekday index for date (1 = Sunday, 2 = Monday, . . . 7 = Saturday).
dayofyear(date)	Returns the day of the year for date, in the range 1 to 366.
decode(crypt_str, pass_str)	Decrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE( ).
degrees(X)	Returns the argument X, converted from radians to degrees.
elt(N,str1,str2,str3,…)	Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT( ) is the complement of FIELD( ).
encode(str,pass_str)	Encrypts str using pass_str as the password. To decrypt the result, use DECODE( ). The result is a binary string the same length as the string.
encrypt(str[,salt])	Encrypts str using the Unix crypt( ) system call. The salt argument should be a string with two characters.
exp(X)	Returns the value of e (the base of natural logarithms) raised to the power of X.
export_set (bits,on,off,[separator, [number_of_bits]])	Returns a string where every bit set in `bit’ gets an `on’ string and every reset bit gets an `off ‘ string. Each string is separated with `separator’ (default `,’) and only `number_of_bits’ (default 64) of `bits’ is used.
field(str,str1,str2,str3,…)	Returns the index of str in the str1, str2, str3, . . . list. Returns 0 if str is not found. FIELD( ) is the complement of ELT( ).
find_in_set(str,strlist)	Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by `,’ characters. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a `,’.
floor(X)	Returns the largest integer value not greater than X.
format(X,D)	Formats the number X to a format like `#,###,###.##’, rounded to D decimals. If D is 0, the result has no decimal point or fractional part.
from_days(N)	Given a daynumber N, returns a DATE value. Not intended for use with values that precede the advent of the Gregorian calendar (1582), due to the days lost when the calendar was changed.
from_unixtime(unix_timestamp)	Returns a representation of the unix_timestamp argument as a value in `YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.
from_unixtime(unix_timestamp,format)	Returns a string representation of the unix_timestamp, formatted according to the format string. Format may contain the same specifiers as those listed in the entry for the DATE_FORMAT( ) function.
get_lock(str,timeout)	Tries to obtain a lock with a name given by the string str, with a timeout of timeout seconds. Returns 1 if the lock is obtained successfully, 0 if the attempt times out, or NULL if an error occurs.
greatest(X,Y,…)	Returns the largest (maximum-valued) argument.
hex(N)	Returns a string representation of the hexadecimal value of N, where N is a long (BIGINT ) number. This is equivalent to CONV(N,10,16). Returns NULL if N is NULL.
interval(N,N1,N2,N3,…)	Returns 0 if N < N1, 1 if N < N2, and so on. All arguments are treated as integers. It is required that N1 <N2 < N3 < . . . < Nn for this function to work correctly.
hour(time)	Returns the hour for time, in the range 0 to 23.
if(expr1,expr2,expr3)	If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), then IF( ) returns expr2, else it returns expr3. IF( ) returns a numeric or string value, depending on the context in which it is used.
ifnull(expr1,expr2)	If expr1 is not NULL, IFNULL( ) returns expr1; otherwise it returns expr2. IFNULL( ) returns a numeric or string value, depending on the context in which it is used.
isnull(expr)	If expr is NULL, ISNULL( ) returns 1; otherwise it returns 0.
insert(str,pos,len,newstr)	Returns the string str. The substring begins at position pos and is 10 characters long, replaced by the stringnewstr.
instr(str,substr)	Returns the position of the first occurrence of substring substr in string str.
last_insert_id([expr])	Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column.
lcase(str)lower(str)	Returns the string str with all characters changed to lowercase according to the current character-set mapping (default is ISO-8859-1 Latin1).
least(X,Y,…)	With two or more arguments, returns the smallest (minimum-valued) argument.
left(str,len)	Returns the leftmost len characters from the string str.
length(str)octet_length(str)char_length(str)character_length(str)	These functions return the length of the string str.
load_ file(file_name)	Reads the file and returns the file contents as a string. The file must be on the server, and the user must specify the full pathname to the file and have the file privilege.
locate(substr,str)position(substr IN str)	Returns the position of the first occurrence of substring substr in string str. Returns 0 if substr is not in str.
locate(substr,str,pos)	Returns the position of the first occurrence of substring substr in string str, starting at position pos; returns 0 if substr is not in str.
log(X)	Returns the natural logarithm of X.
log10(X)	Returns the base-10 logarithm of X.
lpad(str,len,padstr)	Returns the string str, left-padded with the string padstr until str is 10 characters long.
ltrim(str)	Returns the string str with leading-space characters removed.
make_set(bits,str1,str2, . . . )	Returns a set (a string containing substrings separated by `,’ characters) consisting of the strings that have the corresponding bits in bit set. str1 corresponds to bit 0, str2 to bit 1, etc. NULL strings in str1, str2, . . . are not appended to the result.
md5(string)	Calculates a MD5 checksum for the string. Value is returned as a 32-long hex number.
min(expr)max(expr)	Returns the minimum or maximum value of expr. MIN( ) and MAX( ) may take a string argument; in such cases they return the minimum or maximum string value.
minute(time)	Returns the minute for time, in the range 0 to 59.
mod(N,M)	% Modulo (like the % operator in C); returns the remainder of N divided by M.
month(date)	Returns the month for date, in the range 1 to 12.
monthname(date)	Returns the name of the month for date.
now( )sysdate( )current_timestamp	Returns the current date and time as a value in `YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.
nullif(expr1,expr2)	If expr1 = expr2 is true, returns NULL; otherwise returns expr1.
oct(N)	Returns a string representation of the octal value of N, where N is a long number. This is equivalent toCONV(N,10,8). Returns NULL if N is NULL.
ord(str)	If the leftmost character of the string str is a multibyte character, returns the code of multibyte character by returning the ASCII code value of the character in the format of:((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]If the leftmost character is not a multibyte character, returns the same value as the ASCII( ) function does.
password(str)	Calculates a password string from the plain-text password str. This is the function that is used for encrypting MySQL passwords for storage in the Password column of the user grant table.
period_add(P,N)	Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value.
period_diff(P1,P2)	Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.
pi( )	Returns the value of .
pow(X,Y)power(X,Y)	Returns the value of X raised to the power of Y.
quarter(date)	Returns the quarter of the year for date, in the range 1 to 4.
radians(X)	Returns the argument X, converted from degrees to radians.
rand( )rand(N)	Returns a random floating-point value in the range 0 to 1.0.If an integer argument N is specified, it is used as the seed value.
release_lock(str)	Releases the lock named by the string str that was obtained with GET_LOCK( ). Returns 1 if the lock is released, 0 if the lock isn’t locked by this thread (in which case the lock is not released), and NULL if the named lock doesn’t exist.
repeat(str,count)	Returns a string consisting of the string str repeated count times. If count <= 0, returns an empty string. Returns NULL if str or count are NULL.
replace(str, from_str,to_str)	Returns the string str with all occurrences of the string from_str replaced by the string to_str.
reverse(str)	Returns the string str with the order of the characters reversed.
right(str,ten)	Returns the rightmost 10 characters from the string str.
round(X)	Returns the argument X, rounded to an integer.
round(X,D)	Returns the argument X, rounded to a number with D decimals. If D is 0, the result has no decimal point or fractional part.
rpad(str,len,padstr)	Returns the string str, right-padded with the string padstr until str is ten characters long.
rtrim(str)	Returns the string str with trailing space characters removed.
sec_to_time(seconds)	Returns the seconds argument, converted to hours, minutes, and seconds, as a value in `HH:MM:SS’ or HHMMSS format, depending on whether the function is used in a string or numeric context.
second(time)	Returns the second for time, in the range 0 to 59.
sign(X)	Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive.
sin(X)	Returns the sine of X, where X is given in radians.
soundex(str)	Returns a soundex string from str. Two strings that sound “about the same” should have identical soundexstrings.A “standard” soundex string is four characters long, but the SOUNDEX( ) function returns an arbitrarily long string. A SUBSTRING( ) can be used on the result to get a “standard” soundex string. All non-alphanumeric characters are ignored in the given string. All international alphabetic characters outside the A-Z range are treated as vowels.
space(N)	Returns a string consisting of N space characters.
sqrt(X)	Returns the nonnegative square root of X.
std(expr)stddev(expr)	Returns the standard deviation of expr. The STDDEV( ) form of this function is provided for Oracle compatability.
strcmp(expr1,expr2)	STRCMP( ) returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.
substring(str,pos,len)substring(str FROM pos FOR len)mid(str,pos,len)	Returns a substring 10 characters long from string str, starting at position pos. The variant form that usesFROM is ANSI SQL92 syntax.
substring_index (str,delim,count)	Returns the substring from string str after count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.
substring(str,pos)substring(str FROM pos)	Returns a substring from string str starting at position pos.
sum(expr)	Returns the sum of expr. Note that if the return set has no rows, it returns NULL.
tan(X).	Returns the tangent of X, where X is given in radians.
time_ format (time, format)	This is used like DATE_FORMAT( ), but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0.
time_to_sec(time)	Returns the time argument, converted to seconds.
to_days(date)	Given a date, returns a daynumber (the number of days since year 0).
trim([[BOTH | LEADING | TRAILING] [remstr] FROM] str)	Returns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers BOTH,LEADING, or TRAILING are given, BOTH is assumed. If remstr is not specified, spaces are removed.
truncate(X,D)	Returns the number X, truncated to D decimals. If D is 0, the result has no decimal point or fractional part.
ucase(str)upper(str)	Returns the string str with all characters changed to uppercase according to the current character set mapping (default is ISO-8859-1 Latin1).
unix_timestamp( )unix_timestamp(date)	If called with no argument, returns a Unix timestamp (seconds since `1970-01-01 00:00:00′ GMT). IfUNIX_TIMESTAMP( ) is called with a date argument, it returns the value of the argument as seconds since `1970-01-01 00:00:00′ GMT.
user( )system_user( )session_user( )	These functions return the current MySQL username.
version( )	Returns a string indicating the MySQL server version.
week(date)week(date, first)	With a single argument, returns the week for date, in the range 0 to 53. (The beginning of a week 53 is possible during some years.) The two-argument form of WEEK( ) allows the user to specify whether the week starts on Sunday (0) or Monday (1).
weekday(date)	Returns the weekday index for date (0 = Monday, 1 = Tuesday, . . . 6 = Sunday).
year(date)	Returns the year for date, in the range 1000 to 9999.
yearweek(date)yearweek(date, first)	Returns year and week for a date. The second argument works exactly like the second argument to WEEK( ). Note that the year may be different from the year in the date argument for the first and the last week of the year.

Oracle SQL-Supported Functions

provides an alphabetical listing of the SQL functions specific to Oracle.

Oracle-Supported Functions

Function	Description
abs(number)	Returns the absolute value of number.
acos(number)	Returns the arc cosine of number ranging from -1 to 1. The result ranges from 0 to  and is expressed in radians.
add_months(date, int)	Returns the date date plus int months.
ascii(string)	Returns the decimal value in the database character set of the first character of string; returns an ASCII value when the database character set is 7-bit ASCII; returns EBCDIC values if the database character set is EBCDIC Code Page 500.
asin(number)	Returns the arc sine of number ranging from -1 to 1. The resulting value ranges from -/2 to  /2 and is expressed in radians.
atan(number)	Returns the arctangent of any number. The resulting value ranges from - /2 to p/2 and is expressed in radians.
atan2(number,nbr)	Returns the arctangent of number and nbr. The values for number and nbr are not restricted, but the results range from - to  and are expressed in radians.
avg([DISTINCT ] expression) over (analytics)	Returns the average value of expr. It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text).
bfilename(`directory’,'filename’)	Returns a BFILE locator associated with a physical LOB binary filename on the server’s filesystem in directory.
ceil(number)	Returns smallest integer greater than or equal to number.
chartorowid(char)	Converts a value from a character datatype (CHAR or VARCHAR2 datatype) to ROWID datatype.
chr(number [USING NCHAR_CS])	Returns the character having the binary equivalent to number in either the database character set (if USING NCHAR_CS is not included) or the national character set (if USING NCHAR_CS is included).
concat(string1, string2)	Returns string1 concatenated with string2. It is equivalent to the concatenation operator (||).
convert(char_value, target_char_set, source_char_set)	Converts a character string from one character set to another; returns the char_value in the target_char_set after converting char_value from the source_char_set.
corr(expression1, expression2) over (analytics)	Returns the correlation coefficient of a set of numbered pairs (expressions 1 and 2). It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text).
cos(number)	Returns the cosine of number as an angle expressed in radians.
cosh(number)	Returns the hyperbolic cosine of number.
count	Returns the number of rows in the query; refer to the earlier section on COUNT for more information.
covar_pop(expression1, expression2) over (analytics)	Returns the population covariance of a set of number pairs (expressions 1 and 2). It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text).
covar_samp(expression1, expression2) over(analytics)	Returns the sample covariance of a set of number pairs (expressions 1 and 2). It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text).
cume_dist( ) ( [OVER (query)] ORDER BY…)	The cumulative distribution function computes the relative position of a specified value in a group of values.
decode(expr search , result [,. n] [,default])	Compares expr to the search value; if expr is equal to a search, returns the result. Without a match, DECODEreturns default, or NULL if default is omitted. Refer to Oracle documentation for more details.
dense_rank( ) ( [OVER (query)] ORDER BY…)	Computes the rank of each row returned from a query with respect to the other rows, based on the values of thevalue_exprs in the ORDER_BY_clause.
deref(expression)	Returns the object reference of expression, where expression must return a REF to an object.
dump(expression [,return_ format [, starting_at [,length]]] )	Returns a VARCHAR2 value containing a datatype code, length in bytes, and internal representation of expression. The resulting value is returned in the format of return_ format.
empth[B | C]lob( )	Returns an empty LOB locator that can be used to initialize a LOB variable. It can also be used to initialize a LOB column or attribute to empty in an INSERT or UPDATE statement.
exp(number)	Returns E raised to the number ed power, where E = 2.71828183.
first_value( expression) over (analytics)	Returns the first value in an ordered set of values.
floor(number)	Returns largest integer equal to or less than number.
greatest(expression [,...n])	Returns the greatest of the list of expressions. All expressions after the first are implicitly converted to the datatype of the first expression before the comparison.
grouping(expression)	Distinguishes null cause by a super-aggregation in GROUP BY extension from an actual null value.
hextoraw(string)	Converts string containing hexadecimal digits into a raw value.
initcap(string)	Returns string, with the first letter of each word in uppercase and all other letters in lowercase.
instr(string1, string2, start_at, occurrence)	Searches one character string for another character string. INSRT search char1 with a starting position of start_at(an integer) looking for the numeric occurrence within string2. Returns the position of the character in string1 that is the first character of this occurrence.
instrb(string1, string2, [start_a[t, occurrence]])	The same as INSTR, except that start_at and the return value are expressed in bytes instead of characters.
lag(expression [,offset][,default]) over(analytics)	Provides access to more than one row of a table at the same time without a self join; refer to the vendor documentation for more information.
last_day(date)	Returns the date of the last day of the month that contains date.
last_value(expression) over (analytics)	Returns the last value in an ordered set of values; refer to the vendor documentation for more information.
lead(expression [,offset][,default]) over(analytics)	Provides access to more than one row of a table at the same time without a self join. Analytic functions are beyond the scope of this text.
least(expression [,...n])	Returns the least of the list of expressions.
length(string)	Returns the integer length of string, or null if string is null.
lengthb(string)	Returns the length of char in bytes; otherwise, the same as LENGTH.
ln(number)	Returns the natural logarithm of number, where the number is greater than 0.
log(base_number, number)	Returns the logarithm of any base_number of number.
lower(string)	Returns string in the same datatype as it was supplied with all characters lowercase.
lpad(string1, number [,string2])	Returns string1, left-padded to length number using characters in string2; string2 defaults to a single blank.
ltrim(string[, set])	Removes all characters in set from the left of string. Set defaults to a single blank.
make_ref({table_name | view_name} , key [,...n])	Creates a reference (REF ) to a row of an object view or a row in an object table whose object identifier is primary key-based.
max([DISTINCT] expression) over (analytics)	Returns maximum value of expression. It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text).
min([DISTINCT] expression) over (analytics)	Returns minimum value of expression. It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text).
mod(dividend, divider)	Returns remainder of dividend divided by divider ; returns the dividend if divider is 0.
months_between (date1, date2)	Returns number of months between dates date1 and date2. When date1 is later than date2, the result is positive. If it is earlier, the result is negative.
new_time(date, time_zone1, time_zone2)	Returns the date and time in time_zone2 when date and time in time_zone1 are date. Time_zones 1 and 2 may be any of these text strings: AST, ADT: Atlantic Standard or Daylight Time BST, BDT: Bering Standard or Daylight Time CST, CDT: Central Standard or Daylight Time EST, EDT: Eastern Standard or Daylight Time GMT: Greenwich Mean Time HST, HDT: Alaska-Hawaii Standard Time or Daylight Time MST, MDT: Mountain Standard or Daylight Time NST: Newfoundland Standard Time PST, PDT: Pacific Standard or Daylight Time YST, YDT: Yukon Standard or Daylight Time
next_day(date, string)	Returns the date of the first weekday named by string that is later than date. The argument string must be either the full name or the abbreviation of a day of the week in the date language of the session.
nls_charset_decl_len(bytecnt, csid)	Returns the declaration width (bytecnt) of an NCHAR column using the character set ID (csid ) of the column.
nls_charset_id(text)	Returns the NLS character set ID number corresponding to text.
nls_charset_name(number)	Returns the VARCHAR2 name for the NLS character set corresponding to the ID number.
nls_initcap(string [,'nlsparameter'])	Returns string with the first letter of each word in uppercase and all other letters in lowercase. The nlsparameteroffers special linguistic sorting features.
nls_lower(string, [,'nlsparameter'])	Returns string with all letters lowercase. The nlsparameter offers special linguistic sorting features.
nlssort(string [,'nlsparameter'])	Returns the string of bytes used to sort string. The nlsparameter offers special linguistic sorting features.
nls_upper string [,'nlsparameter'])	Returns string with all letters uppercase. The nlsparameter offers special linguistic sorting features.
ntile(expression) over ( query_partition ORDER BY…)	Divides an ordered data set into a number of buckets numbered 1 to expression and assigns the appropriate bucket number to each row.
numtodsinterval (number, `string’)	Converts number to an INTERVAL DAY TO SECOND literal, where number is a number or an expression resolving to a number, such as a numeric datatype column.
numtoyminterval (number, `string’)	Converts number to an INTERVAL DAY TO MONTH literal, where number is a number or an expression resolving to a number, such as a numeric datatype column.
nvl(expression1, expression2)	If expression1 is null, expression2 is returned in the place of a null value. Otherwise, expression1 is returned. The expressions may be any datatype.
nvl2(expression1, expression2, expression3)	Similar to NLV, except that if expression1 is not null, expression2 is returned. If expression1 is null, expression3is returned. The expressions may be any datatype, except LONG.
percent_rank( ) over ( query_partition ORDER BY…)	Similar to the CUME_DIST analytical function. Rather than return the cumulative distribution, it returns the percentage rank of a row compared to the others in its result set. Refer to the vendor documentation for more assistance.
power(number, power)	Returns number raised to the nth power. The base and the exponent can be any numbers, but if number is negative, power must be an integer.
rank (value_expression) over ( query_partition ORDER BY …)	Computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_expression in the ORDER_BY_clause.
ratio_to_report (value_exprs) over ( query_partition)	Computes the ratio of a value to the sum of a set of values. If values_expr is null, the ratio-to-report value also is null.
rawtohex(raw)	Converts a raw value to a string (character datatype) of its hexadecimal equivalent.
ref(table_alias)	REF takes a table alias associated with a row from a table or view. A special reference value is returned for the object instance that is bound to the variable or row.
reftohex(expression)	Converts argument expression to a character value containing its hexadecimal equivalent.
regr_ xxx(expression1, expression2) over (analytics)	Linear regression functions fit an ordinary-least-squares regression line to a set of number pairs where expression1is the dependent variable and expression2 is the independent variable. The linear regression functions are: REGR_SLOPE: returns the slope of the line REGR_INTERCEPT: returns the y-intercept of the regression line REGR_COUNT: returns the number of non-null pairs fitting the regression line REGR_R2: returns the coefficient of determination for the regression REGR_AVGX: returns the average of the independent variable REGR_AVGY: returns the average of the dependent variable REGR_SXX: calculates REGR_COUNT(exp1, exp2) * VAR_POP(exp2) REGR_SYY: calculates REGR_COUNT(exp1, exp2) * VAR_POP(exp1) REGR_SXY: calculates REGR_COUNT(exp1, exp2) * COVAR_POP(exp1, exp2) These can be used as aggregate or analytic functions.
replace(string, search_string [,replacement_string])	Returns string with every occurrence of search_string replaced with replacement_string.
round (number, decimal)	Returns number rounded to decimal places right of the decimal point. When decimal is omitted, number is rounded to 0 places. Note that decimal, an integer, can be negative to round off digits left of the decimal point.
round (date[, format])	Returns the date rounded to the unit specified by the format model format. When format is omitted, date is rounded to the nearest day.
row_number ( ) over ( query_partition ORDER BY … )	Assigns a unique number to each row where it is applied in the ordered sequence of rows specified by theORDER_BY_clause, beginning with 1.
rowidtochar(rowid)	Converts a rowid value to VARCHAR2 datatype, 18 characters long.
rpad(string1, number [, string2])	Returns string1, right-padded to length number with the value of string2, repeated as needed. String2 defaults to a single blank.
rtrim(string[,set])	Returns string, with all the rightmost characters that appear in set removed; set defaults to a single blank.
sign(number)	When number < 0, returns -1. When number = 0, returns 0. When number > 0, returns 1.
sin(number)	Returns the sine of number as an angle expressed in radians.
sinh(number)	Returns the hyperbolic sine of number.
soundex(string)	Returns a character string containing the phonetic representation of string. This function allows words that are spelled differently but sound alike in English to be compared for equality.
sqrt(number)	Returns square root of number, a nonnegative number.
stddev( [DISTINCT] expression) over (analytics)	Returns sample standard deviation of a set of numbers shown as expression.
stdev_pop(expression) over (analytics)	Computes the population standard deviation and returns the square root of the population variance.
seddev_samp(expression) over (analytics)	Computes the cumulative sample standard deviation and returns the square root of the sample variance.
substr(extraction_string [FROM starting_position] [FOR length])	Refer to the earlier section on SUBSTR.
substrb(extraction_string [FROM starting_position] [FOR length])	SUBSTRB is the same as SUBSTR, except that the arguments m starting_position and length are expressed in bytes, rather than in characters.
sum([DISTINCT ] expression) over (analytics)	Returns sum of values of expr ; refer to vendor documentation for assistance with analytics and the OVERsubclause.
sys_context (`namespace’,'attribute’ [,length])	Returns the value of attribute associated with the context namespace, usable in both SQL and PL/SQL statements.
sys_guid( )	Generates and returns a globally unique identifier (RAW value) made up of 16 bytes.
sysdate	Returns the current date and time, requiring no arguments.
tan(number)	Returns the tangent of number as an angle expressed in radians.
tanh(number)	Returns the hyperbolic tangent of number
to_char (date [, format [, `nls_parameter']])	Converts date to a VARCHAR2 in the format specified by the date format format. When fmt is omitted, date is converted to the default date format. The nls_parameter option offers additional control over formatting options.
to_char (number [, format [, `nls_parameter']])	Converts number to a VARCHAR2 in the format specified by the number format format. When fmt is omitted,number is converted to a string long enough to hold the number. The nls_parameter option offers additional control over formatting options.
to_date(string [, format [, `nls_parameter']])	Converts string (in CHAR or VARCHAR2) to a DATE datatype. The nls_parameter option offers additional control over formatting options.
to_lob(long_column)	Usable only by LONG or LONG RAW expressions, it converts LONG or LONG RAW values in the columnlong_column to LOB values. It is usable only in the SELECT list of a subquery in an INSERT statement.
to_multi_byte(string)	Returns string with all of its single-byte characters converted to their corresponding multi-byte characters.
to_number(string [, format [,'nls_parameter']])	Converts a numeric string (of CHAR or VARCHAR2 datatype) to a value of a NUMBER datatype in the format specified by the optional format model format. The nls_parameter option offers additional control over formatting options.
to_single_byte(string)	Returns string with all of its multi-byte characters converted to their corresponding single-byte characters.
translate(`char_value’, `from_text’, `to_text’)	Returns char_value with all occurrences of each character in from_text replaced by its corresponding character into_text; refer to the section “CONVERT and TRANSLATE” earlier in this chapter for more information onTRANSLATE.
translate (text USING [CHAR_CS | NCHAR_CS] )	Converts text into the character set specified for conversions between the database character set or the national character set.
trim({[LEADING | TRAILING | BOTH] trim_char | trim_char }FROM trim_source} )	Enables leading or trailing characters (or both) to be trimmed from a character string.
trunc (base [, number])	Returns base truncated to number decimal places. When number is omitted, base is truncated to 0 places.Number can be negative to truncate (make zero) number digits left of the decimal point.
trunc (date [, format])	Returns date with any time data truncated to the unit specified by format. When format is omitted, date is truncated to the nearest whole day.
uid	Returns an integer that uniquely identifies the session user who logged on. No parameters are needed.
upper(string)	Returns string with all letters in uppercase.
user	Returns the name of the session user who logged on in VARCHAR2.
userenv(option)	Returns information about the current session in VARCHAR2.
value(table_alias)	Takes as a table alias associated with a row in an object table and returns object instances stored within the object table.
var_pop(expression) over (analytics)	Returns the population variance of a set of numbers after discarding the nulls in the expression number set. Analytic functions are covered in the vendor documentation.
var_samp(expression) over (analytics)	Returns the sample variance of a set of numbers after discarding the nulls in the expression number set. Analytic functions are covered in the vendor documentation.
variance([DISTINCT] expression) over (analytics)	Returns variance of expression calculated as follows: 0 if the number of rows in expression = 1 VAR_SAMP if the number of rows in expression > 1
vsize(expression)	Returns the number of bytes in the internal representation of expression. When expression is null, it returns null.

30 Tips While developing any web site, one should keep some points in mind.

1) Set debug=false under compilation as follows:

2) Use Server.Transfer instead of Response.Redirect.

3) Always check Page.IsValid when using Validator Controls

4) Use Foreach loop instead of For loop for String Iteration.

5) Use Client-Side Validation. (but not all the time you have to validate even on the server side)

6) Check “Page.IsPostBack”. To avoid repetition code execution.

7) GIF and PNG are similar, but PNG typically produces a lower file size. (True, but some browsers not supporting PNG format)

8) Use the AppOffline.htm when updating binaries

9) Turn off Tracing unless until required. (by default it’s off, use on the pages where it’s required)

10) Precompiled pages and disable AutoEventWireup; setting the AutoEventWireup attribute to false in the Machine.config file.
11) Turn off Session State, if not required.

12) Select the Release mode before making the final Build for your application.

This option is available in the Top Frame just under the Window Menu option. By default, the Mode is Debug

13) Disable ViewState when not required.

EnableViewState=”false”

14) Avoid frequent round trips to the Database.

15) Use Caching to improve the performance of your application.

16) Validate all Input received from the Users.

17) Use Finally Method to kill resources. (But not in the case of using)

18) The String and Stringbuilder Magic.

It is nice to use Stringbuilder instead of String when string are Amended. Strings occupy different memory location in every time of amended where stringbuilder use single memory location

19) Never use object value directly; first get object value in local variable and then use. It takes more time then variable reading.

20) Avoid Exceptions: Use If condition (if it is check proper condition)

21) Code optimization: Avoid using code like x = x +1; it is always better to use x+=1.

22) Data Access Techniques: DataReaders provide a fast and efficient method of data retrieval. DataReader is much faster than DataSets as far as performance is concerned

23) Before doing a bulky ASP code processing, you can check to make sure Response.IsClientConnected.

24) As always, avoid session variables because each ASP page runs in a different thread and session calls will be serialized one by one. So, this will slow down the application. Instead of session variables you can use the QueryString collection or hidden variables in the form which holds the values.

25) Enabling buffering will improve the performance, like

Then use:

26) Use Repeater control instead of DataGrid , DataList, Because It is efficient, customizable, and programmable.

27) Data listing is more time consume when large data are retrieve from database.

Paging will display only particular data but take load of all data.

Fetch only data that is needed for current page.

28) Avoid Inline JavaScript and CSS

29) Use single css file instead of multiple css file.

Try your best to combine all your CSS based classes into a single .css file as lot of .css files will cause a large amount of requests, regardless of the file sizes.

.css files are normally cached by browsers, so a single and heavy .css file doesn’t cause a long wait on each page request.

Inline .css classes could make HTML heavy, so again: go ahead with a single.css file.

30) Reduce cookie size

31) Compress CSS, JavaScript and Images

Online compressors are available; to compress file please refers following web and Replace your file content with optimize code.

http://iceyboard.no-ip.org/projects/css_compressor for CSS compression

www.xtreeme.com/javascript-optimizer/ . For JS Compression

32 .Use Cache appropriately

i. Page output caching:

ii. Page fragment caching:

Write a Page output caching code into each User Control

iii. Data caching:

Protected void Page_Load (Object src, EventArgs e) {

DataView dv = (DataView) Cache. Get (“EmployeesDataView”);

If (dv == null) { // wasn’t thereSqlConnection conn =

new SqlConnection (“server=localhost;uid=sa;pwd=;database=Test”);

SqlDataAdapter da =new SqlDataAdapter (“select * from Employees”, conn);

Dataset ds = new DataSet();da.Fill(ds, “Employees”);

dv = ds.Tables["Employees"].DefaultView;

Cache.Insert (“EmployeesDataView”, dv);conn.Close();}

Else

Response.Write (“

Loaded employees from data cache!

“);

lb1.DataSource = dv;

lb1.DataTextField = “Name”;

lb1.DataValueField = “Age”;

DataBind () ;}

33) Use server side compression software such as Port80s http://www.port80software.com/products/httpzip/

34) Usage of “using” and I don’t know why it’s not yet published.

35) Don’t make the member variables public or proteted, try to keep private and use public/protected as properties.

36) Use strString=string.Empty instead of strString=”" . [And perhaps instead of strString=null also (?)]

37) Make your page files as light as possible. That is try to avoid unnecessary markups, e.g. use div elements instead of tables.

38) Write static messages in div and make it visible when necessary. This is faster than letting server set Text property of your label or div.

39) Retrieve data from database at once, if possible. Don’t add up to database trip as far as possible. For this, combine the datafields from different tables and select them.

40) Use short ID name for WebControl.

TOP 10 ASP.NET MVC Framework Websites

What’s ASP.NET MVC Framework?

Hi, there! If you’ve never heard about what’s ASP.NET MVC Framework, here I’d like to introduce something about the new Microsoft ASP.NET revolutional product. ASP.NET MVC is a free, fully supported, Microsoft product that enables developers to easily build great web applications. It provides total control over your HTML and URLs, enables rich AJAX integration, and facilitates test driven development.

And here I’d like to collect the top 10 websites which’re using ASP.NET MVC framework application.

TOP 10 ASP.NET MVC Framework Websites

1	Code PlexCodePlex can be the first website which is using ASP.NET MVC Framework. Codeplex is microsoft’s open source project hosting web site. You can use CodePlex to find open source software or create new projects to share with the world. http://www.codeplex.com

2	Stack Over FlowStackoverflow.com is another famous website based on ASP.NET MVC technique. Stackoverflow.com is a language-independent collaboratively edited question and answer site for programmers. http://www.stackoverflow.com

3	Bing ShoppingConsumers go online to compare products, to share opinions with other consumers and to compare prices. Bing Shopping simplifies search by combining these things to help consumers save money and make purchase decisions more quickly. http://www.bing.com/shopping/

4	Kelley Blue BookKelley Blue Book is the trusted resource for prices, values and reviews on new cars and used cars. Before buying or selling your next car, visit KBB.com. http://www.Kbb.com

5	Market WatchMarketWatch operates a financial information website that provides business news, analysis and stock market data to some 6 million people. http://www.marketwatch.com/

6	Ruths ChrisRuth’s Chris Steak House: Best Prime Steak Restaurant! Steakhouse restaurant serving the best prime steak sizzling hot. http://www.ruthschris.com/

7	DotNet Shout OutDotNetShoutout.com is a place where you can find latest Microsoft .NET stories to increase your skills and share your opinions. You can find DotNetShoutout is using Kigg ASP.NET MVC framework project. http://www.Dotnetshoutout.com

8	ServefaultServefault.com is the most popular PHP, CGI, Perl, JavaScript and ASP.Net script collection and resources portal website. http://www.leandrovieira.com

9	Server FaultServer Fault is a collaboratively edited question and answer site for system administrators and IT professionals – regardless of platform. http://www.Serverfault.com

10	DotNetKicksDotNetKicks.com is a community based news site edited by our members. It specialises in .NET development techniques, technologies and tools including ASP. http://www.DotNetKicks.com

20 Tips to Improve ASP.net Application Performance

• Disable Session State
  Disable Session State if you’re not going to use it.  By default it’s on. You can actually turn this off for specific pages, instead of for every page:

  <%@ Page language="c#" Codebehind="WebForm1.aspx.cs"
  AutoEventWireup="false" Inherits="WebApplication1.WebForm1"
  EnableSessionState="false" %>

  You can also disable it across the application in the web.config by setting the <sessionState> mode value to Off.

• Output Buffering
  Take advantage of this great feature.  Basically batch all of your work on the server, and then run a Response.Flush method to output the data.  This avoids chatty back and forth with the server.

  <%response.buffer=true%>

  Then use:

  <%response.flush=true%> 

• Avoid Server-Side Validation
  Try to avoid server-side validation, use client-side instead. Server-Side will just consume valuable resources on your servers, and cause more chat back and forth.
• Repeater Control Good,  DataList, DataGrid, and DataView controls Bad
  Asp.net is a great platform, unfortunately a lot of the controls that were developed are heavy in html, and create not the greatest scaleable html from a performance standpoint.  ASP.net  repeater control is awesome!  Use it!  You might write more code, but you will thank me in the long run!
• Take advantage of HttpResponse.IsClientConnected before performing a large operation:

  if (Response.IsClientConnected)
  {
  // If still connected, redirect
  // to another page.
  Response.Redirect("Page2CS.aspx", false);
  }

  What is wrong with Response.Redirect? Read on…

• Use HTTPServerUtility.Transfer instead of Response.Redirect
  Redirect’s are also very chatty.  They should only be used when you are transferring people to another physical web server.  For any transfers within your server, use .transfer!  You will save a lot of needless HTTP requests.
• Always check Page.IsValid when using Validator Controls
  So you’ve dropped on some validator controls, and you think your good to go because ASP.net does everything for you!  Right? Wrong!  All that happens if bad data is received is the IsValid flag is set to false. So make sure you check Page.IsValid before processing your forms!
• Deploy with Release Build
  Make sure you use Release Build mode and not Debug Build when you deploy your site to production. If you think this doesn’t matter, think again.  By running in debug mode, you are creating PDB’s and cranking up the timeout.  Deploy Release mode and you will see the speed improvements.
• Turn off Tracing
  Tracing is awesome, however have you remembered to turn it off? If not, make sure you edit your web.config and turn it off!  It will add a lot of overhead to your application that is not needed in a production environment.

  <configuration>
  <system.web>
  <trace enabled="false" pageOutput="false" />
  <trace enabled="false" requestLimit="10" pageOutput="false" traceMode="SortByTime" localOnly="true"/>
  <compilation debug="false" />
  </system.web>
  </configuration>

• Page.IsPostBack is your friend
  Make sure you don’t execute code needlessly. I don’t know how many web developers forget about checking IsPostBack!  It seems like such a basic thing to me!  Needless processing!
• Avoid Exceptions
  Avoid throwing exceptions, and handling useless exceptions. Exceptions are probably one of the heaviest resource hogs and causes of slowdowns you will ever see in web applications, as well as windows applications.  Write your code so they don’t happen!  Don’t code by exception!
• Caching is Possibly the number one tip!
  Use Quick Page Caching and the ASP.net Cache API!  Lots to learn, its not as simple as you might think.  There is a lot of strategy involved here.  When do you cache?  what do you cache?
• Create Per-Request Cache
  Use HTTPContect.Items to add single page load to create a per-request cache.
• StringBuilder
  StringBuilder.Append is faster than String + String.  However in order to use StringBuilder, you must

  new StringBuilder()

  Therefore it is not something you want to use if you don’t have large strings.  If you are concatenating less than 3 times, then stick with String + String. You can also try String.Concat

• Turn Off ViewState
  If you are not using form postback, turn off viewsate, by default, controls will turn on viewsate and slow your site.

  public ShowOrdersTablePage()
  {
  this.Init += new EventHandler(Page_Init);
  }
  
  private void Page_Init(object sender, System.EventArgs e)
  {
  this.EnableViewState = false;
  }

• Use Paging
  Take advantage of paging’s simplicity in .net. Only show small subsets of data at a time, allowing the page to load faster.  Just be careful when you mix in caching.  How many times do you hit the page 2, or page 3 button?  Hardly ever right!  So don’t cache all the data in the grid! Think of it this way: How big would the first search result page be for “music” on Google if they cached all the pages from 1 to goggle 
• Use the AppOffline.htm when updating binaries
  I hate the generic asp.net error messages!  If I never had to see them again I would be so happy.  Make sure your users never see them!  Use the AppOffline.htm file!
• Use ControlState and not ViewState for Controls
  If you followed the last tip, you are probably freaking out at the though of your controls not working.  Simply use Control State.  Microsoft has an excellent example of using ControlState here, as I will not be able to get into all the detail in this short article.
• Use the Finally Method
  If you have opened any connections to the database, or files, etc, make sure that you close them at the end!  The Finally block is really the best place to do so, as it is the only block of code that will surely execute.
• Option Strict and Option Explicit
  This is an oldy, and not so much a strictly ASP.net tip, but a .net tip in general.  Make sure you turn BOTH on.  you should never trust .net or any compiler to perform conversions for you.  That’s just shady programming, and low quality code anyway.  If you have never turned both on, go turn them on right now and try and compile.  Fix all your errors.