Good question…for production databases I try (on some kind of regular basis!) to keep tabs on the table structures and their “overall health”. This means I try to be proactive about table size, proper design, fragmentation, etc. Over the years I have collected a series of queries that help in this regard, and decided recently… Continue reading How is your Table Health?
Do not mix (or interleave) DDL and DML statements in your stored procedures. I see this all over the internet in best practice articles for coding T-SQL. What does this mean, and is it true? Let’s dive into this… DDL = Data Definition Language. That means any statement that defines any data storage…all CREATE/ALTER TABLE,… Continue reading Should I mix DDL and DML Statements?
This is a question I have often asked myself after reading an article about TempDB configuration. Most of the time I see only one TempDB data file on a SQL Server instance (that’s the default when SQL Server is installed), but I see lots of suggestions/best practices on the internet for multiple; one data file… Continue reading Should I have multiple TempDB Data Files?
We just found this issue in some of our stored procedures. We are upgrading our SQL Server 2008 and 2008 R2 databases to SQL 2014 when this appeared. I don’t believe that the upgrade process flagged this as an error, and since the databases were upgrading by restoring the backup files into SQL 2014 the… Continue reading Date and Time Add Incompatibility
A CTE is a Common Table Expression in T-SQL. It was introduced in SQL Server 2005. This T-SQL construct can be used to gather information for your query much like a sub-select or a view. Many developers ask me when to use a CTE and when to use some other method and my answer is…it… Continue reading CTEs and When to Use Them
Processing rows in a VLT (very large table)…something that many of us encounter from time to time. I am either asked to code something or am asked to performance tune code that someone else has written to process one or more VLTs about once a year. It happened again recently and made me think about… Continue reading Processing Rows from Very Large Tables
I just had a sudden flashback to my high school English class…oh wait, this is SQL Server, not Shakespeare… Today I’d like to cover the best practice of using the command SET NOCOUNT ON at the top of all of your stored procedures. What does it do? Why is this a best practice? SET NOCOUNT… Continue reading To NOCOUNT, or not to NOCOUNT, that is the question…