Feeds:
Posts
Comments

Archive for July, 2013

In my earlier article, I had discussed about how to implement identity column in the Memory optimized table. Now, lets try to create a BLOB data type column in memory optimized table.

Given below is the script that we usually use to implement BLOB data type columns in the disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
CREATE TABLE tbl_sample
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null,
 [Address] varchar(Max)
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10770, Level 16, State 94, Line 3
The type ‘varchar(max)’ is not supported with memory optimized tables.

Oh no.. I am unable to create a BLOB data type column in memory optimized table and as we know, BLOB data types are one of the most commonly used data types in the database.

So what is the solution?

The solution is very simple. What you need to do is, actually split the above table into two tables and one as memory optimized table and 2nd one should be disk based table. And the logic behind this is, we will keep all the data types columns except BLOB in memory optimized table and will put all the BLOB data types column in disk based tables. But do not forget to keep the same ID in both tables.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_sample_Memory_table
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null,
 ) WITH (MEMORY_OPTIMIZED = ON)
GO

CREATE TABLE tbl_sample_Normal_Table
(
 [ID] integer not null,
 [Address] varchar(Max)
)
GO
--OUTPUT

Command(s) completed successfully.

As you can see that, both tables are created successfully.

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

In my earlier article, I had discussed about a new feature name introduced in SQL Server 2012, Cycle Clipboard Ring. This feature is very handy when it comes to bulk copy & paste.

In this article, we will discuss another issue with copy & paste and at times it can get irritable too, but SQL Server has provided the solution for this problem as well. Let me describe the problem/solution step by step.

Step 1 :
Copy any line of code from the query window.

Step 2 :
Go to any blank line and instead of paste (Ctl+V) just copy (Ctl+C) that line. This step usually happens by mistake.

Step 3 :
Now, try to paste (Ctl+V) on the same line. Ooopsss.. It pasted the blank lines, meaning whatever you copied, is lost.

Basically, you copied the blank line in the step 2 by mistake and as many times you pasted after that, it will paste the blank line.
Now what is the solution?

The solution is very simple. Just Select Tools >> Options >> Text Editor >> Plain text >> General >> and uncheck the Apply Cut or Copy commands to blank lines when there is no selection.

As shown in the pictures below.

copy blank lines1.2

copy blank lines1.1

Now, you can again proceed with the above three steps and it will not copy the blank line even if you do it by mistake.

Let me know if you face this problem in real world and its solution.

Read Full Post »

Reports are the backbone for each and every application and most of the reports are designed using SQL Server Reporting or crystal report in dot net applications. But most of these reports are usually exported by end users in excel for further processing and in most cases we spend a lot of time to design and develop those reports in SSRS or Crystal. So is there a short cut ?

Certainly. You can populate the data in excel directly from SQL Server and prepare the same report in few minutes.

Lets do it step by step.

Step 1 :
In the first step you need to create a table or view, that you will be using in the excel for reporting.
For this demonstration, we will use an already created view namely “vEmployee” in the AdventureWorks2012.
You can download this AdventureWorks2012 database from here.

Step 2 :
Open excel 2010 and select Data tab and then click on From Other Sources and select From SQL Server, as shown in the picture below.

SQL excel connectivity1.1

Step 3 :
Once you click on From SQL Server, it will open a Data Connection wizard. In this wizard, you need to enter SQL Server credentials and click NEXT, as shown in the picture below.

SQL excel connectivity1.2

Step 4 :
Now, you are in the database and table/view selection window. Just select the appropriate database and table or view and click NEXT, as shown in the picture below.

SQL excel connectivity1.3

Step 5 :
This is the last window of Data connection wizard, where you need to save the connection and click FINISH.

SQL excel connectivity1.4

Step 6 :
After that you need to select format (Table, Pivot Table Report, Pivot Chart and Pivot Table Report) of data in excel and where (Existing sheet, new worksheet) to place the data. Lets select a table and the existing sheet from the options.

SQL excel connectivity1.5

Step 7 :
Now, we have the data and beauty is that, whenever you press refresh button, excel refreshes the data from SQL Server and displays the up to date data in excel. Now, you can do pivot and other reporting stuff with this data in few minutes.

SQL excel connectivity1.6

Conclusion :

This tool usually saves a lot of time in developing complex reports. I usually recommend people to prepare some templates of these reports and whenever there is a requirement, just refresh the data and send it to end users.

Let me know if you have implemented this technique in real world.

Read Full Post »

The %S_MSG ‘%ls’ 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 and insert few records in it 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

INSERT INTO tbl_sample ([ID],[Name]) VALUES (1,'raresql-1')
GO
INSERT INTO tbl_sample ([ID],[Name]) VALUES (2,'raresql-2')
--OUTPUT

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

TRUNCATE TABLE tbl_sample
--OUTPUT

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

Ooopps…… I am unable to execute it.

Resolution:
Memory optimized tables does not support truncate statement, So, instead of truncate you can use delete statement for memory optimized tables.

Lets rewrite the above statement using delete. Given below is the script.

USE Sample_DB
GO
DELETE FROM tbl_sample
--OUTPUT

(2 row(s) affected)

Lets browse the table and view either the records are deleted or not.

USE Sample_DB
GO
SELECT * FROM tbl_sample
--OUTPUT

truncate memory optimized table 1.1

Read Full Post »

Memory optimized table is a new feature introduced in SQL Server Hekaton.  As it is new for all of us, I am doing my research with different aspects of memory optimized table. In this article, we will create an identity field in the memory optimized table.

Given below is the script that we usually use to implement identity in the disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
CREATE TABLE tbl_sample
(
 [ID] integer identity(1,1) not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10771, Level 16, State 8, Line 3
The feature ‘identity column’ is not yet implemented with memory optimized tables.

Oooops.. I am unable to create identity column in memory optimized table.

So, how can we create an identity column in memory optimized table ? Here comes the SEQUENCE object solution to the rescue.

Let me create an identity column in memory optimized table in sequential steps.

Step 1 :
First of all, you need to create a memory optimized table without any identity column.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_sample
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Step 2 :
Once you have created the memory optimized table, create a Sequence object.

USE hkNorthwind
GO
CREATE SEQUENCE [Seq_Sample]
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 1000
GO

Step 3 :
Insert the records using sequence in the memory optimized table.
Given below is the script.

USE hkNorthwind
GO
INSERT INTO tbl_sample ([ID],[Name])
VALUES (NEXT VALUE FOR [Seq_Sample],'raresql-1')
GO
INSERT INTO tbl_sample ([ID],[Name])
VALUES (NEXT VALUE FOR [Seq_Sample],'raresql-2')
GO

Step 4 :
Just browse the table and check whether identity is implemented or not.

USE hkNorthwind
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

identity_field_for_memory_optimized_table.1.1

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

Recently, I was working on a legacy database and I came across an issue where I had a full file path of a document but the file name was not separated / isolated from the file path.

Let me create a sample to demonstrate it.

USE Tempdb
GO
CREATE TABLE tbl_Sample
(
 [ID] INT,
 [File Path] VARCHAR(200)
)
GO
INSERT INTO tbl_Sample ([ID],[File Path])
VALUES (1,'C:\Users\imran\Documents\worksheet.xlsx')

INSERT INTO tbl_Sample ([ID],[File Path])
VALUES (2,'C:\Users\imran\image.png')

INSERT INTO tbl_Sample ([ID],[File Path])
VALUES (3,'C:\Users\imran\Documents\Document.doc')
GO

Given below is a script that separates file name from file path.

SELECT
 [ID]
,[File Path]
,SUBSTRING([File Path],LEN([File Path])
- CHARINDEX('\',REVERSE([File Path]))+2,LEN([File Path]))
AS [File Name]
FROM tbl_Sample
GO
--OUTPUT

extractfilename.1.1

Read Full Post »

In my earlier article, I explained how to create memory optimized table. However, once I created memory optimized tables, I came across a problem segregating the disk based tables, file tables & memory optimized tables.

SQL Server Hekaton comes to the rescue with an addition of a field(column) namely is_memory_optimized in sys.tables (system table) which facilitates segregation. Given below is the script.

USE hkNorthwind
GO
SELECT
  object_id
, name  As [Table Name]
,(CASE WHEN is_filetable=0 AND is_memory_optimized=0
 THEN 'DISK BASED TABLE'
 WHEN is_filetable=1
 THEN 'FILE TABLE'
 WHEN is_memory_optimized=1
 THEN 'MEMORY OPTIMIZED TABLE' END)
 AS [Table Type]
FROM sys.tables
--OUTPUT

memoryoptimizedtables1.1

Note : You can download the hkNorthwind database from here.

Let me know if you know a better solution.

Read Full Post »

In my earlier article, I posted a solution how to calculate weekdays between weekends. Today I came across a situation where I need to calculate the weekends between two dates and the criterion is that weekend can be any day (not necessary Sunday only) of the week.

Given below is the function that calculates the weekends between two dates including the dates provided as input parameter. In some parts of the world weekends are on ‘Fridays’, ‘Saturdays’ and in other parts ‘Saturdays’ and ‘Sundays’.
In this function, you need to pass weekend as a parameter.

CREATE FUNCTION dbo.GetWeekendDays
( @StartDate DATETIME,
  @EndDate DATETIME ,
  @Weekend VARCHAR(50)
 )
RETURNS INT
BEGIN

DECLARE @Xml AS XML
DECLARE @WEEKEND_DAYS AS INT
SET @Xml =CAST(('<a>'+replace(@Weekend,',' ,'</a><a>')+'</a>') AS XML)

;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 ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @WEEKEND_DAYS=SUM(1) FROM n4
WHERE DATEADD(day,n-1,@startdate)<=@Enddate
AND DATENAME(dw,DateAdd(day,n-1,@startdate)) In (
    SELECT A.value('.', 'varchar(max)') as [Column]
    FROM @Xml.nodes('A') AS FN(A)
)

RETURN @WEEKEND_DAYS
END
GO
SELECT dbo.GetWeekendDays
('02/01/2013','02/28/2013','Saturday,Sunday')
as [Weekend Days]

GO
SELECT dbo.GetWeekendDays
('02/01/2013','02/28/2013','Friday,Saturday')
as [Weekend Days]

--OUTPUT
Weekend Days
------------
8

(1 row(s) affected)

Weekend Days
------------
8

(1 row(s) affected)

Have a better solution ?

Read Full Post »

Invalid data type %.*ls in function %.*ls. is one of the new error messages in SQL Server 2012. This error message is related to PARSE function, a new conversion function shipped with SQL Server 2012.

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

Severity : 15

Error Message: Invalid data type %.*ls in function %.*ls.

Error Generation:

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

SELECT PARSE(GETDATE() AS varchar(11)  USING 'en-US') AS Result;
--OUTPUT

Msg 10761, Level 15, State 2, Line 1
Invalid data type varchar in function PARSE.

Ooopps…… I am unable to execute it.

Resolution:
In the above example, I tried to parse a datetime into a varchar. First of all, I checked the syntax of PARSE function but the syntax is perfectly fine but the problem is, I used the wrong data type and that datatype is not compatible with the PARSE function.

Here is the list of compatible data types (Numeric & datetime data types) that you can use with this function.

Lets rewrite the above statement with the compatible data types using PARSE function. Given below is the script.

SELECT PARSE('2013-07-12' AS datetime USING 'en-US') AS Result;
--OUTPUT

Result
———————–
2013-07-12 00:00:00.000

(1 row(s) affected)

Conclusion :
Whenever you use PARSE function, make sure that you are using the compatible data type (Numeric & datetime data types) in it.

Read Full Post »

Memory Optimized Table is a new concept introduced in SQL Server Hekaton. Memory optimized table is also a table but the major difference between memory-optimized tables and disk based tables is that the memory optimized table information is available in the memory and it does not require the pages to be read into cache from disk. The performance of memory optimized table is much higher than disk based table. Shall discuss its performance in the upcoming article.

Let me create the memory optimized table, but before proceeding with the creating memory table, we should know that whenever you create memory optimized table the database must have memory-optimized data filegroup.
Lets first create a database having memory-optimized data filegroup. Given below is the script.

CREATE DATABASE Sample_DB
ON
PRIMARY(NAME = [Sample_DB],
FILENAME = 'C:\DATA\Sample_data.mdf', size=500MB)
,FILEGROUP [hekaton_demo_fg] CONTAINS MEMORY_OPTIMIZED_DATA(
NAME = [hekaton_demo_dir],
FILENAME = 'C:\DATA\Sample_dir')
LOG ON (name = [hekaton_demo_log]
, Filename='C:\DATA\Sample_log.ldf', size=500MB)
;
GO

Once you create the database having memory-optimized data filegroup then you can create memory optimized table. Given below is the script.

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

Read Full Post »

« Newer Posts - Older Posts »