One of my DBAs recently reported to me that our indexing job was failing because a database included in the re-indexing code was actually named after a T-SQL keyword. I think I actually guffawed, not because it was impossible, but just because this "actually" happened. So, being inquisitive, I took this whole idea that a database could be named a keyword to the next few levels. How about tables, fields and the data itself? What follows is the query to create the "SELECT" database and a couple of keyword-named tables. It is an excercise in the ridiculous for sure, but makes one wonder. What if you failed to identify your objects with brackets or quoted identifiers?

Query to build SELECT database. Also, this script was created from the new "Script Data" section of the SSMS 2008 "Generate Scripts" wizard to save a few steps (see previous blog).

--BEGIN SCRIPT

USE [master]

CREATE DATABASE [SELECT] ON PRIMARY

( NAME = N'SELECT', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SELECT.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'SELECT_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SELECT_1.ldf' , SIZE = 9216KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [SELECT] SET COMPATIBILITY_LEVEL = 90

GO

USE [SELECT]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[IN](

[BETWEEN] [nchar](25) NULL,

[LIKE] [nchar](10) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'AVG ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'ABS ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'AND ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BACKUP ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BEGIN ', N'END ')

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'BETWEEN ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'CHECKPOINT ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'HAVING ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'EXIT ', NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'IF ', N' ')

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N'LIKE ', N'LIKE ')

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[FROM](

[Where] [int] NULL

) ON [PRIMARY]

GO

INSERT [dbo].[FROM] ([Where]) VALUES (1)

INSERT [dbo].[FROM] ([Where]) VALUES (2)

INSERT [dbo].[FROM] ([Where]) VALUES (3)

INSERT [dbo].[FROM] ([Where]) VALUES (4)

--END Script

And what do you do with a database named SELECT with tables named FROM and IN? Well of course, you populate them with other keywords and query those values. Without explanation we delve deeper with a couple of interesting queries that actually work.

The first: