Doing a database restore may not be the most common task a database professional will ever do, but it’s a lot more frequent than you might think. Operational restores are not exactly uncommon for example. Now, when you are doing a recovery like this, what is common is that there are people in the database.…
Tag: T-SQL
5 Tips for Friday: Naming Conventions
I want to talk about some best practices concerning naming conventions. Most teams or organizations will come up with their own naming conventions for things. This post is not here to tell anyone how to define their naming conventions, especially for things outside of SQL Server. I want to talk specifically about things inside the…
Time Zones are a Drag ... Seriously
Time Zones were definitely being a drag today. I got an email from one of the developers at work asking about the performance difference between 2 queries. The only difference between the 2 queries is that one of them uses the AT TIME ZONE clause that was added in SQL Server 2016. I have not…
5 Tips for Friday: Waits and Queues
This Friday I want to talk about some best practices for troubleshooting queries using waits and queues. Waits and queues are excellent tools for trying to figure out why a query is running slower than you think it should be running. When a query has to wait for some reason, the query enters a suspended…
5 Tips for Friday: Massively Parallel Queries
My day-to-day jobs deals mostly with performance tuning queries that run massively parallel. Like hundreds or thousands of the same query with different parameter values. With a normal query, 1 extra second of run time does not matter much. If the query has 2000 versions running at the same time, that’s an extra 2000 seconds…
Which is the performance killer: SELECT INTO or INSERT INTO?
There are many ways to kill performance in a script or stored procedure. However, not many think about the possibility that adding columns to a temporary table can kill performance. Here, I’m going to show you how a simple design decision – using INSERT vs ALTER TABLE – can have a huge impact on your procedure…
5 Tips for Friday: Deployments
I had some very thoughtful conversations (not lectures … really) this week about deploying changes to SQL Server databases. I want to share some of those best practice tips here with you today. Don’t rely on manual steps: As I see it, there are two reasons why manual steps are bad. Humans are imperfect, are…
sys.objects VS helper functions (OBJECT_ID, OBJECT_NAME, etc)
I’ve you’ve done much work with the system views (DMVs for example) then you’ve had to translate an object_id into a schemaname and objectname and vise versa. To do that you’ve either used the functions OBJECT_ID, OBJECT_SCHEMA_NAME, and OBJECT_NAME or sys.objects and sys.schemas. So what’s the difference? Well, each version has its pros and cons.…
Small identity columns
We frequently talk about dealing with outgrowing INT identity columns. What we don’t talk about all that often is small tables. Where we don’t even need an INT. An IDENTITY column can be any of the following data types tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0). INT and BIGINT we talk about all the time,…
The Alpha and the Omega. The BEGIN and the END.
The BEGIN/END block is a fairly standard thing if you’ve ever done any coding, but it never hurts to do a little review. Not to mention that there are always people just starting to learn about a subject. So what is a BEGIN/END block? They are boundaries that define a group of SQL statements. Transact-SQL…