The most fundamental task you’ll have to learn to do as a Database Administrator is to create a database. Please remember it’s probably a good idea to make sure you’re connected to the master database on your server when creating databases. Don’t change your connection to your database until you’ve closed out your CREATE DATABASE…
Alias
Within a few days of using SQL, you’re going to start wondering if there is a way to speed up your code writing. The good news is you can do that with Aliases. You can give a table or a column another name by using an alias. This can be a good thing to do…
ALTER TABLE
After you’ve spent time designing the perfect table, someone is going to come along and ask you a question that will lead you to changing your table. It can be something as simple as, hey, we can only store 25 characters for a city name, we need to have 50. Or it can be a…
SQL 201-SELECT INTO
I’ve already shown you how to create a table, and how to INSERT data into that table. But did you know you can do both in one statement? The SELECT INTO statement selects data from one table and inserts it into a different table. I use the SELECT INTO statement is to create backup copies…
SQL 201 – Locking Hints
If you’ve spent any time looking at another programmers code, I’m sure you’ve see something like this: SELECT columnName FROM tableName (NOLOCK) I’m sure you asked yourself what the (NOLOCK) was all about, right? Well, it’s time I explained. The keywords in the parenthesis are referred to as table or locking hints. A lock is…
SQL 102 – Foreign Keys
In the simplest terms a Foreign Key in one table refers to the Primary Key of another table. Let’s go back to our demo tables products and productSales. –Products: productName price quantity color ———– —– ——– —– pencil 0.25 100 yellow pen 0.99 73 blue paper 1.00 500 white Mountain Dew 1.25 8 green –prodctSales:…
Merry Christmas!
I just wanted to thank you all for a wonderful year. I’ve really enjoyed pushing myself to produce a post a week this year. It was challenging, but fun. I’d like to ask for a Christmas present from you all this year. It’s nothing that will cost you money. It will only cost you a…
Optimizing Queries
This is the first article in a never ending series of articles on how to optimize your queries. Honestly, this is a topic I’m still learning volumes about. I learned pretty early on the number one rule to getting fast results from a SELECT query. Limit the results to the minimum you need If you…
SQL 201 – Statistics
Microsoft SQL Servers collect statistical information on indexes and column data stored in each database. These statistics are used by the query optimizer to choose the most efficient way to execute your queries. Good statistics hep the optimizer to asses the cost of different query plans, then choose the “best” method to execute your query….
SELECT CASE
A useful skill to learn in SQL is how to translate a value into a description. You’ll use it all the time. Whether you’re turning M|F into Male or Female, or turning T|F into True or False, you’ll have to be able to make the translation as quickly as possible. The easiest way to do…