Isolation levels affect how aggressively SQL Server places and holds locks on tables and schemas. Get too lazy and you’ll end up with phantom data and dirty reads. Be too zealous, and you’ll end up troubleshooting deadlocks. Here’s an overview of the different types of isolation levels available, to help you choose which one is best for you.
Category: SQL Server concepts
Catalog views: Database objects
Catalog views are system views that expose most facets of the server and its databases in a tabular form. In this tutorial, we’ll take a closer look at database objects and how they’re represented in a database’s catalog views.
Top 10 T-SQL worst practices
As part of an improvement process (any process, not just database development), you can start by asking yourself “How could I make this process as bad as possible”. In fact, when you think of how to royally screw something up, all you have to do is flip the answer around, and there you have a good starting point for improving your process, work, whatever.
Here are a few T-SQL “worst practices” to get you started on improving your database design and development practices. I’ve personally seen all of these in the field, and I think you might recognize some of them, too.
A short introduction to application roles
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.
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.
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.
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.
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.
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.