Excessive amount of data received from server 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.
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.