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.
Category: T-SQL
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.
JOIN types and performance
There are basically three main types of joins in SQL Server, each one of which has its own characteristics, advantages and drawbacks. You’ll find these three operators in query plans. Knowing how different types joins work will help you optimize your queries, indexing and how to store your data.
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.
Indexing basics
Understanding indexes in SQL Server can help you build much more efficient database solutions. Often, performance problems can be adressed with indexes, so knowing how indexes work and how to set them up for the best performance are a great asset in your optimization work.
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.