Post New Web Links

Built-in Functions - Text and Image Functions

Posted By: Venkat Posted Date: April 13, 2010 Points: 2 Category :Sql Server

More Related Resource Links

Built-in Functions - Text and Image Functions in SQL Server

Text and image functions operate on TEXT, NTEXT and IMAGE data types. These data types are deprecated with SQL Server 2005 and should be replaced by VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) when possible. Alternatively you can also consider storing large string values using XML data type. Text and image functions are nondeterministic.

Future versions of SQL Server will not support TEXT, NTEXT and IMAGE data types. Columns with TEXT / NTEXT data type do not support commonly used string functions such as LEN, LEFT, RIGHT, etc. Furthermore, due to the large size of TEXT data you''re likely to see performance issues if you store such data in the database. However, at times you can''t help but use the TEXT / NTEXT / IMAGE data types (for example when supporting 3rd party databases when you cannot change the schema) - this is when text and image functions come in handy.

Built-In Functions Fundamentals

While your primary job as a database developer consists of creating lists, probably your second most important job is to assist your users with the various assignments they must perform on your application. One way you can assist is to use functions that perform otherwise complex tasks. We introduced and described functions in the previous lesson. To assist your development with the different tasks of a database, Transact-SQL ships with various already created and tested functions. You just need to be aware of these functions, their syntax, and the results they produce.

Built-in Functions - Aggregate Functions

Aggregate functions return a single value summarizing a given data set. All aggregate functions are deterministic. NOTE: AVG, SUM, STDEV, STDEVP, VAR and VARP functions cannot operate on BIT data types; they can operate on all other numeric data types.

Built-in Functions - Date and Time Functions

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

Built-in Functions - String Functions

String functions let you extract various portions of character strings, change the case of strings, concatenate and reverse strings and perform many other types of manipulations. All built-in string functions are deterministic with the exception of CHARINDEX and PATINDEX

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Introduction to Functions

A function is a section of code that is used to perform an isolated assignment. Once it has performed its assignment, the function can be accessed to present its result(s).

In Transact-SQL, a function is considered an object. After creating the function object, it becomes part of a database. You can then execute it when necessary.

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
4 GETDATE and GETUTCDATE Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Cursor Functions

A cursor allows looping through a record set and performing a certain operation on each record within the set. SQL Server supports three functions that can help you while working with cursors: @@FETCH_STATUS, @@CURSOR_ROWS and CURSOR_STATUS. Cursor functions are non-deterministic.

User Defined Functions in Microsoft SQL Server

User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. They are saved as individual work units, and are created using standard SQL commands. Data transformation and reference value retrieval are common uses for functions. LEFT, the built in function for getting the left part of a string, and GETDATE, used for obtaining the current date and time, are two examples of function use. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.

C# File:

using System;
using System.Data.Odbc;
using System.Data;
using System.Configuration;

partial class site_functions : System.Web.UI.Page
{
	
	protected void Main(object sender, EventArgs e)
	{
		open_database();
	}
	
	public static void open_database()
	{
		OdbcConnection myConn;
		OdbcDataReader MyReader = null;
		string strConnection = ConfigurationSettings.AppSettings["dbConnect"];

		myConn = new OdbcConnection(strConnection);
		myConn.Open();
	}
	
	public void close_database()
	{
		myConn.Close();
	}

        public void Read_Database()
	{
		//do something
        }
}

---------------------------------------------------
.ASPX FILE

<%@ Page Language="C#" CodeFile="site-functions.cs" Inherits="site_functions" ContentType="text/html" ResponseEncoding="utf-8" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "


Categories:

ASP.Net	Windows Application	.NET Framework	C#	VB.Net	ADO.Net
Sql Server	SharePoint	Silverlight	Others	All

Built-in Functions - Text and Image Functions

More Related Resource Links

Built-in Functions - Text and Image Functions in SQL Server

Built-In Functions Fundamentals

Built-in Functions - Aggregate Functions

Built-in Functions - Date and Time Functions

Built-in Functions - String Functions

Insert value using Table Value Functions

Introduction to Functions

Date and Time Functions in SQLSERVER

Cursor Functions

User Defined Functions in Microsoft SQL Server

Essential SQL Server Date, Time and DateTime Functions

Date and Time Data Types and Functions

Accessing Functions from a Class

C++ Q&A: Calling Virtual Functions, Persisting View State, POD Type

Data Points: SQL Server User-defined Functions

The XML Files: XPath Selections and Custom Functions, and More

Win32 Q&A: Handy Features in Windows, and Interlocked Functions