I’ve run into some developers that characterize like this because it’s not a “real developer’s language” yet, the people that feel this way have only scratched the surface. So what did we learn? SQL is a language that is often under estimated as being very simple. To query you just need “SELECT FROM WHERE”. When the two procedures are run together we see that the query that uses a join costs only 17% of the total batch.
#ADVENTUREWORKS2012 ERD MODEL CODE#
It’s clear that the production code costs much more and consumes more resources. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. The combined cost of the two queries is 0.0312159. The existing production code produces the following query plan Let’s start with the existing production code Let’s compare the two methods of retrieving a list of addresses. I’m always open to learning and understanding the benefits of different approaches, just like using c# to write the database DDL as mentioned above. INNER JOIN Person.BusinessEntityAddress BEA ON (A.AddressID = BEA.AddressID) SELECT A.AddressId, A.AddressLine1, A.AddressLine2, A.City, A.StateProvinceId, A.PostalCode, A.ModifiedDate I’m sure Jen from MidnightDBA would like these comments. I always like when comments are in SQL code, especially when they are left completely untouched from their original template form. Next, not one but two stored procedures were created.
![adventureworks2012 erd model adventureworks2012 erd model](https://i.ytimg.com/vi/R8PqWeNalEU/maxresdefault.jpg)
Note that I modified the production code to run on my test system in AdventureWorks2012 and to anonymize the code.įirst, a user defined table type was created.Ĭan you see where this is going? If you can you’ve probably already planted your palm firmly over your face. These was done a way that I’ve never seen before. Normally you’d think a simple JOIN would suffice to get a list of addresses or phone numbers for a customer. A “mapping” table was created for the tables so they could map to a customer. The database included many to many tables for customers who have addresses and phone numbers. I’ve seen a lot of really creative SQL but these were completely puzzling. NET MVC project running on SQL Server 2016 where I found some pretty interesting stored procedures. For now let’s get to something more interesting to us SQL folk. I may follow up with more on this topic in another post. There’s not really a need for individual DDL scripts. They are a really helpful way of deploying DDL and you can design the tables any which way you like. It’s just outright wrong, right? If you haven’t read about migrations, you should. You’d start with a business analyst gathering requirements, build out the database ERD, and then start coding.īuilding the tables in visual studio and thinking about the application code first is not right.
#ADVENTUREWORKS2012 ERD MODEL PROFESSIONAL#
Naturally, being a life long database professional I’d say that Database First is the only way to properly approach a project. There are essentially two principles of development, Code First and Database First. I’ve been working with SQL Server for 18 years and over that long span I’ve seen a lot of different techniques for tuning and development.