Basic SQL Commands and Errors

Task 1: Create Customers and Orders table with relations:

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[fk_cust_name]') AND parent_object_id = OBJECT_ID(N'[dbo].[orders]'))

ALTER TABLE [dbo].[orders] DROP CONSTRAINT [fk_cust_name]

GO

/****** Object: Table [dbo].[orders] Script Date: 10/20/2010 14:42:56 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[orders]')AND type in (N'U'))

DROP TABLE [dbo].[orders]

GO

/****** Object: Table [dbo].[customers] Script Date: 10/20/2010 14:42:46 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[customers]') AND type in (N'U'))

DROP TABLE [dbo].[customers]

GO

create table customers (cust_num integer , fname varchar(20), lname varchar(20),

constraint pk_cust_num primary key(cust_num));

GO

create table orders (order_num integer, order_qty decimal(13,6), order_amtdecimal(13,6),

order_date datetime, cust_num integer,

constraint pk_order_num primary key(order_num),

constraint fk_cust_name foreign key(cust_num) references customers(cust_num)

);

GO

Notes:

1. Name all the constraints like primary and foreign keys so that they can be deleted using their user defined names as and when required.

2. The orders table should be dropped before the customers table as the orders table refers the customers table. Else an error will be thrown as follows:

“Msg 3726, Level 16, State 1, Line 1

Could not drop object 'dbo.customers' because it is referenced by a FOREIGN KEY constraint.”

3. Clustered indexes are automatically created on the primary keys.

Task 2: Enter data into both the Customers and Orders table:

Customers table:

Declare @i Int;

Declare @j Int;

Set @i = 1;

while(@i<=20)

begin

insert into customers values(@i, 'a', 'A');

Set @i = @i + 1;

end;

--Select * from dbo.customers

--delete from dbo.customers

Orders table:

Declare @i int;

Declare @j int;

Set @i = 1;

set @j = 1;

while(@i<=60)

begin

while(@j<=20)

begin

insert into orders values(@i, 20, 100, DATEADD(Day, @i, '10/20/2010'),@j);

set @j = @j + 1;

set @i = @i + 1;

end;

set @j = 1;

end;

--Select * from dbo.orders order by cust_num

--delete from dbo.orders

Task 3: Add a column

IF EXISTS(SELECT OBJECT_NAME(OBJECT_ID)

FROM sys.objects

WHERE type_desc LIKE 'DEFAULT_CONSTRAINT' AND

OBJECT_NAME(parent_object_id) LIKE 'customers')

ALTER TABLE [dbo].[customers] DROP CONSTRAINT [DF_CUST_AGE]

GO

alter table dbo.customers add cust_age int not null constraint DF_CUST_AGE Default 10

Notes:

If a new column is added with not null constraint then either of the following 2 things should be true:

1. The table should be empty.
2. If table is not empty, then a default constraint should be applied which will update the newly added column value for all the existing rows.

Else an error will be thrown like:

“Msg 4901, Level 16, State 1, Line 1

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'cust_age' cannot be added to non-empty table 'customers' because it does not satisfy these conditions.

”

Task 4: Add clustered and non-clustered index

Customers table:

When a primary key is created on a table, a clustered index is automatically created on the table.

If we try to create another clustered index an error is thrown:

“Msg 1902, Level 16, State 3, Line 1

Cannot create more than one clustered index on table 'CUSTOMERS'. Drop the existing clustered index 'pk_cust_num' before creating another.”

The primary key index cannot be dropped explicitly:

“Msg 3723, Level 16, State 4, Line 1

An explicit DROP INDEX is not allowed on index 'customers.pk_cust_num'. It is being used for PRIMARY KEY constraint enforcement.”

To create a clustered index on some other columns, the primary key constraint should be dropped and recreated after the index is created.

ALTER TABLE [dbo].[orders] DROP CONSTRAINT [fk_cust_num]

ALTER TABLE [dbo].[customers] DROP CONSTRAINT [pk_cust_num]

create clustered index IDX_CUST_CLUSTERED on CUSTOMERS(cust_num, lname)

ALTER TABLE customers

add CONSTRAINT PK_CUST_NUM primary key(CUST_NUM)

This primary key automatically creates a unique non-clustered index as a clustered index already exists which was explicitly in previous step.

ALTER TABLE orders

add CONSTRAINT FK_CUST_NUM foreign key(CUST_NUM) references customers(CUST_NUM)

Orders Table:

create nonclustered index IDX_ORD_NONCLUSTERED on ORDERS(order_date)

include (cust_num, order_qty, order_num)

How can you tell if a covering index you created is actually being used by the Query Optimizer? You can find this out by turning on and viewing the graphical execution plan output. If you see this phrase, "Scanning a non-clustered index entirely or only a range," this means that the query optimizer was able to cover that particular query with an index:

Task 4: Rebuild Indexes

ALTER INDEX IDX_CUST_CLUSTERED ON dbo.Customers REBUILD --WITH(ONLINE = ON)

ALTER INDEX IDX_CUST_CLUSTERED ON dbo.Orders REBUILD --WITH(ONLINE = ON)

Or to rebuild all the indexes:

ALTER INDEX ALL ON dbo.Customers REBUILD --WITH(ONLINE = ON)

ALTER INDEX ALL ON dbo.Orders REBUILD --WITH(ONLINE = ON)

The indexes will not be available for use when the rebuilding is in progress unless WITH(ONLINE = ON) is used.

When to rebuild the index:

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'[dbo].[customers]'), NULL, NULL, NULL)

If the avg_fragmentation_in_percent column value is greater than 30% then it should be rebuilt.

Task 5: Update Statistics

To view on what all things the statistics is maintained:

sp_helpstats 'dbo.customers', 'ALL'

sp_helpstats 'dbo.Orders', 'ALL'

Given an individual statistic, you can use DBCC SHOW_STATISTICS to retrieve detailed information:

DBCC SHOW_STATISTICS('dbo.customers', IDX_CUST_CLUSTERED)

DBCC SHOW_STATISTICS('dbo.orders', IDX_CUST_CLUSTERED)

SQL Server takes care of updating all the statistics, but still if some query is taking long time, the update statistics with fullscan:

UPDATE STATISTICS dbo.customers WITH FULLSCAN

UPDATE STATISTICS dbo.orders WITH FULLSCAN

This scans all the rows again and updates the statistics.

To update all the tables in a database of all the servers:

EXEC sp_updatestats

Consider programming it and updating for only user tables and not system tables by looping through all the user table names and updating statistics using “UPDATE STATISTICS dbo.customers WITH FULLSCAN”.