Database Fundamentals #7: Create a Table Using T-SQL

Database Fundamentals, TSQL
The syntax for creating a table logically follows many of the same steps that you did when using the GUI, but it will all be done with the statements. This script will exactly replicate everything that you did with the GUI: CREATE TABLE dbo.Person ( PersonID int IDENTITY(1,1) NOT NULL, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, DateOfBirth date NULL ) ON [PRIMARY]; Breaking the script into separate lines, it’s easy to see how the TSQL commands perform the actions defined in the GUI (it also makes it easier to read). The CREATE TABLE statement in this context is self-explanatory.  After that you’re defining the schema and the table name. Within the parenthesis you define each of the columns. First is the name of the column followed by the…
Read More

There Is a Magic Button, a Rant

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
OK guys. I think it's way past time. A bunch of us have been keeping a secret from the rest of you. We know something that you don't. I don't think we should hide this secret from the world any more. Illuminati? Incompetents. Free Masons? I am one, so I already know all those secrets. Bilderbergers, Cthulhu Cultists, MKUltra, New World Order, Rotarians? All of these are nothing compared to the vast conspiracy that I'm about to reveal. We need to just unveil the magic "Run Really Fast" button. We've been keeping that sucker a secret forever. It's been tough. Every so often some unauthorized person almost finds it or a "query tuning expert" (as if that was a real thing) tries to reveal it. But we've kept it secret…
Read More

Database Fundamentals #4: Create a Database

Database Fundamentals, TSQL
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…
Read More

The Three T’s of Backups

TSQL
I just read several more horror stories that include, among other things, failed backups. I've said it before (at volume, extreme volume), and evidently I have to say it again. Simply creating a backup file is not enough to ensure the protection of your information. In order to attempt to reinforce the importance of this idea, I'm going to introduce a new concept. Maybe it'll help. I'm calling it "The Three T's of Backups." Take 'Em First, and most important, you have to Take backups. That's your first "T". No, disk redundancy through RAID or a SAN or some other setup is not adequate to protect your information. You must take backups. They have to be created and they have to be run regularly. You absolutely should automate this. Test…
Read More

Does Query Store Pre-Allocate Space

SQL Server 2016, TSQL
I love the questions I get while I'm presenting because they force me to think and learn. The question in the title is one I received recently. The answer, now that I'm not standing in front of people, is easy. Of course the space is not pre-allocated. Query Store tables are just system tables. They have a limit on how big they can grow (100mb by default), but that space isn't going to be pre-allocated in any way. The space will just get used as and when it's needed, just like any other system table. However, don't take my word for it, let's prove that. The Test Testing whether or not enabling Query Store is straight forward. Here's a query that should give us information rather quickly: CREATE DATABASE QSTest; GO USE…
Read More

Query Optimizer and Data Definition Language Queries

SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
Data Definition Language queries don't go through the optimizer, right? While normally, my short answer to this question in the past would have been, yes. However, with testing comes knowledge and I want to be sure about the statement. I'm working with a team of people to completely rewrite the SQL Server Execution Plans book. We'll probably be published in April or May. It's going to be amazing. The structure will be completely different and the book will represent five years of additional knowledge in how execution plans work and how to read and interpret them since the last book was written. However, enough on that. Let's answer the question about Data Definition Language. First of all, we need to quickly define our terms. Data Definition Language (DDL) represents the syntax for queries that build…
Read More

OPTIMIZE FOR Hints When Parameter Sniffing is Turned Off

Azure, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
While presenting recently and talking about dealing with bad Parameter Sniffing, I got the question; what happens to OPTIMIZE FOR hints when parameter sniffing is disabled? This is my favorite kind of question because the answer is simple: I don't know. Parameter Sniffing For those who don't know, parameter sniffing is when SQL Server uses the precise values passed into a query as a parameter (this means stored procedures or prepared statements) to generate an execution plan from the statistics using the value from the parameter. Most of the time, parameter sniffing is either helping you, or is not hurting you. Sometimes, parameter sniffing turns bad and hurts you quite severely. Usually, but not always, this is because you either have severely skewed data (some data is very different than the rest, lots…
Read More

Database Clone

SQL Server 2014, SQL Server 2016, TSQL
There are a bunch of ways you could create a database clone. Backup and restore is one method. Export/Import is another. There are even third party tools that will help with that. However, each of these has a problem. It's moving all the data, not just once, but twice. You move the data when you export it and you move it again when import it (same thing with backup and restore). That makes these methods slow for larger databases. How can you create a database clone without moving the data multiple times? Don't Move the Data At All New with SQL Server 2016, expanded in SP1, and added to SQL Server 2014 SP2 is a new command, DBCC CLONEDATABASE. This is like a dream come true. The use is extremely…
Read More

PowerShell to Test a Query

DevOps, PowerShell, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016, TSQL
So you want to do some tuning, but you're not sure how to test a query on it's performance. Not a problem. Here's a very rough script that I use to do some recent testing. This script to test a query is post #11 of the #enterylevel #iwanttohelp effort started by Tim Ford (b|t). Read about it here. The Script The goal here is to load a bunch of parameter values from one table and then use those values to run a query to test it. To do this I connect up to my SQL Server instance, naturally. Then I retrieve the values I'm interested in. I set up the query I want to test. Finally a loop through the data set, calling the query once for each value. [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null #…
Read More

Query Data Store Data

SQL Server 2016, TSQL
The data in the Query Data Store is what makes all the magic happen. From collecting the aggregate performance metrics of a query to the various plans in use by that query to being able to force a plan, it's all controlled by the data within the Query Data Store system tables. The Question When I was presenting on this topic at the PASS Summit a few weeks ago, one great question came up (great question = answer is "I don't know"), well, I defaulted to an "I don't know" answer, but my guess was, "No." The question was: can you take a plan from one server, let's say a test server, export it in some way, and then import it to production? In this manner, you ensure that a…
Read More