Writing T-SQL in SQL Server 2008: What Actually Works
T-SQL is just SQL plus some procedural extensions Microsoft layered on top of it. The 2008 version introduced a few features that changed how people actually wrote queries day to day. The biggest ones being the OUTPUT clause, table value constructors, and the MERGE statement. If you are just starting out, those three features alone will cover most of what you need to know before anything else matters. At the core, T-SQL follows the same rules as standard SQL but with Microsoft's additions for control flow, variables, error handling, and so on. You write a SELECT, maybe throw in a WHILE loop, declare some variables, and run it through Management Studio. That is basically the whole picture at a surface level. The stuff that trips people up is usually the interaction between those procedural pieces and the set-based operations underneath. I ran into a specific problem last year involving the MERGE statement on a table with around 40 million rows. The merge was supposed to update existing records and insert new ones, but it kept deadlocking because the target table had a trigger firing on each row. The workaround was to disable the trigger temporarily during the merge operation, run the statement, then re-enable it. That cut the runtime from over four hours down to about twenty-two minutes on the same hardware. Not something you would find in any official documentation, just experience at that point.
Getting Started with Variables and Control Flow
DECLARE a variable with the @ symbol, assign it with SET or SELECT, and use it in your query. It sounds simple but beginners mess this up constantly by trying to declare variables inside nested blocks without realizing the scope rules. A variable declared in an IF block is still accessible after that block ends. It does not die when the IF finishes. People expect it to, but it does not work that way in T-SQL. Here is a basic example that shows how variables work in practice: DECLARE @OrderTotal DECIMAL(10,2); SET @OrderTotal = 1250.00; SELECT OrderID, OrderTotal FROM Orders WHERE OrderTotal >= @OrderTotal;
That is about as straightforward as it gets. The real questions come when you start mixing variables with dynamic SQL or cursor-based operations. Both are slow. Both have their place. Neither should be your default approach for anything involving large datasets.
Get the Full Details
The OUTPUT Clause
This was one of the more useful additions in 2008. It lets you capture the results of INSERT, UPDATE, DELETE, and MERGE statements without needing a separate SELECT afterward. You insert a row and immediately see what went in. Or you update a batch and track which rows changed and what their old and new values were. Here is how you use it with an UPDATE: UPDATE Sales.Orders SET Discount = Discount * 1.1 OUTPUT inserted.OrderID, inserted.Discount, deleted.Discount AS OldDiscount WHERE CustomerID = 4521;
The inserted and deleted pseudo-tables inside the OUTPUT clause hold the new and old values respectively. This is different from triggers where those same tables exist but you cannot reference them directly in a normal query. The OUTPUT clause is evaluated before constraints fire, which means you can capture values even if the statement later fails due to a constraint violation. That detail matters more than people realize.
Table Value Constructors
Before 2008, inserting multiple rows required multiple INSERT statements or a series of UNION ALL clauses. The table value constructor lets you do it in a single statement: INSERT INTO Products (ProductName, CategoryID, UnitPrice) VALUES ('Widget A', 3, 12.50), ('Widget B', 3, 15.75), ('Widget C', 5, 8.99); The limitation is that you can only insert up to 1000 rows per VALUES clause. If you need more, you either split it into multiple statements or use a different method entirely. Bulk insert or a temp table approach will handle millions of rows without issue. The VALUES clause is convenient for small batches, not for ETL work.
MERGE Statement
The MERGE statement combines INSERT, UPDATE, and DELETE into one operation based on a join condition. It is powerful but notoriously finicky. The syntax is long, easy to get wrong, and difficult to read when you come back to it six months later. A typical MERGE looks like this: MERGE INTO TargetTable AS T USING SourceTable AS S ON T.ID = S.ID WHEN MATCHED THEN UPDATE SET T.Value = S.Value, T.UpdatedDate = GETDATE() WHEN NOT MATCHED BY TARGET THEN INSERT (ID, Value, CreatedDate) VALUES (S.ID, S.Value, GETDATE()) WHEN NOT MATCHED BY SOURCE THEN DELETE;
The WHEN NOT MATCHED BY SOURCE clause is optional. Remove it if you do not need to delete rows from the target. Keeping it when you do not need it is just noise. I have seen production scripts where someone added that clause by mistake and wiped out thousands of rows because the source table was filtered differently than expected. Always test the USING portion separately before wrapping it in a MERGE.
Error Handling with TRY...CATCH
SQL Server 2008 kept the TRY...CATCH structure from 2005 but a lot of people still write stored procedures without it. If a statement fails inside a procedure and there is no CATCH block, the procedure just stops. No rollback, no cleanup, no error message passed back to the caller in a structured way. Transactions stay open. Connections get stuck. A proper error handling pattern looks like this: BEGIN TRY BEGIN TRANSACTION; UPDATE Inventory SET Quantity = Quantity - 5 WHERE ProductID = 1001; INSERT INTO Sales (ProductID, Quantity, SaleDate) VALUES (1001, 5, GETDATE()); COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE(); DECLARE @ErrSeverity INT = ERROR_SEVERITY(); DECLARE @ErrState INT = ERROR_STATE(); RAISERROR(@ErrMsg, @ErrSeverity, @ErrState); END CATCH;
The @@TRANCOUNT check is important. If there is no open transaction and an error occurs, calling ROLLBACK would actually raise a new error. The IF condition prevents that. It is a small detail that prevents bigger problems.
Common Pitfalls to Avoid
Implicit conversions are the most common performance killer in T-SQL. If your column is VARCHAR and your parameter is NVARCHAR, SQL Server has to convert the column, which kills index usage. Every time. Write your parameters to match the column type, or better yet, use SQL Server's native types consistently throughout the database. Another issue is the order of evaluation in WHERE clauses. People assume conditions are evaluated left to right and short-circuit like a programming language. They are not. The query optimizer reorders predicates however it sees fit for the best plan. If you have a condition like WHERE IsActive = 1 AND dbo.SometimesFails(ID) = 1, do not expect the first condition to always filter out bad rows first. It might not. Speaking of functions, scalar user-defined functions are slow. Inevitably slow. Every row in a result set invokes the function as a separate call with its own context switch overhead. A scalar UDF that does a simple calculation might look clean, but on a million-row query it can add minutes to the runtime. Inline table-valued functions are the alternative and they perform dramatically better because the optimizer can fold them into the main query plan.
Execution Plans and Performance
SQL Server 2008 includes the Execution Plan feature in Management Studio, and using it is non-negotiable if you care about query performance. Turn on Include Actual Execution Plan with Ctrl+M before running a query. The visual plan shows you where spills to disk happen, where implicit conversions occur, and which operators are consuming the most resources. A missing index recommendation from the execution plan is not always correct. Sometimes SQL Server suggests an index because the plan looked expensive, but the index would cause more harm than good on a table with heavy write activity. I learned this the hard way on a log table that received thousands of inserts per minute. The suggested index slowed inserts by roughly sixty percent. Dropping the index restored the write throughput but made the reads slower. The right answer was a filtered index on only the rows that were actually queried, which brought both read and write performance back to acceptable levels.
What This Version Gets Wrong
SQL Server 2008 is old. The query optimizer it uses is not the same one in newer versions, and some of the features introduced here have known limitations that were not fully resolved until later releases. The MERGE statement for example had a bug in 2008 where duplicate matching rows in the source could cause incorrect updates. Microsoft acknowledged it and fixed it in a later cumulative update, but if you are running an early build of 2008 without patches, this is a real risk. Always check your build number against the cumulative update list. Another limitation is the lack of native support for JSON. If your application deals with JSON data, you are stuck parsing it manually with string functions or XML intermediaries. It works, but it is clumsy. Later versions of SQL Server added native JSON support, which makes this entire category of work significantly easier. The MAXDOP and cost threshold settings also default to values that are too conservative for modern hardware. A server with eight or more cores will usually benefit from setting MAXDOP to half the number of cores or less, depending on the workload. Leaving it at the default of zero tells SQL Server to use all available processors, which can lead to scheduler contention and worse performance under concurrent load. This is one of those settings that nobody touches unless a query starts acting strangely under load.
Where to Find Resources
The official Microsoft documentation for SQL Server 2008 is still available on the Microsoft Docs site, though it is archived and no longer updated. For current best practices, newer documentation applies to later versions and the T-SQL language fundamentals remain largely the same. The differences between 2008 and 2012 or 2014 are mostly in new features, not in the core language. Books Online for SQL Server 2008 R2 can be downloaded from the Microsoft website as a standalone installer if you prefer offline reference. The 2008 edition covers everything described here plus additional features like FILESTREAM, Full-Text Search integration, and the Database Engine Tuning Advisor. Those are separate topics but useful if your workload involves large binary objects or full-text querying.
