The Power of Powershell

My brother-in-law’s 48″ muskie caught while pre-fishing for the 2015 Rhinelander Hodag muskie challenge was an epic battle. The fish hit the lure 10 – 15 feet from the boat, yet managed to stay in the water for what felt like several minutes before submitting to the net. Having the memory of a different large fish that we had lost right at the net about a month earlier, I was desperate not to mess this one up. I wanted to get the net ready as soon as possible, so instead of hooking my lure to the clip right above the reel, I reeled it up as close to the end of the pole as the leader would allow. This ended up being a mistake since the fish swam circles around the boat several times, and I had to carefully move the pole each time. At the same time, I was holding the net and trying not to get it hooked on anything inside the boat. It was a high pressure situation where I had to do several things at once, and we ultimately succeeded in landing the giant fish.

Jason's muskie
Powershell is great for doing multiple things at once. Maybe not technically at the same time, but the skilled DBA can create scripts to automate many different tasks which can be run one right after another. This is a great way to accomplish a large amount, which would take quite a while to do manually. I have a set of scripts that create an Excel workbook each day with a set of metrics about each of my instances, allowing me to see any possible problems immediately upon getting in each morning. The script shows failed SQL Agent jobs, backup ages, disk usage, and file used size. It highlights in red anything that needs attention, such as files that need to be manually grown or databases that haven’t been backed up recently. I am not great at powershell, but I was able to find some examples online and modify them to work with my current environment.

One personal goal I have for the upcoming year is to find more ways to use powershell to increase my efficiency. In order to begin doing that, I’ve started to practice using powershell. As I mentioned in my previous post, I’m using http://adventofcode.com/ to help with this challenge.
The second day’s challenge involves a list of dimensions of boxes that need to be wrapped up and tied with a bow by Santa’s elves. Given the list, which contains 1,000 length X width X height measurements, I need to get the total amount of feet needed where each needs the surface area of each box plus a little extra slack, the area of the smallest size. I also needed to get the ribbon around the gift, which was the smallest perimeter around the box plus a litle extra for bow, equal to the cubic volume of the box. I learned some basic commands in Powershell that I either hadn’t learned or had forgotten, such as how to ingest a text file into a variable and how to split an array. In the end, it took a few tries but I got the answer correct for both parts of the question. Below is my final code for each.

$dimensions = get-content N:\adventday2input1.txt
$feet = 0
foreach ($dim in $dimensions) {
[int]$l,[int]$w,[int]$h = $dim.split('x',3)
$feet = $feet + $l*$w*2
$feet = $feet + $l*$h*2
$feet = $feet + $h*$w*2
$intmin = [math]::min($l*$w,$l*$h)
$feet = $feet + [math]::min($intmin,$w*$h)
}
$feet
$dimensions = get-content N:\adventday2input1.txt
$feet = 0
foreach ($dim in $dimensions) {
[int]$l,[int]$w,[int]$h = $dim.split('x',3)
$intmin = [math]::min(2*$l+ 2*$w,2*$l+2*$h)
$feet = $feet + [math]::min($intmin,2*$w+2*$h)
$feet = $feet + $l*$w*$h
}
$feet

In summary, Powershell is a great tool and I’m looking forward to increasing my use of it as 2016 progresses.

Advertisements

Coding Challenge – Advent of Code

Although my true passion will always be fishing, I’ve started bow hunting this year. It isn’t quite as fun (although maybe it’ll get better once I actually see some deer), but it’s still a nice way to get out and enjoy nature. Especially during this time of year when it’s too cold to launch a boat but not yet cold enough to ice fish, it’s great to have another way to get out of the house. I’ve found that muskie fishing is probably closer to deer hunting than it is to other types of fishing; you are waiting all day for just the chance to see a muskie/deer, but when you do it’s very exciting. It takes a great deal of though and preparation to be successful.

I was recently shown a set of challenges called Advent of Code. Its a fun way to practice coding and keep your skills fresh. I mainly code in SQL, but used to do quite a bit in Access and Excel VBA, and a small amount in C# and Powershell. I’d like to use this Advent of Code challenge to brush up on my skills and hopefully learn a bit more about Powershell, which can be a big asset in administering SQL Server databases.
The first challenge is to interpret a set of instructions where the parentheses symbols are used to tell Santa to go up or down in a house; right paranthesis “(” tells Santa to go up a floor, left “)” tells him to go down a level. In the two part question, we need to figure out first what floor he ends up at and second to figure out which step first takes him below level 0, into the basement. I solved this one by pasting the instructions, which was 7000 characters long, into a cell in Excel and then running some VBA code to get the answers. I got both parts correct on my first try. Here’s the code I used:

Sub Solver()

    Dim i As Integer
    Dim flr As Integer
    
    For i = 1 To Len(Me.Cells(1, 1).Value)
        If Mid(Me.Cells(1, 1).Value, i, 1) = "(" Then
            flr = flr + 1
        Else
            flr = flr - 1
        End If
    Next i
    
    Debug.Print flr

End Sub
Sub Solver2()

    Dim i As Integer
    Dim flr As Integer
    
    For i = 1 To Len(Me.Cells(1, 1).Value)
        If Mid(Me.Cells(1, 1).Value, i, 1) = "(" Then
            flr = flr + 1
        Else
            flr = flr - 1
        End If
        If flr = -1 Then
            Debug.Print i
            Exit Sub
        End If
    Next i
    

End Sub

If you’re looking for a fun challenge or a way to grow your coding skills, definitely check out the Advent of Code.

How to Prove Database Concepts

Although it is still late fall, there is usually plenty of snow on the ground by now here in Wisconsin. I say usually because this year is different. We’ve experienced a warm fall and the temperatures outside right now are still in the 40s. It made fall fishing more comfortable but less consistent. The tough thing right now is knowing that these beautiful late fall days can’t be enjoyed while muskie fishing since the season ended December 1st. Most the lakes in all of Wisconsin are still open (no ice), but they can only be enjoyed by the walleye and panfishermen. I’ve started bow hunting this year and that is keeping me in the outdoors, but it isn’t nearly as enjoyable as being out on the water, patrolling the depths for an elusive trophy. Fishing will open again on May 7th, which feels like an eternity away.
Disagreements in the workplace are inevitable. They don’t have to be contentious, and they are almost always positive in the long run. They force each side to reinforce their knowledge by proving out their position. If I believe using a 1:1 virtual machine for my database server is a better option, I need to be able to describe why in a factual and eloquent manner. If I believe the resource costs of virtualization aren’t worth the benefits, I’d better be prepared to back that up. I like to make my case by stating my opinion, showing the basis for that opinion in the documentation (MSDN, Technet), and then showing an example.
Recently I had a call with one of our software vendors. There was a disagreement about whether the use of variable length fields would be a benefit to the database – all their tables were using fixed length fields (char instead of varchar). Below is an email I sent them that I felt demonstrated the benefits of variable length fields.

Hi [Recipients]

 

I felt like you weren’t really convinced by my declaration yesterday on some of the database issues, so I figured I’d prove them out.  I realize it’s probably too late to make any changes in V14, but my hope is that you will consider these suggestions when building out V15.  I’ll start with the issue with fixed length fields.

 

As I explained yesterday, the slowest operation for any relational database management system is Disk I/O.  Once the data is read, it goes into the buffer pool and can be read, updated, inserted, or deleted in memory, which is very fast.  Even high performance hard drives such as SSDs are slower than operations in memory.  With this in mind, one key way to improve performance in a database is to minimize the amount of information being read from disk.  This can be done by not storing empty spaces in fixed length fields and instead using variable length fields.  Microsoft’s guidance on when to use variable versus fixed length fields is:

  • Use char when the sizes of the column data entries are consistent.
  • Use varchar when the sizes of the column data entries vary considerably.
  • Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

So as an example, the miscX fields, which are the company defined fields, will not only vary wildly but also often be blank when they are not being used.  This would be a perfect candidate for variable length fields since instead of storing 40 empty spaces of data which would require 40 bytes of disk space, you’d be storing a zero length string, which would require 2 bytes.  Variable length fields can cause fragmentation problems when updates to the fields cause the row to exceed 8060 bytes (your extremely wide tables exacerbate this problem).  When this happens one or more of the variable length fields are pushed off the page onto separate data pages that are normally used for LOB data.  However, SQL Server will stop you from creating tables with fixed length rows greater than 8060 bytes anyway, so you can easily avoid this scenario by enforcing your existing field size limits.  We also talked about the large object data types yesterday, such as the deprecated text fields that are in your database.  Text fields are automatically put on pages reserved for LOB data whether they contain any value or not.  Varchar(max) fields are different.  Once the data in these fields exceeds 8,000 bytes, it will automatically be pushed off the data page onto a separate type of page used to store large data objects.

 

So let’s take a look at some of the existing performance metrics, and see how they would improve based on the suggested changes.  I’ll use the tablename table as the example, but this would apply to any tables with fixed length fields, and especially to those tables with miscX fields.

Here is the current size of the tablename table, using Query 1:

20151214 field length argument current size

You can see that we are averaging just under two rows per page.  We are also using 849 pages for text data, most of which are blank in our database.

Here are the stats for pulling all data from the table.  I ran it three times to account for data entering the buffer pool.

20151214 original table trace

CPU is averaging 344, 93K reads, and over a second and a half in duration.

 

Next I’ll create a new version of the table using variable length fields instead of fixed length.  The DDL is in Query 2.  It is important to note that I am also replacing the text fields with varchar(max).  This allows the smaller LOB data to sit on the same page as the row data until it becomes too big to fit.  I load it with everything in tablename (minus the useless spaces) in Query 3.

Here is the current size of the tablename_improved table, again using the logic from Query 1:

20151214 Improved table size

You can see the size has gone down by 80% on the clustered index and 66% on the nonclustered indexes.  We’re also no longer using any LOB pages.

 

Now look at the improvement in our query performance:

20151214 improved table trace

The CPU has decreased by about 50%, the reads have decreased by 99.5% (mostly due to the text -> varchar(max) change), and the duration has decreased by 66%, a great improvement.

Because every data page that is read first has to be pulled into the buffer pool, and because the buffer pool is limited by the amount of memory on the server and all databases on the server share it, the performance impact of pulling all this extra blank data into memory has a negative performance impact  not only on your PLM application, but all applications that are using the SQL Server instance.

I hope this has shown you the value of using variable length fields instead of fixed length fields.

Thank you

 

Appendix:

Query 1

SELECT  OBJECT_NAME(i.OBJECT_ID) AS 'tableName' ,
        i.name AS 'indexName' ,
        i.type_desc ,
        MAX(p.partition_number) AS 'partitions' ,
        SUM(p.rows) AS 'rows' ,
        SUM(au.data_pages) AS 'dataPages' ,
        CAST(SUM(p.rows)*1.0 / SUM(au.data_pages) AS DECIMAL(10,1)) AS 'rowsPerPage',
              SUM(ps.lob_reserved_page_count) AS 'LOBPages'
FROM    sys.indexes AS i
        JOIN sys.partitions AS p ON i.OBJECT_ID = p.OBJECT_ID
                                    AND i.index_id = p.index_id
              JOIN sys.dm_db_partition_stats ps ON ps.index_id = i.index_id AND ps.OBJECT_ID = i.OBJECT_ID AND ps.partition_id = p.partition_id
        JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id
WHERE   OBJECT_NAME(i.OBJECT_ID) = 'tablename_improved'
        AND au.type_desc = 'IN_ROW_DATA'
GROUP BY OBJECT_NAME(i.OBJECT_ID) ,
        i.name ,
        i.type_desc
ORDER BY rowsPerPage;

 

Query 2

CREATE TABLE [dbo].[tablename_improved](
       [season] [varchar](15) NOT NULL,
       [style] [varchar](30) NOT NULL,
       [designmemo] [varchar](MAX) NOT NULL,
       [designimg] [varchar](50) NOT NULL,
       [id_style] [int] IDENTITY(1,1) NOT NULL,
       [expdate] [datetime] NOT NULL,
       [origexpdate] [datetime] NOT NULL,
       [status] [varchar](50) NOT NULL,
       [cycledays] [int] NOT NULL,
       [laststep] [varchar](50) NOT NULL,
       [dayslate] [int] NOT NULL,
       [inidate] [datetime] NOT NULL,
       [stgscode] [varchar](50) NOT NULL,
       [completed] [bit] NOT NULL,
       [datecompleted] [datetime] NOT NULL,
       [Revision] [varchar](10) NOT NULL,
       [Desce] [varchar](50) NOT NULL,
       [Publish] [bit] NOT NULL,
       [DesnImage] [varchar](MAX) NOT NULL,
       [DistSpecs] [bit] NOT NULL,
       [LastDate] [datetime] NOT NULL,
       [WasRevised] [bit] NOT NULL,
       [misc1] [varchar](30) NOT NULL,
       [misc2] [varchar](30) NOT NULL,
       [misc3] [varchar](30) NOT NULL,
       [misc4] [varchar](30) NOT NULL,
       [misc5] [varchar](30) NOT NULL,
       [misc6] [varchar](30) NOT NULL,
       [misc7] [varchar](30) NOT NULL,
       [misc8] [varchar](30) NOT NULL,
       [misc9] [varchar](30) NOT NULL,
       [misc10] [varchar](30) NOT NULL,
       [misc11] [varchar](30) NOT NULL,
       [misc12] [varchar](30) NOT NULL,
       [misc13] [varchar](30) NOT NULL,
       [misc14] [varchar](30) NOT NULL,
       [misc15] [varchar](30) NOT NULL,
       [misc16] [varchar](30) NOT NULL,
       [misc17] [varchar](30) NOT NULL,
       [misc18] [varchar](30) NOT NULL,
       [misc19] [varchar](30) NOT NULL,
       [misc20] [varchar](30) NOT NULL,
       [prototype] [bit] NOT NULL,
       [startdate] [datetime] NULL,
       [wholecost] [numeric](14, 4) NOT NULL,
       [WholePrice] [numeric](14, 4) NOT NULL,
       [mainimage] [varchar](10) NOT NULL,
       [scale] [varchar](15) NOT NULL,
       [ManufPrice] [numeric](14, 4) NOT NULL,
       [RetailPrice] [numeric](14, 4) NOT NULL,
       [WholeMargin] [numeric](14, 4) NOT NULL,
       [RetailMargin] [numeric](14, 4) NOT NULL,
       [manufcost] [numeric](14, 4) NOT NULL,
       [manufmargin] [numeric](14, 4) NOT NULL,
       [retailcost] [numeric](14, 4) NOT NULL,
       [Forecast] [numeric](10, 0) NOT NULL,
       [unit] [varchar](4) NOT NULL,
       [gartype] [varchar](5) NOT NULL,
       [PDMisc1] [varchar](30) NOT NULL,
       [PDMisc2] [varchar](30) NOT NULL,
       [PDMisc3] [varchar](30) NOT NULL,
       [stylestatus] [varchar](15) NOT NULL,
       [hasColorWay] [bit] NOT NULL,
       [chartcode] [varchar](10) NOT NULL,
       [chartDescription] [varchar](50) NOT NULL,
       [LabPacknotes] [varchar](MAX) NULL,
       [Constnotes] [varchar](MAX) NULL,
       [colwnotes] [varchar](MAX) NULL,
       [labnotes] [varchar](MAX) NULL,
       [ProtoSeason] [varchar](15) NOT NULL,
       [ProtoStyle] [varchar](30) NOT NULL,
       [groupName] [varchar](20) NOT NULL,
       [sizespecs] [varchar](MAX) NULL,
       [colorspecs] [varchar](MAX) NULL,
       [cost] [numeric](8, 2) NOT NULL,
       [saleprice] [numeric](8, 2) NOT NULL,
       [ldpcost] [numeric](10, 4) NOT NULL,
       [retailconfirmed] [bit] NOT NULL,
       [costconfirmed] [bit] NOT NULL,
       [plannedunits] [numeric](11, 0) NOT NULL,
       [id_calendar] [int] NOT NULL,
       [morefields] [varchar](MAX) NULL,
       [wboardMaster] [numeric](1, 0) NOT NULL,
       [optLocked] [numeric](1, 0) NOT NULL,
       [RHReady] [bit] NOT NULL,
       [lastexported] [datetime] NOT NULL,
       [lastedited] [datetime] NOT NULL,
       [imgfront] [varchar](10) NOT NULL,
       [imgBack] [varchar](10) NOT NULL,
       [imgSide] [varchar](10) NOT NULL,
       [imgDetail] [varchar](10) NOT NULL,
       [Frontxt] [varchar](30) NOT NULL,
       [backtxt] [varchar](30) NOT NULL,
       [sidetxt] [varchar](30)  NOT NULL,
       [detailtxt] [varchar](30) NOT NULL,
       [misc21] [varchar](30) NOT NULL,
       [misc22] [varchar](30) NOT NULL,
       [misc23] [varchar](30) NOT NULL,
       [misc24] [varchar](30) NOT NULL,
       [misc25] [varchar](30) NOT NULL,
       [misc26] [varchar](30) NOT NULL,
       [misc27] [varchar](30) NOT NULL,
       [misc28] [varchar](30) NOT NULL,
       [misc29] [varchar](30) NOT NULL,
       [misc30] [varchar](30) NOT NULL,
       [misc31] [varchar](30) NOT NULL,
       [misc32] [varchar](30) NOT NULL,
       [misc33] [varchar](30) NOT NULL,
       [misc34] [varchar](30) NOT NULL,
       [misc35] [varchar](30) NOT NULL,
       [misc36] [varchar](30) NOT NULL,
       [misc37] [varchar](30) NOT NULL,
       [misc38] [varchar](30) NOT NULL,
       [misc39] [varchar](30) NOT NULL,
       [misc40] [varchar](30) NOT NULL,
       [descrev] [varchar](10) NOT NULL,
       [batchNo] [varchar](10) NOT NULL,
       [collab_issues] [int] NOT NULL,
       [prob] [varchar](250) NOT NULL,
       [Attachments] [int] NOT NULL,
       [CreatedDate] [datetime] NOT NULL,
       [Createdby] [varchar](25) NOT NULL,
       [Editedby] [varchar](25) NOT NULL,
       [LastTabEdited] [varchar](25) NOT NULL,
       [CreatedOn] [datetime] NOT NULL,
       [ModifiedBy] [varchar](25) NOT NULL,
       [ModifiedOn] [datetime] NOT NULL,
       [RowVersion] [int] NOT NULL,
       [hts] [varchar](20) NOT NULL,
       [TargetFOB] [money] NOT NULL,
       [QuotedFOB] [money] NOT NULL,
       [ActualFOB] [money] NOT NULL,
       [CurrentFOB] [money] NOT NULL,
       [LandedCost] [money] NOT NULL,
       [xTargetFOB] [money] NOT NULL,
       [xQuotedFOB] [money] NOT NULL,
       [xActualFOB] [money] NOT NULL,
       [xCurrentFOB] [money] NOT NULL,
       [xLandedCost] [money] NOT NULL,
       [xRetailPrice] [money] NOT NULL,
       [xWholePrice] [money] NOT NULL,
       [xWholeMargin] [money] NOT NULL,
       [isset] [bit] NOT NULL,
       [sidemeasurement] [bit] NOT NULL,
       [LastSeason] [varchar](15) NULL,
       [LastStyle] [varchar](30) NULL,
       [lineplanorder] [numeric](6, 0) NOT NULL,
CONSTRAINT [PK_tablename_improved] PRIMARY KEY CLUSTERED 
(
       [id_style] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [Design Styles Improved: Style/Season Duplicated] UNIQUE NONCLUSTERED 
(
       [style] ASC,
       [season] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [tablenameseastyle_improved] ON [dbo].[tablename_improved]
(
[season] ASC,
[style] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
GO

CREATE NONCLUSTERED INDEX [ix_tablenameStyleSeasonID_calendar_imp] ON [dbo].[tablename_improved]
(
[style] ASC,
[season] ASC
)
INCLUDE (     [id_calendar]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
GO

I didn’t paste in all the constraints, but I did add them to the table.

Query 3

SET IDENTITY_INSERT dbo.tablename_improved ON;

INSERT  INTO eSPSv14Prod.dbo.tablename_improved
( season ,
style ,
designmemo ,
designimg ,
id_style ,
expdate ,
origexpdate ,
status ,
cycledays ,
laststep ,
dayslate ,
inidate ,
stgscode ,
completed ,
datecompleted ,
Revision ,
Desce ,
Publish ,
DesnImage ,
DistSpecs ,
LastDate ,
WasRevised ,
misc1 ,
misc2 ,
misc3 ,
misc4 ,
misc5 ,
misc6 ,
misc7 ,
misc8 ,
misc9 ,
misc10 ,
misc11 ,
misc12 ,
misc13 ,
misc14 ,
misc15 ,
misc16 ,
misc17 ,
misc18 ,
misc19 ,
misc20 ,
prototype ,
startdate ,
wholecost ,
WholePrice ,
mainimage ,
scale ,
ManufPrice ,
RetailPrice ,
WholeMargin ,
RetailMargin ,
manufcost ,
manufmargin ,
retailcost ,
Forecast ,
unit ,
gartype ,
PDmisc1 ,
PDMisc2 ,
PDMisc3 ,
stylestatus ,
hasColorWay ,
CHARtcode ,
CHARtDescription ,
LabPacknotes ,
Constnotes ,
colwnotes ,
labnotes ,
ProtoSeason ,
ProtoStyle ,
groupName ,
sizespecs ,
colorspecs ,
cost ,
saleprice ,
ldpcost ,
retailconfirmed ,
costconfirmed ,
plannedunits ,
id_calendar ,
morefields ,
wboardMaster ,
optLocked ,
RHReady ,
lastexported ,
lastedited ,
imgfront ,
imgBack ,
imgSide ,
imgDetail ,
Frontxt ,
backtxt ,
sidetxt ,
detailtxt ,
misc21 ,
misc22 ,
misc23 ,
misc24 ,
misc25 ,
misc26 ,
misc27 ,
misc28 ,
misc29 ,
misc30 ,
misc31 ,
misc32 ,
misc33 ,
misc34 ,
misc35 ,
misc36 ,
misc37 ,
misc38 ,
misc39 ,
misc40 ,
descrev ,
batchNo ,
collab_issues ,
prob ,
Attachments ,
CreatedDate ,
CreatedBy ,
Editedby ,
LastTabEdited ,
CreatedOn ,
ModifiedBy ,
ModifiedOn ,
RowVersion ,
hts ,
TargetFOB ,
QuotedFOB ,
ActualFOB ,
CurrentFOB ,
LandedCost ,
xTargetFOB ,
xQuotedFOB ,
xActualFOB ,
xCurrentFOB ,
xLandedCost ,
xRetailPrice ,
xWholePrice ,
xWholeMargin ,
isset ,
sidemeasurement ,
LastSeason ,
LastStyle ,
lineplanorder
)
SELECT  RTRIM(LTRIM(season)) ,
RTRIM(LTRIM(style)) ,
RTRIM(LTRIM(CAST(designmemo AS VARCHAR(MAX)))) ,
RTRIM(LTRIM(designimg)) ,
RTRIM(LTRIM(id_style)) ,
RTRIM(LTRIM(expdate)) ,
RTRIM(LTRIM(origexpdate)) ,
RTRIM(LTRIM(status)) ,
RTRIM(LTRIM(cycledays)) ,
RTRIM(LTRIM(laststep)) ,
RTRIM(LTRIM(dayslate)) ,
RTRIM(LTRIM(inidate)) ,
RTRIM(LTRIM(stgscode)) ,
RTRIM(LTRIM(completed)) ,
RTRIM(LTRIM(datecompleted)) ,
RTRIM(LTRIM(Revision)) ,
RTRIM(LTRIM(Desce)) ,
RTRIM(LTRIM(Publish)) ,
RTRIM(LTRIM(CAST(DesnImage AS VARCHAR(MAX)))) ,
RTRIM(LTRIM(DistSpecs)) ,
RTRIM(LTRIM(LastDate)) ,
RTRIM(LTRIM(WasRevised)) ,
RTRIM(LTRIM(misc1)) ,
RTRIM(LTRIM(misc2)) ,
RTRIM(LTRIM(misc3)) ,
RTRIM(LTRIM(misc4)) ,
RTRIM(LTRIM(misc5)) ,
RTRIM(LTRIM(misc6)) ,
RTRIM(LTRIM(misc7)) ,
RTRIM(LTRIM(misc8)) ,
RTRIM(LTRIM(misc9)) ,
RTRIM(LTRIM(misc10)) ,
RTRIM(LTRIM(misc11)) ,
RTRIM(LTRIM(misc12)) ,
RTRIM(LTRIM(misc13)) ,
RTRIM(LTRIM(misc14)) ,
RTRIM(LTRIM(misc15)) ,
RTRIM(LTRIM(misc16)) ,
RTRIM(LTRIM(misc17)) ,
RTRIM(LTRIM(misc18)) ,
RTRIM(LTRIM(misc19)) ,
RTRIM(LTRIM(misc20)) ,
RTRIM(LTRIM(prototype)) ,
RTRIM(LTRIM(startdate)) ,
RTRIM(LTRIM(wholecost)) ,
RTRIM(LTRIM(WholePrice)) ,
RTRIM(LTRIM(mainimage)) ,
RTRIM(LTRIM(scale)) ,
RTRIM(LTRIM(ManufPrice)) ,
RTRIM(LTRIM(RetailPrice)) ,
RTRIM(LTRIM(WholeMargin)) ,
RTRIM(LTRIM(RetailMargin)) ,
RTRIM(LTRIM(manufcost)) ,
RTRIM(LTRIM(manufmargin)) ,
RTRIM(LTRIM(retailcost)) ,
RTRIM(LTRIM(Forecast)) ,
RTRIM(LTRIM(unit)) ,
RTRIM(LTRIM(gartype)) ,
RTRIM(LTRIM(PDMisc1)) ,
RTRIM(LTRIM(PDMisc2)) ,
RTRIM(LTRIM(PDMisc3)) ,
RTRIM(LTRIM(stylestatus)) ,
RTRIM(LTRIM(hasColorWay)) ,
RTRIM(LTRIM(CHARtcode)) ,
RTRIM(LTRIM(CHARtDescription)) ,
RTRIM(LTRIM(CAST(LabPacknotes AS VARCHAR(MAX)))) ,
RTRIM(LTRIM(CAST(Constnotes AS VARCHAR(MAX)))) ,
RTRIM(LTRIM(CAST(colwnotes AS VARCHAR(MAX)))) ,
RTRIM(LTRIM(CAST(labnotes AS VARCHAR(MAX)))) ,
RTRIM(LTRIM(ProtoSeason)) ,
RTRIM(LTRIM(ProtoStyle)) ,
RTRIM(LTRIM(groupName)) ,
RTRIM(LTRIM(CAST(sizespecs AS VARCHAR(MAX)))) ,
RTRIM(LTRIM(CAST(colorspecs AS VARCHAR(MAX)))) ,
RTRIM(LTRIM(cost)) ,
RTRIM(LTRIM(saleprice)) ,
RTRIM(LTRIM(ldpcost)) ,
RTRIM(LTRIM(retailconfirmed)) ,
RTRIM(LTRIM(costconfirmed)) ,
RTRIM(LTRIM(plannedunits)) ,
RTRIM(LTRIM(id_calendar)) ,
RTRIM(LTR(CAST(morefields AS VARCHAR(MAX)))) ,
RTRIM(LTRIM(wboardMaster)) ,
RTRIM(LTRIM(optLocked)) ,
RTRIM(LTRIM(RHReady)) ,
RTRIM(LTRIM(lastexported)) ,
RTRIM(LTRIM(lastedited)) ,
RTRIM(LTRIM(imgfront)) ,
RTRIM(LTRIM(imgBack)) ,
RTRIM(LTRIM(imgSide)) ,
RTRIM(LTRIM(imgDetail)) ,
RTRIM(LTRIM(Frontxt)) ,
RTRIM(LTRIM(backtxt)) ,
RTRIM(LTRIM(sidetxt)) ,
RTRIM(LTRIM(detailtxt)) ,
RTRIM(LTRIM(misc21)) ,
RTRIM(LTRIM(misc22)) ,
RTRIM(LTRIM(misc23)) ,
RTRIM(LTRIM(misc24)) ,
RTRIM(LTRIM(misc25)) ,
RTRIM(LTRIM(misc26)) ,
RTRIM(LTRIM(misc27)) ,
RTRIM(LTRIM(misc28)) ,
RTRIM(LTRIM(misc29)) ,
RTRIM(LTRIM(misc30)) ,
RTRIM(LTRIM(misc31)) ,
RTRIM(LTRIM(misc32)) ,
RTRIM(LTRIM(misc33)) ,
RTRIM(LTRIM(misc34)) ,
RTRIM(LTRIM(misc35)) ,
RTRIM(LTRIM(misc36)) ,
RTRIM(LTRIM(misc37)) ,
RTRIM(LTRIM(misc38)) ,
RTRIM(LTRIM(misc39)) ,
RTRIM(LTRIM(misc40)) ,
RTRIM(LTRIM(descrev)) ,
RTRIM(LTRIM(batchNo)) ,
RTRIM(LTRIM(collab_issues)) ,
RTRIM(LTRIM(prob)) ,
RTRIM(LTRIM(Attachments)) ,
RTRIM(LTRIM(CreatedDate)) ,
RTRIM(LTRIM(Createdby)) ,
RTRIM(LTRIM(Editedby)) ,
RTRIM(LTRIM(LastTabEdited)) ,
RTRIM(LTRIM(CreatedOn)) ,
RTRIM(LTRIM(ModifiedBy)) ,
RTRIM(LTRIM(ModifiedOn)) ,
RTRIM(LTRIM(RowVersion)) ,
RTRIM(LTRIM(hts)) ,
RTRIM(LTRIM(TargetFOB)) ,
RTRIM(LTRIM(QuotedFOB)) ,
RTRIM(LTRIM(ActualFOB)) ,
RTRIM(LTRIM(CurrentFOB)) ,
RTRIM(LTRIM(LandedCost)) ,
RTRIM(LTRIM(xTargetFOB)) ,
RTRIM(LTRIM(xQuotedFOB)) ,
RTRIM(LTRIM(xActualFOB)) ,
RTRIM(LTRIM(xCurrentFOB)) ,
RTRIM(LTRIM(xLandedCost)) ,
RTRIM(LTRIM(xRetailPrice)) ,
RTRIM(LTRIM(xWholePrice)) ,
RTRIM(LTRIM(xWholeMargin)) ,
RTRIM(LTRIM(isset)) ,
RTRIM(LTRIM(sidemeasurement)) ,
RTRIM(LTRIM(LastSeason)) ,
RTRIM(LTRIM(LastStyle)) ,
RTRIM(LTRIM(lineplanorder))
FROM    eSPSv14Prod.dbo.tablename;

SET IDENTITY_INSERT dbo.tablename_improved ON;

*note the trim functions can’t be used on text fields, but could have on varchar(max), which is the preferred lob text data type.