SQL Server 2016 shipped with a lot of TSQL Enhancements and one of them is DROP IF EXISTS. It actually minimizes the size of the script which you can use to drop any object (AGGREGATE, ASSEMBLY, COLUMN, CONSTRAINT, DATABASE, DEFAULT, FUNCTION, INDEX, PROCEDURE, ROLE, RULE, SCHEMA, SECURITY POLICY, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER, and VIEW). In other words, you DO NOT need to write long IF EXISTS Statements anymore to drop any object.
The beauty of this TSQL is that if it fails (in case, if object does not exist), it DOES NOT give you errormessage.

Lets me create a sample table to demonstrate how it works but as I mentioned earlier you can use this script to drop any object.

Problem :
Whenever you modify the underlying objects (tables, data types etc) of any non-schema-bound stored procedure, user-defined function, view, trigger mostly due to customization then your object will not address those customization and might give you erroror unexpected results. So how to fix this ?

Before proceeding with the solution, let me create a sample table & a table-valued function to demonstrate the solution.

Step 2 :
Once you modified the table, lets browse the table-valued function and see if above change (Added a new column in the table) is reflecting.

USE tempdb
GO
Select * from dbo.fn_Employee ()
GO
--OUTPUT

Step 3 :
Oopss…… If you look at the above result set, you can observe that whatever change (Added a new column in the table) you made in the table, did not reflect in the table-valued function. It means that whenever you modify an underlying object of any non-schema-bound object then the metadata DOES NOT get updated automatically. Lets update it manually with the help of sp_refreshsqlmodule (One of the system stored procedures).

Step 4 :
It seems, table-valued function’s metadata has been updated in the above step. Lets browse the table-valued function to verify whether its metadata has been updated successfully or not.

USE tempdb
GO
Select * from dbo.fn_Employee ()
GO
--OUTPUT

As you can see above the table-valued function’s metadata has been updated successfully.

Conclusion :
The bottom line of this article is, whenever you alter any underlying object (table, datatype) of any non-schema-bound object then DO NOT forget to refresh the metadata of that particular object using sp_refreshsqlmodule.

EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d, and the corresponding value sent at run time was null is one of the new error messages noticed in SQL Server 2012 and above. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS shipped with SQL Server 2012.

Lets discuss this in detail: Message Number: 11553

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d, and the corresponding value sent at run time was null.

You can see that the above stored procedure is executed successfully and it has ONE result set.

Lets try to execute it using WITH RESULT SETS clause.

USE AdventureWorks2014
GO
EXEC usp_Sample
WITH RESULT SETS
(
( [BusinessEntityID] int NOT NULL,
[First Name] Name NOT NULL,
[MiddleName] Name NOT NULL,
[LastName] Name NOT NULL
));
GO
--OUTPUT

Msg 11553, Level 16, State 1, Procedure usp_Sample, Line 22EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #3 in result set #1, and the corresponding value sent at run time was null.

Ooops…… I am unable to execute it properly. It returned the error message.

Resolution:
Why this error ? Because, I specifically mentioned in the RESULT SETS that none of the columns should return NULLand I did not realize that stored procedures return some NULL values in the middle name column and due to this I got this error. So, when you mention any column as a NOT NULL in RESULT SETS, please make sure that the stored procedure must NOT return NULL VALUE for that particular column. Lets re-write the RESULT SET and this time we need to mention, middle name column is NULL as shown below.

USE AdventureWorks2014
GO
EXEC usp_Sample
WITH RESULT SETS
(
( [BusinessEntityID] int NOT NULL,
[First Name] Name NOT NULL,
[MiddleName] Name NULL,
[LastName] Name NOT NULL
));
GO
--OUTPUT

Conclusion :
Remember, whenever you use any stored procedure using WITH RESULT SETS clause, along with the nullable type, make sure that the data that comes from stored procedure should match with the nullable type. In other words, if you define any column in RESULT SETS clause as NOT NULL column then make sure that stored procedure MUST NOT return NULL value in that particular column to avoidsuch errors.

Resource Governor is one of the best features shipped with SQL Server 2008. We use this feature to handle SQL Server workload and resource allocation. However, if you go into further details of this feature, you will be shocked to see the level of control it gives you to control over CPU, memory on the basis of incoming requests from the application/user etc. But I have been hearing a very common QUESTION since SQL Server 2008 and that is, can we control physical IO using resource governor and the ANSWER is YES, you can do it inSQL SERVER 2014 & Above. But How ?

In SQL Server 2014, we have an additional control in resource governor namely CONTROL PHYSICAL IO. In other words, if you would like to restrict a user/application to use limited physical IO, you can restrict it with this additional control. You can implement this additional control in resource governor by just setting up two options which is MIN_IOPS_PER_VOLUME & MAX_IOPS_PER_VOLUME.

Let me demonstrate how to control physical IO in SQL Server 2014 step by step.

Step 1:
First of all, lets create a pool as usual and restrict its MAX_IOPS_PER_VOLUME limit to 50 ONLY, which means that whatever set of users / applications will be used, this pool cannot exceed 50 Physical IO.

Step 4:
Create a classifier function and define if the above created user is the current user. Then classifier will assign the restricted IO workload group to this user. Then each time this user tries to use physical IO, it CAN’T go beyond 50. SQL Server will restrict this user up to 50.

Step 6:
Now, we are ready to test the new feature (Control Physical IO). To test the feature, let’s open performance monitor, add counter and then select resource pool stats object, further select Disk read IO/sec and add default and custom Pool (Sample_Pool_Restrict_IO) created in step 1 and press ADD BUTTON as shown below.

Step 7:
Now, we setup performance monitor to test the physical IO control feature via resource governor. Lets login with any user except the one created in step 3 and run the given below script and view the results in performance monitor. In my case, I logged in with sa and executed the script.

Note: In the above results, you can easily view that the DISK READ IO/SEC reached the maximum of 50 and why is it not going beyond 50 is because dba user has been restricted by resource governor using physical IO control feature.

Conclusion:
As you can see above that this feature really gives DBA’s more control on physical IO and it would be very handy for the DBA’s where they have serious problems with I/O which can be from users / applications.

Few days ago, I created some users in my development environment and gave them access to use my development database. After sometime, I received a complaint that when they run a normal query it is working fine but when they try to run a query along with execution plan (Ctrl + M), SQL server generates given below error.

Temporal Table is one of the best features available in SQL Server 2016. It actually provides your data stored in the table at any point in time. In other words, if you execute any update or delete statement on a table then the old data will be over written and after that if you query the table you will get the latest data (after update or delete), but using temporal table you can view the latest data as well as old data but how ? Let me explain how it works first.

How Temporal Table works ?

What happens when you Insertrecord(s) in Temporal Table ?

When you insert the data in temporal table, the data will remain in temporal table like a normal table but it will NOT affect the history table as shown below.

What happens when you Update/Delete record(s) in Temporal Table ?

When you update/delete the data in temporal table, the existing records will be MOVEDFIRST into the history table to record the changes before the data is changed in the temporal table.

What happens when you Queryrecord(s) in Temporal Table ?

When you Query the records from the temporal table, Temporal table is smart enough to decide whether to return the data from the temporal table or from the history table, you do NOThave to apply any joins or any sub query between temporal table and history table.

Create Temporal Table

When you create a temporal table it automatically creates a history table (if you already have an existing history table you can link it to temporal table). As you can see below is a usual table creation script with additional columns. These additional column (fields) are specific for temporal table period definition and it will be hidden as well. So when you query the table these columns will not appear in the result. Also, I turned on the system versioning and declared the history table name “dbo.tbl_Product_History”, in this case the temporal table will create the history table as defined. If you do not declare the history table name, SQL will create a history table for the temporal table by default.

As I explained earlier, when you execute UPDATE statement on temporal table then the OLD version of data will be moved to history table and temporal table will have latest data ONLY. Lets execute UPDATE statements and observe the results accordingly.

As I explained earlier, when you execute DELETE statement on temporal table then the OLD version of data will be moved to history table and temporal table will have latest data ONLY. Lets execute DELETE statement and observe the results accordingly.

Select Statement is interesting in temporal table because it knows what you exactly want from temporal table and it internally links to history table to fulfill your requirement. Given below are some queries that we will run on temporal table and you will observe that temporal table (NOT history table) will return the current state and earlier state of the table as well. Isn’t in it amazing ?

Lets browse the TEMPORAL TABLE and it will show the latest state of the table like a NORMAL TABLE.

USE SampleDB
GO
--Current State of the table
SELECT * FROM tbl_Product
GO

Lets browse the TEMPORAL TABLE FOR SYSTEM_TIME ‘2015-06-27 21:33:50.9002439’. It will give you the state of the table at ‘2015-06-27 21:33:50.9002439’ and you will be shocked to see that temporal table returned the result what was available exactly BEFORE the UPDATE statements. Wow !!!

USE SampleDB
GO
SELECT * FROM tbl_Product
FOR SYSTEM_TIME AS OF '2015-06-27 21:33:50.9002439'
GO

Lets browse the TEMPORAL TABLE FOR SYSTEM_TIME at ‘2015-06-27 21:43:31.2982847’. You will be AGAIN shocked to see that temporal table returns the result what was available exactly available BEFORE the DELETE statements. Wow !!!

USE SampleDB
GO
SELECT * FROM tbl_Product
FOR SYSTEM_TIME AS OF '2015-06-27 21:43:31.2982847'
GO

Conclusion :
I quickly reviewed temporal table and found it very interesting and exciting. I am sure it will change the way databases will be designed specially data warehouses because now we do not need to create separate audit tables to record each state manually by using stored procedure or triggers etc while TEMPORAL TABLE is doing it for us behind the scene automatically.

Truncate table is always my preference when I need to delete all the records from any table. The reason for the preference is, actually Truncate Table is faster than delete and it takes less system and transaction log resources. However, there is a problem with TRUNCATE TABLE and that is when you EXECUTETRUNCATE TABLE, it actually deletes all the records in the table and you cannot specify any criteria (WHERE CLAUSE) for deletion like Delete.

The Good News is in SQL Server 2016 you can specify the partition you want to delete using Truncate Table.

Let me create a sample partition table and insert some sample data in that table and then demonstrate how it works in few easy steps.

Step 1 :
Lets find out if the data exists in the partitions properly. Given below is the script.

USE Sample_DB
GO
-- Check if data exists in the partition properly
SELECT OBJECT_NAME(OBJECT_ID) AS [Table Name]
, partition_number AS [Partition Number]
, rows AS [Number of rows]
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='tbl_Sample';
GO

Step 2 :
Lets TRUNCATE TABLE the data of PARTITION 2 in the sample table. Given below is the script.

USE Sample_DB
GO
TRUNCATE TABLE tbl_Sample
WITH (PARTITIONS (2));
GO

Step 3 :
Lets find out if the data of partition 2 has been deleted in the sample table or as usual TRUNCATE TABLE has deleted all the data in the table. Given below is the script.

USE Sample_DB
GO
-- Check if data exists in the partition properly
SELECT OBJECT_NAME(OBJECT_ID) AS [Table Name]
, partition_number AS [Partition Number]
, rows AS [Number of rows]
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='tbl_Sample';
GO

Wow, as expected only partition 2 data has been deleted from sample data. This feature is very helpful for the DBAs handling lots of partition in a single table and if they want to delete any particular partition(s) data quickly.

Blog Stats

Follow Blog via Email

Disclaimer

This blog/website is a personal blog/website and all articles, postings and opinions contained herein are my own. The contents of this blog/website are not intended to defame, purge or humiliate anyone should they decide to act upon or reuse any information provided by me. Comments left by any independent reader are the sole responsibility of that person. Should you identify any content that is harmful, malicious, sensitive or unnecessary, please contact me via email (imran@raresql.com) so I may rectify the problem.