|
|
|
|
Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.
July 2006 - Posts
-
In the last installment, I showed a potentially fastest method using Array.Reverse. After finding and fixing a bug in method #3 posted in my last installment (it is, in fact, quite a bit faster than method #1 when you don't have a big huge bug in the Read More...
|
-
Over in the Simple-Talk forums, there is a good thread going about how best to reverse a string in .NET, since no string reverse method is included in the BCL . A few suggestions were made, and someone implied that they were too complex and that simplicity Read More...
|
-
An hour of cutting and pasting later (yes, I broke down and did it manually), and all of my favorite "archive" articles from my SQLJunkies blog are here. Now I just need to write some new content! I'll be mirroring over to SQLJunkies for a while, but Read More...
|
-
Peter van Ooijen over at CodeBetter.com posted in his blog about some observations he had when working with stored procedures in a recent project . What I found to be interesting about his post was his comment that a stored procedure can be, "a view with Read More...
|
-
Tony Rogerson brings us an interesting blog post about T-SQL variable assignment and SET vs. SELECT . The issue? With SELECT you can assign values to multiple variables simultaneously. But with SET, you can set up your assignment such that you get an Read More...
|
-
Back again! Fourth post for the month of February, making this my best posting month in, well, months. Expect this trend to continue. After yesterday's post on running sums and the evils of cursors , Jamie Thompson came up with a faster solution than Read More...
|
-
Siddhartha Gautama, the Buddha, taught us to understand that the key to enlightenment is following the Middle Path. And today I learned a valuable lesson in extremes. You can file this one in the "Doh! Wrong again!" category... A fairly common question Read More...
|
-
In our previous installment , we saw how to convert Adjacency Lists into Nested Sets using a CTE. In this episode, we will convert the Adjacency List into a Nested Intervals encoding. Specifically, this encoding will make use of the Nested Intervals with Read More...
|
-
I spoke at the Beantown .NET user group meeting tonight, on the topic of SQLCLR in SQL Server 2005. One of the questions that came up during the UDT part of the talk was whether static properties are supported. Unfortunately, I had no answer at the time--it's Read More...
|
-
I'm not sure how many times over the last several years I've seen the same tired article titles... "Climbing Trees in SQL," "Climbing Up the SQL Tree," or maybe, "Naked Coeds Playing in the Trees!" ... Oh wait, I think that last one might be something Read More...
|
-
The other day I annouced the Texas Hold 'em SQL Challenge. I haven't gotten any feedback on it yet, so I have no idea if anyone is working on it, but I thought I'd get the ball rolling and come up with my own solution... The first step I've decided to Read More...
|
-
Of all of the undocumented stored procedures shipped with SQL Server, there are two in particular that I constantly use: sp_MSforeachtable and sp_MSforeachdb . These procedures internally loop over each non-Microsoft shipped (i.e. user-defined) table Read More...
|
-
A new feature added to SQL Server 2005 for the sake of the windowing functions is the OVER clause. Using this clause, you can specify ordering or partitioning for the windowing functions. For instance, to enumerate the names of all of the products in Read More...
|
-
Yesterday morning I had to deal with a non-bootable Windows XP machine. Every time it turned on, it would get to the Windows XP spash screen, sit there for a while, then flash a BSOD and restart -- the BSOD flashed just long enough to see that the screen Read More...
|
-
I just posted a few SqlDataReader performance tips in response to a newsgroup post; I think they're some pretty good tips, so I'll repeat them here. These tips were gleaned from using both Lutz Roeder's Reflector and Compuware's DevPartner Profiler Community Read More...
|
-
Widely acknowledged SQL Server expert Kimberly Tripp has weighed in on SQL Server 2005's CLR integration in her latest blog post . Tripp presents a fairly balanced reaction to the new technology, and stresses a message I've been trying to hit home recently: Read More...
|
-
More fun CLR conversation , this time from SQL advocate Joe Celko. Surprise, surprise, Celko is not a big fan of CLR integration. Yet he mentions the SQL/PSM standard as a better alternative, even though it has the same issues he mentions. But it's open-source Read More...
|
-
Tom Rizzo pointed out the other day that he and James Hamilton were featured on The .NET Show , discussing SQL Server 2005. This was a pretty interesting show, and I recommend that readers interested in SQL 2005 features watch the webcast. However, one Read More...
|
-
I am pleased to announce the official introduction of the TSQLMacro framework , version 0.5 (prebeta-1). This version is functionally complete based on the initial design spec , features are stable, and I am not currently aware of any bugs. So what is Read More...
|
-
Yes, yet another introduction (although there was almost zero reaction to yesterday's introduction ). Today, I introduce the first thing built on top of TSQLMacro . I proudly present the TSQLAssert debug assertion framework ! For those of you who've worked Read More...
|
-
Yes, another string splitting UDF from a guy who's obvioiusly become obsessed with TSQL string splitting. This time we delve into a mysterious world that I call, "Tokenization." So what is Tokenization? It's a word I made up for this problem. But what Read More...
|
-
Ever want to see the text of a stored procedure, function, or trigger -- or manipulate the text in some way? sp_helptext works, sort of. But I really don't like the way it handles large procedures (> 4000 characters). They seem to end up with some Read More...
|
-
In the course of my work, I occasionally need to cluster a primary key that's nonclustered, or go the other way, or make some other modification to a primary key... But it's a hassle! All of the foreign keys need to be dropped, the PK needs to be dropped, Read More...
|
-
"hickymanz" asked in the SQL Server Central forums for a method of counting unique words in a text column. Wayne Lawton recommended using a string split function, which was a good idea, but not quite adequate for the job in my opinion. Typical string Read More...
|
-
Quick installment this time. Left-shift and right-shift operators. Left-shift and right-shift are integral to binary mathematical operations as they have two important qualities: Left-shifting a bitmask once multiplies by two. Right-shifting once divides Read More...
|
-
It's been longer than I hoped since my last installment on bitmask / big number handling . Life caught up with me and I've had many thankless tasks to catch up on. But that's over now and I'm back to the general slacking that typifies my days, so welcome Read More...
|
-
Posting the first part of my series on bitmasks (yes, this is now officially a series) taught me a lot about my readers: You don't care about handling bitmasks in the database. And I respect you for that! I'm overjoyed, as a matter of fact! That article Read More...
|
-
In the article on handling bitmasks I posted the other day, I made a fatal error in the splitBitmask function . The function treated the low byte as the first byte, instead of the high byte. Therefore: 0x01 != 0x0001 ... and that is not good! So here's Read More...
|
-
Continuing in my series of things you should probably not do in SQL Server but sometimes have to , I'm going to do a few posts on dealing with very large bitmasks. Let me first state my utter hatered of bitmasks in databases. I think they're annoying, Read More...
|
-
File this one in your folder of things you should probably never use -- but maybe, some day, in an emergency, you'll need this. I see posts requesting this functionality all the time. "How do I validate a URL in SQL Server?" Not just the string, but the Read More...
|
-
I have absolutely no idea why anyone wants to do this, but I keep answering the same question in forums: "How do I count the occurrences of a substring [note: usually comma] within a string?" In an effort to thwart carpal tunnel syndrome, I have created Read More...
|
-
There are many techniques for splitting a string in T-SQL (in other words, taking a character-delimited string and producing a table of the values), the best of which are encapsulated in Erland Sommarskog's famous article . My favorite of his string splitting Read More...
|
-
Tell me if this situation sends a chill down your spine: You've written a stored procedure, tested it against a variety of inputs, and finally rolled it out in production. All is well... Or so you think. You start getting complaints from some users that Read More...
|
-
Looking at my list of upcoming articles, I keep seeing the same theme repeated over and over. A sequence table of Numbers. Numbers tables are truly invaluable. I use them all of the time for string manipulation, simulating window functions, populating Read More...
|
-
Mladen aka spirit1 posted a speed test of COALESCE vs. ISNULL. Reported result: COALESCE is faster. But leave it to Anatoly Lubarsky to argue with what was posted. He posted his own speed test, showing that ISNULL is faster. Anatoly's results showed a Read More...
|
-
Yeah, yeah, yeah, let's get this out of the way right from the start: Don't concatenate rows into delimited strings in SQL Server. Do it client side. Except if you really have to create delimited strings in SQL Server. In which case you should read on. Read More...
|
-
I keep seeing the same suggestion on various "tips and tricks" websites: For situations in which you might want to use LIKE in the WHERE clause, but for which indexes cannot be used, PATINDEX will perform faster. So, according to these sources , this: Read More...
|
-
I keep seeing questions on newsgroups about paging in stored procedures, and whether there will be a better way in SQL Server 2005. However, aside from a few answers in newsgroups, I haven't seen any content on how to do it. So I'd like to spend a few Read More...
|
-
I recently found a rather old post from Frans Bouma 's blog, "Stored procedures are bad, m'key?" . Since comments are closed (he posted it almost a year ago), I have to reply here. I'll state my bottom line at the top: Stored procedures are not only not Read More...
|
-
As a personal challenge, I decided to write a UDF that will work just like T-SQL's REPLACE() function, but using patterns as input. The first question: How does REPLACE() handle overlapping patterns? SELECT REPLACE('babab', 'bab', 'c') -------------------------------------------------- Read More...
|
-
Someone named "Krygim" posted the following question today in the microsoft.public.sqlserver.programming newsgroup: "Will defaulting a TEXT field to an empty string take up more space than defaulting it to Null when no data is entered into the field. Read More...
|
|
|
|
|
|