Reseeding the Primary Key/ID column of a SQL Table

If you ever need to remove all the records from a SQL database table and then reseed/reset the value of the primary key/ID column, here are the Transact-SQL queries that you can use:

USE [DatabaseName]
TRUNCATE TABLE [TableName]
DBCC CHECKIDENT ("TableName", RESEED, 1);

Adding time to a DateTime value obtained via a SQL SELECT query

If you're looking for a good way to add a fixed duration of time to a DateTime value obtained in a SQL SELECT query, the following example provides the Transact-SQL code that can do this:

DECLARE @DurationTillExpire INT
SET @DurationTillExpire = 1

SELECT 
   dbo.Documents.DocumentId,
   dbo.Documents.LastModifiedDateTime, 
   (SELECT DATEADD(hour, @DurationTillExpire, dbo.Documents.LastModifiedDateTime)) AS 'ExpirationDateTime'
FROM dbo.Documents

In this example, I'm obtaining values from a table called Documents and am simply adding one hour to the DateTime column called LastModifiedDateTime and placing that value in a new column called ExpirationDateTime.  Of course, you may require a different duration such as days, months, or years and can account for this by adjusting the first parameter of the DATEADD function appropriately (see http://msdn.microsoft.com/en-us/library/ms186819.aspx for more info).

Save SQL Server Management Studio Output in a Pipe-Delimited File

In the event that you would like to obtain a pipe-delimited (i.e. '|') output file of data obtained from a query executed in SQL Server Management Studio, here is a quick procedure for making that happen:

If you're using Windows Server 2008 R2:

1. Click Start -> Control Panel
2. Click Clock, Language, and Region
3. Click Change the date, time, or number format
4. Click the Additional settings... button
5. In the List separator field, replace ',' with '|'
6. Click Apply
7. Click OK
8. Click OK
9. Open SQL Management Studio and execute your query
10. Write click in the Results window and select Save Results As...
11. Select the appropriate directory path
12. For File name:, enter an appropriate filename
13. For Save as type:, select CSV (Comma delimited)
14. Click Save
15. You may now open the file in Notepad and confirm that the output is indeed pipe-delimited as opposed to comma-delimited

Once you have the pipe-delimited output you desire, the procedure to reset back to comma delimited is as follows:

1. Click Start -> Control Panel
2. Click Clock, Language, and Region
3. Click Change the date, time, or number format
4. Click the Additional settings... button
5. In the List separator field, replace '|' with ','
6. Click Apply
7. Click OK
8. Click OK

The procedures will be slightly different for other MS operating systems, but the concept will be the same.

SQL Transaction Template

Here is a transaction template that could come in handy any time you need to link a couple of related SQL operations together:

BEGIN TRANSACTION
BEGIN TRY

 --Add related SQL operations here

 IF @@TRANCOUNT > 0
  COMMIT TRANSACTION

END TRY
BEGIN CATCH
 SELECT ERROR_MESSAGE() AS ErrorMessage;
 IF @@TRANCOUNT > 0
  ROLLBACK TRANSACTION;
END CATCH