I love Entity Framework. I also like (not love) nHibernate. That's right, as a DBA and data professional, I'm telling you I love Object/Relational Mapping tools (ORM). I think this is a technology set that the DBA needs to more tightly embrace. Let me tell you why. Most of the Queries I know that the biggest pushback against Entity Framework (EF) and it's fellow ORM tools is that they generate crap code. I know this to be true. I've seen it. ORM tools can, and do, generate seriously poor T-SQL. That's not to mention the N+1 problem and a few others. However, as you see from the article in that link, these problems and how to avoid them are very well defined. You don't have to suffer from the issues.…
SQL Server provides multiple ways to perform most functions. In order to maximize your understanding of how SQL Server works and have as many different mechanisms as possible for getting work done, you’ll use the GUI and TSQL to learn how to create and drop databases. You can then use whichever mechanism works best for you at the moment. Using each method, we’ll first create a database using the least amount of work possible, just so you can see how easy it is create a database. We’ll go over how to remove databases from the system, getting rid of the database you just created. From there we’ll drill down to create another database, exploring some of the different mechanisms you can use to change how databases get created. Then we’ll…
The best way to learn any software is to start using it. There are a bunch of software tools in the SQL Server toolbox, but the biggest and most important is SQL Server Management Studio (SSMS). SSMS is where you'll spend most of your time when you start to work with SQL Server. It provides a very large series of graphical user interfaces for creating databases, setting up security, reading data out of the database, and all sorts of other things within your SQL Server instances, the databases stored there, and all the stuff inside those databases. It also supplies you with an interface to the basic scripting language of SQL Server, through which you can do almost anything to the server. The scripting language is called Transact Structured Query…
I've written several times about the Cost Threshold for Parallelism and it's relationship to your execution plans, how to determine your execution plan cost, and even how to decide what value to set your Cost Threshold to. What I haven't explicitly addressed in extremely clear terms is why you should adjust your Cost Threshold for Parallelism. There are two reasons to modify this value. Cost Threshold for Parallelism Default Value The primary reason to change the Cost Threshold for Parallelism is because the default value is not a good choice for the vast majority of systems. The default value is 5. This means that when a query has an estimated cost greater than 5, it may get a parallel execution plan. Microsoft set the default value for the Cost Threshold…
[caption id="attachment_2548" align="alignleft" width="300"] Tired from all those blog posts[/caption] For my final blog post in the #gettingstarted, #iwanttohelp series, I decided to curate a set of presentations from the PASS Virtual Chapters. This content is available online. It's free. Most importantly for the person just getting started as a SQL Server data pro, it's good. I'm going to marry each of the presentations with my eleven blog posts in this series. The Importance of a Full Backup in SQL Server For this one I'm going to recommend Tim Radney's session Understanding SQL Server Backup and Restore. I know Tim personally and guarantee this is a good session. Why Is The Server Slow Jes Borland is a very close personal friend and an absolutely outstanding presenter (and person). She has…
SQL Server is a deep and complex product. There's always more to learn. For example, I had never heard of Correlated Datetime Columns. They were evidently introduced as a database option in SQL Server 2005 to help support data warehousing style queries (frequently using dates and times as join criteria or filter criteria). You can read up on the concept here from this older article from 2008 on MSDN. However, doing a search online I didn't find much else explaining how this stuff worked (one article here, that didn't break this down in a way I could easily understand). Time for me to get my learn on. The concept is simple, turning this on for your database means that dates which have a relationship, the example from MSDN uses OrderDate and…
Blog post #5 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel. Read about it here. Saying that you should use the correct data type seems like something that should be very straight forward. Unfortunately it's very easy for things to get confusing. Let's take a simple example from AdventureWorks. If I run this query: SELECT a.ModifiedDate FROM Person.Address AS a WHERE a.AddressID = 42; The output looks like this: 2009-01-20 00:00:00.000 Normal right? You see the year, the month and the day followed by the time in hours, minutes, and seconds as a decimal. Ah, but there is an issue. This query is supposed to be for the reporting system, and the business only cares about the date that the values in the Person.Address table have been modified, so they don't want…
Blog post #4 in support of Tim Ford’s (b|t) #iwanttohelp, #entrylevel In SQL Server, in the T-SQL you use to query it, a view looks just like a table (I'm using the AdventureWorks2014 database for all these examples): SELECT * FROM Production.vProductAndDescription AS vpad; SELECT vpad.Name, vpad.Description, vpmi.Instructions FROM Production.vProductAndDescription AS vpad JOIN Production.Product AS p ON p.ProductID = vpad.ProductID JOIN Production.vProductModelInstructions AS vpmi ON vpmi.ProductModelID = p.ProductModelID WHERE vpad.ProductID = 891 AND vpad.CultureID = 'fr'; The above query actually combines two views and a table. This is what is commonly referred to as a "code smell". A code smell is a coding practice that works, but that can lead to problems. In this case, we're talking about performance problems. The performance problems when using views to join to…
Ouch. Let's start with the level set. I'm not an ETL expert. In fact, I haven't done any professional ETL work for several years. My skills are, at best, rusty. With this in mind, I knew I'd have a hard time extracting data from a local database in order to move it up to Azure SQL Data Warehouse. I expected to be fumbling and slow and to constantly feel more than a little stupid as I made one mistake after another. All of this came to pass. Yet... OMG! THAT WAS DIFFICULT! Here's how I started. I defined a bcp command for the tables I was interested in. I ensured it was working correctly, then wrote a little PowerShell script so I could supply a list of tables and get…
One of the many small things you don't have to worry about any more when working with Azure SQL Database are those pesky backups... Ha-ha-ha! Yeah, I can't keep a straight face when I type it. Microsoft takes on the role of DBA and creates backups and log backups for you when you're working in the Platform as a Service offering of Azure SQL Database. If that doesn't scare you, I'm not sure what will. However, there's good news. There are still ways for you to create your own backup (through the bacpac, I outlined it here, years ago). More good news is, Microsoft's backups actually work. Testing it out is easy. Let's walk through it once. I'm going to assume you have an Azure account on which you already…