Azure Cosmos DB

Azure Cosmos DB (“Project Florence”) revolutionary way for globally replicating your data, multi-model database service. Azure Cosmos DB lets you store your data globally-distributed and elastically scale throughput and storage across any number of geographical regions at anytime with single click. Addition to this, Azure Cosmos DB automatically indexes your data which helps in fast querying & also guarantees millisecond latencies.  On 10th May 2017 Microsoft announces general availability of Azure Cosmos DB.

 

Capture

Checkout msdn blog for detailed info : https://azure.microsoft.com/en-us/blog/a-technical-overview-of-azure-cosmos-db/

 

Query Hints – FORCE ORDER

SQL Query Optimizer basically determines most efficient way to execute given query by considering several query plans. Query Hints tell the optimizer to apply hints throughout execution of query. Here we will talk about FORCE ORDER query hint.

Example Script:

CREATE TABLE DIM_1(
ROWID INT IDENTITY(1, 1) NOT NULL,
CODE NVARCHAR(50) NULL
)
CREATE TABLE DIM_2(
ROWID INT IDENTITY(1, 1) NOT NULL,
CODE NVARCHAR(50) NULL
)

CREATE TABLE FACT(
ROWID INT IDENTITY(1, 1) NOT NULL,
VALUE_1 NVARCHAR(50) NOT NULL,
VALUE_2 NVARCHAR(50) NOT NULL,
DIM_1_ID INT,
DIM_2_ID INT
)

SELECT *
FROM FACT t1
JOIN DIM_1 t2 ON t1.DIM_1_ID = t2.ROWID
JOIN DIM_2 t3 ON t1.DIM_2_ID = t3.ROWID

Query without hint:

Capture

If you observed SQL Server optimizer rearranges joins order that it thinks will be optimal for your query to execute.

Query with hint:

SELECT *
FROM FACT t1
JOIN DIM_1 t2 ON t1.DIM_1_ID = t2.ROWID
JOIN DIM_2 t3 ON t1.DIM_2_ID = t3.ROWID
OPTION (FORCE ORDER)

Capture1.PNG

When you put this query hint on to your query, it tells SQL Server that when it executes the statement to not change the order of the joins in the query. It will join the tables in the exact order that is specified in the query.

The FORCE ORDER query hint is only used when you want to override the way that SQL Server wants to execute this query.

Sharing experience of Microsoft build 15 seminar (Mumbai)

8th June 2015, Monday….started early (5:45 AM) to attend Microsoft’s build 15 seminar in Mumbai.

Jpeg

Jpeg

The seminar was hosted by 3 key members…

Pete Brown – Technical Evangelist
Shen Chauhan – Senior Program Manager
KiranKumar Balijepalli – Senior Program Manager

The seminar was mainly focused on deep dive into Windows 10 developer platform for app & web developers wherein Pete Brown, Shen Chauhan demonstrated multiple platforms, new features in Windows 10, Visual Studio 2015 features such as UWP, Machine Learning API, Cassandra, IOT, Raspberry pi technology. The main learning area was Universal Windows Platform (UWP), that can run across wide variety of devices, which provides the guaranteed core API layer across devices. You can create a single app package that can be installed onto a wide range of devices. Pete Brown & Shen Chauhan demonstrated some of the good coding sessions & exciting demos which helped a lot to understand new features and advantages.

Jpeg

Jpeg

I was expecting more about Cloud technology. However, KiranKumar covered bit of Microsoft Azure wherein he elaborated more about the SQL Azure, NOSQL data management services such as Cassandra, DocumentDB, MongoDB. Also, covered a very quick overview on Azure Data lake Service, Elastic SQL Database Pool for azure.

Overall, it was informative seminar wherein I personally come to know about many new things that Microsoft was working on and also planning in near feature . Thanks to Microsoft for bringing in Build 15 in Mumbai!

Check How Old are you? by Microsoft.

SQL Server 2012 TSQL enhancements – Part 3

In the previous article SQL Server 2012: TSQL enhancements – Part 2 we have seen Ad-hoc query paging feature of SQL 2012 that can be leverage to implement Paging functionality in query.

Today, we will see few T-SQL features that can be use in most application development.

THROW statement

  • New operator for enhanced error handling.
  • Can be instead of RAISERROR, however RAISERROR requires user defined message to be added to sys.messages table before you use it to raise an error.
  • THROW statement does not require error number to exist in system table.
  • Exception raised by THROW statement always set severity level to 16.
  • The statement before THROW statement need to terminate with semicolon (;).

Syntax-THROW <error number>, <message>, <state>

Code Snippets –

THROW 51000, ‘The record does not exist.’, 1;

GO

DECLARE @TestRethrow TABLE

(    ID INT PRIMARY KEY

);

BEGIN TRY

INSERT @TestRethrow(ID) VALUES(1);

—  Force error 2627, Violation of PRIMARY KEY constraint to be raised.

INSERT @TestRethrow(ID) VALUES(1);

END TRY

BEGIN CATCH

PRINT ‘In catch block.’;

THROW;

END CATCH;

 

 

TRY_CONVERT

  • A new function to handle conversion issues/errors.
  • Returns a value to specified data type in case of success.
  • Return “NULL“ in case of conversion fail.

Syntax- TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Code Snippets –

SELECT CONVERT(FLOAT, ‘test’) Throws an error – Error converting data type varchar to float.
SELECT TRY_CONVERT(FLOAT, ‘test’) Error is handled by returning NULL

     

IIF

  • Evaluate the Boolean expression and return the True or False value.
  • Can be used instead of CASE statement

Syntax- IIF ( boolean_expression, true_value, false_value )

Code Snippets –

declare @a int =1, @b int = 0

select CASE WHEN @a = @b THEN ‘True’ ELSE ‘False’ END

select IIF(@a = @b, ‘True’, ‘False’)

If you like this post, please share your comments 🙂

SQL Server Memory Has Been Paged Out

Microsoft SQL Server manages memory dynamically based on the memory requirements of server load. But sometime you experience problem such as…

  • The performance of SQL Server decreases suddenly.
  • A computer that is running SQL Server stops responding for a short time.
  • A time-out occurs for applications that connect to SQL Server.
  • Problems occur when you run even simple commands or use applications on the system

This problem occurs because the Windows operating system pages out the working set of the SQL Server process. While looking for workaround for this problem I came across a very good article “How to reduce paging of buffer pool memory in the 64-bit version of SQL Server” by Microsoft Support Team which solved my problem.