SQL Server 2008 DMV's Relationship mapping

Microsoft has introduced the concept of DMV’s (Dynamic Management Views) in SQL Server 2005 and concept is extended to SQL Server 2008 also with additional DMV's for mirroring , memory management etc.

Microsoft has recently released the latest “System Views Map” for SQL Server 2008 which shows the key system views and the relationships between them. You can download the latest System Views Map at

http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en

The updated “System views Map” for SQ Server 2005 also can be downloaded at

http://www.microsoft.com/downloads/details.aspx?familyid=2EC9E842-40BE-4321-9B56-92FD3860FB32&displaylang=en

SQL Server Hot Fixes at one place

Microsoft is now days releasing lot of hot fixes for its products’ recently found one TechNet blog which is specific to hot fixes. This is one place where you can find the cumulative update of each hot fixes / updates released for SQL Server, also it has the release note information linked to the knowledge base articles.

This blog also contains hot fix information for other Microsoft products such as Windows, Visual Studio, and IE etc. The original blog can be find at

http://blogs.technet.com/hot/archive/tags/SQL+Server/default.aspx

Reasons for slower Delete in SQL Server

Delete statement plays a major role in many of the database maintenance activities. Extreme care should be taken before executing the DELETE statements
Before executing the DELETE statement it is better to check the “Estimated execution plan”, so that we can create proper indexes to speed up the execution .I have given some of the possible reasons for slower delete.

1. Locking / Blocking - If it is a production database process is having Lock / Block on the table

2. Fragmentation - The Index pages are fragmented due to excessive delete on the table (Defragment the Indexes and try again)

3. The table you are trying to delete is referred by many tables as foreign key and those columns are not indexed.

4. There might be hanging transactions on the table - Try to truncate the Log and do

5. You can also change the Recovery Mode to simple and try - but not advised in case of production DB

Also it is recommended to execute the DELETE statements in smaller batches so that the Log space & Roll back of the records can be easier as shown below

// assume you wanted to delete 100000 records, we can split them into 10 batches as
DECLARE @V_Count INT =0
WHILE @V_Count < 100000
BEGIN
DELETE TOP(10000)
FROM Table
WHERE COLUMN = Condition
SET @V_Count =@V_Count+10000
END

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')