Monday, July 27, 2015

Missing index suggesion by Execution Plan - SQL Server

The index is a most important factor in the optimization and performance. Whenever we work for the query optimization, we are mostly looking for indexes, then check any other factors. Also scheduling the job for the maintenance of the indexes on periodical basis. From Index usage and statistics report, we can have an idea for the index optimization. But SQL Server query execution plan also recommended and give the suggestion with index hint. We will look here the demonstration for the same with execution plan.
--- Creating tables
IF (OBJECT_ID('ItemTypes','U') > 0 )
DROP TABLE ItemTypes
GO

IF (OBJECT_ID('ItemDetails','U') > 0 )
DROP TABLE ItemDetails
GO

CREATE TABLE ItemTypes
(
 ItemType varchar(100),
 ItemTypeDesc varchar(100)
)
GO

CREATE TABLE ItemDetails
(
 ItemId int not null,
 ItemName varchar(100),
 ItemType varchar(10),
 ItemDescription varchar(200)
)
GO


-- Inserting sample records
INSERT INTO ItemDetails
SELECT 
 a.id,
 a.name,
 a.xtype,
 'ItemDesc' 
FROM sys.sysobjects a
CROSS JOIN sys.sysobjects b
GO

INSERT INTO ItemTypes
SELECT distinct 
 type,
 type_desc 
FROM  sys.objects 
GO

-- Review the execution plan.
SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 it.ItemTypeDesc as ItemTypeDesc,
 id.ItemDescription
FROM ItemDetails id
INNER JOIN ItemTypes it
ON (it.ItemType = id.ItemType)
GO

You can see the index hint with the execution plan, and following is a script for same.
/*
Missing Index Details from SQLQuery.sql - ReportServer (dba (56))
The Query Processor estimates that implementing the following index could improve the query cost by 89.0255%.
*/

/*
USE [ReportServer]
GO
CREATE NONCLUSTERED INDEX []
ON [dbo].[ItemDetails] ([ItemType])
INCLUDE ([ItemId],[ItemName],[ItemDescription])
GO
*/
Apply it and enjoy!

Monday, July 20, 2015

Insert default values for all columns in table - SQL Server

I was asked by some of the job colleagues as how can insert all the DEFAULT values in table when table have all the columns defined with default property. If the table has at least one column without default and identity column, then we can do insert default with rest columns easily. How can with insert default values for all columns in the table? For that we will see a small demo where we will create one table with all columns defined by default and will see the insertion of default values for the same.
USE DEMO
GO

-- Creating table
IF (OBJECT_ID('DefaultTable','U') > 0)
DROP TABLE DefaultTable
GO

CREATE TABLE DefaultTable
(
 TransactionId INT IDENTITY(1,1) NOT NULL,
 TransactionName VARCHAR(100) NOT NULL DEFAULT 'Test Transaction',
 TransactionDate DATETIME NOT NULL DEFAULT GETDATE(),
 TransactionType SMALLINT NOT NULL DEFAULT 0
)
GO

-- Insert Default values for all columns

INSERT DefaultTable
DEFAULT VALUES
GO

-- Reviewing records in table
SELECT 
* 
FROM DefaultTable
GO

Let us try more with some more inserts and see again,
INSERT DefaultTable
DEFAULT VALUES
GO 100

-- Reviewing records in table
SELECT 
* 
FROM DefaultTable
GO

You might experience this type of need.
X