Library of Useful (Difficult) SQL scripts

Sql Problem Overview

Does anyone know where I can find a library of common but difficult (out of the ordinary) SQL script examples. I am talking about those examples you cannot find in the documentation but do need very often to accomplish tasks such as finding duplicates etc.

It would be a big time saver to have something like that handy.

EDIT: Thanks everyone, I think this is turning into a great quick reference. The more descriptive the more effective it would be, so please if you see your way open - please edit and add some descriptions of what one could find. Many thanks to those that have already done so!

Sql Solutions

Solution 1 - Sql

You may find this wiki on LessThanDot useful, for the most part, it is by Denis Gobo, Microsoft SQL MVP.

EDIT: The wiki includes 100+ SQL Server Programming Hacks, the list is, I think, too long to include here, however, there is a comprehensive index.

Also available from the same site: SQL Server Admin Hacks.

Solution 2 - Sql

Here are a few that I find very useful:

SQL Server Best Practices - Microsoft SQL Server White Papers and Best Practices
vyaskn - A mixture of articles From DBA to Developer
Backup, Integrity Check and Index Optimization
SQLServerCentral Scripts - Scripts for most most DBA tasks and more
Script Repository: SQL Server 2005 - TechNet Script Center
Scripts and Tools for Performance Tuning and Troubleshooting SQL Server 2005
SQL Server Query Processing Team - Hardcore advice from the MS SQL optimisation team
Common Solutions for T-SQL Problems
Davide Mauri's Index Scripts

Solution 3 - Sql

Some Administration stuff

Glenn Berry: Five Very Useful Index Selection Queries for SQL Server 2005

Find "Missing" Indexes for the entire instance of SQL Server
Find "Missing" Indexes for a single table
Examine the current index structure for a single table
Look at index usage for a single table
Look for possible bad indexes inside the entire current database
Drill into your workload (Bonus)

SQL Server Central: Seven Monitoring Scripts

Failed jobs report
Free space by drive
Disabled jobs
Running jobs
Server role members
Last backup date
SQL Log

And last, but not least this resource: SQL Server Programming Hacks - 100+ List

Solution 4 - Sql

Joe Celko's SQL Puzzles and Answers

The Art of SQL (slight Oracle bias)

Solution 5 - Sql

Sql Cookbook has a variety of interesting example, though some will undoubtedly be unsupported by your RDBMS of choice. O'Reilly also has a T-SQL Cookbook, but I've never personally read it.

Solution 6 - Sql

directly from MS Script Repository: SQL Server 2005: http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true

Nigel's very usefull stuff: http://www.nigelrivett.net/#TransactSQL

Solution 7 - Sql

Forgive me for the self-advertising, but I have posted a few on my blog (http://progblog.wordpress.com) because I'm rubbish at SQL and it's a good place to store things I know I'll need in the future :-) If anyone has anything more substantial then please post, I'm as keen as anyone to get hold of something like this!

I would guess that a copy of the "SQL Cookbook" would help too.

Solution 8 - Sql

I've had some use of these SQL "hacks" for Oracle a couple of times.

Concatenate as grouping function

In query data generation for joining purposes

Solution 9 - Sql

Here is another link for SQL Server: best practices - dozens of script examples

http://www.sqlusa.com/bestpractices2005/

Solution 10 - Sql

[Check Out SQLCAT.com (MS SQL BEST PRACTICES TEAM)][1]

[1]: http://www.sqlcat.com "SQL Customer Advisory Team Best Practices Blog"

Solution 11 - Sql

Riffing off the Celko answer: SQL For Smarties. This has great in depth chapters that will augment the SQL Puzzles book. Also there is another Celko book I just learned of named Joe Celko's Trees and Hierarchies in SQL for Smarties.

Content Type	Original Author	Original Content on Stackoverflow
Question	Johan Bresler	View Question on Stackoverflow
Solution 1 - Sql	Fionnuala	View Answer on Stackoverflow
Solution 2 - Sql	Mitch Wheat	View Answer on Stackoverflow
Solution 3 - Sql	splattne	View Answer on Stackoverflow
Solution 4 - Sql	Cade Roux	View Answer on Stackoverflow
Solution 5 - Sql	Mark Brackett	View Answer on Stackoverflow
Solution 6 - Sql	Mladen Prajdic	View Answer on Stackoverflow
Solution 7 - Sql	endian	View Answer on Stackoverflow
Solution 8 - Sql	John Nilsson	View Answer on Stackoverflow
Solution 9 - Sql	splattne	View Answer on Stackoverflow
Solution 10 - Sql	Matt	View Answer on Stackoverflow
Solution 11 - Sql	David Robbins	View Answer on Stackoverflow