Find all triggers in a SQL Server database

Use the following small script to find all hidden triggers in a SQL Server database.  Pretty much all competent developers using SQL agree that using triggers is a bad practice, but few know how to find them before they have wasted a lot of development and testing time not understanding why things aren’t working the way they want them to (because of triggers doing behind the scenes work, undesirably).

Trigger_the_horse
Trigger the horse, with Roy Rogers.
-- Finds all "hidden" triggers in a database. Using triggers is not a best practice.
USE <database_name>;
GO
SELECT  name AS [TriggerName]
FROM    sys.objects
WHERE   OBJECTPROPERTY(object_id, 'IsTrigger') <> 0;

Entity Framework

blog-entity-framework1

On a recent enterprise ASP.NET MVC client project, I was introduced to using Microsoft’s Entity Framework for the first time. I was skeptical at first, thinking that an abstraction layer with LINQ over the database would slow things down performance-wise from the standard .NET calls to SQL procedures (there is a performance cost to using LINQ).  I have written and modified thousands of stored procedures, which work reliably well, but don’t have the same development, design, and maintenance advantages with LINQ, Entity SQL, and so forth.  Alternatively, using stored procedures requires a thorough understanding of SQL and the “impedance mismatch” between .NET object/procedural code and the set-based operations of a SQL engine.

I was blown away by the speed of the Entity Framework – honestly.  I’d never seen database transactions happen that fast when stored procedures were not part of the solution, and with Entity Framework, there is no need for a library of stored procedures.  Now part of it could have been the latest and greatest virtual SQL Server 2016 instances in use.

Setting that aside for a moment, I don’t know how one could go wrong with Entity Framework as a starting point for getting away from the standard repertoire (or jungle) of hundreds or even thousands of stored procedures, views, user-defined scalar and table-valued functions, all called from (usually) hand-coded DAL abstraction layers or form-classes in ASP.NET, WinForms, etc.  It’s typically quite a bit to manage on it’s own, and change-analysis on a database server is usually a very time-consuming and tedious undertaking, with or without the benefit of script source-control.  Entity Framework makes data-persistence much more manageable for everyone.

Industry was slow to adopt the Entity Framework due to performance bugs in the first versions. As as a result, Microsoft was forced to update it quickly, moving from v1 to v4 in fairly short order.  By the time the framework had evolved to version 4, industry adoption rates picked up pace and a few performance kinks were worked out.  As of early 2017, version 6 has been out for a while, and adoption is even better, for those enterprises that understand the benefit of adoption, and have the time and resources to implement it.

Benefits

  • One queries strongly-typed domain entities like: Customer, Order, LineItem, TimePeriod, etc.
  • It completely replaces a typical DAL layer.

Drawbacks

  • If any, the learning curve of using the many flavors of LINQ.

Summary

I have a very high regard for the Entity Framework 4 and higher (current iteration is 6) and look forward to using it in any client project where I can.

SQL Server *Worst* Practices

Here’s a link to a great article I read recently that summarized in a concise package things not to do when using SQL Server as a data-persistence solution, which is almost more important than what to do.

Note: SQL cursors especially – please never use cursors of any flavor in your stored procedures. There is almost always a better set-based method to accomplish your end goal.

As it’s not mentioned in the following article, I will add that not making use of SQL Server metadata is a mistake I see very frequently. What is metadata?  It’s data that describes other data or data structures. It’s particularly easy to implement with SQL Server once you know what to do and how to use it. There is probably no easier way to document a database for all users or stakeholders involved than to implement/update SQL Server metadata, and there are no good reasons not to utilize it.  It’s easy and freely available – just waiting to be used.  I have a set of stored procedures developed that make the process absolutely painless for keeping SQL Server database metadata updated at the table, column, view, and function/param levels (all the places that really count!), and across all environments (production, pilot, test).

https://www.mssqltips.com/sqlservertip/1707/sql-server-worst-practices/