Posts filed under: sql server
Data flow in decoupled solutions
Many of the current products we have released into the wild are using an architecture such that the standard product is not tightly coupled with any particular sites custom ETL transforms. We gain many benefits from this approach, such as:...
The Seven Sins against TSQL Performance
SQL MVP Grant Fritchey posted his “The Seven Deadly Sins against TSQL Performance” article quite a while ago, but it is still relevant today. If you are new to TSQL, or just want to understand some common things that kill...
Announcements at SQL PASS 2016
SQL PASS happened out in Seattle a couple weeks ago and Microsoft announced some new features they are bringing to the SQL Server platform. I will briefly discuss some of the more interesting items below. SSAS in Azure Microsoft is...
Tally, Oh! A table of numbers, splitting strings, and more
I was recently asked, “What is the DW.Numbers table used for in our database?” The simple answer is that it is can be used to perform many tasks in a set based operation that would normally require a loop, or...
The Value of Naming Constraints
In certain designers, such as SQL Server Management Studio table designer, when you create certain constraints (Primary and foreign keys, defaults, and check constraints) they will be given a name that contains a GUID to verify the name is unique. This...
Performance Troubleshooting without Data? Enter DBCC Clone Database
With SQL server 2014 SP 2 Microsoft added a new feature that sounds pretty sweet for troubleshooting some performance problems. It is called Clone Database. In a Nutshell Clone database gives you a copy of the specified databases without any...
Improve data quality by addressing non-stated requirements
I read an article by David Poole a few weeks ago where he talks about addressing non-stated requirements. I believe this is an area that is quite often overlooked when data modeling; at least up front. He sums it up...
Rapid product delivery shines the spotlight on issues for SSMS
In an article posted by Phil Factor on SQL Server Central he relates how SQL Server Management Studio had to undergo some pretty significant changes in order to support rapidly delivering improvements. “SSMS got very little love over the years...
Why have a date dimension?
Many of our products utilize a date table (dimension in warehousing speak). They make quite a few things easier when you are doing reporting over denormalized data. For example you no longer have to write case statements in every report...
A slow death from Implicit Type Casting
A common code smell that attracts my attention right away is when I see an explicit conversion being done, typically done using CAST or CONVERT. However, implicit type casting can be worse and is typically not readily apparent. During code reviews I found...