Common Solutions for T-SQL Problems

UPDATED WIKI: ExpressMaintenance

JonathanKehayias — Thu, 17 Jul 2008 18:53:52 GMT

How to Automate Maintenace Tasks with SQL Server Express

Jonathan Kehayias, May 5, 2008

One of the items missing from SQL Server Express is the ability to schedule jobs to run at set times since it lacks the SQL Server
Agent Service. This makes scheduling automated maintenance tasks that are easily available in the other editions of SQL Server
through Database Maintenance Plans, difficult at best, but not impossible. Since the Database Maintenance Plans cover a number
of tasks, they will be broken down individually to allow for implemenation of all or some of the tasks.

The commonly used tasks in the Database Maintenance Plan Wizard are:

Check Database Integrity
Shrink Database
Reorganize/Rebuild Indexes/Up Statistics
Clean Up History
Backup Database (Full, Differential, and/or Transaction Logs)
Maintenance Cleanup Task
Other Published Solutions

Check Database Integrity

Second only to good database backups, validating the integrity of the SQL Databases should be a top priority for anyone running a
SQL Database. To accomplish this, a simple stored procedure will be created that allows for the database consistency checks to be
run against every database in an instance or a single database only.

CREATE PROCEDURE [dbo].[CheckDatabaseIntegrity]
(
	@DatabaseName sysname = null
)
AS
 
-- -- Begin Test Code
--DECLARE @DatabaseName sysname
--SET @DatabaseName = 'tempdb'
-- -- End Test Code
 
IF @DatabaseName IS NULL
BEGIN
	EXEC sp_msforeachdb 'DBCC CHECKDB(''?'')'
END
ELSE
	EXEC ('DBCC CHECKDB('''+@DatabaseName+''')'
END
 
 
/*
-- Execute Full Consistency Check of all Databases
sqlcmd -S .\EXPRESS -Q "EXEC CheckDatabaseIntegrity"
 
-- Execute Full Consistency Check of the WebContacts Database
sqlcmd -S .\EXPRESS -Q "EXEC CheckDatabaseIntegrity @DatabaseName='WebContacts'"
 
*/

Return to Top

Shrink Database

From time to time it is necessary to shrink the size of a database down, perhaps monthly after a data purge process runs. To allow this,
a shrinking stored procedure will be built.

CREATE PROCEDURE [dbo].[ShrinkDatabase]
(
	@DatabaseName sysname = null,
	@FreeSpace int = 0
)
AS
 
-- -- Begin Test Code
--DECLARE @DatabaseName sysname
--SET @DatabaseName = 'tempdb'
-- -- End Test Code
 
IF @DatabaseName IS NULL
BEGIN
	EXEC sp_msforeachdb 'DBCC SHRINKDATABASE(''?'', @FreeSpace)'
END
ELSE
	EXEC ('DBCC SHRINKDATABASE('''+@DatabaseName+''', @FreeSpace)'
END
 
 
/*
-- Execute Full Shrink Check of all Databases
sqlcmd -S .\EXPRESS -Q "EXEC ShrinkDatabase"
 
-- Execute Full Shrink of the WebContacts Database
sqlcmd -S .\EXPRESS -Q "EXEC ShrinkDatabase @DatabaseName='WebContacts'"
 
-- Execute Shrink of the WebContacts Database Leaving 10% FreeSpace
sqlcmd -S .\EXPRESS -Q "EXEC ShrinkDatabase @DatabaseName='WebContacts' @FreeSpace=10"
 
*/

Return to Top

Perform Index Maintenance

Index Maintenance is crucial to optimum performance of a SQL Server Database. Microsoft recommends that indexes
containing over 30% fragmentation be rebuilt, while indexes having less than 30% fragmentation be reorganized. In order
to implement this process in SQL Express, two stored procedures will need to be created. The first one is a modified version
of a procedure provided by Bill Baer for performing database maintenance on Sharepoint databases, a task that was
unsupported using Database Maintenance Plans with the RTM release of SQL Server. The reason that his procedure was
chosen for this task is two fold. First, it is provided in a Microsoft Whitepaper, and second, it updates statistics as a part of its
execution.

USE [master]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- This stored procedure checks all indexes in the current
-- database and performs either offline or online defragmentation
-- according to the specified thresholds.
-- The stored procedure also updates statistics for indexes in which the last update
-- time is older than the specified threshold.
-- Parameters:
--	@onlineDefragThreshold specifies minimum percentage of fragmentation 
--	to perform online defragmentation (default 10%).
--	@offlineDefragThreshold specifies minimum percentage of fragmentation 
--	to perform offline defragmentation (default 30%).
--	@updateStatsThreshold specifies the number of days since the last statistics update
--	which should trigger updating statistics (default 7 days).
-- =============================================
CREATE PROCEDURE [dbo].[sp_DefragIndexes] 
(
	@databaseName sysname = null,
	@onlineDefragThreshold float = 10.0,
	@offlineDefragThreshold float = 30.0,
	@updateStatsThreshold int = 7
)
	
AS
BEGIN
 
IF @databasename is null
BEGIN
	RETURN;
END
 
DECLARE @SQL nvarchar(max)
SET @SQL = 'USE '+ @databasename +'
 
	set nocount on
	DECLARE @objectid int
	DECLARE @indexid int
	DECLARE @frag float
	DECLARE @command varchar(8000)
	DECLARE @schemaname sysname
	DECLARE @objectname sysname
	DECLARE @indexname sysname
 
	declare @AllIndexes table (objectid int, indexid int, fragmentation float)
 
	declare @currentDdbId int
	select @currentDdbId = DB_ID()
	
	insert into @AllIndexes
	SELECT 
		object_id, index_id, avg_fragmentation_in_percent 
	FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL , NULL, ''LIMITED'')
	WHERE index_id > 0
 
	DECLARE indexesToDefrag CURSOR FOR SELECT * FROM @AllIndexes
 
	OPEN indexesToDefrag;
 
	-- Loop through the partitions.
	FETCH NEXT
	   FROM indexesToDefrag
	   INTO @objectid, @indexid, @frag;
 
	WHILE @@FETCH_STATUS = 0
		BEGIN
 
		SELECT @schemaname = s.name
		FROM sys.objects AS o
		JOIN sys.schemas as s ON s.schema_id = o.schema_id
		WHERE o.object_id = @objectid
 
		SELECT @indexname = name 
		FROM sys.indexes
		WHERE  object_id = @objectid AND index_id = @indexid
 
		IF @frag > @onlineDefragThreshold
		BEGIN 
			IF @frag < @offlineDefragThreshold
				BEGIN;
					SELECT @command = ''ALTER INDEX '' + @indexname + '' ON '' + 
							@schemaname + ''.'' + object_name(@objectid) + 
							'' REORGANIZE''
					EXEC (@command)
				END
 
			IF @frag >= @offlineDefragThreshold
				BEGIN;
					SELECT @command = ''ALTER INDEX '' + 
							@indexname +'' ON '' + @schemaname + ''.'' + 
							object_name(@objectid) + '' REBUILD''
					EXEC (@command)
				END;
			PRINT ''Executed '' + @command
		END
 
		IF STATS_DATE(@objectid, @indexid) < DATEADD(dd, -@updateStatsThreshold, getdate())
		BEGIN
			SELECT @command = ''UPDATE STATISTICS '' + @schemaname + ''.'' + object_name(@objectid) + 
					'' '' + @indexname +'' WITH RESAMPLE''
			EXEC (@command)
 
			PRINT ''Executed '' + @command
		END
 
		FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @frag
 
	END
 
	CLOSE indexesToDefrag;
	DEALLOCATE indexesToDefrag;'
 
DECLARE @Params nvarchar(max)
SET @Params = N'
	@onlineDefragThreshold float,
	@offlineDefragThreshold float,
	@updateStatsThreshold int'
 
EXECUTE sp_executesql @SQL, 
		@Params,
		@onlineDefragThreshold=@onlineDefragThreshold,
		@offlineDefragThreshold=@offlineDefragThreshold,
		@updateStatsThreshold=@updateStatsThreshold;
END

The second procedure is the actual wrapper procedure that will be called from the command line to schedule the database
maintenance. Like all of the other procedures in this it accepts the database name as an input parameter to allow for
reindexing a single database, but it can also reindex all of the databases on the SQL Server by calling it without the parameter.

CREATE PROCEDURE [dbo].[PerformIndexMaintenance]
(
	@DatabaseName sysname = null
)
AS
 
-- -- Begin Test Code
--DECLARE @DatabaseName sysname
--SET @DatabaseName = 'tempdb'
-- -- End Test Code
 
IF @DatabaseName IS NULL
BEGIN
	EXEC sp_msforeachdb N'EXEC sp_DefragIndexes ''[?]'''
END
ELSE
	SET @DatabaseName = '['+REPLACE(REPLACE(@DatabaseName,'[', ''),']','')+']'
	EXEC sp_DefragIndexes @DatabaseName
END
 
 
/*
-- Execute Index Maintenance of all Databases
sqlcmd -S .\EXPRESS -Q "EXEC PerformIndexMaintenance"
 
-- Execute Index Maintenance on the WebContacts Database
sqlcmd -S .\EXPRESS -Q "EXEC PerformIndexMaintenance @DatabaseName='WebContacts'"
 
*/
 
GO

Return to Top

History Cleanup

SQL Server tracks the backup history of your server in the msdb database in a series of tables. The core of this
task in the Database Maintenance Plans is the spdeletebackuphistory procedure in msdb. A simple wrapper
procedure will be created to call this procedure providing the @oldestdate parameter automatically set to the
current date minus 30 days.

CREATE PROCEDURE [dbo].[CleanupHistory]
AS
 
DECLARE @OldestDate DATETIME
SET @OldestDate = CONVERT(varchar(10), DATEADD(dd, -30, GETDATE()), 101)
 
EXEC sp_delete_backuphistory @OldestDate
 
/*
-- Execute Cleanup Task
sqlcmd -S .\EXPRESS -Q "EXEC CleanupHistory"
 
*/

Return to Top

Maintenance Cleanup Task

This task is used to delete files created by the Maintenance Tasks executing. Generally it is used to delete backup files from the
backup path that are older than a set number of days. To accomplish this, a simple VBScript can be created based on the
following code:

 
'Start
 
Option Explicit
on error resume next
    Dim oFSO
    Dim sDirectoryPath
    Dim oFolder
    Dim oFileCollection
    Dim oFile
    Dim iDaysOld
    Dim CurDir
    Dim strExtension
 
'Definitions
    iDaysOld = 3
    strExtension = ".bak"
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    sDirectoryPath = CreateObject("WScript.Shell").CurrentDirectory
    sDirectoryPath = sDirectory & "\"
    set oFolder = oFSO.GetFolder(sDirectoryPath)
    set oFileCollection = oFolder.Files 
 
'Walk through each file in this folder collection. 
    For each oFile in oFileCollection
        If oFile.DateLastModified < (Date() - iDaysOld) Then
  If (strExtension="") Or (Right(UCase(oFile.Name), Len(strExtension))=UCase(strExtension)) Then
             oFile.Delete(True)
  End If
        End If
    Next
 
'Clean up
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oFileCollection = Nothing
    Set oFile = Nothing
'End

This code has two variables, extension and number of days which can be set to control what it will delete. To use it, you simply
create a DeleteFiles.vbs file with this code in it and your needed values in the folder to have the files deleted. Then schedule a
task to execute this on the necessary schedule using the CScript.exe executable like the following:

cscript "C:\BackUp\DeleteFiles.vbs"

Return to Top

Automating Database maintenance in SQL 2005 Express Edition Part I

http://www.sqldbatips.com/showarticle.asp?ID=27

Automating Database maintenance in SQL 2005 Express Edition Part II

http://www.sqldbatips.com/showarticle.asp?ID=29

Ola Hallengren has also provided a really nice stored procedure set that were tested against SQL Server Express, and also
provided the following code examples for how to use them with SQL Express:

Backup of system and user databases

-- Objects needed: DatabaseBackup, CommandExecute, DatabaseSelect
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'SYSTEM_DATABASES', @Directory = 'C:\Backup', 
@BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24" -b
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'USER_DATABASES', @Directory = 'C:\Backup', 
@BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 24" -b
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'USER_DATABASES', @Directory = 'C:\Backup', 
@BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 24" -b
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'USER_DATABASES', @Directory = 'C:\Backup', 
@BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 24" -b

Integrity check of system and user databases

--Objects needed: DatabaseIntegrityCheck, CommandExecute, DatabaseSelect
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] 
@Databases = 'SYSTEM_DATABASES'" -b
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] 
@Databases = 'USER_DATABASES'" -b

Index Optimization of user databases

--Objects needed: IndexOptimize, CommandExecute, DatabaseSelect
 
sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES'" -b

All objects are available on his blog and are free to download.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

There is also documentation available here.
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html

For additional information please see:

Books Online References

UPDATED WIKI: TestPage

ArnieRowland — Wed, 02 Jul 2008 18:22:13 GMT

Test Environment

You may create links here to your new Wiki page in order to work on it and keep it out of Public view until you are ready to make it 'public'.
     Then when you are ready to make your page 'Public', note the Wiki PageName, and use it for the Wiki link on the referring page.
     After your page is complete, and you have 'published' it, you may wish to remove the link from this page.

     WikiLink: [Words Seen by User|YourWikiPage]
___________________________________________________________________________________________________________________

Detaching A Database with SSMS - Video Tutorial

Find out all the primary key and foreign key constraints in a table - Suprotim Agarwal

Testing out analytics - Suprotim

This concept 'could' work -requires a counter.cgi hosted somewhere...
(A bit tacky perhaps...)

Launch in another window

Contributors

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 16, 2008

UPDATED WIKI: NumbersTable

JonathanKehayias — Wed, 02 Jul 2008 18:18:24 GMT

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

A Numbers table is simply a table that holds a single integer column with each
positive integer number in the range from the number 1 to the maximum value
of an integer 2,147,483,647. (Note: When creating the table off the exact
code provided in this example below, your Numbers table will be
roughly 8GB in size. This is overkill in most cases and you should
size the number of rows being create according to your own needs.)
Creating a Numbers table is really simple to do and it
can be used to solve a broad number of problems.

Some Examples of how to use a Numbers Table are:

Identify Missing Dates
Get All Dates Between Two Dates
Identify Missing Numeric Values
Get All Numbers Between Two Values

These solutions apply to all versions of SQL Server.

Create Number Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create Sample Data using a Table Varable
SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e
 
-- Create a Primary Key and Clustered Index to control data order
ALTER TABLE dbo.Numbers ADD CONSTRAINT
PK_Numbers PRIMARY KEY CLUSTERED (N)

Return to Top

Identify Missing Dates In a Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Find the dates not in the table an in the first 14 days of January
SELECT DATEADD(dd, N, '01/01/2007') as MissingDates
FROM Numbers
WHERE n < 14
  AND DATEADD(dd, N, '01/01/2007') NOT IN (SELECT DateField FROM @TestTable)
 
 
/* Results
MissingDates
-----------------------
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-06 00:00:00.000
2007-01-11 00:00:00.000
2007-01-13 00:00:00.000
*/

Return to Top

Get All Dates Between Two Dates

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Date Range Values
DECLARE @StartDate datetime
DECLARE @EndDate datetime
 
SET @StartDate = '01/01/2007'
SET @EndDate = '01/07/2007'
 
-- Find the dates between these two dates
SELECT DATEADD(dd, N, @StartDate) as DatesBetween
FROM Numbers
WHERE n < (DATEDIFF(dd, @StartDate, @EndDate)+1)
 
 
/* Results
DatesBetween
-----------------------
2007-01-01 00:00:00.000
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-05 00:00:00.000
2007-01-06 00:00:00.000
2007-01-07 00:00:00.000
*/

Return to Top

Identify Missing Numeric Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Delete Odd Numbered Rows From Table
DELETE @TestTable WHERE RowID IN (1,3,5,7)
 
-- Find the missing row Numbers
SELECT N as MissingRows
FROM Numbers
WHERE N > 0 -- RowID's are greater than 0
  AND N <= (SELECT MAX(RowID) FROM @TestTable) -- Constrain to valid RowID's
  AND N NOT IN (SELECT RowID FROM @TestTable) -- RowID not in the table
 
 
/* Results
MissingRows
-----------
1
3
5
7
*/

Return to Top

Get All Numbers Between Two Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Number Range Values
DECLARE @Start int
DECLARE @End int
 
SET @Start = 14
SET @End = 22
 
-- Find the numbers between these two numbers
SELECT N as NumbersBetween
FROM Numbers
WHERE N BETWEEN @Start AND @End
 
 
/* Results
NumbersBetween
--------------
14
15
16
17
18
19
20
21
22
*/

Return to Top

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Apr 15, 2008

UPDATED WIKI: TestPage

ArnieRowland — Wed, 02 Jul 2008 18:16:02 GMT

Test Environment

Launch in another window

Contributors

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 16, 2008

UPDATED WIKI: NumbersTable

JonathanKehayias — Wed, 02 Jul 2008 18:15:19 GMT

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

A Numbers table is simply a table that holds a single integer column with each
positive integer number in the range from the number 1 to the maximum value
of an integer 2,147,483,647. (*Note: When creating the table off the exact code
provided in this example below, your Numbers table will be roughly 8GB in size.*
This is overkill in most cases and you should size the number of rows being create
according to your own personal needs.) Creating a Numbers table is really simple to do
and it can be used to solve a broad number of problems.

Some Examples of how to use a Numbers Table are:

Identify Missing Dates
Get All Dates Between Two Dates
Identify Missing Numeric Values
Get All Numbers Between Two Values

These solutions apply to all versions of SQL Server.

Create Number Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create Sample Data using a Table Varable
SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e
 
-- Create a Primary Key and Clustered Index to control data order
ALTER TABLE dbo.Numbers ADD CONSTRAINT
PK_Numbers PRIMARY KEY CLUSTERED (N)

Return to Top

Identify Missing Dates In a Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Find the dates not in the table an in the first 14 days of January
SELECT DATEADD(dd, N, '01/01/2007') as MissingDates
FROM Numbers
WHERE n < 14
  AND DATEADD(dd, N, '01/01/2007') NOT IN (SELECT DateField FROM @TestTable)
 
 
/* Results
MissingDates
-----------------------
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-06 00:00:00.000
2007-01-11 00:00:00.000
2007-01-13 00:00:00.000
*/

Return to Top

Get All Dates Between Two Dates

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Date Range Values
DECLARE @StartDate datetime
DECLARE @EndDate datetime
 
SET @StartDate = '01/01/2007'
SET @EndDate = '01/07/2007'
 
-- Find the dates between these two dates
SELECT DATEADD(dd, N, @StartDate) as DatesBetween
FROM Numbers
WHERE n < (DATEDIFF(dd, @StartDate, @EndDate)+1)
 
 
/* Results
DatesBetween
-----------------------
2007-01-01 00:00:00.000
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-05 00:00:00.000
2007-01-06 00:00:00.000
2007-01-07 00:00:00.000
*/

Return to Top

Identify Missing Numeric Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Delete Odd Numbered Rows From Table
DELETE @TestTable WHERE RowID IN (1,3,5,7)
 
-- Find the missing row Numbers
SELECT N as MissingRows
FROM Numbers
WHERE N > 0 -- RowID's are greater than 0
  AND N <= (SELECT MAX(RowID) FROM @TestTable) -- Constrain to valid RowID's
  AND N NOT IN (SELECT RowID FROM @TestTable) -- RowID not in the table
 
 
/* Results
MissingRows
-----------
1
3
5
7
*/

Return to Top

Get All Numbers Between Two Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Number Range Values
DECLARE @Start int
DECLARE @End int
 
SET @Start = 14
SET @End = 22
 
-- Find the numbers between these two numbers
SELECT N as NumbersBetween
FROM Numbers
WHERE N BETWEEN @Start AND @End
 
 
/* Results
NumbersBetween
--------------
14
15
16
17
18
19
20
21
22
*/

Return to Top

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Apr 15, 2008

UPDATED WIKI: NumbersTable

JonathanKehayias — Wed, 02 Jul 2008 18:14:48 GMT

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

A Numbers table is simply a table that holds a single integer column with each
positive integer number in the range from the number 1 to the maximum value
of an integer 2,147,483,647. (Note: When creating the table off the exact code
*provided in this example below, your Numbers table will be roughly 8GB in size.
This is overkill in most cases and you should size the number of rows being create
according to your own personal needs.*) Creating a Numbers table is really simple to do
and it can be used to solve a broad number of problems.

Some Examples of how to use a Numbers Table are:

Identify Missing Dates
Get All Dates Between Two Dates
Identify Missing Numeric Values
Get All Numbers Between Two Values

These solutions apply to all versions of SQL Server.

Create Number Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create Sample Data using a Table Varable
SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e
 
-- Create a Primary Key and Clustered Index to control data order
ALTER TABLE dbo.Numbers ADD CONSTRAINT
PK_Numbers PRIMARY KEY CLUSTERED (N)

Return to Top

Identify Missing Dates In a Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Find the dates not in the table an in the first 14 days of January
SELECT DATEADD(dd, N, '01/01/2007') as MissingDates
FROM Numbers
WHERE n < 14
  AND DATEADD(dd, N, '01/01/2007') NOT IN (SELECT DateField FROM @TestTable)
 
 
/* Results
MissingDates
-----------------------
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-06 00:00:00.000
2007-01-11 00:00:00.000
2007-01-13 00:00:00.000
*/

Return to Top

Get All Dates Between Two Dates

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Date Range Values
DECLARE @StartDate datetime
DECLARE @EndDate datetime
 
SET @StartDate = '01/01/2007'
SET @EndDate = '01/07/2007'
 
-- Find the dates between these two dates
SELECT DATEADD(dd, N, @StartDate) as DatesBetween
FROM Numbers
WHERE n < (DATEDIFF(dd, @StartDate, @EndDate)+1)
 
 
/* Results
DatesBetween
-----------------------
2007-01-01 00:00:00.000
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-05 00:00:00.000
2007-01-06 00:00:00.000
2007-01-07 00:00:00.000
*/

Return to Top

Identify Missing Numeric Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Delete Odd Numbered Rows From Table
DELETE @TestTable WHERE RowID IN (1,3,5,7)
 
-- Find the missing row Numbers
SELECT N as MissingRows
FROM Numbers
WHERE N > 0 -- RowID's are greater than 0
  AND N <= (SELECT MAX(RowID) FROM @TestTable) -- Constrain to valid RowID's
  AND N NOT IN (SELECT RowID FROM @TestTable) -- RowID not in the table
 
 
/* Results
MissingRows
-----------
1
3
5
7
*/

Return to Top

Get All Numbers Between Two Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Number Range Values
DECLARE @Start int
DECLARE @End int
 
SET @Start = 14
SET @End = 22
 
-- Find the numbers between these two numbers
SELECT N as NumbersBetween
FROM Numbers
WHERE N BETWEEN @Start AND @End
 
 
/* Results
NumbersBetween
--------------
14
15
16
17
18
19
20
21
22
*/

Return to Top

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Apr 15, 2008

UPDATED WIKI: NumbersTable

JonathanKehayias — Wed, 02 Jul 2008 18:14:01 GMT

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

A Numbers table is simply a table that holds a single integer column with each
positive integer number in the range from the number 1 to the maximum value
of an integer 2,147,483,647. (boldNote: When creating the table off the exact code
provided in this example below, your Numbers table will be roughly 8GB in size.
This is overkill in most cases and you should size the number of rows being create
according to your own personal needs.*) Creating a Numbers table is really simple to do
and it can be used to solve a broad number of problems.

Some Examples of how to use a Numbers Table are:

Identify Missing Dates
Get All Dates Between Two Dates
Identify Missing Numeric Values
Get All Numbers Between Two Values

These solutions apply to all versions of SQL Server.

Create Number Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create Sample Data using a Table Varable
SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e
 
-- Create a Primary Key and Clustered Index to control data order
ALTER TABLE dbo.Numbers ADD CONSTRAINT
PK_Numbers PRIMARY KEY CLUSTERED (N)

Return to Top

Identify Missing Dates In a Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Find the dates not in the table an in the first 14 days of January
SELECT DATEADD(dd, N, '01/01/2007') as MissingDates
FROM Numbers
WHERE n < 14
  AND DATEADD(dd, N, '01/01/2007') NOT IN (SELECT DateField FROM @TestTable)
 
 
/* Results
MissingDates
-----------------------
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-06 00:00:00.000
2007-01-11 00:00:00.000
2007-01-13 00:00:00.000
*/

Return to Top

Get All Dates Between Two Dates

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Date Range Values
DECLARE @StartDate datetime
DECLARE @EndDate datetime
 
SET @StartDate = '01/01/2007'
SET @EndDate = '01/07/2007'
 
-- Find the dates between these two dates
SELECT DATEADD(dd, N, @StartDate) as DatesBetween
FROM Numbers
WHERE n < (DATEDIFF(dd, @StartDate, @EndDate)+1)
 
 
/* Results
DatesBetween
-----------------------
2007-01-01 00:00:00.000
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-05 00:00:00.000
2007-01-06 00:00:00.000
2007-01-07 00:00:00.000
*/

Return to Top

Identify Missing Numeric Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Delete Odd Numbered Rows From Table
DELETE @TestTable WHERE RowID IN (1,3,5,7)
 
-- Find the missing row Numbers
SELECT N as MissingRows
FROM Numbers
WHERE N > 0 -- RowID's are greater than 0
  AND N <= (SELECT MAX(RowID) FROM @TestTable) -- Constrain to valid RowID's
  AND N NOT IN (SELECT RowID FROM @TestTable) -- RowID not in the table
 
 
/* Results
MissingRows
-----------
1
3
5
7
*/

Return to Top

Get All Numbers Between Two Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Number Range Values
DECLARE @Start int
DECLARE @End int
 
SET @Start = 14
SET @End = 22
 
-- Find the numbers between these two numbers
SELECT N as NumbersBetween
FROM Numbers
WHERE N BETWEEN @Start AND @End
 
 
/* Results
NumbersBetween
--------------
14
15
16
17
18
19
20
21
22
*/

Return to Top

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Apr 15, 2008

UPDATED WIKI: NumbersTable

JonathanKehayias — Wed, 02 Jul 2008 18:13:43 GMT

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

A Numbers table is simply a table that holds a single integer column with each
positive integer number in the range from the number 1 to the maximum value
of an integer 2,147,483,647. (*Note: When creating the table off the exact code
provided in this example below, your Numbers table will be roughly 8GB in size.
This is overkill in most cases and you should size the number of rows being create
according to your own personal needs.*) Creating a Numbers table is really simple to do
and it can be used to solve a broad number of problems.

Some Examples of how to use a Numbers Table are:

Identify Missing Dates
Get All Dates Between Two Dates
Identify Missing Numeric Values
Get All Numbers Between Two Values

These solutions apply to all versions of SQL Server.

Create Number Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create Sample Data using a Table Varable
SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e
 
-- Create a Primary Key and Clustered Index to control data order
ALTER TABLE dbo.Numbers ADD CONSTRAINT
PK_Numbers PRIMARY KEY CLUSTERED (N)

Return to Top

Identify Missing Dates In a Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Find the dates not in the table an in the first 14 days of January
SELECT DATEADD(dd, N, '01/01/2007') as MissingDates
FROM Numbers
WHERE n < 14
  AND DATEADD(dd, N, '01/01/2007') NOT IN (SELECT DateField FROM @TestTable)
 
 
/* Results
MissingDates
-----------------------
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-06 00:00:00.000
2007-01-11 00:00:00.000
2007-01-13 00:00:00.000
*/

Return to Top

Get All Dates Between Two Dates

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Date Range Values
DECLARE @StartDate datetime
DECLARE @EndDate datetime
 
SET @StartDate = '01/01/2007'
SET @EndDate = '01/07/2007'
 
-- Find the dates between these two dates
SELECT DATEADD(dd, N, @StartDate) as DatesBetween
FROM Numbers
WHERE n < (DATEDIFF(dd, @StartDate, @EndDate)+1)
 
 
/* Results
DatesBetween
-----------------------
2007-01-01 00:00:00.000
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-05 00:00:00.000
2007-01-06 00:00:00.000
2007-01-07 00:00:00.000
*/

Return to Top

Identify Missing Numeric Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Delete Odd Numbered Rows From Table
DELETE @TestTable WHERE RowID IN (1,3,5,7)
 
-- Find the missing row Numbers
SELECT N as MissingRows
FROM Numbers
WHERE N > 0 -- RowID's are greater than 0
  AND N <= (SELECT MAX(RowID) FROM @TestTable) -- Constrain to valid RowID's
  AND N NOT IN (SELECT RowID FROM @TestTable) -- RowID not in the table
 
 
/* Results
MissingRows
-----------
1
3
5
7
*/

Return to Top

Get All Numbers Between Two Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Number Range Values
DECLARE @Start int
DECLARE @End int
 
SET @Start = 14
SET @End = 22
 
-- Find the numbers between these two numbers
SELECT N as NumbersBetween
FROM Numbers
WHERE N BETWEEN @Start AND @End
 
 
/* Results
NumbersBetween
--------------
14
15
16
17
18
19
20
21
22
*/

Return to Top

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Apr 15, 2008

UPDATED WIKI: NumbersTable

JonathanKehayias — Wed, 02 Jul 2008 18:12:55 GMT

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

A Numbers table is simply a table that holds a single integer column with each
positive integer number in the range from the number 1 to the maximum value
of an integer 2,147,483,647. *(Note: When creating the table off the exact code
provided in this example below, your Numbers table will be roughly 8GB in size.
This is overkill in most cases and you should size the number of rows being create
according to your own personal needs.)* Creating a Numbers table is really simple to do
and it can be used to solve a broad number of problems.

Some Examples of how to use a Numbers Table are:

Identify Missing Dates
Get All Dates Between Two Dates
Identify Missing Numeric Values
Get All Numbers Between Two Values

These solutions apply to all versions of SQL Server.

Create Number Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create Sample Data using a Table Varable
SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e
 
-- Create a Primary Key and Clustered Index to control data order
ALTER TABLE dbo.Numbers ADD CONSTRAINT
PK_Numbers PRIMARY KEY CLUSTERED (N)

Return to Top

Identify Missing Dates In a Table

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Find the dates not in the table an in the first 14 days of January
SELECT DATEADD(dd, N, '01/01/2007') as MissingDates
FROM Numbers
WHERE n < 14
  AND DATEADD(dd, N, '01/01/2007') NOT IN (SELECT DateField FROM @TestTable)
 
 
/* Results
MissingDates
-----------------------
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-06 00:00:00.000
2007-01-11 00:00:00.000
2007-01-13 00:00:00.000
*/

Return to Top

Get All Dates Between Two Dates

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Date Range Values
DECLARE @StartDate datetime
DECLARE @EndDate datetime
 
SET @StartDate = '01/01/2007'
SET @EndDate = '01/07/2007'
 
-- Find the dates between these two dates
SELECT DATEADD(dd, N, @StartDate) as DatesBetween
FROM Numbers
WHERE n < (DATEDIFF(dd, @StartDate, @EndDate)+1)
 
 
/* Results
DatesBetween
-----------------------
2007-01-01 00:00:00.000
2007-01-02 00:00:00.000
2007-01-03 00:00:00.000
2007-01-04 00:00:00.000
2007-01-05 00:00:00.000
2007-01-06 00:00:00.000
2007-01-07 00:00:00.000
*/

Return to Top

Identify Missing Numeric Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Create a table variable for the test data
DECLARE @TestTable TABLE
(RowID int identity primary key,
 DateField datetime)
 
-- Add some dates to the table
INSERT INTO @TestTable VALUES ('01/01/2007')
INSERT INTO @TestTable VALUES ('01/05/2007')
INSERT INTO @TestTable VALUES ('01/07/2007')
INSERT INTO @TestTable VALUES ('01/08/2007')
INSERT INTO @TestTable VALUES ('01/09/2007')
INSERT INTO @TestTable VALUES ('01/10/2007')
INSERT INTO @TestTable VALUES ('01/12/2007')
INSERT INTO @TestTable VALUES ('01/14/2007')
 
-- Delete Odd Numbered Rows From Table
DELETE @TestTable WHERE RowID IN (1,3,5,7)
 
-- Find the missing row Numbers
SELECT N as MissingRows
FROM Numbers
WHERE N > 0 -- RowID's are greater than 0
  AND N <= (SELECT MAX(RowID) FROM @TestTable) -- Constrain to valid RowID's
  AND N NOT IN (SELECT RowID FROM @TestTable) -- RowID not in the table
 
 
/* Results
MissingRows
-----------
1
3
5
7
*/

Return to Top

Get All Numbers Between Two Values

-- Suppress data loading messages
SET NOCOUNT ON
 
-- Set Number Range Values
DECLARE @Start int
DECLARE @End int
 
SET @Start = 14
SET @End = 22
 
-- Find the numbers between these two numbers
SELECT N as NumbersBetween
FROM Numbers
WHERE N BETWEEN @Start AND @End
 
 
/* Results
NumbersBetween
--------------
14
15
16
17
18
19
20
21
22
*/

Return to Top

___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, Apr 15, 2008

UPDATED WIKI: TestPage

ArnieRowland — Wed, 02 Jul 2008 18:03:05 GMT

Test Environment

Launch in another window

Contributors

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 16, 2008

UPDATED WIKI: TestPage

SuprotimAgarwal — Tue, 01 Jul 2008 18:22:28 GMT

Test Environment

You may create links here to your new Wiki page in order to work on it and keep it out of Public view until you are ready to make it 'public'.
     Then when you are ready to make your page 'Public', note the Wiki PageName, and use it for the Wiki link on the referring page.
     After your page is complete, and you have 'published' it, you may wish to remove the link from this page.

     WikiLink: [Words Seen by User|YourWikiPage]
___________________________________________________________________________________________________________________

Detaching A Database with SSMS - Video Tutorial

Find out all the primary key and foreign key constraints in a table - Suprotim Agarwal

Testing out analytics - Suprotim

Contributors

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 16, 2008

UPDATED WIKI: HowWiki

ArnieRowland — Fri, 27 Jun 2008 13:48:08 GMT

Ten Steps to Create Wiki Pages

All Wiki Pages link to a Parent page. The Wiki Entry page (SQLExamples) is the 'top level' parent page (or Resource Page).
Navigate to the Page that will contain the Link to the Wiki Page you wish to Create. While you are developing your page you will most likely use the [TestEnvironment] page for the Link. Place the Link on the [TestEnvironment] page. After you have completed your page and reviewed it, you may move the link to the [Home] page.
Then Click on the [Edit] button -located under the [Home] tab.
Designate the text that you will use as the 'hotlink' on the page -The 'hotlink' can be a new or existing Word(s) or Sentence.
Insert the Page Link in this Format [Words Seen by User|PageName]. You can designate multiple words to be seen by the user, then a 'pipe' symbol (Vertical Bar), then the WikiPage Name. All enclosed in square brackets. No Spaces in the WikiPage name, and the Wiki page will NOT be created at this point.
Preview the page to verify that it appears as you desire.
SAVE the page. Preview does NOT save the page. If you navigate away without saving, your changes will be lost.
After you have saved the page, it will display with your changes. Click on the designated 'hotlink'.
You will be taken to a 'PlaceHolder' page that informs you that the Wiki page does not exist -BUT will exist if you Click on the [Edit] button and create content. Here is a test -please DO NOT edit or save the NewPage. Click on [HowWiki] in the 'breadcrumb' to return here.
Click the [Edit] on the PlaceHolder page, Add your content, View in the Preview Window, when satisfied set the 'Parent Wiki Page' to 'Home', and click on the [Save] Button.

Your New Wiki Page is now 'on-line'.

There is complete Page Version control -for Saved Pages.
If you make a mistake, you can click on the [Page Info] tab,
Select and View the previous Pages Until you find the Page Version you want to restore,
Click on the [Edit] button for that Page Version, and
Then Click on the [Save] button. It then becomes the Current Page Version.

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 14, 2008

UPDATED WIKI: ListPrimaryForeignKeyInTable

SuprotimAgarwal — Thu, 26 Jun 2008 09:10:20 GMT

Find out all the primary key and foreign key constraints in a table

Suprotim Agarwal, May 6, 2008

SQL Server 2005 exposes meta data using either the Catalog or the InformationSchema Views. The question that arises is when to use what. The Catalog views help retrieve the meta data information on both the database and the server level in an efficient manner. The InformationSchema views have been quiet optimized in Sql Server 2005 and is also a good alternative to access the metadata. Another advantage of using this view is that it is SQL-92 compatible and will work with other databases that support SQL-92 standards.

In this article, we will see how to use the Information Schema views to find out the primary and foreign key constraints in a table.

SQL Server 2005 Solution

 
// To view the primary and foreign key constraints on a particular table in the AdventureWorks database
 
USE ADVENTUREWORKS
GO
 
SELECT 
   Table_Name as [TableName], 
   Column_Name as [ColumnName],
   Constraint_Name as [Constraint], 
   Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE Table_Name = 'Address'
ORDER BY 
   [TableName], 
   [ColumnName]
 
 
// To view the primary and foreign key constraints on all tables in the AdventureWorks database
 
USE ADVENTUREWORKS
GO
 
SELECT 
   Table_Name as [TableName], 
   Column_Name as [ColumnName],
   Constraint_Name as [Constraint], 
   Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ORDER BY 
   [TableName], 
   [ColumnName]

SQL Server 2008 Solution

 
// To view the primary and foreign key constraints on a particular table in the AdventureWorks database
 
USE ADVENTUREWORKS
GO
 
SELECT 
   Table_Name as [TableName], 
   Column_Name as [ColumnName],
   Constraint_Name as [Constraint], 
   Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE Table_Name = 'Address'
ORDER BY 
   [TableName], 
   [ColumnName]
 
 
// To view the primary and foreign key constraints on all tables in the AdventureWorks database
 
USE ADVENTUREWORKS
GO
 
SELECT 
   Table_Name as [TableName], 
   Column_Name as [ColumnName],
   Constraint_Name as [Constraint], 
   Table_Schema as [Schema]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
ORDER BY 
   [TableName], 
   [ColumnName]

_____________________________________________________________________________________________________________

Page Created By: Suprotim Agarwal, May 6, 2008

UPDATED WIKI: RecursiveCTE

SuprotimAgarwal — Thu, 26 Jun 2008 09:08:24 GMT

Exploring Recursive Common Table Expressions (CTE)

Suprotim Agarwal, June 26, 2008

A Common Table Expression(CTE) is an ANSI SQL-99 temporary result set that can be declared once and referenced multiple times in a query. It acts as a replacement for many sub-queries and thereby improves query simplicity and performance in most cases.

If you are new to CTE's, I would recommend you to check this link http://msdn.microsoft.com/en-us/library/ms190766.aspx before you proceed ahead with Recursive CTE's.

A recursive CTE is one in which a CTE refers to itself in the CTE definition to obtain subsets of data until the complete result set is obtained. Recursive CTE's come in quiet handy when you need to represent hierarchical data and query against it. Infact, the raw power of a CTE is realized when it is used recursively to obtain a resultset. In previous versions of SQL Server, you could create recursive queries using cursors and temporary tables.

The syntax of a recursive query would be similar to the following:

WITH cte-nm (column1, column2, ...)
AS
(
cte-query-definition -- Anchor member
UNION ALL
cte-query-definition –- Recursive member which references cte_name
)
SELECT * FROM cte-nm

In this sample, we will see how to use Recursive CTE's to find out the level of a team member in a project.

Case Scenario : Let us assume that we have a table called ProjectMemberDetails. We need to display the 'Level' of each TeamMember in that project. The level of each team member will be ascertained by the hierarchy of Managers above the person.

For the sake of demonstrating Recursive CTE's, we will create the following columns in the ProjectMemberDetails table - ID, TeamMemNm, Designation and ManagerID. Using Recursive CTE's, we will then calculate the level of each team member in the team. Level 1 is the highest grade, given only to those who do not have managers above them; in our case the EngagementManager.

The hierarchy of a project team for demonstratation purposes is as follows:

EngagementManager - 1
Sr. Project Manager - 2
Project Manager - 3
Project Leader - 4
Team Leader - 5
Sr. Developer - 6
Developer/Tester - 7

The code to display the hierarchy would be as follows:

SQL Server 2005/SQL Server 2008

DROP TABLE #ProjectMemberDetails
-- create temporary table called ProjectMemberDetails
CREATE TABLE #ProjectMemberDetails
(
ID int Identity(1,1) PRIMARY KEY,
TeamMemNm varchar(100),
Designation varchar(50),
ManagerID int NULL
)
 
-- Insert some sample records in the ProjectMemberDetails table
INSERT INTO #ProjectMemberDetails VALUES('Tim','Engagement Manager',NULL);
INSERT INTO #ProjectMemberDetails VALUES('Kathy','Sr. Project Manager',1);
INSERT INTO #ProjectMemberDetails VALUES('Levonca','Project Manager',2);
INSERT INTO #ProjectMemberDetails VALUES('Sid','Project Manager',2);
INSERT INTO #ProjectMemberDetails VALUES('Carrie','Project Leader',3);
INSERT INTO #ProjectMemberDetails VALUES('Cristina','Project Leader',4);
INSERT INTO #ProjectMemberDetails VALUES('Santi','Team Leader',5);
INSERT INTO #ProjectMemberDetails VALUES('Michelle','Team Leader',5);
INSERT INTO #ProjectMemberDetails VALUES('Pablo','Team Leader',6);
INSERT INTO #ProjectMemberDetails VALUES('Mario','Team Leader',6);
INSERT INTO #ProjectMemberDetails VALUES('Anand','Sr. Developer',7);
INSERT INTO #ProjectMemberDetails VALUES('Bill','Sr. Developer',7);
INSERT INTO #ProjectMemberDetails VALUES('Jack','Sr. Developer',8);
INSERT INTO #ProjectMemberDetails VALUES('Tibre','Sr. Developer',8);
INSERT INTO #ProjectMemberDetails VALUES('Lisa','Sr. Developer',9);
INSERT INTO #ProjectMemberDetails VALUES('Scott','Sr. Tester',10);
INSERT INTO #ProjectMemberDetails VALUES('Nancy','Developer',11);
INSERT INTO #ProjectMemberDetails VALUES('MJ','Developer',12);
INSERT INTO #ProjectMemberDetails VALUES('Ziak','Developer',13);
INSERT INTO #ProjectMemberDetails VALUES('Martin','Developer',14);
INSERT INTO #ProjectMemberDetails VALUES('David','Developer',14);
INSERT INTO #ProjectMemberDetails VALUES('Steve','Developer',15);
INSERT INTO #ProjectMemberDetails VALUES('Rachael','Developer',15);
INSERT INTO #ProjectMemberDetails VALUES('Sally','Tester',16);
INSERT INTO #ProjectMemberDetails VALUES('Peter','Tester',16);
SELECT * FROM #ProjectMemberDetails;
 
-- Use Recursive CTE to find out the Level of each ProjectMemberDetails
-- in the project
WITH CTE(ID, TeamMemberName, Designation, ManagerID, ProjectLevel)
AS
(
SELECT ID, TeamMemNm, Designation, ManagerID, 1 as ProjectLevel
From #ProjectMemberDetails WHERE ManagerID is NULL
UNION ALL
SELECT e.ID, e.TeamMemNm, e.Designation, e.ManagerID, c.ProjectLevel + 1
FROM #ProjectMemberDetails e
INNER JOIN CTE c
ON e.ManagerID = c.ID
)
SELECT ID, TeamMemberName, Designation, ManagerID, ProjectLevel
FROM CTE Order BY ID

Here's a break up of the query:

1. In the query below, the anchor member returns the person who is at the highest level in the project (Engagement Manager) and marks the level as 1

SELECT ID, TeamMemNm, Designation, ManagerID, 1 as ProjectLevel
From #ProjectMemberDetails WHERE ManagerID is NULL

2. To determine the person directly below him (EngagementManager), a join is made between the ProjectMemberDetails table and the CTE, which helps in determining the member which is directly below the person returned by the anchor member. In our case, that would be the Project Manager.

3. With every recursive invocation, we get the output (c.ProjectLevel + 1) for the join e.ManagerID = c.ID

The final result set consists of the union of all the subsets generated by the anchor and recursive members. The result set is displayed below.

Result

ID	TeamMemberName	Designation	ManagerID	ProjectLevel
1	Tim	Engagement Manager	NULL	1
2	Kathy	Sr. Project Manager	1	2
3	Levonca	Project Manager	2	3
4	Sid	Project Manager	2	3
5	Carrie	Project Leader	3	4
6	Cristina	Project Leader	4	4
7	Santi	Team Leader	5	5
8	Michelle	Team Leader	5	5
9	Pablo	Team Leader	6	5
10	Mario	Team Leader	6	5
11	Anand	Sr. Developer	7	6
12	Bill	Sr. Developer	7	6
13	Jack	Sr. Developer	8	6
14	Tibre	Sr. Developer	8	6
15	Lisa	Sr. Developer	9	6
16	Scott	Sr. Tester	10	6
17	Nancy	Developer	11	7
18	MJ	Developer	12	7
19	Ziak	Developer	13	7
20	Martin	Developer	14	7
21	David	Developer	14	7
22	Steve	Developer	15	7
23	Rachael	Developer	15	7
24	Sally	Tester	16	7
25	Peter	Tester	16	7

References: http://msdn.microsoft.com/en-us/library/ms186243.aspx

UPDATED WIKI: TestPage

SuprotimAgarwal — Thu, 26 Jun 2008 09:06:27 GMT

Test Environment

You may create links here to your new Wiki page in order to work on it and keep it out of Public view until you are ready to make it 'public'.
     Then when you are ready to make your page 'Public', note the Wiki PageName, and use it for the Wiki link on the referring page.
     After your page is complete, and you have 'published' it, you may wish to remove the link from this page.

     WikiLink: [Words Seen by User|YourWikiPage]
___________________________________________________________________________________________________________________

Detaching A Database with SSMS - Video Tutorial

Find out all the primary key and foreign key constraints in a table - Suprotim Agarwal

Contributors

___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 16, 2008

UPDATED WIKI: Home

SuprotimAgarwal — Thu, 26 Jun 2008 08:59:37 GMT

Common Solutions for T-SQL Problems

Information presented in this WIKI represents the suggestions, ideas, and opinions of Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums. (Unless specifically stated otherwise, nothing should be construed to represent the official positions or opinions of Microsoft and/or its Employees.)

Please direct any additional Questions to the MSDN SQL Server Related Forums
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
How can I Prepare My Question to Increase the Possibility of Getting a Good Solution?
What are the Rules of Conduct expected on the Forums?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

How To Examples of Selected T-SQL Programming Constructs

How To Troubleshoot and Debug T-SQL Code

Troubleshoot Deadlocking in SQL Server.
Troubleshoot Deadlocking in SQL Server 2005 using Profiler.
Using the SQL Server Profiler.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Writing Transact-SQL Statements Tutorial

http://msdn2.microsoft.com/en-us/library/ms365303.aspx
___________________________________________________________________________________________________________________
Page Created By: Arnie Rowland, Mar 12, 2008

UPDATED WIKI: RecursiveCTE

SuprotimAgarwal — Thu, 26 Jun 2008 08:57:17 GMT

Exploring Recursive Common Table Expressions (CTE)

DROP TABLE #ProjectMemberDetails
-- create temporary table called ProjectMemberDetails
CREATE TABLE #ProjectMemberDetails
(
ID int Identity(1,1) PRIMARY KEY,
TeamMemNm varchar(100),
Designation varchar(50),
ManagerID int NULL
)
 
-- Insert some sample records in the ProjectMemberDetails table
INSERT INTO #ProjectMemberDetails VALUES('Tim','Engagement Manager',NULL);
INSERT INTO #ProjectMemberDetails VALUES('Kathy','Sr. Project Manager',1);
INSERT INTO #ProjectMemberDetails VALUES('Levonca','Project Manager',2);
INSERT INTO #ProjectMemberDetails VALUES('Sid','Project Manager',2);
INSERT INTO #ProjectMemberDetails VALUES('Carrie','Project Leader',3);
INSERT INTO #ProjectMemberDetails VALUES('Cristina','Project Leader',4);
INSERT INTO #ProjectMemberDetails VALUES('Santi','Team Leader',5);
INSERT INTO #ProjectMemberDetails VALUES('Michelle','Team Leader',5);
INSERT INTO #ProjectMemberDetails VALUES('Pablo','Team Leader',6);
INSERT INTO #ProjectMemberDetails VALUES('Mario','Team Leader',6);
INSERT INTO #ProjectMemberDetails VALUES('Anand','Sr. Developer',7);
INSERT INTO #ProjectMemberDetails VALUES('Bill','Sr. Developer',7);
INSERT INTO #ProjectMemberDetails VALUES('Jack','Sr. Developer',8);
INSERT INTO #ProjectMemberDetails VALUES('Tibre','Sr. Developer',8);
INSERT INTO #ProjectMemberDetails VALUES('Lisa','Sr. Developer',9);
INSERT INTO #ProjectMemberDetails VALUES('Scott','Sr. Tester',10);
INSERT INTO #ProjectMemberDetails VALUES('Nancy','Developer',11);
INSERT INTO #ProjectMemberDetails VALUES('MJ','Developer',12);
INSERT INTO #ProjectMemberDetails VALUES('Ziak','Developer',13);
INSERT INTO #ProjectMemberDetails VALUES('Martin','Developer',14);
INSERT INTO #ProjectMemberDetails VALUES('David','Developer',14);
INSERT INTO #ProjectMemberDetails VALUES('Steve','Developer',15);
INSERT INTO #ProjectMemberDetails VALUES('Rachael','Developer',15);
INSERT INTO #ProjectMemberDetails VALUES('Sally','Tester',16);
INSERT INTO #ProjectMemberDetails VALUES('Peter','Tester',16);
SELECT * FROM #ProjectMemberDetails;
 
-- Use Recursive CTE to find out the Level of each ProjectMemberDetails
-- in the project
WITH CTE(ID, TeamMemberName, Designation, ManagerID, ProjectLevel)
AS
(
SELECT ID, TeamMemNm, Designation, ManagerID, 1 as ProjectLevel
From #ProjectMemberDetails WHERE ManagerID is NULL
UNION ALL
SELECT e.ID, e.TeamMemNm, e.Designation, e.ManagerID, c.ProjectLevel + 1
FROM #ProjectMemberDetails e
INNER JOIN CTE c
ON e.ManagerID = c.ID
)
SELECT ID, TeamMemberName, Designation, ManagerID, ProjectLevel
FROM CTE Order BY ID

ID	TeamMemberName	Designation	ManagerID	ProjectLevel
1	Tim	Engagement Manager	NULL	1
2	Kathy	Sr. Project Manager	1	2
3	Levonca	Project Manager	2	3
4	Sid	Project Manager	2	3
5	Carrie	Project Leader	3	4
6	Cristina	Project Leader	4	4
7	Santi	Team Leader	5	5
8	Michelle	Team Leader	5	5
9	Pablo	Team Leader	6	5
10	Mario	Team Leader	6	5
11	Anand	Sr. Developer	7	6
12	Bill	Sr. Developer	7	6
13	Jack	Sr. Developer	8	6
14	Tibre	Sr. Developer	8	6
15	Lisa	Sr. Developer	9	6
16	Scott	Sr. Tester	10	6
17	Nancy	Developer	11	7
18	MJ	Developer	12	7
19	Ziak	Developer	13	7
20	Martin	Developer	14	7
21	David	Developer	14	7
22	Steve	Developer	15	7
23	Rachael	Developer	15	7
24	Sally	Tester	16	7
25	Peter	Tester	16	7

References: http://msdn.microsoft.com/en-us/library/ms186243.aspx

UPDATED WIKI: RecursiveCTE

SuprotimAgarwal — Thu, 26 Jun 2008 08:55:46 GMT

Exploring Recursive Common Table Expressions (CTE)

DROP TABLE #ProjectMemberDetails
-- create temporary table called ProjectMemberDetails
CREATE TABLE #ProjectMemberDetails
(
ID int Identity(1,1) PRIMARY KEY,
TeamMemNm varchar(100),
Designation varchar(50),
ManagerID int NULL
)
 
-- Insert some sample records in the ProjectMemberDetails table
INSERT INTO #ProjectMemberDetails VALUES('Tim','Engagement Manager',NULL);
INSERT INTO #ProjectMemberDetails VALUES('Kathy','Sr. Project Manager',1);
INSERT INTO #ProjectMemberDetails VALUES('Levonca','Project Manager',2);
INSERT INTO #ProjectMemberDetails VALUES('Sid','Project Manager',2);
INSERT INTO #ProjectMemberDetails VALUES('Carrie','Project Leader',3);
INSERT INTO #ProjectMemberDetails VALUES('Cristina','Project Leader',4);
INSERT INTO #ProjectMemberDetails VALUES('Santi','Team Leader',5);
INSERT INTO #ProjectMemberDetails VALUES('Michelle','Team Leader',5);
INSERT INTO #ProjectMemberDetails VALUES('Pablo','Team Leader',6);
INSERT INTO #ProjectMemberDetails VALUES('Mario','Team Leader',6);
INSERT INTO #ProjectMemberDetails VALUES('Anand','Sr. Developer',7);
INSERT INTO #ProjectMemberDetails VALUES('Bill','Sr. Developer',7);
INSERT INTO #ProjectMemberDetails VALUES('Jack','Sr. Developer',8);
INSERT INTO #ProjectMemberDetails VALUES('Tibre','Sr. Developer',8);
INSERT INTO #ProjectMemberDetails VALUES('Lisa','Sr. Developer',9);
INSERT INTO #ProjectMemberDetails VALUES('Scott','Sr. Tester',10);
INSERT INTO #ProjectMemberDetails VALUES('Nancy','Developer',11);
INSERT INTO #ProjectMemberDetails VALUES('MJ','Developer',12);
INSERT INTO #ProjectMemberDetails VALUES('Ziak','Developer',13);
INSERT INTO #ProjectMemberDetails VALUES('Martin','Developer',14);
INSERT INTO #ProjectMemberDetails VALUES('David','Developer',14);
INSERT INTO #ProjectMemberDetails VALUES('Steve','Developer',15);
INSERT INTO #ProjectMemberDetails VALUES('Rachael','Developer',15);
INSERT INTO #ProjectMemberDetails VALUES('Sally','Tester',16);
INSERT INTO #ProjectMemberDetails VALUES('Peter','Tester',16);
SELECT * FROM #ProjectMemberDetails;
 
-- Use Recursive CTE to find out the Level of each ProjectMemberDetails
-- in the project
WITH CTE(ID, TeamMemberName, Designation, ManagerID, ProjectLevel)
AS
(
SELECT ID, TeamMemNm, Designation, ManagerID, 1 as ProjectLevel
From #ProjectMemberDetails WHERE ManagerID is NULL
UNION ALL
SELECT e.ID, e.TeamMemNm, e.Designation, e.ManagerID, c.ProjectLevel + 1
FROM #ProjectMemberDetails e
INNER JOIN CTE c
ON e.ManagerID = c.ID
)
SELECT ID, TeamMemberName, Designation, ManagerID, ProjectLevel
FROM CTE Order BY ID

Here's a break up of the query:

1. In the query below, the anchor member returns the person who is at the highest level in the project (Engagement Manager) and marks the level as 1

SELECT ID, TeamMemNm, Designation, ManagerID, 1 as ProjectLevel
From #ProjectMemberDetails WHERE ManagerID is NULL

2. To determine the person directly below him (EngagementManager), a join is made between the ProjectMember table and the CTE, which helps in determining the member which is directly below the person returned by the anchor member. In our case, that would be the Project Manager.

3. With every recursive invocation, we get the output (c.ProjectLevel + 1) for the join e.ManagerID = c.ID

The final result set consists of the union of all the subsets generated by the anchor and recursive members. The result set is displayed below.

Result

ID	TeamMemberName	Designation	ManagerID	ProjectLevel
1	Tim	Engagement Manager	NULL	1
2	Kathy	Sr. Project Manager	1	2
3	Levonca	Project Manager	2	3
4	Sid	Project Manager	2	3
5	Carrie	Project Leader	3	4
6	Cristina	Project Leader	4	4
7	Santi	Team Leader	5	5
8	Michelle	Team Leader	5	5
9	Pablo	Team Leader	6	5
10	Mario	Team Leader	6	5
11	Anand	Sr. Developer	7	6
12	Bill	Sr. Developer	7	6
13	Jack	Sr. Developer	8	6
14	Tibre	Sr. Developer	8	6
15	Lisa	Sr. Developer	9	6
16	Scott	Sr. Tester	10	6
17	Nancy	Developer	11	7
18	MJ	Developer	12	7
19	Ziak	Developer	13	7
20	Martin	Developer	14	7
21	David	Developer	14	7
22	Steve	Developer	15	7
23	Rachael	Developer	15	7
24	Sally	Tester	16	7
25	Peter	Tester	16	7

References: http://msdn.microsoft.com/en-us/library/ms186243.aspx

Common Solutions for T-SQL Problems

UPDATED WIKI: ExpressMaintenance

How to Automate Maintenace Tasks with SQL Server Express

Jonathan Kehayias, May 5, 2008

Check Database Integrity

Shrink Database

Perform Index Maintenance

History Cleanup

Maintenance Cleanup Task

Other Published Solutions

Automating Database maintenance in SQL 2005 Express Edition Part I

Automating Database maintenance in SQL 2005 Express Edition Part II

Backup of system and user databases

Integrity check of system and user databases

Index Optimization of user databases

For additional information please see:

Books Online References

DBCC

DBCC CHECKDB

DBCC SHRINKDATABASE

ALTER INDEX

Related Whitepapers and Documents

Reorganizing and Rebuilding Indexes

SQL Database Maintenance for Sharepoint Whitepaper

UPDATED WIKI: TestPage

Test Environment

UPDATED WIKI: NumbersTable

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

Create Number Table

Identify Missing Dates In a Table

Get All Dates Between Two Dates

Identify Missing Numeric Values

Get All Numbers Between Two Values

UPDATED WIKI: TestPage

Test Environment

UPDATED WIKI: NumbersTable

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

Create Number Table

Identify Missing Dates In a Table

Get All Dates Between Two Dates

Identify Missing Numeric Values

Get All Numbers Between Two Values

UPDATED WIKI: NumbersTable

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

Create Number Table

Identify Missing Dates In a Table

Get All Dates Between Two Dates

Identify Missing Numeric Values

Get All Numbers Between Two Values

UPDATED WIKI: NumbersTable

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

Create Number Table

Identify Missing Dates In a Table

Get All Dates Between Two Dates

Identify Missing Numeric Values

Get All Numbers Between Two Values

UPDATED WIKI: NumbersTable

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

Create Number Table

Identify Missing Dates In a Table

Get All Dates Between Two Dates

Identify Missing Numeric Values

Get All Numbers Between Two Values

UPDATED WIKI: NumbersTable

How to Create and Use A Numbers Table

Jonathan Kehayias, April 15, 2008

Create Number Table

Identify Missing Dates In a Table

Get All Dates Between Two Dates

Identify Missing Numeric Values

Get All Numbers Between Two Values

UPDATED WIKI: TestPage

Test Environment

UPDATED WIKI: TestPage

Test Environment