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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s