Separate Domain from email address in Excel

=RIGHT(A4,LEN(A4)-SEARCH("@",A4,1))

List of database Constraints

USE GSPrime4;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint
, SCHEMA_NAME(schema_id) AS SchemaName
, OBJECT_NAME(parent_object_id) AS TableName
, type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%Constraint'
--and NameofConstraint like '%PK_%'
order by OBJECT_NAME(OBJECT_ID)

GO

Select text to left of @

SELECT userID, emailID, left(emailId, charindex('@',emailId)-1)
  FROM [TaxFormsDelivery].[dbo].[TFDP_Client_Users]
  where UserID  = left(emailId, charindex('@',emailId)-1)

Excel CONCATENATE and TEXT functions

=CONCATENATE(A1," ",TEXT(B1,"0000"))

Fragmentation of Views

SELECT DB_NAME() [database], OBJECT_NAME(a.object_id) [table], idx.[name], a.object_id, a.index_id, a.index_type_desc, a.alloc_unit_type_desc, avg_fragmentation_in_percent, a.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') a
JOIN sys.indexes idx ON idx.object_id = a.object_id AND idx.index_id = a.index_id
WHERE page_count > 1000
AND avg_fragmentation_in_percent > 10.0
ORDER BY avg_fragmentation_in_percent DESC

SQL Server Transaction example

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Calculating execution frequency

METHOD #1

USE [databasename]
GO
CREATE PROCEDURE procExecutionFrequency  @Database sysnameASBEGIN SELECT cp.objtype 'ObjectType' , min(DB_NAME(st.dbid)) +'.'+OBJECT_SCHEMA_NAME(st.objectid,dbid) +'.' +OBJECT_NAME(st.objectid,dbid) 'ObjectName' , max(cp.usecounts) 'ExecutionFrequency' FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st WHERE cp.objtype in ( 'proc', 'view')  AND DB_NAME(st.dbid) = @Database GROUP BY   cp.objtype,   cp.plan_handle,   OBJECT_SCHEMA_NAME(objectid,st.dbid),   OBJECT_NAME(objectid,st.dbid)  ORDER BY ObjectType, ExecutionFrequency descENDGO
EXEC procExecutionFrequency 'GSPrime4'
GO

---------------------------------------------------------------------

METHOD #2

CREATE TABLE dbo.tblRTPVrequests (
rtpvID int IDENTITY (1, 1) NOT NULL ,
rtpvDateTime datetime ,

CONSTRAINT PK_RTPVrequests PRIMARY KEY CLUSTERED ( rtpvID )
)
GO

--update the counter

insert into dbo.tblRTPVrequests values(getdate())
select * from dbo.tblRTPVrequests

memory footprint of views

SELECT * FROM sys.views

select [name]
, type_desc
, space_used_in_kb = (page_count * 8.0)
, space_used_in_mb = (page_count * 8.0 / 1024.0)
from
    sys.indexes I inner join sys.dm_db_index_physical_stats(db_id(), object_id('.'), null, null, null) P on I.[object_id] = P.[object_id]
and I.[index_id] = P.[index_id]

Create Date of SQL Server Index

select crdate, i.name, object_name(o.id)
from sysindexes i join sysobjects o ON o.id = i.id
where i.name IN ('ndx_move','ndx_2')
order by crdate desc

SQL Server 2005 Activity Monitor

http://www.dedicatedsqlserver.com/HowTo/SQLServer_Activity_Monitor.aspx