SQL Server Stored Procedures for Beginners

By : Tom O'Neill Learning something new is challenging. Learning something new on your own redefines challenging. Chances are, you are reading my article because you want to learn how to write stored procedures. You are new to this, and you don't know where to start. You are exactly where I was when I wanted to learn how to write stored procedures. The purpose of this article is to help the developer who doesn't know where to start. I will give you a place to start, teach you techniques beyond the basic "hello world", and walk you through examples. I learned how to write stored procedures alone, and have decided to let you use my experiences to your advantage. This article will consist of the following topics: What are stored procedures? What do you need to get started? Writing your first stored procedure Writing more sophisticated stored procedures Input Variables Input Variables with SELECT and UPDATE Statements Exercise:  Pass Data to a SELECT Stored Procedure Conclusion Database script for SPFORBEGINNERS (the database referenced in this article) At the end, I have also included a SQL script for creating the database used in my examples. It is a small database, and easily replicated. Of course, I am interested in receiving any feedback at tomoneill@deloitte.com. What Are Stored Procedures? Have you ever written SQL statements, like inserts, selects, and updates? Then you have already written most of a stored procedure. A stored procedure is an already written SQL statement that is saved in the database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure. When you put this SQL statement in a stored procedure, you can then run the stored procedure from the database's command environment (I am a SQL Server kind of guy, and run stored procedures from the Query Analyzer), using the exec command. An example is: exec usp_displayallusers The name of the stored procedure is "usp_displayallusers", and "exec" tells SQL Server to execute the code in the stored procedure. (Note: "usp_" in front of the stored procedure name is used to designate this stored procedure as a user-created stored procedure.) The code inside the stored procedure can be something as simple as: SELECT * FROM USERLIST This "select" statement will return all data in the USERLIST table. You may think, skeptically, that stored procedures aren’t terribly useful. Just save the query and run it when you need to. Too easy, right? Well, there is more to the story. Many queries get more complex than "select * from . . ."  Also, you may want to call the stored procedure from an application, such as an ASP page, Visual Basic application, or a Java servlet. With a stored procedure, you can store all the logic in the database, and use a simple command to call the stored procedure. Later, if you decide to migrate from ASP to J2EE, you only need to change the application layer, which will be significantly easier. Much of the business logic will remain in the database. Enough background—let’s write some stored procedures. Getting Started with Stored Procedures What do I need to get started? I have heard that question often. To begin writing stored procedures, the following are essential: A database management system. A database built inside the database management system (see the end of this article for a sample). A text editor, such as Notepad or Query Analyzer. Items 1 and 2 are absolutely essential. You can’t write stored procedures without a database. They would be useless. Sometimes, I write my procedures in Notepad (or another text editor), and copy them into the New Stored Procedure window in SQL Server. The New Stored Procedure window is a bit small, and in Notepad I can spread things out a bit (you’ll see later). Next, you will have to decide what you want your stored procedure to do. It can be tempting to just dive right into the task at hand, but it is always prudent to sketch out some ideas first. Some considerations should be: Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)? With which tables will you have to work? Does it make sense to create a VIEW first? How often will this procedure actually be used? Once you have struggled with these questions (something of an exaggeration, I guess), you will be ready to start coding! Note: Throughout this article, I will focus on stored procedures for SQL Server. You can apply the same principles to other database management systems, but I will make clear references to working in a SQL Server environment. Writing Your First Stored Procedure Finally!!! It is time to write your first stored procedure (assuming you have created your database). In SQL Server, under your database tree, select the "Stored Procedures" option from Enterprise Manager (when you gain more experience, you can use Query Analyzer to create stored procedures). There will be a number of system generated stored procedures there already. Just ignore them. Your next step is to right click on any of the existing stored procedures (don’t worry, you won’t actually use them), then select "New Stored Procedure . . ."  This will open the stored properties window I discussed above. The following code will appear already in the window: CREATE PROCEDURE [PROCEDURE NAME] AS The first thing I usually do is provide some spacing (we’ll need it later). This isn’t required, and as you write more stored procedures, you will find a style with which you are comfortable. /* We will use this area for comments */ CREATE PROCEDURE [PROCEDURE NAME]  /* We will put the variables in here, later */ AS /* This is where the actual SQL statements will go */ So far, it is pretty simple. Let’s look at the top comments section first, /* We will use this area for comments */ When you write stored procedures (especially for a business or academic project), you never know who will eventually have to alter the code. This top section is useful for comments about the stored procedure, a change log, and other pertinent information. While this is not required, it is just a good programming habit. For this exercise, make it look like this: /* Name:  usp_displayallusers Description:  displays all records and columns in USERLIST table Author:  Tom O’Neill Modification Log: Change Description                  Date         Changed By Created procedure            7/15/2003    Tom O’Neill */ Of course, you can use your own name and today’s date. The next section will change only slightly. Every stored procedure needs the words "CREATE PROCEDURE" followed by the name you want to assign to the stored procedure. While not required, stored procedure names usually begin with the prefix "usp_". CREATE PROCEDURE usp_displayallusers This tells the database that you are creating a stored procedure named "usp_displayallusers". So far, your stored procedure should look like this: /* Name:  usp_displayallusers Description:  displays all records and columns in USERLIST table Author:  Tom O’Neill Modification Log: Change Description                  Date         Changed By Created procedure            7/15/2003    Tom O’Neill */ CREATE PROCEDURE usp_displayallusers The next step is to think about variables. Since this is our first stored procedure together, we won’t deal with them yet. Just keep in mind that they are usually added after the "CREATE PROCEDURE" line. Since we don’t have variables, the next step is quite simple. Put the word "AS" beneath the create procedure line. CREATE PROCEDURE usp_displayallusers We are telling the database that we want to create a stored procedure that is called "usp_displayallusers" that is characterized by the code that follows. After the "AS" entry, you will simply enter SQL code as you would in a regularly query. For our first, we will use a SELECT statement: SELECT * FROM USERLIST Now, your stored procedure should look like this: /* Name:  usp_displayallusers Description:  displays all records and columns in USERLIST table Author:  Tom O’Neill Modification Log: Change Description                  Date         Changed By Created procedure            7/15/2003    Tom O’Neill */ CREATE PROCEDURE usp_displayallusers AS SELECT * FROM USERLIST Congratulations, you have written your first stored procedure. If you authored the procedure in a text editor, now would be a good time to copy it into the New Stored Procedure window in SQL Server. Once you have done so, click the "Check Syntax" box. This is a great troubleshooting tool for beginners and experts alike. When SQL Server tells you "Syntax check successful!", you can click OK to save your stored procedure. To view the procedure, simply double-click usp_displayallusers in the Stored Procedures window. To run your stored procedure, open the Query Analyzer and type: exec usp_displayallusers Then, click the green "play" button to run the query. You will see that the procedure has run successfully. It can be frustrating to start from scratch. Right now, you can think of all the things you want to accomplish with stored procedures; you just need to learn how! That will happen next. Let’s take a look at some more useful stored procedures. More Sophisticated Stored Procedures In this section, we are going to address a few new topics. In addition to writing SELECT queries, you are going to want to insert, update, and delete database records. Also, you will probably want to pass information from outside the query. Since inserts and updates require some sort of data input to be useful, our first topic will be variables. From there, we will use data stored in variables for inserts and updates. Note: In this article, we will only address input variables (variables that pass data to the SQL statement in the stored procedure). There are various types of outputs and returns, and they can become quite complex. Since this article is an introduction, we will leave outputs for another time. Input Variables There are many reasons for wanting to pass data to a stored procedure, especially if your stored procedure is being called by a dynamic web page or other application. You may want to use a SELECT statement to pull information into the application for dynamic display. In this case, you would pass selection criteria to the stored procedure (for use in a WHERE clause). If you are inserting new records, you will need to get the data from somewhere. Updating existing records also involves simply getting the data. In both INSERT and UPDATE statements, it is necessary to pass data to the stored procedure. For INSERT, UPDATE, and SELECT statements (to name a few), you can pass the data to your stored procedure using variables. Input variables are essentially "storage" for data that you want to pass to your stored procedure. Inside your stored procedure, you will declare variables at the top of the stored procedure. How does the data get there? The data is entered in the execstatement that you use to kick off the stored procedure. We’ll discuss that in more detail in a bit. There are two types of variables that you can create in SQL Server stored procedures:  Global and Local. Since this is for beginners, I don’t want to go crazy with too many options. We’ll stick to local variables for now. You can name a variable most anything you want, though it is best to stick with meaningful works and abbreviations. I also tend to avoid punctuation, though underscores ("_") are sometimes helpful. The only real requirement is that you begin your variable with the "@" symbol. Here are some examples: @f_name @fullname @HomePhone @ext For every data element you want to pass, you will need to declare a variable. Declaring a variable is quite easy. You decide on a name and a datatype (integer, text, etc.), and indicate the name and datatype at the top of the procedure (below the "CREATE PROCEDURE" line). Let’s add a record to USERLIST. Remember the following: "usr_id" is the primary key, and is system-generated. We won’t need to pass a value for it. "login", "pswd", "l_name", and "email" are required fields. We will have to pass values for them.

First, let’s create the header information (like the author, change log, etc.) that should be a part of every stored procedure.

 /*

Name:  usp_adduser
Description:  Adds a user
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

 Remember this?

 CREATE PROCEDURE usp_adduser

/*
We will put the variables in here, later
*/

 Add the "CREATE PROCEDURE" line, assigning the name "usp_adduser". Our next step is to remove the comments and declare our variables!

To start, let’s look at how our variables will fit. We will need to create a variable for every value we may need to pass. We may not pass a value to every field every time we run the stored procedure. But, we do need to address the possibility that over the life of the stored procedure, every data element may be used. The best way to address this issue is to create a variable for every column in USERLIST. To keep this example simple, we are also assuming that each of the columns can be NULL, and we will also be passing all of the variables to the stored procedure. If some of the columns cannot be NULL, or if not all of the columns will be affected, then the stored procedure and/or the exec statement have to be rewritten slightly. The list below shows the variable and the field with which it is associated.

• @login—login
  
• @pswd—pswd
  
• @f_name—f_name
  
• @l_name—l_name
  
• @address_1—address_1
  
• @address_2—address_2
  
• @city—city
  
• @state—state
  
• @zipcode—zipcode
  
• @email—email

 You have probably noticed that I gave the variables names that closely resemble the column names with which they are associated. This will make it easier for you to maintain the stored procedure in the future. Delete the comments about variables, and put your list of variables beneath the "CREATE PROCEDURE" line.

 CREATE PROCEDURE usp_adduser

@login
@pswd
@f_name
@l_name
@address_1
@address_2
@city
@state
@zipcode
@email

Next, add datatypes to each of the variables. The datatype assigned to the variable should match the datatype assigned to the corresponding column in the database. For any elements with the "char", "varchar", or "numeric" datatypes, you will need to put the maximum character length list in parentheses after the datatype. Separate all variables (except the last one), with a comma.

CREATE PROCEDURE usp_adduser

@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35),
@address_1 varchar(30),
@address_2 varchar(30),
@city varchar(30),
@state char(2),
@zipcode char(10),
@email varchar(50)

With that last keystroke, you have created your first set of variables. To finish "usp_adduser", we will have to figure out what we want the stored procedure to do, then add the appropriate code after the "AS" statement. This stored procedure will add a new record to the USERLIST table, so we should use an INSERT statement. The SQL should be:

INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1, address_2, city, state, zipcode, email)

The INSERT clause is pretty standard. The VALUES clause is a bit more complex. If you have worked with databases, you are probably accustomed to seeing something like this:

VALUES ('dnelson', 'dean2003', 'Dean', 'Nelson', '200 Berkeley Street', '', 'Boston', 'MA', '02116', 'dnelson@test.com')

Since we are passing values from variables, it will look a bit different. Instead of putting the actual values in the VALUES clause, we’ll just put the variables. You won’t need to use quotes.

 VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2, @city, @state, @zipcode, @email)

What does the entire stored procedure look like?  Let’s pull it all together.

/*

Name:  usp_adduser
Description:  Add new logins.
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

CREATE PROCEDURE usp_adduser

@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35),
@address_1 varchar(30),
@address_2 varchar(30),
@city varchar(30),
@state char(2),
@zipcode char(10),
@email varchar(50)

AS

INSERT INTO USERLIST (login, pswd, f_name, l_name, address_1, address_2, city, state, zipcode, email)

VALUES (@login, @pswd, @f_name, @l_name, @address_1, @address_2, @city, @state, @zipcode, @email)

It looks pretty long and complex, though we know from the process above that the stored procedure is not necessarily complex; it just contains a lot of data. If you have been working in a separate text editor, copy your stored procedure into the New Stored Procedure window in SQL Server, and check the syntax. The result should be a successful syntax check.

Now, we have a stored procedure that can accept external data. What do we do with it?  How do we get the data?  It’s not that hard; I promise. We’ll start with the "exec" statement we used when we wrote our first stored procedure. Remember?

 exec usp_displayallusers

 We have a new stored procedure to execute, so this time, the command will be:

 exec usp_adduser

 There is still the issue of how to get our data into the stored procedure. Otherwise, all those variables will be useless. To get data into our stored procedure, simply add the information (in single quotes ' ') after the execute statement.

 exec usp_adduser ' '

 Remember to pass as many parameters as you have variables, otherwise SQL Server will throw an error. Since we have ten variables, your execute statement should look like this:

 exec usp_adduser ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '

 Next, let’s include the data that we will want to pass to usp_adduser. Your execute statement will look like:

 exec usp_adduser 'dnelson', 'dean2003', 'Dean', 'Nelson', '200 Berkeley Street', ' ', 'Boston', 'MA', '02116', 'dnelson@test.com'

 Running the query should be successful, and SQL Server will tell you that one row has been affected. Now, let’s try using input variables with some other query types.

Input Variables with SELECT and UPDATE Statements

Regardless of the type of SQL statement you use, variables work the same way. Look at the following stored procedure:

 /*

Name:  usp_updateuser
Description:  Updates user information
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

 CREATE PROCEDURE usp_updateuser

@usr_id int,
@login varchar(20),
@pswd varchar(20),
@f_name varchar(25),
@l_name varchar(35),
@address_1 varchar(30),
@address_2 varchar(30),
@city varchar(30),
@state char(2),
@zipcode char(10),
@email varchar(50)

AS

UPDATE USERLIST

SET

login=@login,
pswd=@pswd,
f_name=@f_name,
l_name=@l_name,
address_1=@address_1,
address_2=@address_2,
city=@city,
state=@state,
zipcode=@zipcode,
email=@email

WHERE usr_id=@usr_id

 What’s different about this stored procedure (compared to the INSERT stored procedure)?  Aside from the obvious fact that this is an UPDATE instead of an INSERT?  First, you should have noticed that we added another variable, @usr_id. This new variable has the datatype "int" because it is an integer field. Why did we have to do this?  In the INSERT stored procedure, we were creating a new record. Since usr_id is assigned by the system, we didn’t need to worry about it. Now we are updating an existing record. To ensure that we update the right record, we need to use the primary key as a filter. Notice that @usr_id shows up again in the WHERE clause, where we would normally have a value in quotes (like '1233').

The other difference is that we have included the variables in the SET clause. Instead of:

 login='dnelson'

 we have used:

 login=@login

 Remember, when you use variables, you do not have to use quotes.

The remaining SQL statement to address in this section is the SELECT statement.  We can pass data to a SELECT statement using variables as well. I’ll let you do this one yourself.

Exercise:  Pass Data to a SELECT Stored Procedure

Create a stored procedure that returns one record, based on the table’s primary key. Remember to:

1. Create the header record (commented)
   
2. Create the stored procedure name and declare variables
   
3. Create the rest of your stored procedure

When you are done, copy your stored procedure into the SQL Server New Stored Procedure window (if you are using a separate text editor), and check the syntax. Also, you may want to open the Query Analyzer and run the execute statement. I’ll provide both the stored procedure and execute statement (with sample data) below.

Answers

Stored Procedure:

/*
Name: usp_finduser
Description:  find a user
Author:  Tom O’Neill
Modification Log: Change

Description                  Date         Changed By
Created procedure            7/15/2003    Tom O’Neill
*/

CREATE PROCEDURE usp_finduser

@usr_id int

AS

SELECT * FROM USERLIST
WHERE usr_id=@usr_id 

Execute Statement:

exec usp_finduser '1'

 Did it work?  If not, keep trying!  You’ll get there.

 In Closing

This has been a pretty aggressive lesson. You showed up somewhat familiar with databases, but probably knowing nothing about stored procedures (unless you are a database guru who read my article so you could viciously critique it later!). We have gone from defining stored procedures to writing them independently. That is great!  Stored procedures are an excellent way to insulate your programming logic from the threat of technology migrations in the future. They are useful, make for efficient application development, and are easy to maintain. Using the information and exercises above, you should be on your way to creating stored procedures to support any database-related endeavor.

Database Script to Create Tables for Exercises

ALTER TABLE [dbo].[USERDETAILS] DROP CONSTRAINT FK_USERDETAILS_USERLIST

GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[USERDETAILS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USERDETAILS]

GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[USERLIST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USERLIST]

GO

CREATE TABLE [dbo].[USERDETAILS] (
           [detail_id] [int] IDENTITY (1, 1) NOT NULL ,
           [usr_id] [int] NOT NULL ,
           [title] [varchar] (50) NULL ,
           [yrs_service] [numeric](18, 0) NULL ,
           [yrs_title] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[USERLIST] (

            [usr_id] [int] IDENTITY (1, 1) NOT NULL ,
            [login] [varchar] (20) NOT NULL ,
            [pswd] [varchar] (20) NOT NULL ,
            [f_name] [varchar] (25) NULL ,
            [l_name] [varchar] (35) NOT NULL ,
            [address_1] [varchar] (30) NULL ,
            [address_2] [varchar] (30) NULL ,
            [city] [varchar] (30) NULL ,
            [state] [char] (2) NULL ,
            [zipcode] [char] (10) NULL ,            [email] [varchar] (50) NOT NULL

) ON [PRIMARY]

GO 

thanks.

Reference :- http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

SQL Server 2008 Sparse Columns

Reference :- http://blogs.technet.com/andrew/archive/2008/02/28/sql-server-2008-sparse-columns.aspx

Sparse Columns are another new feature of SQL Server 2008 and are included in the February CTP (CTP6). They pretty much do what they say on the tin; offering a trade-off between taking more space to hold data, but none at all when they are empty.  They don't get you over the 1024 column limit, but could mean you can squeeze more columns into the 8,060 byte row limit for SQL Server.

Like everything in SQL Server you need to know when they add value and when to avoid them like the plague. Fortunately one of the non-sparse areas of Books On-Line is the section covering sparse columns here.
So the good news first:

• Storing a null in a sparse column takes up no space at all.
• To any external application the column will behave the same
• Sparse columns work really well with filtered indexes as you will only want to create an index to deal with the non-empty attributes in the column.
• You can create a column set over the sparse columns that returns an xml clip of all of the non-null data from columns covered by the set.  The column set behaves like a column itself. Note: you can only have one column set per table.
• Change Data Capture and Transactional replication both work, but not the column sets feature.

And the downsides.

• If a sparse column has data in it it will take 4 more bytes than a normal column e.g. even a bit (0.125 bytes normally) is 4.125 bytes and unique identifier rises form 16 bytes to 20 bytes.
• Not all data type can be sparse: text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinray (max) with the FILESTREAM attribute can be sparse.
• computed columns can't be sparse (although sparse columns can take part in a calculation in another computed column)
• You can't apply rules or have default values.
• Sparse columns cannot form part of a clustered index.  If you need to do that use a computed column based on the sparse column and create the clustered index on that (which sort of defeats the object).
• Merge replication doesn't work.
• Data compression doesn't work.
• Access (read and write) to sparse columns is more expensive, but I haven't been able to find any exact figures on this.

As you can see from Books On-Line there is a really useful guide to when to use them for a particular data type e.g. if more than 64% of your values are null in an int column then use sparse columns, and basically the longer the data type the lower the threshold for using sparse columns.
So how does it work? Just put the keyword SPARSE into a create table statement:
CREATE TABLE CustomerDemographics
(CusomterID int PRIMARY KEY,
Gender varchar(7) NOT NULL,
EducationLevel varchar(20) SPARSE NULL,
SalaryBand varchar(10) SPARSE NULL)
Selects against this table will work exactly as for normal columns whether the sparse column is included as a column in the select column or a filter in a where clause.
Optionally to create a column set for this table append this to the end of the create table statement:
DemographicSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
The column set DemographicSet is then treated like any xml column i.e. it can be selected and also be used for updates and inserts, Note if you do use a column set for updating data sparse columns not specifically declared in the update well be set to null.
Finally if you are wondering why this feature was developed, the simple answer is to support future versions SharePoint which was also one of the drivers behind FileStream.  I can see it being applied to any content management system over SQL Server and also as I have mentioned before for reducing the overhead of storing customer demographics or product catalogs where not every column applies to every product or customer.
Reference :- http://blogs.technet.com/andrew/archive/2008/02/28/sql-server-2008-sparse-columns.aspx
SPARSE column are better at managing NULL and ZERO values in SQL Server. It does not take any space in database at all. If column is created with SPARSE clause with it and it contains ZERO or NULL it will be take lesser space then regular column (without SPARSE clause).

In SQL Server 2008 maximum column allowed per table is 1024. All the SPARSE columns does not count to this limit of 1024. The maximum limit of SPARSE column is 100,000. In summary any table can have maximum of 100,000 SPARSE and 1024 regular columns.

Let us see following example of how SPARSE column saves space in database table.

CREATE TABLE UnSparsed(ID INT IDENTITY(1,1),                 FirstCol INT,                SecondCol VARCHAR(100),                 ThirdCol SmallDateTime) GO CREATE TABLE Sparsed(ID INT IDENTITY(1,1),                 FirstCol INT SPARSE,                 SecondCol VARCHAR(100) SPARSE,                 ThirdCol SmallDateTime SPARSE) GO DECLARE @idx INT = 0 WHILE @idx < 50000 BEGIN INSERT INTO UnSparsed VALUES (NULL,NULL, NULL) INSERT INTO Sparsed VALUES (NULL, NULL, NULL) SET @idx+=1 END GO sp_spaceused 'UnSparsed' GO sp_spaceused 'Sparsed' GO DROP TABLE UnSparsed GO DROP TABLE Sparsed GO 

All SPARSE columns are stored as one XML column in database. Let us see some of the advantage and disadvantage of SPARSE column.

Advantages of SPARSE column are:

• INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
• SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
• SPARSE column saves lots of database space when there are zero or null values in database.

Disadvantages of SPARSE column are:

• SPARSE column does not have IDENTITY or ROWGUIDCOL property.
• SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
• SPARSE column can not have default value or rule or computed column.
• Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.
• Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
• A table operation which involves SPARSE column takes performance hit over regular column.

Let me know your thoughts about SPARSE column feature of SQL Server 2008, I am very eager to see your point of view on this new feature.

Reference : Pinal Dave (http://www.SQLAuthority.com)