This is for SQL 2008R2, but the syntax hasn't really changed for the last few editions of sql server: [CREATE FUNCTION] (MSDN can be really useful) A function is useful in T-SQL in the same respect as just about any other language - abstraction, code re-use and refactoring instantly spring to mind. It is one of the basic programming ideas that gets hammered home from the first moment you really try to learn a programming language. :
+1 to William, but it's worth noting that Scalar Functions and Table-Valued functions often lead to degradation in performance. Scalar functions do so because they are interpreted on each run, and not in-lined to the main query plan. And Table-Valued functions can cause issues because their row count is estimated as 1 row, so can cause issues with inappropriate plans being chosen. Using inline table functions is fine, and they can be used in place of scalar functions by using CROSS APPLY.
I don't mind at all pounding home the idea that the multi-statement table valued functions look quite attractive, but they are very, very dangerous. Performance for these is based on the use of table variables which means they have no statistics so they're inclusion in execution plans is based on returning a single row. As long as they do only return one or a few rows they might not be too bad, but when they start returning hundreds or thousands of rows performance degrades seriously and quickly.