Posts tagged with: sql
Order Matters: Using select to set variables
A quick post today. SQL allows you to set many variables at the same time using a select statement. However, many people do not realize that when you do this the order the variables are set in the select statement...
Warning: RESEED Changed from SQL 2008 R2 to 2012
Recently, someone on my team had an issue with automated database tests failing and we discovered a change Microsoft made to the SQL engine. If you use the DBCC CHECKIDENT command to RESEED a table it could impact you as well. Our...
Code Writing Code Round 1 – CRUD Statements
Unfortunately, things are not that easy for us mere mortals. However, we can make many repeatable tasks much easier by automating them or supplying tools to simplify them. I was having a conversation with someone the other day who saw...
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...
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...