Query Execution Plan (SQL Server)

I am facing an issue in my query return data is too slow , Obviously there are some indexes missing on those table .Trick is when you run query Include Actual execution plan so it will suggest these are the missing indexes if you include this it will make your query faster.

Then just right click and click Missing index detail run that query and see the magic :)

Cursor in SQL

I have a task in which there are some records that are missing in my child table and need to get insert from parent table, records are more then 1000
so by using simple inserting script we can use cursor, I need to insert those users who have  FreeSignUp=1 and call my store procedure do rest of insert thing.




DECLARE @Customer_id numeric
 DECLARE cur_emp CURSOR
STATIC FOR
SELECT  Customer_id from customer where FreeSignUp=1
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN
 FETCH NEXT FROM cur_emp INTO @Customer_id
 WHILE @@Fetch_status = 0
 BEGIN
  EXEC sp_add_Customer @Customer_id

 FETCH NEXT FROM cur_emp INTO @Customer_id
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF


For Ref:
http://www.dotnettricks.com/learn/sqlserver/sql-server-basics-of-cursors

Monthly, Weekly and Daily Report Query

If you want to report that contain filter of daily,weekly,monthly,yearly you can use this query.What you have to do pass a paramter which report you want for
demo purpose I declare in SQL

DECLARE @Period AS VARCHAR(250)
SET @Period = 'Weekly'
select * from table_name WHERE  entry_datetime >= (CASE
       WHEN @Period = 'Daily' THEN Dateadd(day, -1, Getdate())
       WHEN @Period = 'Weekly' THEN Dateadd(week, -1, Getdate())
       WHEN @Period = 'Monthly' THEN Dateadd(month, -1, Getdate())
       WHEN @Period = 'Yearly' THEN Dateadd(year, -1, Getdate())
       END )
 
 
So this query get all the data that exist in this week

Ref:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3d380800-dace-4960-9e4d-5766e3a8fe2f/monthly-weekly-and-daily-reports-question?forum=sqlreportingservices

Database Optimization RND

Now a days our database suck too much so one of my senior make a team so we can optimized a queries who suck CPU time , i did some RND these are the some useful  links that quite helpful may be it can help you as well.

DBCC TRACEON (4199, -1);

https://www.youtube.com/watch?v=Pm8wamJjLAk

https://www.brentozar.com/sql/sql-server-performance-tuning/

https://blog.sqlauthority.com/2007/05/30/sql-server-trace-flags-dbcc-traceon/

https://dba.stackexchange.com/questions/102292/trace-flag-4199-enable-globally
https://sqlserverscotsman.wordpress.com/2016/12/05/trace-flag-4199-query-optimiser-hotfixes/

https://blog.sqlauthority.com/2007/03/23/sql-server-stored-procedure-clean-cache-and-clean-buffer/

http://dbadiaries.com/using-dbcc-freeproccache-to-clear-sql-server-procedure-cache

https://blog.sqlauthority.com/2014/11/02/sql-server-what-are-my-trace-flags-enabled-on-sql-server/

https://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/

https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

https://stackoverflow.com/questions/232333/how-long-should-set-read-committed-snapshot-on-take

Like query ignore spacing in SQL

If your are searching in like query and string contain spaces then it will not search as you know space is also a character
for example in my table my fullname field is [B]Arvind  Kumar  as user is searching with arvind kumar it will not fetch out the records, so what you have to do  use replace function



declare @FullName  as Varchar(MAX)=''
set @FullName='arvind kumar'
set @FullName=REPLACE('%'+@FullName+'%', ' ', '')

select * from Customer WHERE REPLACE(FullName, ' ', '') like   COALESCE('%'+@FullName+'%',customer.FullName)

Validate only certain selected Repter Row Controls like TextBox using ASP.Net Validators.

  In my repter there are textboxes and buttosn, what I am facing that if one of my button click it show in all row to fill the textboxs .
  So i have to do dynmic so it only validate that row in which button is click.In this Shop_Product_id is my unique id you can place your own unique id

   
                <asp:Repeater id="rptProducts" runat="server">
                            <ItemTemplate>
                               
                                           <asp:TextBox ID="txtVoucherCode" runat="server"   ValidationGroup = '<%# "Group_" + Eval("Shop_Product_id").ToString()%>'></asp:TextBox>
                                            <asp:Button ID="btnUpdateSkillVoucher" Text="UpdateVoucher"    ValidationGroup = '<%# "Group_" + Eval("Shop_Product_id").ToString()%>' CssClass="button" runat="server" />
<asp:RequiredFieldValidator runat="server" ID="rqfl" ErrorMessage="Skill Voucher Code Required!"    ValidationGroup = '<%# "Group_" + Eval("Shop_Product_id").ToString() %>' ControlToValidate="txtVoucherCode" SetFocusOnError="True"> </asp:RequiredFieldValidator>
                            </ItemTemplate>
                        </asp:Repeater>

For gridview
https://www.aspsnippets.com/Articles/Validate-only-certain-selected-GridView-Row-Controls-like-TextBox-using-ASPNet-Validators.aspx
                

Conditionally filter on a column in a WHERE clause in SQL Query

I need to apply some date filter in my query but there are some hectic behind it. What I facing in my table some dates are null and some have dates it depend upon my status like
If my status is pending so action date is empty and if somebody  take an action it insert a date.

So I summarize this I need to include my date filter on conditionally based,so what I did
first i make a variable in this I supplied a value 1 when i need this date rang filter otherwise I passed
0 in it.

DECLARE @ISChecked_Apporved_rejected_date AS VARCHAR 

SET ischecked_apporved_rejected_date ="1" 

SELECT 
       * 
FROM   table k
WHERE  k.statusid = 2 
       AND ( ( 0 = CASE 
                     WHEN ( @ISChecked_Apporved_rejected_date = '0' ) THEN 0 
                     ELSE 1 
                   END ) 
              OR ( CONVERT(NVARCHAR(10), k.approveddatetime, 111) BETWEEN 
                         COALESCE(@Approver_Rej_DateFrom, 
                         CONVERT(NVARCHAR(10), @Approver_Rej_DateFrom, 
                         111)) AND COALESCE(@Approver_Rej_DateTo, 
                                   CONVERT(NVARCHAR(10), @Approver_Rej_DateTo, 
                                   111)) ) 
           )

Short query:
Declare @StatusID as INT

set @StatusID=-1 -- it means all

DECLARE @Status_all AS VARCHAR 

 IF @StatusID =-1

 BEGIN                              

  SET @Status_all ='0'                          

    END  

   AND ( ( 0 = CASE 

                     WHEN ( @Status_all = '0' ) THEN 0 

                     ELSE 1 

                   END ) OR (Status_ID = COALESCE(@StatusID,Status_ID))) 

For Reference:
https://stackoverflow.com/questions/1978179/how-to-conditionally-filter-on-a-column-in-a-where-clause