SQL Server Archives - Page 6 of 19

25Feb 2019 by Grant Fritchey 13 Comments

How Do You Export A Database in Azure Data Studio

SQL Server, SQL Server 2016, SQL Server 2017

I've been writing a bunch about Azure Data Studio. I've also been recording videos on the topic. A comment I received recently asked how to export a database from Azure Data Studio. It made me want to explore the topic of exporting a database as it relates to Azure Data Studio. Export? When we say export, what exactly do we mean. It could be as simple as exporting data to a flat file for consumption in Excel or something. It could be creating a backup. Maybe we mean creating a bacpac file. We could also be looking at creating individual scripts for objects within the database. Finally, what about a full export of the database object definitions? Any or all of these could be what the question was about. So,…

18Feb 2019 by Grant Fritchey 6 Comments

Installing Extensions to Azure Data Studio

Azure, SQL Server 2016, SQL Server 2017, T-SQL

If you're even thinking about experimenting with, let alone actively using, Azure Data Studio, you need to plan on installing a few extensions. Buck Woody has a great list that you should look through in this blog post. If you're just getting started with Azure Data Studio, I have an introduction here. Depending on the extension, this could be a simple as a mouse click. However, not all the extensions are that easy. Let's explore this just a little so when you do start using Azure Data Studio, things are easy. Extension From a Mouse Click For this bit of the blog post, we'll stick to nothing but mouse clicks, but, if you really want to bring the power within Azure Data Studio, you really need to learn keyboard shortcuts…

11Feb 2019 by Grant Fritchey 2 Comments

Query Store and a READ_ONLY Database

T-SQL, Tools

What happens in Query Store when the database itself is READ_ONLY? Yeah, I don't know. Let's find out. READ_ONLY The only way to find out how this works is to test it. So, let's write some code: CREATE DATABASE testquerystore; GO ALTER DATABASE testquerystore SET READ_ONLY; GO ALTER DATABASE testquerystore SET QUERY_STORE = ON; Executing that resulted in a small glitch in the Matrix: 8:00:54 AMStarted executing query at Line 1Commands completed successfully.8:00:54 AMStarted executing query at Line 2Commands completed successfully.8:00:54 AMStarted executing query at Line 5Msg 5004, Level 16, State 6, Line 5To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.Msg 5069, Level 16, State 1, Line 5ALTER DATABASE statement failed.Total execution time: 00:00:01.448 Well that's not going to…

04Feb 2019 by Grant Fritchey 3 Comments

Combine Extended Events and TagWith to Monitor Entity Framework

SQL Server 2016, SQL Server 2017, T-SQL

I'm going to start with a sentence that makes a lot of people crazy; As a DBA and database developer, I love Entity Framework. That's right. Entity Framework is the bomb. It's amazing. It does wonderful stuff. Entity Framework makes the developers lives and jobs easier. It makes my life and job easier. Yes, Entity Framework will improve your job quality and reduce stress in your life. With one caveat, it gets used correctly. That's the hard part right? There is tons of technology that makes things better, if used correctly. There are all sorts of programs that make your life easier, if used correctly. Yet, all of these, used incorrectly, can make your life a hell. One nit that I've always had with Entity Framework is that it's very…

28Jan 2019 by Grant Fritchey 1 Comment

Input Into Azure Data Studio

Azure

I see more and more people starting to use Azure Data Studio. As it keeps growing and expanding, it's going to become more and more a go-to tool for everyone that has to work with the Microsoft Data Platform. Wouldn't it be good to be able to provide direct feedback to the development team? Why not do that? GitHub Microsoft does a heck of a lot work with GitHub. It just so happens that the Azure Data Studio team is using it as well. In fact, they have a public Issues resource within GitHub. You can go there yourself, search out the existing issues, submit a problem, or, better still, submit a suggestion. The people working on this are clearly busting their behinds to add functionality and improve functionality. You…

23Jan 2019 by Grant Fritchey 2 Comments

All Day, Training Day at SQLBits

Azure, SQL Server 2016, SQL Server 2017, T-SQL

It's a somewhat late addition, but I have an all-day Training Day at SQLBits. It takes place on Thursday, February 28th. You can read all about it on the SQLBits web site. I want to take a moment here to expand on the information that we're going to cover. I think the abstract does a good job of conveying what we'll be doing all day, but I figured a little more detail won't hurt. Query Tuning is Hard This is the very first thing I talk about. Query tuning is hard. I've got a nearly 1,000 page book on the topic, which should give you an idea of just how much material there is to cover. With the training day I've decided to focus on the tools that Microsoft gives…

21Jan 2019 by Grant Fritchey 1 Comment

Get Your Learn On

PASS, SQL Server

There is one truth that I can say about technology with an absolute certainty: It's going to change. Get your learn on! Technology is going to be shifting under your feet, constantly. Even if all you ever do is work with SQL Server, on premises, on hardware, without VMs, containers, clouds or any of that foofaraw, things are going to change. Dealing With Change What's the best way to deal with change? Get your learn on! Learning all the new stuff is absolutely necessary. It's unavoidable. Even if you're not running SQL Server 2019 in production today (and very few of you should be as I write this because it's still in preview) because you're still on SQL Server 2005 (and, by the way, support ended in April of 2016,…

03Dec 2018 by Grant Fritchey 9 Comments

Database Fundamentals #20: Using the JOIN Operator, Inner Join

Database Fundamentals

It is entirely possible to try to JOIN two tables on almost any field, as long as the two data types can, in some way, be made to reconcile to each other, you can try to join the tables. But, most database designs assume a much more directly relationship and provide a column or columns in one table that match the identifying column or columns in the other table. INNER JOIN The INNER JOIN will return the parts of both data sets that match. Frequently, what you'll see when joining two tables is the same column name in each table. With that, you have to be sure to identify the owner of each column. I've introduced what is called an alias to make it so I don't have to type…

26Nov 2018 by Grant Fritchey 5 Comments

Forcing a Plan That Has a Plan Guide

SQL Server 2016, SQL Server 2017, T-SQL

The question that came up during a recent class I was teaching was: What if you have a plan guide to get the plan you want, but then decide, instead of using the plan guide, you'll just force the plan? Ummmmâ€¦. No idea. Let's test it. First, Create a Plan Guide I have a couple of queries I use to teach about how statistics affects plan choice, so we'll use that here. I'm going to also define and create a plan guide that makes this plan use a small row count for all queries against it: CREATE OR ALTER PROC dbo.AddressByCity @City NVARCHAR(30) AS SELECT a.AddressID, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode FROM Person.Address AS a JOIN Person.StateProvince AS sp ON a.StateProvinceID = sp.StateProvinceID WHERE a.City = @City; GO…

19Nov 2018 by Grant Fritchey 1 Comment

Query Store and Plan Cache Plans Compared

SQL Server 2016, SQL Server 2017

Query Store plans and the plans in cache are identical, right? There won't be differences because the plan that is in cache is the plan that was used to execute the query. Similarly, the plan that is in the Query Store is the plan that was used to execute the query as well. Therefore, they will be the same. However, some small differences actually can show up. Differences Between Plans In order to compare the two plans, first, we need a query. Here's a stored procedure that I'm going to use to generate a plan that will be in cache and in the query store: CREATE PROC dbo.ProductTransactionHistoryByReference ( @ReferenceOrderID int ) AS BEGIN SELECT p.Name, p.ProductNumber, th.ReferenceOrderID FROM Production.Product AS p JOIN Production.TransactionHistory AS th ON th.ProductID = p.ProductID…

Tag: SQL Server