Take care while using Function or Sub Query in Select List - SQL Server by serverku

At the time of development, we are just looking for it to be functionally work. For easy development, we are applying any code sometimes. But we never look for the performance issue at the time of the development. We look forward to the performance on the second stage. At this stage we find the issue and resolve it.

I want you to go through on one example where I have used one function and sub query in the select list to get the data in the script. Some time inner join is best instead of function and sub query, But it totally depends on the data and the script which you write.

How can we review?
We have a script to check all the stuffs here,

 
-- 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 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

-- Creating function which will be used in first script below
CREATE FUNCTION dbo.fn_GetItemTypeDesc   
(   
    @ItemType varchar(10)  
)   
RETURNS varchar(100)   
AS   
BEGIN   
 DECLARE @ItemTypeDesc varchar(100)  

 SELECT 
  @ItemTypeDesc = ItemTypeDesc 
 FROM ItemTypes 
 WHERE ItemType = @ItemType

 RETURN @ItemTypeDesc

End  
GO

Here we will review all below scripts which using function, subquery, and inner join accordingly and review the elapsed time and you for all.

 
SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 dbo.fn_GetItemTypeDesc(id.ItemType) as ItemTypeDesc,
 id.ItemDescription
FROM ItemDetails id

SET STATISTICS IO OFF
SET STATISTICS TIME OFF


SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT 
 id.ItemId,
 id.ItemName,
 id.ItemType,
 (SELECT it.ItemTypeDesc FROM ItemTypes it WHERE it.ItemType = id.ItemType) as ItemTypeDesc,
id.ItemDescription
FROM ItemDetails id

SET STATISTICS IO OFF
SET STATISTICS TIME OFF


SET STATISTICS IO ON
SET STATISTICS TIME ON

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)

SET STATISTICS IO OFF
SET STATISTICS TIME OFF

Here are screen shots of output of each respectively.

(Click on images to enlarge)

I hope you have a better idea what are best for the execution of the query. Please note, review the execution plan before applying any change for the optimization.

Impact of Nonclustered Index without Clustered Index - SQL Server by serverku

We have read and learn so many times from online sources as it is not best practice to create a non-clustered index without any clustered index created on the table. We should have clustered index on the table.

Have you practically seen that best practice? What will be the impact on query when table have a non-clustered index but not clustered index?. Without a clustered index on the table the query execution plan sometime used non-clustered index and sometimes not as depends on where condition used in the query.

I will show you here, how it behaves with and without clustered index. We have a two shot of the show and will see this behavior with normal non-clustered index and additional covering non-clustered index. Let we created a sample table and get insert some records in it.

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

CREATE TABLE ItemDetails
(
 ItemAutiId int identity(1,1),
 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

As discussed, we will go through the following, Will review execution plan for each and compare.

1. Normal non-clustered index behavior without and with clustered index on table.

-- Creating nonclustered index
CREATE NONCLUSTERED INDEX [IX_ItemType]
ON [dbo].[ItemDetails] ([ItemType])
GO

-- Running this query with Include Actual execution plan.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

 
--Now creating a clustered index on table
CREATE CLUSTERED INDEX [IX_ItemAutiId]
ON [dbo].[ItemDetails] ([ItemAutiId])
GO

-- Again running this query with Include Actual execution plan after creating clustered index.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

You can see here first case did table scan and second case did clustered index scan. Now moving other shots,

2. Covering nonclustered index behavior without and with clustered index on table.

-- Dropping existing indexes on table
DROP INDEX [IX_ItemType] ON  [ItemDetails]
DROP INDEX [IX_ItemAutiId] ON  [ItemDetails]

GO

-- Creating covering index on table
CREATE NONCLUSTERED INDEX [IX_ItemType]
ON [dbo].[ItemDetails] ([ItemType])
INCLUDE ([ItemId],[ItemName],[ItemDescription])
GO

-- Running this query with Include Actual execution plan.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

-- Creating clustered index on table
CREATE CLUSTERED INDEX [IX_ItemAutiId]
ON [dbo].[ItemDetails] ([ItemAutiId])
GO

-- Again running this query with Include Actual execution plan after creating clustred index.
SELECT 
 id.ItemAutiId,
 id.ItemId,
 id.ItemName,
 id.ItemType,
 id.ItemType,
 id.ItemDescription
FROM ItemDetails id
WHERE id.ItemType = 'PK'
GO

In this scenario first case did table scan and second case did non-clustered index seek. Do you have any more idea about it if you experienced with it?

Enhanced Mitigation Experience Toolkit 5.2 by serverku

Enhanced Mitigation Experience Toolkit 5.2

I recently came across this utility from microsoft that helps to stop malwares on systems. As currently working on Security Operation Center (SOC) , its one tool to test out and see how it really works 

the tool can be downloaded from Microsoft website below.

https://www.microsoft.com/en-us/download/details.aspx?id=46366

Will post feedback if i get time.

thanks

Contained Database example in SQL Server by serverku

SQL Server 2012 introduced a new feature named "Contained Database" which include all database settings and metadata along with database backup. It allows us to move database backups along with its users to another server, so no need any dependencies on the server. Let us look an example.

1. Enable a setting

 
-- Enable setting for contained database
Use master
GO
exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE
GO 
exec sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE
GO 

2. Enable a feature for a database

 
-- Create a new contained database

CREATE DATABASE [ContainedDB]
CONTAINMENT = PARTIAL
ON PRIMARY
 ( NAME = N'ContainedDB_Data', FILENAME = N'D:\Data\ContainedDB_Data.mdf')
LOG ON
 ( NAME = N'ContainedDB_Log', FILENAME = N'D:\Log\ContainedDB_Log.ldf')
GO

-- Or change existing database to contained
ALTER DATABASE [ContainedDB] SET CONTAINMENT = PARTIAL
GO

3. Create a new SQL User

 
-- Create a new SQL User in this contained database
USE [ContainedDB]
GO
CREATE USER ContainedDBUser WITH PASSWORD = 'ContainedDBUser'
GO

Checking : Let us login with this User created and below error occur .

 Now change a setting when login with this User.

1. Go to 'Options<<'
2. Move on tab 'Connection  Properties'
3. Set 'Connect to database' to "ContainedDB"

After applying an option, you will be succeed. So wherever you want to move contained database to another server, just move it and log in as options stated above. Enjoy Contained Database!

DataType can matter in where condition - a worst case scenario by serverku

We should use proper filter in where conditions as per data type. Like if the data type of filtered column is varchar then use filtered variable or value varchar, if filtered column is an integer then filtered variable/value should be an integer.

Worst case,

 
-- #1. 
-- If OrderId column is VARCHR, this cause table scan
-- because here OrderId column all values convert from varchar to int 
-- even if index created on it 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

Best case,

 
-- #1. 
-- If OrderId column is INT, this will work fine 
SELECT * 
FROM OrderDetails 
WHERE OrderId = 123

-- #2. 
-- If OrderId column is INT, this will also work fine 
-- because here value '123' convert from varchar to int 
SELECT * FROM OrderDetails 
WHERE OrderId = '123'

Create a table with sample records with different data types as mentioned in above queries and check the execution plan, you may see the difference for both of them.