Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Defensive Database Programming


Defensive Database Programming

Author
Message
Alexander Kuznetsov
Alexander Kuznetsov
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 824
Comments posted to this topic are about the item Defensive Database Programming
gbritton1
gbritton1
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 850
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.
Tavis Reddick
Tavis Reddick
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 191
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. :-)
alexey_vip
alexey_vip
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 13
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?
TomThomson
TomThomson
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11503 Visits: 12092
The ePub link provided appears to be dead.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search