For anyone reading this thread in future that would prefer range results (as below) here's another method.

customer product from_date ...

alicia-694427 (4/28/2010)

---

Thanks, unfortunately the tables that I am searching are very large and some of the columns have lengthy text so creating the fulltext index on the fly takes a...

USE tempdb;

GO

-- *Inline* table-valued function

-- Deterministic, system verified, not precise

GO

CREATE FUNCTION dbo.NaturalRound

(

@Value DOUBLE...

DECLARE @DatabaseName SYSNAME;

SET @DatabaseName = QUOTENAME(N'AdventureWorks');

DECLARE @data

TABLE (

table_name SYSNAME PRIMARY KEY,

...

Another option is to use the OUTPUT clause:

SET XACT_ABORT OFF;

DECLARE @test-2

TABLE (

row_id INTEGER IDENTITY PRIMARY KEY,

...

USE tempdb;

GO

CREATE TABLE dbo.Class

(

ClassID INTEGER...

SELECT name

FROM sys.types;

You changed rather more than that!

"Forgot" :laugh: must remember that one. Funny.

This doesn't really add much value to Peter's version, but just for my own amusement:

DECLARE @MissingNumbers

TABLE (n INTEGER NOT NULL);

INSERT @MissingNumbers (n)

VALUES (1),(2),(4),(5),(7),(8),(11),(12),(13),(15),(17),(19),(20);

SELECT N.n

FROM...

Lots of alternatives here (triggers, stored procedures...).

Here's one of the more interesting ones:

USE tempdb;

GO

CREATE TABLE [dbo].[TALK]

(

...

DECLARE @list VARCHAR(50) = '1,2,4,5,7,8,11,12,13,15,17,19,20',

@Delimiter CHAR(1) = ',';

SELECT Numbers.n

FROM dbo.Numbers(REVERSE(LEFT(REVERSE(@list), CHARINDEX(@Delimiter, REVERSE(@list)) - 1))) Numbers

WHERE CHARINDEX(@Delimiter...

kinzent (4/28/2010)

---

You say "The fact is that data is neither stored nor retreived in precise order" , but when there is a clustered index on one column,will the data on...

SwePeso (4/28/2010)

---

Paul, what...

For giggles, here's an extension to my previous code that supports sequnces up to 146,813,779,479,510 (way beyond INT32) - which gives ZZZZZZZZZZ (ten of them!)

SELECT N.n,

...

Hugo Kornelis (4/28/2010)

---

What I dislike about the question is:

* The date format. Not all locale settings recognise "Dec" as december. (Try adding "SET LANGUAGE German;" as the first line -...