daniel – Page 22 – sqlsunday.com

Neat T-SQL tool on the web!

Want to try something in SQL Server, but don’t have a server handy where you are? Check out SQL Fiddle – it’s a kind of web scratch pad for SQL code (not just SQL Server, by the way, lots of other platforms).

This is truly a beautiful tool with separate entry panes for schema and DML statements, as well as the possibility to view or download query plans for each query. And when you’re done editing, you can copy a URL and post on online forums, in e-mails, etc, so other people can run and test your code.

Processing order of a T-SQL statement

There are three different perspectives when you look at a T-SQL statement; the way it’s written, the way it’s evaluated, and the way it’s executed. They’re all important to understand, for different reasons.

An introduction to windowed functions

Windowed functions are a powerful feature of T-SQL, allowing you to perform advanced aggregates. They provide a very efficient way of doing this as soon as you just get the hang of the OVER() clause.

Decrypting SQL objects

Ever wished you could decrypt a database object in SQL Server? The good news is, you can, even in newer versions of SQL Server! This article will take you through the basics of how to decrypt a database object, and it will hopefully give you some deeper knowledge of how encrypted objects are stored in the database, and how to access them.

Locked out from SQL Server?

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.

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.

A short post on SQL injection.

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.

A prime number challenge.

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“.

Using OUTPUT with DML statements

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.

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.