Code

• Do Synonyms impact performance in SQL Server?

  Jun 19, 2018

  I tried an experiment recently to test whether synonyms impacted performance in SQL Server.

  Here’s our setup for the experiment:

  -- Experiment table
  CREATE TABLE test 
  (one INT NOT NULL, 
  two INT NOT NULL, 
  three INT NOT NULL, 
  four INT NOT NULL);
  
  -- Get records
  INSERT INTO test
  VALUES (1, 1, 1) 
  GO 10000
  
  -- Make synonym
  CREATE SYNONYM test2
  FOR test;
  
  -- Clear cache
  DBCC FREEPROCCACHE;
  
  -- Measure
  SET STATISTICS IO ON;
  SET STATISTICS TIME ON;
  
  -- Test
  SELECT * from test2;
  SELECT * FROM test;
  

  When experimenting, I ran each SELECT independently and ran DBCC FREEPROCCACHE (DON’T DO THAT IN PRODUCTION) in between run times.

• Skill showcase – Pet store OLTP database

  Jun 9, 2018

  I was involved awhile ago in a non-officially-competitive but competitive all the same project, where I needed to create a database and showcase my knowledge set. The requirements were straight forward, vague, and yet complex. The timeline was one day, so I had to be smart about my time and use the proper toolset.

  Here’s the assignment summarized:

  Create a pet shop database that involves inventory/point of sale. I plan to sell pets, toys, supplies, and food. Please create a DB definition that will allow me to track purchases, inventory, vendors, holiday sales, and taxes.

• Parameter validation failed in SSRS

  May 7, 2018

  Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError)

  I saw this error the other day and it took me a minute to recall how I’ve fixed this in the past.

  If you are unfamiliar with SSRS and Visual Studios, it can be a little “unfriendly” with some features. Specifically in this case, taking a parameter that used to be hidden and making it a visual integral part of the report. In my particular circumstance, this report is not testable in visual studio and has to be uploaded to a server in order to validate.

• Where did I put that thing in my SSIS project?

  Apr 12, 2018

  You may inherit a large SSIS project or create / use a large SSIS project someday, or perhaps there’s just a lot of packages. Either way, there’s going to come a day and time where you have to find something in your SSIS packages.

  Say you need to remove a column in a table but you’re not sure where that column is referenced in your SSIS packages? Perhaps you are having metadata issues and you need to find every reference to that column? Or maybe you want to see what packages reference what tables, etc.

• Boolean order of precedence in T-SQL

  Jan 4, 2018

  I’ve been seeing a lot people get confused with their where clauses lately. Someone wanted to return where (X = A) and (Y = B or Z = C), but what they get is a totally different result. Sure it’s physically written and viewed as (X = A and Y = B or Z = C), but logically it holds a different meaning.

  This demo will help illustrate this for you.