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

Showing persistently hidden columns in Excel

Sometimes......rare occasions, columns get hidden and won't unhide.

Sub Unhide_cols()
    On Error GoTo err_Unhide_cols

    Dim oWS As Excel.Worksheet
    Set oWS = Application.Worksheets("2013 EOD Account Watch List")
 
    With Application.Worksheets(oWS.Name)
        '.Range("A2").Activate
        .Columns("K").Select
        Selection.EntireColumn.Hidden = False
     
        .Columns("A:I").Select
        Selection.EntireColumn.Hidden = True
     
        Selection.EntireColumn.Hidden = False
                     
        .Columns("A:I").AutoFit
        .Columns("B").ColumnWidth = 20
     
    End With

exit_Unhide_cols:
    Set oWS = Nothing
    Exit Sub
err_Unhide_cols:
    MsgBox Err.Number & " " & Err.Description
    GoTo exit_Unhide_cols
End Sub

CAST vs CONVERT

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

SQL Query to find missing rows between two related tables

SELECT A.ABC_ID, A.VAL WHERE NOT EXISTS 
   (SELECT * FROM B WHERE B.ABC_ID = A.ABC_ID AND B.VAL = A.VAL)

or

SELECT A.ABC_ID, A.VAL WHERE VAL NOT IN 
    (SELECT VAL FROM B WHERE B.ABC_ID = A.ABC_ID)

or

SELECT A.ABC_ID, A.VAL LEFT OUTER JOIN B 
    ON A.ABC_ID = B.ABC_ID AND A.VAL = B.VAL WHERE B.VAL IS NULL

T-SQL check for Integer value

declare @string as varchar(20)
set @string = '1.01'

select 'test' as col1,
case
WHEN ISNUMERIC(@string) = 0 THEN 0
WHEN @string LIKE '%[^-+ 0-9]%' THEN 0
WHEN CAST(@string AS NUMERIC(38, 0))
 NOT BETWEEN -2147483648. AND 2147483647. THEN 0
ELSE 1
end as intCol