I have two SQL Servers, separate hardware boxes.
Server A: Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64) Sep 23 2016 15:49:43 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor).

Server B: Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64) Aug 15 2017 10:23:29 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

I recently deleted all the rows in one table from Server A and copied the rows from Server B using the import wizard.

When I execute these COUNT(*) queries from Server A, I get the identical result for each query, 200,703 rows.

select count(*) from dbo.thisTable

select count(*) from [Server B].[mydb].[dbo].[thisTable]

But if I exec the second COUNT(*) on Server B, it tells me I have 196,420 rows.

The copy wizard threw no errors. Any idea what happened?

closed as too broad by Erik Darling, mustaccio, Andriy M, RDFozz, hot2use Apr 10 '18 at 9:02

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Are the columns nullable in each table and do they have nulls? Are both schemas identical including indexes? Check your query plan to see how the count is actually executed; which index or column was used. Quite likely you're dealing with nulls in one of the counts where it's not counted. – SQLmojoe Apr 10 '18 at 21:50

Some of the things that could describe a different count on the same table (remote or not) are:

  • query a different table without knowing it due to default schema
  • query at a different time, where data changes have happened in the meantime
  • query with different isolation semantics (e.g. using NOLOCK or snapshot during data changes)
  • query a table with row-level security
  • query a view with filtering of any kind
  • query a synonym that actually points to a different object

Other explanations are usually that they are not the same table. Does server B have a loopback linked server to server B? Or did you execute a slightly different query on server B? Is it possible it was in a different database, or that the linked server on server A might be pointing at something else?

Not the answer you're looking for? Browse other questions tagged or ask your own question.