{"id":1184,"date":"2011-08-05T00:00:00","date_gmt":"2011-08-05T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-programmers-workshop\/"},"modified":"2021-09-29T16:21:50","modified_gmt":"2021-09-29T16:21:50","slug":"sql-programmers-workshop","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-programmers-workshop\/","title":{"rendered":"SQL Programmer’s workshop"},"content":{"rendered":"
\n

In this article, I’ll be taking you through the practical steps of creating a SQL Server routine, based on the stages I described in my last article ‘How to write SQL code’<\/a>. I don’t make a claim that this is a standard method of doing it, since I’ve never come across one. All I can say is that it works for me. It will describe a simple routine purely to emphasize the advantages of a structured approach whatever the size of the project, and to make the size of this article manageable.<\/p>\n

For this exercise, we want to design and build \u00a0a new routine that searches any text-based column that you specify \u00a0in any table for instances of a string. It returns all the ‘hits’ or matches in their context in order to make it easy to scan content. \u00a0We want to do this in order to quickly find all occurrences of a string in the chunk of text in which it occurs,\u00a0 in any column of any table.\u00a0 I’m going to try to make it generally useful, but I’ve been asked to \u00a0provide it as a tool for an actual production use as part of an admin process for monitoring website content.<\/p>\n

1\/ Up-front time estimation and planning<\/h2>\n

I’ve only got six hours I can spend, so I’ll have to cut corners. At this stage, I’ve only a vague idea of what is wanted and a slight feeling of apprehension, since the result isn’t a subset of the data set. A single tuple could furnish us with several ‘hits’ or matches, and there is no way of telling at this stage whether we need to use \u00a0a wildcard search. I’ll do a quick Gantt chart to give me a rough idea of progress.<\/p>\n

\"1336-img2D.jpg\"<\/p>\n

Obviously, this didn’t take very long at all since I have a template ready-made. (I use SmartDraw)<\/p>\n

So what do we really want in the results? \u00a0The amount of data in the system suggests to me that a simple wildcard search will be sufficient. I like creating ‘inversions’ for doing fancy searches such as proximity searches and ‘google-style’ searches, but this, I reckon, isn’t going to be one of those times. I’ll need to test that assumption \u00a0too!<\/p>\n

Another worry is how to go about making this generic, so I’ll aim for a barebones design and put in extra niceties if I have some time spare after getting the basic system working. In other words, I’m ‘time-boxing’.<\/p>\n

2\/ Produce the test data, and the automated tests<\/h2>\n

Before we do anything else, we need some simple data for component-testing. In this case, we can grab some sentences from my first article on ‘How to write T-SQL Code’ and make them into a quick\u00a0 table for testing the various parts of the routine, and we’ll \u00a0add a few ‘edge’ cases just to catch the obvious errors. This will do for initial component testing and we can keep it handy if we need to alter anything. \u00a0We can even store it for ‘Assertion-testing’ . We’ll tuck it into a View since that is easier to maintain, but normally we’d probably just use the statement directly to produce the ‘table’<\/p>\n

CREATE VIEW testSentences\r\n\/* this view is specifically designed for component, and assertion testing of string routines and inline code. Alter any of this and something will fail an assertion test. *\/\r\n\u00a0as\r\nSelect 1 AS TheKey,'With the data and the dummy routine, you can test to make sure it fails all your automated component tests.' AS TheSentence\r\nUNION ALL SELECT 2,'If it passes, you're probably in trouble.'\r\nUNION ALL SELECT 3,'Produce a Development harness.'\r\nUNION ALL SELECT 4,'In the course of developing T-SQL code, you will occasionally want to know the values in variables, temporary tables and other awkward places.' \r\nUNION ALL SELECT 5,'If you have all the time you want, and like developing at about the speed that small mammals evolve, then use an IDE.' \r\nUNION ALL SELECT 6,'Otherwise, develop techniques that basically use a temporary log and our own debug code.'\r\nUNION ALL SELECT 7,'The simple version of this used to be called 'PrintEffing' (after printf), but here we ''print'' to a simple log that records the time automatically: From then on simple SQL gives you timings for the various components of your routine.'\r\nUNION ALL SELECT 8,'You'll also want to know about inefficient code.'\r\nUNION ALL SELECT 9,'At this stage, you shouldn't rely on the profiler, Time statistics, the execution plan or extended events to test the performance of code.'\r\nUNION ALL SELECT 10,'You need something far more rapid and reliable.'\r\nUNION ALL SELECT 11,' I like to use two different types of harnesses, a development harness and a 'verbose' harness to test a stored procedure in actual use.' \r\nUNION ALL SELECT 12,'A development-harness is the SQL equivalent of scaffolding, to show how long all the various parts of a long routine are taking. Developers tend not to agree on how to monitor the performance of routines under different data-sizes.' \r\nUNION ALL SELECT 13,'I like to know how long a routine took, often every time it is called, and what the parameters were. I also like to be able to pick two points within a routine and know how long it took to run.'\r\nUNION ALL SELECT 14,'I like the information immediately, so one can rapidly try out different techniques.'\r\nUNION ALL SELECT 15,' I also occasionally like to graph results in Excel.'\r\nUNION ALL SELECT 16,' There are subtleties, of course.'\r\nUNION ALL SELECT 17,'Do you clear cache before you do a test-run?,Do you force a compilation?'\r\nUNION ALL SELECT 18,'It is as well to have the T-SQL to hand to twiddle these knobs, commented out when you don't want them.'\r\nUNION ALL SELECT 19,'At this point, I check the database's strategies for audit, error-reporting, and performance monitoring, and comply with them in the harness, as it makes sense to design development and production harnesses together, and an audit obligation could affect performance anyway.'\r\nUNION ALL SELECT 20,'So you''ll need TO test, test, test again'\r\nUNION ALL SELECT 21,'testing is quite fun anyway'\r\nUNION ALL SELECT 21,'to test is good'\r\nUNION ALL SELECT 22,'unless something fails a test'\r\n<\/pre>\n
Now, with a bit of head-scratching, and the use of a simple RegEx in the SSMS ‘find’ dialog-box, we work out that, if we stick rigidly to just displaying fifty characters, \u00a0the result we need is this, if we search on the word ‘Test’.<\/p>\n
ThePrimaryKey StartOfString context\r\n------------- ------------- --------------------------------------------------\r\n1\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a046\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...my routine, you can test to make sure it...\r\n1 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0 102\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...ails all your automated component tests.\r\n9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 110\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ... events to test the performance of code.\r\n11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...o test a stored procedure in actual use.\r\n17\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 36\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ... a test-run?,Do you force a compilation?\r\n20\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 19\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...So you'll need to test, test, test again\r\n20\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 25\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...So you'll need to test, test, test again\r\n20\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 31\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...So you'll need to test, test, test again\r\n21\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 testing is quite fun anyway\r\n21\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 to test is good\r\n22\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 26\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 unless something fails a test\r\n<\/pre>\n
We then construct a view that gives the correct result.<\/p>\n
CREATE VIVIEW TestSentenceCorrectResult as\r\nSELECT 1 AS PrimaryKey,46 AS startOfString, '...my routine, you can test to make sure it...' AS context\r\nUNION ALL SELECT 1,102, '...ails all your automated component tests.'\r\nUNION ALL SELECT 9,110, '... events to test the performance of code.'\r\nUNION ALL SELECT 11,99, '...o test a stored procedure in actual use.'\r\nUNION ALL SELECT 17,36, '... a test-run?,Do you force a compilation?'\r\nUNION ALL SELECT 20,19, '...So you''ll need TO test, test, test again'\r\nUNION ALL SELECT 20,25, '...So you''ll need TO test, test, test again'\r\nUNION ALL SELECT 20,31, '...So you''ll need TO test, test, test again'\r\nUNION ALL SELECT 21,1, 'testing is quite fun anyway'\r\nUNION ALL SELECT 21,4, 'to test is good'\r\nUNION ALL SELECT 22,26, 'unless something fails a test'\r\n<\/pre>\n
This was an interesting exercise; especially as it fleshed out the underlying rules of where to put the ellipsis, and how to get the context.\u00a0 At this stage I toyed with the idea of always starting and ending the context on a word boundary, but shied away from it for the time being.\u00a0 ‘Let’s do it later if there is time’. I decided. \u00a0So, component-testing this should be easy by just using our test data and comparing the result with what one would expect. We can do a simple ‘assertion test’ using the same method too. We’ll demonstrate how to make a simple test harness in a moment.<\/p>\n
The next thing we need to think about is performance and scalability testing. \u00a0Here we can get acres of text complete with nasty surprises from any book. Generally, I prefer \u00a0a million or so rows to test scalability with. It used to be a lot less before SQL Server 2000, but both hardware and software have improved so much that generating test\u00a0 runs has been more tricky. SQL Data Generator can cope well, but the text is just a bit too uniform, so a book it must be.<\/p>\n
\u00a0We can read in a whole book. Here is a routine that will take in a text-based book and read it into a global temporary table, a sentence on each line.<\/p>\n
Create PROCEDURE LoadBook\r\n\u00a0 @NameAndPathOnServer VARCHAR(255)\r\n\/**\r\nsummary:\u00a0\u00a0 >\r\nThis Procedure loads a file into a variable, chops it up into its constituent words and loads it into a global temporary table for subsequent analysis. This is based on code I published in 'The Parodist'\r\nAuthor: Phil Factor\r\nRevision: 1.0\r\ndate: 19 Jul 2011\r\nexample:\r\n\u00a0\u00a0\u00a0\u00a0 - - code: EXECUTE LoadBook 'D:\\files\\sherlockholmes.txt'\r\nreturns:\u00a0\u00a0 >\r\n0 if successful.\r\n**\/\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nAS\r\nSET NOCOUNT on\r\nDECLARE \r\n\u00a0\u00a0\u00a0 @LotsOfText VARCHAR(MAX),\r\n\u00a0\u00a0\u00a0 @Command NVARCHAR(MAX),\r\n\u00a0\u00a0\u00a0 @SentenceStart INT, \r\n\u00a0\u00a0\u00a0 @SentenceLength int, \r\n\u00a0\u00a0\u00a0 @LotsOfTextLength int;\r\n\u00a0\r\n\/* We want to read the text file in. Microsoft makes it very hard to use the OpenRowset Bulk with a supplied parameter as a local variable but are we at all discouraged? No Sir, we do a bit of sp_execute.*\/\u00a0\u00a0 \r\n\u00a0\r\nSELECT @Command = 'SELECT\u00a0 @Filecontents = BulkColumn\r\nFROM\u00a0\u00a0\u00a0\u00a0 OPENROWSET(BULK ''' + @NameAndPathOnServer + ''', SINGLE_BLOB) AS x'\r\nEXECUTE master..sp_executeSQL @Statment = @Command,\r\n\u00a0\u00a0\u00a0 @params = N'@FileContents VARCHAR(MAX) OUTPUT',\r\n\u00a0\u00a0\u00a0 @Filecontents = @LotsOfText OUTPUT;\r\n\/* Read each sentence into a table (we make it a global temporary table as we don't want to keep it for ever!*\/\r\nIF EXISTS (\r\n\u00a0\u00a0\u00a0 SELECT *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 tempDB.sys.tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 name LIKE N'##sentence' )\r\n\u00a0\u00a0\u00a0 DROP TABLE ##Sentence;\r\n\/* This gives each sentence in order *\/\r\nCREATE TABLE ##Sentence\r\n\u00a0 (SequenceNumber INT IDENTITY(1,1) PRIMARY KEY,\r\n\u00a0\u00a0 Sentence Varchar(max) NOT NULL);\r\n\/* now we put all the sentences from the file, in order, into this table *\/ \r\n\u00a0\r\nSELECT @LotsOfTextlength=LEN(@LotsOfText),@SentenceStart=0\r\nWHILE @SentenceStart<@LotsOfTextLength\r\n\u00a0 BEGIN\r\n\u00a0 SELECT @SentenceStart=@sentenceStart+patINDEX('%[^'+CHAR(0)+'- ]%', RIGHT(@LotsOfText, @LotsOfTextLength-@SentenceStart+1)+' Y. ')-1;\r\n\u00a0\r\n\u00a0 SELECT @SentenceLength=1+patINDEX('%[.?!]['+CHAR(0)+'- ]%', RIGHT(@LotsOfText, @LotsOfTextLength-@SentenceStart+1));\r\n\u00a0 INSERT INTO ##Sentence (sentence)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT SUBSTRING(@LotsOfText,@SentenceStart,@SentenceLength);\r\n\u00a0\u00a0 SELECT @SentenceStart=@SentenceStart+@SentenceLength;\r\n\u00a0 end\r\n\u00a0\r\n<\/pre>\n
With this in place, we probably have the necessary tools to create a simple test harness.<\/p>\n
We can simply run this batch code just to prove that, unless we write some code to provide the functionality, , then the test will fail. I know this sounds obvious but this sort of system catches some very silly common errors<\/p>\n
DECLARE @Result TABLE (\r\n\u00a0 ThePrimaryKey INT,StartOfString INT,Context VARCHAR( 50 )) \r\n-- do stuff here\r\n--or check that if the tables are the same it is all OK, by doing this\r\n---- INSERT INTO @result (ThePrimaryKey, StartOfString, context)\r\n--\u00a0\u00a0 SELECT PrimaryKey, StartOfString, context \r\n--\u00a0\u00a0\u00a0\u00a0\u00a0 FROM TestSentenceCorrectResult\r\n\u00a0\r\nSELECT\u00a0 * FROM @result t\r\n\u00a0\u00a0 FULL OUTER JOIN TestSentenceCorrectResult r\r\n\u00a0\u00a0\u00a0\u00a0 ON t.ThePrimaryKey = r.PrimaryKey\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND t.StartOfString = r.StartOfString\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND t.Context = r.Context ;\r\n\u00a0\r\nIF EXISTS ( SELECT * FROM @result t\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FULL OUTER JOIN TestSentenceCorrectResult r\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON t.ThePrimaryKey = r.PrimaryKey\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND t.StartOfString = r.StartOfString\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND t.Context = r.Context\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 t.Context IS NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR r.Context IS NULL )\r\n\u00a0 RAISERROR('The routine is giving the wrong result', 16,1) ;\r\n<\/pre>\n
What we have here is a simple test harness. We just add code into\u00a0 the part that has the comment ‘-do stuff here’ until you stop getting errors. Coding is really that easy!<\/p>\n
3\/ Produce the development harness.<\/h2>\n
This is going to be quick. We’ll simply use a minimal \u00a0harness like this. \u00a0To test it, we’ll just put in three arbitrary points in order to show that they took immeasurable time to execute.<\/p>\n
DECLARE @log TABLE (\r\n\u00a0 Log_Id INT IDENTITY(1, 1),TheeVent VARCHAR( 2000 ),\r\n\u00a0 DateAndTime DATETIME DEFAULT GetDate()) ;\r\n\u00a0\r\n\u00a0INSERT INTO @log (TheEvent) SELECT 'first point';\r\n\u00a0INSERT INTO @log (TheEvent) SELECT 'second point';\r\n\u00a0INSERT INTO @log (TheEvent) SELECT 'third point';\r\n\u00a0INSERT INTO @log (TheEvent) SELECT 'end';\r\n\u00a0\r\nSELECT\r\n\u00a0 TheStart.TheeVent + ' took ' \r\n\u00a0 + CAST( DatedIff( ms, TheStart.DateAndTime, Theend.DateAndTime ) AS VARCHAR( 10 ))\r\n\u00a0 + ' Ms.'\r\nFROM\u00a0\u00a0 @log TheStart\r\n\u00a0 INNER JOIN @log Theend\r\n\u00a0\u00a0\u00a0 ON Theend.Log_Id = TheStart.Log_Id + 1;\r\n<\/pre>\n
4\/ Determine the likely best algorithms, assemble the candidates<\/h2>\n
So we decide we are going to need to use a wildcard search (LIKE and PATINDEX) to find the strings, otherwise the routine won’t be much use. We won’t do a proximity search (two or more words in proximity within a string) \u00a0since this requires a CLR RegEx function \u00a0and we’re trying to keep things simple.<\/p>\n
The first problem we hit is that a\u00a0 string can be found several times in a column. \u00a0You will therefore find it tricky to use a join to get the result.\u00a0 Maybe you could use a number table but you’d then have to iterate through the rows . If we can’t do a simple join then there is going to be iteration there.\u00a0 This is something that must be kept to a minimum.<\/p>\n
I’ve now got to decide how generic I’d like this to be.\u00a0 Every programmer’s instinct is to make what they write elegant and generic.\u00a0 Here we have an immediate problem in that our code assumes that the primary key of the table, of which we’re searching the string-based column,\u00a0 is an integer.\u00a0 You’ll never win the battle to store primary keys in a generic way. We’ll just have to restrict ourselves to assuming an integer primary key or unique index. Generally, they seem to be!<\/p>\n
What sort of routine are we aiming for? I could go for something that searches all possible rows in a database like this one,<\/a> but here. we are only wanting to search a few columns from a few tables. I decide that a Stored procedure is fine for doing this.<\/p>\n
What sort of output do we want? A result? An output variable? Are we outputting a SQL Server result, XML, an \u00a0XHXHTML fragment?<\/p>\n
5\/ Sketch out the candidates<\/h2>\n
The first principle we need to stick to is to access the table that you’re searching \u00a0as little as possible, so we’ll try to do it just once; when testing out your ideas, it is worth checking the execution plan to make sure that this is happening.\u00a0 Using the test data view, we probably want to just scoop out the likely candidates. We use PATINDEX to find those rows that contain the word ‘test’ and record its first occurrence.<\/p>\n
DECLARE\u00a0 @Theyreintheresomewhere TABLE \r\n\u00a0 (ThePrimaryKey int, TheSentence VARCHAR(2000), [START] int);\r\nINSERT INTO @Theyreintheresomewhere(ThePrimaryKey, TheSentence, [START])\r\nSELECT TheKey, TheSentence, startofhits FROM \r\n(SELECT TheKey, TheSentence,\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PATINDEX('%test%',TheSentence) AS startofhits \r\n\u00a0FROM testsentences)f\r\nWHERE Startofhits>0;\r\n<\/pre>\n
A quick check shows us that ‘so far, so good’. We’ve scooped up the rows we want in one pass and determined the location of the first matches of the wildcard ‘%test%’ at the same time.<\/p>\n
I try out a few ideas for listing all the matches from the rows of the table. There seem to be two likely candidates. We’ll test them out<\/p>\n
6\/ Run preliminary performance and scalability tests<\/h2>\n
Is this SQL that we’ve written any \u00a0more efficient than a simpler version such as this?<\/p>\n
DECLARE @Result TABLE (\r\n\u00a0 ThePrimaryKey INT,StartOfString INT,Context VARCHAR( 50 ));\r\nDECLARE @Theyreintheresomewhere TABLE (\r\n\u00a0 ThePrimaryKey INT,TheSentence VARCHAR( 2000 ),[Start] INT);\r\n\u00a0\r\nINSERT INTO @Theyreintheresomewhere\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (ThePrimaryKey,TheSentence,[Start])\r\nSELECT\r\n\u00a0 TheKey,TheSentence,PatIndex( '%test%', TheSentence )\r\n\u00a0 FROM\u00a0\u00a0 TestSentences\r\n\u00a0\u00a0\u00a0 WHERE\u00a0 TheSentence LIKE '%test%'; \u00a0\r\n<\/pre>\n
We pop them both into the development harness to check, using the ‘Canterbury Tales’ \u00a0to test on.<\/p>\n
--first create the temporary log\r\nDECLARE @log TABLE (\r\n\u00a0 Log_Id INT IDENTITY(1, 1),TheeVent VARCHAR( 2000 ),\r\n\u00a0 DateAndTime DATETIME DEFAULT GetDate()); \r\n--\r\n--\u00a0 first run the 'simple search'\r\nINSERT INTO @log (TheEvent) SELECT 'Simple search';\r\n\u00a0\r\nDECLARE\u00a0 @Result TABLE (ThePrimaryKey int, StartOfString int, context varchar(50))\r\nDECLARE\u00a0 @TheyreInThereSomewhere TABLE \r\n\u00a0 (ThePrimaryKey int, TheSentence VARCHAR(max), [START] int)\r\nINSERT INTO @TheyreInThereSomewhere(ThePrimaryKey, TheSentence, [START])\r\nSELECT Sequencenumber, Sentence, PATINDEX('%test%', Sentence) FROM ##sentence\r\nWHERE Sentence LIKE '%test%'; \r\n\u00a0\r\n--and now put in the log entry for the end of the simple search and start of the \r\n--derived table search\r\nINSERT INTO @log (TheEvent) SELECT 'Derived table search';\r\n\u00a0\r\nDECLARE\u00a0 @StringsAreInThisTable TABLE \r\n\u00a0 (ThePrimaryKey int, TheSentence VARCHAR(max), [START] int)\r\nINSERT INTO @StringsAreInThisTable(ThePrimaryKey, TheSentence, [START])\r\nSELECT TheKey, TheSentence, startofhits FROM \r\n(SELECT SequenceNumber AS TheKey, Sentence AS TheSentence,\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PATINDEX('%test%',Sentence) AS startofhits \r\n\u00a0FROM ##sentence)f\r\nWHERE Startofhits>0;\r\n--we need to add this log entry to make the extraction of the timings easier\r\n\u00a0INSERT INTO @log (TheEvent) SELECT 'end'\r\n--now we just report the comparative timings\r\nSELECT\r\n\u00a0 TheStart.TheeVent + ' took ' \r\n\u00a0 + CAST( DatedIff( ms, TheStart.DateAndTime, Theend.DateAndTime ) AS VARCHAR( 10 ))\r\n\u00a0 + ' Ms.'\r\nFROM\u00a0\u00a0 @log TheStart\r\n\u00a0 INNER JOIN @log Theend\r\n\u00a0\u00a0\u00a0 ON Theend.Log_Id = TheStart.Log_Id + 1;\r\n<\/pre>\n
It turns out that they have almost identical timings, at 250 Ms, \u00a0and a quick look at the execution plans shows \u00a0why: \u00a0they generate identical plans on our test data. We can safely use the simpler version for the time being.<\/p>\n
We then turn our attention to producing the matches.<\/p>\n
We can take the procedural approach<\/p>\n
SET NOCOUNT ON\r\n\u00a0\r\nDECLARE @ii\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT,--the row we are checking\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @jj\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT,--the position in the string of the match\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @rowcount\u00a0\u00a0 INT,--the number of rows to search\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @sentence\u00a0\u00a0 VARCHAR(MAX),--the text of the sentence\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @PrimaryKey INT,--the primary key of the string\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @MATCH\u00a0\u00a0\u00a0\u00a0\u00a0 INT-- the index into the substring we are searching.\r\nDECLARE @Theyreintheresomewhere TABLE (\r\n\u00a0 TheIdentityColumn INT IDENTITY(1, 1) PRIMARY KEY,ThePrimaryKey INT,\r\n\u00a0 TheSentence VARCHAR( 8000 ));--all sentences that had a match\r\nDECLARE @Result TABLE (--the result table with the matches\r\n\u00a0 ThePrimaryKey INT,StartOfString INT,Context VARCHAR( 50 )); \r\n\u00a0\r\nINSERT INTO @TheyreInThereSomewhere (ThePrimaryKey,TheSentence)\r\nSELECT SequenceNumber,Sentence\r\nFROM ##Sentence\r\n\u00a0 WHERE Sentence LIKE '%body%'; \r\n--set up the row counter\r\nSELECT @ii=1,@jj=1, @RowCount=COUNT(*) FROM @TheyreInThereSomewhere;\r\n--hidden cursor!\r\nWHILE (@ii<=@RowCount)\r\nBEGIN\r\n\u00a0 WHILE @jj>0\r\n\u00a0\u00a0\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0 SELECT @match = PATINDEX('%body%',RIGHT(TheSentence,LEN(TheSentence+'!')-@jj)),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @PrimaryKey=ThePrimaryKey, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @Sentence=TheSentence\r\n\u00a0\u00a0\u00a0 FROM @TheyreInThereSomewhere WHERE TheIdentityColumn=@ii;\r\n\u00a0\u00a0\u00a0 IF @Match=0 BREAK;\r\n\u00a0\u00a0\u00a0 SELECT @jj=@jj+@Match-1;\r\n\u00a0\u00a0\u00a0 INSERT INTO @Result(ThePrimaryKey, StartOfString, context)\r\n\u00a0\u00a0\u00a0 SELECT @PrimaryKey, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @jj,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN LEN(@Sentence)<40 THEN @Sentence \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN LEN(@sentence)-@jj< 40 THEN '...'+ RIGHT(@Sentence,40) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN @jj<30 THEN LEFT(@jj,40)+'...'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE '...'+SUBSTRING(@Sentence,@jj-20,40)+'...'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0END;\r\n\u00a0\u00a0\u00a0 SELECT @jj=@jj+1;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0 END\r\n\u00a0 SELECT @jj=1, @ii=@ii+1;\r\n\u00a0 END\r\n<\/pre>\n
(I developed this with the small result set, checking with the test result, and then searched for the substring ‘body’ in the Canterbury Tales. Hmm, quick. Even the iteration part only takes 70Ms with a reasonably small result.<\/p>\n
We can do better. If we use this algorithm, we get the second part to 6 Ms, measured with the test harness. The scan of the entire Canterbury \u00a0Tales took only 240 Ms.<\/p>\n
DECLARE @Result TABLE (\r\n\u00a0 ThePrimaryKey INT,StartOfString INT,Context VARCHAR( 50 ));\r\nDECLARE @StringsAreInThisTable TABLE (\r\n\u00a0 ThePrimaryKey INT,TheSentence VARCHAR( MAX ),[Start] INT);\r\nDECLARE @Start INT ;\r\n\u00a0\r\nSET NoCount ON \r\n\u00a0\r\nINSERT INTO @StringsAreInThisTable\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (ThePrimaryKey,TheSentence,[Start])\r\n\u00a0 SELECT SequenceNumber,Sentence,PatIndex( '%body%', Sentence )\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 ##Sentence\r\n\u00a0\u00a0\u00a0 WHERE\u00a0 Sentence LIKE '%body%' ;\r\n\u00a0\r\nWHILE (1=1)\r\nBEGIN\r\n\u00a0INSERT INTO @Result(ThePrimaryKey, StartOfString, context)\r\n\u00a0 SELECT ThePrimaryKey, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 START,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN LEN(<(TheSentence)<40 THEN TheSentence \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN LEN(TheSentence)-Start< 40 THEN '...'+ RIGHT(TheSentence,40) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN Start<30 THEN LEFT(Start,40)+'...'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE '...'+SUBSTRING(TheSentence,Start-20,40)+'...'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0end\r\n\u00a0 FROM @StringsAreInThisTable WHERE start >0;\r\n\u00a0 IF @@Rowcount=0 BREAK\r\n\u00a0\u00a0\u00a0 UPDATE @StringsAreInThisTable \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @start=PATINDEX('%body%',RIGHT(TheSentence,LEN(TheSentence+'!')-start-1)),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 START=CASE WHEN @Start=0 THEN 0 ELSE @Start+start end\r\n\u00a0 WHERE start >0;\r\n\u00a0 END\r\n<\/pre>\n
In doing this exercise, I’ve noticed how bad the context search strings look with returns and linefeeds in them, and how silly the margin looks in the text file. I also wonder whether they’d be improved by showing exactly where in the displayed string the match took place. If I get time, I’ll go back and improve that, but for the time being I’ll press on and get something running, since with the test and dev harness worked out, this can be done quickly.<\/p>\n
7\/ Build<\/h2>\n
We are now at the point where we can be reasonably confident that the routine is working right, but we can save the test scripts we’ve done in case we find a bug and have to revert, or if we have time for improvements.<\/p>\n
I like to delay creating a procedure or function as long as I can just\u00a0 because it is so much easier to do component testing on the various component blocks of logic (e.g. WHILE loops) and expressions, and it is good to be able to look at intermediate results.<\/p>\n
AtAt this stage, I felt confident enough that things were working well enough to encapsulate the logic in\u00a0 the stored procedure.<\/p>\n
I’d soon popped in comments, a header, improved the variable names here and there and generally tidied it up. I parameterized all the obvious things that would benefit from being parameterized. \u00a0I turned it into a generic routine that would work for any table. A glance at the clock showed me I was well ahead of the allotted time.\u00a0 I I was still niggled by the fact that the context of the match was wrong. I’d decided that it would be OK to break the context anywhere rather than at a word boundary, and the job that required the code could be done with what I’d delivered, but it looked tatty so I redid the code so that, instead of …<\/p>\n
ThePrimaryKey StartOfString context\r\n------------- ------------- --------------------------------------------------\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 46\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...my routine, you can test to make sure it...\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 102\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...ails all your automated component tests.\r\n9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 110\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ... events to test the performance of code.\r\n11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...o test a stored procedure in actual use.\r\n... etc ...\r\n<\/pre>\n
It looked like…<\/p>\n
\u00a0  ThePrimaryKey StartOfString Context\r\n------------- ------------- ------------------------------------------------\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 46\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ... dummy routine, you can test to make sure it...\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 102\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...fails all your automated component tests.\r\n9\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0110\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...events to test the performance of code.\r\n11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 99\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ...to test a stored procedure in actual use.\r\n... etc ...\r\n<\/pre>\n
The next part of the build process was to test it out on\u00a0 a number of databases and tables.\u00a0 As is often the case, each new table I tried it on threw up a new difficulty. Although I’d put in a few diagnostics, I wished at this stage I’d done a few more as it would have speeded this process. I had a lot of fun with Chaucer’s ‘the Canterbury Tales.’ It is always a surprise to find such words were in the classics.<\/p>\n
8\/ Unit Test<\/h2>\n
Whilst doing bug-fixes in the light of testing, on various sizes and types of text-based columns, I did a simple unit test to make sure I’d not broken anything. \u00a0This is so unobtrusive that it can be added to the build script, or used for\u00a0 regular checks. Although I’ve tried to pretend that these phases of developing a procedure are\u00a0 distinct, there is actually quite a bit of leaping back and forth between phases in response to finding a bug, or improving the result. However, in the Unit Test phase, it is now time to set up at least one simple automated test to run whenever you make a change. Here is the one I used, based on the test harness I showed you earlier.<\/p>\n
DECLARE @Result TABLE (  \u00a0 ThePrimaryKey INT,StartOfString INT,Context VARCHAR( 2000 )) ;\r\n-- do stuff here\r\nINSERT INTO @result (ThePrimaryKey,StartOfString,Context)\r\nEXECUTE SearchTableColumn 'TestSentences','TheSentence','TheKey', 'test', 50;\r\n\u00a0\r\nSELECT\u00a0 * FROM @result t\r\n\u00a0\u00a0 FULL OUTER JOIN TestSentenceCorrectResult r\r\n\u00a0\u00a0\u00a0\u00a0 ON t.ThePrimaryKey = r.PrimaryKey\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND t.StartOfString = r.StartOfString\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND t.Context = r.Context ;\r\nIF EXISTS ( SELECT * FROM @result t\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FULL OUTER JOIN TestSentenceCorrectResult r\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON t.ThePrimaryKey = r.PrimaryKey\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND t.StartOfString = r.StartOfString\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND t.Context = r.Context\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 t.Context IS NULL;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR r.Context IS NULL )\r\n\u00a0 RAISERROR('The routine is giving the wrong result', 16,1);\r\n<\/pre>\n
I also ran a number of checks on a number of databases I have lying around to make sure that nothing else is obviously broken.<\/p>\n
9\/ Check-in<\/h1>\n
I I like the finality of the check-in. Time’s up, and I’m running a time-box on this routine.\u00a0 The paint is still a little bit wet, but it is time to nip out to the pub. It is pointless to describe the nuts and bolts of Check-in: it is just a handy way to end an article. You’ll probably be using SQL Source Control, a piece of software that is close to my heart.\u00a0 As a punishment, I’m forcing myself to use GIT raw so I can encourage others to improve some of my work, and start to do some collaborative work! The current version of the stored procedure can be downloaded at the bottom of the article.<\/p>\n
Here is the state of play with the routine<\/p>\n
CREATE PROCEDURE SearchTableColumn\r\n\/**\r\nsummary:\u00a0\u00a0 >\r\nThis Procedure searches through whatever strings are loaded into a \r\nthe table called @TableName, and produces a result of all the matches in a column called\r\n@StringColumnName, in context'\r\nAuthor: Phil Factor\r\nRevision: 1.0\r\ndate: 21 Jul 2011\r\nexample:\r\n\u00a0\u00a0\u00a0\u00a0 - - code: EXECUTE SearchTableColumn 'testSentences','TheSentence','TheKey', 'test', 40\r\n\u00a0\u00a0\u00a0\u00a0 - code: EXECUTE SearchTableColumn 'production.document','DocumentSummary','Documentid', 'cycle', 40\r\n\u00a0\u00a0\u00a0\u00a0 - code: EXECUTE SearchTableColumn '##sentence','sentence','SequenceNumber', 'pain', 20\r\nreturns:\u00a0\u00a0 >\r\nresult \r\nThePrimaryKey INT,StartOfString INT,Context VARCHAR( 8000 ))\r\n**\/\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n@TableName SysName,--the name of the table. e.g. production.document, databaseLog\r\n@StringColumnName Sysname,--the name of the column to search. e.g. Documentsummary, T-SQL\r\n@KeyColumnName Sysname,--the name of the integer column that distinguishes the matched row\r\n@StringTosearch VARCHAR(100),--the string to search for.\r\n@ContextWidth INT = 40\r\nAS\r\n--\r\nDECLARE @Result TABLE (\r\n\u00a0 ThePrimaryKey INT,StartOfString INT,Context VARCHAR( 8000 ));--the generic result table\r\ncreate table #StringsAreInThisTable (\r\n\u00a0 ThePrimaryKey INT,TheSentence VARCHAR( MAX ),[Start] INT);\r\nDECLARE @Start INT, @Command NVARCHAR(MAX);\r\n\u00a0\r\nSET NoCount ON \r\n--check that the table exists\r\nIF NOT EXISTS ( SELECT * FROM\u00a0 sys.objects \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 Name LIKE PARSENAME(@TableName,1) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND type IN ('v','u' ))\r\n\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0 RAISERROR('The TABLE or VIEW ''%s'' does not exist in this database',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16, 1, @TableName);\r\n\u00a0\u00a0\u00a0 RETURN 0\r\n\u00a0 END \r\n-- and that the column to search exists\r\nIF NOT EXISTS ( SELECT * FROM\u00a0\u00a0 sys.columns \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 Name LIKE @StringColumnName \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND [object_id]=object_ID(@TableName))\r\n\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0 RAISERROR('The TABLE or VIEW ''%s'' does not have a column to search called ''%s''',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16, 1, @TableName,@StringColumnName);\r\n\u00a0\u00a0\u00a0 RETURN 0\r\n\u00a0 END \r\n--can this column contain a string?\r\nIF NOT EXISTS ( SELECT * FROM\u00a0\u00a0 sys.columns AS c\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.types AS ty \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON c.user_type_id = ty.user_type_id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE ty.name IN ('char','nchar','nvarchar','varchar','text',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'ntext', 'xml', 'sql_variant')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND\u00a0\u00a0 c.Name LIKE @StringColumnName \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND c.[object_id]=object_ID(@TableName))\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\n\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0 RAISERROR('The column %s in TABLE or VIEW ''%s''\u00a0 can not be converted to a string',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16, 1, @StringColumnName, @TableName);\r\n\u00a0\u00a0\u00a0 RETURN 0;\r\n\u00a0 END \r\n--is the supplied key column an integer, at least\r\nIF NOT EXISTS ( SELECT * FROM\u00a0\u00a0 sys.columns AS c \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.types AS ty \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON c.user_type_id = ty.user_type_id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE ty.name IN ('int', 'bigint')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND\u00a0\u00a0 c.Name LIKE @KeyColumnName \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND c.[object_id]=object_ID(@TableName))\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\r\n\u00a0 BEGIN\r\n\u00a0\u00a0\u00a0 RAISERROR('The column %s in TABLE or VIEW ''%s'' is not an integer so can not be used to distinguish a matched row',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16, 1, @KeyColumnName, @TableName);\r\n\u00a0\u00a0\u00a0 RETURN 0;\r\n\u00a0 END \r\n--create the command to fill our table with data.\r\nSELECT @command='INSERT INTO #StringsAreInThisTable (ThePrimaryKey, TheSentence, [START])\r\nSELECT TheKey, TheSentence, PATINDEX(''%'+@StringToSearch+'%'',TheSentence) AS startofhits FROM \r\n(SELECT '+@KeyColumnName+' AS TheKey,\u00a0 REPLACE(\r\n\u00a0\u00a0\u00a0 REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Replace(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Replace(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Replace(Cast ('+@StringColumnName+' as varchar(max)),CHAR(32),CHAR(32)+CHAR(160)),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(160)+CHAR(32),''''),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(160),'''' ),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHAR(10),'' ''),\r\n\u00a0\u00a0\u00a0\u00a0 CHAR(13),'' ''),\r\n\u00a0\u00a0 ''\u00a0 '', '' '') AS TheSentence\r\n\u00a0FROM '+@Tablename+' WHERE Cast ('+@StringColumnName+' as VARCHAR(MAX)) LIKE ''%'+@StringToSearch+'%'')f';\r\nExec sp_ExecuteSQL @Command;\r\n\u00a0\r\nWHILE (1=1)--for ever until we hit a BREAK (see below)\r\nBEGIN\r\n\u00a0INSERT INTO @Result(ThePrimaryKey, StartOfString, context)\r\n\u00a0 SELECT ThePrimaryKey, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 START,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 --we extract the context\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN LEN(TheSentence)<@contextWidth \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN TheSentence--since there is no need to truncate the line\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN LEN(TheSentence)-Start< @contextWidth \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN '...'+ RIGHT(TheSentence,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHARINDEX(' ',REVERSE(TheSentence)+' ',@contextWidth)-1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )--just take off all but the end\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- we can just show the end as there is space \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN Start<(@contextWidth-10) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN RTRIM(LEFT(TheSentence,CHARINDEX(' ',TheSentence+ ' ',@contextWidth)-1))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +'...' --we truncate the end and just show the start\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE '...' --in this case, we take a deep breath and do it properly.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +RIGHT(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 left(TheSentence,Start-(@contextWidth\/2)-1),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHARINDEX(' ',REVERSE(left(TheSentence,Start-(@contextWidth\/2)-1)))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )--the remains of the severed word at the beginning\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(TheSentence,Start-(@contextWidth\/2),@contextWidth)--the slice of text\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +SUBSTRING(TheSentence, --the remains of the severed word at the end\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Start+(@contextWidth\/2),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CHARINDEX(' ',TheSentence+ ' ',Start+(@contextWidth\/2))-(start+(@contextWidth\/2)))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +'...'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 end\r\n\u00a0 FROM #StringsAreInThisTable WHERE start >0\r\n\u00a0 IF @@Rowcount=0 BREAK --nothing more to do\r\n\u00a0\u00a0\u00a0 UPDATE #StringsAreInThisTable --set the\u00a0 location of the next match\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET @start=PATINDEX('%'+@StringToSearch+'%',RIGHT(TheSentence,LEN(TheSentence+'!')-start-1)),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 START=CASE WHEN @Start=0 THEN 0 ELSE @Start + start end\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE start >0 --don't bother with the ones that have no more hits in the string\r\n\u00a0 END\r\nSELECT ThePrimaryKey, StartOfString, Context FROM @result ORDER BY ThePrimaryKey, StartOfString;\r\n\u00a0\r\n<\/pre>\n
Conclusions<\/h1>\n
There is a certain terror in disclosing the processes behind coming up with a\u00a0 T-SQL routine. It is much cosier to pop up with the finished T-SQL as if one wrote it like a Shakespearian sonnet. It belies the actual errors, dead ends, and frustrations that are part of the process. This is probably why it is rare to see the actual process explained in detail. This article will be pretty meaningless unless you have read the first article in this series, How to develop T-SQL Code.<\/a> Once again, I’d caution you that this method works for me but you’ll probably find that every SQL developer has a different way. Hopefully, this article will encourage some other accounts of how to be a productive SQL programmer.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Phil Factor records, as closely as possible, the twists and turns of creating a SQL Server T-SQL stored procedure, describing the methods that work for him.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4178,4150,4151,4183,4252],"coauthors":[6813],"class_list":["post-1184","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-bi","tag-sql","tag-sql-server","tag-t-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1184"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1184\/revisions"}],"predecessor-version":[{"id":77823,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1184\/revisions\/77823"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1184"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}