How to get alerted anytime SQL Service is restarted?

Here I just wanted to share a quick script to create a SQL Server agent job to send you an email alert whenever SQL Server instance is started/restarted, or more accurately SQL Server Agent service. It assumes that SQL Sever Agent service is started/restarted along with the database service, which we know is not always true.

That being said,  the script basically creates a SQL Server Agent job that gets executed whenever SQL Server Agent service is started.

If you have CMS setup, you could use it to deploy this across all SQL or most critical instances after tweaking it to meet your requirements

/* For SQL 2005 or up
-  Sole purpose of this script is to send email notification whenever 
   SQL Server Agent service is started.

-  It does this by executing SQL Agent job that sends email notification 
   when the job completes, irrespective of job out.

-  The scripts create an email operator if it doesn't already exist
   Make sure to update the value for @SQL_Startup_Operator_Email to the 
   email address of the notification recipients

-  Script then create a SQL Server Agent job that gets executed at 
   SQL Server service startup
*/

USE [msdb]
GO
-- Make sure it is running SQL 2005 or up
IF (@@microsoftversion / 0x1000000) & 0xff >= 9
   PRINT 'This server is running SQL Server 2005 or up.'
ELSE
BEGIN
   RAISERROR('ERROR: This server is running SQL 2000 or older version, exiting...', 16, 1)
   return
END 

-- Set here the email address of operators who would receive the notification
DECLARE @SQL_Startup_Operator_Email NVARCHAR(500)
SET @SQL_Startup_Operator_Email = '<youremailaddresshere>'

-- SQL Agent operation with this name will be created if doesn't already exist
DECLARE @Operator_Name NVARCHAR(500)
SET @Operator_Name = 'DBA - Startup Operator'

-- SQL Agent job will be created wit this name if doesn't already exist
DECLARE @Job_Name NVARCHAR(500)
SET @Job_Name = 'DBA - SQL Server Service Start Notification'

-- CLEAN UP
-- DROP EXISTING JOB AND EMAIL OPERATOR
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @Job_Name)
    EXEC msdb.dbo.sp_delete_job @job_name=@Job_Name, @delete_unused_schedule=1;
IF EXISTS (select * from dbo.sysoperators where name = @Operator_Name)
   EXEC msdb.dbo.sp_delete_operator @name=@Operator_Name;
   
-- Create the operator if it doesn't already exist
USE [msdb]
IF NOT EXISTS (select * from dbo.sysoperators where name = @Operator_Name)
       EXEC msdb.dbo.sp_add_operator @name=@Operator_Name,
                     @enabled=1,
                     @weekday_pager_start_time=90000,
                     @weekday_pager_end_time=180000,
                     @saturday_pager_start_time=90000,
                     @saturday_pager_end_time=180000,
                     @sunday_pager_start_time=90000,
                     @sunday_pager_end_time=180000,
                     @pager_days=0,
                     @email_address=@SQL_Startup_Operator_Email,
                     @category_name=N'[Uncategorized]'

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId VARBINARY(16)
IF  not EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @Job_Name)
BEGIN
	  RAISERROR('Creating SQL Agent job "%s" ....', 10, 1, @Job_Name)
       EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=@Job_Name,
                     @enabled=1,
                     @notify_level_eventlog=0,
                     @notify_level_email=3,
                     @notify_level_netsend=0,
                     @notify_level_page=0,
                     @delete_level=0,
                     @description=N'No description available.',
                     @category_name=N'Database Maintenance',
                     @owner_login_name=N'sa',
                     @notify_email_operator_name=@Operator_Name, @job_id = @jobId OUTPUT
       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
       EXEC @ReturnCode = msdb.dbo.sp_add_jobstep  @job_name=@Job_Name, @step_name=N'dummy',
                     @step_id=1,
                     @cmdexec_success_code=0,
                     @on_success_action=1,
                     @on_success_step_id=0,
                     @on_fail_action=2,
                     @on_fail_step_id=0,
                     @retry_attempts=0,
                     @retry_interval=0,
                     @os_run_priority=0, @subsystem=N'TSQL',
                     @command=N'select @@version',
                     @database_name=N'master',
                     @flags=0
       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
       EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
       EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule  @job_name=@Job_Name, @name=N'When it starts',
                     @enabled=1,
                     @freq_type=64,
                     @freq_interval=0,
                     @freq_subday_type=0,
                     @freq_subday_interval=0,
                     @freq_relative_interval=0,
                     @freq_recurrence_factor=0,
                     @active_start_date=20100929,
                     @active_end_date=99991231,
                     @active_start_time=0,
                     @active_end_time=235959
       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
       EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
       IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
	  DECLARE @jobId_String NVARCHAR(100)
	  SELECT @jobId_String = CONVERT(NVARCHAR(100), @jobId, 1)
	  RAISERROR('Job ID: %s', 10, 1, @jobId_String)
END
ELSE
       PRINT 'JOB ALREADY EXISTS ON THIS SERVER'
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
       IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

This obviously would not alert you when the SQL Server goes down and stays down, which is what you would really want to monitor. However, I still have found this useful when SQL Server gets restarted or fails over because of some technical glitch in the network, OS, etc., or when someone, intentionally or not, restarts the SQL Server at unscheduled and inconvenient times. Since the SQL Server comes right back up, such events may not get caught by monitoring tools. These events can surely have negative consequences as they lead to connection resets, transaction rollbacks, or worse.

Alternatively, you could use sp_procoption, to execute a stored procedure at SQL Server startup, to send the email notification.

-- set a procedure for automatic execution
USE master
GO
EXEC sp_procoption 'sp_sqlserver_startup_notifier', 'startup', 'true'

-- Verify
SELECT [name] 
FROM sysobjects 
WHERE type = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;

However, this should not be seen as a substitute for monitoring with appropriate tools.

Gather Always On Configuration using a DMV queries

If you were like me, you would know your AG configuration by heart. However, what if you get busy with increasing responsibilities in other areas, projects etc.? Therefore, you do not just want to rely on your memory. On the other hand, maybe you want to simply collect snapshot of your configuration every now and then or have it documented before a major upgrades, for your junior DBAs, new hire etc...

Alternatively, maybe you just started working for a new employer or a new client. 

Whatever is your reason, here are some DMV queries to gather the information.

---

-- SQL Server Info
SELECT
    'Server Info' AS [Description],
    SERVERPROPERTY('ServerName')                   [ServerName],
    SERVERPROPERTY('InstanceName')                 [InstanceName],
    @@VERSION                                      [VersionString],
    SERVERPROPERTY('ProductVersion')               [ProductVersion],
    SERVERPROPERTY('Edition')                      [Edition],
    SERVERPROPERTY('IsHadrEnabled')                [AlwaysOn_Enabled];

IF SERVERPROPERTY('IsHadrEnabled') = 1
BEGIN	
	-- Overview of the AGs
	SELECT 
	    'Overview' AS [Description],
	    ag.name AS [AG Name],
	    ar.replica_server_name AS [Replica Server],
	    ar.availability_mode_desc AS [Availability Mode],
	    ar.failover_mode_desc AS [Failover Mode],
	    ars.role_desc AS [Current Role],
	    ars.operational_state_desc AS [Operational State],
	    ars.connected_state_desc AS [Connected State]
	FROM sys.availability_groups ag
	JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
	JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

	-- AG listeners
	SELECT 
	    'AG Listeners' AS [Description],
	    ag.name AS [AG Name],
	    agl.dns_name AS [Listener DNS Name],
	    agl.port AS [Listener Port],
	    agl.ip_configuration_string_from_cluster AS [IP Config]
	FROM sys.availability_groups ag
	JOIN sys.availability_group_listeners agl ON ag.group_id = agl.group_id;

	-- AG Health
	SELECT 
	    'AG Health' AS [Description],
	    ag.name AS [AG Name],
	    ags.primary_replica AS [Primary Replica],
	    ags.primary_recovery_health_desc AS [Primary Health],
	    ags.secondary_recovery_health_desc AS [Secondary Health],
	    ags.synchronization_health_desc AS [Sync Health]
	FROM sys.availability_groups ag
	JOIN sys.dm_hadr_availability_group_states ags ON ag.group_id = ags.group_id;

	-- AG replicas and roles
	SELECT 
	    'Replica Roles' AS [Description],
	    ag.name AS [AG Name],
	    ar.replica_server_name AS [Replica Server],
	    ars.role_desc AS [Current Role],
	    ars.synchronization_health_desc AS [Sync Health],
	    ar.availability_mode_desc AS [Availability Mode],
	    ar.failover_mode_desc AS [Failover Mode]
	FROM sys.availability_groups ag
	JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
	JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

	-- Replication status
	SELECT 
	    'Replication Status' AS [Description],
	    ag.name AS [AG Name],
	    ar.replica_server_name AS [Replica],
	    ar.availability_mode_desc AS [AG Mode],
	    db.name AS [Database],
	    drs.synchronization_state_desc AS [Sync State],
	    drs.log_send_queue_size AS [Log Send Queue KB],
	    drs.log_send_rate AS [Log Send Rate KB/sec],
	    drs.redo_queue_size AS [Redo Queue KB],
	    drs.redo_rate AS [Redo Rate KB/sec]
	FROM sys.availability_groups ag
	JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
	JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id = drs.replica_id
	JOIN sys.databases db ON drs.database_id = db.database_id;

END;
ELSE
BEGIN
    SELECT 'AlwaysOn is disabled' 'AlwaysOn Status' 
END;

Check health and status of Availability Group using DMVs

-- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum

SELECT * FROM sys.dm_hadr_cluster

SELECT * FROM sys.dm_hadr_cluster_members

-- Health of the AGs

SELECT ag.name agname, ags.* FROM sys.dm_hadr_availability_group_states ags INNER JOIN sys.availability_groups ag ON ag.group_id = ags.group_id

-- Health and status of AG replics from the WsFC perspective

SELECT ar.replica_server_name, harc.* FROM sys.dm_hadr_availability_replica_cluster_states harc INNER JOIN sys.availability_replicas ar ON ar.replica_id = harc.replica_id

-- Health and status of AG replicas, run this on the primary replica. 

-- On secondary this will only show info for that instance

SELECT * FROM sys.dm_hadr_availability_replica_states 

-- Health and status of AG databases from the WSFC perspective

SELECT * FROM sys.dm_hadr_database_replica_cluster_states 

-- Health and status of AG databases, run this on the primary replica. 

-- On secondary this will only show info for that instance

SELECT  ag.name ag_name ,

        ar.replica_server_name ,

        adc.database_name ,

        hdrs.database_state_desc ,

        hdrs.synchronization_state_desc ,

        hdrs.synchronization_health_desc ,

        agl.dns_name ,

        agl.port

-- ,*

FROM    sys.dm_hadr_database_replica_states hdrs

        LEFT JOIN sys.availability_groups ag ON hdrs.group_id = ag.group_id

        LEFT  JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id

                                                   AND ar.replica_id = hdrs.replica_id

        LEFT  JOIN sys.availability_databases_cluster adc ON adc.group_id = ag.group_id

                                                             AND adc.group_database_id = hdrs.group_database_id

        LEFT  JOIN sys.availability_group_listeners agl ON agl.group_id = ag.group_id

ORDER BY ag.name , adc.database_name

-- Health and status of AG listeners

SELECT agl.dns_name, agl.port, aglia.* FROM sys.availability_group_listener_ip_addresses aglia INNER JOIN sys.availability_group_listeners agl ON agl.listener_id = aglia.listener_id

Get SQL server database size, location and volume info using DMVs

SELECT 
    DB_NAME(mf.database_id) AS DatabaseName,
    mf.name AS LogicalFileName,
    mf.physical_name AS PhysicalFileName,
    CAST(mf.size * 8.0 / 1024 AS DECIMAL(10,2)) AS FileSizeMB,
    CAST(FILEPROPERTY(mf.name, 'SpaceUsed') * 8.0 / 1024 AS DECIMAL(10,2)) AS SpaceUsedMB,
    ovs.volume_mount_point AS VolumeMountPoint,
    ovs.logical_volume_name AS LogicalVolumeName,
    ovs.file_system_type AS FileSystemType,
    CAST(ovs.total_bytes / 1073741824.0 AS DECIMAL(10,2)) AS VolumeTotalGB,
    CAST(ovs.available_bytes / 1073741824.0 AS DECIMAL(10,2)) AS VolumeAvailableGB,
    CAST(100 * (ovs.available_bytes * 1.0 / ovs.total_bytes) AS DECIMAL(5,2)) AS VolumeFreePercent
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) ovs
ORDER BY DatabaseName, LogicalFileName;

Easy to use WMI commands to query windows system

WMI has been around for a very long while and it has grown significantly over the years. As of now though the WMI has been deprecated by Microsoft and the WMI and it's WMIC command line utility is superseded by Windows PowerShell for WMI!

But if you happened to have any legacy, old, outdated servers lying around that you cannot upgraded just yet or maybe ever, WMI is maybe the only option you may have available to remotely manage servers from the command line.

And the WMIC is a command line interface to the WMI (Windows Management Instrumentation).

To get started,  launch the command shell (CMD.EXE).

You can run WMIC utility interactively, from the WMIC prompt,  or non-interactively. Once you get little comfortable with it, I think you would use the non interactive method more often.

But for learning purpose, lets start WMIC in interactive mode by typing WMIC on the command prompt and hit enter. it should now display the WMIC command promot,  wmic:root\cli>;


Here on I am just listing some informational commands that you can type in and view the results show up on your display:

Type /? and hit enter.  This would display all available WMIC commands reference.

To get information on the CPU on local computer

From command prompt: WMIC CPU

OR

In interactive WMIC session: cpu

To run a WMI command on a remote computer

WMIC /NODE:<SERVERNAME> CPU

OR

In interactive session:

To get information on the OS

wmic os

To display only SELECT fields from the output:

wmic diskdrive get name, caption, mediatype, status, size, systemname, totalheads, totaltracks

And here are some examples to get information on the logical disks:

wmic logicaldisk
wmic logicaldisk get caption, deviceid, drivetype, filesystem, status, volumename
wmic logicaldisk where deviceid="c:" get caption, deviceid, drivetype, filesystem, status, volumename

Notice the use of where clause with one the output fields to filter the results.

Get information on a service:

wmic service where name="mssqlserver" get name, caption, startmode, state, status

Wait a minute, what happened? I know I have SQL Server installed on this machine so what the hack?

The reason is simple, all my sql instances are named instances i.e. there is no default instance of SQL on this machine, which would have service name mssqlserver. So, I am going to modify my where condition like this to get all services with the "sql" anywhere in it's path name:

wmic service where "PathName like '%sql%'" get name, caption, startmode, state, status

That's better!

Here are few others that I have found useful at times.

wmic pagefile
wmic pagefileset
wmic memcache
wmic memphysical

And finally, here are some links for additional information:

Using Windows Management Instrumentation Command-line:

https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2003/cc779482(v=ws.10)

WMIC - Take Command-line Control over WMI:

https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-2000-server/bb742610(v=technet.10)

Windows PowerShell for WMI:

https://learn.microsoft.com/en-us/powershell/scripting/learn/ps101/07-working-with-wmi?view=powershell-7.3

Shrink transaction log files for all databases in SQL Server

USE master;

/* Note **
If a datatabase has more than one transaction log file,
each will have this target size.
*/
DECLARE @target_size_mb NVARCHAR(20) = 100;

DECLARE @SQL NVARCHAR(4000);
SET @SQL = '

USE [?]

DECLARE @Updateability NVARCHAR(100)
SET @Updateability = CAST(DATABASEPROPERTYEX(''?'', ''Updateability'') AS NVARCHAR(100))

IF @Updateability = ''READ_WRITE ''
BEGIN
   RAISERROR(''INFO: Shrinking transaction log of database: %s'', 10, 1, ''?'')

   -- open a cursor so we can iterate through and shrink all log files
   RAISERROR(''INFO: Truncate all transaction log files.....'', 10, 1) WITH NOWAIT;
   DECLARE @log_file_name sysname;
   DECLARE c1 cursor for
   SELECT name from sys.database_files where type=1;
   OPEN c1;
   FETCH c1 into @log_file_name;
   WHILE @@FETCH_STATUS = 0
   BEGIN
   	   RAISERROR(''INFO: Truncating transaction log file %s....'', 10, 1, @log_file_name) WITH NOWAIT; 
   	   DBCC SHRINKFILE (@log_file_name, ' + @target_size_mb +');
   	   FETCH c1 into @log_file_name;
   END
   CLOSE c1;
   DEALLOCATE c1;
   PRINT ''''
END
ELSE
   RAISERROR(''Skipping "database" %s in Updateability mode "%s"'', 10, 1,  ''?'',  @Updateability) 
'
PRINT @SQL

exec sp_MSforeachdb @SQL;

T-SQL Developer Interview Questions with Answers

Originally written in 2010, this Q&A urgently requires an update.

What SQL command do you use to pull and filter data from a table ?  

SELECT [Column List] FROM [Table Name] WHERE [Condition = TRUE]

OR

SELECT * FROM [Table Name] WHERE [Condition = TRUE]

---

How do you update data (what command do  you use)

Syntax to update data without join:

UPDATE TableA SET Field1 = X

WHERE Condition = True

Syntax to update data using a join:

UPDATE TableA SET TableA.Field2 = TableB.Field2

FROM TableA 

INNER JOIN TableB ON TableB.Field1 = TableA.Field1

or (alias solution)

UPDATE a SET a.Field2 = b.Field2

FROM TableA AS a

INNER JOIN TableB AS b ON b.Field1=a.Field1

---

What is the Syntax to insert data in a table

INSERT [INTO] TableA [()] SELECT Field1 [, FieldN] FROM TableB WHERE Condition = True

INSERT [INTO] TableA [()] SELECT Constant

INSERT [INTO] TableA [()] VALUES (…)

INSERT [INTO] TableA [()] EXEC sp

SELECT Field1, Field2, … INTO [TableName] FROM Table WHERE Condition = True

---

What is the identity property?

Auto incremental numeric value, gets populated automatically by SQL Server

---

When inserting a record into a table having an identity column, how do you capture the newly-generated number?  

You can use @@IDENTITY, IDENT_CURRENT(‘Table Name’) or  SCOPE_IDENTITY() functions

---

How would you capture the last identity value created by your database session (assuming other connections are actively inserting records into the same table)?    

SCOPE_IDENTITY() function

---

---

How can one insert data into a field declared as having the identity property?

Identity fields are populated automatically and you are not allowed to specify what that value should be. However there is a way where you could force a specific value manually . 

Turn the identity insert on first

SET IDENTITY_INSERT TableA ON

Perform the insert

Turn off the identity insert

SET IDENTITY_INSERT TableA OFF

---

---

Syntax to remove record(s) from a table 

DELETE FROM TableA WHERE Condition = True

DELETE FROM a FROM TableA AS a

INNER JOIN TableB AS b ON b.Field1 = a.Field1

There are other ways and variations to the above syntax

You can also use TRUNCATE command that wipes out everything from the table.

---

What is the difference between the DELETE statement (to empty a table) and the TRUNCATE TABLE statement?

DELETE statement is a logged operation whereas the TRUNCATE TABLE statement is a minimally logged operation (data page deallocations are recorded in the log).

TRUNCATE TABLE statement performs faster than the DELETE statement.

TRUNCATE TABLE cannot be used on tables that are referenced by a foreign key constraint.

TRUNCATE TABLE will not activate a trigger because it is not logged; however, the DELETE statement may fire a trigger.

TRUNCATE TABLE will reset a table’s identity value back to its seed (usually 1).

DELETE statement is DML whereas TRUNCATE TABLE is DDL.

---

Different Joins and Their Purpose

Inner –Match rows from 2 tables based on the same values in the matching fields

Left Outer – All rows from left table and those records that match in right table.  If not match, then null.

Right Outer – All rows from right table and those records that match in left table.  If no match, then null.

Full Outer – All rows from left and right tables.  Any non matching rows will contain a NULL in either the left or right tables.

Cross – Cartesian product.  Left table record is returned with all records from right table.

Natural – Not implemented in SQL Server but it is in the ANSI standard. It basically amounts to a syntax short cut when you have your foreign and primary constraints set up.

Theta – A through D represent Equi-Joins which are a subset of Theta Joins that use the equality comparison. However you can join with othe non-equality 

Boolean operators. …. FROM TABLEA A JOIN TABLEB B ON A.ID > B.ID. 

---

WHERE clause purpose?  

To filter only the records of interest.

---

HAVING clause purpose?  

To filter the aggregation of interest.

---

What keyword(s) allow 2 datasets to be returned as a single dataset?  For example, query 1 pulls data from a table while another query pulls data from another table.  

UNION and UNION ALL clauses

---

And what is the difference between UNION and UNION ALL?  

UNION automatically performs a DISTINCT operation – ensuring unique records in a dataset.  UNION ALL returns a dataset AS-IS.

---

What is aliasing?  

Substituting a short-hand name for a table after defining it.

---

Why alias tables?  

To make code less verbose, yet more readable.  You may have to join to the same table multiple times.

---

What is a correlated sub-query?  

correlated sub-queries references same table in its sub query

---

How would you determine records that are missing when comparing 2 tables:

NOT IN subquery solution

SELECT a.*

FROM TableA

WHERE PKID NOT IN ( SELECT PKID FROM TableB )

LEFT OUTER JOIN solution

SELECT a.*

FROM TableA a

LEFT OUTER JOIN TableB b ON b.PKID=a.PKID

WHERE b.PKID IS NULL

NOT EXISTS correlated subquery solution

SELECT a.*

FROM TableA a

WHERE NOT EXISTS ( SELECT NULL FROM TableB WHERE PKID = a.PKID )

---

What is the difference between CROSS APPLY and OUTER APPLY in SQL Server?  

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

---

What looping constructs are available in T-SQL?

WHILE Loop

Cursors Pros

Allows row-by-row processing

Cursors Cons

Poor performance

Best Practice – Avoid them if at all possible.  95% of the time, a set-oriented approach can be implemented with much better results.

---

What is a derived table?  

Select statement defined as its own separate table.   

---

what is syntax to define a derived table?

SELECT *

FROM (

SELECT Field1, Field2

FROM TableA

WHERE [Condition=TRUE] ) AS a

---

What is a benefit of using derived tables as compared to declaring and populating a temp table/ table variable?  

Performance because it uses existing indexes on referenced tables as compared to a temp table that needs to have indexes created on it before using it.

---

What is a CTE?  

It’s a more readable form of a derived table that can be referenced multiple times within the same query.  Syntax is:

CTEs can have recursive qualities.  

<CTE Name> [(Column1, Column2, ColumnN)] AS

(

<CTE Query Anchor Member SELECT statement>

UNION ALL

 <CTE Query Recursive Member SELECT statement>

)

SELECT *

FROM <CTE Name>

---

What is the difference between a temp table and a table variable?  

Table variables don’t support check constraints, default values, and computed columns cannot reference user-defined functions.  They don’t allow named constraints, table truncation, the overriding of the identity property.  They cannot be created on-the-fly using SELECT … INTO …

A common misconception is that Temp tables are physically stored in TempDB whereas table variables solely reside in memory.  This is not the case.  Both can be stored in memory, unless memory is insufficient.  In that case both can be flushed to disk in TempDB.

Table variables are good for storing small datasets (about 100 records or so) – not large unless it is indexed via primary key or unique constraint that is proven to be used in subsequent queries.  

A table variable limits recompiling events and avoids the statistics manager; whereas temp tables have statistics performed to help the optimizer determine the most efficient way of referencing the data. 

---

At what point (if any) do you determine to use a temp table over a table variable?  

It depends.  Performance tests ultimately will prove or disprove the best solution of a temp table vs. table variable.

---

What is dynamic SQL?  

A string that is assembled and executed at run-time.

---

How is dynamic SQL invoked?

EXECUTE  (T-SQL Command String)

EXECUTE sp_ExecuteSQL  @Stmt, @Params (declare), @Param1 (value)…

---

What is the difference between the above commands?  EXECUTE runs a statement in Ad-Hoc fashion, requiring compiler to operate.  

Sp_ExecuteSQL attempts to reuse query plans and therefore may skip compilation (provided a query plan exists).

---

When using sp_ExecuteSQL, do referenced tables need to have their database name fully qualified?  

YES.  

---

What happens if the database name is not fully qualified?  

Then the query is treated like an ad-hoc query – requiring the use of the compiler.

---

What conditions are appropriate to use a dynamic SQL solution?  

A query statement that must change to support a specific request; otherwise, multiple similar queries would need to be created statically.  For example, a requirement may state that a query handle up to 3 filtering conditions in a WHERE clause.  Each condition may be supplied by itself or in conjunction with others.

---

What functions types available?

User Defined Inline (returns a table of data), syntactically referenced as a table and achieves the functionality of a parameterized view

User Defined Multistatement table-valued functions

User Defined Scalar (returns a single data value)

---

Why is it preferable to NOT use the function?  

Because the function is called for EACH record, the performance will be slower than if one were to write a CASE statement or a concatenated string (with the + sign).

---

what is a database trigger?

Essentially a special stored procedure installed on a table and is invoked by INSERT, UPDATE and/or DELETE actions taken on rows within the table.

---

What 2 special tables are present during the execution of a trigger?  

Inserted & Deleted.