SQL – JRevell.com

SQL Server – Identify Unused Indexes

September 14, 2018ComputersNo Comments

Within SQL Server an index is used to optimise the reading of data from the database. However the side effect of having such an index is reduced write speed for inserts and updates. This is because SQL Server will need to update and maintain the index. Running the below SQL…

Azure SQL Database View Running Transactions / Blocking

September 21, 2017ComputersNo Comments

With the Azure SQL Database, sometimes you want to view the current running transactions. Unfortunately there is no GUI as such like there is with normal SQL Server (right click, “Activity Monitor”), however it can be grabbed by executing sys.dm_exec_requests. The following SQL is very useful as it shows details…

Azure SQL Database Index Size

September 19, 2017ComputersNo Comments

Recently I’ve been doing some work on optimising indexes within Azure SQL Database. The following SQL from Daniel is very useful in showing the status of all the current indexes within a SQL Database. The SQL works for both SQL Server 2014 onwards, plus Azure SQL Database. I have not…

Delete Duplicate Records – Leave Only 1 Behind MS SQL

February 27, 2017ComputersNo Comments

Recently I’ve had to delete duplicate records from the Record Link table in Microsoft Dynamics Nav. Using the following SQL, (which uses the OVER clause for partitioning) this deletes all duplicate records, leaving the first unique item behind. The SQL can be updated easily to function for other tables /…

Suggesting MS SQL Index Creations Using sys.dm_db_missing_index_details

February 16, 2017ComputersNo Comments

Within SQL Server, sys.dm_db_missing_index_details returns indexes which it believes are required by the Query Optimiser. Basically as queries are run in the background, the Query Optimiser makes a record of any optimisations it feels are necessary. Restarting the SQL server resets all these stats. Using some SQL it is possible…

Microsoft Dynamics NAV – View All Active Sessions

November 23, 2016ComputersNo Comments

With Microsoft Dynamics Nav, there are various ways of viewing all the active sessions within the system. The easiest of which is the “Sessions” page within the software itself: The downside of this, is that it only shows active sessions on the tier which the user is connected. This is…

Convert MS SQL Query to MySQL Automatically

September 28, 2016ComputersNo Comments

With a recent project, I’ve had to reproduce a table from a Microsoft SQL Database into a MySQL Database. The table had lots of fields and would be very time consuming to write the SQL to create the table, but luckily I came across this tool to convert MSSQL to…

SQL Remove Trailing Decimal Places

July 29, 2016ComputersNo Comments

Within a recent project, I’ve been extracting data from Microsoft Dynamics NAV using Taskcentre. Within NAV, decimals are stored as decimal(38,20), complete will all 20 decimal places..! The following SQL query is very handy at removing the un-required decimal places (but keeping the accuracy of the data) The results of…

SQL Server – Find Last Backup Date/Time

March 15, 2016ComputersNo Comments

With SQL server and 3rd party backup software, it can sometimes be difficult to tell when the last SQL backup has been taken and if the backup software has actually performed the backup or not. There are 3 main backup types in SQL Server: Full Incremental Transaction Log The following…

TaskCentre SQL Performance – One way to improve performance

February 24, 2016ComputersNo Comments

When using the “ODBC” or “OLEDB” tools in TaskCentre, it may appear that a single query is executed on the database. In actual fact from trial and error, I have identified multiple queries are being called. For example, if you run the following query (This query identifies all Item Ledger…