SQL DBA Survival Guide

T-SQL Code - Part 2

This section includes many activities that demonstrate the functionality of the topics.

Note: some of the demonstrations and activities have dependencies on prior activities being performed for the objects to exist.

AdventureWorks

The AdventureWorks database is a sample database provided by Microsoft. It uses many of the features supported by SQL Server for demonstration purposes.

It can be helpful to download then restore this database to a test server for testing and experimentation.

Download AdventureWorks sample database: AdventureWorks installation and configuration — Microsoft Docs

Install AdventureWorks Database

Many of the demonstrations in this book will rely on the AdventureWorks database.

There are two ways to install the AdventureWorks database. Depending on the type of file downloaded, it can be performed as a database restore or ran as a SQLCMD script.

Install using a script in SSMS

  1. Download the OLTP script.
  2. Copy the install files to a folder (i.e., C:\temp\AdventureWorks-oltp-install-script)
  3. Start SQL Server Management Studio and connect to your desired SQL Server instance.
  4. Open a new query window.
  5. Navigate to the Query menu.
  6. Select SQLCMD mode.
  7. Paste the instawdb.sql code into the query tab.
  8. Edit line 39 with the path to the install files
  9. Optionally edit line 46 with the name of the database to create

Note: This install script will drop an existing database of the same name being installed.

Note: If the script display errors related to Full-Text Search, it is ok for the purposes of this book.

Install using the database restore method

  1. Download the backup file for the SQL version being used
  2. Restore the database to the server

 

Aggregation

In order to demonstrate some aggregation concepts, we will create some database objects for later use. Run the script below to create the tables and data.

-- 0302-00-05 Demonstration Code ================
-- Create Demonstration objects
-- Create a Facebook table
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Facebook](
[Name] [varchar](50) NULL,
[Friends] [int] NULL
) ON [PRIMARY]
GO

-- Add data to the Facebook table
INSERT INTO Facebook ([name], [Friends])
VALUES ('John',130);

INSERT INTO Facebook ([name], [Friends])
VALUES ('Jane',75);

INSERT INTO Facebook ([name], [Friends])
VALUES ('Alex',290);

INSERT INTO Facebook ([name], [Friends])
VALUES ('Alice',170);

INSERT INTO Facebook ([name], [Friends])
VALUES ('Fred',210);
GO

-- Create a LinkedIn table
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[LinkedIn](
[Name] [varchar](50) NULL,
[Friends] [int] NULL
) ON [PRIMARY]
GO

-- Add data to the LinkedIn table
INSERT INTO LinkedIn ([name], [Friends])
VALUES ('John',200);

INSERT INTO LinkedIn ([name], [Friends])
VALUES ('Jane',300);

INSERT INTO LinkedIn ([name], [Friends])
VALUES ('Alex',150);

INSERT INTO LinkedIn ([name], [Friends])
VALUES ('Mary',240);

COUNT

The Count() function returns the number of rows in a result set. It is the most common type of Aggregation in SQL, it counts the number of rows in a column or table. COUNT(*) tells SQL to count the number of rows of the whole table. COUNT(some column) tells SQL to count the number of non-null rows in that column.

Syntax

SELECT COUNT(*)
FROM MyTable

SQL goes through all the rows of the table and creates a memory table with total count of rows.

With more complex data, SQL can answer more complex questions such as how many rows are there per state.

The COUNT aggregation can answer this, but SQL needs to be told what to group by in the query. SQL also needs the column it is grouping by in the SELECT statement so that the final table can show the data in its proper groups.

SELECT State, COUNT(*)
FROM facebook
GROUP BY State

First SQL will group together data by the column or columns listed in the group by statement in this case state. Then SQL will perform the same COUNT operation as before but check which group to assign the count to.

The data and question can be even more complicated, in this example there is a city column in the table as well. The question could be how many rows are there for each State and City.

The COUNT aggregation will get us our answer as long as it is grouped by state and city. Remember SQL needs what we are grouping by in the SELECT statement as well so that these groups will show in the final table.

SELECT State, City, COUNT(*)
FROM facebook
GROUP BY State, City

TOP

If you are exploring the data to figure out what it contains it is recommended to use the TOP statement to limit the number of records returned to improve the speed of the query.

-- Using TOP
-- Returns TOP 5 rows in a table
SELECT TOP (5) * FROM sys.databases

SUM and AVERAGE

After COUNT, for all other types of aggregation you will need to define the column you are aggregating.

Incorrect:

SELECT SUM(*)
FROM facebook

Correct:

SELECT SUM(friends)
FROM facebook

How would it SUM all the columns in the facebook table? The name column has text and cannot be summed. Also there are multiple columns so we cannot SUM the row as a whole like the way we could COUNT the row as a whole. SQL needs to know which column the aggregation should be on, in this case the only numeric column is friends.

The Average aggregation operates similarly to SUM. The data will be more complex for this example.

SELECT State, AVG(friends)
From facebook
GROUP BY State

To get an average SQL needs the sum of each group and then divide it by the count of rows in each group.

ISNULL

The IsNull() function returns the specified value if the expression is NULL, otherwise return the expression

Syntax

ISNULL(expression, value)

-- 0302-04-05 Demonstration Code ================
-- IsNull Since the expression is not NULL, the expression is returned
SELECT ISNULL('Hello', 'It is Not null, this message should not display')

-- 0302-04-10 Demonstration Code ================
-- IsNull Since the expression is NULL, The value is returned
SELECT ISNULL(NULL, 'It is null')

-- 0302-04-15 Demonstration Code ================
-- IsNull Since the expression is not NULL, the expression is returned
SELECT ISNULL('NULL', 'It is null')

-- 0302-04-20 Demonstration Code ================
-- IsNull Since the expression is NULL, the value is returned
DECLARE @MyValue varchar(10)
SET @MyValue = NULL
SELECT ISNULL(@MyValue, 'It is null')

IS NULL / IS NOT NULL

The Is Null and Is Not Null logic qualifiers are not functions but related to ISNULL() and are often confused with each other.

This is used to limit a record result set.

-- 0302-05-05 Demonstration Code ================
-- Is Null Return record result set where to column value is NULL
SELECT *
FROM MyTable
WHERE MyColumn2 IS NULL

-- 0302-05-10 Demonstration Code ================
-- Is Not Null Return record result set where to column value is not NULL
SELECT *
FROM MyTable
WHERE MyColumn2 IS NOT NULL

-- 0302-05-15 Demonstration Code ================
-- Is Not Null or blank Return record result set where to column value is NULL or empty
SELECT *
FROM MyTable
WHERE (MyColumn2 is null or MyColumn2 = '')

Square Brackets

SQL code can be written in many styles. For others to read the code, it is best to follow syntax guidelines.

Square Brackets for Columns

Square Brackets delimit identifiers. This is necessary if the column name is a reserved keyword or contains special characters such as a space or hyphen.

Some users also like to use square brackets even when they are not necessary.

-- Syntax highlighter displays the column name in blue because it is a keyword
-- The script will still run fine in this case
SELECT
name,
create_date
FROM sys.databases

Script above will work just fine because SQL Server knows that the keyword “name” is also a column in the table.

The better way to write the script is to use Square Brackets to make it more obvious that it is a column.

-- Using brackets around column names
SELECT
[name],
[create_date]
FROM sys.databases

Square Brackets for Tables and Schemas

Like columns it is possible to use reserved words, spaces and special characters in a table name, if it is enclosed on Square Brackets.


-- Using brackets for schema and table names
SELECT
[name],
[create_date]
FROM [sys].[databases]

Schema Name Shortcut

If the Schema is dbo, in most cases it is not required in the script. It can be omitted, or abbreviated as “..” (2 dots)

All these queries will work

-- Please select a database that has a table with a dbo schema
-- eg, dbOmnibus AlertItems table
-- 3 result sets, all 3 commands act the same
SELECT * FROM MyIdentityTable

SELECT * FROM dbo.MyIdentityTable

SELECT * FROM ...MyIdentityTable

If the schema name is NOT dbo, it must be specified.

Wildcards

There are 2 wildcards often seen in SQL scripts.

Asterisk Wildcard

“*” (asterisk)

Used in a SELECT statement to return all columns. It can be used in combination with naming columns to return.

-- Using the Asterisk wildcard to get all columns
-- Note: No way to know for sure the order of the returned columns across all servers
SELECT * FROM [sys].[databases]

Asterisk1

Return all the columns and also return the Name and compatibility_level columns as the first 2 columns.

-- Specifying column names and all columns
SELECT [name], [compatibility_level], * FROM sys.databases

Asterisk2

Percent Wildcard

“%” (percent)

Used in a WHERE clause with the LIKE condition to replace multiple characters.

-- 0304-02-05 Demonstration Code ================
-- Returns no rows - Asterisk is treated as a literal string in a WHERE clause
SELECT * FROM sys.databases
WHERE [name] = 'm*'

-- 0304-02-10 Demonstration Code ================
-- Returns no rows - Percent is treated as a literal string in a WHERE clause
SELECT * FROM sys.databases
WHERE [name] = 'm%'

-- 0304-02-15 Demonstration Code ================
-- Returns no rows - Asterisk is treated as a literal string in a WHERE clause
SELECT * FROM sys.databases
WHERE [name] LIKE 'm*'

-- 0304-02-20 Demonstration Code ================
-- Returns all databases that begin with M
SELECT * FROM sys.databases
WHERE [name] LIKE 'm%'

-- 0304-02-25 Demonstration Code ================
-- Returns all databases that contain d
SELECT * FROM sys.databases
WHERE [name] LIKE '%d%'

Alias Names

Column Alias

An Alias can be assigned to a column by adding the alias directly after the column name in a query.

Used to improve readability and handle ambiguous object names (covered in a later demonstration).

-- 0305-01-05 Demonstration Code ================
-- The column headers in the result set are assigned specific names
-- Notice the use of brackets to accommodate the space in the assigned names
SELECT
[name] ,
[create_date]
FROM sys.databases

ColumnAlias01

-- 0305-01-10 Demonstration Code ================
-- The column headers in the result set are assigned specific names
-- Notice the use of brackets to accommodate the space in the assigned names
SELECT
[name] AS [Database Name],
[create_date] AS [Created Date]
FROM sys.databases

ColumnAlias01

-- 0305-01-15 Demonstration Code ================
-- Fails because 'Database' is a reserved keyword and must be enclosed in Square Brackets
-- "Incorrect syntax near the keyword 'Database'."
SELECT
name AS [Database],
create_date AS Created
FROM sys.databases

ColumnAlias01

-- 0305-01-20 Demonstration Code ================
-- Omit using the AS command for column Aliases
-- The ‘AS’ command is optional but often used for columns to improve code readability.
SELECT
[name] [Database Name],
[create_date] [Created Date]
FROM [sys].[databases]

ColumnAlias01

Table Alias

Table aliases can be used to get past multi-part identifier errors.

Table Aliases may not appear to have value at this time but will come in handy when dealing with Joins in a later demonstrations.

It is important to note that they can and are used and it is essential to have the ability to read them in SQL code.

-- 0305-02-05 Demonstration Code ================
-- Table alias 'tbl' using AS
SELECT
[name] AS [Database Name],
[create_date] AS [Created Date]
FROM [sys].[databases] AS tbl

-- 0305-02-10 Demonstration Code ================
-- Table alias 'tbl' without using AS
SELECT
[name] AS [Database Name],
[create_date] AS [Created Date]
FROM [sys].[databases] tbl

-- 0305-02-15 Demonstration Code ================
-- Table multi-part identifier error
SELECT
[sys].[databases].[name] AS [Database Name],
[create_date] AS [Created Date]
FROM [sys].[databases] AS tbl

-- 0305-02-20 Demonstration Code ================
-- Table multi-part identifier works
SELECT
tbl.[name] AS [Database Name],
[create_date] AS [Created Date]
FROM [sys].[databases] AS tbl

Data Types

Types of Data

This is a brief list of the common data types. Consult the internet for a more comprehensive list.

It is important to understand the data types since some of them cannot be directly transferred to a different type.

NUMBERS

  • int — holds whole numbers (negative and positive integers)
  • decimal — fixed position decimal numbers
  • float — floating point numbers
  • bit — 0, 1 or NULL

STRINGS

  • char (n) — fixed width string. Regardless of how many characters are stored, the space used is the defined size. n defines the maximum number of characters stored.
  • varchar (n) — variable width string. The space used is only enough to store the information.
  • nvarchar (n) — like varchar, but with the ability to store Unicode (international characters) data.
  • sysname — equivalent of varchar(128).

DATES

  • date — stores only a date (January 1, 0001 to December 31, 9999)
  • datetime — stores a date and time value (January 1, 1753 to December 31, 9999)
  • datetime2 — stores a date and time value (January 1, 0001 to December 31, 9999)

CAST

Used to change the data type of an expression.

It is an ANSI-SQL Standard function

Syntax:

CAST (expression AS data_type)

Number to String

-- 0306-02-05 Demonstration Code ================
-- Implicitly converting numbers to strings works without CAST
DECLARE @Age varchar(2)
SET @Age=12
SELECT ('Sam is ' + @Age + ' years old') as Result
--PRINT 'Sam is ' + @Age + ' years old'

Cast01

String to Number

-- 0306-02-10 Demonstration Code ================
-- Attempting to implicitly add strings results as CONCATENATION
DECLARE @A varchar(2)
DECLARE @B varchar(2)
DECLARE @C varchar(2)
set @A=25
set @B=15
set @C=33
SELECT (@A + @B + @C) as Result

Cast02

-- 0306-02-15 Demonstration Code ================
-- Declaring each value as an Integer, results in ADDITION
DECLARE @A [int]
DECLARE @B [int]
DECLARE @C [int]
set @A=25
set @B=15
set @C=33
SELECT @A + @B + @C as Result

Cast03

-- 0306-02-20 Demonstration Code ================
-- Explicitly casting each value as an Integer, results in ADDITION
DECLARE @A varchar(2)
DECLARE @B varchar(2)
DECLARE @C varchar(2)
set @A=25
set @B=15
set @C=33
SELECT CAST(@A as int) + CAST(@B as int) + CAST (@C as int) as Result

Cast04

-- 0306-02-25 Demonstration Code ================
-- Print with Concatenation
-- Works with CAST
DECLARE @Item1 int = 5, @Item2 varchar(5) = 'xyz'
PRINT 'Item1 = ' + CAST(@Item1 AS varchar(10))

Cast05

-- 0306-02-30 Demonstration Code ================
-- Print with Concatenation
DECLARE @Item1 int = 5, @Item2 varchar(5) = 'xyz', @Msg varchar(50)
SET @Msg = 'Item2 = ' + @Item2
PRINT @Msg

Cast06

-- 0306-02-35 Demonstration Code ================
-- Print with Concatenation
DECLARE @Item1 int = 5, @Item2 varchar(5) = 'xyz'
PRINT 'Item1 = ' + CAST(@Item1 AS varchar(10)) + ' Item2 = ' + @Item2

Cast07

-- 0306-02-40 Demonstration Code ================
-- Print with Concatenation
DECLARE @Item1 int = 5, @Item2 varchar(5) = 'xyz', @Msg varchar(50)
SET @Msg = 'Item1 = ' + CAST(@Item1 AS varchar(10)) + ' Item2 = ' + @Item2
PRINT @Msg

Cast08

Dates to Strings

Date and Time is best performed using the CONVERT function. CAST can be used like the other types, but is typically found in code that was written for older SQL Server versions.

TRY_CAST

Works like cast, but can gracefully handle errors

-- 0306-03-05 Demonstration Code =========================
-- Explicit CAST a number string to a number
-- Works
SELECT CAST('123' AS INT);

TryCast01

-- 0306-03-10 Demonstration Code =========================
-- Explicit TRY_CAST a number string to a number
-- Works
SELECT TRY_CAST('123' AS INT);

TryCast02

-- 0306-03-15 Demonstration Code =========================
-- Explicit TRY_CAST a string to a number
-- Fails with conversion error
SELECT CAST('xyz' AS INT);

TryCast03

-- 0306-03-20 Demonstration Code =========================
-- Explicit TRY_CAST a string to a number
-- Results in NULL, but does not error (crash) the script
SELECT TRY_CAST('xyz' AS INT);

TryCast04

CONVERT

Convert works like CAST but is more versatile, especially for Date and Time types.

It is a SQL Server(T-SQL) function.

Syntax:

CONVERT(data_type(length), expression, style)

-- Some examples of Explicitly converting Dates to Strings
DECLARE @DateToday datetime
SET @DateToday=GETDATE()

--Raw value
SELECT @DateToday

-- Formatted value (MM/DD/YYYY)
SELECT CONVERT(varchar(20), @DateToday, 101)

-- Formatted value (mmm DD, YYYY)
SELECT CONVERT(varchar(20), @DateToday, 106)

-- Formatted value (mmm DD, YYYY)
SELECT CONVERT(varchar(20), @DateToday, 107)

Convert01

To convert date from YYYY-MM-DD 00:00:00.000 to YYYY-MM-DD, trim the time portion off of the data.

SELECT CONVERT(DATE, GETDATE())

Convert to a Decimal

CONVERT (DECIMAL (12, 2), round (sysfiles.size / 128.000, 2))

Additional Reading: Date and Time Conversions in SQL Server — MSSQLTips

 

Modifying Result Sets

It is more common than not to modify or filter a result set.

Demonstration data

These tables will be used to illustrate the joint types in the following demonstrations. Execute this script to create the tables and data.

With this data, we’ll suppose we work for a government social agency supporting people or families whose income is below a certain threshold. This agency uses several metrics to identify people or families needing help.

This dataset describes persons belonging to four families that live in two cities. For simplicity’s sake, we will assume that LastName identifies the family.

-- 0307-00-05 Demonstration Code ================
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Create Income table
CREATE TABLE [dbo].[Income](
[GivenName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[BirthDate] [varchar](50) NULL,
[City] [varchar](50) NULL,
[Income] [int] NULL
) ON [PRIMARY]
GO

-- Add data to [Income]
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('Mary', 'Roberts', '1964-01-11', 'Oklahoma', 78000)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('Peter', 'Roberts', '1962-09-25', 'Oklahoma', 86500)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('John', 'Roberts', '1999-06-03', 'Oklahoma', 0)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('Sue', 'Roberts', '1996-03-06', 'Oklahoma', 0)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('Melinda', 'Roberts', '1998-04-04', 'Oklahoma', 0)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('George', 'Hudson', '1953-02-23', 'Oklahoma', 48000)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('Nancy', 'Hudson', '1958-12-06', 'Oklahoma', 65000)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('Ann', 'Hudson', '1979-04-02', 'Oklahoma', 35000)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('Carl', 'Gibson', '1963-04-03', 'Phoenix', 102800)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('Li', 'Gibson', '1963-12-27', 'Phoenix', 96000)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('Kate', 'Bishop', '1994-07-10', 'Phoenix', 920000)
INSERT INTO [Income] ([GivenName], [LastName], [BirthDate], [City], [Income])
VALUES('Mark', 'Bishop', '2018-09-13', 'Phoenix', 0)

WHERE

The Where clause limits the data returned with qualifiers. WHERE filters rows before they are grouped.

-- Returns a list of databases that are not the default system databases
SELECT * from sys.databases
WHERE [database_id] > 4

WHERE 1=1

Seems like a silly thing to have but is useful debugging scripts and data sets. It allows for numerous AND modifiers to be turned on and off with ease.

Since 1 always equals 1, the condition is always true.

-- Using WHERE 1=1
-- Returns all rows in a table
SELECT * FROM sys.databases
WHERE 1=1

-- Using WHERE 1=1 and various AND modifiers
SELECT * FROM sys.databases
WHERE 1=1
--AND [database_id] < 5
AND [database_id] > 4

WHERE IN

-- 0307-02-05 Demonstration Code ================
-- Filter the result set with IN()
SELECT
'MyColumnValue' AS [New Column],
[name] AS [Database Name],
@@SPID AS [My Spid],
[create_date] AS [Created Date]
FROM [sys].[databases] tbl
WHERE [name] IN ('master', 'model')

-- 0307-02-10 Demonstration Code ================
-- Filter the result set with IN()
SELECT
'MyColumnValue' AS [New Column],
[name] AS [Database Name],
@@SPID AS [My Spid],
[create_date] AS [Created Date]
FROM [sys].[databases] tbl
WHERE tbl.[name] IN ('master', 'model')

-- 0307-02-15 Demonstration Code ================
-- Filter the result set with IN()
-- Fails: Column aliases cannot be referenced in the WHERE clause
SELECT
'MyColumnValue' AS [New Column],
[name] AS [Database Name],
@@SPID AS [My Spid],
[create_date] AS [Created Date]
FROM [sys].[databases] tbl
WHERE [Database Name] IN ('master', 'model')

GROUP BY

-- 0307-03-05 Demonstration Code ================
-- Group the result set by created datetime
SELECT [create_date], COUNT(*)
FROM [sys].[databases]
GROUP BY [create_date]

-- 0307-03-10 Demonstration Code ================
-- Group the result set by the created year
SELECT YEAR([create_date]), COUNT(*)
FROM [sys].[databases]
GROUP BY YEAR([create_date])

-- 0307-03-15 Demonstration Code ================
-- Group and order the result set by the created year and month
SELECT YEAR([create_date]), MONTH([create_date]), COUNT(*)
FROM [sys].[databases]
GROUP BY YEAR([create_date]), MONTH([create_date])
ORDER BY YEAR([create_date]), MONTH([create_date])

HAVING

HAVING filters the newly grouped rows.

In simple words, the WHERE and HAVING clauses act as filters; they remove records or data that don’t meet certain criteria from the final result of a query.

However, they are applied to different sets of data. That’s the important point to understand about WHERE vs. HAVING: WHERE filters at the record level, while HAVING filters at the "group of records" level.

The idea is to obtain metrics at the person level and at the family level.

Here is a sample query that uses the WHERE clause: Suppose we want to obtain the names of people with an annual income greater than $100,000. We need to filter (or discard) at the record level, so we will use the WHERE clause instead of the HAVING clause for this query:

SELECT GivenName, LastName
FROM Income
WHERE Income > 100000

Now let’s try a similar query, but this time with the HAVING clause: Suppose we want to obtain the last_name of families having a household income (i.e. the summed income of all family members) over $100,000. This is a clear case for using the HAVING clause, as we don’t need to filter by record. (We are not going to discard a person’s record because they make less than $100,000.) The idea is to filter based on family income, so we need to group persons by last_name and use HAVING to filter the groups of persons, as shown below:

SELECT LastName,
SUM(Income) AS "family_income"
FROM Income
GROUP BY LastName
HAVING SUM(Income) > 100000;

ORDER BY

Can be specified by Name or Position

-- 0307-05-05 Demonstration Code ================
-- Ordered by a name column name
SELECT
[name] AS [Database Name],
[create_date] AS [Created Date]
FROM [sys].[databases] AS tbl
--ORDER BY [Created Date] ASC
ORDER BY [create_date] DESC

-- 0307-05-10 Demonstration Code ================
-- Ordered by a name column (Alias name)
SELECT
[name] AS [Database Name],
[create_date] AS [Created Date]
FROM [sys].[databases] AS tbl
--ORDER BY [Created Date] ASC
ORDER BY [Created Date] DESC

-- 0307-05-15 Demonstration Code ================
-- Ordered by column position in the result set
SELECT
[name] AS [Database Name],
[create_date] AS [Created Date]
FROM [sys].[databases] AS tbl
--ORDER BY 1 ASC
ORDER BY 2 DESC

DISTINCT

-- 0307-06-05 Demonstration Code ================
-- Filter result set to 1 row per created year
SELECT DISTINCT(YEAR([create_date]))
FROM [sys].[databases]

Static Column Values

Adding a column to a result set with a static values is accomplished by inserting the value into the SELECT statement.

-- 0307-07-05 Demonstration Code ================
-- Add a static value column
SELECT
'MyColumnValue' AS [New Column],
[name] AS [Database Name],
[create_date] AS [Created Date]
FROM [sys].[databases] tbl

-- 0307-07-10 Demonstration Code ================
-- Add a column with variable 'SPID'
SELECT
'MyColumnValue' AS [New Column],
[name] AS [Database Name],
@@SPID AS [My Spid],
[create_date] AS [Created Date]
FROM [sys].[databases] tbl

Troubleshooting Syntax

When writing SQL queries there are many places where a small mistype can cause you to receive an error. We will review some of the most common errors due to syntax mistakes.

Spelling

Misspellings are the most common cause for error in SQL. Unfortunately, SQL will not autocorrect mistyped keywords, tables, columns, or values.

Check keyword spelling by referring to the documentation for the type of SQL you are using.

Check table spelling by referring to the database schema.

Check column spelling by referring to the database schema or doing SELECT * FROM the table you are trying to check the column name on.

Check value spelling by doing SELECT * FROM table GROUP BY the column where the value is in. Then look through this column to find the value you were trying to match.

Single vs. double quotes

A very common, yet not so obvious syntax error you can make is to use the wrong type of quotation marks in your query. This can change the meaning of what you are referencing inside the quotes.

Data Types

A common syntax error is not using the correct data type when comparing a value in a field with some constant in your query. Sometimes when a number is stored in a database it is stored as a string.

Clause Order

When writing a SQL query you have to place the clauses you use in this order.

SELECT
FROM
JOIN
WHERE
GROUP BY
HAVING
ORDER BY

You do not need to include all of these clauses in a SQL query but you do need at least the first two. The most common mistake is placing the HAVING clause ahead of the GROUP BY clause. Or placing the WHERE clause after the GROUP BY.

Fix Some Scripts

An essential skill for DBAs to possess is the ability to correct syntax errors in script code. Most of the issues are created by ourselves but it is also common to help others with the code they are creating.

This is not a test, but a way to see how much of this chapter has been retained — for your own review.

Here are some scripts with basic syntax errors.

-- 0308-01-05 Fix Some Scripts ==================

--Script 1 --------------------------------------
DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
SET @Name = 'Jane Doe'
GO
SELECT @Name

---

--Script 2 --------------------------------------
DECLARE @Name varchar(50)
SET @Name = 'John Smith'
SELECT @Name
GO
SET @Name = 'Jane Doe'
SELECT @Name

---

--Script 3 --------------------------------------
USE [MyDatabose]
GO
SELECT * FROM ...MyIdentityTable

---

--Script 4 --------------------------------------
USE [MyDatabase]
GO
SELECT * FROM ....MyIdentityTable

---

--Script 5 --------------------------------------
SELECT
[name],
[create_date
FROM [sys].[databases]

---

--Script 6 --------------------------------------
USE [MyDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyFixedTable](
[ID] [int](1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](128) NULL
) ON [PRIMARY]
GO

---

--Script 7 --------------------------------------
--
SELECT *
FROM MyTable
--
-end

---

--Script 8 --------------------------------------
--
/* This is a basic script /
SELECT *
FROM MyTable
--
--end
--end