Why would you use “Stuff” instead of “Replace?” Good question. Both perform a similar function – replace a string with another string (or no string at all), but Replace has a simpler syntax. The Replace function looks like this: select replace(‘abcdef’,'bc’,'xx’) result: axxdef No locations to find, just replace the string. However, what if we had [...]
Here is a simple example of a routine to capture the results of an executed stored procedure so they can be acted upon. To be honest, I am posting this completely for selfish reasons as I tend to forget this syntax more often than I wish to admit. CREATE TABLE #Results ( Column1 varchar(64), Column2 varchar(64), ) [...]
For the uninitiated, a derived table is where you create a query that contains another query but that second query is wrapped in parenthesis and you will normally join to it for further results or set operations. The pseudo-code is like: Select a.Blah, b.BlahBlah From FirstTable a Join (select BlahBlah from SecondTable ) b On [...]
We can create a new copy of a table from an existing one, using the “select into” function. However, while “select into” is a powerful function it can also be a server killer due to the i/o overhead copying a bazillion rows in a single batch (if the source was a large table), and more [...]
Here’s something to contemplate: What’s the difference between “group by” and “select distinct” in a simple query like: select distinct a.LastName from dbo.Account a with (nolock) could be rewritten as: select a.LastName from dbo.Account a with (nolock) group by a.LastName Who can tell us what the difference in execution would be? When would you pick [...]
We use the @@Error and @@rowcount system variables all the time in queries and sprocs. However, one might be lead astray thinking they behaved like “regular” variables in programming, i.e., they are persistent, but they are not, they only hold the value from the most recent statement. Here’s an example where I declare a couple [...]
This question circulates through the office about once a year. “Um, I have Windows service which processes requests which are queued in a database table. Everything is working great, but I would like to add a second instance of the service and I need to know the best way to ensure the services don’t step [...]
Continue reading about What’s the Best Way to Manage a Database Queue?
Out of the box, you can do a lot with the DataView.RowFilter property, but you can’t make use of the top clause. However, if the view contains a column with unique values, you can obtain the same results by setting the Sort property and then generating a RowFilter which uses the in condition. Here’s a [...]