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

How to check if JavaScript function exists?

Checking if function exists differs from the regular object checking. In order to ensure a function exists you can use typeof operator for the verification.

if (typeof myFunctionName === 'function') {
     myFunctionName();
}

Rebuild SQL database indexes

In this article I will not be going through the matter of indexes.The purpose of the post is to provide a quick reference to a manually triggered re-indexing process. What is important to be understood is the fill-factor for every particular index. The fill-factor value defines the percentage of space in every index page to be filled with data and respectively defines the space left as free space for future growth.

Here is the recommended fill-factor values based on the reads-writes balance:
Tables with low number of updates
- ex. 100:1 read to write ratio: 100% fill-factor
Tables with high  number of updates 
- read to write ratio less than 1 (more writes than read) : 50%-70% fill-factor
Tables with average number of updates - 80%-90% fill-factor

DECLARE @from int
DECLARE @to int
DECLARE @fillFactor int
SET @from=1
SET @to=10000
SET @fillFactor=90
DECLARE @dateFrom datetime
DECLARE @table varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name from(
SELECT  ROW_NUMBER() OVER (ORDER BY table_name) AS [SortNum], table_name FROM information_schema.tables
WHERE table_type = 'base table')
AS t1 WHERE sortnum>=@from and sortnum<=@to
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY SET @dateFrom = getdate()
DBCC DBREINDEX(@table,' ',@fillFactor)
print 'Table ['+@table+'] indexed for: '+ CAST( DATEDIFF ( ms , @dateFrom , getDate() ) as nvarchar(10)) + ' ms'
END TRY
BEGIN CATCH
print 'Number of errors: '+CAST(ISNULL( ERROR_NUMBER(),'') as nvarchar)
print 'Error: '+ISNULL(ERROR_MESSAGE(),'')
END CATCH

FETCH NEXT FROM TableCursor INTO @table
END
CLOSE TableCursor
DEALLOCATE TableCursor