Articles Posted by the Author:

  • NULL Values in Histogram

    During my cardinality estimator presentation in DBA virtual group there was a question asked about NULL values and histogram. I am quoting the question as is “SQL Server include null values in the index. How are statistics handle for null values?”

    I will answer the question in this blog post.

    For demo purpose I will be using WideWorldImporters database which you can download and restore from here. In table [Sales].[Orders] column ‘PickingCompletedWhen’ does allow NULL values.

    How many rows are NULL and how many are NOT NULL?

    USE [WideWorldImporters];
    GO
    SELECT 'NULL' AS [Value], COUNT(*) AS [NumOfRecord]
    FROM [Sales].[Orders]
    



  • Warning: Non-existent step referenced While Creating SQL Agent Job

    Warning: Non-existent step referenced While Creating SQL Agent Job

    Today there was a question submitted in dba.stackexchange.com with heading “Suppressing non-ANSI warnings in SQLCMD script”. Details here. In summary, this happens when you use a script to create a SQL Agent job and one of the job step has conditions (under advanced properties) to go to certain steps in case of success or failure. Exact text of the warning:

    Warning: Non-existent step referenced by @on_success_step_id.
    Warning: Non-existent step referenced by @on_fail_step_id.

    I searched for the keywords “Warning: Non-existent step referenced” and looked at the top 10 results. Most of them talked about properly setting advance property of each …


  • How to Exclude Read_Only Databases Using sp_MSforeachdb

    How to Exclude Read_Only Databases Using sp_MSforeachdb

    I will explain how you can use sp_MSforeachdb stored procedure against READ_WRITE database only.

    There are numerous articles on the web about excluding databases using database id or name. Recently I needed a script to run against databases that are part of Always On Availability Groups and in READ_WRITE status. I used the ‘Updateability’ properties of DATABASEPROPERTYEX function.

    Here is how I implemented this.

    SET NOCOUNT ON
    
    EXEC sp_MSForEachDb '
    USE [?]
    IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'') 
    IF ((SELECT DATABASEPROPERTYEX(''?'', ''Updateability''))=''read_write''  )
    BEGIN
    .....
    .....
    END
    '
    

    I would like to know if you implemented same …