Turn off Identity Insert

To be able to insert a record into a table with a "hardcoded" ID you simply run:

SET IDENTITY_INSERT mytable ON

--run your insert code here

SET IDENTITY_INSERT mytable OFF

Reseed All Identity Columns in SQL Server 2005

EXEC sp_msforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED)'

This will get the highest record ID for each table and reset the identity seed to one number higher.

It will display any error for any table that does not have an identity column.

To do only one table you can run this:
DBCC CHECKIDENT ('tablename', RESEED)

To see what the current seed is:
DBCC CHECKIDENT ('tablename', NORESEED)

SQLServer 2005 Generate Scripts Append Bug

There is a known bug in SQL Management Studio 2005 where when you try to generate scripts and you set 'Include Descriptive Headers' to false, then 'Append to File' will always be true.

Sadly they fixed this in SMS 2008, but didn't include the fix in SP3 for SMS2005.

The only work around I've found is to download SQLServer 2008 and use SQL Management Studio 2008. The downside to this is that the scripts 2008 generates differ from what 2005 generates.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387334

SQL: 24hr Pivot

this is a little sample of how to pivot data in the form of
names|values|hours
a|1|12
a|9.1|13
b|2.5|12
b|3.6|13

and change it to:
names|12|13
a|1|9.1
b|2.5|3.6

SELECT * FROM (
SELECT names,Hours, Values
FROM aTable a
WHERE a.names IN ('a','b')
) p

PIVOT (
SUM(values) FOR [hours] IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])

) AS pvt

SQL: Randomly Select One Record from Multiple Categories

This query will randomly select one record from multiple categories that you specify. First the inner select partitions the record set based on a specific field, then order's it randomly by using newid(). The outer select then selects the top row from each category (aka the partioned group). The top record is always random due ordering by newid().

SELECT * FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY a.Category ORDER BY newid()) AS RowNumber,
*
FROM Table a ) b
WHERE RowNumber = 1

SQL: More Fun with Unpivot

takes a table like:
date 0 1 2 3 4 5
3/15 a b c d e f

and converts it to

3/15 12:00am a
3/15 1:00am b
3/15 2:00am c

and so on

SELECT dateadd(hour,cast(times AS int),date), cast(times AS int), value

FROM (SELECT date, [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
FROM b) AS p
UNPIVOT (value FOR times IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
)) AS s