|
|
|
|
Adam Machanic, Boston-based independent database consultant, writer, and speaker, shares his experiences with programming, performance tuning, and optimizing SQL Server 2000, 2005, and 2008, in conjunction with related technologies such as .NET.
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...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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... Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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, Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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. Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 ! Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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, Read More...
|
-
Originally posted here . "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 Read More...
|
-
Originally posted here . 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. Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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. Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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 Read More...
|
-
Originally posted here . 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', Read More...
|
-
Originally posted here . 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 Read More...
|
-
Welcome to my new space. I'll be re-posting my favorite and/or most popular (probably the latter first) articles and posts here over the next couple of days. In the meantime, if you feel like checking out the archives, visit my old blog here . Lots of Read More...
|
|
|
|
|
|