Learning to handle errors better can be one of the skills that will really set you apart from other SQL Developers. You can do constant unit testing after each change, but odds are you’ll never hit ever scenario your code will face. There is a better way. If you have programmed in any language like…
Tag: DEV
Default Values
When you start creating tables and setting them so they can’t have NULL values, you’ll eventually need a way to define a default value for the column. A default value is one that a column would apply to its record if a value is not provided. To specify the default value in a SQL statement,…
Identity Columns
An IDENTITY column is a column that automatically gets it’s value set by the database engine when a new record is added. This is one of the oldest ways Microsoft SQL has of making sure a record is unique. Even if a user were to insert the same record twice, the IDENTITY will always be…
Computed Columns
What is a Computed Column A computed column is a column that users will not enter data for, but the SQL Server will compute and store a value in. The expression for a computed column may include the names of other columns in the table. These columns can also be combined with literal values and…
Renaming a Database
A topic that you’ll need sooner or later is how to rename a database. You’ll get a request to build a database, and it’ll be documented fairly well. You’ll be coding along, then you get a ping through IM. -> Hey, you know that database you just created for me? <- Yeah. I’ve already built…
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 102 – Primary Keys
In SQL 101, I showed you how to create a table. I want to build on that today by introducing the concept of a primary key. A primary key is a column, or combination of columns that uniquely identify the row in the table. By doing this you can have some assurance that the row…