Transaction Isolation levels

I was looking for some good read on transaction isolatio levels.

Following is a good article.

Ref: http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx

Limitation of Update through views

When modifying data through a view (that is, using INSERT or UPDATE statements) certain limitations exist depending upon the type of view.

1. Views that access multiple tables can only modify one of the tables in the view.
2. Views that use functions, specify DISTINCT, or utilize the GROUP BY clause may not be updated.
3. Views having columns with derived (i.e., computed) data in the SELECT-list
4. Views that do not contain all columns defined as NOT NULL from the tables from which they were defined

ref:http://www.craigsmullins.com/cnr_0299b.htm

OPTION (MAXDOP 1)

If you want a query to run on a single processor in a multiprocessor system, use this option.

Ref:http://www.mssqltips.com/tip.asp?tip=1047

Error TF80012

Today while trying to upload my Test cases to VSTS through an excel i got following error.

TF80012: The document cannot be opened because there is a problem with the installation of Visual Studio 2008 Team Foundation Office integration components. Please see the Team Foundation Installation Guide for more information.

It was working fine perfectly last week. I got this error with both VSTS 2008 and VSTS 2010.

Following solution i tried and it worked like magic:


Run the following command (from an Elevated Command Prompt if running on Windows Vista or later, or Windows Server 2008 or later):
Regsvr32.exe "C:\Program Files\Microsoft Visual Studio 8.0\Common7\IDE\PrivateAssemblies\TFSOfficeAdd-in.dll"

(Update the path, for me it was d: and Visual Studio 9 and 10)

Ref: http://support.microsoft.com/kb/947865

SQL Server Management Studio (SSMS) Shortcuts

Some shortcuts that I use quite frequntly are

1. CTRL+R To hide the Result Window
2. For getting the definition of a SP/Function: I define a shortcut in the Keyboard Shortcuts for SP_HELPTEXT

Go to tools--> Options-->Environment-->Keyboard, I have defined CTRL+3 as SP_Helptext. Now whenever I am going through a SP/ function and come across another SP all I have to do is to select the SP name and Press CTRL+3. I get the SP definition immediately in the result window. This shorcut helps me a lot as I do not have to type/copy paste SP_Hleptext to see the definition of the SP.

3.For Commenting and Uncommenting

Commet: CTRL+K followed by CTRL+C
UnComment: CTRL+K followed by CTRL+U

Again I do not have to move my hands to the mouse to sue toolbar to comment few lines of code at once.

4.To Execute a SQl Statement: its F5. its probably the first shortcut that we learn in management studio

5. To Open a new Query Editior Window

CTRL+ N

6. To Change Database Context

CTRL+U

7. I want to search for a particular Keyword throughout the entire solution,

CTRL+SHFT+F

sp_msforeachdb,sp_msforeachtable, sp_spaceused

sp_msforeachdb
This SP can be used to execute same T-SQL statement for all the databases on a server without specifying the Database naem explicitely.

For example if I want to perform Shrink operation on all teh databases ona server once a month, I can specify following query in a SQL Job

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

sp_msforeachtable

Siliarly, If you want to perform same operation on all the tables ina database sp_msforeach table is averygood option. For exaple, if I want to build the Indexes of each table in a databse, I can use it in following way:

GO
EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD '
GO


Ref: http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx

sp_spaceused

this is a a wonderful SP which returns the disk space used by a table/Indexed View.

Delete Duplicate Data From a Table

USE SAMPLE
GO
CREATE TABLE #TAB1
(ID INT,
NAME VARCHAR(10)
)

INSERT INTO #TAB1
SELECT 1, 'JOHN'

INSERT INTO #TAB1
SELECT 1, 'JOHN'


INSERT INTO #TAB1
SELECT 2, 'Stephen'

INSERT INTO #TAB1
SELECT 3, 'Sonia'

INSERT INTO #TAB1
SELECT 4, 'RIYA'

INSERT INTO #TAB1
SELECT 4, 'RIYA'

INSERT INTO #TAB1
SELECT 4, 'RIYA'

--SELECT * FROM #TAB1

--To Identify Duplicates
SELECT * FROM #TAB1
GROUP BY ID, NAME HAVING COUNT(*) > 1


--To Delete Duplicates from the Table

DECLARE @DupCount INT
DECLARE @NonDupCount INT
DECLARE @TotalCount INT
DECLARE @NAME VARCHAR(10)
DECLARE @ID INT

SELECT @TotalCount = COUNT(*) FROM #TAB1

SELECT * FROM #TAB1
GROUP BY ID, NAME HAVING COUNT(*) = 1
SELECT distinct ID, name FROM #TAB1

SET @NonDupCount = @@ROWCOUNT

SELECT @DupCount = @TotalCount - @NonDupCount
--Select distinct * from #TAB1

Select @DupCount as dup, @TotalCount as total, @NonDupCount as nondup

set @DupCount=@DupCount+4
SET ROWCOUNT 1

WHILE @DupCount > 0
BEGIN
--SELECT TOP 1 NAME , ID FROM #TAB1 GROUP BY ID, NAME HAVING COUNT(*) > 1
SELECT TOP 1 @NAME = NAME , @ID = ID FROM #TAB1 GROUP BY ID, NAME HAVING COUNT(*) > 1
--Select @name as Name , @id as ID

DELETE FROM #TAB1 WHERE NAME = @NAME AND ID = @ID
Select @name =null, @id =0
SET @DupCount = @DupCount - 1
END
SET ROWCOUNT 0
Select * from #TAB1


DROP TABLE #TAB1

Error 1222: Lock Request Time Out Exceeded

Today I came across this error. We were trying to shrink a database. the shrink execute for almost 4 hours and then it exited with error message:

Shrink failed for database "DBNAME"
Lock request timeout Period Exceeded.

So here is what you are supposed to do:
1. Execute the SP_Who2 to see if there is any user logged in who is executing a query which leads to deadlock.
2. Execute SO_LOCK to see all locks that are applied on the database.
3. From the result set of SP_LOCK figure out if there is any dbid = 2 and ObjID in 1,2,3.
4. If yes then Kill that spid by using Kill method.

Finding Frangmentation of Indexes

Here is the query which will tell you How to find the fragmentation of a particular Index in Database


declare @database_id int
select @database_id = db_id()



SELECT
i.name AS [Index_Name],
CAST(i.index_id AS int) AS [Index_ID],
fi.index_depth AS [Depth],
fi.page_count AS [Pages],
fi.record_count AS [Rows],
fi.min_record_size_in_bytes AS [Minimu mRecordSize],
fi.max_record_size_in_bytes AS [MaximumRecordSize],
fi.avg_record_size_in_bytes AS [AverageRecordSize],
fi.forwarded_record_count AS [ForwardedRecords],
fi.avg_page_space_used_in_percent AS [AveragePageDensity],
fi.index_type_desc AS [IndexType],
fi.partition_number AS [PartitionNumber],
fi.ghost_record_count AS [GhostRows],
fi.version_ghost_record_count AS [VersionGhostRows],
fi.avg_fragmentation_in_percent AS [AverageFragmentation]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0
and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.dm_db_index_physical_stats
(@database_id, NULL, NULL, NULL, 'SAMPLED') AS fi
ON fi.object_id=CAST(i.object_id AS int)
AND fi.index_id=CAST(i.index_id AS int)
WHERE
(i.name='MyIndexName')
and((tbl.name='MyTableName' and
SCHEMA_NAME(tbl.schema_id)='dbo'))
ORDER BY
[Index_Name] ASC

Rebuild vs Reorganize

Today I came across a scenario where a batch job was getting deadlocked with another Long running query. This job had a peice of code to drop and create some indexes on a table which was being used by the query. As the query was supposed to get executed for more than 2 hours to return the result set, the batch job was stuck for all this while.

When I looked into the code, there was no need to drop and create the indexes as the data getting inserted was not much. But the table containing data had almost 6 million rows.

Hence it was necessary to either rebuild or reorganise the indexes frequently. Out of my R&D this is what I figured out:

a. Rebuilng takes much lesser time than reorganizing.
b. Rebuilding Indexes creates a deadlock if some other query is accessing the Table whereas Reorganizing Indexes gets executes smoothly.
c. Reorganizing indexes took much lesser time in second run.

Also, above behavior is expected. For further information on this topic, refer msdn

http://technet.microsoft.com/en-us/library/ms189858.aspx

deleting file from sharepoint through SSIS Package

Today I came across a scenario where some XML files are to be read from sharepoint and to be deleted after downloading.

Thanks to msdn, I googled and found the code. I tested it on a console application and it is able to delete the file from sharepoint.

ref: http://msdn.microsoft.com/en-us/library/ms879334(EXCHG.65).aspx

Insert Data into Indentityt column in a table having only one(identity Column)

I came across a very interesting scenario by a friend who had a recent job interview:

How to insert data into a table which has only one column and the column is an identity column

There are 2 options
1. Set Insert_identity ON
2. Use following query



create table Table1 (Col1 int identity(1,1))
go
insert into Table1 default values

select * from Table1