Excessive amount of data received from server

Question

We are having a problem with slow queries on a certain table. We tried some things with indexes but that didn't make any difference. Then we discovered that the amount of data being transferred was excessively high.

The table definition is as follows:

/****** Object:  Table [dbo].[survey_tbMultiLanguageText]    Script Date: 23-6-2015 17:45:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[survey_tbMultiLanguageText](
    [fMultiLanguageTextId] [int] IDENTITY(1,1) NOT NULL,
    [fProjectID] [int] NOT NULL,
    [fElementTypeID] [int] NOT NULL,
    [fElementCode] [varchar](50) NOT NULL,
    [fPropertyID] [int] NOT NULL,
    [fLanguageID] [int] NOT NULL,
    [fText] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_survey_tbMultiLanguageText] PRIMARY KEY CLUSTERED 
(
    [fMultiLanguageTextId] 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]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText]  WITH CHECK ADD  CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbLanguage] FOREIGN KEY([fLanguageID])
REFERENCES [dbo].[survey_tbLanguage] ([fLanguageID])
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText] CHECK CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbLanguage]
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText]  WITH CHECK ADD  CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbProject] FOREIGN KEY([fProjectID])
REFERENCES [dbo].[survey_tbProject] ([fProjectID])
GO

ALTER TABLE [dbo].[survey_tbMultiLanguageText] CHECK CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbProject]
GO

If I query this table like this:

select top 100 *
from survey_tbMultiLanguageText

This results in bytes received of 1.284202E+07 (12.25 MB)

Within these 100 records the max length of fText is 380, and if I convert to nvarchar(380) the number of bytes received is much lower (79339).

The database server I'm testing this on is (select @@VERSION):

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Oddly enough, when querying for a specific project/language, it gives different results on different servers when querying the same data (normal on staging vs excessively high on live). Yet the top 100 query also gives the same problem on staging.

Answer 1

Try

SELECT MAX(DATALENGTH(fText)) FROM survey_tbMultiLanguageText

The LEN function truncates trailing spaces that can still be stored in a varchar/nvarchar field. This seems the most likely answer particularly given the fact that if you change it to nvarchar(380) you get the correct values.

Answer 2

Use of TOP without an ORDER BY means you cannot guarantee which rows are returned. The result may be different across servers, or even on the same server at different times of day.

Answer 3

The LEN() function is unfaithful, as it implicitly runs an RTRIM.

Here is a comparison:

DECLARE @nvarchar nvarchar(10), @varchar varchar(10)

SET @nvarchar = N'HALLOHALLO'   -- 10 Chars long
SET @varchar = N'HALLOHALLO'    -- 10 Chars long

SELECT  DATALENGTH(@nvarchar) as NVarDataLength,    -- Returns 20 (2 Bytes per nchar)
        DATALENGTH(@varchar) as VarDataLength,      -- Returns 10 (1 Byte per nchar)
        DATALENGTH(@nvarchar)/2 as NVarCharLength,  -- Returns 10 (2 Bytes per nchar)
        DATALENGTH(@varchar) as NVarCharLength      -- Returns 10 (1 Byte per nchar)
GO

DECLARE @nvarchar nvarchar(10), @varchar varchar(10)

SET @nvarchar = N'HALLOHAL  '   -- 10 Chars long (2 whitespaces)
SET @varchar = N'HALLOHAL  '    -- 10 Chars long (2 whitespaces)

SELECT  LEN(@nvarchar) as NVarLen,              -- Returns 8 (whitespaces ignored)
        LEN(@varchar) as VarLen,                -- Returns 8 (whitespaces ignored)
        LEN(@nvarchar+N'X')-1 as NVarLenFixed,  -- Returns 10 (workaround, whitespaces not ignored)
        LEN(@varchar+N'X')-1 as VarLenFixed     -- Returns 10 (workaround, whitespaces not ignored)
GO