SQL Server (vNext) on Linux – CTP 1.0

linuxlove

Last month November 16th, 2016, Microsoft announced its first Community Technology Preview of next release of SQL Server, called SQL Server vNext. This release will run not only on Windows but also no Linux, Docker, or macOS (via Docker).

Here is the download link for SQL Server vNext CTP 1.0

Sneak peak: New features and enhancements

Database Engine

  • Addition of new compatibility level 140
  • Improvement in incremental statistics update threshold (available through new database COMPATIBILITY_LEVEL 140)
  • Addition of new DMVs:  sys.dm_exec_query_statistics_xml (return live query plan and execution statistics for the running batch), sys.dm_os_host_info (provide operating system information for both Windows and Linux)
  • Many performance and language enhancements to In-Memory tables:
    • Support for more than 8 indexes
    • Support for sp_spaceused, sp_rename, CASE statement, TOP (N) WITH TIES
  • Clustered Columnstore Indexes now support LOB columns (nvarchar(max), varchar(max), varbinary(max)).
  • New function: STRING_AGG()
  • Database roles are created with R Services for managing permissions associated with packages
  • Addition for new Japanese Collation

R Services

  • Microsoft R Server and SQL Server R Services provide a variety of new features to enhance integration of R with SQL Server and the Microsoft BI stack

Integration Services (SSIS)

  • Support Scale Out of SSIS – easier to run SSIS package on multiple machines
  • Support for Microsoft Dynamics Online Resources – connection to Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online thru OData source and connection manager

Useful Links

 

Advertisements

How to use insert query to store images


I have been usually asked the question on how to insert  
images using insert statements. Here is the way to do that…

Let’s create a table to store images having two columns one using IMAGE data type and other using VARBINARY data type. My intention here is to demonstrate that image can be stored in either of the two types in same manner.

CREATE TABLE image_tbl
  (
     img_varbinary VARBINARY(max),
     img_image     IMAGE
  );
GO

We shall now use OPENROWSET with BULK mode to read the image data. BULK mode allows us to interpret and read the image data in a binary stream. This is where we take the advantage and insert the data as a binary stream into our table.

In the first example we shall look at queries inserting in column with VARBINARY data type using JPEG and PNG image.

INSERT INTO image_tbl(img_varbinary)
SELECT *
FROM   OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image1.png’, SINGLE_BLOB) IMG_DATA;
 
INSERT INTO image_tbl(img_varbinary)
SELECT *
FROM   OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image2.JPG’, SINGLE_BLOB) IMG_DATA;

Next, we shall look at queries inserting data in column with IMAGE data type using same JPEG and PNG images.

INSERT INTO image_tbl(img_image)
SELECT *
FROM   OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image1.png’, SINGLE_BLOB) IMG_DATA;

 

INSERT INTO image_tbl(img_image)
SELECT *
FROM   OPENROWSET(BULK N’C:\Users\lokesh.vij\Desktop\image2.JPG’, SINGLE_BLOB) IMG_DATA;

Here is how the output looks like:

image_tbl

Length of replicated LOB data exceeds configured maximum

We had set-up peer-to-peer replication on three geographically distinct nodes with around 800 articles being published with data type of lot many columns as varchar(max), image and text data. Once the replication was up and running, users reported this error “com.microsoft.sqlserver.jdbc.SQLServerException: Length of LOB data (70199) to be replicated exceeds configured maximum 65536”.

Reason for this issue: Default SQL Server configuration supports “Max text replication size” of 65536 bytes (2 power 16 bytes) for text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data columns. We get this exception when in a single insert or update statement data size for any of the replicated text column violates this limit.

Solution: There are two ways to check and rectify this issue. Once using T-SQL and another using SSMS

Using T-SQL
Check the server configuration for current max text replication size

USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ; 
RECONFIGURE ; 
GO
EXEC sp_configure 'max text repl size'; 
GO

-- OUTPUT:
-- name                    minimum  maximum     config_value run_value
-- ----------------------- -------- ----------- ------------ -----------
-- max text repl size (B)  -1       2147483647  65536        65536

Output clearly shows maximum value of 65536 bytes. Now, Execute the below set of statements to resolve the issue.

-- For SQL Server 2005
USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ; 
RECONFIGURE ; 
GO
EXEC sp_configure 'max text repl size', 2147483647 ; 
GO
RECONFIGURE; 
GO

-- For SQL Server 2008 (and 2008 R2)
USE TestDB;
GO
EXEC sp_configure 'show advanced options', 1 ; 
RECONFIGURE ; 
GO
EXEC sp_configure 'max text repl size', -1 ; 
GO
RECONFIGURE; 
GO

-1 indicates that there is no limit other than imposed by the data type itself. Furthermore, max data replication option applies to transactional replication and CDC (Change Data Capture) and is ignored for snapshot replication and merge replication.

Using SSMS

  1. Open SSMS and connect to object explorer
  2. Right-click on the server name (in explorer) and choose properties
  3. Select “Advanced” options on the properties page.
  4. Max text replication Size is available under “Miscellaneous” header.
  5. Change the value from 65536 to -1 or 2147483647 (depending on the version on SQL Server) and press OK.

 

List jobs residing on a particular database

Below query become very handy when it is required to list all the jobs residing on a specific database.

DECLARE @db_name VARCHAR(100);

SET @db_name = 'My_Database'; -- Change your database name here

SELECT database_name [Database Name],
 name [Job Name],
 js.job_id [Job ID]
FROM msdb.dbo.sysjobsteps js
 INNER JOIN msdb.dbo.sysjobs_view jv
 ON js.job_id = jv.job_id
WHERE database_name = @db_name;

If it is required to get the complete list of jobs on a server along with the database they reside on, below query helpful.

SELECT DISTINCT database_name [Database Name],
                name          [Job Name],
                js.job_id     [Job ID]
FROM   msdb.dbo.sysjobsteps js
       INNER JOIN msdb.dbo.sysjobs_view jv
               ON js.job_id = jv.job_id
ORDER  BY database_name;

Myth: Truncate cannot rollback

There is plethora of reasons which people give when asked if truncate can rollback. Some say it does not rollback as it is a DDL (Data Definition Language) statement and DDL statements are not allowed in explicit transactions. Other view is that as truncate does not create entries in transaction log file it will not rollback. And many other reasons!

Truth is truncate can rollback!

Let us try to prove this fact; Code snippet below does the following:
1) Create table tblTruncate
2) Insert few values [I have inserted 3 values]
3) Truncate the table between an explicit transaction

CREATE TABLE tblTruncate
  (
     id INT
  )
GO

INSERT INTO tblTruncate(id) VALUES (1),(2),(3)
GO
-- Open transaction
BEGIN TRAN
-- Begining of the transaction
PRINT '*********Inside transaction***************'
PRINT ' '
SELECT 'No of records before truncate - ' + CONVERT(CHAR(2), COUNT(*))
FROM   tblTruncate
PRINT '*********Truncating table***************'
PRINT ' '
--Truncating table tblTruncate
TRUNCATE TABLE tblTruncate

SELECT 'No of records after truncate - ' + CONVERT(CHAR(2), COUNT(*))
FROM   tblTruncate

-- Transaction rollback
ROLLBACK TRAN
-- Transaction closed
PRINT '*********Outside transaction***************'
PRINT ' '

SELECT 'No of records after rollback - ' + CONVERT(CHAR(2), COUNT(*))
FROM   tblTruncate 

Here it the output log:

*********Inside transaction***************
 
No of records before truncate - 3 

*********Truncating table***************
 
No of records after truncate - 0 

*********Outside transaction***************

No of records after rollback - 3 

Clearly we can see that all the three records which were truncated from table tblTruncate were present after rollback.

Taking another example: Here we will create a table inside a transaction within rollback mode and try to check if table exists after rollback.

-- Pre-condition to check if table exists
IF OBJECT_ID('table_inside_transaction') IS NOT NULL
  PRINT 'Table exists!'
ELSE
  PRINT 'Table does not exist!' 
Print '***** Transaction begins *****'
print ''

--Open transaction
BEGIN TRAN

CREATE TABLE table_inside_transaction
  (
     id INT
  )

-- Checking if table is created inside transaction
IF OBJECT_ID('table_inside_transaction') IS NOT NULL
  PRINT 'Table exists!'
ELSE
  PRINT 'Table does not exist!' 
  
ROLLBACK TRAN 
-- Close transaction in rollback mode


Print '***** Transaction closed *****'
print ''
-- Checking if table exists
IF OBJECT_ID('table_inside_transaction') IS NOT NULL
  PRINT 'Table exists!'
ELSE
  PRINT 'Table does not exist!' 

Here is the output log generated:


Table does not exist!

***** Transaction begins *****
 
Table exists!

***** Transaction closed *****
 
Table does not exist!

Again, it is very clear that DDL transactions can rollback. Is it good to think at this stage that every DDL transaction can be executed within transaction?

Answer is NO!

All the DDL transactions can be executed inside transaction except the following:
CREATE/ALTER/DROP DATABASE
CREATE/ALTER/DROP FULLTEXT INDEX
CREATE/ALTER/DROP FULLTEXT CATALOG
RECONFIGURE
BACKUP/RESTORE

Another thing worth mentioning is that UPDATE STATISTICS can be used inside an explicit transaction. But it cannot be rolled back; the fact being UPDATE STATISTICS commits independently of the transaction in which it is enclosed.

Hope this post was helpful and set a group to bust myths around DDL inside transactions.

Happy Learning
Lokesh Vij

ISDATE() revisited

ISDATE is a wonderful function used to test if the underlying value is a valid date. I have often found developers wink-their-eyes in shock over ISDATE’s non-deterministic behaviour. I hope this small presentation will help developers use ISDATE with care.

What this function does?

ISDATE function returns 1 if the value/expression under test is a valid datetime and returns 0 for all other values.

Syntax

ISDATE (value/expression under test)

Let us know take a deep-dive into application of this function. Though it would be very easy for me to list down what is valid (when function returns 1) and what is invalid (when function returns 0) date; it would be better to apply and check how this function behaves in different scenarios (build over different application sets, given below, with varied complexity). These will build-up our learning base very well.

Application Set – 1

Below is the list of queries where ISDATE is applied to different expressions. For all the these cases, consider that dateformat is set to DMY and language is set to English (US).

SELECT ISDATE('15/01/2012') -- Result returned = 1
SELECT ISDATE('15-01-2012') -- Result returned = 1
SELECT ISDATE('15-2012-01') -- Result returned = 1
SELECT ISDATE('01-15-2012') -- Result returned = 0
SELECT ISDATE('01-2012-15') -- Result returned = 0
SELECT ISDATE('2012-01-15') -- Result returned = 0
SELECT ISDATE('2012-15-01') -- Result returned = 1

As per ISDATE logic it is clear that for #1 and #2 the return value should be 1 and for all other it should be 0. But this is not the case!

#3 and #7 also have return value of 1. Why??

Because, SQL Server can implicitly convert expression (character string specifically) in #3 and #7 into valid datetime and for others (#4, #5 and #6) implicit conversion will fail with out-of-range conversion error. To demonstrate this, just try to execute the following code snippet in SSMS.

PRINT '@DT1'
DECLARE @DT1 DATETIME = '15/01/2012';

PRINT '@DT2'
DECLARE @DT2 DATETIME = '15-01-2012';

PRINT '@DT3'
DECLARE @DT3 DATETIME = '15-2012-01';

PRINT '@DT4'
DECLARE @DT4 DATETIME = '01-15-2012';

PRINT '@DT5'
DECLARE @DT5 DATETIME = '01-2012-15';

PRINT '@DT6'
DECLARE @DT6 DATETIME = '2012-01-15';

PRINT '@DT7'
DECLARE @DT7 DATETIME = '2012-15-01';

You will find the following error message for @DT4, @DT5 and @DT6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Application Set – 2

Let us try to re-execute the same set of statements executed in Application Set – 1, but this time dateformat is set to MDY (SET DATEFORMAT MDY)

SELECT ISDATE('15/01/2012') -- Result returned = 0
SELECT ISDATE('15-01-2012') -- Result returned = 0
SELECT ISDATE('15-2012-01') -- Result returned = 0
SELECT ISDATE('01-15-2012') -- Result returned = 1
SELECT ISDATE('01-2012-15') -- Result returned = 1
SELECT ISDATE('2012-01-15') -- Result returned = 1
SELECT ISDATE('2012-15-01') -- Result returned = 0

Return value of the function is just the opposite. This does not need any explanation. But it is worth mentioning here that

response of ISDATE function depends upon the current dateformat

Application Set – 3

Dateformat being MDY let us change the language setting to something other than English (US) and check the result. Say for example, we are changing the language to Italian (SET LANGUAGE Italian)

SELECT ISDATE('15/01/2012') -- Result returned = 1
SELECT ISDATE('15-01-2012') -- Result returned = 1
SELECT ISDATE('15-2012-01') -- Result returned = 1
SELECT ISDATE('01-15-2012') -- Result returned = 0
SELECT ISDATE('01-2012-15') -- Result returned = 0
SELECT ISDATE('2012-01-15') -- Result returned = 0
SELECT ISDATE('2012-15-01') -- Result returned = 1

Again we noticed that this has an impact on ISDATE response. Hence it is worth mentioning that

response of ISDATE function depends upon current language settings

Application Set – 4

Let us try to check the behaviour of ISDATE when expression is of the following types (dateformat being DMY and language being English (US)):

  • Varchar
  • N Varchar
  • Datetime
  • Datetime2
  • Date
  • Null expression

Here is the code snippet used to check this:

SET DATEFORMAT DMY;
SET LANGUAGE English;
-- When expression => VARCHAR
DECLARE @dt_varchar VARCHAR(10) = '15-01-2012'
SELECT ISDATE(@dt_varchar)
GO
-- When expression => N'VARCHAR
DECLARE @dt_NVarchar NVARCHAR(10) = N'15-01-2012'
SELECT ISDATE(@dt_NVarchar)
GO
-- When expression => NULL
DECLARE @dt_Null DATETIME = NULL
SELECT ISDATE(@dt_Null)
GO
-- When expression => DATETIME
DECLARE @dt_DateTime DATETIME = '15-01-2012 00:00:00.000'
SELECT ISDATE(@dt_DateTime)
GO
-- When expression => DATETIME2
DECLARE @dt_DateTime2 DATETIME2 = '15-01-2012 00:00:00.0000000'
SELECT ISDATE(@dt_DateTime2)
GO
-- When expression => DATE
DECLARE @dt_Date DATE = '15-01-2012'
SELECT ISDATE(@dt_Date)
GO

Here is the resultant

#
Expression
Return Value
1
VARCHAR
1
2
N'VARCHAR
1
3
Is Null
0
4
DATETIME
1
5
DATETIME2
ERROR: Invalid argument
6
DATE
ERROR: Invalid argument

From above, picture about the behaviour of ISDATE is very clear now. It is also crystal clear that Datatime2 and Date data type are not valid arguments for ISDATE function. But care should be taken when datetime2 value is directly passed as character string in ISDATE argument. Here return value will be zero rather than an error message.

SELECT ISDATE('15-01-2012 00:00:00.0000000')
-- Returns 0

In nutshell, we can summarize our discussion with following points:

  • Behaviour of ISDATE function is dependent on default dateformat and language
  • It returns 1 when the string argument can implicitly be converted into valid datetime value. Increasing the seconds precision of the same string value to represent datetime2 format (or even if the seconds precision exceeds 3 significant digits) will return a 0 value.
  • Date and Datetime2 arguments are not allowed, they result in invalid argument error
  • Null values inside expressions also results in 0 as return value
  • Values such as text, ntext and image data types also return 0 value

I hope you enjoyed reading this post and must have admitted few facts to your memory.

The motivation to write this post came from SQLServerCentral Question of the Day. Here is the link to the question posted by me –> link

Happy Learning
Lokesh Vij