Category Archives: TSQL

Setting up Cross Database Queries in Azure SQL Database

A question that I answer what seems like all the time is how to run cross-database queries against Azure SQL Database. So rather than answering those questions repeatedly I figured I should blog my response up so from here on out I can just forward the link to this blog on to my customers and others interested.

Most people I talk to still think that cross-database queries in Azure SQL DB aren’t possible. But thanks to a new feature called “Elastic Query”, we can issue cross database queries. There’s actually a few different types of cross database queries that Continue reading Setting up Cross Database Queries in Azure SQL Database

Microsoft & R Programming Session Materials & Resources Available

Last Thursday night I had the pleasure of speaking with Gregory Kramer and his friends at the Madison, WI Power BI User Group. We took an introductory look at how Microsoft’s acquisition of Revolution Analytics is making serious waves across the Microsoft data platform. We also walked through leveraging R in Power BI and SQL Server 2016.

New to R & Power BI? Start here.

R visuals in Power BI

Continue reading Microsoft & R Programming Session Materials & Resources Available

TSQL Script to Find Foreign Key References to a Given Column

It’s always kind of a pain to have to hunt down all those foreign key references so you can address the issues. So I put this script together (based on a script found on StackOverflow) in order to help me find all the required information related to a particular column in a specified table. I’m mostly posting this for my own reference later and for anyone else that may find this useful, so enjoy!

SELECT OBJECT_NAME(f.object_id) as ForeignKeyConstraintName,
    OBJECT_NAME(f.parent_object_id) TableName,
    COL_NAME(fk.parent_object_id,fk.parent_column_id) ColumnName,
    OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName,
    COL_NAME(fk.referenced_object_id,fk.referenced_column_id) as ReferencedColumnName

FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fk 
        ON f.OBJECT_ID = fk.constraint_object_id
    INNER JOIN sys.tables t
        ON fk.referenced_object_id = t.object_id

WHERE OBJECT_NAME(fk.referenced_object_id) = 'your table name'
    and COL_NAME(fk.referenced_object_id,fk.referenced_column_id) = 'your key column name'

Here’s a picture of what the results look like. I ran this query against the ReportServer database used for SSRS in case you were wondering.

T-SQL to find FK key columns

If you want to find every Foreign Key in your database, just eliminate the Where clause to bring back all the FKs. Hopefully you found this as useful as I did.

Generate a Date Table via Common Table Expression (CTE)

Occasionally I find myself needing to generate a small table with a list of dates for various queries I may be running. To do this, I usually leverage the Date dimension since I do most of my work in BI environments with a traditional data warehouse. But if you don’t have access to a Date dimension table, you can quickly generate a date table using Continue reading Generate a Date Table via Common Table Expression (CTE)

T-SQL Script to Dynamically Create Table, Build Clustered Columnstore Index, and Partition Switch

Recently myself and Mitchell Pearson (blog|twitter) were working on a project for a client that required us to load a ton of data (dozens of TBs) into some tables each built with a clustered columnstore index. We discovered during testing that the fastest way to get that much data into the clustered columnstore index is to create an empty uncompressed table, load the data into the uncompressed table, then apply the clustered columnstore index to the table, and partition switch the data into the main table. In order to facilitate this, I created this script to dynamically create a copy of the target table (without the columnstore index), create the clustered columnstore index, and then do the partition switch automatically.

Continue reading T-SQL Script to Dynamically Create Table, Build Clustered Columnstore Index, and Partition Switch

Script To Populate AdventureWorksDW DimDate

I do quite a bit of training for Pragmatic Works so I find myself working a lot with the AdventureWorksDW data warehouse database. AdventureWorksDW is a great test database if you’re wanting to demonstrate a concept or test a theory, but one of the things that has always bugged me is that the date dimension (dbo.DimDate) has holes in the data! Having a complete date dimension is important when working with SSAS. Well yesterday was the final straw, so I put together the following script that will fill in the missing dates in DimDate. You can specify a start date and end date and make the AdventureWorksDW DimDate date dimension as big as you like. Enjoy!

BEGIN TRAN 

declare @startdate date = '2005-01-01',
    @enddate date = '2014-12-31'

IF @startdate IS NULL
    BEGIN
        Select Top 1 @startdate = FulldateAlternateKey
        From DimDate 
        Order By DateKey ASC 
    END

Declare @datelist table (FullDate date)

while @startdate <= @enddate

Begin 
    Insert into @datelist (FullDate)
    Select @startdate

    Set @startdate = dateadd(dd,1,@startdate)

end 

 Insert into dbo.DimDate 
    (DateKey, 
        FullDateAlternateKey, 
        DayNumberOfWeek, 
        EnglishDayNameOfWeek, 
        SpanishDayNameOfWeek, 
        FrenchDayNameOfWeek, 
        DayNumberOfMonth, 
        DayNumberOfYear, 
        WeekNumberOfYear, 
        EnglishMonthName, 
        SpanishMonthName, 
        FrenchMonthName, 
        MonthNumberOfYear, 
        CalendarQuarter, 
        CalendarYear, 
        CalendarSemester, 
        FiscalQuarter, 
        FiscalYear, 
        FiscalSemester)


select convert(int,convert(varchar,dl.FullDate,112)) as DateKey,
    dl.FullDate,
    datepart(dw,dl.FullDate) as DayNumberOfWeek,
    datename(weekday,dl.FullDate) as EnglishDayNameOfWeek,
    (Select top 1 SpanishDayNameOfWeek From DimDate Where EnglishDayNameOfWeek = datename(weekday,dl.FullDate)) as SpanishDayNameOfWeek,
    (Select top 1 FrenchDayNameOfWeek From DimDate Where EnglishDayNameOfWeek = datename(weekday,dl.FullDate)) as FrenchDayNameOfWeek,
    datepart(d,dl.FullDate) as DayNumberOfMonth,
    datepart(dy,dl.FullDate) as DayNumberOfYear,
    datepart(wk, dl.FUllDate) as WeekNumberOfYear,
    datename(MONTH,dl.FullDate) as EnglishMonthName,
    (Select top 1 SpanishMonthName From DimDate Where EnglishMonthName = datename(MONTH,dl.FullDate)) as SpanishMonthName,
    (Select top 1 FrenchMonthName From DimDate Where EnglishMonthName = datename(MONTH,dl.FullDate)) as FrenchMonthName,
    Month(dl.FullDate) as MonthNumberOfYear,
    datepart(qq, dl.FullDate) as CalendarQuarter,
    year(dl.FullDate) as CalendarYear,
    case datepart(qq, dl.FullDate)
        when 1 then 1
        when 2 then 1
        when 3 then 2
        when 4 then 2
    end as CalendarSemester,
    case datepart(qq, dl.FullDate)
        when 1 then 3
        when 2 then 4
        when 3 then 1
        when 4 then 2
    end as FiscalQuarter,
    case datepart(qq, dl.FullDate)
        when 1 then year(dl.FullDate)
        when 2 then year(dl.FullDate)
        when 3 then year(dl.FullDate) + 1
        when 4 then year(dl.FullDate) + 1
    end as FiscalYear,
    case datepart(qq, dl.FullDate)
        when 1 then 2
        when 2 then 2
        when 3 then 1
        when 4 then 1
    end as FiscalSemester

from @datelist dl left join 
    DimDate dd 
        on dl.FullDate = dd.FullDateAlternateKey
Where dd.FullDateAlternateKey is null 




COMMIT TRAN
I’ve tested the script against AdventureWorksDW2012 and AdventureWorksDW2008R2 and it worked great.
Let me know if you found this useful! Thanks for reading!

Comparing and Syncing Data With Data Inspector

image

Earlier this month Pragmatic Works released Workbench. Workbench combines the powerful tools many of you are already using with an entire host of new, powerful features part of DBA xPress.

DBA xPress features tools designed to make designing, maintaining, and administering your SQL Server databases easier than before. Now you can easily do things like visually navigate your databases, visualize schema dependencies, compare, synchronize, script, and navigate schemas, and more.

image

One of my personal favorite tools included with DBA xPress is the Data Inspector. Data Inspector allows you to create a comparison between the data stored in two different databases. Not only can you compare the data between databases, but you can also copy the data from one database to another.

With Data Inspector, there are three ways to sync data between databases. Data can either be synced directly between databases with the Data Inspector Synchronization Wizard,

a script can be generated to synchronize the data for you, or a hybrid of the first two options will directly sync the data and generate the script for you.

I’m very excited about this tool and am looking forward to using it to speed up my data warehouse development projects. Head over to PragmaticWorks.com and check out DBA xPress.

Create Date Dimension Table Script

On a recent project I created a Date dimension script that I thought, I’d share (mostly so I would have access to it from anywhere). I based this script on one written by Mike Davis, but I included a few changes such as a first and last day of month flag, open flag, relative time period attributes, and a few others. Enjoy! Continue reading Create Date Dimension Table Script

Recursive Queries and The Power of Common Table Expressions

Yesterday I was working on a requirement for a client that asked me to show a list of Organizational Units and the every single parent Organization above every Organization. The source table gave each Org Unit’s ID with their parent Org Unit’s ID. In Oracle’s PSQL, this requirement was met using the Start With and Connect By statements. In TSQL, however, there is no such thing. To meet this requirement, I could have written multiple select statements Union’ed together with differing amounts of joins back to the OrgUnit table to go up in the OrgUnit hierarchy multiple levels, but this would have been extremely painful, time consuming, and the query would have performed very poorly. Instead, I used the magic of Common Table Expressions (CTE) to build a simple query with a recursive join.

If you’re not familiar with CTE’s, check out this link about using Common Table Expressions.

For this example, I’m using the DimEmployee table in the AdventureWorksDW database. So let’s take a look at the query and then we’ll discuss it.

;with a as ( /*This select statement is our anchor statement. This will bring back our top level employees that do not report to anyone.*/ Select EmployeeKey, /*If the ParentEmployeeKey IS NULL, then we want to set the EmployeeKey as the ParentEmployeeKey*/ CASE WHEN ParentEmployeeKey IS NULL THEN EmployeeKey ELSE ParentEmployeeKey END as ParentEmployeeKey, FirstName + ' ' + LastName + ', ' + Title as Boss, FirstName + ' ' + LastName + ', ' + Title as Employee, 0 as [Level] From DimEmployee where ParentEmployeeKey is null Union All /*This select statement joins our source table back to our CTE using the ParentEmployeeKey.*/ Select e.EmployeeKey, e.ParentEmployeeKey, a.Employee as Boss, e.FirstName + ' ' + e.LastName + ', ' + e.Title, [Level] + 1 as [Level] From DimEmployee e /*This is our recursive join back to our CTE.*/ inner join a on a.EmployeeKey = e.ParentEmployeeKey ) Select * From a

The first thing you’ve probably noticed is that my CTE contains two select statements union’ed together. The first select statement is whats called our anchor statement. The first select statement only brings back our top level employees that do not report to anyone. In the case of AdventureWorksDW, there is only one top level employee (Ken Sanchez, CEO), but your situation could have multiple top levels.

The second select statement returns all employees, but it does something very interesting. The select statement actually join DimEmployee to the CTE it is contained in, thus creating a recursive join. By joining DimEmployee.ParentEmployeeKey to the EmployeeKey in the CTE, we are able to recursively join our results back to itself over and over until no records with a ParentEmployeeKey are left.

But if you remember, my requirements were to show every ParentEmployeeKey in the hierarchy of each Employee. So our first CTE alone isn’t going to cut it. To get all ParentEmployeeKey’s in the hierarchy, we will need a second CTE with a recursive join. Here is the select statement that returns those results.

;with a as ( /*This select statement is our anchor statement. This will bring back our top level employees that do not report to anyone.*/ Select EmployeeKey, /*If the ParentEmployeeKey IS NULL, then we want to set the EmployeeKey as the ParentEmployeeKey*/ CASE WHEN ParentEmployeeKey IS NULL THEN EmployeeKey ELSE ParentEmployeeKey END as ParentEmployeeKey, FirstName + ' ' + LastName + ', ' + Title as Boss, FirstName + ' ' + LastName + ', ' + Title as Employee, 0 as [Level] From DimEmployee where ParentEmployeeKey is null Union All /*This select statement joins our source table back to our CTE using the ParentEmployeeKey.*/ Select e.EmployeeKey, e.ParentEmployeeKey, a.Employee as Boss, e.FirstName + ' ' + e.LastName + ', ' + e.Title, [Level] + 1 as [Level] From DimEmployee e /*This is our recursive join back to our CTE.*/ inner join a on a.EmployeeKey = e.ParentEmployeeKey ) , b as ( /*Once again this is our anchor statement. We are only bringing in Employees who report to someone besides themselves.*/ Select EmployeeKey, ParentEmployeeKey, Boss, Employee, [Level] From a Where ParentEmployeeKey != EmployeeKey UNION ALL /* This is where it gets tricky. In this recursive join, we join on the ParentEmployeeKey's in CTE a with the EmployeeKeys in this CTE, but we display the ParentEmployeeKey of this CTE. This allows us to show the parent of the parent recursively. Essentially we are going back up the hierarchy to display the boss of each level in the hierarchy.*/ Select a.EmployeeKey, b.ParentEmployeeKey, b.Boss, a.Employee, b.Level From a inner join b on a.ParentEmployeeKey = b.EmployeeKey ) /* In the final select statement, we must once again query our first CTE, a, to gather the employee that do no report to anyone. Those employees are at level 0. */ Select EmployeeKey, ParentEmployeeKey, Boss, Employee, [Level] as LevelsDownFromTop From a Where [Level] = 0 union all /* Then we query CTE b to get all the other employees along with every employee above them in their hierarchy. */ Select * From b Order by employeekey, [Level]

 

 

 

 

 

 

 

 

 

 

Essentially what we are doing in our second CTE, called “b”, is we are joining on the ParentEmployeeKey’s in the first CTE, “a”, with the EmployeeKey in b. This allows us to show the parent of the parent so we can go up the hierarchy and return all the bosses in an employee’s hierarchy. Here is a screen shot of some of the results.

QueryResults

 

As you can see, the third record shows that Guy Gilbert reports to Jo Brown. One level up from Jo Brown, we see that Guy Gilbert reports to Peter Krebs, who is Jo Brown’s boss. And finally we can see that Guy also reports to Ken Sanchez, since Ken is the CEO.