Features | SQL Server Portal

Archive for the ‘Features’ Category

SQL Server 2022 – TSQL – IS [NOT] DISTINCT FROM

Posted in Features, SQL SERVER, tagged Having Clause, IS [NOT] DISTINCT FROM, SQL, SQL Server, WHERE clause on March 27, 2023| Leave a Comment »

SQL Server 2022 brought some exciting features which will help us in optimising the SQL scripts. IS [NOT] DISTINCT FROM is one of the new features. It helps us in code optimisation and reduce the code complexity.

Compatibility Level:

Your database compatibility level MUST be 160 or higher to use this new feature.

 ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 160

Sample:

USE tempdb
GO
 
CREATE TABLE [#Customer](
[CustomerID] INT IDENTITY(1,1),
[CustomerName] VARCHAR(100),
[PhoneNumber] VARCHAR(100),
[AlternatePhoneNumber] VARCHAR(100));
GO
 
INSERT INTO [#Customer]
      ([CustomerName],[PhoneNumber],[AlternatePhoneNumber])
VALUES
      ('Maria','+92-300074321','+92-300074321'),
      ('Trujillo',null,null),
      ('Antonio','+96-562108460',null),
      ('Thomas',null,'+96-0515552282'),
      ('Christina','+92-059675428','+92-05676753425');
GO

SELECT * FROM [#Customer];
GO
--OUTPUT

Example 1:

IS DISTINCT FROM

Old Approach:(Using Complex WHERE Clause)

Whenever we need to search some records having NULL, the search criteria will look like this.


USE tempdb
GO
  
SELECT [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
FROM   [#Customer]
WHERE
     (([PhoneNumber] <> [AlternatePhoneNumber] 
   OR  [PhoneNumber] IS NULL
   OR  [AlternatePhoneNumber] IS NULL)
   AND NOT ([PhoneNumber] IS NULL
   AND [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT

New Approach:(Using IS DISTINCT FROM)

The complexity and length of where clause has been reduced by the new enhancement which comes with the name IS DISTINCT FROM which takes all unique records of two columns as well as return single null values and drop where both columns are null as shown below:

USE tempdb
GO

SELECT [CustomerID]
     , [CustomerName]
	 , [PhoneNumber]
	 , [AlternatePhoneNumber]
FROM   [#Customer] 
WHERE  [PhoneNumber] IS DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT

Example 2:

IS NOT DISTINCT FROM

Old Approach:(Using Complex WHERE Clause)

In old methodology if we had to take same values from two columns and also where both columns have null values, then the query would be as shown below:

USE tempdb
GO

SELECT [CustomerID]
     , [CustomerName]
	 , [PhoneNumber]
	 , [AlternatePhoneNumber]
FROM   [#Customer]
WHERE 
 (NOT ([PhoneNumber] <> [AlternatePhoneNumber] 
  OR   [PhoneNumber] IS NULL 
  OR   [AlternatePhoneNumber] IS NULL)
  OR  ([PhoneNumber] IS NULL 
  AND  [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT

New Approach:(Using IS NOT DISTINCT FROM)

In new Approach, the complexity has been reduced to just single clause named IS [NOT] DISTINCT FROM & rest all conditions are applied by default as shown in the picture below:

USE tempdb
GO

SELECT [CustomerID]
     , [CustomerName]
	 , [PhoneNumber]
	 , [AlternatePhoneNumber]
FROM   [#Customer] 
WHERE  [PhoneNumber] IS NOT DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT

Example 3:

IS DISTINCT FROM

Old Approach:(Using Complex HAVING Clause)

In old methodology if we had to take unique values using HAVING Clause from two columns and also single null values and drop where both columns are null, then the query would be as shown below:

USE tempdb
GO
  
SELECT [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
FROM   [#Customer]
GROUP BY
       [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
HAVING
     (([PhoneNumber] <> [AlternatePhoneNumber] 
   OR  [PhoneNumber] IS NULL
   OR  [AlternatePhoneNumber] IS NULL)
   AND NOT ([PhoneNumber] IS NULL
   AND [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT

New Approach:(Using IS DISTINCT FROM)

The complexity is being reduced by New Approach where we need to write only one clause and rest is handled by itself as shown in the example below.

USE tempdb
GO
 
SELECT [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber] 
FROM   [#Customer]
GROUP BY
       [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
HAVING
       [PhoneNumber] IS DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT

Example 4:

IS NOT DISTINCT FROM

Old Approach:(Using Complex HAVING Clause):

In old methodology if we had to take same values using HAVING from two columns and also where both columns have null values then the query would be as shown below.


USE tempdb
GO
  
SELECT [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
FROM   [#Customer]
GROUP BY
       [CustomerID]
     , [CustomerName]
     , [PhoneNumber]
     , [AlternatePhoneNumber]
HAVING
(NOT  ([PhoneNumber]  [AlternatePhoneNumber] 
 OR    [PhoneNumber] IS NULL
 OR    [AlternatePhoneNumber] IS NULL)
 OR   ([PhoneNumber] IS NULL
 AND   [AlternatePhoneNumber] IS NULL));
GO
--OUTPUT

New Approach:(Using IS NOT DISTINCT FROM)

The complexity is being reduced by New Approach where we need to write only one clause and rest is handled by itself as shown in the example below.

USE tempdb
GO

SELECT [CustomerID]
     , [CustomerName]
	 , [PhoneNumber]
	 , [AlternatePhoneNumber]
FROM   [#Customer] 
GROUP BY
       [CustomerID]
     , [CustomerName]
	 , [PhoneNumber]
	 , [AlternatePhoneNumber]
HAVING [PhoneNumber] IS DISTINCT FROM [AlternatePhoneNumber];
GO
--OUTPUT

Conclusion:

The IS [NOT] DISTINCT Clause is a life saver where writing a query with multiple conditions just reduced to a single clause. It also reduces the complexity of the query. Do let me know if you used IS [NOT] DISTINCT Clause and found it useful.

Read Full Post »

SQL Server 2022 – TSQL Enhancements – Dynamic Data Masking – Granular permissions

Posted in Features, SQL SERVER, tagged Dynamic Data Masking, EXECUTE AS USER, Granular permissions, SQL, SQL Server on March 24, 2023| Leave a Comment »

In SQL Server 2022, a new enhancement was introduced in dynamic data masking known as Granular Permissions which was a long-awaited enhancement. This new enhancement gives us control to provide unmasking permission at lowest level (table’s column) which we could not achieve in the earlier version of SQL Server. In other words, we can give permissions to unmask columns, tables, schemas & databases to different users based on their accessibility levels.

Let me create a sample to demonstrate the functionality of Dynamic data masking granular permissions in which, I will create a Sample database, two schemas and each schema will have one table as shown below.

Sample:

--Create Sample Database
CREATE DATABASE SampleDB; 
GO

USE SampleDB
GO

--Create HumanResources Schema 
CREATE SCHEMA HumanResources;
GO

--Create Accounts Schema 
CREATE SCHEMA Accounts;
GO

--Create Employee Table
CREATE TABLE HumanResources.Employee
(
 EmployeeID    INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 EmployeeName  VARCHAR(250),
 Birthdate     DATE MASKED WITH (FUNCTION = 'default()') NULL,
 Salary        DECIMAL(10,2) MASKED WITH (FUNCTION = 'default()') NULL 
);
GO

--Create BankAccount Table
CREATE TABLE Accounts.BankAccount
(
 AccountID     INT IDENTITY(1,1) PRIMARY KEY, 
 EmployeeID    INT NOT NULL,
 AccountTitle  VARCHAR(250) MASKED WITH (FUNCTION = 'default()') NULL,
 AccountNumber VARCHAR(250) MASKED WITH (FUNCTION = 'partial(3,"xxxxxxxxx", 4)') NULL
);
GO

--Insert few records into Employee Table
INSERT INTO Humanresources.Employee(EmployeeName,Birthdate,Salary)
VALUES ('Nancy','1988-12-08','200000'),
       ('Andrew','1994-07-16','120000'),
	   ('Janet','1994-07-16','75000');
GO

--Insert few records into BankAccount table
INSERT INTO Accounts.BankAccount(EmployeeID,AccountTitle,AccountNumber)
VALUES(1,'Nancy','9922-0105664197'),
      (2,'Andrew','0010-0107605278'),
	  (3,'Janet','4010-3568743987');
GO

Configuration:

Step 1:

Let’s create few users, which will be used to provide relevant permissions to unmask data as shown below.

USE SampleDB
GO

--Create User HRAdministrator 
CREATE USER HRAdministrator WITHOUT LOGIN; 
GO

--Create User PayrollAdministrator 
CREATE USER PayrollAdministrator WITHOUT LOGIN; 
GO  

--Create User Accountant
CREATE USER Accountant WITHOUT LOGIN;  
GO

--Create User HRManager
CREATE USER HRManager WITHOUT LOGIN;  
GO

Step 2:

Let’s add the db_datareader role in each user created above.

USE SampleDB
GO

--Grant db_datareader role to HRAdministrator
ALTER ROLE db_datareader ADD MEMBER HRAdministrator; 
GO

--Grant db_datareader role to PayrollAdministrator 
ALTER ROLE db_datareader ADD MEMBER PayrollAdministrator ; 
GO

--Grant db_datareader role to Accountant
ALTER ROLE db_datareader ADD MEMBER Accountant; 
GO

--Grant db_datareader role to HRManager
ALTER ROLE db_datareader ADD MEMBER HRManager;
GO

Step 3:

Let’s provide UNMASK permissions to above created users. The details of the access are as shown below:

HRAdministrator : can view Birthdate column Data only in Employee table.
PayrollAdministrator : can view Salary column Data only in Employee table .
Accountant : can view the entire tables data in Account schema only.
HRManager : can view the entire data in the SampleDB database.

USE SampleDB
GO

--Grant Birthday column unmask permission to HRAdministrator;
GRANT UNMASK ON Humanresources.Employee(Birthdate) TO HRAdministrator;
GO

--Grant salary column unmask permission to PayrollAdministrator
GRANT UNMASK ON Humanresources.Employee(Salary) TO PayrollAdministrator;
GO

--Grant Accounts schema unmask permission to Accountant
GRANT UNMASK ON SCHEMA::Accounts TO Accountant;  
GO

--Grant entire database unmask permission to HRManager
GRANT UNMASK TO HRManager;
GO

Testing:

HRAdministrator

Let’s access the Employee table under the context of HRAdministrator user.

USE SampleDB
GO

EXECUTE AS USER='HRAdministrator';  
SELECT EmployeeID
     , EmployeeName 
	 , Birthdate
	 , Salary 
FROM   Humanresources.Employee; 
REVERT;
GO
--OUTPUT

As we can see above, the HRAdministrator can view the Birthdate column data in Employee table but cannot view Salary column data in Employee table.

Let’s access the BankAccount table under the context of HRAdministrator user.

USE SampleDB
GO

EXECUTE AS USER='HRAdministrator';  
SELECT EmployeeID
     , AccountTitle
	 , AccountNumber 
FROM   Accounts.BankAccount;  
REVERT;
GO
--OUTPUT

As we can see above, the HRAdministrator can’t view the data of BankAccount Table since unmask permission is not given.

PayrollAdministrator

Let’s access Employee table under the context of PayrollAdministrator user.

USE SampleDB
GO

EXECUTE AS USER='PayrollAdministrator';  
SELECT EmployeeID
     , EmployeeName
	 , Birthdate
	 , Salary 
FROM   Humanresources.Employee;  
REVERT;
GO
--OUTPUT

As we can see above, the PayrollAdministrator can view the Salary column data in Employee table but cannot view Birthdate column data in Employee table.

Let’s access the BankAccount table under the context of PayrollAdministrator user.

USE SampleDB
GO

EXECUTE AS USER='PayrollAdministrator';  
SELECT EmployeeID
     , AccountTitle
	 , AccountNumber 
FROM   Accounts.BankAccount;
REVERT;
GO
--OUTPUT

As we can see above, the PayrollAdministrator can’t view the data of BankAccount Table since unmask permission is not given.

Let’s access the Employee table under the context of Accountant user.

USE SampleDB
GO

EXECUTE AS USER='Accountant';  
SELECT EmployeeID
     , EmployeeName
	 , Birthdate
	 , Salary 
FROM   Humanresources.Employee;  
REVERT;
GO
--OUTPUT

As we can see above, the Accountant cannot view the Salary, Birthdate column data in Employee table since unmask permission is not given.

Let’s access the BankAccount table which is in Accounts schema under the context of Accountant user.

USE SampleDB
GO

EXECUTE AS USER='Accountant';  
SELECT EmployeeID
     , AccountTitle
	 , AccountNumber 
FROM   Accounts.BankAccount;
REVERT;
GO
--OUTPUT

As we can see above, the Accountant can view the data of BankAccount Table.

Let’s access the Employee table which is in HumanResources schema under the context of HRManager user.

USE SampleDB
GO

EXECUTE AS USER='HRManager';  
SELECT EmployeeID
     , EmployeeName
	 , Birthdate
	 , Salary 
FROM   Humanresources.Employee;  
REVERT;
GO
--OUTPUT

As we can see above, the HRManager can view the data of Employee Table.

Let’s access the BankAccount table which is in Accounts schema under the context of HRManager user.

USE SampleDB
GO

EXECUTE AS USER='HRManager';  
SELECT EmployeeID
     , AccountTitle
	 , AccountNumber 
FROM   Accounts.BankAccount; 
REVERT;
GO
--OUTPUT

As we can see above, the HRManager can view the data of BankAccount Table.

Conclusion:

Granular Permission in dynamic data masking is a very handy enhancement, it gives us the control to unmask column, table, schema, database data to different users. Do let me know if you use it and found it helpful.

Read Full Post »

SQL SERVER 2012 – How to add multiple files in a single filestream filegroup in the existing database.

Posted in Features, Solutions, SQL SERVER, tagged multiple files in a single filestream filegroup, raresql, SQL, SQL Server, SQL Server 2012 on July 28, 2013| Leave a Comment »

Adding multiple files in a single filestream filegroup was a very nice & helpful feature shipped with SQL Server 2012. Now, you can easily organize your data under multiple files in the same filestream filegroup. Also, it is not necessary that you place all of them in the same location. You can place them in multiple locations to divide the load as well.

Let me create a sample database to demonstrate. Given below is the script.
Note : Before executing the below script, kindly create a Data folder in C: drive.

USE [master]
GO
CREATE DATABASE [Sample_DB]
CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'Sample_DB',
FILENAME = N'C:\Data\Sample_DB.mdf',
SIZE = 500MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10MB
),
FILEGROUP [FS] CONTAINS FILESTREAM DEFAULT
(
NAME = N'Filestream1',
FILENAME = N'C:\Data\FileStream1',
MAXSIZE = UNLIMITED
)
LOG ON
(NAME = N'Sample_DB_log'
,FILENAME = N'C:\Data\Sample_DB_log.ldf',
SIZE = 500MB,
MAXSIZE = 1GB,
FILEGROWTH = 10MB
)
GO

Let me add an additional file to the same filegroup. Given below is the script.

ALTER DATABASE [Sample_DB]
ADD FILE
(
NAME = N'Filestream2',
FILENAME = N'C:\Data\Filestream2',
MAXSIZE = 100MB
)TO FILEGROUP [FS]
GO

Lets check if the file has been created or not. Given below is the script.

Select * from sys.master_files where database_id =db_id('Sample_DB')
GO

Let me know if you have done this in real world and what was the solution.

Read Full Post »

SQL SERVER 2012 – Semantic Search – Implementation– Part-2

Posted in Features, SQL SERVER, tagged raresql, SEMANTICKEYPHRASETABLE, SQL, SQL Server, SQL Server 2012, SQL SERVER 2012 – Semantic Search – Implementation– Part-2 on May 6, 2013| 5 Comments »

In Part 1 of Semantic Search Implementation that I published yesterday, I wrote about how to install and configure semantic search in SQL Server 2012. Today in Part 2, we will learn how to implement semantic search and will demonstrate how beneficial it is.

Prerequisite :

Given below are the two articles that you should read to have clear understanding about Semantic Search Implementation.

Lets Implement it step by step.

Step 1: Create database

USE master
GO
CREATE DATABASE [SampleDB]
ON PRIMARY
( NAME = N'SampleDB',
FILENAME = N'C:\DATA\SampleDB.mdf'
),
FILEGROUP SampleFileGroup CONTAINS FILESTREAM
(NAME = SampleFileTableFS,
FILENAME='C:\Data\SampleFileTable'
)
LOG ON
( NAME = N'SampleDB_log',
FILENAME = N'C:\DATA\SampleDB_log.ldf'
)
WITH FILESTREAM
( NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N'SampleFileTableDB'
)
GO

Step 2: Create FileTable

USE SampleDB
GO
CREATE TABLE CVBank AS FileTable
WITH
(
FileTable_Directory = 'CVFileTable_Dir'
);
GO

Step 3: Populate data into FileTable
In this step, we will insert few Cvs in the file table.

USE SampleDB
GO
INSERT INTO [dbo].[CVBank] ([name],[file_stream])
SELECT'Muhammad Imran -CV.doc',
* FROM OPENROWSET
(
BULK N'C:\Cv\Muhammad Imran -CV.doc', SINGLE_BLOB
) AS FileData
GO

INSERT INTO [dbo].[CVBank] ([name],[file_stream])
SELECT 'Salman Tahir -CV.doc',
* FROM OPENROWSET
(
BULK N'C:\Cv\Salman Tahir -CV.doc', SINGLE_BLOB
) AS FileData
GO

INSERT INTO [dbo].[CVBank] ([name],[file_stream])
SELECT 'Syed Kamran Ali -CV.doc',
* FROM OPENROWSET
(
BULK N'C:\Cv\Syed Kamran Ali -CV.doc', SINGLE_BLOB
) AS FileData
GO

Step 4: Create full text search on FileTable

Right click on the file table point to full-Text index then select Define Full-Text Index… Further details to create Full Text Index is available here

Now, we are prepared to use semantic search. Lets create few examples.

Example 1: Search Sharepoint Developer CVs
As I mentioned above that, I populated some CVs in the file table, now it is time to search Cvs with certain criteria using semantic search.
In this example, I will search candidates having knowledge of sharepoint.

USE SampleDB
GO
SELECT
D.name
,KEYP_TBL.score
,KEYP_TBL.Keyphrase
FROM dbo.CVBank D
INNER JOIN SEMANTICKEYPHRASETABLE
(
dbo.CVBank,(name,file_stream)
) AS KEYP_TBL
ON D.path_locator = KEYP_TBL.document_key
WHERE KEYP_TBL.keyphrase like '%sharepoint%'
ORDER BY KEYP_TBL.score DESC
--OUTPUT

Example 2: Search SQL Developer CVs
In this example, I will search candidates having knowledge of SQL.

USE SampleDB
GO
SELECT
D.name
,KEYP_TBL.score
,KEYP_TBL.Keyphrase
FROM dbo.CVBank D
INNER JOIN SEMANTICKEYPHRASETABLE
(
dbo.CVBank,(name,file_stream)
) AS KEYP_TBL
ON D.path_locator = KEYP_TBL.document_key
WHERE KEYP_TBL.keyphrase like '%sql%'
ORDER BY KEYP_TBL.score DESC
--OUTPUT

Conclusion :
Semantic Search is very handy if you need to develop some solution on the basis of unstructured document. Shall post another nice feature of semantic search in my upcoming post.

Read Full Post »

SQL SERVER 2012 – Semantic Search – Install and configure – Part-1

Posted in Features, SQL SERVER, tagged raresql, Semantic Search, sp_fulltext_semantic_register_language_statistics_db, SQL, SQL Server, SQL Server 2012, SQL SERVER 2012 - Semantic Search - Install and configure - Part-1, sys.fulltext_semantic_language_statistics_database on May 5, 2013| 1 Comment »

In the earlier versions of SQL Server, it was very difficult to view the unstructured documents placed in the SQL Server. But in SQL Server 2012, with the solution shipped namely Semantic Search not only can you easily look inside in the unstructured documents but also you can create phrase table to find key phrases, similar documents, related documents as well. This feature gave boost to another newly introduced feature namely File Table in SQL Server 2012. You must install and configure Semantic Search before you use it.
Let’s install / configure it Step by Step.

Step 1:
In order to install Semantic Search, first of all you need to check whether FULL TEXT SEARCH feature is installed in your SQL Server or not. Because Semantic search feature is based on Full text search feature.
Given below is the script to check this.

USE master
GO
SELECT SERVERPROPERTY('IsFullTextInstalled') AS [Result]
GO
--OUTPUT

Result
1

(1 row(s) affected)

Step 2:
If result of step 1 is 1 then you should not do anything in Step 2. But if the result is 0, you must run the SQL Server setup again, go to Feature Selection option, select Full-Text and Semantic Extractions for search and install this feature (as shown in the picture below).

Step 3:
Once you install the Full-Text and Semantic Extractions for search feature, you need to check whether Semantic Language Statistics Database (One of the core dependency of Semantic searches) is installed or not. Given below is the script to check.

USE master
GO
SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Ooooppss……… as you can see, the output says blank; it means semantic language statistics database is not yet installed. So you need to install it.

Step 4:

To install semantic language statistics database, you need to go to SQL Server installation media and browse the given below folder and run SemanticLanguageDatabase.msi

• For x86 : …\x86\Setup\SemanticLanguageDatabase.msi
• For x64 : …\x64\Setup\SemanticLanguageDatabase.msi

Given below is the screen image.

Step 5:

Once you execute the above SemanticLanguageDatabase.msi and install it properly, it will give you two database files (semanticsDB, semanticsdb_log) in a location (C:\Program Files\Microsoft Semantic Language Database), in case you install it in the default location. Now you have database files, just attach these files in your SQL Server.

Step 6:
Once you attach these database files in your SQL Server, then you have to register semantic language database. Given below is the script.

EXEC sp_fulltext_semantic_register_language_statistics_db
@dbname = N'semanticsdb'
GO

Step 7:
Again, you need to check whether the semantic language statistics database is installed or not. Given below is the script.

SELECT * FROM
sys.fulltext_semantic_language_statistics_database
GO
--OUTPUT

Now, you can see that the semantic language statistics database is installed.

In my next article, I will discuss how to implement semantic search on file tables and make your life easier.

Reference : MSDN

Read Full Post »

SQL SERVER 2012 – Deprecated Features – Ability to return result sets from triggers

Posted in Features, SQL SERVER, Triggers, tagged disallow results from triggers, raresql, SQL, SQL Server, SQL SERVER 2012 - Deprecated Features - Ability to return result sets from triggers on April 8, 2013| 2 Comments »

Ability to return result sets from triggers is one of the features that is marked as a deprecated feature in SQL Server 2012.
After reading this, few questions flashed my mind, that I would like to share with you.

How do we return the result sets from triggers?
Why do we need this feature mostly?
How can we stop using this feature from earlier versions?
How can we get the same functionality without using this feature?

I added first two questions for the beginners to understand this concept.

Lets proceed step by step from the first question.

How do we return the result sets from triggers ?

Step 1 :
Lets create a table to demonstrate the use of return the result sets from trigger.

USE tempdb;
GO
CREATE TABLE tbl_Employee(
EmployeeID int identity (1,1),
EmployeeName nvarchar(100) NULL
)
GO

Step 2 :
Create a trigger on this table (tbl_Employee).

CREATE TRIGGER trg_Insert ON tbl_Employee
FOR INSERT
AS
SELECT INSERTED.EmployeeID, INSERTED.EmployeeName FROM inserted;
GO

Step 3 :
Now, lets insert one record and check if it is working properly.

INSERT INTO tbl_Employee VALUES('Imran')
GO
--OUTPUT

Woww, it is working, we got the result sets from the trigger 😉

Why do we need this feature mostly?

Mostly, we use select statement inside the trigger/ stored procedure to return the result set and the purpose of the result set is to debug (to check what values it returns) the trigger / stored procedure.
(Note : It is a common practice to debug any trigger/stored procedure via result set, but it may break your application and also it is not recommended).

How can we stop using this feature from earlier versions?

Step 4 :
We are enabling an advance feature ‘disallow results from triggers’ to 1 to enable this feature in the earlier version of SQL Server and it is also recommended by SQL Server to make it 1.

sp_configure 'disallow results from triggers',1
GO
RECONFIGURE

Step 5 :
Insert one more record in the table after enabling the feature and check that this feature has enabled properly or not.

INSERT INTO tbl_Employee VALUES('Bob')
GO
--OUTPUT

Ooopps…… I am unable to execute it, because I enabled the return result set feature from trigger at instance level not on database level
and it is working fine.
Msg 524, Level 16, State 1, Procedure trg_Insert, Line 4
A trigger returned a resultset and the server option ‘disallow_results_from_triggers’ is true.

How can we get the same functionality without using this feature?

Step 6 :
You can easily use the same functionality without using this feature. Let me alter the trigger and make a sample.

ALTER TRIGGER trg_Insert ON tbl_Employee
FOR INSERT
AS

Declare @EmployeeID int
Declare @EmployeeName nvarchar(100)

SELECT @EmployeeID=INSERTED.EmployeeID
, @EmployeeName=INSERTED.EmployeeName FROM inserted;

Print 'EmployeeID ' + Convert (varchar(10),@EmployeeID)
Print 'Employee Name ' + @EmployeeName
GO

Step 7 :
Insert one more record in the table and check the functionality.

INSERT INTO tbl_Employee VALUES('Sandra')
GO
--OUTPUT

EmployeeID 3
Employee Name Sandra

Conclusion :
DO NOT use SELECT statement to return the result set from a stored procedure / trigger. Always use print command instead, to debug it.

Reference : MSDN

Read Full Post »

SQL SERVER 2012 – Discontinued Features – DATABASEPROPERTY

Posted in Features, SQL SERVER, tagged DATABASEPROPERTY vs SQL SERVER 2012 - Discontinued Features - DATABASEPROPERTYEX, raresql, SQL, SQL Server, SQL SERVER 2012 - Discontinued Features - DATABASEPROPERTY on March 13, 2013| Leave a Comment »

DATABASEPROPERTY (metadata function) is being discontinued since the emerge of SQL Server 2012 and it is replaced by another metadata function namely DATABASEPROPERTYEX. Both functions take parameters (database name, property name) and return property value on the basis of input parameters. But the major difference between both functions is the return type. DATABASEPROPERTY returns only int datatype while DATABASEPROPERTYEX returns the sql_variant datatype. In simple words, you cannot expect any other data type than int from DATABASEPROPERTY.
I did some research and found some additional difference that I would like to share.

Given below are the scripts, that is written for DATABASEPROPERTYEX & DATABASEPROPERTY to compare both meta data functions.

--Code for DATABASEPROPERTYEX
SELECT DATABASEPROPERTYEX('AdventureWorks2012','Collation')
AS [Collation],
DATABASEPROPERTYEX('AdventureWorks2012','ComparisonStyle')
AS [ComparisonStyle],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiNullDefault')
AS [IsAnsiNullDefault],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiNullsEnabled')
AS [IsAnsiNullsEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiPaddingEnabled')
AS [IsAnsiPaddingEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAnsiWarningsEnabled')
AS [IsAnsiWarningsEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsArithmeticAbortEnabled')
AS [IsArithmeticAbortEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoClose')
AS [IsAutoClose],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoCreateStatistics')
AS [IsAutoCreateStatistics],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoShrink')
AS [IsAutoShrink],
DATABASEPROPERTYEX('AdventureWorks2012','IsAutoUpdateStatistics')
AS [IsAutoUpdateStatistics],
DATABASEPROPERTYEX('AdventureWorks2012','IsCloseCursorsOnCommitEnabled')
AS [IsCloseCursorsOnCommitEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsFulltextEnabled')
AS [IsFulltextEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsInStandBy')
AS [IsInStandBy],
DATABASEPROPERTYEX('AdventureWorks2012','IsLocalCursorsDefault')
AS [IsLocalCursorsDefault],
DATABASEPROPERTYEX('AdventureWorks2012','IsMergePublished')
AS [IsMergePublished],
DATABASEPROPERTYEX('AdventureWorks2012','IsNullConcat')
AS [IsNullConcat],
DATABASEPROPERTYEX('AdventureWorks2012','IsNumericRoundAbortEnabled')
AS [IsNumericRoundAbortEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsParameterizationForced')
AS [IsParameterizationForced],
DATABASEPROPERTYEX('AdventureWorks2012','IsQuotedIdentifiersEnabled')
AS [IsQuotedIdentifiersEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsPublished')
AS [IsPublished],
DATABASEPROPERTYEX('AdventureWorks2012','IsRecursiveTriggersEnabled')
AS [IsRecursiveTriggersEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','IsSubscribed')
AS [IsSubscribed],
DATABASEPROPERTYEX('AdventureWorks2012','IsSyncWithBackup')
AS [IsSyncWithBackup],
DATABASEPROPERTYEX('AdventureWorks2012','IsTornPageDetectionEnabled')
AS [IsTornPageDetectionEnabled],
DATABASEPROPERTYEX('AdventureWorks2012','LCID')
AS [LCID],
DATABASEPROPERTYEX('AdventureWorks2012','Recovery')
AS [Recovery],
DATABASEPROPERTYEX('AdventureWorks2012','SQLSortOrder')
AS [SQLSortOrder],
DATABASEPROPERTYEX('AdventureWorks2012','Status')
AS [Status],
DATABASEPROPERTYEX('AdventureWorks2012','Updateability')
AS [Updateability],
DATABASEPROPERTYEX('AdventureWorks2012','UserAccess')
AS [UserAccess],
DATABASEPROPERTYEX('AdventureWorks2012','Version')
AS [Version]

--Code for DATABASEPROPERTY
SELECT DATABASEPROPERTY('AdventureWorks2012','Collation')
AS [Collation],
DATABASEPROPERTY('AdventureWorks2012','ComparisonStyle')
AS [ComparisonStyle],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiNullDefault')
AS [IsAnsiNullDefault],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiNullsEnabled')
AS [IsAnsiNullsEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiPaddingEnabled')
AS [IsAnsiPaddingEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAnsiWarningsEnabled')
AS [IsAnsiWarningsEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsArithmeticAbortEnabled')
AS [IsArithmeticAbortEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsAutoClose')
AS [IsAutoClose],
DATABASEPROPERTY('AdventureWorks2012','IsAutoCreateStatistics')
AS [IsAutoCreateStatistics],
DATABASEPROPERTY('AdventureWorks2012','IsAutoShrink')
AS [IsAutoShrink],
DATABASEPROPERTY('AdventureWorks2012','IsAutoUpdateStatistics')
AS [[IsAutoUpdateStatistics]],
DATABASEPROPERTY('AdventureWorks2012','IsCloseCursorsOnCommitEnabled')
AS [IsCloseCursorsOnCommitEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsFulltextEnabled')
AS [IsFulltextEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsInStandBy')
AS [IsInStandBy],
DATABASEPROPERTY('AdventureWorks2012','IsLocalCursorsDefault')
AS [IsLocalCursorsDefault],
DATABASEPROPERTY('AdventureWorks2012','IsMergePublished')
AS [IsMergePublished],
DATABASEPROPERTY('AdventureWorks2012','IsNullConcat')
AS [IsNullConcat],
DATABASEPROPERTY('AdventureWorks2012','IsNumericRoundAbortEnabled')
AS [IsNumericRoundAbortEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsParameterizationForced')
AS [IsParameterizationForced],
DATABASEPROPERTY('AdventureWorks2012','IsQuotedIdentifiersEnabled')
AS [IsQuotedIdentifiersEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsPublished')
AS [IsPublished],
DATABASEPROPERTY('AdventureWorks2012','IsRecursiveTriggersEnabled')
AS [IsRecursiveTriggersEnabled],
DATABASEPROPERTY('AdventureWorks2012','IsSubscribed')
AS [IsSubscribed],
DATABASEPROPERTY('AdventureWorks2012','IsSyncWithBackup')
AS [IsSyncWithBackup],
DATABASEPROPERTY('AdventureWorks2012','IsTornPageDetectionEnabled')
AS [IsTornPageDetectionEnabled],
DATABASEPROPERTY('AdventureWorks2012','LCID')
AS [LCID],
DATABASEPROPERTY('AdventureWorks2012','Recovery')
AS [Recovery],
DATABASEPROPERTY('AdventureWorks2012','SQLSortOrder')
AS [SQLSortOrder],
DATABASEPROPERTY('AdventureWorks2012','Status')
AS [Status],
DATABASEPROPERTY('AdventureWorks2012','Updateability')
AS [Updateability],
DATABASEPROPERTY('AdventureWorks2012','UserAccess')
AS [UserAccess],
DATABASEPROPERTY('AdventureWorks2012','Version')
AS [Version]

Given below is the output of both meta data functions and you can see that DATABASEPROPERTY will reveal the correct result whenever the return type of property value is int but on the other hand DATABASEPROPERTYEX will give you the result of any property value because its return type is sql_variant.

Conclusion:
DATABASEPROPERTYEX is one of the handy metadata functions when we need to retrieve the property value on the basis of property and database name.
It also provides an additional info than DATABASEPROPERTY. Remember, DATABASEPROPERTY is discontinued in SQL Server 2012, so don’t use it in further developments. Also if you upgrade SQL Server earlier version to SQL Server 2012, change DATABASEPROPERTY to DATABASEPROPERTYEX in the script.

MSDN : Reference

Read Full Post »

SQL Server 2012–Discontinued Features-CREATE TRIGGER-WITH APPEND clause

Posted in Features, SQL SERVER, tagged raresql, SQL, SQL Server, SQL Server 2012–Discontinued Features-CREATE TRIGGER-WITH APPEND clause on March 3, 2013| Leave a Comment »

Whenever you upgrade previous version of SQL Server to SQL Server 2012 you need to take care of few issues. In this article, I will discuss one of the important issues that is Create trigger with append clause. This clause will not stop you to upgrade it to SQL Server 2012 but it will break your scripts. But how ?
Let me explain it Step by Step with the simple examples.
Step 1 :
First create given below table and triggers on any database prior to SQL Server 2012.

CREATE TABLE dbo.Student
([Student_ID] INT,
[Student_Name] VARCHAR(100)
)
GO
CREATE TRIGGER dbo.FirstTrigger
ON dbo.Student
FOR INSERT
AS
BEGIN
PRINT 'First Trigger'
END
GO
CREATE TRIGGER dbo.SecondTrigger
ON dbo.Student
FOR INSERT
WITH APPEND
AS
BEGIN
PRINT 'Second Trigger'
END
GO

Step 2 :
Upgrade the database on SQL Server 2012. Once you upgrade it will not alert you.
Step 3 :
Immediately after upgrade, run the given below query to view if any with append clause is available in the trigger.

Select A.object_id,A.definition from sys.sql_modules A
Inner Join sys.triggers B On A.object_id =B.object_id
where definition like '%with append%'

Step 4 :
In the above example, we have one trigger with append clause, so alter the trigger and remove the with append clause because it is no more required for the triggers in latest versions of SQL Server.

Now, you are ready to use this trigger in the SQL Server 2012.

In case, if you don’t follow step 3 and 4, lets see what may happen.

insert into student values (1,'Imran')
--OUTPUT

First Trigger
Msg 195, Level 15, State 1, Procedure SecondTrigger, Line 5
‘APPEND’ is not a recognized option.

Conclusion :
Remember, whenever you upgrade to SQL Server 2012, check WITH APPEND clause in the triggers to avoid this error.

Read Full Post »

Difference between Identity and Sequence in SQL Server 2012

Posted in Features, SQL SERVER, tagged Comparison between Identity and Sequence, Comparison between SEQUENCE and IDENTITY in SQL Server 2012, Difference between SEQUENCE and IDENTITY in SQL Server 2012, SEQUENCE Vs IDENTITY in SQL Server 2012 on May 1, 2012| 7 Comments »

In my last post, I had written about the new feature SEQUENCE in SQL SERVER 2012. But in this post, I would like to write the difference between SEQUENCE and IDENTITY in SQL Server 2012 with examples. I hope this example will make these two concepts more clear.
Given below are the differences along with the sample source code:

S.No	Identity	Sequence
1	Dependant on table.	Independent from table.
2	Identity is a property in a table. Example : CREATE TABLE Table test_Identity ( [ID] int Identity (1,1), [Product Name] varchar(50) )	Sequence is an object. Example : CREATE SEQUENCE [dbo].[Sequence_ID] AS [int] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000 NO CYCLE NO CACHE
3	If you need a new ID from an identity column you need to insert and then get new ID. Example : Insert into [test_Identity] Values (‘SQL Server’) GO SELECT @@IDENTITY AS ‘Identity’ –OR Select SCOPE_IDENTITY() AS ‘Identity’	In the sequence, you do not need to insert new ID, you can view the new ID directly. Example : SELECT NEXT VALUE FOR dbo.[Sequence_ID]
4	You cannot perform a cycle in identity column. Meaning, you cannot restart the counter after a particular interval.	In the sequence, you can simply add one property to make it a cycle. Example : ALTER SEQUENCE [dbo].[Sequence_ID] CYCLE;
5	You cannot cache Identity column property.	Sequence can be easily cached by just setting cache property of sequence. It also improves the performance. Example : ALTER SEQUENCE [dbo].[Sequence_ID] CACHE 3;
6	You cannot remove the identity column from the table directly.	The sequence is not table dependent so you can easily remove it Example : Create table dbo.[test_Sequence] ( [ID] int, [Product Name] varchar(50) ) GO –First Insert With Sequence object INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (NEXT VALUE FOR [Ticket] , ‘MICROSOFT SQL SERVER 2008’) GO –Second Insert without Sequence INSERT INTO dbo.test_Sequence ([ID],[Product Name]) VALUES (2 , ‘MICROSOFT SQL SERVER 2012’)
7	You cannot define the maximum value in identity column it is based on the data type limit.	Here you can set up its maximum value. Example : ALTER SEQUENCE [dbo].[Sequence_ID] MAXVALUE 2000;
8	You can reseed it but cannot change the step size. Example : DBCC CHECKIDENT (test_Identity, RESEED, 4)	You can reseed as well as change the step size. Example : ALTER SEQUENCE [dbo].[Sequence_ID] RESTART WITH 7 INCREMENT BY 2;
9	You cannot generate range from identity.	You can generate a range of sequence values from a sequence object with the help of sp_sequence_get_range.

Read Full Post »

SQL Server Portal

Real world SQL scenarios & its unique & optimized solutions

Archive for the ‘Features’ Category

SQL Server 2022 – TSQL – IS [NOT] DISTINCT FROM

SQL Server 2022 – TSQL Enhancements – Dynamic Data Masking – Granular permissions

SQL SERVER 2012 – How to add multiple files in a single filestream filegroup in the existing database.

SQL SERVER 2012 – Semantic Search – Implementation– Part-2

SQL SERVER 2012 – Semantic Search – Install and configure – Part-1

SQL SERVER 2012 – Deprecated Features – Ability to return result sets from triggers

SQL SERVER 2012 – Discontinued Features – DATABASEPROPERTY

SQL Server 2012–Discontinued Features-CREATE TRIGGER-WITH APPEND clause

Difference between Identity and Sequence in SQL Server 2012

Recent Posts

Archives

Categories

Blog Stats

Follow Blog via Email

Find us on Facebook

Disclaimer