It’s only a matter of time before you need to clean up after yourself in Microsoft SQL. You’ll have to get rid of an index, or a table, or maybe even a whole database. Indexes, tables, and databases can easily be removed using the DROP statement. The DROP INDEX Statement If you get into trying…
Tag: SQL201
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…
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…
SQL 201 – Indexes
Indexes in Microsoft SQL are just like indexes in a book. They help you jump to the information you want more quickly. In Microsoft SQL you can index one or more columns in a table or view. The indexes defined can help your select statements run much more quickly… but they do have a cost,…
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…