Application roles provide a practical way to assign application-specific permissions in your database and to make sure that your applications always use a defined login. Not to be confused with actual roles, application roles are more like users in the database.
Category: T-SQL
Collations and case sensitive wildcards
The other day, I ran across the following issue: I was looking to split a value using upper case and lower case characters. Here’s an example query:
SELECT PATINDEX('%[a-z][A-Z]%',
'testValue' COLLATE Finnish_Swedish_CS_AS) AS offset
I expected this query to return the value 4, because at that offset in the string, there is a lower case character (t) followed directly by an upper case character (V). However, in practice, this query returns the value 1.
Using Service Broker to send messages
Until now, I’ve never really looked any closer at Service Broker and message queues in SQL Server, but it turns out it’s a really useful feature if you need asynchronous processing or any other kind of queued messaging logic. Messages can be sent within a database, a server, or even between servers.
Date formats and conversions
Have you noticed how date conversions can seem a bit arbitrary at times? How a string value is translated to a date depends on a number of factors, including how you perform the conversion and what language settings you have set for your connection. But there are ways to limit your conversion headaches.
A URL encoding function in T-SQL
A while ago, I needed to create a URL encoding function in T-SQL. URL encoding is what happens when you translate special characters (basically anything that isn’t an alphanumerical) so they’ll fit in a URL.
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.
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“.