things to remember while using SQL Server

For SQL SERVER ONLY

Sql Server Links

Find All Triggers particular to database

select so.name, text
from sysobjects so, syscomments sc
where type = 'TR'
and so.id = sc.id
and text like '%YoutTableName%'

All Trigger In the DataBase Sql Server

1.

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR' 

2.

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR'
For Sql Server 2000
SELECT 
     o.name AS trigger_name 
    ,'x' AS trigger_owner 
    /*USER_NAME(o.uid)*/ 
    ,s.name AS table_schema 
    ,OBJECT_NAME(o.parent_obj) AS table_name 
    ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects AS o 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sysobjects AS o2 
    ON o.parent_obj = o2.id 

INNER JOIN sysusers AS s 
    ON o2.uid = s.uid 

WHERE o.type = 'TR'

Find references of tables,stored proc,functions,...etc

----Option 1SELECT DISTINCT so.nameFROM syscomments scINNER JOIN sysobjects so ON sc.id=so.idWHERE sc.TEXT LIKE '%tablename%'----Option 2SELECT DISTINCT o.name, o.xtypeFROM syscomments cINNER JOIN sysobjects o ON c.id=o.idWHERE c.TEXT LIKE '%tablename%'

Find lock on Table and kill lock on table in sql server

 SELECT

    OBJECT_NAME(P.object_id) AS TableName,

    Resource_type,

    request_session_id

FROM

    sys.dm_tran_locks L

    join sys.partitions P

ON L.resource_associated_entity_id = p.hobt_id

WHERE   OBJECT_NAME(P.object_id) = 'MstPD_CADNo'

Kill request_session_id;

add linked server in sql server

Exec sp_addlinkedserver 
@server ='MWNJDB',
@srvproduct ='' ,
@provider ='SQLNCLI',
@datasrc ='MWNJDB',
@location ='',
@provstr ='',
@catalog =''

find duplicate rows...in sql server

http://stackoverflow.com/questions/2112618/finding-duplicate-rows-in-sql-server

Sql server Import CSV File

DECLARE @tableName varchar(10);

DECLARE @importFile varchar(10),@importQuery varchar(100);

set @importFile='France_AD_Rates.csv';

SET @tableName = '#France_AD_Rates';

SET @importQuery= 'France_AD_Rates.csv';

EXEC( 'SELECT * INTO ' + @tableName + 'from OpenRowset(

''MSDASQL'',''Driver={Microsoft Text Driver (*.txt;*.csv)};DefaultDir=D:\;'',''SELECT * FROM ' + @importFile + ''')')

Csv file import in sql server

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};

DEFAULTDIR=D:\;','SELECT * FROM France_AD_Rates.csv')

Replacement code for Cursor Using Temp Tables.

There Should be 1 Primary Key In the Table.


Declare @id varchar(100)
 select CADNo into #TempOtherRing from ##tempHead ;

 while exists(select CADNo from #TempOtherRing)                                                  
  BEGIN                                
SELECT TOP 1 @id=CADNo  FROM #TempOtherRing;  
-----Here From Your Primary Key You Can Fetch any Record in the table.
----CADNo  is primary key in my table.
----Fetch Records from Main Table Using CADNo.
----Perform Your Operations.
 delete from #TempOtherRing  where CADNo=@id;      
 END



 http://weblogs.asp.net/jgalloway/archive/2007/07/18/the-real-reason-select-queries-are-bad-index-coverage.aspx

For Millions of Record Use full-text index On DataBases

http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005

Indexing In Sql Server 
From Basic To Expert
http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server

https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

http://www.sqlteam.com/article/sql-server-indexes-the-basics

http://odetocode.com/articles/70.aspx

http://msdn.microsoft.com/en-us/library/ms190457.aspx

Visual Representation of SQL JOINS

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

SQL Server Optimization 
http://technet.microsoft.com/en-us/library/aa964133%28v=sql.90%29.aspx

SQL SERVER – Shrinking Truncate Log File – Log Full

use DFE2

Go

ALTER DATABASE DFE2 SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(ALLCO_Log, 1)

ALTER DATABASE DFE2 SET RECOVERY FULL WITH NO_WAIT

USE DatabaseName
GODBCC SHRINKFILE(<TransactionLogName>, 1)BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLYDBCC SHRINKFILE(<TransactionLogName>, 1)GO

http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

Database Talks while Making Connection Using Sql Server with Dot Net

http://www.mstecharticles.com/

Diff Between Candidate-Keys-and-Primary-Key
http://dotnetslackers.com/articles/sql/Difference-Between-Candidate-Keys-and-Primary-Key.aspx

AdventureWorks Databases – 2012, 2008R2 and 2008

http://msftdbprodsamples.codeplex.com/releases/view/93587

Connection String For SQL SERVER 2008 LOCAL

Data Source=.;Initial Catalog=AdventureWorks2008R2;User ID=sa;Password=sqlserver