Computed columns - Persisted Vs Non-persisted‏

http://www.sqlservercurry.com/2013/02/computed-columns-persisted-vs-non.html



There are two types of computed columns namely persisted and non-persisted. 

There are some major differences between these two

1. Non-persisted columns are calculated on the fly (ie when the SELECT query is executed) whereas persisted columns are calculated as soon as data is stored in the table.

2. Non-persisted columns do not consume any space as they are calculated only when you SELECT the column. Persisted columns consume space for the data

3. When you SELECT data from these columns Non-persisted columns are slower than Persisted columns

A computed column is not physically stored in the table, unless the column is marked PERSISTED.

Send Job failure description details when ever a job fails

USE [msdb];
GO

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
/* drop trigger trg_stepfailures */
Alter TRIGGER [dbo].[trg_stepfailures] ON [dbo].[sysjobhistory]
    FOR INSERT
AS
    DECLARE @strcmd VARCHAR(800) ,
        @strRecipient VARCHAR(500) ,
        @strMsg VARCHAR(2000) ,
        @strServer VARCHAR(255) ,
        @strTo VARCHAR(255);

    DECLARE @Subject VARCHAR(500);


    IF EXISTS ( SELECT  *
                FROM    inserted
                WHERE   run_status = 0
                        AND step_name NOT IN ('job outcome'))
        BEGIN
            SELECT  @strMsg = @@servername + '-Job: ' + sysjobs.name
                    + '. Step= ' + inserted.step_name + 'Message '
                    + inserted.message
            FROM    inserted
                    JOIN sysjobs ON inserted.job_id = sysjobs.job_id
            WHERE   inserted.run_status = 0;

            SELECT  @Subject = 'Job ' + sysjobs.name + ' Failed on Job Server'
                    + @@Servername
            FROM    inserted
                    JOIN sysjobs ON inserted.job_id = sysjobs.job_id
            WHERE   inserted.run_status = 0;

            SET @strRecipient = 'naresh.koudagani@xyz.com';
            EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DbMail', --uses the default profile
            @recipients = @strRecipient,
@subject = @Subject,
            @body = @strMsg,
@body_format = 'HTML'; --default is TEXT


        END;

How to Connect SSIS to Always on Availability Groups Listener:

 How to Connect SSIS to Always on Availability Groups Listener:

Step1: Open up Visual Studio

Step2: Tools->Connect to Database

Step3: Enter Listener name , then go to advanced

Step4: Change Multisubnetfailover=True from False

Step5: Test connection with your windows Account or SQL account

Step6:Create OLEDB Connection Manager

 From then create new OLEDB connection manager, choose FPSQL1Listener and the choose login, database name etc.

First a database connection must be made with multisubnet failover=true

Index and stats rebuild on Always on HADR VS Replication

HADR:
If I need to rebuild indexes, can I do this on the primary?
Index operations are fully logged and will be replicated to the secondaries.



Replication:
CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.
we need to rebuild stats also 

Disable or Enable all SQL Agent Jobs

declare @sql nvarchar(max) = '';
select
@sql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 0;
' from msdb.dbo.sysjobs
where enabled = 1  --1= Enabled, 0 is disabled
order by name;

print @sql;
--exec (@sql);

Disable or enable alerts , change all alerts notification Operator

--Check All Alerts:
SELECT * from msdb.[dbo].[sysalerts]


 --Disable All Alerts
 UPDATE msdb.[dbo].[sysalerts]
SET [enabled] = 0
WHERE [enabled] = 1


  --Enable All Alerts
 UPDATE msdb.[dbo].[sysalerts]
SET [enabled] = 0
WHERE [enabled] = 1



---Change Alerts Notify operators
SET NOCOUNT ON
DECLARE @Alert_Names TABLE
(
AlertName SYSNAME NOT NULL,
Operator_name Varchar(30) NULL
)
Declare @operator_name Varchar(30)='SQLOperDBA'
 Declare @notification_method int = 1 ;
INSERT INTO @Alert_Names(AlertName,Operator_name)
SELECT s.name,@operator_name
FROM msdb.[dbo].[sysalerts] s
WHERE s.Enabled = 0 --Optional filter
ORDER BY s.name

DECLARE @Alert_name SYSNAME
DECLARE @Alert_id UNIQUEIDENTIFIER


DECLARE ChangeOperator CURSOR FOR
SELECT Alertname,operator_name
FROM @Alert_Names


OPEN ChangeOperator
FETCH NEXT FROM ChangeOperator INTO @Alert_name,@operator_name

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC msdb.dbo.sp_add_notification  @alert_name, @operator_name,1

FETCH NEXT FROM ChangeOperator INTO @Alert_name,@operator_name

END

CLOSE ChangeOperator
DEALLOCATE ChangeOperator

SID in SQL Server Login and windows login

https://www.mssqltips.com/sqlservertip/2705/identifying-the-tie-between-logins-and-users/

For SQL Server-based login, the SID is generated by SQL Server.

For Windows users and groups, the SID matches the SID in Active Directory.

SQL profiler to trace trigger events

Use Standard Default template and MUST Add SP:StmtCompleted 


Now kick off SQL Profiler and a new trace; you need only trace the SP:StmtCompleted event because that’s where trigger execution appears. 

Choose text data if you want to trace for particular tables 

Trigger to find insert update and delete on table

 use db name 
go 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  TRIGGER [dbo].[tr_TableAudit_AccountTbl] 
   ON [dbo].Account
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
SET NOCOUNT ON;


BEGIN TRY
IF EXISTS (SELECT * FROM DELETED) OR EXISTS (SELECT * FROM INSERTED)
BEGIN
DECLARE @Database_Name sysname, @Schema_Name sysname, @Trigger_Name sysname;
SELECT @Database_Name = DB_NAME(), @Schema_Name = OBJECT_SCHEMA_NAME([parent_id]), @Trigger_Name = OBJECT_NAME([parent_id])
FROM sys.triggers WHERE object_id = @@PROCID;

----get the result set
WITH XML_Values AS (
SELECT 
@Database_Name + '.' + @Schema_Name + '.' + @Trigger_Name AS [TableName],
(SELECT * FROM DELETED D1 WHERE D1.AccountID=D.AccountID FOR XML RAW, ROOT, TYPE, ELEMENTS XSINIL) AS [OldRecord],
(SELECT * FROM INSERTED I1 WHERE I1.AccountID=I.AccountID FOR XML RAW, ROOT, TYPE, ELEMENTS XSINIL) AS [NewRecord],
COALESCE(I.AccountID, D.AccountID) As RecordID--Added new 
FROM Inserted I
FULL OUTER JOIN Deleted D ON I.AccountID=D.AccountID
)


INSERT INTO [AuditLog] 
( [TableName], 
[OldRecord], 
[NewRecord],
[RecordID]--Added New
)

SELECT 
[TableName],
[OldRecord],
[NewRecord],
   [RecordID]
 
FROM XML_Values
WHERE ISNULL(CONVERT(VARCHAR(MAX), [OldRecord]), '')<>ISNULL(CONVERT(VARCHAR(MAX), [NewRecord]), '');
END
END TRY
BEGIN CATCH
-- No op
END CATCH
END

Migration SQL Server 2008R2-SQL Server 2014(incomplete)

Deprecated features: find what features are deprecated?

https://www.mssqltips.com/sqlservertip/1370/identifying-deprecated-sql-server-code-with-profiler/
https://www.mssqltips.com/sqlservertip/1857/identify-deprecated-sql-server-code-with-extended-events/


when database backup and restored to any machine server, users will be moved , all we need to move is logins

when database is read only, backed up,
restore will also have same read only 

Find Job Schedules good one

USE msdb
GO
CREATE FUNCTION [dbo].[udf_schedule_description] (@freq_type INT ,
  @freq_interval INT ,
  @freq_subday_type INT ,
  @freq_subday_interval INT ,
  @freq_relative_interval INT ,
  @freq_recurrence_factor INT ,
  @active_start_date INT ,
  @active_end_date INT,
  @active_start_time INT ,
  @active_end_time INT )
RETURNS NVARCHAR(255) AS
BEGIN
DECLARE @schedule_description NVARCHAR(255)
DECLARE @loop INT
DECLARE @idle_cpu_percent INT
DECLARE @idle_cpu_duration INT

IF (@freq_type = 0x1) -- OneTime
BEGIN
SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
RETURN @schedule_description
END
IF (@freq_type = 0x4) -- Daily
BEGIN
SELECT @schedule_description = N'Every day '
END
IF (@freq_type = 0x8) -- Weekly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
SELECT @loop = 1
WHILE (@loop <= 7)
BEGIN
IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1))
SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', '
SELECT @loop = @loop + 1
END
IF (RIGHT(@schedule_description, 2) = N', ')
SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' '
END
IF (@freq_type = 0x10) -- Monthly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month '
END
IF (@freq_type = 0x20) -- Monthly Relative
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '
SELECT @schedule_description = @schedule_description +
CASE @freq_relative_interval
WHEN 0x01 THEN N'first '
WHEN 0x02 THEN N'second '
WHEN 0x04 THEN N'third '
WHEN 0x08 THEN N'fourth '
WHEN 0x10 THEN N'last '
END +
CASE
WHEN (@freq_interval > 00)
AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval))
WHEN (@freq_interval = 08) THEN N'day'
WHEN (@freq_interval = 09) THEN N'week day'
WHEN (@freq_interval = 10) THEN N'weekend day'
END + N' of that month '
END
IF (@freq_type = 0x40) -- AutoStart
BEGIN
SELECT @schedule_description = FORMATMESSAGE(14579)
RETURN @schedule_description
END
IF (@freq_type = 0x80) -- OnIdle
BEGIN
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUPercent',
@idle_cpu_percent OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUDuration',
@idle_cpu_duration OUTPUT,
N'no_output'
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
RETURN @schedule_description
END
-- Subday stuff
SELECT @schedule_description = @schedule_description +
CASE @freq_subday_type
WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
END
IF (@freq_subday_type IN (0x2, 0x4, 0x8))
SELECT @schedule_description = @schedule_description + N' between ' +
CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' + CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) )

RETURN @schedule_description
END


SELECT dbo.sysjobs.name, CAST(dbo.sysschedules.active_start_time / 10000 AS VARCHAR(10))  
+ ':' + RIGHT('00' + CAST(dbo.sysschedules.active_start_time % 10000 / 100 AS VARCHAR(10)), 2) AS active_start_time,  
dbo.udf_schedule_description(dbo.sysschedules.freq_type,
dbo.sysschedules.freq_interval,
dbo.sysschedules.freq_subday_type,
dbo.sysschedules.freq_subday_interval,
dbo.sysschedules.freq_relative_interval,
dbo.sysschedules.freq_recurrence_factor,
dbo.sysschedules.active_start_date,
dbo.sysschedules.active_end_date,
dbo.sysschedules.active_start_time,
dbo.sysschedules.active_end_time) AS ScheduleDscr, dbo.sysjobs.enabled

INTO #temp
FROM dbo.sysjobs INNER JOIN
dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id INNER JOIN
dbo.sysschedules ON dbo.sysjobschedules.schedule_id = dbo.sysschedules.schedule_id

SELECT Name, ScheduleDscr
from #Temp
where ScheduleDscr NOT LIKE '%Automatically starts when SQLServerAgent starts%'
order by name