SQL - My Best Friend

Calculating Working days

2011-02-09T05:33:00.000-08:00

Here is a script for finding out the calculating the work days (excluding the saturdays and sundays). The script can be altered also to accomodate local holidays in a separate table too. Only thing is to substract the count of the table for that period.


Declare @StartDate Date,  @EndDate Date
Set @StartDate = Getdate()-20
Set @EndDate = Getdate()

SELECT  (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

Removing multiple spaces in data

2011-01-08T02:25:00.000-08:00

I am certain some of us would have really come across a situation that the spaces between data becomes annoying and needs to be removed on request. Many of the solutions have come across multiple blogs and sites with while loop or cursor was really made to think Is there any way???

Hmm!! On searching my tressure, after a while, found something really interested(I dont really know to whome should give the credit, ofcourse it should go the person.) . The idea was really good and quite simple.Sometimes we do take the things very complicated and dont think about a simple solution.Here it is .......

Table variable myths

2010-12-18T02:02:00.000-08:00

There are always some myths around the table variable:
1. Table variables works with only memory.
- It is wrong understanding that the table varibles works only with memory. If table variables are populated with more records, it spills to the temp DB and works as Temperory tables. We need to make sure if the records are less and enough to do the operations in memory, can easily go with the table variable considering the basic differences with temperory table.

2. Table variables can not be indexed at all.
- Many of SQL developers think that the table variable can not be indexed. In truth, it can be very well at the declaration part as below:
DECLARE @Test TABLE (
ID INT NOT NULL PRIMARY KEY,
IndexableColumn1 INT,
IndexableColumn2 DATETIME,
IndexableColumn3 VARCHAR(10),
UNIQUE (IndexableColumn1,ID),
UNIQUE (IndexableColumn2,ID),
UNIQUE (IndexableColumn3, IndexableColumn2, ID)
)
3. Changes to table variables are not logged.
- Even table variables are not part of the transactions(can not be a partof rollback; no effect), it logs the operations. This is for the main reason to avoid any violation to the primary/unique key constraints. Say, inserting a bunch of records to a table varible breaks at the mid of the operations, it has to delete the records from that table variable(Atomicity concept).

Reference:
http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/#more-687

IF statement resulting in Recompile

2010-12-16T07:45:00.000-08:00

There is a wrong perception amoung someDBAs that the if statement in procedures would cause a recompile of the procedures at the execution time. However, it is been proved that if a procedure contains if statements, all codes are getting compiled at the first compilation stored in the cache,ie, the cache plan contains all the statements and never been recompiled due to the "if statement".
--Look at the plan created

From the above figure, it is clear that even you execute the procedure which succeeds for a condition, SQL server would create plan for both cases and utilize the same for the next execution. The issue here is for a procedure with more if and else condition, the plan created may not be suitable and may lead to an unexpected execution plans.

Reference:
http://www.sqlservercentral.com/blogs/sqlinthewild/archive/2010/12/14/do-if-statements-cause-recompiles_3F00_.aspx
http://scarydba.com/2010/05/11/recompiles-and-constant-learning/

My Favouirite DMV Queries

2010-11-04T04:54:00.000-07:00

Performance tuning is really an art in SQL server. I would say whatever you see today would not be the case tomorrow. Oh....oops...I dont mean it really. I wanna say something like tuning for performance is an ongoing activity. Thats better!!! We need more patience and aggressive if we are really need to get some benefits. I would say we need to really understand the SQL server internals and architecture to start with.
Here am giving a check list that I would follow generally. I would like to keep on update the same as I learn things in SQL server(learning new things every day). I just wanna share my experience with performance tuning and looking forward your comments on the same.

A. Analysing the wait types and queues.

Analysing the wait types are one of the most important aspect of performance tuning for a large systems. SQL server provides information about the performance degradation and can be tracked through analysing the wait types. The following query would provide the most occuring wait types in the production and that can be taken as a first step of your performance tuning of a system.


WITH Waits AS(SELECT 
wait_type, 
wait_time_ms / 1000.0 AS WaitS, 
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS, 
signal_wait_time_ms / 1000.0 AS SignalS, 
waiting_tasks_count AS WaitCount, 
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage, 
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum 
FROM sys.dm_os_wait_stats 
WHERE wait_type NOT IN ( 
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK') 
) 
SELECT 
W1.wait_type AS WaitType, 
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S, 
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S 
FROM Waits AS W1 
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum GROUP BY W1.RowNum, W1.wait_type, 
W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage HAVING SUM 
(W2.Percentage) - W1.Percentage <95

Few of major Wait type and its analysis as follows:
1. CXPACKET
B. Analysing the index structures.
1. Clustered Index Structure
2. Non Clustered Index Structure.
C. Implicit Datatype Conversion
D. Lock Contention/Escalations on Tables
E. Memory Issues
F. Look for Spool information
G. Look for Index Scans (Clustered/Non-Clustered)
H. Page split and Fragmentation

Sequence Numbers in SQL server

2010-10-02T11:05:00.001-07:00

Have you ever thoguht of generating sequential numbers? I guess, many of us had atleast once a scenario atleast for our reporting purpose to think about sequencing the records.

Here am going to explain how do I come across with various SQL server versions. We have something like IDENTITY (I will explain little more about this later) in all of SQL versions available. It is a kind of methodology given by SQL server to generate sequencing. How about when you do have scenario to generate the
sequence while you write a procedure.Here we go!!!

SQL Server 2000


SELECT
RowNumber = IDENTITY(INT,1,1)
,c.LastName
,c.FirstName
INTO #Customer_RowID
FROM SalesLT.Customer c
ORDER BY c.LastName ASC
SELECT * FROM #Customer_RowID

SQL Server 2005 and 2008


SELECT PurchaseID, PurchaseDate, ROW_NUMBER() OVER (ORDER BY PurchaseDate) AS 'RowNumber' FROM PurchaseOrder

SQL Server -(Denali)
In addition to ROW_NUMBER, there is something called Sequence introduced in Denali Version. I have not got any personal experience with the same.Hence am just giving a clue...

Rebuild tables in a database

2009-04-08T21:18:00.001-07:00

The below script will do a rebuilding of indexes of all tables in a database.

We can explicitly mention the below features for this procedure:
1. Excluded list of tables
2. MAXDOP can be specified.
3. Fillfactor can be specified(by default it takes from the system catalogs.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[RebuildAllIndexes]
AS
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @indexname sysname;
DECLARE @maxdop tinyint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @infomessage varchar(1000);
DECLARE @fillfactor tinyint;
DECLARE @returncode tinyint;
DECLARE @dbname sysname;
DECLARE @localerror integer;
DECLARE @ExcludeTableList varchar(100)

SET @ExcludeTableList = ''+','
SET @dbname = DB_NAME()

-- Set MAXDOP default value
IF (@maxdop IS NULL)
BEGIN
SET @maxdop = (SELECT CONVERT(smallint, value) FROM master.sys.configurations (NOLOCK) WHERE name = 'max degree of parallelism')
END
SET @returncode = 0

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
IF OBJECT_ID('tempdb..#work_to_do') IS NOT NULL
BEGIN
DROP TABLE #work_to_do
END
CREATE TABLE #work_to_do(
[objectid] [int] NULL,
[indexid] [int] NULL,
[fill_factor] [int] NOT NULL
)
-- Get indexes that meet our criteria
INSERT INTO #work_to_do
SELECT o.object_id,i.index_id, CASE i.fill_factor WHEN 0 THEN 100 ELSE i.fill_factor END AS fill_factor
FROM sys.indexes i (NOLOCK) INNER JOIN sys.objects o(nolock) ON i.object_id = o.object_id
WHERE i.index_id > 0 AND o.type ='U' AND CHARINDEX(o.name+',',@ExcludeTableList) = 0
ORDER BY O.name,i.index_id
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @fillfactor; --@partitionnum, @frag, ;
IF @@FETCH_STATUS < objectname =" QUOTENAME(o.name)," schemaname =" QUOTENAME(s.name)" schema_id =" o.schema_id" object_id =" @objectid;" schemaname =" '[sys]')" indexname =" QUOTENAME(name)" object_id =" @objectid" index_id =" @indexid;" command =" N'ALTER" command =" @command" command =" @command" fillfactor =" '" command =" @command" maxdop =" '" command =" @command" infomessage =" 'Executing" localerror =" @@ERROR"> 0
BEGIN
SET @infomessage = 'Reindex failed with error ' + CAST(@localerror AS varchar(10)) + '.'
RAISERROR (@infomessage, 11, 1) WITH LOG, NOWAIT
SET @returncode = @returncode + 1
END
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
RETURN @returncode

Split function to get values from Comma separated string

2008-08-16T00:23:00.000-07:00

Split function is one of the main string function to split a list of items separated by a character in our daily functions.

1. Using While loop


ALTER FUNCTION [dbo].[Split]
( @List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
( Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END

Testing the Linked server connectivity From SQL server 2005

2008-06-09T22:04:00.000-07:00

Have you ever encountered a scenario in SQL Server 2000 where you want to test the connectivity of a linked server configuration via TSQL? This was not possible easily and best handled from client side. But we have now a system stored procedure "sp_testlinkedserver" in SQL Server 2005 that allows you to test linked server connection. This stored procedure takes a linked server name as parameter, tests the connectivity and returns 0 for success & 1 for failure. You can find more details about this stored procedure in the SQL Server 2005 Books Online. Here is how a sample call can look like:

declare @srvr nvarchar(128), @retval int;
set @srvr = 'my_linked_srvr';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0
raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
The reason for the try...catch block is left as an exercise to readers!

Due to lack of exception handling and implementation of OPENQUERY/OPENROWSET/OPENDATASOURCE interfaces it is not possible to do it cleanly. The afore-mentioned interfaces remain the same in SQL Server 2005 also and they perform their validation at compile-time itself. So for example, if the linked server name is incorrect or the parameter to OPENROWSET is invalid you will get an error immediately. You cannot capture such errors within TRY...CATCH also. You need to enclose it in dynamic SQL so it can be caught in the same batch as the TRY...CATCH. EX:
-- below will fail at compile-time itself
-- so the try..catch never executes
begin try
select * from openquery(missing_server, 'select * from sys.tables')
end try
begin catch ... end catch

-- using dynamic sql will help since the query
-- is evaluated only when the dynamic SQL statement is executed
begin try
exec('select * from openquery(missing_server, ''select * from sys.tables'')')
end try
begin catch ... end catch
This also holds true in SQL Server 2000. Compare code below:
select * from openquery(some_servr, 'select 1')
if @@error <> 0
begin
print 'Error'
end
go
exec('select * from openquery(some_servr, ''select 1'')')
if @@error <> 0
begin
print 'Error'
end
So the bottom-line is that you could use dynamic SQL to run the test statement against remote server and catch any errors. Now, as for your question about different domains there is lot of complexity involved depending on how you want to establish the connection. If you are using SQL logins then it is straight forward assuming the firewall or network settings allow communication between the servers. In case of Windows authentication, you may have to enable constrained delegation (Windows Server 2003) or security delegation depending on service account configurations and from where the clients connect. See BOL for discussion on these topics.

Dead Lock Related Topics

2008-05-28T10:05:00.000-07:00

Deadlock Notification
Deadlocks... huh??
Deadlocks can be a pain to debug since they're so rare and unpredictable. The problem lies in repeating them in your dev environment. That's why it's crucial to have as much information about them from the production environment as possible.
There are two ways to monitor deadlocks, about which I'll talk about in the future posts. Those are SQL Server tracing and Error log checking. Unfortunately both of them suffer from the same thing: you don't know immediately when a deadlock occurs. Getting this info as soon as possible is sometimes crucial in production environments. Sure you can always set the trace flag 1222 on, but this still doesn't solve the immediate notification problem.
One problem for some might be that this method is only truly useful if you limit data access to stored procedures. So all you ORM lovers stop reading since this doesn't apply to you anymore!
The other problem is that it requires a rewrite of the problematic stored procedures to support it. However since SQL Server 2005 came out my opinion is that every stored procedure should have the try ... catch block implemented. There's no visible performance hit from this and the benefits can be huge. One of those benefits are the instant deadlocking notifications.

Needed "infrastructure"
So let's see how it done. This must be implemented in the database you wish to monitor of course.
CREATE VIEW vLocks
AS
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction AS IsUserTransaction,
AT.name AS TransactionName
FROM sys.dm_tran_locks L
LEFT JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
LEFT JOIN sys.objects O ON O.object_id = P.object_id
LEFT JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
LEFT JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
LEFT JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
LEFT JOIN sys.dm_exec_requests ER ON AT.transaction_id = ER.transaction_id
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
WHERE resource_database_id = db_id()
GO
Next we have to create our stored procedure template:CREATE PROC
AS
BEGIN TRAN
BEGIN TRY

COMMIT
END TRY
BEGIN CATCH
-- check transaction state
IF XACT_STATE() = -1
BEGIN
DECLARE @message xml
-- get our deadlock info FROM the VIEW
SET @message = '' + (SELECT * FROM vLocks ORDER BY SPID FOR XML PATH('TransactionLock')) + ''
-- issue ROLLBACK so we don't ROLLBACK mail sending
ROLLBACK
-- get our error message and number
DECLARE @ErrorNumber INT, @ErrorMessage NVARCHAR(2048)
SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorMessage = ERROR_MESSAGE()
-- if it's deadlock error send mail notification
IF @ErrorNumber = 1205
BEGIN
DECLARE @MailBody NVARCHAR(max)
-- create out mail body in the xml format. you can change this to your liking.
SELECT @MailBody = ''
+
(SELECT 'Error number: ' + isnull(CAST(@ErrorNumber AS VARCHAR(5)), '-1') + CHAR(10) +
'Error message: ' + isnull(@ErrorMessage, ' NO error message') + CHAR(10)
FOR XML PATH('ErrorMeassage'))
+
CAST(ISNULL(@message, '') AS NVARCHAR(MAX))
+
''
-- for testing purposes
-- SELECT CAST(@MailBody AS XML)
-- send an email with the defined email profile.
-- since this is async it doesn't halt execution
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'your mail profile',
@recipients = 'dba@yourCompany.com',
@subject = 'Deadlock occured notification',
@body = @MailBody;
END
END
END CATCH
GO
The main part of this stored procedure is of course the CATCH block. The first line in there is check of the XACT_STATE() value. This is a scalar function that reports the user transaction state. -1 means that the transaction is uncommittable and has to be rolled back. This is the state of the victim transaction in the internal deadlock killing process. Next we read from our vLocks view to get the full info (SPID, both SQL statements text, values, etc...) about both SPIDs that created a deadlock. This is possible since our deadlock victim transaction hasn't been rolled back yet and the locks are still present. We save this data into an XML message. Next we rollback our transaction to release locks. With error message and it's corresponding number we check if the error is 1205 - deadlock and if it is we send our message in an email. How to configure database mail can be seen here.
Both the view and the stored procedures template can and probably should be customized to suit your needs.

Testing the theory
Let's try it out and see how it works with a textbook deadlock example that you can find in every book or tutorial.-- create our deadlock table with 2 simple rows
CREATE TABLE DeadlockTest ( id INT)
INSERT INTO DeadlockTest
SELECT 1 UNION ALL
SELECT 2
GO
Next create two stored procedures (spProc1 and spProc2) with our template:
For spProc1 replace in the template with: UPDATE DeadlockTest
SET id = 12
WHERE id = 2

-- wait 5 secs TO SET up deadlock condition IN other window
WAITFOR DELAY '00:00:05'
UPDATE DeadlockTest
SET id = 11
WHERE id = 1

For spProc2 replace in the template with: UPDATE DeadlockTest
SET id = 11
WHERE id = 1
-- wait 5 secs TO SET up deadlock condition IN other window
WAITFOR DELAY '00:00:05'
UPDATE DeadlockTest
SET id = 12
WHERE id = 2

Next open 2 query windows in SSMS:
In window 1 run put this script:exec spProc1
In window 2 put this script:exec spProc2

Run the script in the first window and after a second or two run the script in the second window. A deadlock will happen and a few moments after the victim transaction fails you should get the notification mail. Mail profile has to be properly configured of course.
The resulting email should contain an XML with full info about the deadlock. You can view it by commenting msdb.dbo.sp_send_dbmail execution and uncommenting the SELECT CAST(@MailBody AS XML) line.

If you fire up the SQL Profiler, reset the table values, rerun both scripts and observe the deadlock graph event you should get a picture similar to this one:

End ? ... for now... but more to come soon!
With the upper technique we can get mostly the same info as with trace flags with the advantage of knowing immediately after happening, plus if we want we can extend this to notify/log about every error which I actually prefer to do.

Colasce and ISNULL

2008-03-10T23:49:00.000-07:00

Colaesce
Coalesce and ISNULL are SQL server system functions. Both are using to check the null values in the parameters.
However there are few differences as follows.
1. Coalesce requires atleast two parameters. Can have n number of parameters.
2. Coalesce is an ANSI standard
3. COALESCE works more like a CASE expression, which returns a single datatype depending on precendence and accommodating all possible outcomes
4. Caution using coalesce in sub query; it may create less efficient execution plan.

ISNULL
1. ISNULL is not a NASI standard. It has been opted by Microsoft in addition to ANSI. ISNULL() accepts exactly two parameters. If you want to take the first non-NULL among more than two values, you will need to nest your ISNULL() statements. COALESCE(), on the other hand, can take multiple inputs
2. The result of ISNULL() always takes on the datatype of the first parameter (regardless of whether it is NULL or NOT NULL).
3. ISNULL uses an implicit conversion of data type to the first parameter. Beware of these kind of conversions in real time.

Examples of colaesce usages :

1. The following query can be improved as follows:
Where ( DBA_StartdateIs NULL Or DBA_Startdate <= @Startdate) Use the below: Where Colaesce(DBA_Startdate, @Startdate) <= @Startdate

2. The following query uses seeks If you wanna check the parameters upon the availability, coalesce/ISNULL can be given as follows to enforce the seeks on the index.


SELECT * FROM Authors WHERE AU_LName LIKE COALESCE(@lastName, '%')

3. Comma separated string can be created using Colaesce instead of using Cursors


Declare @TableList Varchar(8000) 

Select @TableList = COALESCE(@TableList + ', ', '') +Cast(A.TableName As varchar(50))From Table1 A 

Left Join Table2 B On A.TableName = B.TableName 

And Convert(Varchar(50),Getdate(),101)= Convert(Varchar(50),B.Date_Column,101)

Where (B.TableName Is Null)

Case Study:
Createdtwo table T and T2 as the below specifications:/>

The below execution plan is for the coalesce usage. The plan does not have any compute scalara operator as it happens with the ISNULL.
The below figure is XML plan for ISNULL, where an implicit conversion happens for all cases. It does not really not based on any CASE as it happens with the COALESCE.

The below execution plan is with ISNULL function, where it is clearly showing that an operator in the execution.

The XML plan of the query is much more detailed and much more reliable. The below XML plan for the coalesce shows a CASE statement inside an implicit conversion to make the result as discussed above for precendence and accommodating all possible outcomes.

From all the ablove, it always better to look at the XMLplan than graphical representation. Even COALESCE has an implicit conversion of datatype within the CASE, it does not show any indication in the graphical representation of the execution plan.

In real time scenarios, lets really evaluate the possible better way between ISNULL and COALESCE for the optimized code. Need to take a call based on your scenario.There is no hard rule found in this case.

Happy Coding!!!