Author: shanegrimesblog

SQL–Function To Remove Non-numeric Characters

Such a simple task in hindsight, but if you’re scratching your head about how to remove non-numeric characters from a string, this function should help you.

The function uses the in built STUFF and PATINDEX functions to identify a characters which aren’t numeric (0-9) and replace each non-numeric character with an empty string (‘’).

 

   1: CREATE Function [xFnRemoveNonNumericCharacters](@strText VARCHAR(1000))

   2: RETURNS VARCHAR(1000)

   3: AS

   4: BEGIN

   5:     WHILE PATINDEX('%[^0-9]%', @strText) > 0

   6:     BEGIN

   7:         SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')

   8:     END

   9:     RETURN @strText

  10: END

SQL and the ALT+Shift shortcut

Something i’ve come across recently is the number of developers out there who don’t know about one of my favourite SQL Server management studio(SSMS) shortcuts. The ALT+Shift command to select an area of onscreen real estate.

This shortcut allows you to select a cross section of lines and rows in in SSMS and add/remove text. This is very good when you want to add something simple like a comma, or a table alias to the beginning of a number of columns.  In the example below, we’ll simply add the typename function to a couple of ID’s, in reality this isn’t going to save much time but just imagine the scenarios were you’ve repetitively copied and pasted the same 2-3 letters, line after line.

My starting point:

From here I want to position my cursor between the comma and the ‘S’ on line three.  Next I simply hold ALT+Shift and then press down.

After doing this I can simply start typing, and should see my typed letters appearing on both lines:

Once that is done, I simply need to go to the end of each line and add my closing bracket.

Finally, in the last screenshot I am simply showing how I can select a range of characters over multiple lines and either delete that which is constraint to my select box or replace it with a set of alternative values.

image

SQL Column Capture For Data Documentation

Working with application developers is a somewhat new experience for me, and recently two of us were tasked with documenting and understanding a new (to us) application database.

One of the first steps we looked to identify were which columns in a table were actually used. The qualifying criteria for this was simply any column which had more than one distinct value in it. Now, as we had numerous tables, some with 100+ columns, we decided we wanted to do this programmatically, and not spend hours/days sifting through the objects manually.

The below script is what I have written to complete such a task:

   1: DECLARE @MyTable     nvarchar(50) = 'SALES_ORDER_INVOICE_ITEM'

   2: DECLARE @ObjectID    nvarchar(30) = (SELECT Object_ID FROM sys.objects WHERE name = @MyTable AND Type_Desc ='USER_TABLE')

   3: DECLARE @Statement   nvarchar(max)

   4:  

   5: SET @Statement = (

   6:                                   SELECT

   7:                                   ' WITH CTE AS

   8:                                          (SELECT '+STUFF(

   9:                                                 (SELECT      ', ' + 'COUNT(DISTINCT('+CASE WHEN SYSTEM_TYPE_ID =35 THEN 'CAST('+Name+' as varchar(4000))' ELSE Name END+')) AS '+Name    FROM sys.columns t1 WHERE t1.object_id = t2.object_id FOR XML PATH('')  ) ,1,1,'')+

  10:                                                        ' FROM '+object_name(@ObjectID)+'

  11:                                          )

  12:                                          SELECT '''+@MyTable+'''as tbl

  13:                                                        ,X.Col

  14:                                                        ,X.Cnt

  15:                                                        ,CASE  WHEN type_name(C.system_type_ID) IN (''NVARCHAR'',''NCHAR'') THEN type_name(C.system_type_ID)+''(''+cast(max_length/2 as varchar)+'')''

  16:                                                                      WHEN type_name(C.system_type_ID) IN (''VARCHAR'',''CHAR'') THEN type_name(C.system_type_ID)+''(''+cast(max_length as varchar)+'')''

  17:                                                                      WHEN type_name(C.system_type_ID) = ''DECIMAL'' THEN type_name(C.system_type_ID)+''(''+cast(precision as varchar)+'',''+cast(scale as varchar)+'')''

  18:                                                                      ELSE type_name(C.system_type_ID)

  19:                                                         END as Typ

  20:                                          FROM   CTE

  21:                                          UNPIVOT (cnt for col in ('+STUFF((SELECT     ', '+Name FROM sys.columns t1 WHERE t1.object_id = t2.object_id FOR XML PATH('')    ) ,1,1,'')+')) as X 

  22:                                          INNER JOIN sys.columns c

  23:                                                               on x.col = c.name

  24:                                                               and c.object_id = '+@ObjectID+'

  25:                                          WHERE Cnt >1' AS Txt

  26:                                   FROM        sys.columns t2

  27:                                   WHERE         object_id = @ObjectID

  28:                                   GROUP BY    object_id

  29:                            )

  30: --PRINT @Statement

  31: exec sp_executesql  @Statement

  32:  

The results for the example above are shown below, tbl being the table name, col being the column name, cnt being the number of distinct values that field contains and typ being the data type of the column.

image

As we look to develop this in the future, we will invariably look to add additional information such as primary/foreign keys and constraints. But for now, this has saved us a lot of time on our upfront documentation processes.

SSRS bug– Exporting to Excel creates invalid file

Another new one for me, so another one I decided to write a quick little blog about.

I created a fairly simple report the other day, which has a value column, which the report owner wanted formatting to two decimal places. However when the test user was exporting the file to excel they received the following error message:

Repaired Records: Cell information from /xl/worksheets/sheet1.xml part

Not only that, but on zero value cells in my value column, the zeros were coming through as “0.000000000000000000”

A quick Google search turned up the following link:

https://connect.microsoft.com/SQLServer/feedback/details/2524655/ssrs-export-to-excel-xlsx-creates-invalid-excel-file-when-source-data-contains-zero-with-precision-more-than-14-decimals-places

So in my case the problem occurred when a view, my source stored proc was referencing,, was handling a calculated column as decimal (38,16). The workaround for me was to simply cast the column as (35,13), as testing verified this would not affect my end results.

If you genuinely need to have more numerical columns with a precision higher than 14, then I can only recommend you upvote the bug reported in the link above.

Flip that BIT

Another day another “how have I never come across this problem before”.  So todays challenge is to essentially execute a stored procedure which will allow us to alternate a BIT value. So if the value is 0 make it 1, if it is 1 make it 0.

After a little goggling it seems utilising the Bitwise XOR operator is the best method.

Script:

   1: DECLARE @Bit BIT= 1

   2: SELECT CAST(@Bit^1 AS bit)

While i’m not the best person to preach about the benefits of using this operator it got the job done in this instance, for more information start here:

https://msdn.microsoft.com/en-us/library/ms190277.aspx

Updating linked server columns

Here’s  a fun one I came across this week, we had what was supposed to be a relatively simple update statement taking forever to execute. The update was to an application database on another server, and the table had about 300k rows in it.  We had two very similar update statements and only one of them was causing us any grief.

As mentioned, we were updating an application database, so used a cursor to lock the rows 1 at a time.

   1: DECLARE @co_num NVARCHAR(10)

   2: DECLARE @OrderOutcome NVARCHAR(50)

   3:  

   4: DECLARE CurOrders CURSOR FORWARD_ONLY READ_ONLY FOR

   5:        SELECT        co_num, LEFT(OO.OrderOutcome,40)

   6:        FROM          [SERVER01].DATABASE01.dbo.co co

   7:        INNER JOIN    OrderOutcome OO ON OO.OrderNo = co.ordno

   8:  

   9: OPEN CurOrders

  10: FETCH NEXT FROM CurOrders INTO @co_num, @OrderOutcome

  11:  

  12: WHILE @@FETCH_STATUS = 0

  13:  

  14: BEGIN

  15:        UPDATE [SERVER01]. DATABASE01.dbo.co

  16:        SET    Outcome = @OrderOutcome

  17:        WHERE  co_num = @co_num   

  18:  

  19:        FETCH NEXT FROM CurOrders INTO @co_num, @OrderOutcome

  20:              

  21: END

  22:  

  23: CLOSE CurOrders

  24: DEALLOCATE CurOrders

The original execution plan looked as follows:

So you can see the query is doing a full table scan of the remote table (the table we want to update).

Why is it doing this?  Well after much head scratching we finally identified the difference between our working query and this troublesome query. Our @OrderOutcome parameter is of length 50, where as the data being entered into it is limited to 40 characters. The fix, update the @OrderOutcome parameter to 40 characters…

The resulting execution plan, now looks like this:

I can’t explain why this happens, but it looks to have been a problem ever since SQL 2005, and when you have 2,000 updates to do, doing a table scan for each of those can be quite tedious, but now you know.  Make your parameters the same length as the longest data going into it.

ApexSQL Diff review

My place of work recently rolled out ApexSQL to its development team. Having not used it before, but being familiar with other schema comparison programs I must say I am quite the fan.

Based on the screenshot below which compares a DEV and a PROD version of the same database, you can see it is very easy to see which objects have changed and which objects are new/unique in both databases. I particularly like the way it divides out all of this different objects, which you could choose to exclude from the comparison if you wished.

Another feature I like, which is very common amongst schema comparison tools is the script window at the bottom of the program, which highlights any changes and scrolls both versions of the object in synchronicity making it nice an easy to review all the changes to an object.

One concern I do have, is when scripting changes to be executed at a later date, ApexSQL seems quite indiscriminate in choosing to alter an object or dropping and re-creating the object, which could obviously cause some problems down the line with potential data loss.

But overall, for a company still in the infancy of source/version control this program is a welcome relief when it comes to deploying (only) changes to an existing database, and no ApexSQL have not paid me for any of this review.

For more information please visit: https://www.apexsql.com/