Feeds:
Posts
Comments

Archive for September, 2013

HASH index (NONCLUSTERED) is a new type of index introduced in SQL Server 2014. It is only supported by memory optimized table. Recently, I was working on a memory optimized table and I came across in a situation where I had to find all the hash indexes available in the database. As usual I got it from a system view (sys.indexes). It is nothing new but the story did not end here because I found a shorter way to get the list of non-clustered hash indexes.
Given below is script that can give you the list of all hash indexes available in any particular database.

USE hkNorthwind
GO
SELECT
  object_schema_name([object_id]) As [Schema Name]
, object_name([object_id]) As [Table Name]
, [name] As [Index Name]
, [type_desc] As [Index Description]
FROM
sys.hash_indexes
--OUTPUT

list of hash indexes.1.1

Read Full Post »

Recently, I was doing some research on memory optimized table and I came across a situation where I needed to keep some temporary data in the tables. So I thought of creating temporary table. Now I have two choices, either to go for disk based temporary table or to go for memory optimized temporary table.
I decided to create a memory optimized temporary table.

Given below is the script.

USE hkNorthwind
GO
--DROP TABLE #tbl_Product_Master
--GO
CREATE TABLE #tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10770, Level 16, State 92, Line 11
The feature ‘temporary tables’ is not supported with memory optimized tables.

Opssssssss error….

If I am not wrong, you cannot create a memory optimized temporary table because this feature is not supported by memory optimized table. So I started my research and tried to find out the work around that can help me create a memory optimized temporary table. Finally I came to know how to create a temporary table in memory optimized table.

Given below is the script.

USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY)

GO
--OUTPUT

As you can see, I just added one argument (DURABILITY = SCHEMA_ONLY) at the time of memory optimized table and because of this argument, the table is converted to a temporary table.

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

Reference : MSDN

Read Full Post »

Recently, I came across a query where I needed to calculate the first and last day of the year. Fortunately, we do have the solution using DATEADD & DATEDIFF function. But the problem is we need to use these functions multiple times to achieve the first and last day of the year. So I went to the drawing board and started reducing the formula and finally I succeeded. Given below is the script to calculate the first and last day of the year using Format function (shipped with SQL Server 2012).

--This script is compatible with SQL Server 2012 and above.
SELECT DATEADD(YEAR,-1,FORMAT(Getdate(),'yyyy')) As [Result]
, 'First Day of Previous Year' As [Type]

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(GETDATE(),'yyyy'))
, 'Last Day of Previous Year'

UNION ALL
SELECT FORMAT(GETDATE(),'yyyy')
, 'First Day of Current Year'

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(DATEADD(YEAR,1,GETDATE()),'yyyy'))
, 'Last Day of Current Year'

UNION ALL
SELECT DATEADD(YEAR,1,FORMAT(Getdate(),'yyyy'))
, 'First Day of Previous Year'

UNION ALL
SELECT DATEADD(ms,-3,FORMAT(DATEADD(YEAR,2,GETDATE()),'yyyy'))
, 'Last Day of Previous Year'
--OUTPUT

first_day_and_last_day_of_year.1.1

Read Full Post »

A couple of days ago, I came across an issue where I had to get the total number of records in the memory optimized table. Finally, I found the solution but during the research, I came across that you can get the total number of records of any tables, views, indexes using OBJECTPROPERTYEX as well. Basically, in SQL Server 2012, a new property namely Cardinality has been added in the OBJECTPROPERTYEX and the purpose of this property is to return the total number of the records in any object.

Given below is the simple script.

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
SELECT
OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]
,name AS [Table Name]
,OBJECTPROPERTYEX(OBJECT_ID, N'Cardinality')
AS [Total No of Records]
FROM sys.tables
--OUTPUT

Total number of records using objectpropertyex.1.1

Let me know your feedback about this approach.

Read Full Post »

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

get the previous row result.1.1

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

get the previous row result.1.2

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

get the previous row result.1.2

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.

Read Full Post »

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

memory optimized table record count1.1

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

memory_optimized table_record_count.1.2

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.

Read Full Post »

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.

Read Full Post »

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 DateAddDatePartDateDiff

--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)

Read Full Post »

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.

  1. 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.
  2. 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’

  3. 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.
  4. 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.

  5. 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;
    
  6. Cleaning :

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

    Reference : MSDN

    Read Full Post »

« Newer Posts - Older Posts »