Sunday, August 31, 2014

In-Memory Optimization in SQL Server: A Simple Experiment

In one of my previous articles I briefly introduced In-Memory Optimization in SQL Server 2014. In this article we will conduct a simple experiment utilizing this new technology and discussing the benefits. The experiment will be organized in the following steps:
  1. Create the In-Memory processing enabled database
  2. Create the tables (disk-based, memory-enabled)
  3. Populate the tables with sample data
  4. Prepare the processing code for three scenarios (execution modes):
    • Disk-based processing
    • In-Memory optimization
    • In-Memory optimization with natively compiled stored procedure
  5. Run all three execution modes and measure execution times
  6. Discuss findings
Enough words, let’s proceed with some T-SQL code!


1. Create the In-Memory processing enabled database

--Creates a memory-enabled database by adding a memory-optimized filegroup with a specified directory.
--Note: This assumes that directory c:\tmp exists (the subdirectory is created by SQL Server).
CREATE DATABASE InMemDB;
GO
ALTER DATABASE InMemDB
ADD FILEGROUP [inMemOLTP_fg] CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE InMemDB
ADD FILE ( name = [inMemOLTP_fg_dir],
   filename= 'c:\tmp\InMemOLTPDir')
   TO FILEGROUP [inMemOLTP_fg];
GO


2. Create the tables (disk-based, memory-enabled)
--Create sample tables representing a scenario involving 200000 Products
USE InMemDB;
GO

--Create traditional table (disk-based)
CREATE TABLE tblDiskBased(
id INT PRIMARY KEY,
code VARCHAR(50),
descr VARCHAR(250),
price float
);
GO

--Create a durable (data will be persisted) memory-optimized table
--Alternative choice: SCHEMA_ONLY = Data will be lost if the server turns off unexpectedly
CREATE TABLE tblMemOptimized(
id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=400000),
code VARCHAR(50),
descr VARCHAR(250),
price float
)WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);
GO


--Create a durable (data will be persisted) memory-optimized table
--Alternative choice: SCHEMA_ONLY = Data will be lost if the server turns off unexpectedly
--Note: This table will be used by the natively-compiled stored procedure
CREATE TABLE tblMemOptimized2(
id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=400000),
code VARCHAR(50),
descr VARCHAR(250),
price float
)WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);
GO

So far: We have created a memory enabled database and data structures. Now, it's time to populate the tables with sample data.

3. Populate the tables with sample data
--Populate the tables with sample data (200K records)
--tblMemOptimized
DECLARE @i INT
SET @i=0
WHILE (@i<200000)
BEGIN
INSERT INTO tblMemOptimized
SELECT CAST(@i as varchar(6)) as id, ('Code '+ (CAST (@i AS varchar(6)))) as code, ('Description '+ (CAST (@i AS varchar(6)))) as descr, (10 + (500-10)*RAND()) as price
SET @i=@i+1
END
GO

--Populate the tables with sample data (200K records)
--tblDiskBased
--tblMemOptimized2
INSERT INTO tblDiskBased
SELECT * FROM tblMemOptimized;
GO

INSERT INTO tblMemOptimized2
SELECT * FROM tblMemOptimized;
GO

All three tables now have same data (200K rows each). You can easily check it out!

4. Prepare the processing code for three scenarios (execution modes):
We will wrap each code block into a stored procedure for easier execution.
The scenario is to decrease each product's price by 5%.

a. Disk-based processing
--Disk-based processing
CREATE PROCEDURE updateDiskBased as
BEGIN
UPDATE dbo.tblDiskBased
SET price=price-(price*0.05)
END
GO

b. In-Memory optimization
--Memory-Optimized
CREATE PROCEDURE updateMemoryOptimized as
BEGIN
UPDATE dbo.tblMemOptimized
SET price=price-(price*0.05)
END
GO

c. In-Memory optimization with natively compiled stored procedure
--Natively-Compiled Stored Procedure
CREATE PROCEDURE updateMemoryOptimizedNatively
with native_compilation, schemabinding, execute as owner
as 
begin atomic with (transaction isolation level = snapshot,language = N'English')
UPDATE dbo.tblMemOptimized2
SET price=price-(price*0.05)
END
GO

So far: We have created a memory enabled database and data structures. We have populated the tables with sample data (all three tables have identical data for experiment consistency purposes) and we have prepared the stored procedures that target each one of the three tables and perform the processing, that is the update of the 'price' column by decreasing it by 5%.

Now it's time to run each stored procedure and measure the execution times.

But before executing the stored procedures let's check a sample (TOP 5) from each table:

Figure 1: Top 5 Records from each table before update.



















5. Run all three execution modes and measure execution times
Ok, now it's time to execute the three stored procedures, thus update all tables and measure execution times and here they are:

Figure 2: Execution times for three modes.





























Additionally, let's check once more some records just to confirm that the output is the same in all three tables:

Figure 3: Top 5 Records from each table after update.




























6. Discussion
First of all, from the above figure we can see that the output of the three update operations is exactly the same which is something that validates the correctness of the three different execution modes.

As you can see from the screenshot with execution times (Figure 2), the slowest execution was the one that involved the disk-based table (elapsed time: 1924 ms) having also the highest CPU time (328 ms).

The fastest execution was the one of the memory-optimized table with the natively compiled stored procedure where the elapsed time was 329 ms and the CPU 93 ms!

The memory optimized execution was faster than the disk-based with 404 ms elapsed time and 250 ms CPU time but it was not faster than the memory-optimized scenario combined with the natively compiled stored procedure.

In-Memory optimization in SQL Server is a great new technology which can significantly improve performance in many cases. Especially in cases of certain workloads such as staging processes in Data Warehouse systems, high data insert rates (i.e. smart metering) and low latency execution scenarios (i.e. investment trading), In-Memory optimization can boost performance. Of course, before applying it on Production systems, a proper analysis is required along with testing.

During the last six months I have been working developing a simulator for In-Memory optimization in SQL Server. I expect to make it publicly available by the end of September 2014. So make sure that you check my official website often for updates!


Read more on this article...

Thursday, July 31, 2014

The Role of Technical Communities

Everything in our world is governed by technology. From large corporations to small stores and simple individuals, everyone uses a computational system for performing the most complex or even the simplest task. Even though the level of technology’s involvement in our lives can be characterized by many as a controversial topic, it is a fact that technology is everywhere.

The creation of new technology as well as the deployment of existing technology, in many cases, is not trivial. To this end, we find ourselves many times in the position of seeking deep technical information on how to perform certain, rather complex tasks or solve difficult technical issues. The “traditional” way of getting access to technology-related information is the different user manuals, white papers and related documentation issued by the creator of the original technology. However, most of the times, we are looking for something more. Even though the documentation can be comprehensive, is never enough. What we really seek is the experience and feedback of another person that achieved the same or at least similar task before us. That’s where the role of technical communities comes in. But before discussing the role of technical communities we should first try to define them.

So what a technical community really is? Is it a mailing list on which you can post questions on various technical issues you might be dealing with and wait for answers? Is it a platform on which you collaborate by answering various technical questions? Is it a forum, a blog maybe?

Well, a Technical Community is all the above plus much more. First of all, it is not just the platform that makes a technical community. The platforms and related tools make it easier to exchange knowledge and they have a significant role within a community. Though, what really makes a technical community is the people. It is the people who share a passion for technology and have the willingness to exchange knowledge without requesting anything in return. It is the people who spend hours and days and nights from their free time for helping others just because it feels really good doing so. It is the people who constantly get more educated on their area of expertise and present in events, trying to help other people who are interested on those topics for learning even more, answering their questions, discuss with them and guide them.

The role of the community is to serve as an “environment” that helps its members to develop their skills through knowledge exchange via presentations, hands-on labs, forums, online and offline discussions and many other activities. It helps its members to catch up with the latest technologies of common interest, easier and more efficiently. It is a “place” where each individual puts effort and time for helping others because he believes in people. A community is all about people and their need to belong to a broader group of people within which they can share knowledge, exchange ideas, and share the passion and excitement on technology.

Now imagine a world without technical communities. Imagine when searching the Web for a solution to a specific problem and end up only with sets of technical documentation and nothing from fellow people who might have experienced the same problem and found a solution “that just worked”. Would this be enough? For a limited number of cases yes, but for the majority no, at least not that easy. This is the role of technical communities, to complete the global quest for knowledge by enabling its members to share knowledge just the way each one of them perceives it. This exchange of knowledge creates interesting discussions via which all the participants end up understanding the solutions to different technical problems and challenges.
Read more on this article...

Tuesday, July 22, 2014

Introducing In-Memory Optimization in SQL Server

In-Memory OLTP (codenamed “Hekaton”) is the most significant feature shipped with SQL Server 2014. It is Microsoft’s new technology which is fully integrated into SQL Server’s Database Engine. In-Memory OLTP introduces new data structures for optimizing the performance of OLTP workloads. These data structures are called “memory-optimized” tables and along with natively compiled stored procedures, they offer a huge performance boost when it comes to processing large amounts of information, especially in data environments with high levels of concurrency. In certain cases, the execution time can be up to 50 times faster.

As the name of the technology implies, the In-Memory OLTP Engine is optimized for OLTP workloads. Some examples of best-suited workloads are:

  • Staging tables (i.e. DW)
  • High data insert rate (i.e. smart metering)
  • Low latency execution (i.e. investment trading)

When used in such scenarios, the performance gain of using the In-Memory OLTP Engine can be huge.

Memory-optimized tables store their data into memory using multiple versions of each row’s data. This technique is characterized as “Non-blocking multi-version optimistic concurrency control” and eliminates both Locks and Latches thus achieving breakthrough performance.

The list of main features of memory-optimized tables are:

  • Rows in the table are read from and written to memory
  • The entire table resides in memory
  • Non-blocking multi-version optimistic concurrency control
  • Durable & non-durable
  • A second copy maintained on disk for durability (if enabled)
  • Data in memory-optimized tables is only read from disk during database recovery
  • Interoperable with disk-based tables

Another feature of the In-Memory OLTP Engine is “natively compiled stored procedures”. A natively-compiled stored procedure is a SQL Server object that can access only memory-optimized data structures such as memory-optimized tables, table variables, etc. The main features of a natively-compiled stored procedure are:

  • It is compiled to native code (DLL) upon its creation (the interpreted stored procedures are compiled at first execution)
  • Aggressive optimizations take time at compile time
  • Can only interact with memory-optimized tables

The call to a natively compiled stored procedure is actually a DLL Entry Point which is something that offers very fast execution times!

When you properly combine memory-optimized tables with natively compiled stored procedures for heavy workloads like DW ETL processes, high data insert rate processes and processes that demand low latency execution you can expect some serious speedups.

Subsequent posts will dive into the In-Memory OLTP Engine of SQL Server 2014 and illustrate via different examples the benefits of using this technology.
Read more on this article...

Monday, June 30, 2014

New Artemiou Data Tools Release: SQL Multi-Exporter

Today I released a brand new data tool via my "Artemiou Data Tools" initiative. The tool is called SQL Multi-Exporter.

SQL Multi-Exporter allows the user to connect to a SQL Server instance and by issuing a query against a database, to export the results to an XML, CSV or HTML file with a single click.


SQL Multi-Exporter (Community Edition) Main Screen











































Currently, in the Community Edition of the tool, there are 3 available output export for the retrieved information:
  • Export to XML file.
  • Export to CSV file (multiple delimiters available).
  • Export to HTML.
Some more screenshots:
Figure 2: Connecting to a SQL Server Instance and Running a Query.















Figure 3: Preview the Retrieved Information and then Export.





































For all the data-junkies out there, a Ultimate Edition of the tool is coming out soon with a richer set of features that will skyrocket your interaction with data!
For more information and the download link please visit the tool's page.
Feel free to use the tool and provide your valuable feedback!
Read more on this article...

Tuesday, June 17, 2014

Modelling Database Creation with the Model System Database in SQL Server

The Model database in SQL Server is used as the template for all the user databases that are created on a SQL Serve instance. Most of the times we do not modify the Model database however there are cases where it might come in handy.

For example consider the following scenario: For audit purposes, you want to set your SQL Server instance, each time a user database is created, to automatically create an audit-related table as well as a relevant stored procedure that will allow each application that uses a database to be able to store in a table (in each database) the log-in and log-out times for each user.

To do this, we can create the relevant table and stored procedure in the Model database:

USE [model]
GO

CREATE TABLE [dbo].[AuditLog](
[userID] [int] NOT NULL,
[loginTime] [datetime] NOT NULL,
[logoutTime] [datetime] NOT NULL
) ON [PRIMARY]
GO


CREATE PROCEDURE dbo.logUser
  @userID int,
  @loginTime datetime,
  @logoutTime datetime
AS 
BEGIN
  INSERT INTO dbo.AuditLog
VALUES (@userID, @loginTime, @logoutTime)
END
GO

Let's take a look at the Model database:

Figure 1: The Model database after adding the table and stored procedure.



















































OK, so we have added the table and stored procedure in the Model database. Now let's create three empty sample databases and check if the same tables and stored procedures are created automatically upon their creation:

CREATE DATABASE [SampleDB1];
GO
CREATE DATABASE [SampleDB2];
GO
CREATE DATABASE [SampleDB3];
GO

Time to check the three newly created databases and see if the "AuditLog" table and "logUser" stored procedures were automatically created:

Figure 2: SampleDB1
Figure 3: SampleDB2



Figure 4: SampleDB3

As you can see, all three databases were created fully based on the (modified) Model system database, thus the "AuditLog" table and "logUser" stored procedure were automatically created.

You can use the Model database for other things as well, as the above was just a simple example. However always have in mind when creating objects in the Model database that they will be automatically created for all user databases that will be created on that instance so be careful!
For more info on the Model database, please visit the following MSDN Library link.

P.S. Many thanks to my buddy Nakis for this great discussion. Always a pleasure man! :)

Read more on this article...

Monday, May 19, 2014

How to Import and Export Unstructured Data in SQL Server - FileTables

Good evening (or good morning) friends all around the globe! Wherever you are, whatever you do, wishing you a nice rest of the day (or night!) :)

It's been more than a month since my last article but here's a tip: I'm working on something really, really massive...and yes, it has to do with the In-Memory OLTP Engine of SQL Server 2014...more news early next month! :)

This is the last of a series of posts that deal with the task of importing and exporting unstructured data in SQL Server.

The first article of this series, explained how you can store and export binary files in earlier versions of SQL Server such as SQL Server 2005 with the use of the image datatype.

The second article presented the FILESTREAM technology which was first introduced in SQL Server 2008.

This article discusses the FileTables feature which builds on top of SQL Server FILESTREAM technology. FileTables was first introduced in SQL Server 2012.

The FileTables feature allows the user to store unstructured data (i.e. files, documents, images, etc.) in special tables in SQL Server called FileTables, but being able to access them from the file system. To this end, if you have an application that needs to access unstructured data, you can directly access them from the File System even though the data is stored into FileTables in SQL Server.

Enough with the talking, let’s see an example of using this great feature.

First, let’s enable FILESTREAM (this is a prerequisite - for more info, see the second article of this series):

Figure 1: Enable FILESTREAM.



































Then, let’s create a FILESTREAM-enabled database (make sure to update the path “C:\Blog\SQLData\” if you are using a different one):

CREATE DATABASE FileStreamDB 
ON
PRIMARY ( NAME = FileStreamDBData, FILENAME = 'C:\Blog\SQLData\filestreamDB_data.mdf'),
FILEGROUP FileStreamGroup_1 CONTAINS FILESTREAM( NAME = FileStreamDBFS,
    FILENAME = 'C:\Blog\SQLData\filestream1')
LOG ON  ( NAME = FileStreamDBLogs, FILENAME = 'C:\Blog\SQLData\filestreamDB_log.ldf');
GO

Then we need to enable non-transactional access at the database level as well as specify the directory for FileTables:

ALTER DATABASE FileStreamDB
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTablesDir')
GO

Now it’s time to create the FileTable:

USE FileStreamDB;
GO

CREATE TABLE DocumentStore AS FileTable
    WITH ( 
          FileTable_Directory = 'FileTablesDir',
          FileTable_Collate_Filename = database_default
         );
GO

OK, it’s time for the magic to take place. Let's check the contents of the FileTable:

Figure 2: The FileTable in SSMS Object Explorer.


As you can see, the FileTable "DocumentStore" is currently empty:
Figure 3: Checking the contents of the FileTable.























Now, let's open the FileTable directory in Explorer, and by accessing it directly from the Windows File System, copy some files:

Figure 4: Access the FileTable directory in Explorer.

Figure 5: Copy files in the FileTable directory.



















Now, let's check again the contents of the FileTable from SSMS:

Figure 6: Checking the FileTable contents after copying the files from Windows Explorer.






































And that's it! As you can see in the above screenshot, by copying the two files from Windows Explorer directly into the FileTable directory, the corresponding two records have been created in the FileTable in the SQL Server instance! Now you can access the files either from the SQL Server Database Engine via T-SQL or directly from the Windows File System!

FileTables along with the FILESTREAM technology, enables the user with more options when it comes to storing binary objects in SQL Server. The seamless integration of FileTables with the Windows File System (NTFS) allows the user to store large binary objects in fast speeds and at the same time to be able to use the powerful features of SQL Server's Database Engine to traverse this data (i.e. full-text search, semantic search, etc.).
Read more on this article...

Tuesday, April 1, 2014

Congratulations 2014 Microsoft MVP!‏

It's that time of the year again. April 1st, where you just wait to see if you have received the magic email from Microsoft saying that you are awarded as a Most Valuable Professional. During this waiting process you just keep on asking yourself: "Was I active enough and contributed as much as possible to the community in the last year?" Of course, the answer is not easy as sharing knowledge with the community and helping other people is never enough. However, the important thing here, is to feel good with yourself that you really helped other people without requesting anything in return. To feel good because you just shared knowledge with a fellow human being irrespective of race, religion and beliefs.

Technology is common for all of us as well as the challenges. Technology is a global language. It is really a privilege interacting with all of you, my fellow community members around the globe and exchange knowledge on SQL Server. Thank you for being awesome!

I would also like to thank Microsoft for re-awarding me with the MVP award on SQL Server as well as the SQL Server Product Team for building and keep on developing such an amazing data platform. I would also like to thank the local Microsoft Office in Cyprus for supporting my community activities as well as my MVP Lead for her continuous support.

Additionally, I would like to thank you, my fellow SQL Server community members for being just great! I feel really proud for being a member of such an awesome community with so many great people and for sharing a common passion, the passion of technology!

On top of everything, a huge thank you to my beautiful wife and daughter for their true support, love and understanding for all those hours I spent away of them working on all those "community stuff" :) Without your support, you know it would just not feel right. I love you and thank you from the depths of my heart loves! This award is dedicated to you!

My commitment to all of you is that I will continue doing my best and share with you the technical knowledge that I acquire on SQL Server as well as my experience via all the available channels!

-----------------










Dear Artemakis Artemiou,

Congratulations! We are pleased to present you with the 2014 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in SQL Server technical communities during the past year.

...

The Microsoft MVP Award provides us the unique opportunity to celebrate and honor your significant contributions and say "Thank you for your technical leadership."
-----------------
Read more on this article...

Monday, March 31, 2014

The In-Memory OLTP Engine in SQL Server 2014: An Overview

A few days ago, on March 21st, I participated as an invited speaker at Microsoft Conference 2014 that took place in Nicosia, Cyprus.

My session was on SQL Server 2014 and specifically about what’s new in this release of SQL Server.

My session’s agenda was on the following areas of new features on SQL Server:
  • Interaction with Windows Azure
  • Security Enhancements
  • Performance Improvements & Enhancements
  • T-SQL Enhancements
  • In-Memory OLTP Engine
Someone might wonder why the “In-Memory OLTP Engine” is in bold, red letters :)

The reason is that this feature is the long-awaited in-memory processing engine that it is built-in into the SQL Server Engine and when you properly use it, you can get extremely high speedups in data processing when compared to the “traditional processing”.

The In-Memory OLTP Engine introduces new data structures described as “memory-optimized” (i.e. memory-optimized tables and table variables). Memory-optimized tables store their data into memory using multiple versions of each row’s data. This technique is characterized as “Non-blocking multi-version optimistic concurrency control” and eliminates both Locks and Latches thus achieving breakthrough performance. The list of main features of memory-optimized tables are:
  • Rows in the table are read from and written to memory
  • The entire table resides in memory
  • Non-blocking multi-version optimistic concurrency control
  • Durable & non-durable
  • A second copy maintained on disk for durability (if enabled)
  • Data in memory-optimized tables is only read from disk during database recovery
  • Interoperable with disk-based tables

Another feature of the In-Memory OLTP Engine is “natively compiled stored procedures”. A natively-compiled stored procedure is a SQL Server object that can access only memory-optimized data structures such as memory-optimized tables, table variables, etc. The main features of a natively-compiled stored procedure are:
  • It is compiled to native code (DLL) when it is created
  • Aggressive optimizations take time at compile time
  • Can only interact with memory-optimized tables

The call to a natively compiled stored procedure is actually a DLL Entry Point which is something that offers very fast execution times!

When you properly combine memory-optimized tables with natively compiled stored procedures for heavy workloads like DW ETL processes, high data insert rate processes and processes that demand low latency execution you can expect some serious speedups.

Feel free to view/download my presentation below:



This was the introductory of a series of posts, screencasts and webcasts having as a topic the In-Memory OLTP Engine of SQL Server 2014.

Last but not least: SQL Server 2014 will be generally available tomorrow, April 1st, 2014, so feel free to evaluate it and see the true power of the In-Memory OLTP Engine!

Read more on this article...

Friday, February 28, 2014

How to Import and Export Unstructured Data in SQL Server - FILESTREAM

A few weeks ago I posted an article on how to import and export unstructured data in SQL Server 2005 by using the "image" data type.

In this article I will show how you can manipulate unstructured data in SQL Server 2008 or later by using the FILESTREAM feature. FILESTREAM allows storing unstructured data (i.e. music, video, documents, etc.) onto the NTFS file system and manipulating it via the Database Engine.

Step 1: Enabling FILESTREAM
Before using the FILESTREAM feature you have to enable it. To this end you need to navigate to SQL Server Configuration Manager and under SQL Server Services right click on the SQL Server instance, select properties and in the “FileStream” tab check “Enable FILESTREAM for Transact-SQL access” and “Enable FILESTREAM for file I/O access”:

Figure 1: Enabling FILESTREAM


































The last step for enabling FILESTREAM is from within SSMS, to open a new query window and execute the following T-SQL statement and then restart the SQL Server instance:

EXEC sp_configure filestream_access_level, 2;
GO
RECONFIGURE
GO

Note: the available filestream access levels are:

  • 0: Disables FILESTREAM support for this instance.
  • 1: Enables FILESTREAM for Transact-SQL access.
  • 2: Enables FILESTREAM for Transact-SQL and Win32 streaming access.


Step 2: Creating a FILESTREAM-Enabled Database 
For creating a FILESTREAM-enabled database you just need to include a FILESTREAM filegroup. For example:

CREATE DATABASE FileStreamDB 
ON
PRIMARY ( NAME = FileStreamDBData,
    FILENAME = 'C:\Blog\SQLData\filestreamDB_data.mdf'),
FILEGROUP FileStreamGroup_1 CONTAINS FILESTREAM( NAME = FileStreamDBFS,
    FILENAME = 'C:\Blog\SQLData\filestream1')
LOG ON  ( NAME = FileStreamDBLogs,
    FILENAME = 'C:\Blog\SQLData\filestreamDB_log.ldf');
GO

Step 3: Creating a Table for Storing FileStream Data
The only difference between a "normal" table and a table that can store filestream data is the use of the "FILESTREAM" data type for a specific column in the table's definition script:

USE [FileStreamDB];
GO

CREATE TABLE dbo.Files
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
[FileName] VARCHAR(100),
[ActualFile] VARBINARY(MAX) FILESTREAM NULL
);
GO

Step 4: Storing FileStream Data
For this example, consider the following unstructured data file:
Figure 2: Image file to be stored in FILESTREAM-enabled database.


















Now, let's store the file in our FILESTREAM-enabled database and table that was created earlier:
USE [FileStreamDB];
GO

INSERT INTO dbo.Files
    VALUES (newid (), 
'SampleImage.png',
(SELECT * FROM   OPENROWSET(BULK 'C:\Testing\2\SampleImage.png',SINGLE_BLOB) AS x)
  )
GO

Here's the contents of the table:
Figure 3: The contents of the FILESTREAM-enabled table after inserting unstructured data (image file).
















As you can see, the file is visible on the file system level too:
Figure 4: Binary file stored using FILESTREAM - Accessible on the file system level.





















Now let's try to open the file using MS Paint: 

Figure 5: Accessing the data stored in the FILESTREAM-enabled database from the file system level (Windows).




















As you can see, the image file is stored in the SQL Server database table but besides T-SQL access, you can also access it from Windows! 

What we just did with the above example, shows a small glimpse of the real power of FILESTREAM, that is leveraging the performance and rich APIs of the Windows file system and at the same time maintaining consistency between structured and unstructured data in SQL Server.

FILESTREAM actually works like a bridge between structured and unstructured data via a combination of transactional data and file system access and can be extremely useful in cases where you have many binary objects like images and videos and you want to store it in SQL Server and being able to access it with the speed of the file system.

Read more on this article...

Saturday, February 8, 2014

A New Project: Artemiou Data Tools

A few months ago I started a new project that has to do with software development. The concept behind this project is to develop simple yet useful software tools that have to do with data processing. All my tools will be distributed under the collection name “Artemiou Data Tools”.

The majority of the tools will be interacting with SQL Server directly or indirectly. However, there will be also tools for undertaking any other data-related tasks (i.e. split files, etc.).

The purpose is to help fellow community members to solve easy tasks that however require valuable time when are not performed via a software tool. As I plan to be releasing one tool every month, make sure that you visit my website often!

The project went live a few days ago and until now “Artemiou Data Tools” has three tools with a new one coming out next week.

File Splitter v1.0
File Splitter does exactly what the name suggests: it splits files based on either file size or number of records.

File Splitter v1.0 - Main Screen (visit homepage).


Dynamic T-SQL Generator v1.2
Dynamic T-SQL Generator makes it easy for Database Developers to generate dynamic T-SQL scripts by converting a given static T-SQL script and vice versa. Also, it provides a set of additional functions like parsing T-SQL and converting T-SQL scripts to uppercase and lowercase.

Dyamic T-SQL Generator v1.2 - Main Screen (visit homepage)




























Snippets Generator v2.2
Snippets Generator allows the user to easily create/modify T-SQL snippets for SQL Server. You can create a snippet from scratch or by using snippet templates.

Snippets Generator v2.2 - Main Screen (visit homepage)













































Feel free to visit "Artemiou Data Tools", download the tools and provide your valuable feedback!







Read more on this article...

Thursday, January 30, 2014

How to Import and Export Unstructured Data in SQL Server - The IMAGE Datatype

Importing and exporting unstructured data from a database is a common practice, especially in large applications. SQL Server 2008 introduced the FILESTREAM feature that allows storing unstructured data (i.e. music, video, documents, etc.) onto the NTFS file system and manipulating it via the Database Engine. SQL Server 2012 introduced FileTables which is an enhancement to FILESTREAM.

With this post I am starting a series of articles that will deal with the topic of storing, manipulating and extracting unstructured data from SQL Server.

In this article we are going to see how it is possible to import and export binary objects in SQL Server 2005. Instead of saying more I would just like to show you by example how you can do this.

For this example, consider that we have the following two objects:

  • SampleImage.png
  • SampleTextFile.txt

Figure 1: The two sample files for the BLOBs example.




















Let's take a closer look at the files just for checking out their content:
Figure 2: Content of SampleTextFile.txt

























Figure 3: Content of SampleImage.png




























Now let's store these two files in a SQL Server table.

USE master;
GO

--Create test database
CREATE DATABASE [BinaryFilesDB];
GO

--Use test database
USE [BinaryFilesDB];
GO

--Create table that will be hosting the files
CREATE TABLE [dbo].[tblFiles](
[fileID] [bigint] IDENTITY(1,1),
[fileName] [nvarchar](255) NULL,
[binFile] [image] NOT NULL);
GO

--
--Import SampleTextFile.txt
--
INSERT INTO dbo.tblFiles
           ([fileName],[binFile])
VALUES     ('SampleTextFile.txt',(SELECT * FROM   OPENROWSET(BULK 'c:\testing\SampleTextFile.txt',SINGLE_BLOB) AS x))
GO
--
--Import SampleImage.png
--
INSERT INTO dbo.tblFiles
           ([fileName],[binFile])
VALUES     ('SampleImage.png',(SELECT * FROM   OPENROWSET(BULK 'c:\testing\SampleImage.png',SINGLE_BLOB) AS x))
GO

Let's check the contents of "tblFiles" table:
Figure 4: Contents of the table after importing the binary files.








Let's rename the files, just for performing a minor modification:

--Rename files
UPDATE tblFiles
SET fileName='SampleTextFileModified.txt'
WHERE filename='SampleTextFile.txt';
GO

UPDATE tblFiles
SET fileName='SampleImageModified.png'
WHERE filename='SampleImage.png';
GO

Let's check the table contents again:
Figure 5: Renamed contents of table "tblFiles"








Now let's produce the export statements:

SELECT
'bcp "select binFile from BinaryFilesDB.dbo.tblFiles where fileid=' + cast (fileID as varchar(50)) + '" queryout "c:\testing\'+[filename]
+'" -f bcp.fmt -S .\SQL2K14CTP2 -T' as RunTheseOnCommandPrompt
FROM BinaryFilesDB.dbo.tblFiles;
GO

Some notes on the above T-SQL statement:

- SQL2K14CTP2 is the named instance of my SQL Server
- Also note that I am using Trusted (-T) connection for my generated bcp commands
- If you want to use username/password instead of a Trusted connection replace "-T" with -U [username] -P [password]
- You can download the bcp.fmt format file from here.

Figure 6: Generated statements for exporting the binary files.










Now let's run the two bcp commands on the command prompt:
Figure 7: Executing the bcp commands for exporting the binary files.


















Also, let's check the contents of the "c:\testing" directory:
Figure 8: Binary files successfully exported from SQL Server.












As you can see, the two renamed files were successfully exported and have the exact same size as the original ones!

I hope you enjoyed the article! Until next time!
Read more on this article...