I've said it before, but I feel I should repeat myself. Using the SSMS GUI for data entry and data manipulation is not the preferred mechanism. T-SQL is the right way to manipulate the data in your database. For purposes of completion though, I will show the GUI methods in this blog series. Information doesn’t go into the database and stay there, unchanged, forever. Data is modified. This occurs because information changes, such as when a person marries and changes their name, or information was incorrectly entered, in which case you need to fix it, or just about anything else. You have to have a mechanism for modifying existing information. Modifying Data You start modifying data in the tables the same way you did the insert, by taking advantage of…
T-SQL provides lots of functions that help to make data entry through T-SQL much more powerful. Over time you won’t be typing everything into T-SQL directly as we’ll do here. You’ll be able to use stored procedures and parameters to automate the use of scripts. These will also be generated or used by applications. To start using T-SQL, you need to open a query window. You can do this by right clicking on a database and selecting the “New Query†command from the context menu. This will open a new query window in the main window on your screen. This is basically just a big, open text box into which you can type commands. The INSERT Statement To add rows using T-SQL, the principal statement is the INSERT statement. The…
If you've been following along with the previous 10 Database Fundamentals blog posts, you have a SQL Server installed and a database with a table in it. You may have more if you've been practicing. Now would be the time to start adding data to the database, but first, I want to talk about the importance of T-SQL Why T-SQL? The way SQL Server accepts information is very different than most programs you’re used to using. Most programs focus on the graphical user interface as a mechanism for enabling data entry. While there is a GUI within SQL Server that you can use for data entry, and I will do a blog post on it, the primary means of manipulating data within SQL is the Transact Structured Query Language, or…
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…
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…
One of my favorite things about being a technologist is constantly learning new things, but, this can lead us to forget about the fundamentals. More importantly, in our pursuit of the latest and greatest things, it's very easy for those of us who teach to forget to reach back and pull others forward. With this in mind, I'm going launch a new blog series called Database Fundamentals. Database Fundamentals The goal here is simple. I'm going to talk about the basics. Creating a database. Creating tables. Inserts, selects, primary keys, and on and on. I have a bunch of material accumulated around these topics. I may as well share it as much as I can. I will continue posting information about all the fun cutting edge stuff I get to…
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…
One question constantly comes up; What should the Cost Threshold for Parallelism be? The default value of 5 is pretty universally denigrated (well, not by Microsoft, but by most everyone else). However, what value should you set yours to? What Do Your Plans Cost? I have a question right back at you. What do your plans currently cost? Let's say, for argument's sake, that all your plans have an estimated cost (and all plan costs are estimates, let's please keep that in mind, even on Actual plans) value of 3 or less. Do you need to adjust the cost threshold in this case? Probably not. But the key is, how do you look at the costs for your plans? Unfortunately, there isn't a property in a DMV that shows this value. Instead,…