## About generation of number sequences in SQL Server page 1 |
||

Author: Bezhaev A.Yu. In the next realization of MS A database management system (DBMS) by Microsoft Corporation. SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server 2005 the new possibility to use recursive CTE-constructions had appeared. The CTE (common table expression) allows to determine a table in framework of a query for multiple referencings.CTE allows to realize cycles for generation of number sequences and iterative calculations. The introduction to recursion in MS SQL Server may be founded in Microsoft`s corresponding manuals, this book and in the Internet. In this paragraph we`ll consider only new examples, useful in the practical mastering. The simplest example of using recursive CTE is generation limited number of natural sequence: 1,2,3, ... N.
This construction is meant for generation of natural sequences as one-column table with values from 1 to 100. ## A. Iteration calculationsIn the elementary and the high mathematics there are more interesting sequences with notable features. Some sequences converge and may be used for realization of calculate algorithms or for calculation of algebraic and transcendent numbers, values of trigonometric functions, for finding equation`s roots, solving linear equation systems and others. Other sequences, such as factorial, Binomial theorem and Fibonacci numbers are divergent sequences which have wide application in the probability theory and the mathematical statistics. These sequences are made by iterations (recursions in SQL Server), for instance: Here, A1, A2, A3 — start values for iteration process, fun — is a function for calculation 4th, 5th numbers etc, it always uses three previous numbers. Let`s suppose process starts with three equal numbers A1 = A2 = A3 = 1. Then the realization`s schema with using recursive CTE assumes such view:
Here [iter] column is using for iteration`s number output, [A1] column contains first fifty members of sequence and [A2], [A3], [A4] columns are auxiliary and contain intermediate results. The generalization of this method is in this example. Let n >= 1, m >= 1. Then sequent calculations A[n+1] = fun(A[1], A[2], …, A[n]) A[n+2] = fun(A[2], A[3], …, A[n+1]) … A[n+m] = fun(A[m], A[m+1], …, A[m+n-1]) lead to generation of m new members of sequence A[n+1],…,A[n+m]. We aren`t going to make an example of using recursion in general, because it`s possible only in pseudocode perhaps. You can try to make it for your own. Note that the resulting table has such look: A[1] A[2] … A[n] A[n+1] |

aggregate functions
Airport
ALL
AND
AS keyword
ASCII
AVG
Battles
Bezhaev
Bismarck
C.J.Date
calculated columns
Cartesian product
CASE
cast
CHAR
CHARINDEX
Chebykin
check constraint
classes
COALESCE
common table expressions
comparison predicates
Computer firm
CONSTRAINT
CONVERT
correlated subqueries
COUNT
CROSS APPLY
CTE
data type conversion
data types
database schema
date/time functions
DATEADD
DATEDIFF
DATENAME
DATEPART
DATETIME
DDL
DEFAULT
DEFAULT VALUES
DELETE
DISTINCT
DML
duplicates
equi-join
EXCEPT
exercise (-2)
exercise 19
More tags

exercise 23
exercise 32
exercise 37
exercise 39
exercise 46
exercise 54
exercise 55
exercise 56
exercise 57
exercise 7
exercise 70
exercise 8
exercises
EXISTS
FLOAT
FOREIGN KEY
FROM
FULL JOIN
GROUP BY
grouping
Guadalcanal
HAVING
head ships
IDENTITY
IN
income
inner join
INSERT
INTERSECT
IS NOT NULL
IS NULL
ISNULL
join operations
laptop
launched year
LEFT
LEFT OUTER JOIN
LEN
LIKE
LTRIM
MAX
MIN
mistakes
MySQL
NOT
NOT IN
NULL
NULLIF
number sequences
number-sequence generation
numbering
ON DELETE CASCADE
OR
Oracle
ORDER BY
outcome
Outcomes
outer joins
OVER
paging
Painting
PARTITION BY
Pass_in_trip
PATINDEX
PC
PIVOT
PostgreSQL
predicates
primary key
printer
Product
Ranking functions
recursive CTE
renaming columns
REPLACE
RIGHT
RIGHT JOIN
ROUND
rounding
ROW_NUMBER
ships
sorting
SQL Server
SQL Server 2012
SQL-92
sql-ex.ru
string functions
subquery
SUBSTRING
SUM
tables join
tips and solutions
Torus
Transact-SQL
Trip
TRUNCATE TABLE
type conversion
UNION
UNION ALL
UNKNOWN
UPDATE
varchar
WHERE
window functions
WITH
XML
XPath
XQuery

The book was updated

*month ago*