A quick guide how to write safe SQL scripts

This is quite trivial but I am going to provide few common examples how to ensure safe multiple run of sql server scripts. Of course we have to check if the script has already been executed to avoid errors or duplication.

SQL TABLE
Check if a table exists before its creation:

IF (NOT EXISTS
(SELECT * FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA = N'SchemaName'
          AND  TABLE_NAME = N'TableName'))
BEGIN
    --Run table creation script
END

SQL TABLE COLUMN
Check if a column exists before adding it:

IF NOT EXISTS(
    SELECT * FROM sys.columns WHERE Name = N'ColumnName'
    AND Object_ID = Object_ID(N'TableName'))
BEGIN
    --Run column definition script
END

SQL VIEW
Check if a view exists and remove it in order to safely create the one:

IF EXISTS(select * FROM sys.views where name = N'ViewName')
BEGIN
   DROP VIEW ViewName
END  

go 

CREATE VIEW ViewName ....

SQL STORED PROCEDURE
Check if a stored procedure exists and remove it

IF EXISTS (SELECT * FROM sys.objects
           WHERE object_id = OBJECT_ID(N'ProcName')
           AND type IN ( N'P', N'PC' ) )
BEGIN
   DROP PROCEDURE dbo.[ProcName]
END
go
CREATE PROCEDURE ...

SQL FUNCTION
Check if a function exists and remove it

IF EXISTS (SELECT * FROM sys.objects
           WHERE object_id = OBJECT_ID(N'[dbo].[FuncName]')
           AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
  DROP FUNCTION [dbo].[FuncName]
END
go
CREATE FUNCTION...

SQL TABLE INDEX
Check if an index exists and remove it

IF EXISTS (SELECT * FROM sys.indexes
           WHERE name='IndexName'
           AND object_id = OBJECT_ID('TableName'))
BEGIN
   --DROP INDEX ...
END

SQL DATA ENTRY
Check whether data has already been updated.

IF NOT EXISTS (SELECT * FROM SampleTable WHERE )
BEGIN
  INSERT INTO SampleTable...
END

IF EXISTS (SELECT * FROM SampleTable WHERE )
BEGIN
  UPDATE SampleTable SET .... WHERE
END

SQL - How to delete table content quickly

The deletion of table content may take significant amount of time when the number of rows is large. This is so because every singe row deletion is logged into the database transaction log.
In the cases that the deletion log is not important you can simply remove the data rows w/o logging any individual row deletion by truncating the table:

TRUNCATE TABLE [TableName]

Reference:
http://msdn.microsoft.com/en-us/library/ms177570.aspx

How to concatenate row data into string using SQL

Very simple way to achieve this is to use FOR XML statement. The result of the following example is list of user names including first and last name separated by commas.

SELECT FirstName+' '+LastName+', ' FROM UserProfile FOR XML PATH('')

But the result string ends with a comma and space. We can either remove it by SUBSTR or REPLACE functions. Using REPLACE as it is shown below saves the usage of subquery or calcualtion of the length of the same expression.

SELECT
REPLACE((REPLACE((REPLACE(
(SELECT FirstName+' '+LastName FROM UserProfile FOR XML PATH('A'))
,'</A><A>',', '))
, '</A>',''))
,'<A>','')

Here is another way to achieve this which might be convinient in stored procedures.

DECLARE @Names VARCHAR(8000)
set @Names=''
SELECT @Names = @Names + CASE WHEN @Names<>''
THEN ', ' ELSE '' END + Name
FROM Users
SELECT @Names

3 ways to get date in SQL without time

Here are 3 ways of getting date-only part of timestamp in SQL Server:

1. DATEADD( DAY, 0 , DATEDIFF(DAY,0, CURRENT_TIMESTAMP) )

2. CONVERT( datetime, FLOOR(CONVERT(float(24), GETDATE())) )

3. CAST( CONVERT(CHAR(8), GETDATE(),112) as DATETIME)

SQL – Cannot resolve collation conflict for equal to operation

When the sql collation of strings is not equal but need to me compared we need to explicitly define the collation. The most common way is the using of the default database collation as in the example:

WHERE Table1.Name COLLATE DATABASE_DEFAULT=Table2.Name COLLATE DATABASE_DEFAULT

The collation change can be performed on tables joining, 'where' clausesq as part of stored procedures and functions, as well as database Default collation change.

SQL: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing.

The following error message is got when the number of open transactions is different than the number of committed or rolled back transaction in the end of the stored procedure.

The global variable @@TRANCOUNT gives the number of opened transactions in the moment of check-up. If you are not sure what is number of opened transactions you can check the number at the end of the procedure and commit or rollback.

DECLARE @TranStarted bit
SET @TranStarted = 0

IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0

-- do something
-- use select statements with locking rows or tables using
-- WITH ( UPDLOCK, HOLDLOCK ), WITH (HOLDLOCK)

IF( @@ERROR <> 0 )
GOTO Cleanup

--do something


IF( @@ERROR <> 0 )
GOTO Cleanup


-- if no errors commit the transaction
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END

RETURN 1

-- in case of errors jump to the label cleanup to rollback the transaction
Cleanup:

IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END

RETURN 0

SQL: How to check for existing contraint

Just an example how to get it form the database information schema:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='dbo' AND CONSTRAINT_NAME='IX_Constraint' AND TABLE_NAME='TableName')
BEGIN
ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT ..... /* constraints */
END

SQL Stored Procedures - getting return value

Starting directly with example:

CREATE PROCEDURE ProcessValue
(
@value int,
@returnValue int OUTPUT
)
AS
BEGIN
/* do calculation and processing and assign the result value to output parameter */
SET @returnValue = value + 5*value

END

The following calling of the procedure shows how to get output parameter.

declare @result int
EXEC ProcessValue 15, @result OUTPUT

Of course this example is pretty simple - only for showing how to use. For such usage the SQL scalar-valued functions are better way.

SQL pagination: get fixed number of rows page by page

See the following examples:

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Id) AS [SortNum], * FROM MyTable) As Tmp
WHERE SortNum Between 11 AND 20

or

WITH Tmp As
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS [SortNum], * FROM MyTable)
SELECT * FROM Tmp
WHERE SortNum Between @StartRow AND @StartRow+@NumberOfRows-1


or

WITH Tmp As
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS [SortNum], * FROM MyTable)
SELECT * FROM Tmp
WHERE SortNum Between (@page-1)*10+1 AND (@page)*10


The first one gives the second page of 10 records.
The second example shows the usage of WITH keyword and getting rows passed as parameter of stored procedure.
The third one shows you how to use 1 passed parameter as number of the desired page.

Sql Date Manipulation

DATEPART (datepart, date) returns the desired part of the date specified by datepart parameter: year,
quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond.

Example: DATEPART(month, GETDATE()) returns current month number

DAY(date), MONTH(date), YEAR(date) return respectively the number of the day, month or year of the passed date.

To add or subtract date parts from a given date use this function:
DATEADD (datepart , number, date)
where datepart is one of the following parameter (datepart): year,
quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond.

Example: DATEADD(day, -7, GETDATE()) return the date week ago.

DATEDIFF (datepart, startdate, enddate) gives you the difference between two dates in desired date part (see DATEPART date parts above). Startdate should be date before end date. Otherwise a negative number will be returned.

Example: DATEADD(day, DATEADD(day, -7, GETDATE()), GETDATE()) return 7 days.

How to execute string (custom built sql query ) in a stored procedure

sp_executesql executes string in a stored procedure. It is used in more complex queries which we need to build concatenating strings. The string is run as a batch. The following example gets the TOP records from a table matching predefined conditions.

CREATE PROCEDURE [GetTOPRecords]
@count varchar(6),
@conditions nvarchar(300)
AS
BEGIN

DECLARE @SQL nvarchar(1000)

SET
@SQL='SELECT
TOP '+ @count + ' * FROM RecordsTable
WHERE ' + @conditions

EXEC sp_executesql @SQL

END


The @count and @conditions are passed as parameters. The execution string is built run-time and executed. If you don't need any conditions here just pass the string ' 1=1 ' as @conditions.

A tip for avoiding null values in aggregate functions in sql

Sometimes the aggreagete functions in SQL returns null value - when the data doesn't meet selection criteria defined in the query.
We can return 0 instead NULL with (ISNULL (column, 0)).

For example:
SELECT
(ISNULL (SUM (salary), 0)) As Total
FROM Salaries
WHERE
UserId=@UserId

Using such statement avoids checking for null values in the code.

Getting the the newly created primary key after sql insert operation

INSERT INTO Table(COL1) VALUES('213')

SELECT @@IDENTITY

or

Return SCOPE_IDENTITY()

or

Return IDENT_CURRENT('TableName')


SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session.

SCOPE_IDENTITY returns values inserted only within the current scope.

@@IDENTITY is not limited to a specific scope. It returns the last identity value after performing all scopes (including triggers).


IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. In othe words it teturns the last value inserted into 'Tablename'

Add/remove columns, changing default values in a SQL Server 2005 table

Add new column in a table with checking for existence.
IF NOT EXISTS(SELECT * FROM syscolumns WHERE id=object_id('ColumnName') and name='TableName')
 ALTER TABLE TableName ADD ColumnName bit not null default 0

Changing default values in a SQL Server 2005 table
ALTER TABLE [Table]  ADD CONSTRAINT
    ConstrName  DEFAULT 0 FOR ColumnName