Architecture and Design

• What is DAMA?

  Sep 24, 2019

  DAMA is the Data Management Association International which is an association of data professionals. They have curated a certification program, framework, and documentation when it comes to database. There are many frameworks out there that you can use when dealing with data architecture:

  • DAMA
  • DCAM
  • CMMI
  • IBM
  • Stanford
  • Gartner

  I’m not going to go deep into the others or their differences right now, but here’s a link from datacrossroads that talks about them all briefly. So, what exactly is the framework? Well, it is essentially a high-level guide to help obtain or work towards Data Governance while enhancing your company’s data maturity.

• How to keep track of how many records were truncated?

  Oct 9, 2018

  Someone had an issue where a third party app would truncate a table for maintenance and they had no control over the truncate and when it happened. The issue was that they needed to know how many records were in the table before the truncate occurred.

  Let’s start by understanding what DELETE and TRUNCATE are and how they work.

  When you delete from a table, you are removing rows. You can specify what rows to delete with a WHERE clause. When you TRUNCATE a table, it is not logging what rows are deleted and it does perform minimal logging. TRUNCATEis a great way to clear a table without causing the Transaction Logs to balloon.

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

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