SQL Server Portal

SQL SERVER 2012 – How to get a value from previous result row effectively

September 11, 2013 by Muhammad Imran

I came across this query many times and most often we need the previous row result in the current row for comparison purposes. Fortunately, we do have a solution for this problem using self join. But I will share another efficient solution with you using LAG (an analytic function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
[ID] int,
[Levels] varchar(50)
)
GO

INSERT INTO tbl_sample VALUES (1,'LEVEL 1')
INSERT INTO tbl_sample VALUES (2,'LEVEL 2')
INSERT INTO tbl_sample VALUES (3,'LEVEL 3')
INSERT INTO tbl_sample VALUES (4,'LEVEL 4')
INSERT INTO tbl_sample VALUES (5,'LEVEL 5')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

Let me show you both old and new approaches.

Old Approaches :
Given below is the old approach that we generally develop using self join. This approach you can use in any version of SQL Server.

USE tempdb
GO
SELECT
A.ID
,A.Levels As [Current Level]
,B.Levels AS [Previous Level]
FROM tbl_sample A
LEFT JOIN tbl_sample B ON A.ID=B.ID+1
ORDER BY A.ID
GO
--OUTPUT

New Approaches :
In this approach, you do not need to do self join and make it complicated. You just need to use LAG function and it will calculate the previous result row for you automatically. This approach can be used in SQL Server 2012 and above.

USE tempdb
GO
SELECT
A.ID
,A.Levels As [Current Level]
,LAG(A.levels,1,0) OVER (ORDER BY A.ID) AS [Previous Level]
FROM tbl_sample A
GO
--OUTPUT

Conclusion:
In the above approaches, you can see that the result set are same but the new approaches reduce the complexity and increase the performance.

Posted in Solutions, SQL SERVER | Tagged How to get a value from previous result row effectively, raresql, SQL, SQL Server, SQL Server 2012 | 1 Comment »

SQL SERVER HEKATON – Simple way to count number of records in memory optimized tables

September 10, 2013 by Muhammad Imran

Today, I came across a situation where I had to get the total number of records in each Memory optimized table in a particular database.
I remember developing a solution earlier (How to get the number of records in a disk based tables. ). I tried this solution on memory optimized table but it did not work. Ooopssssssss.
Given below is the script that we use to get the number of records in a disk based table. I included the table type in the query to demonstrate the problem.

USE hkNorthwind
GO
SELECT
OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]
, OBJECT_NAME(OBJECT_ID) As [Table Name]
, SUM([rows]) as [Total Records]
,(CASE WHEN OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=0
THEN 'User Table'
WHEN OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=1
THEN 'Memory Optimized Table'
ELSE NULL
END) As [Table Type]
FROM sys.partitions
WHERE
(OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
OR
OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=1)
GROUP BY OBJECT_ID
ORDER BY OBJECT_NAME(OBJECT_ID)
--OUTPUT

As you can see in the above example, it only returned the total number of records in disk based tables and returned zero for memory optimized table. So I started doing my research and found the solution. Given below is the script.

SOLUTION :

USE hkNorthwind
GO
SELECT
OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]
, OBJECT_NAME(OBJECT_ID) As [Table Name]
, SUM([rows]) as [Total Records]
FROM sys.partitions
WHERE
(OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=0)
GROUP BY OBJECT_ID

UNION

SELECT OBJECT_SCHEMA_NAME(Ind.OBJECT_ID) AS [Schema Name]
,OBJECT_NAME(Ind.OBJECT_ID) As [Table Name]
,ISNULL([rows],0)  as [Total Records]
FROM sys.hash_indexes AS Ind
CROSS APPLY sys.dm_db_stats_properties(Ind.object_id,Ind.index_id)
WHERE Ind.index_id =2
ORDER BY OBJECT_NAME(OBJECT_ID)
--OUTPUT

Note : Before executing the above query make sure that all your tables’ stats are up to date.

Let me know if you came across this issue and its solution.

Posted in Solutions, SQL SERVER | Tagged raresql, Simple way to count number of records in memory optimized table, SQL, SQL Server, SQL SERVER HEKATON | 1 Comment »

SQL SERVER 2012 – Simple way to add variables in the dynamic SQL

September 7, 2013 by Muhammad Imran

Sometimes, you need to construct (n) number of result set from (n) number of inputs using single query and here the dynamic SQL concept comes handy. In some solutions, I really feel that it is very hard to ignore dynamic SQL. (Never ever execute the user input directly using dynamic SQL. Always validate it before execution.) But it has its own rule to construct and you must follow it.

Given below is the list of actions that you must take care while adding variable in the dynamic SQL.

If you pass a NULL value of a variable in the dynamic SQL the whole string will be NULL, you must handle NULL variables in the dynamic SQL.
If you pass any data type variables other than varchar/ nvarchar, it will generate error. You must convert it into varchar/nvarchar before using it into dynamic SQL.
you must use concatenation sign ( + ) to concatenate the dynamic SQL with variables. You cannot place it directly inside the the dynamic SQL.
You must be very careful while opening and closing single quotes (‘) in the dynamic SQL. If you forget even a single place you cannot execute dynamic SQL.

Given below are the two approaches. The old approach we usually follow in all versions of SQL Server and the new approach, we can follow in SQL Server 2012 and above. I came across this approach recently, built some dynamic SQL on it and found it very good.

Old Approach :
Given below is the script we usually use to construct dynamic SQL.

--This script is compatible with all versions of SQL Server
DECLARE @SQL as varchar(max)
DECLARE @Datetime as datetime

SET @Datetime='2005-07-01 00:00:00.000'

SET @SQL='SELECT * FROM Sales.SalesOrderHeader
WHERE [OrderDate]=''' + CONVERT(varchar(50),@Datetime) + ''''
PRINT @SQL
EXEC (@SQL)

New Approach :
In the new approach, we need to use the CONCAT FUNCTION (shipped in SQL Server 2012) and it will resolve the above described problems.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @SQL as varchar(max)
DECLARE @Datetime as datetime

SET @Datetime='2005-07-01 00:00:00.000'

SET @SQL=CONCAT('SELECT * FROM Sales.SalesOrderHeader
WHERE [OrderDate]=','''',@Datetime,'''')
PRINT @SQL
EXEC (@SQL)

Conclusion :
The performance will be same for both approaches but the new approach will be the shortest way to write dynamic SQL because it handles the above dynamic SQL issues internally. Also, it will be easier to debug as well because it will be in parts so you can easily split at the time of debugging.

Let me know your opinion about new approach and its pros and cons.

Posted in Solutions, SQL SERVER | Tagged Dynamic SQL, SQL, SQL Server, SQL Server 2012, SQL Server 2012 - CONCAT | Leave a Comment »

SQL SERVER – Shorter way to convert Milliseconds to Days, Hours, Minutes, Seconds & Milliseconds

September 6, 2013 by Muhammad Imran

In my earlier article, I wrote the solution Shorter way to convert Seconds to Days, Hours, Minutes, Seconds . Recently, I received a request inquiring how to convert Milliseconds to Days, Hours, Minutes, Seconds & Milliseconds. Given below is the simple script to convert it without doing any divisions and remainders, using only built-in SQL functions DateAdd, DatePart, DateDiff

--This script will work on SQL Server 2005 and above.
Declare @Milliseconds as bigint
Declare @SubtractDate as datetime
--Enter Number of Milliseconds here
Set @Milliseconds=91234567
Set @SubtractDate=DateAdd(ms,@Milliseconds,getdate()) - Getdate()

Select Convert(varchar(10),DateDiff(day,'1900-01-01',@SubtractDate))
+ ' Day(s) ' +
Convert(varchar(10),DatePart(hh,@SubtractDate))
+ ' Hour(s) ' +
Convert(varchar(10),DatePart(mi,@SubtractDate))
+ ' Minute(s) ' +
Convert(varchar(10),DatePart(ss,@SubtractDate))
+ ' Second(s) ' +
Convert(varchar(10),DatePart(ms,@SubtractDate))
+ ' milliseconds(s) '
AS [Result]
--OUTPUT

Result
——————————————————————————————————–
1 Day(s) 1 Hour(s) 20 Minute(s) 34 Second(s) 567 milliseconds(s)

(1 row(s) affected)

Posted in Solutions, SQL SERVER | Tagged Convert Milliseconds to Days Hours Minutes Seconds and Milliseconds, raresql, SQL, SQL Server | Leave a Comment »

SQL SERVER 2012 – Guidelines to implement THROW Statement

September 5, 2013 by Muhammad Imran

In my earlier articles, I wrote about THROW statement and its enhanced features that give us more control over the debugging.
I also implemented it with some clients and found it much more effective than raiserror. Given below are the guidelines that must be followed to avoid any error.

We usually do not use (;) semicolon terminator in SQL Server, however it is recommended to use. But make sure to use it prior to any statement before THROW statement. Given below is the example.

BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 1 / 0
END TRY
BEGIN CATCH
PRINT 'ERROR';--Do not forget to put this semicolon
--prior to THROW Statement
THROW
END CATCH

If you fail to use the semicolon terminator prior to THROW statement, SQL Server will generate given below error message.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘THROW’

Sometimes we pass THROW statement without parameter, so make sure that THROW Statement is inside CATCH block as shown in the above example. If you fail to do it, SQL Server will generate given below error message.Msg 10704, Level 15, State 1, Line 9
To rethrow an error, a THROW statement must be used inside a CATCH block. Insert the THROW statement inside a CATCH block, or add error parameters to the THROW statement.
We usually pass the customized message_id in the THROW statement, make sure you use message_id between 50000 and 2147483647. Given below is the example.
```
EXEC sys.sp_addmessage @msgnum = 80000 ,@severity = 16
,@msgtext = N'This is a test message.'
,@lang = 'us_english';
GO

THROW 80000 , N'This is a test message', 1;
```
if you fail to do it, SQL Server will generate given below error message.
Msg 35100, Level 16, State 10, Line 9
Error number 49999 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647.

THROW does not allow the substitution parameter, so if you need to use it, always use with the FORMATMESSAGE function. Given below is the example.

EXEC sys.sp_addmessage
@msgnum = 90000
,@severity = 16
,@msgtext = N'This is a %s test message'
,@lang = 'us_english';
GO

DECLARE @msg NVARCHAR(50) =
FORMATMESSAGE(90000, N'raresql');

THROW 90000, @msg, 1;

Cleaning :

USE master;
GO
EXEC sp_dropmessage 80000;
GO
EXEC sp_dropmessage 90000;
GO

Reference : MSDN

Posted in SQL SERVER, Trainings | Tagged raresql, SQL, SQL Server, SQL Server 2012, THROW Statement | Leave a Comment »

SQL SERVER HEKATON – Fix – Error – 10770 – The operation ‘ALTER TABLE’ is not supported with memory optimized tables.

September 3, 2013 by Muhammad Imran

The operation ‘ALTER TABLE’ is not supported with memory optimized tables is one of the new error messages in SQL Server Hekaton. This error message is related to Memory optimized tables feature, a new type of table shipped with SQL Server Hekaton.

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

Severity : 16

Error Message: The %S_MSG ‘%ls’ is not supported with memory optimized tables.

Error Generation:
Let me create a sample memory optimized table to demonstrate this error.

USE Sample_DB
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

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

ALTER TABLE tbl_sample ADD [CreationDateTime] datetime NULL
--OUTPUT

Msg 10770, Level 16, State 102, Line 17
The operation ‘ALTER TABLE’ is not supported with memory optimized tables.

Ooopps…… I am unable to execute it.

Resolution:
Memory optimized tables do not support alter table statement. So, instead of alter, you need to drop and create the memory optimized tables.

Lets rewrite the above statement using drop and create. Given below is the script.

USE Sample_DB
GO
DROP TABLE tbl_sample
GO
CREATE TABLE tbl_sample
(
[ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[Name] VARCHAR(50) NOT NULL,
[CreationDateTime] datetime
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Posted in Errors, SQL SERVER | Tagged Msg 10770 Level 16 State 102, raresql, SQL, SQL Server, SQL SERVER HEKATON - Fix - Error - 10770 - The %S_MSG '%ls' is not supported with memory optimized tables., The operation 'ALTER TABLE' is not supported with memory optimized tables. | 2 Comments »

SQL SERVER 2012 – Create custom snippet for Sequence object

September 2, 2013 by Muhammad Imran

SEQUENCE object is one of my favorite objects introduced in SQL Server 2012. It is very handy when you need an enhanced functionality in an identity column. Due to its performance & functionality, I use it very frequently, but the issue is, I either need to write the complete script of sequence by myself or I need to use SSMS and then go to database >> Programmability >> Sequence and create a new sequence object as shown in the picture below.

Alternatively, we can create the Sequence object snippet, as shown in the script below. Just copy the given below script and save as “Create Sequence.snippet” and register it. Once registered, you can use it like any other custom snippet.

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
<_locDefault _loc="locNone" />
<_locTag _loc="locData">Title</_locTag>
<_locTag _loc="locData">Description</_locTag>
<_locTag _loc="locData">Author</_locTag>
<_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
<CodeSnippet Format="1.0.0">
<Header>
<Title>Create Sequence</Title>
<Shortcut></Shortcut>
<Description>Creates a sequence.</Description>
<Author>Muhammad Imran</Author>
<SnippetTypes>
<SnippetType>Expansion</SnippetType>
</SnippetTypes>
</Header>
<Snippet>
<Declarations>
<Literal>
<ID>SchemaName</ID>
<ToolTip>Name of the schema</ToolTip>
<Default>dbo</Default>
</Literal>
<Literal>
<ID>Sequencename</ID>
<ToolTip>Name of the Sequence</ToolTip>
<Default>Sample_Sequence</Default>
</Literal>
<Literal>
<ID>datatype</ID>
<ToolTip>Data type of the column</ToolTip>
<Default>int</Default>
</Literal>
<Literal>
<ID>start</ID>
<ToolTip>Start Value</ToolTip>
<Default>1</Default>
</Literal>
<Literal>
<ID>increment</ID>
<ToolTip>Increment Value</ToolTip>
<Default>1</Default>
</Literal>
<Literal>
<ID>minvalue</ID>
<ToolTip>Min Value</ToolTip>
<Default>1</Default>
</Literal>
<Literal>
<ID>maxvalue</ID>
<ToolTip>Max Value</ToolTip>
<Default>100</Default>
</Literal>
<Literal>
<ID>Cache</ID>
<ToolTip>Cache Value</ToolTip>
<Default>2</Default>
</Literal>
</Declarations>
<Code Language="SQL">
<![CDATA[CREATE SEQUENCE [$SchemaName$].[$Sequencename$]
AS [$datatype$]
START WITH $start$
INCREMENT BY $increment$
MINVALUE $minvalue$
MAXVALUE $maxvalue$
CACHE $Cache$
GO
]]>
</Code>
</Snippet>
</CodeSnippet>
</CodeSnippets>

Posted in Solutions, SQL SERVER | Tagged Create custom snippet for Sequence object, raresql, SQL, SQL Server 2012 | Leave a Comment »

SQL SERVER 2012- How to format phone numbers

August 30, 2013 by Muhammad Imran

Formatting phone number has never been an issue, the only thing that you need to do is, use few string functions and you can convert it into any format. But what encourages me to write this article is that we can format the phone numbers in any format using only one function, i.e. Format (introduced in SQL Server 2012).

Note : I usually recommend that phone number must be properly maintained inside the database. But sometimes we come across this issue in legacy systems.

Sample :
Let me create a sample to explain it.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
[ID] INT,
[Phone Numbers] INT
)
GO
INSERT INTO tbl_sample VALUES (1,3333333333)
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

ID Phone Numbers
———– ————-
1 3333333333

(1 row(s) affected)

SOLUTION :
In the solution, we will use FORMAT function and provide a custom format of telephone number such that, the Format function will automatically convert it into custom format.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
[Phone Numbers]
,FORMAT([Phone Numbers],'###-###-####') AS [Formatted Phone]
FROM tbl_sample
UNION ALL
SELECT
[Phone Numbers]
,FORMAT([Phone Numbers],'(###) ###-####') AS [Formatted Phone]
FROM tbl_sample
GO
--OUTPUT

Posted in Solutions, SQL SERVER | Tagged Format Function, How to format phone numbers, raresql, SQL, SQL Server, SQL Server 2012 | 1 Comment »

SQL SERVER HEKATON – Statistics for Memory-Optimized Tables

August 28, 2013 by Muhammad Imran

Statistics is very helpful when it comes to performance because query optimizer uses these statistics to create query plans to improve query performance. I recently implemented the statistics on memory optimized table and given below are my findings.

S.No	Disk based tables	Memory Optimized tables
1	Statistics are updated automatically.	Statistics are NOT updated automatically
2	It uses by default sampled statistics.	It has no default statistics, you must specify fullscan option.
3	It supports sampled statistics & fullscan options.	It supports ONLY fullscan option.

Following are the steps you must be cognizant of while implementing statistics in memory optimized tables :

First of all create memory optimized tables and indexes.
After that, insert (update, delete) data into the memory optimized tables.
Once you are done with the data manipulation, update statistics on the memory optimized tables. (Do not do this step in the peak hour)
The last step is to create natively compiled stored procedures that access the memory tables.

Given below is the script to update memory optimized table statistics.

USE hkNorthwind
GO
UPDATE STATISTICS dbo.tbl_Product_Master WITH FULLSCAN, NORECOMPUTE

Posted in SQL SERVER, Tables | Tagged raresql, SQL, SQL Server, SQL Server 2012, Statistics for Memory-Optimized Tables | Leave a Comment »

« Newer Posts - Older Posts »

SQL Server Portal

Real world SQL scenarios & its unique & optimized solutions

SQL SERVER 2012 – How to get a value from previous result row effectively

SQL SERVER HEKATON – Simple way to count number of records in memory optimized tables

SQL SERVER 2012 – Simple way to add variables in the dynamic SQL

SQL SERVER – Shorter way to convert Milliseconds to Days, Hours, Minutes, Seconds & Milliseconds

SQL SERVER 2012 – Guidelines to implement THROW Statement

SQL SERVER HEKATON – Fix – Error – 10770 – The operation ‘ALTER TABLE’ is not supported with memory optimized tables.

SQL SERVER 2012 – Create custom snippet for Sequence object

SQL SERVER 2012- How to format phone numbers

SQL SERVER HEKATON – Statistics for Memory-Optimized Tables

Recent Posts

Archives

Categories

Blog Stats

Follow Blog via Email

Find us on Facebook

Disclaimer