While deleting a database is a fairly rare occurrence, I’m teaching you now, since it’s a pretty easy task to learn. Before teaching you this, you do know rule number 2, right? I bring this up because, you never know when you’ll get a request to drop a database, only to get a call 5…
Tag: SQL
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:…
SQL 201 – Views
Previously I showed you how to create tables. The normal goal for designing a table is to store data in such a way that each table contains one group of facts that are highly related to each other. For example, if you had built a contact database, you’d have one table for people, one for…
SQL 201 — User Defined Functions
Back in SQL 101, I introduced you to some functions, GETDATE(), ISNULL(string1, string2), etc. All of these functions come defined in Microsoft SQL before you even install it on your machine. But that’s just the beginning. You can create your own functions to do tasks too! There are two types of user defined functions, table…
A Series for Database Administration
I’m at it again! I’m working on some notes about how to administer a Microsoft Database Server. I’m planning on following the fundamentals you would need to cover the 70-431 (the Microsoft Database Administrator’s exam). I’m doing this to share, but I’m also doing this to help me grow a bit stronger in my Database…
The DBA’s Rules — Rules 3 and 4
In previous posts I shared with you my rules for who does and doesn’t have access to sa, and keeping your data safe by having tested backup and restore procedures. Next I’m covering two related rules. These are to protect me as much as they are to protect you. If you’ve worked in a team…
TOP PERCENT
We have a multi-threaded process that grabs it’s fair share records and then processes them. It basically looks at the total number of records to process, and takes the top 10% of the records and marks them so that no other process will grab those records. If you haven’t done a lot with TOP, then…