DDL: Create Statements

Table

Let’s create a table dbo.Song.

CREATE TABLE dbo.Song (
	ID		int IDENTITY(1,1) PRIMARY KEY
,	Name		nvarchar(100)
,	Duration	int);

View

A view is a stored query that allows us encapsulate a larger query into a database object that we can reference like a table object.

SELECT * FROM dbo.vLongSongs; -- select from view

To create a really simple view:

CREATE VIEW dbo.vLongSongs AS 
	SELECT
		*
	FROM dbo.Song
	WHERE Duration >= 300;

Index

Let’s a create a non-clustered index on dbo.Songs.

CREATE NONCLUSTERED INDEX IX_NAME ON dbo.Songs (Name);

Stored Procedure

Let’s create a very simple stored procedure that selects 100 rows from both a table and from a view.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.spSelectTableAndView
AS
BEGIN
	SET NOCOUNT ON;
	SELECT TOP 100 * FROM dbo.Songs;
	SELECT TOP 100 * FROM dbo.vLongSongs;
END