Microsoft Transact SQL (T-SQL) Demystified

The example SQL script includes Line Numbers for ease of reference and reflects the default SQL Server Management Studio (SSMS) color coding scheme as follows:

Blue words are Keywords and reserved for internal SQL operations
Green words are comments and are ignored by the Database Engine
Red indicate a Text entry and are delimited with single quote characters (‘a word’)

The following example script creates a new database with an empty table:

Line 1. The master database contains metadata or identification information for SQL Objects

Line 2.  The GO operator sends the signal to SQL to execute the USE master statement

Line 4.  This line is a comment explaining what the following code does, it is ignored by the SQL Database Engine during code execution. The  – –  characters indicate a comment

SQL

Line 5. The CREATE statement tells SQL to make a new Database Object

Line 6. The GO operator tells SQL to execute the previous CREATE statement

Line 9. Tells the Database Engine to open and access the new FruitBasket database

Line 10. Execute the previous USE statement

Line 11. This line is a – – comment documenting what the following code does

Line 12. This is a SQL Data Definition Language (DDL) command to create a new object, in this case a new table (Fruits) in the FruitBasket Database

When creating Fields in a database table, you must decide in advance what type of data will be captured in that field. T-SQL data types include Integer and Decimal numbers, Text and Logical (Boolean) entries among others.

Line 13. Create a column in the Fruits table named Product ID as an integer datatype, the PRIMARY KEY syntax means that there can be no duplicate entries in the column and the NOT NULL operator means that each row must contain a unique value (think a Social Security or Employee ID Number)

Line 14. Create another column in the Fruits table called Product Name and constrain its values to Text entries up to 25 characters in length; this column must also be provided with a value according to the NOT NULL designation

Line 15. This adds a currency or money typed column to store the Price of each product, the NULL syntax means that you do not have to input a value for this field, it can be left blank until the time comes to populate it with a value

Line 16. Finally, the last field defined is a Product Description field of the Text type and does not require a value like the NULL Price field

Line 17. The GO command completes the process of table creation by instructing SQL Server to execute the CREATE TABLE statement

At this point the table with its columns is created, but it has no data and now requires SQL INSERT commands to populate the table with values

Note: Line numbers are not contiguous in screen shots

Lines 27. and 28. The INSERT statement begins with a list of columns, separated by a colon and enclosed in parentheses. The VALUES portion of the INSERT statement includes the data to be captured by the Database Engine in the requisite column, this list is also comma delimited and enclosed in parentheses

Line 29. A matching INSERT statement populates the second row of the table

Line 30. By now the familiar GO statement directs the Database Engine to execute the preceding INSERT statements

SQL

The SELECT statement returns a listing of your table contents. In order to specify a conditional return of data, use a WHERE clause with the SELECT statement.

Line 32.  SELECT* uses a Wildcard Operator that translates to return ALL records

Line 35. The WHERE clause returns only those records that have a price greater than $2.00

Line 38. A specific list of columns is returned (Product ID is not included in the result)

SQL


A Smarter SQL Script

At this point the script will run one time and fail if a second attempt is made to execute it because the database, table and content to be created already exist.

An important addition to the start of the script (below) checks the Master database for an existence of the FruitBasket database and initiates a DROP (Delete) operation thereby enabling the new instance to be created. This makes the script reusable if required.

Note: Line numbers are not contiguous in screen shots

3

Leave a Reply