Author Archives: Robert

Creating a virtual machine with Vboxmanage

Posted on July 15, 2014 by Robert

Lately, I’ve been creating, deleting and recreating a lot of virtual machines with Oracle VirtualBox. So today, when I needed to start with a clean environment, I decided to create a virtual machine using the command line instead of the … Continue reading →

Posted in Uncategorized | 1 Comment

Two wrongs make a right: tricking the query optimizer

Posted on May 6, 2014 by Robert

Sometimes, the query optimizer makes mistakes. Instead of selecting the most optimal plan, a sub-optimal plan is chosen, and it is your task as a DBA to help SQL choose the right plan. In this case, the optimizer overestimated the … Continue reading →

Posted in Performance | Leave a comment

Code samples for my book on Database Fundamentals (MTA 98-364)

Posted on February 3, 2014 by Robert

A while ago, I’ve published my book on Database Fundamentals for SQL Server 2008. The book covers everything you need to know to pass the Microsoft exam on this topic, the MTA 98-364. This exam is part of the Microsoft … Continue reading →

Posted in MTA 98-364 | Leave a comment

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 3

Posted on January 27, 2014 by Robert

Chapter 3, Using Multiple Tables.

CREATE TABLE Customers (
CustomerID int IDENTITY
,[First name] varchar(100)
,[Last name] varchar(100)
,[Address] varchar(100)
,PRIMARY KEY (CustomerID))

CREATE TABLE Orders (
OrderID int PRIMARY KEY
,CustomerID int NULL
,OrderDate datetime
,CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

CREATE TABLE Orderlines (
OrderID int references orders(orderid)
,[Order line number] int
,Item varchar(100)
,quantity int
,price decimal(18,2)
,primary key (orderid, [Order line number]))

CREATE TABLE Employees (
EmployeeID int IDENTITY
,[First name] varchar(100) 
,[Last name] varchar(100)
,[Address] varchar(100)
,PRIMARY KEY (EmployeeID))

--DROP TABLE orderlines
--DROP TABLE Orders
--DROP TABLE customers
--DROP TABLE Employees

INSERT Customers values ('John','Smith','First Street 1, Washington');
INSERT Customers values ('Peter','Jones','Second Street 2, London');
INSERT Customers values ('Frank','James','Champs-Elysees , Paris');
INSERT Customers values ('Jim','Gray','Main Street 7, New York');

INSERT Customers VALUES 
('Peter', 'Jones', 'Second Street 2, London')
,('Frank', 'James', 'Champs-Elysees , Paris')
,('Jim', 'Gray', 'Main Street 7, New York');

INSERT Orders VALUES ('1','1', '2013-01-01');
INSERT Orders VALUES ('2','2', '2012-07-01');
INSERT Orders VALUES ('3','3', '2012-08-15');
INSERT Orders VALUES ('4','4', '2012-12-31');

INSERT orderlines values (1,1,'A Brief History of Time – S. Hawking',1,9.99);
INSERT orderlines values (2,1,'The Prince – Macchiavelli',10,59.90);
INSERT orderlines values (3,1,'Lord of the Rings – J.R.R. Tolkien',2,17.98);
INSERT orderlines values (4,1,'The Prince – Macchiavelli',1,5.99);

INSERT INTO Employees values ('Barry','St. John','Broadway 5, New York');
INSERT Employees values ('Jim','Gray','Main Street 7, New York');

--outer join
SELECT *
FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN Orderlines ol on ol.OrderID = o.OrderID;

--subqueries 
SELECT *
FROM Customers
WHERE [First name] in ( SELECT [First name]
FROM Employees);


--subquery
SELECT 'Address' = [Street name] + [House number] + ', ' + City
FROM Shipments
--table subquery example
CREATE TABLE Shipments (
 ShipmentID int identity
 ,Shipmentdate datetime
 ,[Street name] varchar(100)
 ,[House number] varchar(10)
 ,[City] varchar(100));
--outer query
SELECT *
FROM Customers c 
 INNER JOIN (...) s on c.Address = s.Address
 
--Total
SELECT *
FROM Customers c 
 INNER JOIN (SELECT 'Address' = [Street name] + [House number] + ', ' + City
 FROM Shipments
 ) s on c.Address = s.Address;

CREATE TABLE Customers (

CustomerID int IDENTITY

,[First name] varchar(100)

,[Last name] varchar(100)

,[Address] varchar(100)

,PRIMARY KEY (CustomerID))

CREATE TABLE Orders (

OrderID int PRIMARY KEY

,CustomerID int NULL

,OrderDate datetime

,CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

CREATE TABLE Orderlines (

OrderID int references orders(orderid)

,[Order line number] int

,Item varchar(100)

,quantity int

,price decimal(18,2)

,primary key (orderid, [Order line number]))

CREATE TABLE Employees (

EmployeeID int IDENTITY

,[First name] varchar(100)

,[Last name] varchar(100)

,[Address] varchar(100)

,PRIMARY KEY (EmployeeID))

--DROP TABLE orderlines

--DROP TABLE Orders

--DROP TABLE customers

--DROP TABLE Employees

INSERT Customers values ('John','Smith','First Street 1, Washington');

INSERT Customers values ('Peter','Jones','Second Street 2, London');

INSERT Customers values ('Frank','James','Champs-Elysees , Paris');

INSERT Customers values ('Jim','Gray','Main Street 7, New York');

INSERT Customers VALUES

('Peter', 'Jones', 'Second Street 2, London')

,('Frank', 'James', 'Champs-Elysees , Paris')

,('Jim', 'Gray', 'Main Street 7, New York');

INSERT Orders VALUES ('1','1', '2013-01-01');

INSERT Orders VALUES ('2','2', '2012-07-01');

INSERT Orders VALUES ('3','3', '2012-08-15');

INSERT Orders VALUES ('4','4', '2012-12-31');

INSERT orderlines values (1,1,'A Brief History of Time – S. Hawking',1,9.99);

INSERT orderlines values (2,1,'The Prince – Macchiavelli',10,59.90);

INSERT orderlines values (3,1,'Lord of the Rings – J.R.R. Tolkien',2,17.98);

INSERT orderlines values (4,1,'The Prince – Macchiavelli',1,5.99);

INSERT INTO Employees values ('Barry','St. John','Broadway 5, New York');

INSERT Employees values ('Jim','Gray','Main Street 7, New York');

--outer join

SELECT *

FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID

JOIN Orderlines ol on ol.OrderID = o.OrderID;

--subqueries

SELECT *

FROM Customers

WHERE [First name] in ( SELECT [First name]

FROM Employees);

--subquery

SELECT 'Address' = [Street name] + [House number] + ', ' + City

FROM Shipments

--table subquery example

CREATE TABLE Shipments (

ShipmentID int identity

,Shipmentdate datetime

,[Street name] varchar(100)

,[House number] varchar(10)

,[City] varchar(100));

--outer query

SELECT *

FROM Customers c

INNER JOIN (...) s on c.Address = s.Address

--Total

SELECT *

FROM Customers c

INNER JOIN (SELECT 'Address' = [Street name] + [House number] + ', ' + City

FROM Shipments

) s on c.Address = s.Address;

Posted in Code samples, MTA 98-364 | Leave a comment

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 2

Posted on January 27, 2014 by Robert

Code samples for chapter 2, using a Single Table.

-- create table dbo.Persons, scripted by SSMS
USE [testdatabase]
GO

/****** Object: Table [dbo].[Persons] Script Date: 01/02/2013 20:37:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Persons](
[Firstname] [varchar](100) NOT NULL,
[Lastname] [varchar](100) NOT NULL,
[Address] [varchar](100) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-- create table dbo.Persons
DROP TABLE Persons
GO
CREATE TABLE [Persons] (
Firstname varchar(100) NOT NULL,
Lastname varchar(100) NOT NULL,
Address varchar(100) NULL DEFAULT '1600 Pennsylvania Avenue, Washington D.C.')

-- insert records into table dbo.Persons
INSERT Persons (Firstname, Lastname)VALUES ('John', 'Smith' );
INSERT Persons VALUES ('Peter', 'Jones', 'Second Street 2, London');
INSERT Persons VALUES ('Frank', 'James', 'Champs-Elysees, Paris');
INSERT Persons VALUES ('Elvis', 'Presley', NULL);

-- select records from table dbo.Persons
SELECT *
FROM Persons

SELECT Firstname
,Lastname
,Address
FROM Persons;

SELECT Firstname,Lastname,Address FROM Persons;

SELECT *
FROM Persons
ORDER BY Lastname;

SELECT *
FROM Persons
ORDER BY Lastname, Firstname;

SELECT *
FROM Persons
ORDER BY Lastname DESC;

SELECT *
FROM Persons
WHERE Lastname = 'Jones';

SELECT *
FROM Persons
WHERE Lastname = 'James'
or Lastname = 'Jones' ;

SELECT *
FROM Persons
WHERE Lastname IN ('James', 'Jones') ;

SELECT *
FROM Persons
WHERE Lastname LIKE 'Jam%';

SELECT *
FROM Persons
WHERE Lastname IS NULL;

SELECT *
FROM Persons
WHERE Lastname = 'James'
and Lastname = 'Jones' ;

SELECT TOP 2 *
FROM Persons;

SELECT DISTINCT [Lastname]
FROM Persons;

SELECT 'Achternaam' = Lastname
FROM Persons

SELECT Lastname AS 'Achternaam'
FROM Persons

SELECT Lastname + ', ' + Firstname AS 'Name'
FROM Persons

SET CONCAT_NULL_YIELDS_NULL OFF

SET CONCAT_NULL_YIELDS_NULL ON

SELECT 'any string'
FROM Persons

-- update records in table dbo.Persons
UPDATE Persons
SET Firstname = 'Frank jr.'
, Address = 'Unter den Linden, Berlin'
WHERE Firstname = 'Frank jr.';

UPDATE Persons
SET Firstname = 'Frank'
, Address = 'Unter den Linden, Berlin'
WHERE Firstname = 'Frank jr.';

-- delete 1 record (in case of duplicates)
DELETE
FROM Persons
WHERE Lastname = 'James';

DELETE * /*comment: this delete statement will produce a syntax error*/
FROM Persons
WHERE Lastname = 'James'

DELETE TOP (1)
FROM Persons
WHERE Firstname = 'John';

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

-- create table dbo.Persons, scripted by SSMS

USE [testdatabase]

/****** Object: Table [dbo].[Persons] Script Date: 01/02/2013 20:37:06 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Persons](

[Firstname] [varchar](100) NOT NULL,

[Lastname] [varchar](100) NOT NULL,

[Address] [varchar](100) NOT NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

-- create table dbo.Persons

DROP TABLE Persons

CREATE TABLE [Persons] (

Firstname varchar(100) NOT NULL,

Lastname varchar(100) NOT NULL,

Address varchar(100) NULL DEFAULT '1600 Pennsylvania Avenue, Washington D.C.')

-- insert records into table dbo.Persons

INSERT Persons (Firstname, Lastname)VALUES ('John', 'Smith' );

INSERT Persons VALUES ('Peter', 'Jones', 'Second Street 2, London');

INSERT Persons VALUES ('Frank', 'James', 'Champs-Elysees, Paris');

INSERT Persons VALUES ('Elvis', 'Presley', NULL);

-- select records from table dbo.Persons

SELECT *

FROM Persons

SELECT Firstname

,Lastname

,Address

FROM Persons;

SELECT Firstname,Lastname,Address FROM Persons;

SELECT *

FROM Persons

ORDER BY Lastname;

SELECT *

FROM Persons

ORDER BY Lastname, Firstname;

SELECT *

FROM Persons

ORDER BY Lastname DESC;

SELECT *

FROM Persons

WHERE Lastname = 'Jones';

SELECT *

FROM Persons

WHERE Lastname = 'James'

or Lastname = 'Jones' ;

SELECT *

FROM Persons

WHERE Lastname IN ('James', 'Jones') ;

SELECT *

FROM Persons

WHERE Lastname LIKE 'Jam%';

SELECT *

FROM Persons

WHERE Lastname IS NULL;

SELECT *

FROM Persons

WHERE Lastname = 'James'

and Lastname = 'Jones' ;

SELECT TOP 2 *

FROM Persons;

SELECT DISTINCT [Lastname]

FROM Persons;

SELECT 'Achternaam' = Lastname

FROM Persons

SELECT Lastname AS 'Achternaam'

FROM Persons

SELECT Lastname + ', ' + Firstname AS 'Name'

FROM Persons

SET CONCAT_NULL_YIELDS_NULL OFF

SET CONCAT_NULL_YIELDS_NULL ON

SELECT 'any string'

FROM Persons

-- update records in table dbo.Persons

UPDATE Persons

SET Firstname = 'Frank jr.'

, Address = 'Unter den Linden, Berlin'

WHERE Firstname = 'Frank jr.';

UPDATE Persons

SET Firstname = 'Frank'

, Address = 'Unter den Linden, Berlin'

WHERE Firstname = 'Frank jr.';

-- delete 1 record (in case of duplicates)

DELETE

FROM Persons

WHERE Lastname = 'James';

DELETE * /*comment: this delete statement will produce a syntax error*/

FROM Persons

WHERE Lastname = 'James'

DELETE TOP (1)

FROM Persons

WHERE Firstname = 'John';

Posted in Code samples, MTA 98-364 | Leave a comment

Finding the application timeout

Posted on December 16, 2013 by Robert

The problem Last week, an application started reporting SQL timeouts. By default, SQL does not have a query timeout; it is the application that decides it doesn’t want to wait any longer. The question was: how do we find out … Continue reading →

Posted in Performance | Leave a comment

The curious case of the trailing space

Posted on December 4, 2013 by Robert

Some weeks ago, a developer told me he had found some strange behavior in SQL Server. Someone immediatelly yelled “bug”. Comments like this always grab my attention. More often than not, however, it’s not a bug. That also turned out … Continue reading →

Posted in T-SQL | Leave a comment

Index reorg problem

Posted on November 24, 2013 by Robert

Problem Recently, we upgraded an application to a new version. The following night, our standard maintenance plan failed. The job output log file gave the following error: Failed:(-1073548784) Executing the query “ALTER INDEX […] ON […” failed with the following … Continue reading →

Posted in Indexes | Leave a comment

Hello world!

Posted on October 23, 2013 by Robert

This is my first blog post. I’ll skip introducing myself, because you can read more than enough about me in the section that is appropriately labeled “about me”. Instead, I’ll tell you why I’m starting this blog. The main reason … Continue reading →

Posted in Blogging | Leave a comment

Author Archives: Robert

Creating a virtual machine with Vboxmanage

Two wrongs make a right: tricking the query optimizer

Code samples for my book on Database Fundamentals (MTA 98-364)

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 3

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 2

Finding the application timeout

The curious case of the trailing space

Index reorg problem

Hello world!

Recent Posts

Archives

Categories