SQL Server Field Types, Constraints, and Index Design: Essential Best Practices

Common Field Type Choices

Use varchar/nvarchar for character data.

Use money for monetary values.

Use numeric for scientific calculations.

Use bigint for auto‑incrementing identifiers to avoid overflow.

Use datetime for date‑time values.

Avoid legacy types text, ntext, image.

Avoid xml, varchar(max), nvarchar(max).

Constraints and Indexes

Every table must have a primary key to enforce entity integrity.

Only one primary key per table; it must be non‑null and unique.

Prefer single‑column primary keys.

Foreign keys are discouraged because they increase schema change complexity and can degrade insert/update performance; data integrity should be enforced in the application layer.

New tables should prohibit NULL on all columns to simplify application logic and avoid three‑valued logic pitfalls.

NULL Handling

Allowing NULL adds complexity: every equality comparison must handle ISNULL, and three‑valued logic can produce unexpected results.

Example of unexpected behavior: SELECT * FROM NULLTEST WHERE NAME <> 'aa' This returns rows where NAME = 'bb' but skips rows where NAME IS NULL. To include NULL values, use: SELECT * FROM NULLTEST WHERE ISNULL(NAME,1) <> 'aa' Be aware that ISNULL can cause performance bottlenecks; prefer validating input at the application level.

Index Design Guidelines

Create indexes on columns frequently used in WHERE clauses.

Create indexes on columns frequently used for table joins.

Create indexes on columns frequently used in ORDER BY clauses.

Do not index very small tables where a full scan is faster.

Limit the total number of indexes per table to six.

Avoid single‑column indexes on low‑selectivity fields (e.g., gender, boolean flags).

Leverage unique constraints whenever possible.

Keep the total number of indexed columns (including INCLUDE columns) to five or fewer.

Low‑Selectivity Columns

SQL Server may ignore indexes on columns with low selectivity such as gender, 0/1, or TRUE/FALSE. Prefer indexing high‑cardinality columns like ORDERID or UID.

Unique Index Utilization

A unique index guarantees that a column contains no duplicate values. When the query optimizer finds a matching row via a unique index, it stops scanning immediately, improving performance.

Index Count Limit

Having more than six indexes on a table can increase compilation time and hinder the optimizer from choosing the best execution plan.

SQL Query Best Practices

Avoid complex calculations inside the database; perform them in the application.

Never use SELECT *; specify required columns.

Do not apply functions or calculations to indexed columns in WHERE clauses.

Avoid cursors; prefer set‑based operations.

Avoid triggers; they add hidden side effects.

Do not force index usage with hints like WITH (INDEX=...) because the optimal index may change as data evolves.

Variable, parameter, and related column types must match the column definition to prevent implicit conversions and scans.

Use parameterized queries via sp_executesql, prepared statements, or stored procedures.

Limit the number of tables joined in a single statement to five.

Restrict the number of items in an IN clause to 100 to avoid resource exhaustion.

Keep transactions short; start them only when data must be updated.

Enable SET NOCOUNT ON to suppress row‑count messages and reduce network traffic.

Use NOLOCK only when dirty reads are acceptable.

Prefer UNION ALL over UNION to avoid unnecessary sorting and deduplication.

Paginate large result sets or use TOP to limit rows returned.

Control recursive CTE depth with MAXRECURSION.

Choose between temporary tables and table variables based on scope and cardinality.

Use local variables to encourage the optimizer to generate a balanced execution plan.

Avoid the OR operator; rewrite as separate queries combined with UNION ALL when possible.

Implement robust transaction error handling (e.g., SET XACT_ABORT ON).

Reference columns with two‑part naming ( Table.Column) to prevent ambiguous column errors after schema changes.

Function Use on Indexed Columns

Applying functions to indexed columns (e.g., ABS(Col1)=1 or Col1+1>9) forces a full table scan because the optimizer cannot use the index.

Conversely, simple predicates such as Col1=3.14, Col1>100, Col1 BETWEEN 0 AND 99, Col1 LIKE 'abc%', or Col1 IN (2,3,5,7) can still leverage the index.

LIKE Query Index Issues

Patterns that start with a wildcard ( %abc or %abc%) cause an index scan, while a leading literal ( abc%) enables an index seek.

Cursor, Trigger, and Index Hint Restrictions

Cursors consume significant memory and lock resources; prefer set‑based logic.

Triggers operate invisibly to the application and can introduce unexpected side effects.

Forcing an index with WITH (INDEX=...) can become sub‑optimal as data changes and makes the application less resilient to schema modifications.

Parameter Type Alignment

Match variable/parameter types to column types to avoid costly implicit conversions that trigger large scans. For example:

VARCHAR columns → use AnsiString with explicit length.

CHAR columns → use AnsiStringFixedLength with explicit length.

NVARCHAR columns → use String with explicit length.

Parameterized Query Techniques

Common methods include sp_executesql, prepared statements, and stored procedures.

JOIN and IN Clause Limits

Restrict joins to a maximum of five tables per query and limit IN clause items to 100 to prevent excessive compilation cost and runtime errors.

Transaction Management

Begin a transaction only when data modification is required.

Implement exception handling and rollback mechanisms.

Avoid distributed transactions across databases.

SET NOCOUNT ON and NOLOCK

Enable SET NOCOUNT ON to suppress row‑count messages. Use NOLOCK only when dirty reads are acceptable, understanding the risk of reading uncommitted data.

UNION vs UNION ALL

UNION

removes duplicates and sorts results, incurring extra CPU and memory; UNION ALL preserves all rows and is more efficient.

Pagination and TOP

Limit returned rows with pagination techniques or the TOP clause to reduce I/O and network load.

Recursive Query Limits

Control recursion depth using MAXRECURSION to prevent infinite loops.

Temporary Tables and Table Variables

Choose the appropriate temporary storage based on scope, cardinality, and indexing needs.

Local Variables for Balanced Execution Plans

Using local variables hides actual values from the optimizer, leading it to generate a generic but balanced plan, which can avoid extreme over‑ or under‑optimizations.

Avoid OR Operator

OR often forces a full scan; rewrite queries using UNION/UNION ALL when possible to enable index usage.

Exception Handling in Transactions

Set SET XACT_ABORT ON to automatically roll back on errors.

Two‑Part Naming Convention

Reference columns as Table.Column to avoid ambiguous column errors after schema changes.

Architecture Design Considerations

Read/write separation for scalability.

Schema decoupling; avoid cross‑database joins.

Data lifecycle management: archive large tables and separate primary from archive storage.

Partitioning Large Tables

Partition or shard high‑write tables to improve I/O performance and enable fast partition switching for maintenance.