T-SQL triggers on a table

 
Finding what triggers on a table using t-sql can be performed in T-SQL using the following Query

select t.*

from sysobjects s

inner join sys.triggers t on t.parent_id=s.id

where s.name = '<>'

Generating a new GUID value for insert

The other day I needed to insert some records into a table but I received an error on a column called GUID.  This unique reference column required a value similar to the following: 1A2BBCA2-7D80-4F34-9EDF-025203A721A3. 

For a 100 records what on earth was I going to enter.  With a bit of searching on internet I found that using the command newid() allowed me to create new value (Thank you Devx.com http://www.devx.com/tips/Tip/13951)

So for example

insert into tableA
select newid(), column 1, column 2 from tableB

SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance - script

The following document was from the great Pinal Dave on from the following website: http://www.sqlauthority.com/. This article was extremely useful when I was troubleshooting performance issues in Dynamics GP:


Just a day ago, while using one index I was not able to get the desired performance from the table where it was applied. I just looked for its fragmentation and found it was heavily fragmented. After I reorganized index it worked perfectly fine. Here is the quick script I wrote to find fragmentation of the database for all the indexes.

SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
GO

You can REBUILD or REORGANIZE Index and improve performance. Here is article SQL SERVER - Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script for how to do it.

Reference : Pinal Dave (http://www.sqlauthority.com/)

SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance

The following document was found in www.sqlauthority.com and was extremely useful.

Index Rebuild : This process drops the existing Index and Recreates the index.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Reference : Pinal Dave (http://www.SQLAuthority.com)

Reducing databases with an ever expanding transaction log

Reducing a database due to its transaction log expanding to a large size run the following steps:

All done in SQL Server Management Studio

1. Run the following t-sql command: DUMP TRANSACTION [db_name] WITH NO_LOG
2.Run a shrink on the database