Free Microsoft Training from Microsoft Virtual Academy

What is MVA?

Successful technologists never stop learning and great technology never stops evolving. Microsoft Virtual Academy (MVA) offers online Microsoft training delivered by experts to help technologists continually learn, with hundreds of courses, in 11 different languages. Our mission is to help developers, knowledgeable IT professionals and advanced students learn the latest technology, build their skills, and advance their careers. MVA is free of charge, and the entire service is hosted on Windows Azure.


SQL Alternative to Virtual Table using “With” Keyword

Let’s say I have a table called States with the following columns and data.


Now let’s say I need to create a virtual table that consists of the ID and Abbreviation columns only.  I can do this by using the “with” keyword.  Here’s an example:

with myVirtualTable as (
Select ID, Abbreviation
From States

select * From myVirtualTable

Here are the results:



Look Up Tables in SQL Database Using SysObject

This one is probably a no brainer for many SQL experts, but I continue to talk to people that don’t know how to do this.

Here’s SQL to look up user tables:

FROM	dbo.SysObjects
Where	XType = 'U'
Order by Name

For Stored Procedures, replace “XType = ‘U'” with “XType = ‘P'”

There’s a lot more you can find by replacing that string.  That table holds objects in the database including tables, columns, foreign keys, primary keys, views, triggers, constraints, defaults, and more.  Here’s a link for all the possible values.

How to Create an Identity Column with SQL “Into” Statement

Use the syntax below to create an identity column with an “Into” statement.

SELECT IDENTITY(INT, 1, 1) AS MyIdentityColumn
INTO #temp
FROM dbo.SysObjects
Where	XType = 'U'
Order by Name

How to Set SQL Variables in Dynamic SQL Text

The following example shows how to use variables for input and output for execution of sql text.  This is handy for stored procedures that use sp_executesql for executing query text against a database.

-- Declare Input/Output Variables
Declare @vc_sql 		nvarchar(4000)	-- MUST BE NVARCHAR
	,@vc_input_Name  	varchar(200)
	,@sdt_output_BirthDay 	Smalldatetime

select @vc_sql = '
Select @sdt_output_BirthDay  = Employee.Birthday
From  dbo.Employee (NoLock)
where  Employee.Name >= ''@vc_input_Name'''

exec sp_executesql 	@vc_sql
			,N'@vc_input_Name varchar(200), @sdt_output_BirthDay Smalldatetime output'
			,@sdt_output_BirthDay out