Decimal length demo

CREATE TABLE dbo.UsersDemo (Id INT PRIMARY KEY CLUSTERED, Reputation DECIMAL(16,0));
GO
INSERT INTO dbo.UsersDemo (Id, Reputation)
  SELECT Id, Reputation
  FROM dbo.Users WITH (NOLOCK);
GO


/* Turn on actual plans and run both of these: */
SET STATISTICS IO ON;
GO
DECLARE @Reputation DECIMAL(5,0) = 2; /* "Wrong" datatype */
SELECT *
  FROM dbo.UsersDemo
  WHERE Reputation = @Reputation;
GO
DECLARE @Reputation DECIMAL(18,0) = 2; /* "Right" datatype */
SELECT *
  FROM dbo.UsersDemo
  WHERE Reputation = @Reputation;
GO
/* 
Hover your mouse over the first query's index scan, and there's
an implicit conversion, but it doesn't matter: SQL Server is just
converting the variable once. Both index scans have the same
number of estimated rows. Makes no difference whatsoever.

Now let's try with an index:
*/
CREATE INDEX IX_Reputation ON dbo.UsersDemo(Reputation);
GO

/* Then run both of these together: */
DECLARE @Reputation DECIMAL(5,0) = 2; /* "Wrong" datatype */
SELECT *
  FROM dbo.UsersDemo
  WHERE Reputation = @Reputation;
GO
DECLARE @Reputation DECIMAL(18,0) = 2; /* "Right" datatype */
SELECT *
  FROM dbo.UsersDemo
  WHERE Reputation = @Reputation;
GO
/* Same plans, same estimates, no problems. */