You’ll be hard-pressed not to stumble over XML data in your daily work. Good thing then, SQL Server contains built-in XML parsing logic – there’s a native xml datatype, built-in XPath support and all the tools you need to store and transform data from or to XML data. However, this is an extensive topic, so this article will cover the basics of parsing XML data into a recordset, just to wet your apetite.
tutorial
Using MERGE to insert, delete and update all-in-one
As of SQL Server 2008, there’s a new powerful consolidation statement in the DML toolbox: MERGE. Using MERGE, you can perform so-called “upserts”, i.e. one statement that performs an insert, delete and/or update in a single statement. And, more importantly, with just a single join.
Different kinds of temp tables
Temp tables are a vital part of every T-SQL developer’s toolbox, but did you know that there are different types of temp tables? This article goes through the ways you can implement a temp table, as well as how they affect performance.
Web requests using a CLR procedure
For most things that you can’t do straigh-off in T-SQL, you have the option to write a Common Language Runtime procedure. This gives you access to a huge array of APIs, so you can create really cool stuff, way beyond what T-SQL would normally allow you to do. Here, we’ll explore how to write a web request procedure in C#, so you can download a web document (for instance, a file on a REST service) directly into a table or a string variable using just a regular function call.
How to create, find and eliminate deadlocks
Deadlocks are typically encountered in high-load scenarios or in complex concurrent transactions. And the worst part is, you probably won’t see them until you’re live in production. Luckily, finding and solving deadlock issues isn’t as complicated as it may sound. All it takes is a bit of logging and patience, along with some basic know-how of how locking mechanisms work in SQL Server.
Introduction to DML triggers
A trigger is like a stored procedure that automatically (and atomically) executes on a given condition, for instance when you insert a record into a table. Used properly, this is a powerful tool to enforce business rules, perform automated calculations, log changes, and more. But triggers also come with some potential pitfalls.
Basic reconciliation skills
You will often encounter situations where you have to reconcile two sets of data – are they the same, and if not, what differences are there? This can be because you re-wrote a piece of code and you want to verify that it still does the same job, or it can be a straight-forward comparison of two data sources. In this article, we’ll look through some useful methods and functions to compare data in SQL Server.
Working with covering indexes
As you could read in the indexing basics article, a well-defined index can boost query performance, but there are a few more basic tricks that can have a great impact on how your query is executed. One of the most important is a technique called covering indexes. A covering index is basically a non-clustered index that covers all the columns you need in a query, not just the keys.
Reading a query plan
Knowing how to read a query plan is absolutely key to optimizing SQL Server query performance. The query plan tells you how SQL Server goes about running your query, including what indexes are used (and how), what join strategies are applied and a lot of other information. If you can read the query plan, you can make the appropriate changes to indexes, query hints, join conditions, etc to tune your workload for optimum performance.
Basic string parsing
A lot of times, T-SQL queries aren’t just for compiling huge sets of aggregate calculations or advanced joins and indexing strategies. Sometimes, you need the skills and tools to do some basic string parsing. Here, you’ll learn the most common basic string parsing and functions found in T-SQL.
sqlsunday.com 