Less Than Dot A New community Site Has Been Launched

Myself and a bunch of friends have been working on Less Than Dot for a while now. The site has a forum, blogs and a wiki. More info why we started Less Than Dot and who we are can be found here: http://www.lessthandot.com/aboutus.php

Since I am mostly a SQL guy, I wrote a collection of SQL Server hacks. This collection of SQL hacks is available on the Wiki, right now we have 8 sections and between 70 and 80 hacks. Ideally we will have more hacks and we will also have a SQL admin hacks page in the future.


SQL Server Hacks Sections
* 1 NULLS
* 2 Dates
* 3 Sorting, Limiting Ranking, Transposing and Pivoting
* 4 Handy tricks
* 5 Pitfalls
* 6 Query Optimization
* 7 Undocumented but handy
* 8 Usefull Admin stuff For The Developer


Below are direct links to all the hacks on the wiki, you can also get a list of all the hacks on the wiki itself here: SQL Server Programming Hacks


NULLS

Testing for NULL values
Use COALESCE To Return First Non Null Value
How To Check If Any, ALL Or No Parameters Have A NULL Value
Return first non null value
Return Null If A Value Is A Certain Value
Concatenation with nulls
NOT IN and nulls

Dates

First and last days of month, quarter, week
Epoch date
Get Datetime Without Time
Date Ranges Without Loops (numbers table)
Calculate birthday in years
Formatting Dates
Calling Stored Procedures With Datetime Parameters
Trouble With ISDATE And Converting To SMALLDATETIME

Sorting, Limiting,Ranking, Transposing and Pivoting

Return Top N Rows
Dynamic top
Sorting Numbers Stored In A Varchar Column
How To Use ROW_NUMBER() In A WHERE Clause
Row To Column (PIVOT)
Column To Row (UNPIVOT)
Split A String By Using A Number Table
Concatenate Values From Multiple Rows Into One Column
Concatenate Values From Multiple Rows Into One Column Ordered
Rank
Dense rank
Rownumber
Sort certain values last
Returning The Maximum Value For A Row

Handy tricks

Five ways to return all rows from one table which are not in another table
Order IP Addresses
Data formatting dates
Data formatting SSN
6 Different Ways To Get The Current Identity Value
Use XACT_ABORT to roll back non trapable error transactions
Random Sorting
Sort Values Ascending But NULLS Last
Adding Leading Zeros To Integer Values
How do I format money/decimal data with commas?
Find Out How Many Occurrences Of A Substring Are In A String
Ten SQL Server Functions That You Have Ignored Until Now
Use the *1 trick to do math with two varchar values
Store The Output Of A Stored Procedure In A Table Without Creating A Table
Find all The Rows Where Any Of The Columns Is Null Or Zero Or Both
Three Ways To Display Two Counts From a Table Side By Side
Finding duplicates across columns
Use REPLACE To eliminate unwanted characters

Pitfalls

Update triggers
Integer math
Identity Values And Triggers
Use XACT_STATE() To Check For Doomed Transactions
Three differences between COALESCE and ISNULL
Non deterministic functions and nullif
Subquery typo with using in
ISNUMERIC Trouble
Case Without Else

Query Optimization

Case sensitive search
Functions on left side of the operator
Query Optimizations With Dates
Optimization: Set Nocount On
Don’t use arithmetic operators on a column in the where clause
Don’t use * but list the columns

Undocumented but handy

xp_getnetname
xp_fileexist
xp_dirtree
xp_subdirs
xp_getfiledetails
xp_fixeddrives
Sp_tempdbspace
xp_enumdsn
xp_enumerrorlogs
Some Undocumented DBCC Commands
sp_MSforeachtable
sp_MSforeachDB

Usefull Admin stuff For The Developer

sys.dm_exec_sessions
Find all tables that contain a certain column
Find All Tables Without Triggers In SQL Server
Find all Primary and Foreign Keys In A Database
Find Out If A Table Has An Identity Column
Use the sys.dm_db_index_usage_stats dmv to check if indexes are being used

Hopefully you will like the site and find some good content, if you have a question then don't hesitate to ask it in a forum.