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

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

Change Collation of a Mirrored Databse

If you have already tried this directly you have probably seen messages like the action is not available on databases involved in mirroring or database unable to be locked and so on.

So first you have to the database from the mirror session, then do the necessary change actions and restore the mirror session. Ex,

ALTER DATABASE SET PARTNER OFF
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE COLLATE SQL_Latin1_General_CP1_CI_AS

On the mirrored database:

RESTORE DATABASE WITH RECOVERY

Error Messages:

• The database could not be exclusively locked to perform the operation. 
• The operation cannot be performed on database it is involved in a database mirroring session or an availability group 

How to find the largest sql objects in an SQL Server database

Sometimes you may need know how big some tables are, how many rows they have and how much disk space they use.

The following query looks for the biggest tables in size (MB) and number of rows:

SELECT * FROM (

SELECT 

    tbl.NAME AS TableName,

    ind.name as indexName,

    sum(p.rows) as RowCounts,

    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 

    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 

    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM 

    sys.tables tbl INNER JOIN 

    sys.indexes ind ON tbl.OBJECT_ID = ind.object_id INNER JOIN 

    sys.partitions p ON ind.object_id = p.OBJECT_ID AND ind.index_id = p.index_id INNER JOIN 

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE 

    tbl.NAME NOT LIKE 'dt%' AND

    ind.OBJECT_ID > 255 AND   

    ind.index_id <= 1

GROUP BY     tbl.NAME, ind.object_id, ind.index_id, ind.name 

) as t1  ORDER BY TotalSpaceMB desc ,UsedSpaceMB desc

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

100% CPU usage by SQL Server - Look for Active (running) SQL queries

I found a very useful posts regarding analysis of currently running SQL queries and high SQL server load in Pinal Dave's blog.

http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/

http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/

Using these tricks the culprit of the high load can be easily indicated.

Until the problem resolution is implemented KILL [session_id] can stop the problematic query w/o server restart or long waiting.

SQL Server Express and IIS: Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path.

SQL Express user instancing fails with the default IIS 7.x configuration. To solve this you can do the following:

1. Create new application pool in IIS
2. Open Application Pool Advanced settings
3. Change Identity from ApplicationPoolIdentity to NETWORK SERVICE and confirm
4. Change the application pool that the web application belongs to by opening Advanced setting of the web application

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 Server 2005 - Out Of Memory exceptions connected to its RAM usage

After the allocated RAM for SQL Server exceeds the System memory unexpected behavior of the server is present.

Here is a quick guide for configuring Sql server.

http://www.mattec.com/service/appnotes/820-0019.pdf

And remember: Never allocate less then 1 GB of RAM to SQL Server :)

How to obtain current SQL Server version using sql query

SELECT SERVERPROPERTY('productversion') As Version, SERVERPROPERTY ('edition') As Edition, SERVERPROPERTY ('productlevel') As Updates

The first columns shows current version as number, the second one is the edition (standard, express etc.) and the third one shows which version level the server belongs to (SP1, SP2 etc.)

Trick to start web application with another SQL Server database

The following exception is thrown when you try to use one application with the another database. The problem is caused by asp.net membership provider which cannot recognize its schema.

The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_reqsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.

First of all be sure the aspnet membership provider is registered on that database using aspnet_regsql.exe.

Then the solution is: taking the web application offline and then online using Web Site Administration Tool. That makes some changes in web.config in the pages tag and globalization if needed. Then you can go!

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.

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.

SQL Server Express permissions

Fix for exceptions like these:
"CREATE DATABASE permission denied in database 'master'",
"EXECUTE permission denied on object "
"SELECT permission denied on object"

Asp.NET uses the NETWORK SERVICE account (for Windows Server 2003) and ASPNET user for Windows XP. If we use windows authetincation for using sql express we need to add these users as sql server logins:
SQL Server manager -> Security -> Logins -> add "NT AUTHORITY\NETWORK SERVICE" or ASPNET
Then click on the properties menu of the login and make the login dbcreator. This will fix the first of the exceptions above. Then with User Mapping setting we can add permissions explicitly to the desired database -> dbowner gives all needed permission.

How to use SQL Express database for more than one application

Somethimes we need to use one database for more than one application, web, desktop or service.
The only thing to do is to get rid of the generation of a new user instance by the connection string in web.config. Assure that User Instance=False in the connection string and try again.

Problems with using SQL Server instance by Windows remote desktop

'Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.'

This problem occurs when the user uses remote desktop and logs into the system for the first time. If the user logs in at first locally and then uses remote desktop the problem doesn't occur.

Microsoft describe the problem in the article
http://support.microsoft.com/?id=896613
It is enough to request them to send you a link for the fix (windows update) and wait a few hours. The fix cannot be downloaded directly because of need of additional testing. But it works fine for me.

Using ASP.NET SQL Server Registration Tool (aspnet_regsql.exe) with SQL Express 2005 databases

The aspnet_regsql can be found in
[HDD Drive]:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

The command used for adding asp.net 2.0 membership stuff is

aspnet_regsql -A all
-C "Data Source=.\EXPRESS;Integrated Security=True;User Instance=True"
-d "[database path]"

-d - specifies the database by actual database path

-A all|m|r|p|c|w -

Adds support for one or more ASP.NET application services. Service identifiers can be specified together or separately. The following identifiers are used for ASP.NET application services:

all - All services, including common tables and stored procedures shared by the services

m - Membership

r - Role Manager

p - Profile

c - Web Parts Personalization

w - Web Events

-C - Specifies the connection string to the computer running SQL Server where the database will be installed, or is already installed. This option is not necessary if you specify only the server (-S) and login (-U and -P, or -E) information.

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

Getting fixed number of rows with SQL Select query

The syntax for SQL Server:
SELECT TOP 10 id, name email
FROM person

The syntax for MySQL:
SELECT id, name email
FROM person
LIMIT 10

These examples get the first (top) 10 rows in the query.

I will describe also the paging technology of the asp.net.