February 24, 2010 at 9:13 am
Comments posted to this topic are about the item Defensive Database Programming
December 4, 2013 at 7:44 am
I noticed the examples in the book for creating a Numbers table. Of course they all work fine! I would like to contribute a variant that I happen to like. It depends on the (undocumented but heavily used) table master.dbo.spt_values, though any sufficiently-large table will do:
SELECT TOP <quantity desired> IDENTITY(int, 0,1) AS n
INTO #Numbers
FROM master.dbo.spt_values v1, master.dbo.spt_values v2;
ALTER TABLE #Numbers ADD PRIMARY KEY CLUSTERED (n);
SELECT COUNT(*) from #Numbers;
This gave me 5503716 natural numbers to use on my SQL Server 2005 instance and 6290064 on my SQL Server 2008 R2 instance. I believe that it is also much faster than the while-loop approach. Since the table is populated using a cartesian product, one can rapidly build tables of any size, subject to disk space limitations of course. You can fill in <quantity desired> with the upper limit of the range you need.
August 5, 2014 at 8:48 am
I was reading Chapter 1 of your interesting book and was a bit puzzled by the solution to the Defending Against Cases of Unintended Use example. If you do not want people to use special SQL characters in the search string, why not just recode your stored procedure as a basic string comparison, like:
CREATE PROCEDURE dbo.SelectMessagesBySubjectBeginning_v2
@SubjectBeginning VARCHAR(30)
AS
SET NOCOUNT ON;
SELECT Subject, Body
FROM dbo.Messages WHERE LEFT(Subject, LEN(@SubjectBeginning)) = @SubjectBeginning;
which removes the problems with LIKE and works with searches on '[OT]' or '50%' which was one of the problem examples given?
When it comes to parameters in SQL or XSLT, the more strongly-typed the better, I would have thought. Primitive strings should be primitive strings without magical symbols.
If you wanted to provide variations on "starts-with" or "contains" or "all these words" or "exact phrase" or even "synonyms" then you could provide separate parameters. I am probably biased against wildcard searches, mind you, perhaps as they are associated in my mind with people messing about with non-atomic identifiers. 🙂
April 6, 2015 at 11:34 am
gbritton1 (12/4/2013)
I noticed the examples in the book for creating a Numbers table. Of course they all work fine! I would like to contribute a variant that I happen to like. It depends on the (undocumented but heavily used) table master.dbo.spt_values, though any sufficiently-large table will do:
SELECT TOP <quantity desired> IDENTITY(int, 0,1) AS n
INTO #Numbers
FROM master.dbo.spt_values v1, master.dbo.spt_values v2;
ALTER TABLE #Numbers ADD PRIMARY KEY CLUSTERED (n);
SELECT COUNT(*) from #Numbers;
This gave me 5503716 natural numbers to use on my SQL Server 2005 instance and 6290064 on my SQL Server 2008 R2 instance. I believe that it is also much faster than the while-loop approach. Since the table is populated using a cartesian product, one can rapidly build tables of any size, subject to disk space limitations of course. You can fill in <quantity desired> with the upper limit of the range you need.
Indeed, this variant is faster than the one in the book. However, I would not use it for two reasons:
1. This method is based on system tables (undocumented spt_values). I also saw a variation of this method using sys.objects. I would not base my solution on anything which does not belong to my application and over which I do not have full control. For example: the content of these sys tables may change, the tables may not be valid anymore in new version of SQL, etc.
2. This method is still slow. It creates and populates #Numbers table very fast, but then it spends a lot of time to add PRIMARY KEY. If you want to play with IDENTITY, I would recommend the following approach:
CREATE TABLE Numbers (Number int identity(1,1) NOT NULL PRIMARY KEY, T bit NULL);
WITH
T1(T) AS (SELECT T FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(T)) --10 rows
,T2(T) AS (SELECT A.T FROM T1 AS A CROSS JOIN T1 AS B CROSS JOIN T1 AS C) --1,000 rows
,T3(T) AS (SELECT A.T FROM T2 AS A CROSS JOIN T2 AS B CROSS JOIN T2 AS C) --1,000,000,000 rows
INSERT INTO dbo.Numbers(T)
SELECT TOP <quantity desired> NULL
FROM T3;
ALTER TABLE Numbers
DROP COLUMN T;
However, if you really want to see the fastest method, here is the winner (according to my tests):
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
DECLARE @RowsToCreate int = <quantity desired>;
--Code from a post by Itzik Ben-Gan
WITH
L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
L5 AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
Tally AS(SELECT ROW_NUMBER() OVER(ORDER BY C) AS Number FROM L5)
INSERT INTO Numbers (Number)
SELECT Number
FROM Tally
WHERE Number <= @RowsToCreate;
If you want to see more methods please check out this link: What is the best way to create and populate a numbers table?
August 4, 2016 at 8:04 pm
The ePub link provided appears to be dead.
Tom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply