Spatial, Geography and Geometry, oh my! – Part 2: Spatial Data (continued)

Last week, we started the Spatial Analysis series with an overview of the two-dimensional aspects of geometry.  Today, we’ll look into coordinate systems, which include both two- and three-dimensional mapping.

Functions – A handy tool for formatting data (among other things)

Last week we started Spatial with Part 1 of a series, and we’ll get back to that on Thursday.  Today, we’ll talk about functions.  Functions are a tool that can be used by developers to perform automated data replacement, concatenation, calculations or other tasks in a much easier way:  by programming a function, then simply calling that function for the field that needs to be calculated.   There are two types, system functions and User-Defined Functions (UDF’s).  This post is going to deal with UDF’s.

Spatial, Geography, and Geometry. Oh my! – Geospatial Data Part 1

Geography and Geometry.  Gives me flashbacks to high school.  Though those would lead to interesting stories, today we’re here to discuss Spatial within SQL Server.   There are a lot of different uses for spatial, but it all boils down to the ability to represent SQL Server data on a map, either 3D or 2D.  Shall we begin?

Recursive Queries – Leveraging CTE’s to work for you

Playing around with Microsoft SQL Server 2008 Express edition, I’ve sorted through a bunch of tidbits. One that I thought was interesting, is how to perform a recursive or hierarchical query. This describes how you can perform the magic.

The official name of the WITH clause in Oracle’s lexicon (otherwise known as Oraclese) is a subquery factoring clause. Microsoft has a different name for the WITH clause. They call it a Common Table Expression or CTE. You can find more on that in this earlier blog post.

Triggers – Save yourself the extra coding and time with automation

Triggers are an often overlooked feature of SQL Server that can automate a LOT of tasks, such as auto-update an activity flag, or auto-insert new rows based on a new row in a different table. Instead of being required to code 100 lines of code to add a new row to a table and update all dependent tables, a trigger can be set to execute on an Update, Insert, or Delete (DML Trigger), Create, Alter, Drop, Grant, Deny, Revoke (DDL Trigger), or even upon login (Login Trigger).  Today, we’ll cover the three different types and how they can be used to automate your database tasks.

Common Table Expressions (CTE) – What they are and how to use them

Common Table Expressions are a nice tool in SQL Server that allow you to create Temporary Tables without using TempDB.  They only exist while the query runs, whereas temp tables live for the entire session.  They are a great tool when it comes to optimizing queries, as it also reduces the load on tables at runtime.  Today, we’ll discuss what they are and how they’re used.

Basic Querying in SQL Server

Today we’re going to talk about querying a database in SQL Server.  Queries can be really simple or nearly infinitely complex.  But that complexity can be deceiving, as it all breaks down to multiple combinations of the simple aspects of querying.  Today, we’ll discuss selecting items from a database and limiting those results to get results the way we want them.  You should have SQL Server 2008R2 and the AdventureWorks2008R2 database installed on your system.  If you don’t already, see the “Software”  link above.

Creating & Importing Databases – SQL Server 2008R2

SQL Server is a Relational Database Management System (RDBMS), so being able to create and import databases is an essential function of all Database Administrators. Today, we’ll take a look at the different methods of creating and importing databases in SQL Server 2008 R2. There are multiple ways to create and import databases, including wizards, menu options, and coding. We’ll cover each in this lesson.

Today’s Lesson: Installation of SQL Server

Today, we’ll discuss the preparations for the installation of SQL Server 2008 R2.  After opening the installation program, unlike most programs, you’ll be faced with a plethora of options and choices.  It can be daunting to someone new to SQL, and here I’ll do a quick walk through to get you started.

Welcome to The SQL School!

Welcome to The SQL School, a new place to learn all things SQL.  New lessons will be posted on Tuesday’s and Thursday’s, so make sure to keep up with me on Facebook and/or Twitter to get links to the latest lessons.

All lessons will use Microsoft SQL Server 2008 R2 Express, the free edition of SQL.  Click to download.
If something isn’t available in the Express version of SQL Server, I’ll be sure to post more detailed step-by-steps and pictures/videos to make sure you can take from it what is intended.

We will use the AdventureWorks2008R2 database provided by Microsoft.  This contains tables, procedures, everything needed to test and develop on a sample database.  Click to download.