Sinlge user mode in SQL Server

I was trying to change the isolation level of my database using the ALTER DATABASE statement and the query was running for hours to execute it, because there were ongoing transactions in the db which is preventing the isolation change. I have solved this problem by taking the DB into single user mode before running the alter statement using below script

if(charindex('Microsoft SQL Server 2005',@@version) > 0)
begin
declare @sql varchar(8000)
select @sql = '
ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;'
Exec(@sql)
end

Any changes to Database level properties in SQL Server can be done easily through the use of single user mode, which permits only one connection to be made to the database at any time.

How to combine Multiple Rows into Single Column in SQL Server

I have come across a link where you can combine multiple rows into single column using the COALESCE function.It is very simple as shown below

CREATE TABLE WeekDays
([Name] varchar(40))
INSERT INTO WeekDays VALUES('Mon');
INSERT INTO WeekDays VALUES('Tue');
INSERT INTO WeekDays VALUES('Wed');

DECLARE @str VARCHAR(2000)
select @str = COALESCE(@str + ',', '') + [Name]
from WeekDays
SELECT @str

Output :Mon,Tue,Wed

You can see the original article on
http://www.sqlservercurry.com/2008/06/combine-multiple-rows-into-one-row.html

How to import Database Schema to XML

I often get questions on how to import database schema into XML file for doing activities like Data Comaprison etc. I have written a small script to generate the database schema including the following

• Column Details ( Name, data type ,length etc)
• Primary Key
• Foreign Key
• Indexes

Sample Script

SELECT a.name TableName,
( SELECT
c.name ColumnName,type_name(c.xusertype) DataType,
CASE WHEN type_name(c.xusertype)='NUMERIC' THEN CAST(c.prec AS SMALLINT)
WHEN type_name(c.xusertype)='UNIQUEIDENTIFIER' THEN NULL
WHEN type_name(c.xusertype)='BIGINT' THEN
CASE WHEN colstat =1 THEN CAST(IDENT_SEED(a.name) AS SMALLINT)
END
ELSE CAST(c.prec AS SMALLINT)
END DataLength,
CASE WHEN type_name(c.xusertype)='NUMERIC' THEN c.scale
WHEN type_name(c.xusertype)='BIGINT' THEN
CASE WHEN colstat =1 THEN CAST( IDENT_INCR(a.name) AS INT)
END
ELSE NULL
END Scale,
CAST(c.isnullable AS BIT) As IsNullable,NULL AS DataDefault,NULL AS DefConstraintName,
CASE WHEN colstat=1 THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT)
END AS IsIdentColumn
FROM SYSColumns c
WHERE c.id = OBJECT_ID(a.name)
and a.id = c.id
AND C.CDEFAULT =0
FOR XML AUTO, TYPE
) columns,
(select 'UID' ColumnName,f.name PrimaryKeyName, f.type_desc PrimaryKeyType
from sys.indexes f
where f.object_id = a.id
AND f.NAME IS NOT NULL
AND f.Is_Primary_Key =1
AND OBJECT_ID > 97
FOR XML AUTO, TYPE
)PrimaryKey,
(Select
object_name(rkeyid) Parent_Table,object_name(fkeyid) Child_Table, object_name(constid) FKey_Name, c1.name FKey_Col,c2.name Ref_KeyCol
From
sys.sysforeignkeys s
Inner join sys.syscolumns c1
on ( s.fkeyid = c1.id And s.fkey = c1.colid )
Inner join syscolumns c2
on ( s.rkeyid = c2.id And s.rkey = c2.colid )
where s.fkeyid = a.id
FOR XML RAW,TYPE
) ForeignKey,
(select f.name IndexName ,DBO.fGetIndexCols (object_NAME(f.object_id), f.index_id ) IndexColumn,
f.type_desc IndexType
from sys.indexes f
where f.object_id = a.id
AND f.NAME IS NOT NULL
AND f.Is_Primary_Key =0
AND OBJECT_ID > 97
FOR XML AUTO, TYPE
) Indexes
from sysobjects a
where a.xtype ='u'
FOR XML PATH('Table'), ROOT('TableDetails')

SQL Server 2008 – Inline variable initialization

Microsoft has extended the support of inline variable initialization feature from the programming languages in to T-SQL in SQL Server 2008.

When we want to declare and initialize a value to variable in T- SQL, we need to do it is two steps (declaration & initialization) as shown in the example

DECLARE @V_Value DATETIME
SET @V_Value =GETDATE()

In SQL Server 2008 this can be simplified by combining both the lines into a single steps as we do in programming languages

DECLARE @V_Value DATETIME = GETDATE()

Even though it is a very small feature, it helps the developers who have the technical background for programmig languages like JAVA, C#.NET , VB.NET etc

Turn Off - Prevent saving changes in SQL Server 2008

I was working with SQL Server 2008 for quite some time and noticed a strange behavior (which is not in previous versions of SQL Server) when I try to save a table in Management Studio that requires table to be dropped and recreated (e.g. Try to ADD a new NOT NULL column to the table which is having data) .I get the following warning message

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

I though it is bug with SQL Server, but later cam to know it is the expected behavior and can be turned of by unchecking "Prevent saving changes that require table re-creation" in the Designer properties. Please refer to the below screen shot

How to upgrade database from SQL Server 2005 to SQL Server 2008

The following steps should be carried out upgrade a database from SQL Server 2005 to SQL Server 2008

Step 1:
Detach the database files from SQL Server 2005 and attach to SQL Server 2008 Server

Step 2:
Execute the below command to update the index and table Statistics in the newly attached database
SP_UPDATESTATS
It is recommended to execute update statistics separately for each table with full scan as given below
UPDATE STATISTICS . WITH FULLSCAN, NORECOMPUTE

Step 3:
Execute the below command to rest all the counters DBCC UPDATEUSAGE('')

Step 4:
Change the Database Compatibility Level as specified
Go to Database Properties à Options à Compatibility Level to à SQL Server 2008(100)

Paging using LINQ

SQL Server supports many ways of implementing paging in the applications. With the introduction of LINQ (Language Integrated Query) support in VS 2008 paging is further made simple. Please follow the below steps to implement paging for Employee List screen

Step 1 :
Write a method to retrieve the employee list from the table “Employee” using LINQ as shown in the method “GetEmployeeList”

public List GetEmployeeList(int PageNo ,out int TotalPages)
{
Int NoofRecordsPerPage =10;
List oResultOut = new List();
var query = (from p in dc.Employees
select p).ToList();
if (PageNo == 0)
TotalPages = query.Count / NoofRecordsPerPage;

oResultOut = query.Skip(PageNo * NoofRecordsPerPage)
.Take(NoofRecordsPerPage)
.ToList();

}
The parameter “PageNo” is used to determine the current page of Employee List view. It is often required to know the total number of pages, the List view will span during the first execution of the query. The above method will fill the total number of pages using the “query.Count” attribute.
The query.Skip() will filter the records of the previous pages and the query.Take() method will show only the records qualified for the current page. The number of records to be displayed on each page can be controlled using the variable “NoofRecordsPerPage” .

Step 2 :
Calling the “GetEmplyeeList” from the UI based on current page selected by the user in the Employee List view

// assume the user sees the first page
List oResult = new List();
int TotalPages =0;
oResult = GetEmployeeList( 0, out Totalages);

// assume the user sees the fifth page
List oResult = new List();
int TotalPages =0;
oResult = GetEmployeeList( 5, out Totalages);

We will see the benefits of using LINQ and how to implement paging for retrievals using the stored procedures using DLINQ in my next post