Browse by Tags

• Architecture
• bitmasks
• challenge
• concatenation
• contest
• date
• datetime
• developer tools
• grouping
• Hierarchies
• logic
• Optimization
• Performance
• puzzle
• Query Tuning
• Scripts
• splitting strings
• Testing

• You REQUIRE a Numbers table!

  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... Posted Wednesday, July 12, 2006 10:18 PM by Adam Machanic | 10 Comments Filed under: T-SQL

• Performance: ISNULL vs. COALESCE

  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... Posted Wednesday, July 12, 2006 10:16 PM by Adam Machanic | 20 Comments Filed under: Performance, T-SQL

• Rowset string concatenation: Which method is best?

  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... Posted Wednesday, July 12, 2006 10:15 PM by Adam Machanic | 25 Comments Filed under: Performance, T-SQL, Architecture

• Is PATINDEX faster than LIKE?

  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... Posted Wednesday, July 12, 2006 10:13 PM by Adam Machanic | 5 Comments Filed under: Performance, T-SQL

• Paging in SQL Server 2005

  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... Posted Wednesday, July 12, 2006 10:12 PM by Adam Machanic | 3 Comments Filed under: Performance, Query Tuning, T-SQL

• Pattern-based replacement UDF

  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... Posted Wednesday, July 12, 2006 10:07 PM by Adam Machanic | 14 Comments Filed under: T-SQL, Scripts

• Caveats of the TEXT datatype

  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... Posted Wednesday, July 12, 2006 10:02 PM by Adam Machanic | 0 Comments Filed under: T-SQL, Architecture