Archive

Archive for the ‘SQL Server’ Category

Why do we need SQL Server 2017

September 18, 2017 Leave a comment

SQL Server 2017 (Linux)

DIM (Data is Money) is the latest or at-least a new business model. Microsoft did a fantastic job in implementing SQL Server in Linux as a cross platform. This strategy has surprised Microsoft community and overturned the way we think about Microsoft database management products. In this story, I am going to talk about the SQL Server 2017 on Linux.

Reference:

https://blogs.technet.microsoft.com/dataplatforminsider/2017/08/31/six-reasons-to-consider-sql-server-2017-especially-if-youre-on-linux/

SQL Server 2017 runs on Windows, Linux, Docker containers. It has key features that attract many industry leaders for these features.

  • Cost
  • Security
  • Performance
  • Simplicity
  • Flexibility
  • Everything built in

Cost:

First of all, I would think about product cost. Because, there are many technologies available as an open source. Linux is open source and free for commercial use. So, we can remove Operating system license cost from the investment. Linux can run on any hardware and we can have affordable hardware to setup a business. It reduces the infrastructure cost. Well, With these two cost reduction options, it would possible to achieve ROI in short period of time.

  • With SQL Server 2017 on Linux, it’s possible to realize a lower total cost of ownership (TCO) and quicker return on investment (ROI)
  • Expand your hardware options and achieve ROI more quickly with Linux.
  • Mobile BI anywhere at a fraction of cost.

Security:

Next, with the current digital environment, we are worried about data protection. Past several yearly we have been hearing about data breaches in different sectors. Some events are unavoidable but we need to take maximum effort to protect it. In this way, we need to give preferences to security. So, what kind of security does SQL Server have?

SQL Server 2017 – Data Sheet

  1. Fewest NIST vulnerabilities over last 7 years
  2. Encrypt data at rest and in motion with Always Encrypted
  3. Dynamic Data Masking conceals sensitive data
  4. Control access to database rows with Row-Level Security

Performance:

We do not have much time to wait for a task to complete. It is due to heavy competition and vast amount of data to process to get serious decision quickly. Such as, Weather forecast, Medical diagnosis and real-time sale performance during holidays, etc. So, what does SQL Server 2017 have?

From Data Sheet:

  • Speed query performance without tuning using Adaptive Query Processing
  • Faster transactions with In-Memory OLTP and up to 100x faster analytics with in-memory Columnstore
  • Real-time operational analytics when you combine inmemory technologies

Simplicity:

Who do like to spend lot of man hours for just migrating database from one platform to others. With SQL Server 2017, we can transfer database from Windows to Linux easily by taking backup and restore. For security, we can use encrypted backup with master key.

Reference:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-migrate-restore-database

Flexibility:

This word “Flexibility” is more complicated then it looks. Because, it has to satisfy every audience from every perspective. SQL Server 2017 is also a flexible platform to connect with any application. Also, it gives various flexibility for language of your choice, on the platform of your choice, on premises or in the cloud.

Language of your choice:

  • C#, Java, Ruby, or another coding language

Platform of your choice:

  • Support for Windows and Linux operating systems and Docker containers

Cloud:

  • Any cloud, any platform including OpenShift, Red Hat OpenStack, Kubernetes, Docker Swarm and Pivotal

Everything built-in:

SQL Server 2017 is a complete package that can be used for data management with 99.99% availability, simplicity, security, efficiency, affordability and reliability. Also, features like data compression, columnstore, partitioning, high availability, and disaster recovery.

Advertisements
Categories: SQL Server

Gartner – Operational Database Management Systems

September 13, 2017 Leave a comment

I recent got to see the gartner’s magic quadrant for Operational Database Management Systems (ODMS). It is an interesting chart to visualize different companies and its market position in ODMS. There are 4 categories given in the chart with company’s position.

  • Niche Players –
    1. Couchbase
    2. MapR
    3. Clustrix
  • Visionaries
    • None
  • Challengers
    1. MarkLogic
    2. MongoDB
    3. EnterpriceDB
  • Leaders
    1. Microsoft
    2. Oracle
    3. SAP

Image credit: https://www.gartner.com

G_ODMS

For more details download the document from this link

https://info.microsoft.com/CO-SQL-CNTNT-FY16-09Sep-14-MQOperational-Register.html

Categories: SQL Server

Gartner Magic Quadrant BI and Analytics

February 18, 2017 Leave a comment

It is very interesting to see a consistent growth of Microsoft in BI and Analytics environment. Lets see Gartner’s Magic Quadrant BI and Analytics graph so several years.

2013

2013_mq

2014

2014_mq

2015

2015_mq

2016

2016_MQ.png

2017

2017_mq

We will revisit next year to see what-else has changed. By the way, what is your favorite tool.

Categories: Application, SQL Server, Tools

SQL Server 2016 – Arrived

June 3, 2016 Leave a comment

This is a good news for the SQL Server community for this year. Microsoft has released SQL Server 2016 on June 1st  2016.

You need to sign-up to download the evaluation edition from this web page.

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

There are many exciting features in this version. Now, SQL Server has moved to the top of the database management servers ladder.

GartnerMQ_ODBMS

 

Categories: SQL Server

Insert or Update a File in Database – SQL Server

February 17, 2016 Leave a comment

Database is a repository for data of various formats. In the past, we use text files to store huge data and technology revolution has changed everything for what we store and How we store data. Big data is a good example. It happened in different forms as punch card, file system, relational database, graph db, NoSQL, NewSQL and etc.

But, in this blog post I am going to show a simple example for storing (insert) and altering (update) a binary image file stored in a table.

Prerequisite

Create a Table

CREATE TABLE [dbo].[Images](
ImageID INT IDENTITY(1,1),
ImageDesc VARCHAR(20) NOT NULL,
[ImageData] VARBINARY(MAX) NOT NULL
) ON [PRIMARY]

Sample Image file – SQL2016.jpg

SQL2016

Insert a record with a Image file

INSERT dbo.[Images]
(ImageDesc, ImageData)
SELECT ‘SQL2016′ AS ImageDesc, * FROM OPENROWSET(BULK N’C:\Test\SQL2016.jpg’, SINGLE_BLOB) AS Img

Update an existing record with Image file

UPDATE dbo.[Images]
SET ImageDesc = ‘SQLServer’
,ImageData = BulkColumn from Openrowset( Bulk ‘C:\Test\SQLServer.jpg’, Single_Blob) as Img
WHERE ImageID = 1

This blog post is for code reference. You can use this code with not restriction.

Categories: SQL Server

How to debug T-SQL statement in SQL Server Management Studio

September 1, 2015 Leave a comment

Debugging is an important feature for every programming language with no exception SQL Server has this to help developer trace the their code and remove potential logical issue in the code before moving to production environment or simply to find the bug in the code.

SQL Server management Studio (SSMS) provide easy way to debug T-SQL statements.

Expectation: This blog post is to show how to use debug feature in SSMS with simple T-SQL statement.

T-SQL

declare @i as int, @n as int;
set @i = 0;
set @n = 5;

while @i < @n
begin
set @i = @i + 1;
print @i;
end

Step 1: Open new query window and paste the above code

Step 2: Click Debug button on the toolbar

debug_button

Step 3: Once debug starts, another debug related toolbar and windows (local variables, call stack) will show up. We can view and modify local variables and parameters, view global variables, as well as control and manage breakpoints while debugging.

debug_window

Step 3: Click Step into (F11 – function key) to execute step by step.

This image is captured after 3 iterations. so, we can see the value of the variable @i = 3 and @n =5.

iteration

Try other debugging features in SSMS and keep  your code robust. Thanks.

Reference

Categories: SQL Server Tags:

Synchronize Table – SQL Server

September 1, 2015 2 comments

We have been doing table synchronize for many years using various techniques. Merge statement in SQL Server is not new for us. But, I liked this statement. So, I decided to write a simple note about it.

Expectation: This blog post is for beginners those who want to learn about this Merge statement using simple examples.

We can perform insert, update, or delete operations on a target table based using single statement based on the results of joining source and target table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table. – MSDN reference

Creating working environment:

This is the main (target) table that we are going to use for synchronization.

declare @emp as table
(
EmployeeId int not null primary key,
DepartmentId int not null,
EmployeeName varchar(50) not null
)

This is the stage table to hold the data for sync.

declare @emp_stg as table
(
EmployeeId int not null,
DepartmentId int not null,
EmployeeName varchar(50) not null
)

Loading few rows to the stage table.

Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (1,1, ‘Robert’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (2,2, ‘Bobby’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (3,2, ‘Dan’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (4,3, ‘Johnson’);
Insert into @emp_stg (EmployeeId, DepartmentId, EmployeeName) values (5,4, ‘Monti’);

Now, using merge statement we are going to insert new rows to the main table @emp.

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
WHEN NOT MATCHED BY TARGET THEN
INSERT  (employeeid, departmentid, employeename)
VALUES (src.employeeid, src.departmentid, src.employeename)
OUTPUT $Action, inserted.*
;

$Action column will have the DML operation string value such as ‘INSERT’ or ‘UPDATE’ or ‘DELETE’. inserted.* will give us the news inserted row(s).

Now, We are now going to update the rows if there is a change in the incoming data.

This is a simple DML update statement for this experiment.

UPDATE @emp_stg set EmployeeName = ‘robert k’ where EmployeeId=1;

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
WHEN MATCHED AND
(src.departmentid <> tgt.departmentid or
src.employeename <> tgt.employeename)
THEN
UPDATE SET tgt.departmentid = src.departmentid,
tgt.employeename = src.employeename
OUTPUT $Action, inserted.*
;

Following merge statement is to perform table sync with delete operation for the unavailable data.

DELETE from  @emp_stg where EmployeeId=3;

MERGE @emp as tgt
USING @emp_stg as src
ON src.employeeid = tgt.employeeid
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $Action, deleted.*
;

I have separated Insert, Update and Delete operation for this example to show the flexibility of the Merge statement. But, These statements can be used together to perform all in one operation.

Categories: SQL Server Tags: ,
%d bloggers like this: