SQL Server BasicsWhat is SQL Server database?What is RDBMS?What is Normalization?Why we use Denormalization?What_is_SQL?What is PL/SQL?Difference between SQL and PL/SQLDatabase TableOne to One RelationshipOne to Many RelationshipMany to Many RelationshipMany to One RelationshipString Data TypesNumber Data TypesDate Data TypesOther Data TypesCreate DatabaseDrop DatabaseCreating and Managing Users in SQL ServerCreate TableAlter TableDrop TableConstraints in SQL serverPrimary KeyForeign KeyUnique KeyCandidate KeyComposite KeyDifference between primary key and candidate keyPrimary key and foreign key relationshipSurrogate KeyCascading Referential Integrity ConstraintsSelf Referential Integrity ConstraintsInsert into statementInsert multiple recordsUpdate statementDelete statementTruncate statementDifference between Delete and TruncateAlias in SQL ServerSelect statementSelect DistinctSelect TopSelect IntoNull Functions(ISNULL(),NULLIF(),COALESCE())Sub QueryIdentity ColumnSequence objectDifference between sequence and identity columnSQL Server ClausesWHERE ClauseOrder By ClauseTop N ClauseGroup By ClauseHaving ClauseDifference between Where and HavingSQL Server OperatorsArithmetic OperatorsComparison OperatorsLogical OperatorsBitwise OperatorsAny OperatorsAll OperatorsUnion OperatorsUnion All OperatorsDifference between Union and Union AllIntersect OperatorExcept OperatorDifference between Except and IntersectJoinsInner JoinLeft JoinRight JoinFull JoinSelf JoinCross JoinViewsWhat are views?Create views using SSMSIndexed ViewsComplex ViewsCheck Option in ViewCheck Encryption in ViewSchema Binding Option in ViewRead-only ViewsUpdatable ViewsAdvantages and disadvantages of viewsCreate multiple views on one tableCan we implement index on views?Can we Perform Insert, update, delete operation on views?Stored Procedure and FunctionsWhat are Stored Procedures?Why we use stored procedures?Passing parameters to Stored procedureUser-Defined FunctionsDifference between UDF and Stored procedurePre-Defined Functions@@Indentity and Scope_IndentityNULLIF, ISNULL and COALESCE
Previous
Next

Understanding Number Data Types in SQL Server: A Comprehensive Guide with Examples

Why Are Number Data Types Important?

Number data types define how numeric data is stored, processed, and retrieved in SQL Server. Choosing the right data type ensures:

  • Efficient Storage: Optimizes memory and disk space usage.
  • Data Accuracy: Ensures precision for calculations and comparisons.
  • Performance: Improves query performance by reducing unnecessary overhead.

Commonly Used Number Data Types in SQL Server

Here are the most commonly used number data types in SQL Server:

1. INT (Integer)

  • Description: Used to store whole numbers (positive or negative).
  • Range: -2,147,483,648 to 2,147,483,647.
  • Storage: 4 bytes.
  • Example Use Case: Storing employee IDs, product quantities, or order counts.

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50)
);

2. BIGINT

  • Description: Used to store very large whole numbers.
  • Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
  • Storage: 8 bytes.
  • Example Use Case: Storing unique identifiers for large datasets, such as transaction IDs.

CREATE TABLE Transactions (
TransactionID BIGINT PRIMARY KEY,
Amount DECIMAL(10, 2)
);

3. SMALLINT

  • Description: Used to store small whole numbers.
  • Range: -32,768 to 32,767.
  • Storage: 2 bytes.
  • Example Use Case: Storing small quantities, such as the number of items in stock.

CREATE TABLE Inventory (
ProductID INT PRIMARY KEY,
StockQuantity SMALLINT
);

4. TINYINT

  • Description: Used to store very small whole numbers.
  • Range: 0 to 255.
  • Storage: 1 byte.
  • Example Use Case: Storing age, ratings, or status codes.

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Age TINYINT
);

5. DECIMAL(p, s) or NUMERIC(p, s)

  • Description: Used to store precise numeric values with fixed decimal places.
  • Parameters:
    • p: Total number of digits (precision).
    • s: Number of digits after the decimal point (scale).
  • Example Use Case: Storing financial data, such as prices or salaries.

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Price DECIMAL(10, 2)
);

6. FLOAT

  • Description: Used to store approximate numeric values with floating-point precision.
  • Range: Depends on the size specified.
  • Example Use Case: Storing scientific or engineering data, such as measurements.

CREATE TABLE Measurements (
MeasurementID INT PRIMARY KEY,
Value FLOAT
);

7. REAL

  • Description: Used to store single-precision floating-point numbers.
  • Range: Smaller range and precision compared to FLOAT.
  • Example Use Case: Storing less precise numeric data, such as temperature readings.

CREATE TABLE WeatherData (
RecordID INT PRIMARY KEY,
Temperature REAL
);

8. MONEY

  • Description: Used to store monetary values with up to four decimal places.
  • Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
  • Example Use Case: Storing financial transactions or account balances.

CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
Balance MONEY
);

9. SMALLMONEY

  • Description: Used to store smaller monetary values with up to four decimal places.
  • Range: -214,748.3648 to 214,748.3647.
  • Example Use Case: Storing small financial transactions or discounts.

CREATE TABLE Discounts (
DiscountID INT PRIMARY KEY,
Amount SMALLMONEY
);

Choosing the Right Number Data Type

When selecting a number data type, consider the following factors:

  • Range: Ensure the data type can handle the minimum and maximum values you need.
  • Precision: For decimal values, choose a data type that supports the required number of decimal places.
  • Storage: Use the smallest data type that meets your requirements to save memory and improve performance.
  • Use Case: Match the data type to the nature of the data (e.g., whole numbers, monetary values, or scientific data).

Practical Examples

Example 1: Storing Employee Data


CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
Age TINYINT,
Salary DECIMAL(10, 2)
);
  • EmployeeID: Uses INT for unique identification.
  • Age: Uses TINYINT because age values are small.
  • Salary: Uses DECIMAL(10, 2) for precise monetary values.

Example 2: Storing Product Data


CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2),
StockQuantity SMALLINT
);
  • Price: Uses DECIMAL(10, 2) for accurate pricing.
  • StockQuantity: Uses SMALLINT for small inventory counts.

Conclusion

Understanding and choosing the right number data types in SQL Server is essential for efficient database design and performance. Whether you’re storing whole numbers, precise decimal values, or monetary data, SQL Server provides a variety of data types to meet your needs.

By following the guidelines and examples in this article, you can ensure that your database is optimized for storage, accuracy, and performance. Happy querying!

Previous
Next