Feeds:
Posts
Comments

Archive for the ‘SQL SERVER’ Category

We have been using REPLACE() function for ages in SQL Server whenever we need to replace any characters in a string but the problem arises when we need to replace multiple characters with multiple characters. In such cases we had to use REPLACE() function multiple times to achieve it till TRANSLATE() function came in picture in SQL Server 2017.

Compatibility Level:

Your database compatibility level MUST be 140 or higher to use TRANSLATE() function. Given below is the query to change your database compatibility level.

 ALTER DATABASE Northwind SET COMPATIBILITY_LEVEL = 140 

Let me demonstrate, how we were using REPLACE() function in earlier version of SQL Server:

Example 1: (Old approach using REPLACE() function)

In the given below Example, we used REPLACE() function twice in order to replace multiple characters in a string.


DECLARE @Number VARCHAR(25)
SET @Number='+92-3317892345'
SELECT @Number AS PhoneNumber
     , REPLACE(REPLACE(@Number,'+',' '),'-',' ') AS ReplacedPhoneNumber;

GO

--OUTPUT

Example 2: (New approach using TRANSLATE() function)

In new approach, we can achieve the same output using TRANSLATE() function and we do not need to write function twice.


DECLARE @Number AS VARCHAR(25)
SET @Number='+92-3317892345'
SELECT @Number AS PhoneNumber
     , TRANSLATE(@Number,'+-','  ') AS TranslatedPhoneNumber

GO

--OUTPUT

Now, we know how to use these functions (TRANSLATE() and REPLACE()). Let me show you how we can use it in query having tables.

Example 3: (Old approach using REPLACE() function)

In the given below example, we are going to replace ( , ),- with spaces using REPLACE() function.


USE Northwind
GO

SELECT FirstName
           , LastName
	   , Title
           , HomePhone
	   , REPLACE(REPLACE(REPLACE(HomePhone,'(',''),')',' '),'-',' ') AS ReplacedHomePhone
FROM [dbo].[Employees] ;
GO
--OUTPUT

Example 4: (New approach using TRANSLATE() function)

In the given below example, we are going to replace ( , ),- with spaces using TRANSLATE() function.


USE Northwind
GO

SELECT FirstName
           , LastName
	   , Title 
	   , HomePhone
	   , TRANSLATE(HomePhone,'()-','   ') AS TranslatedHomePhone  
FROM [dbo].[Employees] ;
GO
--OUTPUT

This image has an empty alt attribute; its file name is translate-ver-1.4.png

Conclusion:

I used TRANSLATE() function and found it very handy, the only limitation I can see is that the characters and translations should be the same size. If not it will generate error which we will discuss in upcoming articles. Do let know if you used this function and found it useful.

Advertisement

Read Full Post »

At times we come across some cases where we need to extract date and time for a certain period of time (year, month, day, hour, minutes, seconds etc.). In earlier version of SQL Server (earlier than 2022), we use multiple functions like DATEADD, DATEDIFF etc. to achieve it.

In SQL Server 2022, a new function namely DATETRUNC() shipped which solved this problem. Now, we can just use DATETRUNC() function to extract date and time till any date and time parts.

In the given below diagram, you can see the different date & time parts which can be easily extracted by DATETRUNC() function.

Example:

Let me demonstrate the functionality of DATETRUNC() function by giving below example.

DECLARE @DateTime DATETIME2
SET @DateTime = '2022-10-27 12:02:31.9033333';
SELECT @DateTime                    AS [Current Date]
      , DATETRUNC(YEAR,@DateTime)   AS [DateTrunc Function]
      ,'Extract Date Till The YEAR' AS [Description]
UNION
SELECT @DateTime                      AS [Current Date]
     , DATETRUNC(QUARTER,@DateTime)   AS [DateTrunc Function]
     ,'Extract Date Till The QUARTER' AS [Description]
UNION
SELECT @DateTime                    AS [Current Date]
     , DATETRUNC(MONTH,@DateTime)   AS [DateTrunc Function]
     ,'Extract Date Till The MONTH' AS [Description]
UNION
SELECT @DateTime                    AS [Current Date]
     , DATETRUNC(WEEK,@DateTime)    AS [DateTrunc Function]
     ,'Extract Date Till The WEEK'  AS [Description]
UNION
SELECT @DateTime                    AS [Current Date]
     , DATETRUNC(DAY,@DateTime)     AS [DateTrunc Function]
     , 'Extract Date Till The DAY'  AS [Description]
UNION
SELECT @DateTime                    AS [Current Date]
     , DATETRUNC(HOUR,@DateTime)    AS [DateTrunc  Function]
     , 'Extract Date Till The HOUR' AS [Description]
UNION
SELECT @DateTime                      AS [Current Date]
      , DATETRUNC(MINUTE,@DateTime)   AS [DateTrunc  Function]
      , 'Extract Date Till The MINUTE'AS [Description]
UNION 
SELECT @DateTime                       AS [Current Date]
      , DATETRUNC(SECOND,@DateTime)    AS [DateTrunc  Function]
      , 'Extract Date Till The SECOND' AS [Description]
UNION
SELECT @DateTime                            AS [Current Date]
      , DATETRUNC(MILLISECOND,@DateTime)    AS [DateTrunc  Function]
      , 'Extract Date Till The MILLISECOND' AS [Description]
UNION
SELECT @DateTime                            AS [Current Date]
      , DATETRUNC(MICROSECOND,@DateTime)    AS [DateTrunc  Function]
      , 'Extract Date Till The MICROSECOND' AS [Description];
GO
--OUTPUT

Conclusion:

I found the DATETRUNC() function very useful; earlier we used multiple functions to extract date and time till whichever time period we needed, but now we can achieve it with the help of this function.

Read Full Post »

History of query becomes very important when you really need the earlier version of your query, in case you do not have backups or you want to know which queries have been executed earlier in your respective database.

In SQL Server Management Studio, you can achieve it by running given below query but as per SQL Server documentation, the lifetime of the given below rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view and you cannot find the query in the history. Also, if your administrator restarts the server, you will lose the entire history. There is also a limitation that you can only get the queries executed successfully; failed queries will not be there in the history.

  • Query History in SQL Server Management Studio

USE AdventureWorks2019
GO

SELECT last_execution_time
     , text 
FROM sys.dm_exec_query_stats stats 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  
ORDER BY 
       last_execution_time DESC 
GO
--OUTPUT

  • Query History in Azure Data Studio

Fortunately, there is an extension available in Azure Data Studio to get the history of query and there is no limitation like I mentioned above.

Query History Extension Installation:

Let’s install the extension step by step.

Step 1: First of all, please click on View menu which is on the left top of the screen, then click on Extensions as shown below. You can also use the shortcut Ctrl+Shift+X to open Extensions window.

 

Step 2: Extensions window is now open. Let’s search the Query History extension as shown below and click on Install button.

Step 3: As you can see the Query History extension is installed as shown below.

Query History Extension Application:

Since, we have installed query history extension successfully, let’s use it step by step.

Step 1: Next step is to view the Output window, let’s go to View menu and then click on Output menu as shown below. Or you can also use the shortcut Ctrl+Shift+U.

Step 2: Output window has appeared as shown below.

Step 3: Now, let’s open a New Query window to run some queries and see the query history. To do this you need to go to File menu and then click on New Query, or you can use shortcut Ctrl+N.

Step 4: New Query window is opened as shown below. Please connect the respective database to run queries.

Step 5: Now, we need to execute some queries on Azure Data Studio to see the query history, as shown in the Query History window.

Sections of Query History window:

There are four sections in query history as shown below.

  1. Status icon: This section shows the status of query.
    If a query has executed successfully, we can see a green tick on it.
    If a query has error, we can see a cross sign in red which shows that the query has failed.
  2. Query Text: This section shows the actual query.
  3. Connection Info: This section shows the conection info like Server and Database name.
  4. TimeStamp: This section shows the timestamp when you executed the query.

Options in Query History:

Query history provides multiple options when you right-click on the query. You get the following options on the context menu:

  • Open Query.
  • Run Query.
  • Delete.
  • Clear History.

    Let’s discuss these options in detail.
  • Open Query:

The Open Query option opens the selected query from query history in a new query widow using the same connection as shown below.

  • Run Query:

The Run Query option opens the selected query from query history in a new query widow using the same connection and execute it immediately as shown below.

  • Delete:

The Delete option permanently deletes the selected row from query history.

  • Clear History:

The Clear History option clears the entire query history.

Query Window: Play / Pause options:

On the right top of the Query window, you can see a play / pause button. This feature gives us the flexibity to stop or start recording query history.

Conclusion:

Query History extension is very useful; you can view query history without much effort in Azure Data Studio.

Let me know if you use this extension and how user friendly you found it.

Read Full Post »

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 error message.

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.

SAMPLE:


USE tempdb
GO
--Create Sample table
CREATE TABLE dbo.tbl_Sample
(
[ID] INT,
[Name] NVARCHAR(50)
)
GO

OLD METHOD:
Given below methods are compatible with SQL Server 2005 and above.

--Method 1
USE tempdb
GO
IF OBJECT_ID('dbo.tbl_Sample', 'U') IS NOT NULL
DROP TABLE dbo.tbl_Sample
GO

--Method 2
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tbl_Sample')
DROP TABLE dbo.tbl_Sample
GO

NEW METHOD:
Given below method is compatible with SQL Server 2016 and above.

USE tempdb
GO
DROP TABLE IF EXISTS tbl_Sample;
GO

Reference : MSDN

Read Full Post »

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.

Error Generation:
Let me create a sample stored procedure to generate this error.

Use AdventureWorks2014
GO
--DROP PROCEDURE usp_Sample
--GO
CREATE PROCEDURE usp_Sample
AS
SELECT  
  [BusinessEntityID]
, [FirstName]
, [MiddleName]
, [LastName]
FROM [HumanResources].vEmployee ORDER BY BusinessEntityID 
GO
EXEC usp_Sample
GO

Error number 11553.1.1

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 22
EXECUTE 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.

Error number 11553.1.2

Resolution:
Why this error ? Because, I specifically mentioned in the RESULT SETS that none of the columns should return NULL and 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

Error number 11553.1.3

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 avoid such errors.

Read Full Post »

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 in SQL 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.

 USE master 
 GO
--DROP RESOURCE POOL Sample_Pool_Restrict_IO;
--GO
CREATE RESOURCE POOL Sample_Pool_Restrict_IO WITH
(
       MAX_IOPS_PER_VOLUME = 50,
       MIN_IOPS_PER_VOLUME = 1
);
GO

Step 2:
Once we created the pool, lets create the workload group. This is also a usual step while configuring resource governor.

 USE master 
 GO
 --DROP WORKLOAD GROUP Sample_Workload_Group_Restrict_IO
 --GO
 CREATE WORKLOAD GROUP Sample_Workload_Group_Restrict_IO 
 USING Sample_Pool_Restrict_IO;
 GO

Step 3:
Let’s, create a test user, later on I will demonstrate how resource governor will restrict physical IO for this test user.

 USE master
 GO
 CREATE LOGIN dba WITH PASSWORD = 'imran.1234@';
 GO
 ALTER SERVER ROLE [sysadmin] ADD MEMBER [dba]
 GO

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.

USE MASTER;
GO
--DROP FUNCTION dbo.fnIOClassifier
GO
CREATE FUNCTION dbo.fnIOClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
       DECLARE @GroupName SYSNAME
          IF SUSER_NAME() = 'dba'
            BEGIN
              SET @GroupName = 'Sample_Workload_Group_Restrict_IO'
           END
       ELSE
       BEGIN
              SET @GroupName = 'default'
       END
       RETURN @GroupName;
END
GO

Step 5:
Let’s assign the classifier function (created in the above step) to resource governor and reconfigure the resource governor to implement new settings.

USE master; 
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnIOClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

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.

Resource Governor 1.0

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.

USE AdventureWorks2014
GO
DBCC DROPCLEANBUFFERS
GO
EXEC sp_MSforeachtable 'SELECT * FROM ?'
GO

Resource Governor 1.1

Note: In the above results, you can easily view that the DISK READ IO/SEC reached the maximum of 122 and it may go far from it as well because we did not restrict DISK READ IO/SEC for sa user.

Step 8:
This time lets login with ‘dba’ user which we created in step 3 and execute the same script which we executed in the above step.

USE AdventureWorks2014
GO
DBCC DROPCLEANBUFFERS
GO
EXEC sp_MSforeachtable 'SELECT * FROM ?'
GO

Resource Governor 1.2

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.

Read Full Post »

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.

Let me explain this error in detail :

Message Number: 262

Severity : 14

Error Message: SHOWPLAN permission denied in database ‘AdventureWorks2012’.

Error Generation:

Let me create a sample to demonstrate this error.

USE AdventureWorks2012
GO

--Create a user
CREATE USER Imran WITHOUT LOGIN;
GO
--Grant select permission to Imran user
GRANT SELECT ON [HumanResources].[Department] TO Imran;
GO

-- Press Ctl+M to Include Actual Execution Plan
EXECUTE AS USER = 'Imran';
SELECT * FROM [HumanResources].[Department] ;
REVERT;

Msg 262, Level 14, State 4, Line 12
SHOWPLAN permission denied in database ‘AdventureWorks2012’.

ShowPlan error.1.1

Ooopps…… I am unable to show the actual execution plan (Ctrl + M) of the select query as shown above.

Resolution:

The resolution is very simple, actually, once you created the user you did not grant SHOWPLAN access to the USER (Imran). Lets give this user the SHOWPLAN access. Given below is the script.

Grant SHOWPLAN access

USE AdventureWorks2012
GO
GRANT SHOWPLAN TO Imran
GO
-OUTPUT

Command(s) completed successfully.

Now, the SHOWPLAN access has been granted, lets re-run the above query with execution plan and it works as shown below.

ShowPlan error.1.2

Conclusion :

Remember, whenever you want any user to include the execution plan in the query, you MUST give him SHOWPLAN access.

Read Full Post »

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 Insert record(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.

Temporal Table.1.7

      • 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 MOVED FIRST into the history table to record the changes before the data is changed in the temporal table.

Temporal Table.1.8

      • What happens when you Query record(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 NOT have to apply any joins or any sub query between temporal table and history table.

Temporal Table.1.9

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

CREATE DATABASE SampleDB
GO
USE SampleDB
GO
--DROP TABLE tbl_Product
--GO
CREATE TABLE tbl_Product
(
Product_ID int NOT NULL PRIMARY KEY CLUSTERED,
Product_Name varchar(50) NOT NULL,
Rate numeric(18,2),

/*Temporal Specific Fields - Period Definition */
[Valid From] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
[Valid Till] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME ([Valid From] ,[Valid Till])
)
WITH
/* Temporal Specific - System-Versioning Configuration*/
(SYSTEM_VERSIONING = ON
    (HISTORY_TABLE = dbo.tbl_Product_History, DATA_CONSISTENCY_CHECK = ON)
);
GO

Create Temporal Table

  • Data Manipulation Language (DML) Statements on Temporal Table ?
    Lets execute some Data Manipulation (DML) Statement on temporal table and view the impact on temporal table as well as history table.

      • INSERT STATEMENT

    As I explained earlier, when you execute INSERT statement on temporal table there will be NO Impact on history table. Lets execute INSERT statements and observe the results accordingly.

    USE SampleDB
    GO
    INSERT INTO dbo.tbl_Product VALUES
     (1,'Product A', 300)
    ,(2,'Product B', 400)
    GO
    
    SELECT * FROM tbl_Product
    GO
    SELECT * FROM dbo.tbl_Product_History
    GO
    

    Temporal Table.1.1

      • UPDATE STATEMENT

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.

USE SampleDB
GO
UPDATE tbl_Product SET Rate =Rate/2
WHERE Product_ID IN (1,2)
GO

SELECT * FROM tbl_Product
GO
SELECT * FROM dbo.tbl_Product_History
GO

Temporal Table.1.2

      • DELETE STATEMENT

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.

USE SampleDB
GO
DELETE FROM tbl_Product WHERE Product_ID = 2
GO

SELECT * FROM tbl_Product
GO
SELECT * FROM dbo.tbl_Product_History
GO

Temporal Table.1.3

      • SELECT STATEMENT

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

Temporal Table.1.4

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

Temporal Table.1.5

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

Temporal Table.1.6

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.

Let me know your experience about temporal table.

Reference : Channel9

Read Full Post »

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 EXECUTE TRUNCATE 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.

Sample :


USE master
GO
ALTER DATABASE [Sample_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
--Drop Sample database
DROP DATABASE [Sample_DB]
GO
--Create Sample database
CREATE DATABASE Sample_DB
GO

-- Create Filegroups
ALTER DATABASE Sample_DB ADD FILEGROUP [2014]
GO
ALTER DATABASE Sample_DB ADD FILEGROUP [2015]
GO

--Create one file for each filegroup and
--so that you can store partition data

ALTER DATABASE Sample_DB
  ADD FILE
  (NAME = N'FG_2014',
  FILENAME = N'C:\Data\FG_2014.ndf'
  )
  TO FILEGROUP [2014]
GO  

ALTER DATABASE Sample_DB
  ADD FILE
  (NAME = N'FG_2015',
  FILENAME = N'C:\Data\FG_2015.ndf'
  )
  TO FILEGROUP [2015]
GO  

USE Sample_DB
GO

-- Drop the partition function if it exists
IF( EXISTS( SELECT * FROM sys.partition_functions
WHERE name = 'OrderDateRangePFN' ) )
BEGIN
DROP PARTITION FUNCTION OrderDateRangePFN
END
GO

--Create partition function
CREATE PARTITION FUNCTION OrderDateRangePFN(DATETIME)  AS
  RANGE LEFT FOR VALUES
  ('2014-12-31',
  '2015-12-31'
  )
GO

-- Drop the partition Scheme if it exists
IF( EXISTS( SELECT * FROM sys.partition_schemes
WHERE name = 'OrderDateRangePScheme' ) )
BEGIN
DROP PARTITION SCHEME OrderDateRangePScheme
END
GO

--Create partition scheme
CREATE PARTITION SCHEME OrderDateRangePScheme AS
  PARTITION OrderDateRangePFN  TO
  ([2014],
   [2015],
   [PRIMARY]  )
GO

-- Drop sample table if exists
IF( OBJECT_ID( 'tbl_Sample', 'U' ) IS NOT NULL )
BEGIN
   DROP TABLE tbl_Sample
END
GO

-- Create sample table
CREATE TABLE tbl_Sample
(OrderID INT NOT NULL,
[OrderDate] DATETIME)
ON OrderDateRangePScheme ([OrderDate]);
GO

-- Insert some sample data
insert into tbl_Sample Values (1,'2014-01-01')
insert into tbl_Sample Values (2,'2014-02-01')
insert into tbl_Sample Values (3,'2014-03-01')
insert into tbl_Sample Values (4,'2015-01-01')
insert into tbl_Sample Values (5,'2015-02-01')
insert into tbl_Sample Values (6,'2015-03-01')
insert into tbl_Sample Values (7,'2015-04-01')
insert into tbl_Sample Values (8,'2016-01-01')
insert into tbl_Sample Values (9,'2016-02-01')
GO

SELECT * FROM tbl_Sample
GO

Truncate Table 1.1

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

Truncate Table 1.2

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

Truncate Table 1.3

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

Truncate Table 1.4

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.

Reference : MSDN

Read Full Post »

« Newer Posts - Older Posts »