Archives

T-SQL Basics

“First learn stand, then learn fly. Nature rule, Daniel-san, not mine.” – Karate Kid

What is SQL?

SQL (Structured Query Language) is a language for manipulating database data and structure. It can be pronounced by stating it’s letters “S-Q-L” as well as the word “sequel”, as in the Matrix had two crappy sequels.

SQL is mostly standardized across Relational Database Management Systems RDMBS, but each, like SQL Server, may have it’s own unique syntax. T-SQL is Microsoft’s version of the language and it’s the version I focus on.

Two Main SQL Statement Types

There are other types of SQL Statements but we’ll restrict our discussions to these two.

  • Data Definition Language (DDL) – These define database structure and schema. Eg. Create Database.
  • Data Manipulation Language (DML) – These statements manipulate data. Eg. Select * from

Most of what I’ll cover with you are DML since most Made2Manage Admins will not be creating a lot of databases, manipulating indexes, etc.

Basic Syntax Rules of Using SQL

“Do you understand the words that are coming out of my mouth? – Rush Hour

These rules are true for the most part and in most circumstances. I’m generalizing to make things as simple to learn as possible. The syntax requirements of T-SQL are incredibly flexible. In this article, I’ll explain what you can do, and in a future article, I’ll explain what I think you should do.

I’m explaining these rules in the context of a very basic select statement. This following statement requests all of the values in the column (or field) fsono from the somast table.

SELECT fsono FROM somast
  1. T-SQL statements are case insensitive. For example the following scripts will function the same way:
  2. SELECT fsono FROM somast

    SeleCT fSoNO froM SOMast

  3. The “*” is a wildcard and will select all fields from a table. “Never” use this as it wastes resources and creates maintenance headaches. If you delve into M2M’s own code however, you will see many instances where they do that very thing.
  4. When selecting fields, separate them with commas. The order of fields selected doesn’t matter to the computer, but as I said earlier I’ll explain my preferences in a future post.
  5. White Space, and placement of some punctuation, doesn’t matter. The following statements are equivalent to the computer.
  6. SELECT  fcompany, fcustno, fcustpono, forderdate, fsono, fsorev FROM somast
     
    -- Extra Spaces
    SELECT  fcompany       , fcustno,       fcustpono, forderdate, fsono, fsorev FROM somast
     
    -- Extra Carriage Returns
    SELECT fcompany,
           fcustno,
           fcustpono,
           forderdate,
           fsono,
           fsorev
      FROM somast
     
    --Leading Commas
    SELECT
      fcompany
     ,fcustno
     ,fcustpono
     ,forderdate
     ,fsono
     ,fsorev
    FROM
      somast

Consistency

If there’s one thing I’ve learned over the years its that consistency is key. Since most M2M Admins are the lone DBA and reporting person, you must create your own standard ways of writing SQL code and stick to them. Consistency has several benefits.

  1. Your code will be easily read by you and others.
  2. You will be able to cut and paste code snippets between SQL Scripts (more on this later).
  3. Your efficiency goes up dramatically when you work this way.

So Daniel-san, as I said before this is your first lesson. Tomorrow, I’ll show you around our dojo that will use for future lessons in this T-SQL Basics Series.

6 comments to T-SQL Basics – First Things First

Leave a Reply to Fred Crawford

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>