http://www.sqlservercentral.com/Articles tagged T-SQL, SQL Puzzles posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)How can you delete only some duplicates? Without Identity's, Temp tables, Cursors, loops or ROW_NUMBER()? Would you believe, go back to the 70's?http://www.sqlservercentral.com/articles/T-SQL/63578/2008/08/05http://www.sqlservercentral.com/articles/T-SQL/63578/T-SQL does some things wonderfully, but cursors are the bane of the language, often causing performance issues. Changing your queries around to remove cursors can be tricky and new author Kamran Ali brings us one technique he has used to dramatically improve performance. http://www.sqlservercentral.com/articles/SQL+Puzzles/eliminatingcursors/2223/2008/06/13http://www.sqlservercentral.com/articles/SQL+Puzzles/eliminatingcursors/2223/Longtime SQL Server DBA and author Grant Fritchey decided to quiz his developers on how to perform some simple functions in T-SQL. Read about his results and see how you might do in taking his quiz. http://www.sqlservercentral.com/articles/SQL+Puzzles/2973/2008/05/01http://www.sqlservercentral.com/articles/SQL+Puzzles/2973/All of us need a steady stream of random passwords for the various systems we manage. Peter Larsson brings us an interesting T-SQL solution that generates one for us. http://www.sqlservercentral.com/articles/SQL+Puzzles/2878/2008/02/25http://www.sqlservercentral.com/articles/SQL+Puzzles/2878/While it's not likely that many of you need to find prime numbers using T-SQL, it is an interesting programming exercise. SQL Server guru Kathi Kellenburger brings us one solution after taking a break over the holidays and reading some popular fiction. http://www.sqlservercentral.com/articles/SQL+Puzzles/2782/2008/01/01http://www.sqlservercentral.com/articles/SQL+Puzzles/2782/A SQL puzzle from the SQL guru.http://www.sqlservercentral.com/redirect/articles/3232/2007/09/14http://www.sqlservercentral.com/redirect/articles/3232/An interesting puzzle from the Orlando SQL Server Users Grouphttp://www.sqlservercentral.com/redirect/articles/3150/2007/08/13http://www.sqlservercentral.com/redirect/articles/3150/There are probably not many SQL Server DBAs that use the semicolon as a statement separator. In SQL Server 2005, this is required in certain places and new author Kenneth Powers brings us a look at where and why you need to use this syntax. http://www.sqlservercentral.com/articles/SQL+Puzzles/thegocommandandthesemicolonterminator/2200/2007/06/15http://www.sqlservercentral.com/articles/SQL+Puzzles/thegocommandandthesemicolonterminator/2200/Longtime SQL Server DBA and author Grant Fritchey decided to quiz his developers on how to perform some simple functions in T-SQL. Read about his results and see how you might do in taking his quiz. http://www.sqlservercentral.com/articles/SQL+Puzzles/2973/2008/05/01http://www.sqlservercentral.com/articles/SQL+Puzzles/2973/Chess makes a fantastic game for programming examples. You will find hundreds of examples on the internet. Some dedicated to OO patterns, others to algorithms and so forth. Unfortunately, most of these examples do not use a database or if they do, treat the database as nothing more than a storage repository. In this series of articles we will use SQL Server and T-SQL to implement the game of chess with an emphasis on thinking in sets.http://www.sqlservercentral.com/redirect/articles/3008/2007/05/21http://www.sqlservercentral.com/redirect/articles/3008/All of us need a steady stream of random passwords for the various systems we manage. Peter Larsson brings us an interesting T-SQL solution that generates one for us. http://www.sqlservercentral.com/articles/SQL+Puzzles/2878/2008/02/25http://www.sqlservercentral.com/articles/SQL+Puzzles/2878/For those times when you absolutely, positively got to perform a cross tab query in SQL, Keith Fletcher's T-SQL stored procedure will allow you to do it "on the fly". You can add it to your database and start cross tabbing immediately, without any further setup or changes to you SQL code. Check it out, and then take the cross tab challenge.http://www.sqlservercentral.com/redirect/articles/2949/2007/04/11http://www.sqlservercentral.com/redirect/articles/2949/While it's not likely that many of you need to find prime numbers using T-SQL, it is an interesting programming exercise. SQL Server guru Kathi Kellenburger brings us one solution after taking a break over the holidays and reading some popular fiction. http://www.sqlservercentral.com/articles/SQL+Puzzles/2782/2008/01/01http://www.sqlservercentral.com/articles/SQL+Puzzles/2782/T-SQL does some things wonderfully, but cursors are the bane of the language, often causing performance issues. Changing your queries around to remove cursors can be tricky and new author Kamran Ali brings us one technique he has used to dramatically improve performance. http://www.sqlservercentral.com/articles/SQL+Puzzles/eliminatingcursors/2223/2008/06/13http://www.sqlservercentral.com/articles/SQL+Puzzles/eliminatingcursors/2223/In the final installment of his series, Michael Coles examines a few other functions in his SQL Server 2000 toolkit to help you with other functions, like reading a directory on your server. Read the series and send some feedback to make this THE premier toolset for SQL Server 2000 DBAs. http://www.sqlservercentral.com/articles/Administering/sql2000dbatoolkitpart4/2364/2006/05/04http://www.sqlservercentral.com/articles/Administering/sql2000dbatoolkitpart4/2364/Phonetic matching, working on finding terms that are misspelled, is an art and there are a number of ways to handle it. In SQL Server 2000 the tools are limited, but Michael Coles brings us part 3 of his toolkit with some handy functions that can help you with text searching. http://www.sqlservercentral.com/articles/SQL+Puzzles/sql2000dbatoolkitpart3/2363/2006/04/27http://www.sqlservercentral.com/articles/SQL+Puzzles/sql2000dbatoolkitpart3/2363/SQL Server allows multiple triggers on a table, but the order in which they are executed can sometimes cause problems. Author Dinesh Asanka brings us a technique for managing the order of execution for your triggers. http://www.sqlservercentral.com/articles/SQL+Puzzles/triggerstoexecuteinpredefinedorder/2337/2006/04/06http://www.sqlservercentral.com/articles/SQL+Puzzles/triggerstoexecuteinpredefinedorder/2337/Do you use identity values? Do missing values ever cause problems? Maybe you need to look for missing invoices? Stephen Lasham brings us a technique for finding those missing values in a sequence. http://www.sqlservercentral.com/articles/SQL+Puzzles/findinggapsinasequentialnumbersequence/2336/2006/04/03http://www.sqlservercentral.com/articles/SQL+Puzzles/findinggapsinasequentialnumbersequence/2336/Do you really understand what happens when you create a stored procedure? What if you have some extra code in your batch? This is an interesting article that shows an interesting effect of creating a stored procedure and where your GO statement is placed. http://www.sqlservercentral.com/articles/SQL+Puzzles/thedodgygostatement/2276/2006/02/21http://www.sqlservercentral.com/articles/SQL+Puzzles/thedodgygostatement/2276/String manipulation is not one of the strong points of T-SQL, but there are some functions that can greatly assist you with searching and working with character values. New author Robert Davis brings us a look at PATINDEX, one of the search functions and also explains the differences between it and the often used CHARINDEX. http://www.sqlservercentral.com/articles/SQL+Puzzles/practicalusesofpatindex/2236/2006/02/06http://www.sqlservercentral.com/articles/SQL+Puzzles/practicalusesofpatindex/2236/T-SQL does some things wonderfully, but cursors are the bane of the language, often causing performance issues. Changing your queries around to remove cursors can be tricky and new author Kamran Ali brings us one technique he has used to dramatically improve performance. http://www.sqlservercentral.com/articles/SQL+Puzzles/eliminatingcursors/2223/2008/06/13http://www.sqlservercentral.com/articles/SQL+Puzzles/eliminatingcursors/2223/It's not recommended, but sometimes writing scripts against the system tables and getting information is a great way to work with SQL Server. Alex Grinberg brings us a look at some of the things you can get from system tables along with some sample scripts. http://www.sqlservercentral.com/articles/SQL+Puzzles/workingwithsystemtables/2212/2006/01/16http://www.sqlservercentral.com/articles/SQL+Puzzles/workingwithsystemtables/2212/There are probably not many SQL Server DBAs that use the semicolon as a statement separator. In SQL Server 2005, this is required in certain places and new author Kenneth Powers brings us a look at where and why you need to use this syntax. http://www.sqlservercentral.com/articles/SQL+Puzzles/thegocommandandthesemicolonterminator/2200/2007/06/15http://www.sqlservercentral.com/articles/SQL+Puzzles/thegocommandandthesemicolonterminator/2200/Writing outer joins can be more difficult than it appears at first glance. Steve Jones examines this less often used query technique and brings to light a few places where you can lose data. http://www.sqlservercentral.com/articles/SQL+Puzzles/outerjointrouble/2226/2006/01/05http://www.sqlservercentral.com/articles/SQL+Puzzles/outerjointrouble/2226/When programming in T-SQL, the impact of NULL values can be severe in many cases, especially aggregates. New Author Peter Ward brings us a great explanation on how NULLs can impact things like SUM operations. http://www.sqlservercentral.com/articles/SQL+Puzzles/malefemaleandtheotheronenull/2194/2005/12/28http://www.sqlservercentral.com/articles/SQL+Puzzles/malefemaleandtheotheronenull/2194/SQL Server 2000 has table valued functions that are very useful in many ways. However when you try to limit results with the ROWCOUNT setting, you can end up with some strange results. Peter He brings us a comprehensive look at some of the unpredictable results that you can get and how to code around them. http://www.sqlservercentral.com/articles/SQL+Puzzles/setrowcountandtvf/2198/2005/12/21http://www.sqlservercentral.com/articles/SQL+Puzzles/setrowcountandtvf/2198/SQL Server 2000 error handling isn't the most mature system for dealing with unexpected events. It has been much enhanced in SQL Server 2005, but many people will be using SQL Server 2000 for a long time. RAISERROR is one of those functions that can really aid in troubleshooting, but is often underutilized. David Poole brings us some hints on how this can help you out in your code. http://www.sqlservercentral.com/articles/SQL+Puzzles/quickhintsforusingtheraiserrorcommand/2114/2005/11/23http://www.sqlservercentral.com/articles/SQL+Puzzles/quickhintsforusingtheraiserrorcommand/2114/Selecting a menu structure from a series of tables is pretty easy. If you bound the number of levels in the menu. However if you have potentially variable numbers of levels. Stephen Lasham brings us a new piece on using recursion to select the data. http://www.sqlservercentral.com/articles/SQL+Puzzles/reportingamenustructureusingarecursiveudf/2072/2005/10/11http://www.sqlservercentral.com/articles/SQL+Puzzles/reportingamenustructureusingarecursiveudf/2072/Auditing is becoming more and more important in many SQL Server environments. DBAs are being tasked with setting up, maintaining, and reporting on audit data. Author Leo Peysakhovich brings us one of his solutions to automatically generate an audit trail for your data. http://www.sqlservercentral.com/articles/SQL+Puzzles/anaudittrailgenerator/2067/2005/10/10http://www.sqlservercentral.com/articles/SQL+Puzzles/anaudittrailgenerator/2067/We've had a lot of coverage of dynamic sql (including another great one from Robert Marda later this week) but this one is a little different. Done in a question/answer format, Andy tries to explain to junior developers why dynamic sql is to be avoided, how to do so, what to do when you can't. http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicsqlorstoredprocedure/969/2005/08/26http://www.sqlservercentral.com/articles/Performance+Tuning/dynamicsqlorstoredprocedure/969/