Feeds:
Posts
Comments

Archive for September, 2013

Today is month end (30 September 2013) and I was checking my general checklist for month end closing procedure. I saw one of my scripts that I had written earlier regarding how to check whether a date is month end date or not. So, I thought of re-writing this script even much shorter & efficient than my earlier script using EOMONTH .

Let me create a sample table to demonstrate.

USE tempdb
GO
--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
 [ID] INT,
 [Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-03-31')
INSERT INTO tbl_Sample VALUES (3,'2013-10-10')
INSERT INTO tbl_Sample VALUES (4,'2013-12-31')
GO

SOLUTION 1 :
Given below is the script that we usually use in SQL Server 2005/2008 to check whether the date is End of month date or not.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT [ID],[Date],
(CASE WHEN
CONVERT(varchar(11)
,DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date])+1, -1),120)=[Date]
THEN 'Month End' ELSE 'Not a Month End' END) As [Result]
FROM tbl_Sample
GO

isdate is a mont end date.1.1

SOLUTION 2 :
Given below is the script, that I developed using new functions (EOMONTH & IIF) shipped in SQL Server 2012 to check whether the date is End of month date or not. Due to these functions, we can reduce the script size and improve the performance.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT [ID],[Date],
IIF(EOMONTH([Date])=[Date],'Month End','Not a Month End')
As [Result]
FROM tbl_Sample
GO
--OUTPUT

isdate is a mont end date.1.2

CONCLUSION :
As you can see, both solutions have the same result set but Solution 2 is recommended because it is using only one function to check whether the date is month end date or not, instead of a lot of date functions.

Advertisements

Read Full Post »

How to get list of all tables’ and its index sizes along with row count looks very simple ? We do have the solution for it. Either you can use sp_spaceused or you can use one of my own solutions.

But things became strange when I tried both solutions on SQL Server 2014 having memory optimized table and both solutions return zero for memory optimized tables. Given below is the script and its output in SQL Server 2014.

USE hkNorthwind
GO
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';
GO

table and index size of memory optimized table.1.1

As you can see in the above image, all memory optimized tables showing zero result. Oopsssssss. So what is the solution ?
I started my research and developed a solution . Given below is the script.

SOLUTION :

USE hkNorthwind
GO
DECLARE @PageSize float
SELECT @PageSize=v.low/1024.0 FROM MASTER.dbo.spt_values v WHERE v.number=1 and v.type='E'

SELECT
  OBJECT_SCHEMA_NAME(tbl.object_id) as [Schema Name]
, OBJECT_NAME(tbl.object_id) as [Table Name]
, CASE
  WHEN (tbl.is_memory_optimized=0) THEN
		ROUND(TRY_CONVERT(float,ISNULL((SELECT @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
		FROM sys.indexes as i
		JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
		JOIN sys.allocation_units as a ON a.container_id = p.partition_id
		WHERE i.object_id = tbl.object_id),0.0))/1024,3)
  ELSE
		Round(TRY_CONVERT(float,isnull((SELECT (tms.[memory_used_by_table_kb])
		FROM [sys].[dm_db_xtp_table_memory_stats] tms
		WHERE tms.object_id = tbl.object_id), 0.0))/1024,3)
		END
 as [DataSpaceUsed (In Mbs)]
, (CASE
  WHEN (tbl.is_memory_optimized=0) THEN
    ROUND(TRY_CONVERT(float,ISNULL((
    (SELECT SUM (used_page_count) FROM sys.dm_db_partition_stats ps WHERE ps.object_id = tbl.object_id)
    + ( CASE (SELECT count(*) FROM sys.internal_tables WHERE parent_id = tbl.object_id AND internal_type IN (202,204,207,211,212,213,214,215,216,221,222))
        WHEN 0 THEN 0
        ELSE (
            SELECT sum(p.used_page_count)
            FROM sys.dm_db_partition_stats p, sys.internal_tables it
            WHERE it.parent_id = tbl.object_id AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222) AND p.object_id = it.object_id)
        END )
    - (SELECT SUM (CASE WHEN(index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END)
        FROM sys.dm_db_partition_stats WHERE object_id = tbl.object_id)
    ) * @PageSize, 0.0) )/1024,3)
 ELSE
    ROUND(TRY_CONVERT(float,isnull((SELECT (tms.[memory_used_by_indexes_kb])
    FROM [sys].[dm_db_xtp_table_memory_stats] tms
    WHERE tms.object_id = tbl.object_id), 0.0)) /1024,3)
END)
as [IndexSpaceUsed (In Mbs)]

,(CASE
WHEN (tbl.is_memory_optimized=0) THEN
OBJECTPROPERTYEX(OBJECT_ID, N'Cardinality')
ELSE
(
SELECT 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 AND Ind.object_id=tbl.object_id
)
END) [Total No of Rows]

FROM
sys.tables as tbl
--OUPUT

table and index size of memory optimized table.1.2

As you can see in the above script (solution), SQL Server 2014 handles disk based table and memory optimized table space, index space and row count separately. So you need to calculate it separately.

Let me know if you came across the same situation and its solution.

Read Full Post »

I had come across this problem (how to convert varchar to currency) a couple of years ago and fixed this problem. Recently I had a chance to revisit my solutions and I discovered that we can re-script this solution much more efficiently than I did earlier, using FORMAT & TRY_CONVERT (new functions shipped in SQL Server 2012).

Note : It is recommended to do this formatting from the front end of the application.

Let me create a sample to demonstrate the solution :

SAMPLE:

USE [tempdb]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
CREATE TABLE [dbo].[tbl_sample]
(
[Col_ID] [int] NULL,
[Col_varchar] [varchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (1, N'1000.00')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (2, N'-10.21')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (3, N'1234.5678')
GO
INSERT [dbo].[tbl_sample] ([Col_ID], [Col_varchar])
VALUES (4, N'123.45')
GO

SOLUTION:
In this solution, first of all, we need to convert the data into money data type then format it into any currency using culture.
Given below is the solution.

SELECT
[Col_ID]
,[Col_varchar]
,FORMAT(TRY_CONVERT(money,[Col_varchar]),'C','en-US')
AS [Varchar_to_currency]
FROM [tbl_sample]
GO

varchar_to_currency.1.1

Read Full Post »

I have written multiple articles on memory optimized table and its handling. I was also working on its core area that is its performance. I continued with my research to see whether it really improves the performance or not. And as per my research, I found a massive improvement in the performance (except few limitations).

Before proceeding with the performance, I would like to create a sample to compare the performance between disk based & memory optimized table.

  • Sample For Memory Optimized table :

Let me create sample tables for memory optimized table and insert bulk data in it to measure the performance.
Given blow is the script.

--Given below scripts are compatible with SQL Server 2014 and above.
USE hkNorthwind
GO
--Create a memory optimized table
CREATE TABLE [dbo].[tbl_product_Master_MO]
(
	[Product ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH
        WITH (BUCKET_COUNT = 100000),
	[Product Name] [nvarchar](100) NULL,
	[Creation Datetime] [datetime] NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
--Insert 65536 records into the memory optimized table
--This script may take few minutes to insert records.

USE hkNorthwind
GO
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N4 AS X, N4 AS Y)
INSERT INTO tbl_product_Master_MO
SELECT n,'Number' + Convert(varchar(10),n),GETDATE()
from N5

---Create native compiled procedure to give boost to memory optimized table.
CREATE PROCEDURE dbo.usp_product_master
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
SELECT [Product ID],[Product Name],[Creation Datetime]
FROM dbo.[tbl_product_Master_MO]

END
GO
  • Sample For Disk Based table :

Let me create sample tables for disk based table and insert bulk data in it.
Given blow is the script.

--Create a disk based table (Normal table)
USE [hkNorthwind]
GO
CREATE TABLE [dbo].[tbl_product_Master_DB](
	[Product ID] [int] NOT NULL,
	[Product Name] [nvarchar](100) NULL,
	[Creation Datetime] [datetime] NULL
) ON [PRIMARY]
GO

--Insert 65536 records into the disk based table.
--This script may take few minutes to insert records.
USE hkNorthwind
GO
;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y),
N5 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N4 AS X, N4 AS Y)
INSERT INTO tbl_product_Master_DB
SELECT n,'Number' + Convert(varchar(10),n),GETDATE()
from N5
GO

Let’s proceed with the different categories of performance comparison between normal table and memory optimized table.

  • Query Cost related to the Batch :

Let us start with the query cost related to the batch.
Given below is the script and its output.
Note : Given below results are tested on 300K~ records.

USE hkNorthwind
GO
 --Memory optimzed table
 Select * from [tbl_product_Master_MO]

 --Disk based table
 Select * from [tbl_product_Master_DB]
 GO
 --OUTPUT

diskbased_vs_memory_optimized.1.1

As you can see that memory optimized table only took 7% but on the other hand disk based table took 93% for the same task.

  • Time Statistics

Let’s turn on the time statistics and view the performance comparison.
Given below is the script


SET STATISTICS TIME ON
USE hkNorthwind
GO
---Given below is the Memory optimized native compiled stored procedure.
--This stored procedure we used to browse memory optimized table.
--It will give boost to memory optimized table performance.
EXEC usp_product_master
 GO

 --Disk based table
 Select * from [tbl_product_Master_DB]
 GO
SET STATISTICS TIME OFF
 --OUTPUT

–For memory optimized
SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 1706 ms.

— For disk based table.
SQL Server Execution Times:
CPU time = 234 ms, elapsed time = 3251 ms.

  • IO Statistics 

Let’s turn on the IO statistics and you will be amazed to view that there is NO IO involvement in memory optimized table
and due to this, it produces a high level performance.
Given below is the script.

SET STATISTICS IO ON
USE hkNorthwind
GO
 --Memory optimzed table
 Select * from [tbl_product_Master_MO]
GO
 --Disk based table
 Select * from [tbl_product_Master_DB]
 GO
SET STATISTICS IO OFF
 --OUTPUT

–For memory optimized query
No Result.

— For disk based table query.
(327680 row(s) affected)
Table ‘tbl_product_Master_DB’. Scan count 1, logical reads 1962, physical reads 0, read-ahead reads 1584, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

diskbased_vs_memory_optimized.1.2

Conclusion :
I am very much impressed with the memory optimized table performance. But still it requires some improvement regarding BLOB data types & validations.
Given below is the consolidated summary that will give you a glance performance review of disk based vs memory optimized table.

S.No

Type

Memory Optimized Table

Disk Based Table

1

Query Cost related to the Batch

7%

93%

2

Statistics Time

93 ms

234 ms

3

Statistics IO

NO IO involvement

1962 read

Let me know about your test results.

Read Full Post »

Sequence object is one of the nice features shipped with SQL Server 2012. It gives much more flexibility than identity column. Due to its flexibility, I usually recommend to use it instead of identity. But recently I came across with a so called issue related to sequence object. The issue is that sequence object does not enforce uniqueness by itself.

Let me create a sample to demonstrate the issue step by step.
Step 1:
Create a sample table. Given below is the script.

USE [AdventureWorks2012]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample Table
CREATE TABLE [dbo].[tbl_sample]
(
[ID] [int] NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]
GO

Step 2 :
Create a sequence object with CYCLE property.

--Create a sample Sequence object
--DROP SEQUENCE [dbo].[Seq_Sample]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2
CYCLE
GO
--

Step 3 :
Insert few records in the above sample table using sequence object.

USE [AdventureWorks2012]
GO
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-1')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-2')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-3')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-4')
GO

Step 4 :
Browse the table. Given below is the script.

USE [AdventureWorks2012]
GO
SELECT * FROM [tbl_sample]
GO
--OUTPUT

sequence_eniqueness.1.1

As you can see in the above result set, there are duplicate IDs in the ID column. Ooopsss it does not enforce uniqueness by default. So what is the solution ?

SOLUTION :
Let me explain the solution step by step.

Step 1 :
Create a sample table. Given below is the script.

USE [AdventureWorks2012]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample Table
CREATE TABLE [dbo].[tbl_sample]
(
[ID] [int] NULL,
[Name] [varchar](100) NULL
) ON [PRIMARY]
GO

Step 2 :
Create a unique index on column ID of sample table.

USE [AdventureWorks2012]
GO
CREATE UNIQUE INDEX AK_tbl_sample_ID
  ON dbo.tbl_sample (ID)
GO

Step 3 :
Create a sequence object with NO CYCLE property.

USE [AdventureWorks2012]
GO
--Create a sample Sequence object
--DROP SEQUENCE [dbo].[Seq_Sample]
--GO
CREATE SEQUENCE [dbo].[Seq_Sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 4
NO CYCLE
GO
--

Step 4 :
Insert few records in the above sample table using sequence object to check whether the uniqueness is enforced or not.

USE [AdventureWorks2012]
GO
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-1')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-2')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-3')
INSERT INTO [tbl_sample] VALUES (NEXT VALUE FOR [Seq_Sample],'TEST-4')
GO
--OUTPUT

Step 5 :
Browse the table again. Given below is the script.

USE [AdventureWorks2012]
GO
SELECT * FROM [tbl_sample]
GO
--OUTPUT

sequence_eniqueness.1.2

Conclusion :
Whenever you need to enforce uniqueness in the table using sequence object, make sure you have a UNIQUE INDEX on that particular column of the table. Also make sure that SEQUENCE object has NO CYCLE property.

Read Full Post »

I came across this error message while I was working on one of the file tables (A new table concept came in SQL Server 2012) and tried to create a full text index. Most likely this error is related to semantic search (A new type of search concept came in SQL Server 2012).
Let me explain this error in detail :

Message Number: 41202

Severity : 16
Error Message: The source table ‘%.*ls’ specified in the SEMANTICSIMILARITYTABLE, SEMANTICKEYPHRASETABLE or SEMANTICSIMILARITYDETAILSTABLE function doesn’t have a full-text index that uses the STATISTICAL_SEMANTICS option. A full-text index using the STATISTICAL_SEMANTICS option is required to use this function.

Error Generation:
I tried to create full text index on file table and received given below error message.

errormessage41202.1.1

Lets resolve this issue step by step :

Step 1 :
First of all you need to browse the SQL Server installation media and select the given below folder. Here you will find a file namely SemanticLanguageDatabase.msi, just execute this file.
Given below are the different paths for x86 & x64 machines.

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

Given below is the screen image of this folder.

errormessage41202.1.2

Step 2:
Once you run the above SemanticLanguageDatabase.msi and install it, it will give you two database files (semanticsDB, semanticsdb_log) in a location (C:\Program Files\Microsoft Semantic Language Database), if you install it in the default location.
Given below is the image.

errormessage41202.1.3

Step 3:
Once we extract the semantic database files (semanticsDB, semanticsdb_log), attach in your database server. Given below is the image.

errormessage41202.1.4

Step 4:
Once you attach the file in your database, just register the semantic database. Given below is the script.

EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N'semanticsdb'
GO

Now your semantic database has been registered. You can create full text indexes on file tables without any errors.

Read Full Post »

Older Posts »