SQL Server Scripts for “Clustering Data”


This page for

This section contains the create, insert and T-SQL code to run the examples from Chapter 5 in a SQL Server database. It requires the helper functions RANDOM_DATE and RANDOM_INT from the where clause examples.

Index-Organized Table (Clustered Index)

The following creates a second sales table with a clustered index and a secondary index on SALE_DATE.

CREATE TABLE sales_clst (
  sale_id       NUMERIC NOT NULL,
  employee_id   NUMERIC NOT NULL,
  subsidiary_id NUMERIC NOT NULL,
  sale_date     DATE    NOT NULL,
  eur_value     NUMERIC(17,2) NOT NULL,
  junk          CHAR(200),
  CONSTRAINT sales_pk     
     PRIMARY KEY (sale_id),
  CONSTRAINT sales_emp_fk 
     FOREIGN KEY          (subsidiary_id, employee_id)
      REFERENCES employees(subsidiary_id, employee_id)
);
GO

SELECT RAND(0);
GO

WITH generator (n)
  AS (
     SELECT 1
      UNION ALL
     SELECT n + 1
       FROM generator
      WHERE N < 1800
     )
INSERT INTO sales_clst (sale_id
                      , subsidiary_id, employee_id
                      , sale_date, eur_value, junk)
SELECT row_number() OVER (ORDER BY sale_date), data.*
  FROM (
       SELECT e.subsidiary_id, e.employee_id
            , [dbo].random_date(0, 3650) sale_date
            , [dbo].random_int(1, 100000)/100 eur_value
            , 'junk' junk
         FROM employees e
            , generator gen
        WHERE employee_id % 7 = 4
          AND gen.n < employee_id / 5
       ) data
        ORDER BY sale_date
OPTION(MAXRECURSION 2000);
GO


EXEC sp_updatestats;
GO

About the Author

As an author, trainer, and coach Markus Winand specializes in helping developers cope with SQL performance issues. He also published the book SQL Performance Explained and tweets his best performance tips via @SQLPerfTips.http://winand.at/

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
126
views

We want to buy the book but I can't

Jul 18 at 21:36 Markus Winand ♦♦ 541
book
0
votes
2
answers
157
views
0
votes
0
answers
820
views

Performance very bad in Postgresql 9.3

Jul 08 at 11:54 Markus Winand ♦♦ 541
performance issue