T-SQL

• What is the optimal way to order a clustered index?

  Sep 25, 2018

  Someone recently was asking me about indexes and how the order of the columns affected the index.

  I have a table, and I want to create a clustered index on it. SSMS lets me choose the order of the columns when I make a clustered index. Logic would dictate that this must have some effect or else it wouldn’t be offered as an option. What factors are considered when determining the optimal order?

• T-SQL Tuesday #106 Trigger headaches or happiness?

  Sep 11, 2018

  Triggers are both a useful tool with a specific niche… and the devil. Thank you Steve Jones for hosting this week’s topic on triggers!

  On the left we have triggers and on the right we have constraints.

  When used appropriately, it’s a great skill to have in your repertoire. When used inappropriately, you will probably ruin someone’s day.

• How many Sequences can I have in SQL Server?

  Jun 27, 2018

  I thought this was an interesting question, but it makes sense to have some concern about it. If you are using Sequences over identity inserts, this typically means you need more control over how those numbers are handled from creation to archive. So what if you need a lot of different Sequences stored on your server to handle your needs? Will you run out of Sequence objects that you can create?

• 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.

• Featured Stack Exchange answer!

  May 4, 2018

  As many people I talk to about the pursuit of knowledge know, I advocate for a minimum of learning three new things per day. So typically I’ll subscribe to SQL newsletters, podcasts, blogs, and any other medium of information I can find to learn as much as possible. Well, I was really surprised when I found out an answer I posted on Stack Exchange was featured in Brent Ozar’s newsletter!

• Snapshot isolation transaction failed in database (Availability Group and SSIS woes)

  Apr 5, 2018

  The Setup:

  I have a SSIS package and it runs a relatively basic ETL setup. We take data from TableA, move it to TableB. TableB is a heap, we index it in our ETL and drop it afterwards. Now we join TableB to TableC. The reason we don’t go from A to C is because of the data relationship, it can cause a cascade join that creates BILLIONS of reads when we plug in some where predicate values.

• How do I check the metadata of my temporal table?

  Mar 27, 2018

  There was an interesting question recently about checking whether a table had been created with DATA_CONSISTENCY_CHECK, if the SYSTEM_VERSION option was set, and how to find the name of the historical tables.

  First up, DATA_CONSISTENCY_CHECK.

  I have finished studying A LOT on temporal tables for my 70-761 and I knew most of the answers off the top of my head. The question regarding DATA_CONSISTENCY_CHECK and seeing if that value persisted anywhere was a head scratcher though. It seemed no one had asked a question in that way before and I did not find any links or articles that talked about that aspect.

• My study guide to the 70-762: Developing SQL Databases

  Mar 13, 2018

  I couldn’t find a spreadsheet that would help track the topics, ETA, and other notes online, so I decided to compile all of my resources into a singular spreadsheet to help me keep track of studying and to help the community for those taking the 70-762. You can copy / make a copy of this and track yourself.

  Spreadsheet here!

  The tab Topic Resources has been taken from Daniel Calbimonte and his post on MSSQLTips. Huge thanks to him for taking the time to compile all of those resources on those topics!