Partitioning a table can greatly enhance performance and manageability, particularly with large datasets. In this article, we will walk you through the process of creating a partitioned table in SQL Server using the AdventureWorks sample database. This practical example will illustrate how to set up a partitioned table based on order dates.

1. Introduction to Table Partitioning

Partitioning involves dividing a table into smaller, more manageable pieces, yet still presenting it as a single table to users. This is particularly useful for tables with a large volume of data, as it can improve query performance and make data management more efficient.

2. Creating the Partition Function

The partition function determines how data is distributed across partitions. In our example, we will partition data based on DATETIME values, creating ranges for different years.

CREATE PARTITION FUNCTION pf_orders_date_range (DATETIME)
AS RANGE LEFT FOR VALUES ('2011-01-01', '2012-01-01', '2013-01-01');

• pf_orders_date_range is the name of the partition function.
• RANGE LEFT indicates that the range values specified are inclusive on the left and exclusive on the right.
• The function will create partitions for dates up to but not including January 1 of the subsequent years.

3. Adding Filegroups and Files

Filegroups are used to organize data files and optimize storage. We will create three filegroups, each corresponding to a year, and then add data files to these filegroups.

Adding Filegroups

-- Add Filegroup for 2011
ALTER DATABASE advworks
ADD FILEGROUP fg_orders_201101;

-- Add Filegroup for 2012
ALTER DATABASE advworks
ADD FILEGROUP fg_orders_201201;

-- Add Filegroup for 2013
ALTER DATABASE advworks
ADD FILEGROUP fg_orders_201301;

Adding Files

-- Add File for 2011
ALTER DATABASE advworks
ADD FILE 
(
    NAME = 'Partition1_File',
    FILENAME = 'C:\tmp\dummy\fg_orders_201101.ndf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)
TO FILEGROUP fg_orders_201101;

-- Add File for 2012
ALTER DATABASE advworks
ADD FILE 
(
    NAME = 'Partition2_File',
    FILENAME = 'C:\tmp\dummy\fg_orders_201201.ndf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)
TO FILEGROUP fg_orders_201201;

-- Add File for 2013
ALTER DATABASE advworks
ADD FILE 
(
    NAME = 'Partition3_File',
    FILENAME = 'C:\tmp\dummy\fg_orders_201301.ndf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)
TO FILEGROUP fg_orders_201301;

• Each FILE command creates a new file in the specified filegroup, with growth settings and initial size defined.

4. Creating the Partition Scheme

The partition scheme maps partitions to filegroups. This scheme will use the previously created partition function and filegroups.

CREATE PARTITION SCHEME ps_orders_date_range  
AS PARTITION pf_orders_date_range  
TO (fg_orders_201101, fg_orders_201201, fg_orders_201301, [PRIMARY]);

• ps_orders_date_range is the name of the partition scheme.
• It maps the ranges defined in the partition function to the filegroups.

5. Creating the Partitioned Table

Finally, create the table and specify that it should use the partition scheme for data distribution.

CREATE TABLE [Sales].[SalesOrderHeaderPartitioned](
    [SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [RevisionNumber] [tinyint] NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [DueDate] [datetime] NOT NULL,
    [ShipDate] [datetime] NULL,
    [Status] [tinyint] NOT NULL,
    [OnlineOrderFlag] [dbo].[Flag] NOT NULL,
    [SalesOrderNumber]  AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')),
    [PurchaseOrderNumber] [dbo].[OrderNumber] NULL,
    [AccountNumber] [dbo].[AccountNumber] NULL,
    [CustomerID] [int] NOT NULL,
    [SalesPersonID] [int] NULL,
    [TerritoryID] [int] NULL,
    [BillToAddressID] [int] NOT NULL,
    [ShipToAddressID] [int] NOT NULL,
    [ShipMethodID] [int] NOT NULL,
    [CreditCardID] [int] NULL,
    [CreditCardApprovalCode] [varchar](15) NULL,
    [CurrencyRateID] [int] NULL,
    [SubTotal] [money] NOT NULL,
    [TaxAmt] [money] NOT NULL,
    [Freight] [money] NOT NULL,
    [TotalDue]  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),
    [Comment] [nvarchar](128) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_SalesOrderHeaderPartitioned_SalesOrderID] PRIMARY KEY CLUSTERED (
        [SalesOrderID] ASC,
        [OrderDate] ASC -- Include OrderDate in the primary key
    ) 
) ON ps_orders_date_range ([OrderDate]);

• The ON ps_orders_date_range ([OrderDate]) clause specifies that the table uses the partition scheme, distributing data based on the OrderDate column.

6. Verifying the Partition Setup

To ensure that the partitions are correctly set up, you can run the following query:

SELECT 
    p.partition_number,
    f.name AS file_group,
    p.rows
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_ID = OBJECT_ID('Sales.SalesOrderHeaderPartitioned')
ORDER BY p.partition_number;

• This query provides information about partition numbers, associated filegroups, and the number of rows in each partition.

Conclusion

Partitioning a table in SQL Server can significantly improve performance and ease data management. By following these steps—creating a partition function, adding filegroups and files, setting up a partition scheme, creating the partitioned table, and verifying the setup—you can efficiently manage large datasets and optimize query performance.

Query optimization is a critical aspect of database performance, especially for large datasets or complex queries. By optimizing your SQL queries, you can significantly improve the speed and efficiency of your applications.

Index Creation

• Create Indexes on Frequently Searched Columns: Indexes are data structures that speed up data retrieval. Create indexes on columns that are frequently used in WHERE, JOIN, GROUP BY, or ORDER BY clauses.
• Avoid Over-Indexing: Too many indexes can slow down data modification operations. Carefully consider the trade-off between read and write performance.

Example:

If you frequently query a table based on the order_date column, create an index on it:

CREATE INDEX idx_orders_order_date ON orders (order_date);

Query Rewriting

• Use JOINs Instead of Subqueries: JOINs are often more efficient than subqueries, especially for large datasets.
• Avoid Using Functions in WHERE Clauses: Functions applied in WHERE clauses can prevent the optimizer from using indexes. If possible, rewrite the query to avoid functions.

Example:

Replace a subquery with a JOIN:

-- Subquery
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- JOIN
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Parameterization

• Use Parameterized Queries: Parameterized queries prevent SQL injection attacks and can improve performance by allowing the query optimizer to reuse execution plans.

Example:

Use parameterized queries to prevent SQL injection and improve performance:

DECLARE @customerId INT = 123;

SELECT * FROM orders WHERE customer_id = @customerId;

Data Denormalization

• Consider Denormalization: In some cases, denormalizing data can improve query performance by reducing the number of joins required. However, this can lead to data redundancy and increased maintenance overhead.

Example:

If you frequently need to join two tables on a common column, consider denormalizing one of the tables to reduce the number of joins:

-- Normalized tables
CREATE TABLE customers (customer_id INT, name VARCHAR(50));
CREATE TABLE orders (order_id INT, customer_id INT, product_id INT);

-- Denormalized table
CREATE TABLE orders_denormalized (order_id INT, customer_id INT, product_id INT, customer_name VARCHAR(50));

Query Hints

• Use Query Hints Carefully: Query hints provide the optimizer with specific instructions on how to execute a query. Use them cautiously, as they can override the optimizer's intelligent decisions.

Example:

Use a NOLOCK hint to force a specific join type:

SELECT *
FROM person.Person p WITH (NOLOCK)
JOIN person.BusinessEntity b WITH (NOLOCK) 
ON p.BusinessEntityID = b.BusinessEntityID

Partitioning

• Partitioning: Partitioning is a technique that divides a large table into smaller, more manageable segments called partitions. This can significantly improve query performance, especially for analytical workloads or data warehousing scenarios.

Example:

Partition a table based on a date column:

CREATE PARTITION FUNCTION pf_orders_date_range (DATETIME)
AS RANGE LEFT FOR VALUES ('2023-01-01', '2023-02-01', '2023-03-01', ...);

CREATE PARTITION SCHEME ps_orders_date_range
AS PARTITION pf_orders_date_range
TO (fg_orders_202301, fg_orders_202302, ...);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATETIME,
    ...
) ON ps_orders_date_range (order_date);

Understanding the Fundamentals

When working with Microsoft SQL Server databases, efficient data retrieval is paramount. Indexes play a crucial role in accelerating these operations. Two primary methods for accessing data through indexes are index scans and index seeks. A third operation, key lookup, is often performed in conjunction with these two.

Index Scans

• Process: Scans the entire index from beginning to end.
• When used: Typically used when a large portion of the index needs to be examined, or when the query doesn't have a specific condition that can be used to narrow down the search.
• Performance: Less efficient than index seeks, especially for large datasets, as it reads unnecessary data.

Index Seeks

• Process: Directly navigates to the specific location in the index where the desired data is stored, using the index's structure.
• When used: Ideal for queries with specific conditions, such as equality comparisons or range searches.
• Performance: Significantly more efficient than index scans, as it avoids reading unnecessary data.

Key Lookups

• Process: Retrieves the complete row data from the base table after an index scan or index seek has identified the matching rows.
• When used: Typically used when the index doesn't contain all the columns needed for the query result.
• Performance: Can add overhead to query execution, especially if the clustered index is not on the same column as the non-clustered index used for the scan or seek.

The Interplay Between Operations Often, a query in SQL Server involves a combination of these operations. For instance:

1. Index Seek: A query with a specific condition, like WHERE LastName = 'Smith', will typically use an index seek to efficiently locate the relevant rows.
2. Key Lookup: If the query requires additional columns not included in the index (e.g., FirstName), a key lookup is performed to retrieve the complete row data.

Optimizing Performance in SQL Server To maximize query performance in SQL Server:

• Design effective indexes: Ensure indexes are created on frequently queried columns and are aligned with the most common query patterns. Use tools like the CREATE INDEX statement to create indexes.
• Consider clustered indexes: Clustered indexes can reduce the need for key lookups, especially when the index contains all the columns needed for the query. The clustered index determines the physical storage order of the data.
• Analyze query plans: Use tools like SQL Server Management Studio's execution plans or the EXPLAIN statement to understand how the database is executing queries and identify potential optimizations.
• Leverage query hints: In some cases, you can use query hints to provide the optimizer with additional information or override its default choices.

Conclusion By understanding the nuances of index scans, index seeks, and key lookups, SQL Server administrators and developers can significantly improve query performance and ensure efficient data retrieval. By carefully designing indexes and optimizing query execution plans, it's possible to achieve substantial performance gains in SQL Server databases.

In SQL Server, indexes are crucial for improving query performance by providing a structured way to access data. There are two primary types: clustered and non-clustered.

Clustered Index

• Defines the physical order of the data: A clustered index determines how the rows are physically arranged on disk.
• Can only have one per table: A table can only have one clustered index.
• Impacts data retrieval: Queries that use the clustered index columns are generally faster as they directly access the data.
• Often based on primary key: The primary key is often defined as a clustered index, ensuring data integrity and efficient retrieval.

Non-Clustered Index

• Points to the physical location of data: A non-clustered index contains a list of pointers to the actual data rows.
• Can have multiple per table: A table can have multiple non-clustered indexes.
• Improves query performance: Non-clustered indexes can significantly improve query performance, especially for queries that frequently filter on or join data based on the indexed columns.

Key Differences

When to Use Which

• Clustered index: Use for columns that are frequently used in primary key operations or for data retrieval based on the clustered index columns.
• Non-clustered index: Use for columns that are frequently used in filtering or joining operations.

Example: If you have a table Orders with columns OrderID, CustomerID, OrderDate, and TotalAmount, you might:

• Create a clustered index on OrderID to ensure data integrity and efficient retrieval of orders by ID.
• Create non-clustered indexes on CustomerID and OrderDate to improve performance for queries that filter based on these columns.

By understanding the differences between clustered and non-clustered indexes, you can optimize your SQL Server database design for efficient data retrieval and query performance.

Introduction

In Microsoft SQL Server, the NOLOCK hint is a powerful tool for improving query performance in high-concurrency environments. However, it's essential to use it judiciously as it can introduce data inconsistencies if not employed correctly.

What is NOLOCK?

The NOLOCK hint instructs SQL Server to bypass locking mechanisms when accessing data. This means your query won't wait for other transactions to release locks on the data, potentially leading to significant performance gains.

When to Use NOLOCK

• Data Warehousing: When data consistency is less critical than performance, NOLOCK can be used to extract data rapidly for analysis.
• Reporting: For non-critical reports that can tolerate some level of data inconsistency.
• Temporary Data: When working with temporary data that doesn't require strict consistency.

Key Considerations

• Dirty Reads: Using NOLOCK can lead to "dirty reads," where a transaction reads data that has not yet been committed by another transaction. This can result in inconsistent results or errors.
• Phantom Reads: Another potential issue with NOLOCK is "phantom reads." This occurs when a transaction reads a set of rows, then another transaction inserts or deletes rows that meet the same criteria. When the first transaction re-reads the data, it may see different results than the initial read.
• Performance Impact: While NOLOCK can improve performance, it's important to evaluate the trade-offs carefully. In some cases, using READ_UNCOMMITTED or READ_PAST might be more appropriate.
• Alternatives: Consider alternative locking mechanisms like READ_UNCOMMITTED, READ_COMMITTED, or REPEATABLE_READ based on your specific requirements and data consistency needs.

Example

SELECT CustomerID, OrderID, OrderDate
FROM Orders with (NOLOCK)

This query will retrieve data from the Orders table without waiting for other transactions to release locks, potentially improving performance but also increasing the risk of dirty reads and phantom reads.

Best Practices

• Use with Caution: Only use NOLOCK when absolutely necessary and understand the potential risks.
• Test Thoroughly: Test your application with NOLOCK to ensure it produces accurate results and handles potential inconsistencies gracefully.
• Consider Alternatives: If data consistency is critical, explore other locking mechanisms that provide stronger guarantees.

Alternatives to NOLOCK

Here are some alternative locking mechanisms that you might consider depending on your specific requirements:

• READ UNCOMMITTED: This isolation level allows a transaction to read uncommitted data from other transactions. It provides the highest level of concurrency but also the highest risk of dirty reads and phantom reads.

  SELECT CustomerID, OrderID, OrderDate
  FROM Orders WITH (READUNCOMMITTED);

• READ COMMITTED: This isolation level ensures that a transaction reads data that has been committed by other transactions. It prevents dirty reads but can still introduce phantom reads.

  SELECT CustomerID, OrderID, OrderDate
  FROM Orders WITH (READCOMMITTED);

• REPEATABLE READ: This isolation level guarantees that a transaction will not see any changes made by other transactions after it has started. It prevents dirty reads and phantom reads but can introduce deadlocks. Moreover, no other transactions can modify data that has been read by the current transaction until the current transaction completes.

  SELECT CustomerID, OrderID, OrderDate
  FROM Orders WITH (REPEATABLEREAD);

Choosing the Right Alternative

The choice of which isolation level to use depends on your specific requirements for data consistency and performance. If data consistency is critical, you should choose a higher isolation level. If performance is more important, you can consider a lower isolation level, but be aware of the potential risks of inconsistencies.

Conclusion

The NOLOCK hint can be a valuable tool in SQL Server for improving query performance. However, it's crucial to use it judiciously and understand the potential risks associated with dirty reads and phantom reads. By carefully evaluating your specific needs and following best practices, you can effectively leverage NOLOCK to optimize your SQL Server applications. Additionally, exploring alternative locking mechanisms can help you achieve the right balance between performance and data consistency for your specific use cases.

The with clause is also known as common table expression (CTE) and subquery refactory. It is a temporary named result set.

SQL:1999 added the with clause to define "statement scoped views". They are not stored in the database scheme: instead, they are only valid in the query they belong to. This makes it possible to improve the structure of a statement without polluting the global namespace.

Syntax

with <QUERY_NAME_1> (<COLUMN_1>[, <COLUMN_2>][, <COLUMN_N>]) as
     (<INNER_SELECT_STATEMENT>)
[,<QUERY_NAME_2> (<COLUMN_1>[, <COLUMN_2>][, <COLUMN_N>]) as
     (<INNER_SELECT_STATEMENT>)]
<SELECT_STATEMENT>

Non-Recursive Example

with sales_tbl as (
select sales.*
	from (VALUES
		('Spiderman',1,19750),
		('Batman',1,19746),
		('Superman',1,9227),
		('Iron Man',1,9227),
		('Wonder Woman',2,16243),
		('Kikkoman',2,17233),
		('Cat Woman',2,8308),
		('Ant Man',3,19427),
		('Aquaman',3,16369),
		('Iceman',3,9309)
	) sales (emp_name,dealer_id,sales)
)
select ROW_NUMBER() over (order by dealer_id) as rownumber, *
from sales_tbl

Recursive Example

WITH [counter] AS (

   SELECT 1 AS n  -- Executes first and only once.

   UNION ALL      -- UNION ALL must be used.

   SELECT n + 1   -- The portion that will be executed 
   FROM [counter] -- repeatedly until there's no row 
                  -- to return.

   WHERE  n < 50  -- Ensures that the query stops.
)
SELECT n FROM [counter]