T-SQL: Find if “xp_cmdshell” is configured on your SQL Server instance

Hi Friends,

How are you doing?

Today, we will see how to check if the “xp_cmdshell” is already configured on your SQL Server instance or not. We can check the other configurations as well using the same T-SQL.

What is “xp_cmdshell”?

“xp_cmdshell” allow us to run windows commands by using SQL Server, but we need to enable this!

We can check if this configuration is already configured on this instance of SQL Server or not by referring to the system view “sys.configurations“. You can explore this by following this link.

T-SQL Script:

-- CHECK FOR "xp_cmdshell"
SELECT name, CONVERT(INT, ISNULL(value, value_in_use)) AS IsConfigured 
FROM sys.configurations 
WHERE name = 'xp_cmdshell';

-- CHECK FOR "show advanced options"
SELECT name, CONVERT(INT, ISNULL(value, value_in_use)) AS IsConfigured 
FROM sys.configurations 
WHERE name = 'show advanced options';

-- CHECK FOR "Agent XPs"
SELECT name, CONVERT(INT, ISNULL(value, value_in_use)) AS IsConfigured
FROM sys.configurations 
WHERE name = 'Agent XPs';

Snapshot:

Please note that if you found value in “sys.configurations” is not set to 1 then you may need to manually enable the “xp_cmdshell” function.

If you check the defined structure of the sys.configurations system view, you will get the following:

That’s it! Hope, you may like it…

Happy Coding 🙂

 

T-SQL: Find the location of data and log files of the databases within a SQL Server instance.

Hi Friends,

Hope, you are doing fine!

Today, please find below the T-SQL script tip to find the location of the data & log files available within a SQL Server instance. The script is as below:

T-SQL:

SELECT 
        name AS NameOfFile, 
        physical_name AS FileLocation
FROM 
        sys.master_files

Snapshot:

Happy Coding 🙂

 

T-SQL to find I/O usage stats by databases within the SQL Server instance

Hi Friends,

Hope, you are doing fine!

Today, we will see how to extract the I/O statistics for data and log files within a SQL Server instance, with the help of dynamic management view (DMV) named as (“sys.dm_io_virtual_file_stats“).

T-SQL Script:

-- T-SQL to find I/O usage stats by databases within 
-- the SQL Server instance (Database level IO usage stats)
SELECT 
 DB_NAME(database_id) AS [Database Name],
 -- IO READS
 [IO_Reads_In_Bytes] = CAST(SUM(num_of_bytes_read) AS NUMERIC(18, 2)),
 [IO_Reads_In_KB] = CAST(SUM(num_of_bytes_read) / 1024 AS NUMERIC(18, 2)),
 [IO_Reads_In_MB] = CAST(SUM(num_of_bytes_read) / (1024 * 1024) 
AS NUMERIC(18, 2)),
 [IO_Reads_In_GB] = CAST(SUM(num_of_bytes_read) / (1024 * 1024 * 1024) 
AS NUMERIC(18, 2)),
 -- IO WRITES
 [IO_Writes_In_Bytes] = CAST(SUM(num_of_bytes_written) AS NUMERIC(18, 2)),
 [IO_Writes_In_KB] = CAST(SUM(num_of_bytes_written) / 1024 
AS NUMERIC(18, 2)),
 [IO_Writes_In_MB] = CAST(SUM(num_of_bytes_written) / (1024 * 1024)
 AS NUMERIC(18, 2)),
 [IO_Writes_In_GB] = CAST(SUM(num_of_bytes_written) / (1024 * 1024 * 1024)
 AS NUMERIC(18, 2)),
 -- IO READS + IO WRITES
 [IO_In_Bytes] = CAST(SUM(num_of_bytes_read + num_of_bytes_written) 
AS NUMERIC(18, 2)),
 [IO_In_KB] = CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1024
 AS NUMERIC(18, 2)),
 [IO_In_MB] = CAST(SUM(num_of_bytes_read + num_of_bytes_written) 
/ (1024 * 1024) AS NUMERIC(18, 2)),
 [IO_In_GB] = CAST(SUM(num_of_bytes_read + num_of_bytes_written) 
/ (1024 * 1024 * 1024) AS NUMERIC(18, 2))
FROM 
 sys.dm_io_virtual_file_stats(NULL, NULL) AS IO_Stats_DMV
GROUP BY database_id
ORDER BY database_id

Snapshot:

Hope, this T-SQL script will be useful when you need to see which database is the busiest database within a SQL Server instance.

Happy Coding 🙂

T-SQL inbuilt function to retrieve all the supported collations within SQL Server

Hi Friends,

Hope, you are doing fine!

Today, we will see the demo of T-SQL in-built TVF (Table Valued Function) function “fn_helpcollations()” which help us to retrieve all the supported collations within SQL Server.

Please refer the below link for your reference:

https://msdn.microsoft.com/en-us/library/ms187963.aspx

Syntax:

fn_helpcollations()

T-SQL:

SELECT * FROM fn_helpcollations()

Snapshot:

That’s it! Also you can apply WHERE clause to filter your result set as well! Please share and press like, if you enjoyed this post! 🙂

Happy Coding 🙂

T-SQL Fun: Guess your age based on two parameters

Hi Friends,

Hope, you are doing fine! Today is the last day of 2016 and I hope that this year brought a lot of happiness and good lessons to all of our life. I wish you all a very happy and prosperous new year 2017 in advance!

Today, I will share a T-SQL for fun to demo a trick to find your age, based on two parameters. I know that this could be also done directly in T-SQL using many date’s inbuilt functions but let’s solve this by some other alternate/trick.

Let’s start now:

Parameters to be given by the end user, are as below:

1. Number (between 1 to 10)
2. DOB (in mm/dd/yyyy format)

Pseudocode‘s steps:

1. Think a number between 1 to 10.
2. Multiply the number 2 with the number in Step-1
3. Add 5 to the result, we get in Step-2.
4. Multiply 50 to the result, we get in Step-3.
5. If you already had your Birthday as compare to current date, Add 1766 to the result, we get in Step-4, else add 1765.
6. Subtract your Birth Year from the result, we get in Step-5
7. The first two digit will be your Number (1st Parameter) and the rest will be your Age.

Doesn’t it sound interesting?

Let’s try this with below the T-SQL script:

T-SQL Script:

--PUZZLE: T-SQL FUN TO GUESS YOUR AGE BASED ON TWO INPUTS....
DECLARE 
 @Num INT = 10, -- NUMBER SHOULD BE BETWEEN 1 TO 10
 @DOB DATETIME = '10/18/1912' -- YOUR DATE OF BIRTH (mm/dd/yyyy FORMAT)
BEGIN
 DECLARE @Result INT = 0, @NumToAddIfBDPast INT = 1766, 
@NumToAddIfBDDue INT = 1765,
 @NumLength INT = (CASE WHEN @Num BETWEEN 1 AND 9 THEN 1 ELSE 2 END), 
 @CurrentDate DATETIME = DATEADD(ss, -1, CAST((CONVERT(VARCHAR, 
DATEADD(d, 1, GETDATE()), 101) + ' 00:00:00') AS DATETIME))
 SET @DOB = CAST((@DOB + ' 00:00:00') AS DATETIME)
 SET @Result = @Num * 2 -- MULTIPLY THE GIVEN NUMBER BY 2 
-- AND ASSIGN IT TO THE RESULT
 SET @Result = @Result + 5 -- ADD 5 TO THE RESULT
 SET @Result = @Result * 50 -- MULTIPLY THE RESULT WITH 50 
-- AND ASSIGN IT BACK TO THE RESULT
 SET @Result = @Result + 
 (CASE WHEN (MONTH(@DOB) <= MONTH(@CurrentDate) 
AND DAY(@DOB) < DAY(@CurrentDate)) 
 THEN @NumToAddIfBDPast ELSE @NumToAddIfBDDue END)
 -- SUBTRACT THE YEAR OF BIRTH FROM THE RESULT
 SET @Result = @Result - YEAR(@DOB)
 SELECT @Result AS FinalResult, 
 CASE WHEN (CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) AS INT) 
 > @Num) 
 THEN (CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) AS INT) 
 - (CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) AS INT) - @Num))
 ELSE
 CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) AS INT)
 END AS YourNumber,
 CAST((CASE WHEN (CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) 
   AS INT) > @Num) 
 THEN CAST((CAST(SUBSTRING(CAST(@Result AS VARCHAR), 1, @NumLength) AS INT) 
  - @Num) AS VARCHAR)
 + SUBSTRING(CAST(@Result AS VARCHAR), (LEN(CAST(@Result AS VARCHAR)) - 1), 
     LEN(CAST(@Result AS VARCHAR)))
 ELSE
 SUBSTRING(CAST(@Result AS VARCHAR), (LEN(CAST(@Result AS VARCHAR)) - 1), 
    LEN(CAST(@Result AS VARCHAR)))
 END) AS INT) AS YourAge
END
GO

Snapshot:

Hope, you will enjoy this T-SQL trick and may give it a try, at least once!

Happy Coding  🙂

Find all available Dynamic Management Views (DMVs) and Dynamic Management Inline Functions (DMIFs) using T-SQL in SQL Server

Hi Friends,

Hope, you are doing fine!

Please find below the T-SQL script to find all available Dynamic Management Views (DMVs) and Dynamic Management Inline Functions (DMIFs) using T-SQL in SQL Server Instance.

Actually, this script is the answer of one of the random question asked by my friend that “How many Dynamic Management Views (DMVs) available in SQL Server ?”

T-SQL Script:

-- 1st Way (By Using sys.system_objects system table)
SELECT *
FROM sys.system_objects 
WHERE name LIKE 'dm_%' 
ORDER BY type, name

-- 2nd Way (By Using sys.sysobjects system table)
SELECT * 
FROM sys.sysobjects 
WHERE name LIKE 'dm_%' 
ORDER BY type, name

Snapshot:

That’s it, my friend! Hope you can explore this further as per your need!

Happy Coding 🙂

T-SQL to read JSON formatted data in MS SQL SERVER 2016

Hi Friends,

How are doing today?

Well, today I will demonstrate you “how to convert json data / document back to sql server tabular formatted data“. You may have already gone through my last post “JSON SUPPORT in MS SQL SERVER 2016“. If not, then you can check this link first to get some idea first!

To read a JSON formatted data / document, we will be using OPENJSON() function.  The syntax of the OPENJSON() function is as below:

Syntax:

OPENJSON( jsonExpression [ , path ] ) 
[ 
 WITH ( 
 colName type [ column_path ] [ AS JSON ] 
 [ , colName type [ column_path ] [ AS JSON ] ] 
 [ , . . . n ] 
 ) 
]

Let’s take the below T-SQL script to have demo on this function(), which will help us to understand how we can query the json data and convert it back to sql server tabular format.

T-SQL Script:

DECLARE @jsonData VARCHAR(MAX) = ''
-- ASSIGN JSON Data to variable @jsonData...
SET @jsonData = (SELECT TOP (2) id, joined, ip, 
 latitude, longitude FROM [dbo].[visitors] 
 WHERE latitude IS NOT NULL AND longitude IS NOT NULL 
 ORDER BY NEWID() 
 FOR JSON AUTO, INCLUDE_NULL_VALUES)
-- EXTRACT @jsonData...
SELECT @jsonData AS JsonData
-- READ @jsonData WITHIN SELECT USING 'OPENJSON()' function...
SELECT * FROM OPENJSON(@jsonData) 
 WITH (
 id int, joined datetime, ip varchar(50), 
 latitude DECIMAL(18,10), longitude DECIMAL(18,10) 
 )
GO

Snapshot:

Hope, you will like this post and will share your feedback (, if any).

Thanks!

Happy Coding  🙂

“JSON Support”: One of the new features introduced in MS SQL Server 2016

Hello friends,

Hope, you are doing fine while reading this post!

MS SQL Server 2016 has introduced one of the coolest feature in this version. Now, we can also process the sql data into json format and also read / process json data/files back to sql server tables.

Before moving ahead, it is assumed that you know what JSON is. If not, then let me make you understand on a high level.

WHAT IS JSON?
JSON stands for Java Script Object Notation. It is a minimal, readable format 
for structuring data. It is used primarily to transmit data between a server 
and web application, as an alternative to XML. For more details, please visit 
the official JSON website.

Before SQL Server 2016 version, SQL Server supports the XML manipulation but now we can also play with json data which is most popular format used for the data interchange between different platforms and application without any glitches in-between.

How we can format a SQL query result as JSON?

We can export data from SQL Server as JSON, or format query results as JSON, by adding the FOR JSON clause to a SELECT statement.

FOR JSON clause is having further two options which can be used to structure the data as per user’s requirement or we can have the data as it is mentioned in SQL Server.

• USE PATH mode: When you use PATH mode with the FOR JSON clause, you maintain full control over the format of the JSON output. You can create wrapper objects and nest complex properties.

SELECT TOP (1) 
 id AS VisitorID, 
 joined AS 'Detail.Joined', 
 ip AS 'Detail.IP_Address', 
 latitude AS 'Geography.Latitude', 
 longitude AS 'Geography.Longitude'
FROM [dbo].[visitors] 
FOR JSON PATH, INCLUDE_NULL_VALUES
GO

Snapshot:

 

• USE AUTO mode: When you use AUTO mode with the FOR JSON clause, the JSON output is formatted automatically based on the structure of the SELECT statement.

SELECT TOP (2) id, joined, ip, latitude, longitude 
FROM [dbo].[visitors] 
FOR JSON AUTO
GO

Snapshot:

In the above screenshot, you will observe that the latitude and longitude columns are not extracted in the json formatted result. The reason is these columns are having NULL values for the two rows.

Snapshot:

To get these columns also to be extracted in json format, we can use the INCLUDE_NULL_VALUES keyword after FOR JSON <mode> within SELECT statement, as below:

SELECT TOP (2) id, joined, ip, latitude, longitude 
FROM [dbo].[visitors] 
FOR JSON AUTO, INCLUDE_NULL_VALUES
GO

Snapshot:

 

Hope, you will like this post and will really appreciate your feedback to improve the content of this post!

 

Happy Coding 🙂

 

 

SQL Server 2016’s new in-built function (i.e. SPLIT_STRING()) to split the string into rows using delimiter

Hi Friends,

Hope, you are doing fine!

Today, I found the SQL Server 2016 new in-built function (SPLIT_STRING()) which will make the developer’s life more easier, when it comes to split the string into rows using some delimiter.

Earlier to MS SQL Server 2016 version, we were using either XML Path() clause to split the string or to define some custom user-defined function to achieve this task.

You can explore this new function more by visiting this link.

Syntax:

SPLIT_STRING(<input_string>, <delimiter_character>)

Please note that the delimiter_character should be of single character expression and should not be some non-empty string.

Sample T-SQL:

SELECT * FROM STRING_SPLIT('My name is Gaurav Lal 
and I am going to demo today the new built-in-function 
of SQL-Server-2016 i.e. STRING_SPLIT()', ' ');

Snapshot (Result):

You can also apply this function within your SQL select statement. Let’s try this with an example:

Consider a case, where you have a table for CarPooling which contains two columns “CarNo” and “CarMembers“.

Note: CarMembers column is containing the comma-separated values in it.

Table and Data creation T-SQL:

DECLARE @CarPooling TABLE 
(
 Sno INT IDENTITY(1,1),
 CarNo VARCHAR(100), 
 CarMembers VARCHAR(300)
)
INSERT INTO @CarPooling 
 (carNo, CarMembers)
VALUES 
 ('DL-05-SW-4512', 'Jai, Neeraj, Mohit, Sachin, Gaurav'),
 ('HR-26-TQ-9085', 'Satya, Harinder, Vikas'),
 ('UP-16-RT-7775', 'Amrish, Ashish, Harsh, Amit')
SELECT * FROM @CarPooling
GO

Snapshot:

Now, if you wish to seperate this csv column into separate row, then you can use this STRING_SPLIT() function within your select statement using CROSS APPLY. Let’s have a look into next T-SQL below:

Solution (T-SQL):

DECLARE @delimiter_character CHAR(1) = ','
DECLARE @CarPooling TABLE 
( 
 Sno INT IDENTITY(1,1), 
 CarNo VARCHAR(100), 
 CarMembers VARCHAR(300)
)
INSERT INTO @CarPooling 
 (carNo, CarMembers)
VALUES 
 ('DL-05-SW-4512', 'Jai, Neeraj, Mohit, Sachin, Gaurav'), 
 ('HR-26-TQ-9085', 'Satya, Harinder, Vikas'), 
 ('UP-16-RT-7775', 'Amrish, Ashish, Harsh, Amit')
-- EXTRACT ORIGINAL DATA...
SELECT * FROM @CarPooling
-- Query using STRING_SPLIT() function to seperate the above 
-- comma-seperated-value defined in the column and make it 
-- as a seperate row value...
SELECT CarNo, LTRIM(RTRIM(value)) AS Individual_Member_Name
 FROM @CarPooling 
 CROSS APPLY STRING_SPLIT(CarMembers, @delimiter_character)
GO

Snapshot:

Please note, if you will pass the non-empty string as a delimiter, then you will get an error as below:

Msg 214, Level 16, State 11, Line 3
Procedure expects parameter 'separator' of type 'nchar(1)/nvarchar(1)'.

Snapshot:

Hope, you will enjoy this new feature and will share your valuable feedback!

Happy Coding 🙂

Fun with T-SQL: Get FIBONACCI SERIES using T-SQL and CTE

Hi Friends,

Hope, you are doing good!

Today, let’s create CTE demo to get FIBONACCI SERIES using T-SQL and CTE (Common Table Expression)

T-SQL Script:

DECLARE @FS_Length INT = 15 -- Defined Maximum Series Length
;WITH CTE_FIBONACCI_SERIES
AS
(
 SELECT 
 0 AS level, 
 CAST(0 AS FLOAT) As FNum, 
 CAST(1 AS FLOAT) AS NextValue
 UNION ALL
 SELECT 
 a.level + 1 AS level, 
 a.NextValue AS FNum, 
 a.FNum + a.NextValue AS NextValue 
 FROM CTE_FIBONACCI_SERIES a 
 WHERE level <= @FS_Length
)
SELECT FNum FROM CTE_FIBONACCI_SERIES 
OPTION (MAXRECURSION 0)
GO

Snap-shot:

That’s it… 🙂

Hope, you have enjoyed this!

Happy Coding 🙂

T-SQL Fun: T-SQL to print FLOYD’s TRIANGLE

Hi Friends,

Hope, you are doing fine while reading this post! I know that this is a very simple puzzle but thought why not to implement this using T-SQL which may be helpful to some of the freshers!

Let’s start now!

Problem:

——————————————————————–
T-SQL Fun: Write a T-SQL batch to print “Floyd’s Triangle”:
——————————————————————–
1
2 3
4 5 6
7 8 9 10
11 12 13 14 15

Note: If you don’t know about the Floyd’s Triangle, then you can take the help of google to explore this more!

T-SQL Script:

USE [GeneralDB]
GO
/*
--------------------------------------------------------------------
T-SQL Fun: Write a T-SQL batch to print "Floyd's Triangle":
--------------------------------------------------------------------
 1
 2 3
 4 5 6
 7 8 9 10
 11 12 13 14 15
*/
DECLARE @maxRow INT = 5, @row INT = 1, @col INT = 1, @ctr INT = 1,
 @valToPrint VARCHAR(MAX) = ''
WHILE (@row <= @maxRow)
 BEGIN
 SET @col = 1
 SET @valToPrint = ''
 WHILE (@col <= @row)
 BEGIN
 SET @valToPrint = @valToPrint + 
 (CASE WHEN @ctr BETWEEN 0 AND 9 
 THEN ' ' + CAST(@ctr AS VARCHAR(100)) 
 WHEN @ctr BETWEEN 10 AND 99 
 THEN ' ' + CAST(@ctr AS VARCHAR(100)) 
 ELSE CAST(@ctr AS VARCHAR(100)) END) + ' '
 SET @ctr = @ctr + 1
 SET @col = @col + 1
 END
 PRINT(@valToPrint)
 SET @row = @row + 1
 END
GO

Snapshot:

 

Hope, you would like this t-sql!

Happy Coding 🙂

“With Result Sets ()” – SQL Server 2012 T-SQL’s New feature

Hi Friends,

Hope, you are doing fine!

Today, I will let you know the new feature (“With Result Sets ()“) introduced in MS SQL Server 2012 version for the T-SQL Development.

But, before that, let’s understand the case where the importance of this new feature comes into the picture!

Problem Case:

Suppose, you have designed one stored procedure which returns some resultset back to the user (let’s say, the returned columns as “id” with datatype (int), “name” of datatype(varchar(20)) and “salary” of datatype (int)). Now, if the user wants the column name to be renamed and also to change the datatype of the column(s), then the user needs to do either of these below mentioned solutions:

1). Create another stored procedure with the same logic implemented there in the new SP and renamed the column alias and also change its datatype(s), which results in the code duplication and unnecessary creation of the different SPs.

2). Create a user-defined table-valued function and implement the same logic with new desired  column names and datatypes and use it, which again results in the code-duplication.

3). Edit the existing stored procedure and insert the records into the temporary tables and read it from temporary table itself.

And there could be many other alternates to achieve the same… I feel this was not good from the maintenance point of view.

What is “With Result Sets()” meant for?

MS SQL Server 2012 introduced a new feature named as “With Result Sets ()“. It is an extension to EXECUTE in the form of “WITH RESULT SETS()”.

This allows user to changed/renamed the column name(s) and its datatype(s) in the results returned from the stored procedure.

Now, we will see this from the below demo T-SQL scripts and its results…

T-SQL Scripts:

USE [PrepareTSQL]
GO

IF OBJECT_ID(N'[dbo].[EmployeeSalary]') IS NOT NULL
 DROP TABLE [dbo].[EmployeeSalary]
GO

CREATE TABLE [dbo].[EmployeeSalary]
(
 [id] [INT] IDENTITY(1,1) NOT NULL,
 [name] [VARCHAR] (20) NULL,
 [sal] [INT] NULL
) 
ON [PRIMARY]
GO

--Insert Data into table
insert into [dbo].[EmployeeSalary] (name, sal)
 values
 ('Anil', '100000'),
 ('Patel', '100000'),
 ('Chikoti', '300000'),
 ('Nikol', '100000'),
 ('Rajeev', '100000'),
 ('Binod', '300000'),
 ('Pradeep', '500000'),
 ('Dinel', '300000'),
 ('Gemil', '400000')
GO

The above code is to create one sample table “EmployeeSalary” and inserts some dummy data into it.

Snap-shot:

Now create one stored procedure to get the Nth Highest Salary based of the passed int parameter…

T-SQL Script:

USE [PrepareTSQL]
GO

IF OBJECT_ID(N'[dbo].[GetNthHighestSalary]') IS NOT NULL
 DROP PROCEDURE [dbo].[GetNthHighestSalary]
GO

CREATE PROCEDURE [dbo].[GetNthHighestSalary]
(
 @NthPosition INT = NULL
)
AS
BEGIN
 SET NOCOUNT ON

 SET @NthPosition = ISNULL(@NthPosition, 2)

 SELECT * FROM [dbo].[EmployeeSalary] 
 ORDER BY sal DESC OFFSET (@NthPosition - 1) 
 ROWS FETCH NEXT 1 ROW ONLY

 SET NOCOUNT OFF
END
GO

Snap-shot:

Now let’s execute the above stored procedure in the normal way as we do before sql server 2012.

T-SQL Script:

USE [PrepareTSQL]
GO

EXEC [dbo].[GetNthHighestSalary] 3
GO

Snap-shot:

Now, execute the above stored procedure along with this new introduced feature “With Result Sets()” to see the desired output :

T-SQL Script:

USE [PrepareTSQL]
GO

EXEC [dbo].[GetNthHighestSalary] 3
WITH RESULT SETS 
 (
 (
 EmployeeId money,
 EmployeeName VARCHAR(250),
 EmployeeSalary DECIMAL(18,2)
 )
 )
GO

Snap-shot:

 

Point to Remember:

Please note that the number of columns being returned in the result set cannot be changed, and the number of columns present in the stored procedure’s returned result should be identical within the “With Result Sets()” extension feature.

I hope you will like this post and share your feedback!

Happy Coding 🙂

 

 

 

 

Understand the importance of Filtered Indexes in SQL Server

Hi Friends,

Hope, you are doing good!

Today, we will be doing some demo to understand the importance of Filtered indexes within SQL Server.

What is Filtered Index?

Filtered Index were being introduced by Microsoft to its SQL Server family from version 2008 or later. A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table.

As the name is self-explanatory, these indexes gives the user to add the filter condition within non-clustered index itself.

Filtered indexes can provide the following advantages over full-table indexes:

• Improved query performance and plan quality
• Reduced index maintenance costs
• Reduced index storage costs

The SYNTAX of this as below:

CREATE NONCLUSTERED INDEX <INDEX_NAME> 
ON <[TABLE_SCHEMA_NAME].[TABLE_NAME]> 
         (<Col1> [, <Col2>] [, <Col3>]) 
[WHERE <FILTERED_CONDITION_1> 
       [<AND|OR> <FILTERED_CONDITION_2>] ];

Please refer the MSDN Link for your further reference!

Let’s understand be creating some sample demo:

Scenario:

Suppose, we have a user-defined table named as “PublishedArticals” with 10 lacs of rows and there are records for different categories. Now if there is no filetered index exists on this table and if the user wants to extract latest 20 recrods for category ID = 2, then the estimation will use Table Scan and takes a lot of time to extract the result.

The structure of this table “PublishedArticals” as below:

USE [GeneralPractice]
GO

CREATE TABLE [dbo].[PublishedArticals]
(
 PublishedID INT IDENTITY(1,1),
 PublishedDate DATETIME,
 PublishedCost MONEY,
 Category INT,
 ApprovalStatus INT,
 IsAnswered BIT DEFAULT 0 
)
GO

Now insert some dummy data into this table with the below T-SQL Script:

USE [GeneralPractice]
GO
SET NOCOUNT ON
INSERT INTO [dbo].[PublishedArticals]
 (PublishedDate, PublishedCost, Category, 
 ApprovalStatus, IsAnswered)
VALUES 
 (
 DATEADD(dd, -RAND() * 22520, GETDATE())
 ,ROUND(( RAND() * 1025 ), 2)
 ,CASE 
 WHEN CEILING(ROUND(( RAND() * 50 ), 2)) 
 BETWEEN 0 AND 10 THEN 1 
 WHEN CEILING(ROUND(( RAND() * 50 ), 2)) 
 BETWEEN 11 AND 20 THEN 2
 WHEN CEILING(ROUND(( RAND() * 50 ), 2)) 
 BETWEEN 21 AND 30 THEN 3
 WHEN CEILING(ROUND(( RAND() * 50 ), 2)) 
 BETWEEN 31 AND 40 THEN 4
 WHEN CEILING(ROUND(( RAND() * 50 ), 2)) 
 BETWEEN 41 AND 50 THEN 5
 WHEN CEILING(ROUND(( RAND() * 50 ), 2)) 
 BETWEEN 51 AND 60 THEN 6
 WHEN CEILING(ROUND(( RAND() * 50 ), 2)) 
 BETWEEN 61 AND 70 THEN 7
 WHEN CEILING(ROUND(( RAND() * 50 ), 2)) 
 BETWEEN 71 AND 80 THEN 8
 WHEN CEILING(ROUND(( RAND() * 50 ), 2)) 
 BETWEEN 81 AND 90 THEN 9
 ELSE 10
 END
 ,CASE WHEN (ROUND(( RAND() * 2 ), 2)/ 2) 
BETWEEN 0.00 AND 0.33 THEN 0 
WHEN (ROUND(( RAND() * 2 ), 2)/ 2) 
BETWEEN 0.34 AND 0.66 THEN 1 ELSE 2 END
 ,CAST((CASE WHEN CEILING(ROUND(( RAND() * 2 ), 2)) = 1 
THEN 1 ELSE 0 END) AS BIT)
 )
GO 1000000
SET NOCOUNT OFF
GO

Snap-shot:

 

Now open you new query window and let’s say that the user is asked to write the query for the latest 20 articles for a category id = 2 and ApproavlStatus = 2 And IsAnwered = 1 (Let’s say, this is some business requirements for the query).

So the query will be as below:

T-SQL Script:

USE [GeneralPractice]
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS time ON
GO
SET STATISTICS IO ON
GO
SELECT TOP (20) * FROM [dbo].[PublishedArticals] 
WHERE Category = 2 AND ApprovalStatus = 2 
AND IsAnswered = 1 
ORDER BY PublishedDate DESC
GO

Now, when you find the result then go to your “Messages” tab in the below result grid. You will find as below:

SQL Server Execution Times: CPU time = 78 ms,  elapsed time = 1538 ms.

Snap-shot:

 

Now, create the Filtered Index on the above business requirement:

T-SQL to create Filtered Index

CREATE NONCLUSTERED INDEX 
IDX_PublishedArticals_PublishedDate_FILTERED_Category_2 
ON [dbo].[PublishedArticals] (PublishedDate ASC) 
WHERE (Category = 2 
AND ApprovalStatus = 2 
AND IsAnswered = 1)
GO

Now when your index is created, then re-run the above query again and see the result:

SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 498 ms.

Snap-shot:

Now, you can see that there is a huge difference and this is the importance of the Filtered Indexes within SQL Server.

Happy Coding 🙂

T-SQL (tip) statement to view Trigger order sequence

Hi Friends,

Hope, you are doing fine!

Please find below the basic T-SQL statement to view the existing Trigger’s execution order over an object (i.e. Table):

Database Used: AdventureWorks2012

T-SQL Script:

USE [AdventureWorks2012]
GO

SELECT 
 name AS TriggerName,
 ROW_NUMBER() OVER (PARTITION BY parent_id 
         ORDER BY modify_date DESC) ExecutionOrder
 , CASE WHEN parent_id <= 0 THEN 'DDL' 
         ELSE 'DML' END AS TriggerType
 , CASE WHEN parent_id <= 0 THEN 'N/A' 
        ELSE OBJECT_NAME(parent_id) END AS TableName
 , CASE WHEN CAST(is_instead_of_trigger AS BIT) = 1 THEN 
         'Yes' ELSE 'No' END AS Is_InsteadOf_Trigger
FROM sys.triggers
WHERE parent_id = OBJECT_ID(N'[Production].[WorkOrder]')
ORDER BY modify_date DESC
GO

Snap-shot:

 

Please note that we can also set the trigger order using “sp_settriggerorder” system procedure. Please refer the MSDN Link for your reference.

Hope, this is useful to some viewers!

Happy Coding 🙂

 

T-SQL Demo: “Indexed view always use table index…”

Hi Friends,

Hope, you are doing fine!

One of the recent interview, I have been asked the question that “How indexed view works internally in SQL Server?” Thank GOD, I have read this over internet somewhere in the past and were able to answer this correctly.

Let’s see this by doing a demo:

First of all, let us create the demo table in t-sql:

-- CREATE SAMPLE TABLE
CREATE TABLE [dbo].[tbl_SampleData] 
(
 Col1 INT, 
 Col2 INT, 
 RawData VARCHAR(100)
)
GO

Now, do some dummy insertion of data into this table:

-- INSERT SOME DUMMY DATA
INSERT INTO [dbo].[tbl_SampleData]
(
 Col1, 
 Col2, 
 RawData
)
SELECT TOP 100000 
 ROW_NUMBER() OVER (ORDER BY obj.name), 
 ROW_NUMBER() OVER (ORDER BY obj.name DESC), 
 obj.name
FROM 
 sys.all_objects obj 
 CROSS JOIN sys.all_objects allObj
GO

Now, let’s create a Unique Clustered Index on the sample table, as below:

-- CREATE INDEX ON TABLE
CREATE UNIQUE CLUSTERED INDEX [IX_tbl_SampleData] 
ON [dbo].[tbl_SampleData] (Col1 ASC)
GO

Snap-shot:

 

Now, create a view and an index on the view:

-- Create sample View
CREATE VIEW [dbo].[vwSampleData] 
WITH SCHEMABINDING
AS
 SELECT 
 Col1,
 Col2, 
 RawData 
 FROM 
 [dbo].[tbl_SampleData]
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_vwSampleData] 
ON [dbo].[vwSampleData] (Col1 ASC)
GO

Snap-shot:

Now, we are ready with the sample table alongwith data and view and the relevant Table Index and View Index. Let’s open a new query window and write the below query:

-- Enable Execution Plan using CTRL + M
SELECT Col1, Col2, RawData FROM 
[dbo].[tbl_SampleData] WHERE Col1 = 4323189
GO

SELECT Col1, Col2, RawData FROM 
[dbo].[vwSampleData] WHERE Col1 = 4323189
GO

-- QUERYING WITH INDEX NAME FORCEFULLY
SELECT Col1, Col2, RawData FROM 
[dbo].[vwSampleData] WITH (INDEX ([IX_vwSampleData])) 
WHERE Col1 = 4323189
GO

 

Enable Execution Plan using CTRL + M, before running the above three sql statements.

Snapshot:

Query 1:

Query 2:

Query 3:

 

 

 

 

You can see that all the above three t-sql statements are using table index although, we have supplied the view index name within the last t-sql query.

I hope, you have enjoyed this post and share it across within your network.

– Happy Coding 🙂