The scenario is there should only be one AccountID/CreateDate combo in our table1. But some process ran and has duplicated some rows and we want to get rid of them. Dedupe time! Here is an example of deduping: Now let’s see what lives in our table1: Now let’s look at the Query to group the … Continue reading Dedupe it or lose it!
JOIN versus WHERE filtering
Intellectually I knew that query results can vary depending on where we decide to "filter" our data. In the JOIN condition, or in the predicate WHERE clause? The following example of this revealed itself recently. It struck me as such a simple and clear case I thought others might benefit from it. The situation arose … Continue reading JOIN versus WHERE filtering
How I Learned to Stop Worrying and Add The Nonclustered Index
In my post about transactional replication we configured the dbo.Badges table for replication. Recall that this is a table that lives in the StackOverflow database. Let's pretend that over time the production table we replicated has grown to billions of rows, and our reporting relies on hitting the dbo.Badges subscriber table in the SO_small … Continue reading How I Learned to Stop Worrying and Add The Nonclustered Index
The Path to Enlightenment – SQLSkills style!
Too often in my career I have heard "SQL Server doesn't scale as well as DBMS x" or "We don't need a DBA. You just install SQL Server and it runs itself". Both of these are misconceptions that are reinforced by a lack of people who truly understand the deep magic that is SQL Server … Continue reading The Path to Enlightenment – SQLSkills style!
Setup a Simple Transactional Replication lab
At work we lost our super star DBA some months back, and though he is sorely missed, this has created opportunities for me to grow in my SQL Server knowledge. One of the key components of our Business Intelligence environment (my world!) is our replication between PROD and the Data Warehouse. Because this replication is … Continue reading Setup a Simple Transactional Replication lab
Can Table Partitioning Improve Query Performance?
At work we have tables containing billions of rows, and are hundreds of GB in size. The business intelligence queries we run against them in the reporting database are often focused around dates and not accountids / productids as they are in PROD. For these large tables, it is sometimes useful to split the records … Continue reading Can Table Partitioning Improve Query Performance?
How much space will table compression save me?
At work we have a reasonably large table with a NVARCHAR(MAX) column. This column stores HTML and other user generated content. This table is on a SQL Server 2014 instance, so cannot take advantage of new compression technologies in SS 2016. Looking at the estimated benefits via query: Well … bummer! Looks like no savings … Continue reading How much space will table compression save me?
Nice date labels in SSRS
You have a create date in Datetime format (ugly!). You want to create a nice row label of out that for your SSRS report. Well here ya go!: Set the text box to an expression: =MonthName(Month(Fields!CreateDate.Value))&" "& Year(Fields!CreateDate.Value) This will give you a nice date label like "December 2015". For an even snazzier label, I … Continue reading Nice date labels in SSRS
Virtual logfile (VLF) hell…
At one point in my career I inherited a database that had suffered from neglect (to be kind). The logfiles had be set to autogrow 1MB at a time, and this had happened ALOT! Interestingly this rather small database was taking a surprisingly long time to restore, Knowing that a high number (>1000) of VLFs … Continue reading Virtual logfile (VLF) hell…
Create an index on a table in Transactional Replication
Not long ago I took over a database that uses Transactional Replication. Not long after I needed to add an index. So here is the syntax and misc I found for doing this: The important point is that you use ONLINE=ON, and since our TEMPDB is on SSD, go ahead and use SORT_IN_TEMPDB.
