When you install SQL Server, you need to specify what account(s) or group(s) that you want to give administrative privileges. Way back when, it would be sufficient to be a local administrator on the Windows machine running the SQL Server service, but not any more.
Luckily, there’s a solution.
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.
Whenever you run dynamic SQL code from an application or in a stored procedure, make sure you clean (called “escaping” in developer-speak) all those apostrophes and semicolons, or you may find yourself on the business end of an SQL injection.
I stumbled upon a challenge on a blog i follow, to find prime numbers using T-SQL. With a little bit of Wikipedia research, I’ve built a T-SQL version of the “sieve of Eratosthenes“.
The OUTPUT clause allows you to combine DML statements with a kind of SELECT statement on the rows affected by the DML operation. This is a powerful way to visualize what records were touched by your statement, or an easy way to build an auditing mechanism.
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.
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.
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.
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.
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.