tSQL: Drop Temp Table if Exists

Posted by SteveHardie | On: Feb 26 2014 | Comments (0)
This article describes how to check if a temporary table exists before you delete it. IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL DROP TABLE #tmpTable'

How To: MS SQL Reseed or Reset Identity Column

Posted by SteveHardie | On: Jan 20 2013 | Comments (0)
DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer. DBCC CHECKIDENT ([YourTableName],...

How To: MS SQL Empty Database Log File

Posted by SteveHardie | On: Jul 15 2012 | Comments (0)
You will often find your database log file (.ldf) growing to a very large size, often larger than the databasie file (.mdf) itself. Log files should shrink after a full backup, but this isnt always the case. Below is a quick command you can run to empty a database...

SQL 2008: Truncate or Srink a large Log File

Posted by SteveHardie | On: Nov 23 2011 | Comments (0)
I had a problem with a SQL Server 2008 database that had a log file in excess of 90gb, when the DB file itself was less than 2gb. Using the SQL Server Managment Studio to Backup, or Shrink File did not reduce the log file size. In previous versions of SQL, you...

How To: Move MS SQL Database to a New Location

Posted by SteveHardie | On: Jul 20 2011 | Comments (0)
This article describes how to change the location of the data files and of the log files for any Microsoft SQL Server 2005, SQL Server 2000, or SQL Server 7.0 database.

tSQL Return Identity When Adding New Record

Posted by SteveHardie | On: Mar 21 2011 | Comments (0)
@@Identity (Transact-SQL) Is a system function that returns the last-inserted identity value. To use this in a tSQL statement, you need to declare an output variable and set the value after inserting a new row into a table. SET @RecordID=@@IDENTITY Using...

MS SQL: If Statment within Select

Posted by SteveHardie | On: Jun 17 2010 | Comments (0)
When creating a SELECT query, sometimes you may want to display a dynamic field with a different value depending on a certain condition. Below is an example of how to use an IF Statment for creating a column within a select query: SELECT Field1, Field2, PriceField ...