Feeds:
Posts
Comments

Archive for April, 2013

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

Posted in Features, SQL SERVER, Triggers, tagged , , , , 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

triggerresultset1.1

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 – Finding Size of a Columnstore Index on disk

Posted in Solutions, SQL SERVER, tagged , , , , on April 6, 2013| Leave a Comment »

Few days ago, I was working on columnstore Index and I came across a problem finding the size of all columnstore Indexes on disk in a particular database.
I have given below the script which can help you find the size of a single columnstore index on disk or all columnstore indexes in a particular database by using
sys.column_store_segments, sys.column_store_dictionaries.

Use AdventureWorks2012
Go
Declare @TableName as nvarchar(Max)
--If you need columnstore index size for one table
--Change the @TableName parameter from NULL to table name
Set @TableName =NULL --'SalesOrderDetail_Sample'

;With CTE AS (
SELECT i.object_id
,i.name as [Index_Name]
,SUM(c.on_disk_size)/(1024.0*1024)
As [Columstore_Index_size_on_disk_size (In MBs)]
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON i.object_id = p.object_id
JOIN sys.column_store_segments AS c
ON c.hobt_id = p.hobt_id
WHERE i.type_desc = 'NONCLUSTERED COLUMNSTORE'
AND ((1=(CASE WHEN @TableName is Null THEN 1 ELSE 0 END)
OR i.object_id = object_id(@TableName)))
GROUP BY i.object_id,i.name

UNION ALL

SELECT i.object_id
,i.name as [Index_Name]
,SUM(c.on_disk_size)/(1024.0*1024)
As [Columstore_Index_size_on_disk_size (In MBs)]
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON i.object_id = p.object_id
JOIN sys.column_store_dictionaries AS c
ON c.hobt_id = p.hobt_id
WHERE i.type_desc = 'NONCLUSTERED COLUMNSTORE'
AND ((1=(CASE WHEN @TableName is Null THEN 1 ELSE 0 END)
OR i.object_id = object_id(@TableName)))
GROUP BY i.object_id,i.name )

Select object_id,object_name(object_id) as [Table_Name]
,[Index_Name]
,SUM([Columstore_Index_size_on_disk_size (In MBs)])
AS [Columstore_Index_size_on_disk_size (In MBs)] from CTE
Group By object_id,[Index_Name]

Columnstoreindexsize1.1

Reference : MSDN

Read Full Post »

SQL SERVER 2012 – Fix – Error – 11728 – The sequence object has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Posted in Errors, SQL SERVER, tagged , , , , , on April 5, 2013| 2 Comments »

The sequence object ‘%.*ls’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated is one of the new error messages came in SQL Server 2012. This error message is related to Sequence object, a new database feature shipped with SQL Server 2012.

Let’s discuss this in detail:
Message Number: 11728

Severity : 16

Error Message: The sequence object ‘%.*ls’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Ticket]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2
NO CACHE
GO
Select NEXT VALUE FOR [Ticket]
GO 3

Msg 11728, Level 16, State 1, Line 2
The sequence object ‘Ticket’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Ooopps…… I am unable to execute it.

Resolution:
There are three different methods to resolve this error.

Method 1:
In method 1, you need to RESTART the sequence and it will be resolved but temporarily. Once it reaches to its maximum, it will give you the same error again.

ALTER SEQUENCE [dbo].[Ticket]
RESTART WITH 1
GO

Method 2:
In method 2, you need to INCREASE the sequence object  maximum number (If required by business logic) but again, it will be resolved but temporarily because once it reaches to its maximum, it will give you the same error again.

ALTER SEQUENCE [dbo].[Ticket]
MAXVALUE 10000
GO

Method 3:
In method 3, you need to CYCLE the sequence object (If required by business logic) and it will never give you this error message. But again it depends on your business logic because if you follow method 3 it will duplicate your sequence numbers.

ALTER SEQUENCE [dbo].[Ticket]
CYCLE
GO

Conclusion :
You can fix this error message by using above three different methods but before selecting any method be certain of your business logic.

Read Full Post »

SQL SERVER 2012 – How to find list of contained databases

Posted in Solutions, SQL SERVER, tagged , , , on April 4, 2013| Leave a Comment »

Contained databases are one of the favorite features introduced in SQL Server 2012. Contained databases are isolated / independent databases from the other databases and from the instance it is hosted on.

Today, we will go through a very simple script related to contained databases and in this script we need to find the list of all contained databases only. Given below is the script.

Select name as [Database Name], containment ,containment_desc
from sys.databases
Where [containment] =1
--OUTPUT

listcontained DB1.2

Read Full Post »

SQL SERVER 2012 – Get Day, Month and Year separately from Date Time Using Format Function

Posted in Solutions, SQL SERVER, tagged , , , on April 3, 2013| 2 Comments »

How to get day, month and year from Date Time? I came across this question many times in multiple blogs and there are many ways to do it in the earlier versions of SQL Server. In SQL Server 2012, you can also do it using FORMAT function (Method 3).
Given below are different methods to get day, month and year from date time which are compatible with different versions, including SQL Server 2012. Ensure that you are using correct method in your SQL Server version.

Method 1:

--This method will work on SQL SERVER 2005 and above
DECLARE @Date_Time DATETIME
SET @Date_Time = '2013-12-31 10:49:59.460'

SELECT DATEPART(DAY, @Date_Time) as [Day],
DATEPART(MONTH, @Date_Time) as [Month],
DATEPART(YEAR, @Date_Time) as [Year]
--OUTPUT

getdaymonthyear
Method 2:

--This method will work on SQL SERVER 2005 and above
DECLARE @Date_Time DATETIME
SET @Date_Time = '2013-12-31 10:49:59.460'
SELECT DAY(@Date_Time) as [Day],
MONTH(@Date_Time) as [Month],
YEAR(@Date_Time) as [Year]
--OUTPUT

getdaymonthyear1.1
Method 3:

--This method will work on SQL SERVER 2012 and above
DECLARE @Date_Time DATETIME
SET @Date_Time = '2013-12-31 10:49:59.460'
SELECT FORMAT(@Date_Time,'dd') as [Day],
FORMAT(@Date_Time,'MM') as [Month],
FORMAT(@Date_Time,'yyyy') as [Year]
--OUTPUT

getdaymonthyear1.2

Read Full Post »

SQL SERVER – How to convert Hashbytes to varchar

Posted in Solutions, tagged , , , on April 2, 2013| 3 Comments »

How to convert Hasbytes to varchar ? I came across this question recently when I was working on my previous article. Then I started with my research to develop a solution.

Given below is the script that can convert Hashbytes to Varchar :

DECLARE @varchar varchar(Max);
DECLARE @hashbytes varbinary(20)

-- Convert 'raresql' string into Hasbytes
SET @hashbytes=HASHBYTES('SHA1','raresql');
-- Select Hasbytes value
Select @hashbytes as Hasbytes

--Convert varbinary value to varchar value
Set @varchar='0x' + cast('' as xml).value
('xs:hexBinary(sql:variable("@hashbytes"))', 'varchar(max)');

--Select varchar value
Select @varchar as [Varchar]

hasbytestovarchar1.1

Read Full Post »

SQL SERVER – Cryptographic Functions – HASHBYTES – Enhancement

Posted in Functions, SQL SERVER, tagged , , , , on April 1, 2013| 1 Comment »

HASHBYTES is one of the useful functions when it comes to generate hash values on the basis of different types of algorithms. In the earlier versions, it supports MD2, MD4, MD5, SHA, SHA1 algorithms and these algorithms are limited up to 20 bytes only.
In SQL Server 2012, we have an enhancement in this function and now it supports SHA2_256, SHA2_512 algorithms that can generate 32 and 64 bytes hash codes for the respective input.
Let me explain this enhancement with simple example :

DECLARE @String varchar(7);
Set @String ='raresql'

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD2' AS [Algorithm type]
, HASHBYTES('MD2', @String) as [HashBytes]
, LEN(HASHBYTES('MD2', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD4' AS [Algorithm type]
, HASHBYTES('MD4', @String) as [HashBytes]
, LEN(HASHBYTES('MD4', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'MD5' AS [Algorithm type]
, HASHBYTES('MD5', @String) as [HashBytes]
, LEN(HASHBYTES('MD5', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'SHA' AS [Algorithm type]
, HASHBYTES('SHA', @String) as [HashBytes]
, LEN(HASHBYTES('SHA', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2005 and above
SELECT 'SHA1' AS [Algorithm type]
, HASHBYTES('SHA1', @String) as [HashBytes]
, LEN(HASHBYTES('SHA1', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2012 and above
SELECT 'SHA2_256' AS [Algorithm type]
, HASHBYTES('SHA2_256', @String) as [HashBytes]
, LEN(HASHBYTES('SHA2_256', @String)) as [Length in Bytes]
UNION ALL

--This [Algorithm type] will work in SQL Server 2012 and above
SELECT 'SHA2_512' AS [Algorithm type]
, HASHBYTES('SHA2_512', @String) as [HashBytes]
, LEN(HASHBYTES('SHA2_512', @String)) as [Length in Bytes]
GO
--OUTPUT

hashbytes 1.1

Note : If you execute above script in earlier version of SQL Server, it will return NULL value for SHA2_256 & SHA2_512 whereas it generates the hashbytes value for all other algorithms.

Reference : MSDN

Read Full Post »

« Newer Posts