If you are using Change Data Capture (CDC) it can be very useful to look at the CDC State table to understand what is happening. I am writing this in the context of using SSIS 2012 CDC components; in this case the CDC Control Task. The CD State can be very helpful to determine why data isn’t getting loaded right after an initial load for instance, which is when I decided to dig into the...
Change data capture was introduced in SQL Server 2008 and has been pretty popular ever since. The basic concept behind it is that all changes (inserts, updates, deletes) are captured and the details made available in a consumable manner. This means ultimately that all changes will be written to a table you can query to see everything that has been going on. In SSIS 2008 it was a little cumbersome...
One dimension you can be positive will always make it’s way into your data warehouse is the Date dimension. Over the past couple of years I have taken a look at a lot of Date dimension scripts and found quite a few good ones. Well recently I needed to add fiscal date to the dimension and the script I had didn’t let me do that properly. Rarely does the fiscal year start and end on...
If you have ever closed a query window by accident only to wish you could get back your work an instant later you may be in luck. If you ran the query in question there is hope for you. The query below will return queries that have been run for you including the time and query text. I haven’t figured up a way to get the user that executed the query just yet. So if you have that tidbit...
What is the difference between COUNT(*), COUNT(1) and COUNT(ColumnName)? A mystery that will never be known… ok that was a lie, but the rest of this blog is not a lie, just to be clear. 🙂 COUNT(*) – Number of records in the table regardless of NULL values and duplicates COUNT(1) – Number of records in the table regardless of NULL values and duplicates **IMPORTANT NOTE: The 1 does NOT refer to an...
I’ll be honest, I am partially writing this because I always forget how to reset the identity column on a table and I want to be able to just pull up my blog and look it up. Yet again today I had to do this and I couldn’t remember the exact syntax, so here it is. The next record is going to be the seed value plus 1, or in the case of this example I will use zero so the first record...
There will inevitably come a day when you want to take a look at the metadata of the tables inside a database. This can be accomplished one of many ways, the quickest and easiest is to expand the columns node inside the Object Explorer in SQL Server Management Studio (SSMS). What if you want to look at your entire database though? Running a query against INFORMATION_SCHEMA.COLUMNS is a great way...
I know it has been almost a month since I posted last and this is not really a “real” post, but I assure you that new content is on the way. I have a couple things in the works that I hope to post this week. It has been a crazy month and I have been really busy, so I apologize. I’m in the middle of moving some Access databases off Access and ran into some pitfalls I want to...
There will come a day, if it has not already come and gone, when you will want to search through your stored procedures because you don’t remember which one did what you want to do. Did that make sense? Because I don’t know if it did. Anyway, here is what we have… You A bunch of stored procedures Something you are looking for inside one or more of your stored procedures A...
When doing calculations in SQL Server queries it is important to know that anything without a decimal is going to be treated as an integer and therefore will cause the result to be an integer as well. For instance, lets take 598/128 and look at some results. This will obviously give us a decimal for a result…or at least it SHOULD. SELECT 598/128 = 4 This is clearly NOT an even 4, however...
Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).