Posts tagged with: sql
SQL Management Studio 17.1
Starting in 2016 SQL Server Management Studio got its own team at Microsoft and after many years of very little improvement we are starting to see some pretty consistent releases. They just recently released SSMS 17.1. It is compatible...
When is Select * ok
Generally speaking SELECT * should be avoided. There are multiple reasons for this, including: Requesting columns you do not need may force the optimizer to use more objects than needed or pull more data to memory than needed. Save some...
Optimization Smarts! Join Simplification
Someone recently read a post by Erik Darling over at BrentOzar.com titled Is it ever worth adding indexes to table variables. They were a little confused by the post and asked me about it. I thought I would break it...
Idempotent statements
Idempotent statements are those where applying the statement multiple times yields the same result. Commonly in database development this is significant during deployment activities. If we create a new table the deployment script will merely have the CREATE TABLE syntax....
SSIS Package Parts First Impressions
Reading the way package parts were described in the SQL 2016 release notes I was pretty excited. I remember having hundreds of SSIS packages where the overall structure was fairly redundant, this was before BIML was a thing, and package...
Gaps and Islands – Determining Continuous Hours
T-SQL Master Itzik Ben-Gan covered different solutions in SQL Magazine quite a few years ago, and there are many other articles out there about it. I thought I would give a current example we were solving where this came into...
Linked Server Inconsistent Meta Data Error and Synonyms
* Featured image from In most of our more recent tools we have a single layer in the ETL Staging solution that understands all of the connections to other systems / databases that need to be made. This layer is...
Moving SET NOEXEC OFF in SQLCMD deployment scripts
Currently when we build deployment packages we try to validate that the deployment is properly configured for the customer site to reduce errors. We do this by validating the version number, the database it is pointed to, connections are working,...
Deployment FAQ: Oracle Driver and Linked Server Setup
For many of our tools we routinely need to pull data from an Oracle server. In order to connect; SQL Server needs to understand how to talk to Oracle. This is true whether you are using a linked server to...
Deployment Variables Anti-Pattern and Site Configurations
This example has been in my list to post on for quite some time now and a recent conversation reminded me to bump it up the list. The number one benefit of database projects is definitely the compiler, or as...