Query SQL Server transactions - per day, hour and minute

This post will help you query your SQL Server for transaction count at both the server level and for each of the databases.  The query returns transaction statistics per day, hour and minute since the time of the last service restart.

/* SQL Server transactions per day/hour/minute using sys.dm_os_performance_counters */

       -- declarations

       DECLARE      

              @Days SMALLINT,

              @Hours INT,

              @Minutes BIGINT,

              @LastRestart DATETIME;

       -- get last restart date

       SELECT 

              @Days = DATEDIFF(D, create_date, GETDATE()),@LastRestart = create_date

       FROM   

              sys.databases

       WHERE  

              database_id = 2;

       -- collect days/hours since last restart

       SELECT @Days = CASE WHEN @Days = 0 THEN 1 ELSE @Days END;

       SELECT @Hours = @Days * 24;

       SELECT @Minutes = @Hours * 60;

       -- trans since last restart

       SELECT 

              @LastRestart [LastRestart],

              @@servername [Instance],

              cntr_value [TotalTransSinceLastRestart],

              cntr_value / @Days   [AvgTransPerDay],

              cntr_value / @Hours  [AvgTransPerHour],

              cntr_value / @Minutes [AvgTransPerMinute]

       FROM

              sys.dm_os_performance_counters

       WHERE  

              counter_name = 'Transactions/sec'

              AND instance_name = '_Total';

       -- trans since last restart per database

       SELECT 

              @LastRestart [LastRestart],

              @@servername [Instance],

              instance_name [Database_Name],

              cntr_value [TotalTransSinceLastRestart],

              cntr_value / @Days [AvgTransPerDay],

              cntr_value / @Hours  [AvgTransPerHour],

              cntr_value / @Minutes [AvgTransPerMinute]

       FROM  

              sys.dm_os_performance_counters

       WHERE  

              counter_name = 'Transactions/sec'

              AND instance_name <> '_Total'

       ORDER BY

              cntr_value DESC;

Your output will be similar to this:

Take a look at this for more details on the dm_os_performance_counters DMV:     sys.dm_os_performance_counters

CASE / CONCATENATE a full name from multiple columns

Today a customer of mine was trying to query a user's full name out as one column, rather than as three separate columns, FirstName, MiddleName and LastName.  He was having problems with the users who did not have a MiddleName, because the NULLs were causing his concatention to come back empty.  This happens because by default, any string concatenation (+) that includes a NULL value will also produce a NULL result.  This post will show you two ways to work around that, using the AdventureWorks2012.Person.Person table.

Option #1 - CASE 

First I pull out the three columns separately, followed with my customer's statement and finishing with my CASE on the MiddleName.  The end result for records with no MiddleName is derived from First and Last name, rather than NULL:

    SELECT

       FirstName,MiddleName,LastName,

       FirstName + ' ' + MiddleName +' '+ LastName [NoGood],

    CASE 

       WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName

       WHEN MiddleName IS NOT NULL THEN FirstName + ' ' + MiddleName + ' ' + 

        LastName END [FullName]

    FROM

        Person.Person;

Output:

Option #2 - CONCAT_NULL_YIELDS_NULL 

Even easier, you can use CONCAT_NULL_YIELDS_NULL to tell the server to treat the NULLs like empty strings instead of NULLs.  Run exactly the same query as above, but prefaced like this:

    

     SET CONCAT_NULL_YIELDS_NULL OFF; 

   SELECT

       FirstName,MiddleName,LastName,

       ........

       ....

Output:

Pretty snazzy.  BUT -- they say the CONCAT_NULL_YIELDS_NULL will always be ON in a future release, so this 2nd option is not a long-term solution.  See here for more details:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql?view=sql-server-2017

Can I change a normal SQL Server column to a computed column?

Nope.  Per BOL, "To change an existing column to a computed column you must drop and re-create the computed column."   Though because the computed column isn't there yet, I am fairly sure they meant  '...drop and re-create the existing column'.  See here:  Not going to happen

So what do you do?  Well, you could create a new table with computed columns and copy the data into it, OR you could rename your existing columns and create new ones with the intended computations.  I like the 2nd option a little better because I don't have to move a bunch of data around AND because I can see my old and computed columns side by side. Then, when I'm sure the computed columns are what I need -- I drop the old columns.  In this example I am adding 3 new computed columns to my CUSTOMER table.  The computations are based on other columns in the CUSTOMER table, but you see what I mean.  We rename the old column and define the new one w/the desired computation:

    -- rename old column

    EXEC sp_rename 'CUSTOMER.TSLONG', 'TSLONGold', 'COLUMN';

    -- add new column w/computation

    ALTER TABLE CUSTOMER ADD TSLONG AS (CONVERT(BIGINT,TS,0));

    -- rename old column

    EXEC sp_rename 'CUSTOMER.LOOKUPID', 'LOOKUPIDold', 'COLUMN';

    -- add new column w/computation

    ALTER TABLE CUSTOMER ADD LOOKUPID AS (CASE

      LEN(CUSTOMID) WHEN (0) THEN '8-'+CONVERT(NVARCHAR(20),SEQUENCEID,0) ELSE CUSTOMID END);

    -- rename old column

    EXEC sp_rename 'CUSTOMER.GENDER', 'GENDERold', 'COLUMN';

    -- add new column w/computation

    ALTER TABLE CUSTOMER ADD GENDER AS (CASE GENDERCODE

           WHEN (0) THEN 'Unknown'

           WHEN (1) THEN 'Female' END

           WHEN (2) THEN 'Male'

    );

Once you're sure everything is good, you'll use this to DROP the old columns:

       -- drop old columns

       ALTER TABLE CUSTOMER

       DROP COLUMN TSLONGold;

       ALTER TABLE CUSTOMER

       DROP COLUMN LOOKUPIDold;

       ALTER TABLE CUSTOMER

       DROP COLUMN GENDERold;

Or, you could even use this:

       ALTER TABLE CUSTOMER

       DROP COLUMN TSLONGold,LOOKUPIDold,GENDERold;

Did you know you drop more than one table column at a time?  How cool is that?!  Better be safe.

Hope you found this helpful.

List all indexes for all tables in a SQL Server database

Just a quick post to show how to list all indexes for all tables in your SQL Server database.  In this example I am referencing AdventureWorks2012.

       USE AdventureWorks2012;

       SELECT

              DB_NAME() [Database],

              sc.name [Schema],

              o.name [Table],

              i.name [Index],

              i.type_desc [Index Type]

       FROM

              sys.indexes i INNER JOIN sys.objects o

                ON i.object_id = o.object_id INNER JOIN sys.schemas sc

                     ON o.schema_id = sc.schema_id

       WHERE

              i.name IS NOT NULL

              AND o.type = 'U'

       ORDER BY

              o.name, i.type

Sample output:

Which foreign keys reference this table?

BIG task yesterday... ran into some data corruption that fortunately could be repaired by dropping/recreating the table's primary key, but -- that table had 469 foreign keys!  Try to drop the PK on any table with FK references, and you will fail.  For example, here's the error when I try to drop the PK on Person.Address in the AdventureWorks db:

      Msg 3725, Level 16, State 0, Line 18
      The constraint 'PK_Address_AddressID' is being referenced by table      
         'SalesOrderHeader', foreign key constraint 'FK_SalesOrderHeader_Address_ShipToAddressID'.
      Msg 3727, Level 16, State 0, Line 18
      Could not drop constraint. See previous errors.

Ok. So I need to drop/recreate FK_SalesOrderHeader_Address_ShippToAddressID first,  but are there more?  How do I know all of the foreign keys that reference another table's primary key?  Easy sneasy.  Just input the table and schema name from the table you are dropping the PK from.      

     EXEC sp_fkeys @pktable_name = 'Address',@pktable_owner = 'Person'

It's cut off, but you get the point.  These are the foreign keys that I must also address if I am going to drop/recreate the Person.Address primary key.  It's just a few, so easy enough to write up the code for the DROP/CREATE of the foreign keys... but what about that table with 469 foreign keys?!  NOT.  

Check out this post for a great piece that will generate the statements to both drop and recreate the referencing foreign keys for the given @PKname.  It was certainly helpful yesterday! 

And see this for more details on sp_fkeys.   sp_fkeys