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: Find the currently running queries in sql server

Hi Friends,

Hope, you are doing fine!

Please find below the T-SQL script to find the T-SQL batches/queries/statements, which is/are still running as on current time on a SQL Server instance!

T-SQL Script:

SELECT
 queryRequest.session_id, 
 queryRequest.start_time, 
 queryStatement.text
FROM
 sys.dm_exec_requests queryRequest
 CROSS APPLY
 sys.dm_exec_sql_text(queryRequest.sql_handle) queryStatement
GO;

-- DMV (EXTRACT Complete COLUMNS FROM THIS DM VIEW)
SELECT * FROM sys.dm_exec_requests WHERE sql_handle IS NOT NULL
GO;

Please find below the snapshot of a result of this query:

Snapshot:

That’s it…!

Hope, this post may be useful to someone!

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 🙂

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 🙂

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

Understanding the SQL View’s behavior in SQL Server

Hi Friends,

Hope, you are doing fine!

Today, we will see how SQL View behaves in SQL Server, which was recently asked to me by an interviewer, in one of the interview.

Let’s assume that there two users working on their independent machines on the same database.

User-1 Activity: User-1 is creating a base table in SQL Server database and creating a view.

User-2 Activity: User-2 is trying to change the name of the columns in an existing base table created by User-1 and also introduced an additional column to the same.

Will the view will reflect the changes done by User-2, assuming that the view definition is containing the statement as “SELECT * FROM [schema_name].[Base_Table_Name]”.

Let’s try this:

T-SQL Script written by User-1:

USE [Interviews]
GO

/*
ASSUME THAT "USER - 1" IS WORKING ON SOME MACHINE 
& IS CREATING A BASE TABLE AND INSERTING SOME RECORDS.
POST THAT, HE IS ALSO CREATING A VIEW WHICH IS 
REFERENCING TO THE BASE TABLE CREATED BY HIM.
*/

IF EXISTS (SELECT 'x' FROM sys.objects 
WHERE name = N'BaseTable_ToDemo_ViewBehaviour' 
AND type = 'U')
 DROP TABLE [dbo].[BaseTable_ToDemo_ViewBehaviour];
GO

CREATE TABLE [dbo].[BaseTable_ToDemo_ViewBehaviour] 
( EmpID INT, EmpName VARCHAR(100) )
GO

INSERT INTO [dbo].[BaseTable_ToDemo_ViewBehaviour] 
(EmpID, EmpName) 
VALUES (1, 'Rajat Jain'), (2, 'Aamir Badshah');
GO

--You then create a view "View_BaseTable_ToDemo_ViewBehaviour"
IF EXISTS (SELECT * FROM sys.objects 
WHERE name = N'View_BaseTable_ToDemo_ViewBehaviour' 
AND type = 'V')
 DROP VIEW [dbo].[View_BaseTable_ToDemo_ViewBehaviour];
GO

CREATE VIEW [dbo].[View_BaseTable_ToDemo_ViewBehaviour] 
AS
SELECT * FROM [dbo].[BaseTable_ToDemo_ViewBehaviour];
GO

-- BASE TABLE (SELECT QUERY)
SELECT * FROM [dbo].[BaseTable_ToDemo_ViewBehaviour];

-- VIEW (SELECT QUERY)
SELECT * FROM [dbo].[View_BaseTable_ToDemo_ViewBehaviour];

Snap-shot [User-1]:

We can see that everyone is getting extracted as it is populated by User-1.

Now, let’s assume that User-2 is also working on this table on his one of the business requirements and he have no reason to change the structure of this base table.

T-SQL Script written by User-2:

USE [Interviews]
GO

/*ASSUME THAT "USER - 2" IS WORKING ON SOME 
OTHER MACHINE & UNFORTUNATELY HE IS TRYING TO 
CHANGE THE TABLE COLUMNS BY DROP/CREATE TABLE*/

IF EXISTS (SELECT 'x' FROM sys.objects 
WHERE name = N'BaseTable_ToDemo_ViewBehaviour' 
AND type = 'U')
 DROP TABLE [dbo].[BaseTable_ToDemo_ViewBehaviour];
GO

CREATE TABLE [dbo].[BaseTable_ToDemo_ViewBehaviour] 
(
 EmployeeCode INT, 
 EmployeeName VARCHAR(100),
 Dept VARCHAR(100),
)
GO

INSERT INTO [dbo].[BaseTable_ToDemo_ViewBehaviour] 
(EmployeeCode, EmployeeName, Dept)
 VALUES 
(1, 'Nisha Kapoor', 'IT'), 
(2, 'Ronald Fakrey', 'HR');
GO

Snap-shot [User-2]:

As we can see that User-2 dropped the base table and recreate the table with different column name and with an additional column.

Also, we know that the view definition created by User-1 is as below:

USE [Interviews]
GO

IF EXISTS (SELECT * FROM sys.objects 
WHERE name = N'View_BaseTable_ToDemo_ViewBehaviour' 
AND type = 'V')
 DROP VIEW [dbo].[View_BaseTable_ToDemo_ViewBehaviour];
GO

CREATE VIEW [dbo].[View_BaseTable_ToDemo_ViewBehaviour] 
AS
SELECT * FROM [dbo].[BaseTable_ToDemo_ViewBehaviour];
GO

Now, what will happen if we run the below statements:

-- BASE TABLE (SELECT QUERY)
SELECT * FROM [dbo].[BaseTable_ToDemo_ViewBehaviour];

-- VIEW (SELECT QUERY)
SELECT * FROM [dbo].[View_BaseTable_ToDemo_ViewBehaviour];

Let’s see below snapshot when we run the above two statements within our SSMS window:

Snapshot:

What’sss this…!!!

The new column and the changed column did not get reflected to the view. It is still referring the old columns although we have used “SELECT * FROM [dbo].[BaseTable_ToDemo_ViewBehaviour];“

The reason behind this is “When a view is created using “SELECT *”, the view is saved to use the ordinal position of each column in the underlying table, with that given column name as an alias. If, for example, the data types of the relative columns were to change, the select would have failed with a conversion error.”

If we need to reflect the new changes in the view, you should use system procedure “sp_refreshview ‘ViewName’;” to refresh the view.

Note: Even better, though, would be to create the view with SCHEMABINDING, then the view would have to be created specifying the required column names and one could not drop or alter the underlying table without first dropping the view.

Also if the user-1 would have used the column names of the base tables instead of “SELECT *”, and then if the user changed the column names in the base table, then the view will throw an error. Let’s see this ahead:

T-SQL Code:

 USE [Interviews]
GO

/*
Now, If the User-1 has created the view with the 
Column Names of the base table instead of astrick (*) 
symbol,then everything will work fine....
*/
--You then create a view "View_BaseTable_ToDemo_ViewBehaviour"
IF EXISTS (SELECT * FROM sys.objects WHERE 
name = N'View_BaseTable_ToDemo_ViewBehaviour' AND type = 'V')
 DROP VIEW [dbo].[View_BaseTable_ToDemo_ViewBehaviour];
GO

CREATE VIEW [dbo].[View_BaseTable_ToDemo_ViewBehaviour] 
AS
SELECT EmpID, EmpName 
FROM [dbo].[BaseTable_ToDemo_ViewBehaviour];
GO

-- BASE TABLE (SELECT QUERY)
SELECT * FROM [dbo].[BaseTable_ToDemo_ViewBehaviour];

-- VIEW (SELECT QUERY)
SELECT * FROM [dbo].[View_BaseTable_ToDemo_ViewBehaviour];

Snap-shot:

Now if the user-2 have done his activity post this as above, and then when user-1 will run the query from the view, it will give an error.

Snap-shot:

Please share your comments or feedback, if you like this post or if you have the better solution or explanation for this.

Happy Coding 🙂

Fun with T-SQL: Generate RAW data (mainly of DateTime & Money data type) using T-SQL

Hi Folks,

Hope, you are having a great time!

Sometimes, we need to generate some RAW data for our development purposes. Let’s try the below mentioned T-SQL script to generate the RAW data to a user-defined table within a database and this script shows that how will you generate the RAW data specially for DateTime and Money data type in SQL Server.

The T-SQL script is as below:

T-SQL Script:

USE [testDB]
GO

Create TABLE [dbo].[TestRawData]
(
 KeyID INT IDENTITY(1,1),
 OrderDate DATETIME,
 OrderValue MONEY
)
GO

SET NOCOUNT ON
INSERT INTO [dbo].[TestRawData] 
 (OrderDate, OrderValue)
VALUES 
 (DATEADD(dd, -RAND() * 22520, GETDATE()), ROUND(( RAND() * 1025 ), 2))
GO 10000
SET NOCOUNT OFF
GO

SELECT * FROM [dbo].[TestRawData]
GO

DROP TABLE [dbo].[TestRawData]
GO

Snapshot:

Hope, you may like it….!

Happy Coding 🙂

Fun with T-SQL: Extract the similar dates based on your DOB and which also lies on the same day when you were born.

Hi Friends,

Hope, you are doing!

Let’s do fun with T-SQL:

Problem#

Enter your Date of Birth in “mm/dd/yyyy” format and find its Day_Name (Monday, TuesDay, etc etc…). Now we need to extract the next similar dates based on your Date Of Birth for the next 100 years and which also lies on the same Day_Of_Week when you were born….!

Expected Output#

• DOB Entered by the User: 10/18/1981  (mm/dd/yyyy)
• Day_Of_Week Name: SUNDAY

Now we need to write a T-SQL Query to find the next Birthday which will/was coming on SUNDAY, in this case.

T-SQL Script:

USE [testDB]
GO
/*
PUZZLE# Enter your Date of Birth and find its Day_Name 
          (Monday, TuesDay, etc etc...)
 Extract the next similar dates to your Date Of Birth 
 and which also lies on the same Day_Of_Week when you were born....
*/
DECLARE @DOB DATETIME = '10/18/1981' -- Your Date Of Birth ("mm/dd/yyyy" format)
/* Here you can place your DOB date and run this T-SQL query to find your details.... */
DECLARE @EndDate DATETIME, @Day_Name VARCHAR(100) = ''
SET @Day_Name = DATENAME(DW, @DOB)
SET @EndDate = CAST((CONVERT(VARCHAR, DATEADD(YY, 100, @DOB), 101) + ' 00:00:00') 
AS DATETIME)
;WITH CTE_DOB_DATES 
AS
(
 SELECT @DOB AS Date_Of_Birth, DATENAME(DW, @DOB) AS Day_Name
 UNION ALL
 SELECT 
 DATEADD(YY, 1, Date_Of_Birth) AS Start , 
 DATENAME(DW, DATEADD(YY, 1, Date_Of_Birth)) AS Day_Name 
 FROM CTE_DOB_DATES 
 WHERE Date_Of_Birth <= @EndDate 
)
SELECT * FROM CTE_DOB_DATES WHERE @Day_Name = Day_Name 
OPTION (MAXRECURSION 0)
GO

Snap-shot

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

Happy Coding 🙂

T-SQL code to extract the remaining time left for my exit date in the current organisation

Hi Friends,

Hope you are doing good!

Please find below the t-sql script to find the difference between two dates using DATEDIFF() function.

In fact, this t-sql code is to extract the remaining time for my exit date in my current organisation.

T-SQL Code:

USE [testDB]
GO

/***************************************************************************************
 T-SQL QUERY SNIPPET TO GET REMAINING DAYS FOR OUR EXIT DAY IN THE COMPANY ....
***************************************************************************************/

DECLARE 
 @currentDate DATETIME = GETDATE(), 
 @OurExitDayInCompany DATETIME = '2015-12-31 20:00:00'

; WITH CTE_COUNT_DOWN (Fact, Description)
AS
(
 SELECT DATEDIFF(DAY, @currentDate, @OurExitDayInCompany), 'Days Left'
 UNION ALL
 SELECT DATEDIFF(MONTH, @currentDate, @OurExitDayInCompany), 'Months Left'
 UNION ALL
 SELECT DATEDIFF(YEAR, @currentDate, @OurExitDayInCompany), 'Years Left'
 UNION ALL
 SELECT DATEDIFF(QUARTER, @currentDate, @OurExitDayInCompany), 'Quarters Left'
 UNION ALL
 SELECT DATEDIFF(HOUR, @currentDate, @OurExitDayInCompany), 'Hours Left'
 UNION ALL
 SELECT DATEDIFF(MINUTE, @currentDate, @OurExitDayInCompany), 'Minutes Left'
 UNION ALL
 SELECT DATEDIFF(SECOND, @currentDate, @OurExitDayInCompany), 'Seconds Left' 
)
SELECT * FROM CTE_COUNT_DOWN
GO

Snap-shot:

Hope, you like it…!

Happy Coding 🙂